[Home] [Help]
PACKAGE BODY: APPS.DPP_UTILITY_PVT
Source
1 PACKAGE BODY DPP_UTILITY_PVT AS
2 /* $Header: dppvutlb.pls 120.43 2008/05/29 05:38:13 sdasan noship $ */
3 PROCEDURE Check_Transaction(
4 p_transaction_header_id IN NUMBER
5 ,p_status_change IN VARCHAR2
6 ,x_rec_count OUT NOCOPY NUMBER
7 ,x_msg_data OUT NOCOPY VARCHAR2
8 ,x_return_status OUT NOCOPY VARCHAR2)
9 IS
10 l_item_number VARCHAR2(100);
11 l_flag VARCHAR2(30) :=NULL;
12 l_process_code VARCHAR2(100);
13
14 CURSOR select_claims_csr(p_transaction_header_id IN VARCHAR2)
15 IS
16 SELECT ocl.claim_number
17 FROM ozf_claims_all ocl,
18 dpp_transaction_claims_all dtcl
19 WHERE dtcl.transaction_header_id = p_transaction_header_id
20 AND dtcl.claim_type IN('SUPP_CUST_CL', 'SUPP_DSTR_CL')
21 AND dtcl.claim_id = ocl.claim_id
22 AND ocl.status_code <> 'CLOSED';
23
24 BEGIN
25 x_rec_count := 0;
26 x_msg_data := '';
27 Fnd_Msg_Pub.initialize;
28
29 x_return_status := fnd_api.g_ret_sts_success;
30
31 IF p_status_change = 'CANCELLED' THEN
32 BEGIN
33 SELECT DISTINCT 'Y' INTO l_flag
34 FROM dpp_transaction_lines_all dtla
35 WHERE transaction_header_id = p_transaction_header_id
36 AND update_purchasing_docs IN ('Y','P') ;
37 SELECT meaning
38 INTO l_process_code
39 FROM fnd_lookups
40 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
41 AND lookup_code ='UPDTPO';
42 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
43 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
44 fnd_message.set_token('PROCESS_CODE', l_process_code);
45 fnd_msg_pub.add;
46 END IF;
47 EXCEPTION WHEN OTHERS THEN
48 NULL;
49 END;
50
51 BEGIN
52 SELECT DISTINCT 'Y' INTO l_flag
53 FROM dpp_transaction_lines_all dtla
54 WHERE transaction_header_id = p_transaction_header_id
55 AND notify_purchasing_docs IN ('Y','P') ;
56 SELECT meaning
57 INTO l_process_code
58 FROM fnd_lookups
59 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
60 AND lookup_code ='NTFYPO';
61 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
62 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
63 fnd_message.set_token('PROCESS_CODE', l_process_code);
64 fnd_msg_pub.add;
65 END IF;
66 EXCEPTION WHEN OTHERS THEN
67 NULL;
68 END;
69
70 BEGIN
71 SELECT DISTINCT 'Y' INTO l_flag
72 FROM dpp_transaction_lines_all dtla
73 WHERE transaction_header_id = p_transaction_header_id
74 AND update_inventory_costing IN ('Y','P') ;
75 SELECT meaning
76 INTO l_process_code
77 FROM fnd_lookups
78 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
79 AND lookup_code ='INVC';
80 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
81 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
82 fnd_message.set_token('PROCESS_CODE', l_process_code);
83 fnd_msg_pub.add;
84 END IF;
85 EXCEPTION WHEN OTHERS THEN
86 NULL;
87 END;
88
89
90 BEGIN
91 SELECT DISTINCT 'Y' INTO l_flag
92 FROM dpp_transaction_lines_all dtla
93 WHERE transaction_header_id = p_transaction_header_id
94 AND update_item_list_price IN ('Y','P') ;
95 SELECT meaning
96 INTO l_process_code
97 FROM fnd_lookups
98 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
99 AND lookup_code ='UPDTLP';
100 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
101 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
102 fnd_message.set_token('PROCESS_CODE', l_process_code);
103 fnd_msg_pub.add;
104 END IF;
105 EXCEPTION WHEN OTHERS THEN
106 NULL;
107 END;
108
109 BEGIN
110 SELECT DISTINCT 'Y' INTO l_flag
111 FROM dpp_transaction_lines_all dtla
112 WHERE transaction_header_id = p_transaction_header_id
113 AND notify_inbound_pricelist IN ('Y','P') ;
114 SELECT meaning
115 INTO l_process_code
116 FROM fnd_lookups
117 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
118 AND lookup_code ='INPL';
119 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
120 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
121 fnd_message.set_token('PROCESS_CODE', l_process_code);
122 fnd_msg_pub.add;
123 END IF;
124 EXCEPTION WHEN OTHERS THEN
125 NULL;
126 END;
127
128 BEGIN
129 SELECT DISTINCT 'Y' INTO l_flag
130 FROM dpp_transaction_lines_all dtla
131 WHERE transaction_header_id = p_transaction_header_id
132 AND notify_outbound_pricelist IN ('Y','P') ;
133 SELECT meaning
134 INTO l_process_code
135 FROM fnd_lookups
136 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
137 AND lookup_code ='OUTPL';
138 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
139 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
140 fnd_message.set_token('PROCESS_CODE', l_process_code);
141 fnd_msg_pub.add;
142 END IF;
143 EXCEPTION WHEN OTHERS THEN
144 NULL;
145 END;
146
147 BEGIN
148 SELECT DISTINCT 'Y' INTO l_flag
149 FROM dpp_transaction_lines_all dtla
150 WHERE transaction_header_id = p_transaction_header_id
151 AND notify_promotions_pricelist IN ('Y','P') ;
152 SELECT meaning
153 INTO l_process_code
154 FROM fnd_lookups
155 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
156 AND lookup_code ='PROMO';
157 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
158 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
159 fnd_message.set_token('PROCESS_CODE', l_process_code);
160 fnd_msg_pub.add;
161 END IF;
162 EXCEPTION WHEN OTHERS THEN
163 NULL;
164 END;
165 BEGIN
166 SELECT DISTINCT 'Y' INTO l_flag
167 FROM dpp_transaction_lines_all dtla
168 WHERE transaction_header_id = p_transaction_header_id
169 AND supp_dist_claim_status IN ('Y','P') ;
170 SELECT meaning
171 INTO l_process_code
172 FROM fnd_lookups
173 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
174 AND lookup_code ='DSTRINVCL';
175 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
176 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
177 fnd_message.set_token('PROCESS_CODE', l_process_code);
178 fnd_msg_pub.add;
179 END IF;
180 EXCEPTION WHEN OTHERS THEN
181 NULL;
182 END;
183
184 BEGIN
185 SELECT DISTINCT 'Y' INTO l_flag
186 FROM dpp_transaction_lines_all dtla
187 WHERE transaction_header_id = p_transaction_header_id
188 AND supp_dist_claim_status IN ('Y','P') ;
189 SELECT meaning
190 INTO l_process_code
191 FROM fnd_lookups
192 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
193 AND lookup_code ='CUSTCL';
194 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
195 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_REQUESTED');
196 fnd_message.set_token('PROCESS_CODE', l_process_code);
197 fnd_msg_pub.add;
198 END IF;
199 EXCEPTION WHEN OTHERS THEN
200 NULL;
201 END;
202
203 IF l_flag = 'Y' THEN
204 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
205 fnd_message.set_name('DPP', 'DPP_TXN_NOT_CANCELLED');
206 fnd_msg_pub.add;
207 END IF;
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 ELSIF p_status_change = 'CLOSED' THEN
212 BEGIN
213 SELECT DISTINCT 'Y' INTO l_flag
214 FROM dpp_transaction_lines_all dtla
215 WHERE transaction_header_id = p_transaction_header_id
216 AND update_purchasing_docs IN ('N','P') ;
217 SELECT meaning
218 INTO l_process_code
219 FROM fnd_lookups
220 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
221 AND lookup_code ='UPDTPO';
222 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
223 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
224 fnd_message.set_token('PROCESS_CODE', l_process_code);
225 fnd_msg_pub.add;
226 END IF;
227 EXCEPTION WHEN OTHERS THEN
228 NULL;
229 END;
230 BEGIN
231 SELECT DISTINCT 'Y' INTO l_flag
232 FROM dpp_transaction_lines_all dtla
233 WHERE transaction_header_id = p_transaction_header_id
234 AND notify_purchasing_docs IN ('N','P') ;
235 SELECT meaning
236 INTO l_process_code
237 FROM fnd_lookups
238 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
239 AND lookup_code ='NTFYPO';
240 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
241 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
242 fnd_message.set_token('PROCESS_CODE', l_process_code);
243 fnd_msg_pub.add;
244 END IF;
245 EXCEPTION WHEN OTHERS THEN
246 NULL;
247 END;
248
249
250 BEGIN
251 SELECT DISTINCT 'Y' INTO l_flag
252 FROM dpp_transaction_lines_all dtla
253 WHERE transaction_header_id = p_transaction_header_id
254 AND update_inventory_costing IN ('N','P') ;
255 SELECT meaning
256 INTO l_process_code
257 FROM fnd_lookups
258 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
259 AND lookup_code ='INVC';
260 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
261 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
262 fnd_message.set_token('PROCESS_CODE', l_process_code);
263 fnd_msg_pub.add;
264 END IF;
265 EXCEPTION WHEN OTHERS THEN
266 NULL;
267 END;
268
269 BEGIN
270 SELECT DISTINCT 'Y' INTO l_flag
271 FROM dpp_transaction_lines_all dtla
272 WHERE transaction_header_id = p_transaction_header_id
273 AND update_item_list_price IN ('N','P') ;
274 SELECT meaning
275 INTO l_process_code
276 FROM fnd_lookups
277 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
278 AND lookup_code ='UPDTLP';
279 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
280 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
281 fnd_message.set_token('PROCESS_CODE', l_process_code);
282 fnd_msg_pub.add;
283 END IF;
284 EXCEPTION WHEN OTHERS THEN
285 NULL;
286 END;
287
288 BEGIN
289 SELECT DISTINCT 'Y' INTO l_flag
290 FROM dpp_transaction_lines_all dtla
291 WHERE transaction_header_id = p_transaction_header_id
292 AND notify_inbound_pricelist IN ('N','P') ;
293 SELECT meaning
294 INTO l_process_code
295 FROM fnd_lookups
296 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
297 AND lookup_code ='INPL';
298 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
299 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
300 fnd_message.set_token('PROCESS_CODE', l_process_code);
301 fnd_msg_pub.add;
302 END IF;
303 EXCEPTION WHEN OTHERS THEN
304 NULL;
305 END;
306
307 BEGIN
308 SELECT DISTINCT 'Y' INTO l_flag
309 FROM dpp_transaction_lines_all dtla
310 WHERE transaction_header_id = p_transaction_header_id
311 AND notify_outbound_pricelist IN ('N','P') ;
312 SELECT meaning
313 INTO l_process_code
314 FROM fnd_lookups
315 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
316 AND lookup_code ='OUTPL';
317 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
318 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
319 fnd_message.set_token('PROCESS_CODE', l_process_code);
320 fnd_msg_pub.add;
321 END IF;
322 EXCEPTION WHEN OTHERS THEN
323 NULL;
324 END;
325
326 BEGIN
327 SELECT DISTINCT 'Y' INTO l_flag
328 FROM dpp_transaction_lines_all dtla
329 WHERE transaction_header_id = p_transaction_header_id
330 AND notify_promotions_pricelist IN ('N','P') ;
331 SELECT meaning
332 INTO l_process_code
333 FROM fnd_lookups
334 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
335 AND lookup_code ='PROMO';
336 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
337 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
338 fnd_message.set_token('PROCESS_CODE', l_process_code);
339 fnd_msg_pub.add;
340 END IF;
341 EXCEPTION WHEN OTHERS THEN
342 NULL;
343 END;
344
345 BEGIN
346 SELECT DISTINCT 'Y' INTO l_flag
347 FROM dpp_transaction_lines_all dtla
348 WHERE transaction_header_id = p_transaction_header_id
349 AND supp_dist_claim_status IN ('N','P','D') ;
350 SELECT meaning
351 INTO l_process_code
352 FROM fnd_lookups
353 WHERE lookup_type LIKE 'DPP_EXECUTION_PROCESSES%'
354 AND lookup_code ='DSTRINVCL';
355 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
356 fnd_message.set_name('DPP', 'DPP_EXE_PROCESS_INCOMPLETE');
357 fnd_message.set_token('PROCESS_CODE', l_process_code);
358 fnd_msg_pub.add;
359 END IF;
360 EXCEPTION WHEN OTHERS THEN
361 NULL;
362 END;
363
364 FOR select_claims_rec IN select_claims_csr(p_transaction_header_id)
365 LOOP
366 l_flag := 'Y';
367 fnd_message.set_name('DPP', 'DPP_CLAIM_NOT_CLOSED');
368 fnd_message.set_token('CLAIM_NUMBER',select_claims_rec.claim_number );
369 fnd_msg_pub.add;
370 END LOOP;
371 END IF;
372 IF l_flag = 'Y' THEN
373 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
374 fnd_message.set_name('DPP', 'DPP_TXN_NOT_CLOSED');
375 fnd_msg_pub.add;
376 END IF;
377 RAISE FND_API.G_EXC_ERROR;
378 END IF;
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 x_return_status := FND_API.G_RET_STS_ERROR;
382
383 WHEN OTHERS THEN
384 x_return_status := fnd_api.g_ret_sts_unexp_error;
385
386 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
387 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
388 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.check_txnclose');
389 fnd_message.set_token('ERRNO', sqlcode);
390 fnd_message.set_token('REASON', sqlerrm);
391 FND_MSG_PUB.ADD;
392 END IF;
393 END Check_Transaction;
394
395 PROCEDURE search_vendors(
396 p_search_criteria IN search_criteria_tbl_type
397 ,x_vendor_tbl OUT NOCOPY vendor_tbl_type
398 ,x_rec_count OUT NOCOPY NUMBER
399 ,x_return_status OUT NOCOPY VARCHAR2
400 )
401 IS
402 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
403 l_vendor_tbl vendor_tbl_type;
404 l_trunc_sysdate DATE := trunc(sysdate);
405
406 CURSOR get_vendor_csr (p_search_criteria IN VARCHAR2, p_search_text IN VARCHAR2) IS
407 SELECT
408 vendor_id,
409 segment1 vendor_num,
410 vendor_name
411 FROM
412 ap_suppliers pov
416 AND (l_trunc_sysdate >= NVL(TRUNC(start_date_active), l_trunc_sysdate) AND l_trunc_sysdate < NVL(TRUNC(end_date_active), l_trunc_sysdate + 1))
413 WHERE enabled_flag = 'Y'
414 AND hold_flag = 'N'
415 --AND pov.party_id = hzp.party_id
417 -- BETWEEN NVL(start_date_active, l_trunc_sysdate) AND NVL(end_date_active,l_trunc_sysdate)
418 AND ((UPPER(vendor_name) like UPPER(p_search_text) AND p_search_criteria = 'VENDOR_NAME')
419 OR (UPPER(segment1) like UPPER(p_search_text) AND p_search_criteria = 'VENDOR_NUMBER'))
420 ORDER BY vendor_name,segment1;
421
422 BEGIN
423
424 OPEN get_vendor_csr(NVL(l_search_criteria_tbl(1).search_criteria,'VENDOR_NAME'), NVL(l_search_criteria_tbl(1).search_text,'%'));
425 LOOP
426
427 FETCH get_vendor_csr BULK COLLECT INTO l_vendor_tbl;
428 EXIT WHEN get_vendor_csr%NOTFOUND;
429 END LOOP;
430
431 CLOSE get_vendor_csr;
432 x_rec_count := l_vendor_tbl.COUNT;
433 x_vendor_tbl := l_vendor_tbl;
434 x_return_status := fnd_api.g_ret_sts_success;
435
436 IF DPP_DEBUG_HIGH_ON THEN
437 DPP_UTILITY_PVT.debug_message ('search_vendors(): x_return_status: ' || x_return_status);
438 END IF;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 x_return_status := fnd_api.g_ret_sts_unexp_error;
443
444 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
445 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
446 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_vendors');
447 fnd_message.set_token('ERRNO', sqlcode);
448 fnd_message.set_token('REASON', sqlerrm);
449 FND_MSG_PUB.ADD;
450 END IF;
451
452 END search_vendors;
453
454 PROCEDURE search_vendor_sites(
455 p_search_criteria IN search_criteria_tbl_type
456 ,x_vendor_site_tbl OUT NOCOPY vendor_site_tbl_type
457 ,x_rec_count OUT NOCOPY NUMBER
458 ,x_return_status OUT NOCOPY VARCHAR2
459 )
460 IS
461 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
462 l_vendor_site_tbl vendor_site_tbl_type;
463
464
465 CURSOR get_vendor_sites_csr (p_search_criteria1 IN VARCHAR2,
466 p_search_text1 IN VARCHAR2,
467 p_search_criteria2 IN VARCHAR2,
468 p_search_text2 IN VARCHAR2,
469 p_search_criteria3 IN VARCHAR2,
470 p_search_text3 IN VARCHAR2) IS
471 SELECT apssa.vendor_id,
472 apssa.vendor_site_id,
473 apssa.vendor_site_code,
474 apssa.address_line1,
475 apssa.address_line2,
476 apssa.address_line3,
477 apssa.city,
478 apssa.state,
479 apssa.zip,
480 apssa.country
481 FROM ap_supplier_sites_all apssa,
482 ozf_supp_trd_prfls_all ostp
483 WHERE apssa.vendor_id = to_NUMBER(p_search_text1)
484 AND ostp.supplier_id = apssa.vendor_id
485 AND ostp.supplier_site_id = apssa.vendor_site_id
486 AND ostp.org_id = apssa.org_id
487 AND p_search_criteria1 = 'VENDOR_ID'
488 AND nvl(apssa.rfq_only_site_flag, 'N') ='N'
489 AND NVL(apssa.inactive_date, TRUNC(SYSDATE +1)) > TRUNC(SYSDATE)
490 AND UPPER(apssa.vendor_site_code) like UPPER(p_search_text2)
491 AND p_search_criteria2 = 'VENDOR_SITE_CODE'
492 AND apssa.org_id = to_NUMBER(p_search_text3)
493 AND p_search_criteria3 = 'ORG_ID'
494 ORDER BY apssa.vendor_site_code;
495 BEGIN
496
497 x_rec_count := 0;
498
499 IF l_search_criteria_tbl(1).search_text IS NULL THEN
500 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
501 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_VENDORID');
502 fnd_msg_pub.add;
503 END IF;
504 RAISE FND_API.G_EXC_ERROR;
505
506 ELSIF l_search_criteria_tbl(3).search_text IS NULL THEN
507 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
508 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_ORGID');
509 fnd_msg_pub.add;
510 END IF;
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513
514 OPEN get_vendor_sites_csr(NVL(l_search_criteria_tbl(1).search_criteria,'VENDOR_ID'), l_search_criteria_tbl(1).search_text,
515 NVL(l_search_criteria_tbl(2).search_criteria,'VENDOR_SITE_CODE'), NVL(l_search_criteria_tbl(2).search_text,'%'),
516 NVL(l_search_criteria_tbl(3).search_criteria,'ORG_ID'), l_search_criteria_tbl(3).search_text);
517 LOOP
518
519 FETCH get_vendor_sites_csr BULK COLLECT INTO l_vendor_site_tbl;
520 EXIT WHEN get_vendor_sites_csr%NOTFOUND;
521 END LOOP;
522 CLOSE get_vendor_sites_csr;
523
524 x_rec_count := l_vendor_site_tbl.COUNT;
525 x_vendor_site_tbl := l_vendor_site_tbl;
526 x_return_status := fnd_api.g_ret_sts_success;
527 IF DPP_DEBUG_HIGH_ON THEN
528 DPP_UTILITY_PVT.debug_message ('search_vendor_sites(): x_return_status: ' || x_return_status);
529 END IF;
530
531 EXCEPTION
532
533 WHEN FND_API.G_EXC_ERROR THEN
534 x_return_status := FND_API.G_RET_STS_ERROR;
538
535
536 WHEN OTHERS THEN
537 x_return_status := fnd_api.g_ret_sts_unexp_error;
539 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
540 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
541 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_vendor_sites');
542 fnd_message.set_token('ERRNO', sqlcode);
543 fnd_message.set_token('REASON', sqlerrm);
544 FND_MSG_PUB.ADD;
545 END IF;
546
547 END search_vendor_sites;
548
549 PROCEDURE search_vendor_contacts(
550 p_search_criteria IN search_criteria_tbl_type
551 ,x_vendor_contact_tbl OUT NOCOPY vendor_contact_tbl_type
552 ,x_rec_count OUT NOCOPY NUMBER
553 ,x_return_status OUT NOCOPY VARCHAR2
554 )
555 IS
556 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
557 l_vendor_contact_tbl vendor_contact_tbl_type;
558
559
560 CURSOR get_vendor_contacts_csr (p_search_criteria1 IN VARCHAR2, p_search_text1 IN VARCHAR2,
561 p_search_criteria2 IN VARCHAR2, p_search_text2 IN VARCHAR2) IS
562 SELECT assa.vendor_site_id,
563 poc.vendor_contact_id,
564 poc.first_name,
565 poc.middle_name,
566 poc.last_name,
567 poc.area_code
568 ||poc.phone phone,
569 poc.email_address,
570 poc.fax
571 FROM po_vendor_contacts poc,
572 ap_supplier_sites_all assa
573 WHERE assa.vendor_site_id = TO_NUMBER(p_search_text1)
574 AND assa.party_site_id = poc.org_party_site_id
575 AND assa.vendor_site_id = poc.vendor_site_id
576 AND p_search_criteria1 = 'VENDOR_SITE_ID'
577 AND NVL(poc.inactive_date,SYSDATE + 1) > SYSDATE
578 AND ((UPPER(first_name) LIKE UPPER(p_search_text2)
579 AND p_search_criteria2 = 'FIRST_NAME')
580 OR (UPPER(last_name) LIKE UPPER(p_search_text2)
581 AND p_search_criteria2 = 'LAST_NAME')
582 OR (UPPER(middle_name) LIKE UPPER(p_search_text2)
583 AND p_search_criteria2 = 'MIDDLE_NAME'))
584 ORDER BY poc.last_name,poc.first_name,poc.middle_name;
585
586 BEGIN
587
588 x_rec_count := 0;
589
590 IF l_search_criteria_tbl(1).search_text IS NULL THEN
591 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
592 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_VENDORSITEID');
593 fnd_msg_pub.add;
594 END IF;
595
596 RAISE fnd_api.g_exc_error;
597 END IF;
598
599 OPEN get_vendor_contacts_csr(NVL(l_search_criteria_tbl(1).search_criteria,'VENDOR_SITE_ID'), l_search_criteria_tbl(1).search_text,
600 NVL(l_search_criteria_tbl(2).search_criteria,'FIRST_NAME'), NVL(l_search_criteria_tbl(2).search_text,'%'));
601 LOOP
602 FETCH get_vendor_contacts_csr BULK COLLECT INTO l_vendor_contact_tbl;
603 EXIT WHEN get_vendor_contacts_csr%NOTFOUND;
604 END LOOP;
605 CLOSE get_vendor_contacts_csr;
606
607 x_rec_count := l_vendor_contact_tbl.COUNT;
608 x_vendor_contact_tbl := l_vendor_contact_tbl;
609 x_return_status := fnd_api.g_ret_sts_success;
610 IF DPP_DEBUG_HIGH_ON THEN
611 DPP_UTILITY_PVT.debug_message ('search_vendor_contacts(): x_return_status: ' || x_return_status);
612 END IF;
613
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 x_return_status := FND_API.G_RET_STS_ERROR;
617
618 WHEN OTHERS THEN
619 x_return_status := fnd_api.g_ret_sts_unexp_error;
620
621 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
622 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
623 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_vendor_contacts');
624 fnd_message.set_token('ERRNO', sqlcode);
625 fnd_message.set_token('REASON', sqlerrm);
626 FND_MSG_PUB.ADD;
627 END IF;
628
629 END search_vendor_contacts;
630
631 PROCEDURE search_items(
632 p_search_criteria IN search_criteria_tbl_type
633 ,x_item_tbl OUT NOCOPY itemnum_tbl_type
634 ,x_rec_count OUT NOCOPY NUMBER
635 ,x_return_status OUT NOCOPY VARCHAR2
636 )
637 IS
638 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
639 l_itemnum_tbl itemnum_tbl_type;
640 l_supp_trade_profile_id NUMBER;
641 l_supp_item_count NUMBER := 0;
642 l_trunc_sysdate DATE := trunc(sysdate);
643
644 CURSOR get_item_csr (p_search_criteria1 IN VARCHAR2,
645 p_search_text1 IN VARCHAR2,
646 p_search_criteria2 IN VARCHAR2,
647 p_search_text2 IN VARCHAR2,
648 p_supp_trade_profile_id IN NUMBER) IS
649 SELECT msi.inventory_item_id,
650 msi.concatenated_segments,
651 msi.description,
652 occ.external_code
656 WHERE occ.internal_code = to_char(msi.inventory_item_id)
653 FROM mtl_system_items_kfv msi,
654 financials_system_params_all fspa,
655 ozf_supp_code_conversions_all occ
657 AND occ.code_conversion_type = 'OZF_PRODUCT_CODES'
658 AND occ.supp_trade_profile_id = p_supp_trade_profile_id
659 AND occ.org_id = fspa.org_id
660 AND TRUNC(SYSDATE) BETWEEN NVL(occ.start_date_active,TRUNC(SYSDATE)) AND
661 NVL(occ.end_date_active,TRUNC(SYSDATE))
662 AND msi.purchasing_item_flag = 'Y'
663 AND msi.shippable_item_flag = 'Y'
664 AND msi.enabled_flag = 'Y'
665 AND nvl(msi.consigned_flag,2) = 2 -- 2=unconsigned
666 AND msi.mtl_transactions_enabled_flag = 'Y'
667 AND msi.organization_id = fspa.inventory_organization_id
668 AND fspa.org_id = to_number(p_search_text1)
669 AND p_search_criteria1 = 'ORG_ID'
670 AND ((p_search_criteria2 = 'ITEM_NUMBER'
671 AND UPPER(msi.concatenated_segments) LIKE UPPER(p_search_text2))
672 OR (p_search_criteria2 = 'SUPPLIER_ITEM_NUMBER'
673 AND UPPER(occ.external_code) LIKE UPPER(p_search_text2)))
674 UNION
675 SELECT msi.inventory_item_id,
676 msi.concatenated_segments,
677 msi.description,
678 null external_code
679 FROM mtl_system_items_kfv msi,
680 financials_system_params_all fspa
681 WHERE msi.purchasing_item_flag = 'Y'
682 AND msi.shippable_item_flag = 'Y'
683 AND msi.enabled_flag = 'Y'
684 AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
685 AND msi.mtl_transactions_enabled_flag = 'Y'
686 AND msi.organization_id = fspa.inventory_organization_id
687 AND fspa.org_id = to_number(p_search_text1)
688 AND p_search_criteria1 = 'ORG_ID'
689 AND p_search_criteria2 = 'ITEM_NUMBER'
690 AND UPPER(msi.concatenated_segments) LIKE UPPER(p_search_text2)
691 AND NOT EXISTS (SELECT 1
692 FROM ozf_supp_code_conversions_all occ
693 WHERE occ.internal_code = to_char(msi.inventory_item_id)
694 AND occ.code_conversion_type = 'OZF_PRODUCT_CODES'
695 AND occ.supp_trade_profile_id = p_supp_trade_profile_id
696 AND occ.org_id = to_number(p_search_text1)
697 AND TRUNC(SYSDATE) BETWEEN NVL(occ.start_date_active,TRUNC(SYSDATE)) AND
698 NVL(occ.end_date_active,TRUNC(SYSDATE)))
699 ORDER BY 2,4;
700
701 CURSOR get_msi_item_csr (p_search_criteria1 IN VARCHAR2,
702 p_search_text1 IN VARCHAR2,
703 p_search_criteria2 IN VARCHAR2,
704 p_search_text2 IN VARCHAR2) IS
705 SELECT DISTINCT msi.inventory_item_id,
706 msi.concatenated_segments,
707 msi.description,
708 NULL external_code
709 FROM mtl_system_items_kfv msi,
710 financials_system_params_all fspa
711 WHERE msi.organization_id = fspa.inventory_organization_id
712 AND msi.purchasing_item_flag = 'Y'
713 AND msi.shippable_item_flag = 'Y'
714 AND msi.enabled_flag = 'Y'
715 AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
716 AND msi.mtl_transactions_enabled_flag = 'Y'
717 AND fspa.org_id = TO_NUMBER(p_search_text1)
718 AND p_search_criteria1 = 'ORG_ID'
719 AND p_search_criteria2 = 'ITEM_NUMBER'
720 AND UPPER(msi.concatenated_segments) like UPPER(p_search_text2)
721 ORDER BY msi.concatenated_segments;
722
723 BEGIN
724
725 x_rec_count := 0;
726
727 IF l_search_criteria_tbl(1).search_text IS NULL THEN
728 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
729 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_ORGID');
730 fnd_msg_pub.add;
731 END IF;
732
733 RAISE fnd_api.g_exc_error;
734 ELSIF l_search_criteria_tbl(3).search_text IS NULL THEN
735 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
736 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_VENDORID');
737 fnd_msg_pub.add;
738 END IF;
739 RAISE fnd_api.g_exc_error;
740 ELSIF l_search_criteria_tbl(4).search_text IS NULL THEN
741 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
742 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_VENDORSITEID');
743 fnd_msg_pub.add;
744 END IF;
745 RAISE fnd_api.g_exc_error;
746 END IF;
747
748 BEGIN
749 SELECT supp_trade_profile_id
750 INTO l_supp_trade_profile_id
751 FROM ozf_supp_trd_prfls_all
752 WHERE supplier_id = to_number(l_search_criteria_tbl(3).search_text)
753 AND supplier_site_id = to_number(l_search_criteria_tbl(4).search_text)
754 AND org_id = l_search_criteria_tbl(1).search_text;
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 l_supp_trade_profile_id := NULL;
758 END;
759
760 BEGIN
761 SELECT count(*)
762 INTO l_supp_item_count
766 END;
763 FROM ozf_supp_code_conversions_all
764 WHERE supp_trade_profile_id = l_supp_trade_profile_id;
765
767
768 IF l_supp_trade_profile_id IS NOT NULL THEN
769
770 BEGIN
771 SELECT count(*)
772 INTO l_supp_item_count
773 FROM ozf_supp_code_conversions_all
774 WHERE supp_trade_profile_id = l_supp_trade_profile_id;
775
776 END;
777
778 IF l_supp_item_count > 0 THEN
779 -- select from msi and occ (get_item_csr)
780 OPEN get_item_csr(NVL(l_search_criteria_tbl(1).search_criteria,'ORG_ID'),
781 l_search_criteria_tbl(1).search_text,
782 NVL(l_search_criteria_tbl(2).search_criteria,'ITEM_NUMBER'),
783 l_search_criteria_tbl(2).search_text,
784 l_supp_trade_profile_id);
785 LOOP
786
787 FETCH get_item_csr BULK COLLECT INTO l_itemnum_tbl;
788 EXIT WHEN get_item_csr%NOTFOUND;
789 END LOOP;
790 CLOSE get_item_csr;
791
792 ELSE -- l_supp_item_count = 0
793 -- select only from msi (get_msi_item_csr)
794 OPEN get_msi_item_csr(NVL(l_search_criteria_tbl(1).search_criteria,'ORG_ID'),
795 l_search_criteria_tbl(1).search_text,
796 NVL(l_search_criteria_tbl(2).search_criteria,'ITEM_NUMBER'),
797 l_search_criteria_tbl(2).search_text);
798 LOOP
799
800 FETCH get_msi_item_csr BULK COLLECT INTO l_itemnum_tbl;
801 EXIT WHEN get_msi_item_csr%NOTFOUND;
802 END LOOP;
803 CLOSE get_msi_item_csr;
804
805 END IF;
806
807 ELSE
808
809 -- select only from msi (get_msi_item_csr)
810 OPEN get_msi_item_csr(NVL(l_search_criteria_tbl(1).search_criteria,'ORG_ID'),
811 l_search_criteria_tbl(1).search_text,
812 NVL(l_search_criteria_tbl(2).search_criteria,'ITEM_NUMBER'),
813 l_search_criteria_tbl(2).search_text);
814 LOOP
815
816 FETCH get_msi_item_csr BULK COLLECT INTO l_itemnum_tbl;
817 EXIT WHEN get_msi_item_csr%NOTFOUND;
818 END LOOP;
819 CLOSE get_msi_item_csr;
820
821 END IF;
822
823 x_rec_count := l_itemnum_tbl.COUNT;
824 x_item_tbl := l_itemnum_tbl;
825 x_return_status := fnd_api.g_ret_sts_success;
826 IF DPP_DEBUG_HIGH_ON THEN
827 DPP_UTILITY_PVT.debug_message ('search_items(): x_return_status: ' || x_return_status);
828 END IF;
829
830 EXCEPTION
831
832 WHEN FND_API.G_EXC_ERROR THEN
833 x_return_status := FND_API.G_RET_STS_ERROR;
834
835 WHEN OTHERS THEN
836 x_return_status := fnd_api.g_ret_sts_unexp_error;
837
838 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
839 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
840 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_items');
841 fnd_message.set_token('ERRNO', sqlcode);
842 fnd_message.set_token('REASON', sqlerrm);
843 FND_MSG_PUB.ADD;
844 END IF;
845
846 END search_items;
847
848 PROCEDURE search_customer_items(
849 p_search_criteria IN search_criteria_tbl_type
850 ,x_customer_item_tbl OUT NOCOPY item_tbl_type
851 ,x_rec_count OUT NOCOPY NUMBER
852 ,x_return_status OUT NOCOPY VARCHAR2
853 )
854 IS
855 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
856 l_customer_item_tbl item_tbl_type;
857
858 CURSOR get_customer_item_csr (p_search_criteria1 IN VARCHAR2, p_search_text1 IN VARCHAR2,
859 p_search_criteria2 IN VARCHAR2, p_search_text2 IN VARCHAR2,
860 p_search_criteria3 IN VARCHAR2, p_search_text3 IN VARCHAR2) IS
861 SELECT DISTINCT
862 msi.inventory_item_id,
863 msi.concatenated_segments,
864 msi.description
865 FROM
866 mtl_system_items_kfv msi,
867 mtl_parameters mp,
868 financials_system_params_all fspa,
869 oe_order_lines_all oola,
870 dpp_transaction_lines_all dtla
871 WHERE
872 dtla.transaction_header_id = TO_NUMBER(p_search_text1) and
873 p_search_criteria1 = 'TRANSACTION_HEADER_ID' AND
874 oola.inventory_item_id = dtla.inventory_item_id and
875 (dtla.prior_price - NVL(dtla.supplier_new_price,0)) > 0 and
876 dtla.org_id = oola.org_id and
877 purchasing_item_flag = 'Y' AND
878 shippable_item_flag = 'Y' AND
879 msi.mtl_transactions_enabled_flag = 'Y' AND
880 msi.organization_id = mp.organization_id AND
881 mp.organization_id = fspa.inventory_organization_id AND
882 UPPER(segment1) like UPPER(p_search_text2) AND
883 p_search_criteria2 = 'ITEM_NUMBER' AND
884 oola.org_id = fspa.org_id AND
885 oola.inventory_item_id = msi.inventory_item_id AND
886 oola.sold_to_org_id = to_number(p_search_text3) AND
887 p_search_criteria3 = 'CUST_ACCOUNT_ID'
888 ORDER BY msi.concatenated_segments;
889
890 BEGIN
891 x_rec_count := 0;
892
893 IF l_search_criteria_tbl(1).search_text IS NULL THEN
894 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
895 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_TRANSACTIONID');
899 RAISE fnd_api.g_exc_error;
896 fnd_msg_pub.add;
897 END IF;
898
900 ELSIF l_search_criteria_tbl(3).search_text IS NULL THEN
901 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
902 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_CUST_ACCT_ID');
903 fnd_msg_pub.add;
904 END IF;
905 RAISE fnd_api.g_exc_error;
906 END IF;
907
908 OPEN get_customer_item_csr(NVL(l_search_criteria_tbl(1).search_criteria,'TRANSACTION_HEADER_ID'),
909 l_search_criteria_tbl(1).search_text,
910 NVL(l_search_criteria_tbl(2).search_criteria,'ITEM_NUMBER'),
911 NVL(l_search_criteria_tbl(2).search_text,'%'),
912 l_search_criteria_tbl(3).search_criteria,
913 NVL(l_search_criteria_tbl(3).search_text,'CUST_ACCOUNT_ID'));
914 LOOP
915
916 FETCH get_customer_item_csr BULK COLLECT INTO l_customer_item_tbl;
917 EXIT WHEN get_customer_item_csr%NOTFOUND;
918 END LOOP;
919 CLOSE get_customer_item_csr;
920
921 x_rec_count := l_customer_item_tbl.COUNT;
922 x_customer_item_tbl := l_customer_item_tbl;
923 x_return_status := fnd_api.g_ret_sts_success;
924 IF DPP_DEBUG_HIGH_ON THEN
925 DPP_UTILITY_PVT.debug_message ('search_customer_items(): x_return_status: ' || x_return_status);
926 END IF;
927
928 EXCEPTION
929 WHEN FND_API.G_EXC_ERROR THEN
930 x_return_status := FND_API.G_RET_STS_ERROR;
931
932 WHEN OTHERS THEN
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934
935 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
936 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
937 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_customer_items');
938 fnd_message.set_token('ERRNO', sqlcode);
939 fnd_message.set_token('REASON', sqlerrm);
940 FND_MSG_PUB.ADD;
941 END IF;
942
943 END search_customer_items;
944
945 PROCEDURE search_warehouses(
946 p_search_criteria IN search_criteria_tbl_type
947 ,x_warehouse_tbl OUT NOCOPY warehouse_tbl_type
948 ,x_rec_count OUT NOCOPY NUMBER
949 ,x_return_status OUT NOCOPY VARCHAR2
950 )
951 IS
952 cursor get_warehouse_csr(p_search_criteria1 IN VARCHAR2, p_search_text1 IN VARCHAR2,
953 p_search_criteria2 IN VARCHAR2, p_search_text2 IN VARCHAR2) is
954 SELECT
955 ood.organization_id warehouse_id,
956 ood.organization_code warehouse_code,
957 ood.organization_name warehouse_name
958 FROM
959 org_organization_definitions ood
960 WHERE
961 operating_unit = to_number(p_search_text2)
962 AND p_search_criteria2 = 'ORG_ID'
963 AND ((ood.organization_code LIKE p_search_text1
964 AND p_search_criteria1 = 'WAREHOUSE_CODE') OR
965 (ood.organization_name LIKE p_search_text1
966 AND p_search_criteria1 = 'WAREHOUSE_NAME'))
967 AND NVL(ood.disable_date,SYSDATE+1) > SYSDATE
968 ORDER BY ood.organization_name;
969
970 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
971 l_warehouse_tbl warehouse_tbl_type;
972
973 BEGIN
974 x_rec_count := 0;
975 x_return_status := fnd_api.g_ret_sts_success;
976 IF l_search_criteria_tbl(2).search_text IS NULL THEN
977 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
978 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_ORGID');
979 fnd_msg_pub.add;
980 END IF;
981
982 RAISE FND_API.G_EXC_ERROR;
983 END IF;
984 OPEN get_warehouse_csr(NVL(l_search_criteria_tbl(1).search_criteria,'WAREHOUSE_CODE'),
985 NVL(l_search_criteria_tbl(1).search_text,'%'),
986 NVL(l_search_criteria_tbl(2).search_criteria,'ORG_ID'),
987 l_search_criteria_tbl(2).search_text);
988 LOOP
989
990 FETCH get_warehouse_csr BULK COLLECT INTO l_warehouse_tbl;
991 EXIT WHEN get_warehouse_csr%NOTFOUND;
992 END LOOP;
993 CLOSE get_warehouse_csr;
994
995 x_rec_count := l_warehouse_tbl.COUNT;
996 x_warehouse_tbl := l_warehouse_tbl;
997
998 IF DPP_DEBUG_HIGH_ON THEN
999 DPP_UTILITY_PVT.debug_message ('search_warehouses(): x_return_status: ' || x_return_status);
1000 END IF;
1001
1002 EXCEPTION
1003 WHEN FND_API.G_EXC_ERROR THEN
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005
1006 WHEN OTHERS THEN
1007 x_return_status := fnd_api.g_ret_sts_unexp_error;
1008
1009 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1010 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1011 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_warehouses');
1012 fnd_message.set_token('ERRNO', sqlcode);
1013 fnd_message.set_token('REASON', sqlerrm);
1014 FND_MSG_PUB.ADD;
1015 END IF;
1016 END search_warehouses;
1017
1018 PROCEDURE Get_CoveredInventory(
1019 p_hdr_rec IN dpp_inv_hdr_rec_type
1020 ,p_covered_inv_tbl IN OUT NOCOPY dpp_inv_cov_tbl_type
1021 ,x_return_status OUT NOCOPY VARCHAR2
1022 )
1023 IS
1024 l_hdr_rec dpp_inv_hdr_rec_type:= p_hdr_rec;
1025 l_covered_inv_tbl dpp_inv_cov_tbl_type := p_covered_inv_tbl;
1026 l_covered_inv_wh_tbl dpp_inv_cov_wh_tbl_type;
1030
1027 l_covered_inv_rct_tbl dpp_inv_cov_rct_tbl_type;
1028 l_num_count NUMBER;
1029 l_primary_uom_code VARCHAR2(3);
1031 CURSOR get_covered_inventory_csr (p_org_id IN NUMBER, p_effective_start_date DATE, p_effective_end_date DATE, p_inventory_item_id IN NUMBER) IS
1032 SELECT
1033 sum(case when ( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date)
1034 and (NVL(moqd.orig_date_received,moqd.date_received) < p_effective_start_date))
1035 --BETWEEN p_effective_start_date and p_effective_end_date)
1036 then moqd.transaction_quantity else 0 end) covered_qty,
1037 sum(moqd.transaction_quantity) onhand_qty,
1038 moqd.transaction_uom_code
1039 FROM
1040 mtl_onhand_quantities_detail moqd,
1041 org_organization_definitions ood,
1042 mtl_parameters mp
1043 WHERE
1044 moqd.organization_id = ood.organization_id AND
1045 moqd.inventory_item_id = p_inventory_item_id AND
1046 mp.organization_id = ood.organization_id AND
1047 -- NVL(mp.consigned_flag,'N') = 'N' AND
1048 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
1049 ood.operating_unit = p_org_id AND
1050 moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
1051 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
1052 AND moqd.PLANNING_TP_TYPE = 2
1053 AND moqd.OWNING_TP_TYPE = 2
1054 AND moqd.IS_CONSIGNED = 2
1055 GROUP BY moqd.transaction_uom_code;
1056
1057 cursor get_covered_inv_wh_csr(p_org_id IN NUMBER, p_effective_start_date DATE, p_effective_end_date DATE, p_inventory_item_id IN NUMBER) is
1058 SELECT
1059 SUM(moqd.transaction_quantity) sum,
1060 ood.organization_name warehouse,
1061 ood.organization_id warehouse_id
1062 FROM
1063 mtl_onhand_quantities_detail moqd,
1064 org_organization_definitions ood,
1065 mtl_parameters mp
1066 WHERE
1067 moqd.organization_id = ood.organization_id AND
1068 moqd.inventory_item_id = p_inventory_item_id AND
1069 ood.operating_unit = p_org_id AND
1070 mp.organization_id = ood.organization_id AND
1071 -- NVL(mp.consigned_flag,'N') = 'N' AND
1072 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
1073 ( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date)
1074 AND (NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date ) )
1075 --BETWEEN p_effective_start_date and p_effective_end_date
1076 AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
1077 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
1078 AND moqd.PLANNING_TP_TYPE = 2
1079 AND moqd.OWNING_TP_TYPE = 2
1080 AND moqd.IS_CONSIGNED = 2
1081 GROUP BY ood.organization_name,
1082 ood.organization_id;
1083
1084 cursor get_covered_inv_rct_csr(p_org_id IN NUMBER, p_inventory_item_id IN NUMBER, p_warehouse_id IN NUMBER) is
1085 SELECT
1086 TRUNC(NVL(moqd.orig_date_received,moqd.date_received)) date_received,
1087 SUM(moqd.transaction_quantity) sum
1088 FROM
1089 mtl_onhand_quantities_detail moqd,
1090 org_organization_definitions ood,
1091 mtl_parameters mp
1092 WHERE
1093 moqd.organization_id = ood.organization_id AND
1094 moqd.inventory_item_id = p_inventory_item_id AND
1095 ood.operating_unit = p_org_id AND
1096 mp.organization_id = ood.organization_id AND
1097 -- NVL(mp.consigned_flag,'N') = 'N' AND
1098 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
1099 moqd.organization_id = p_warehouse_id AND
1100 moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
1101 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
1102 AND moqd.PLANNING_TP_TYPE = 2
1103 AND moqd.OWNING_TP_TYPE = 2
1104 AND moqd.IS_CONSIGNED = 2
1105 GROUP BY TRUNC(NVL(moqd.orig_date_received,moqd.date_received));
1106
1107 BEGIN
1108
1109 FOR i in l_covered_inv_tbl.FIRST..l_covered_inv_tbl.LAST
1110 LOOP
1111
1112 FOR get_covered_inventory_rec IN get_covered_inventory_csr(l_hdr_rec.org_id, l_hdr_rec.effective_start_date, l_hdr_rec.effective_end_date, l_covered_inv_tbl(i).Inventory_ITem_ID)
1113 LOOP
1114 l_covered_inv_tbl(i).covered_quantity := NVL(get_covered_inventory_rec.covered_qty,0);
1115 l_covered_inv_tbl(i).onhand_quantity := NVL(get_covered_inventory_rec.onhand_qty,0);
1116 l_covered_inv_tbl(i).uom_code := get_covered_inventory_rec.transaction_uom_code;
1117
1118 l_num_count := 0;
1119
1120 FOR get_covered_inv_wh_rec IN get_covered_inv_wh_csr(l_hdr_rec.org_id, l_hdr_rec.effective_start_date, l_hdr_rec.effective_end_date, l_covered_inv_tbl(i).Inventory_ITem_ID)
1121 LOOP
1122
1123 l_num_count := l_num_count + 1;
1124
1125 l_covered_inv_wh_tbl(l_num_count).warehouse_name := get_covered_inv_wh_rec.warehouse;
1126 l_covered_inv_wh_tbl(l_num_count).warehouse_id := get_covered_inv_wh_rec.warehouse_id;
1127 l_covered_inv_wh_tbl(l_num_count).covered_quantity := NVL(get_covered_inv_wh_rec.sum,0);
1128
1129 OPEN get_covered_inv_rct_csr(l_hdr_rec.org_id, l_covered_inv_tbl(i).Inventory_ITem_ID, get_covered_inv_wh_rec.warehouse_id);
1130 LOOP
1131 FETCH get_covered_inv_rct_csr BULK COLLECT INTO l_covered_inv_rct_tbl;
1132 EXIT WHEN get_covered_inv_rct_csr%NOTFOUND;
1133 END LOOP;
1134 CLOSE get_covered_inv_rct_csr;
1135
1139
1136 l_covered_inv_wh_tbl(l_num_count).rct_line_tbl := l_covered_inv_rct_tbl;
1137
1138 END LOOP;
1140 l_covered_inv_tbl(i).wh_line_tbl := l_covered_inv_wh_tbl;
1141
1142 END LOOP;
1143
1144 IF l_covered_inv_tbl(i).onhand_quantity IS NULL THEN
1145
1146 l_covered_inv_tbl(i).covered_quantity := 0;
1147 l_covered_inv_tbl(i).onhand_quantity := 0;
1148
1149 BEGIN
1150 SELECT primary_uom_code
1151 INTO l_primary_uom_code
1152 FROM mtl_system_items msi,
1153 mtl_parameters mp
1154 WHERE inventory_item_id = l_covered_inv_tbl(i).inventory_item_id
1155 AND mp.organization_id = msi.organization_id
1156 AND mp.organization_id = mp.master_organization_id
1157 AND rownum = 1;
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 DPP_UTILITY_PVT.debug_message ('Error in fetching primary UOM: ' || SQLERRM);
1161 x_return_status := FND_API.G_RET_STS_ERROR;
1162 END;
1163
1164 l_covered_inv_tbl(i).uom_code := l_primary_uom_code; -- Default to Primary UOM
1165 END IF;
1166
1167 END LOOP;
1168
1169 p_covered_inv_tbl := l_covered_inv_tbl;
1170
1171 x_return_status := fnd_api.g_ret_sts_success;
1172
1173 IF DPP_DEBUG_HIGH_ON THEN
1174 DPP_UTILITY_PVT.debug_message ('get_coveredinventory(): x_return_status: ' || x_return_status);
1175 END IF;
1176
1177 EXCEPTION
1178
1179 WHEN OTHERS THEN
1180 x_return_status := fnd_api.g_ret_sts_unexp_error;
1181
1182 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1183 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1184 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_CoveredInventory');
1185 fnd_message.set_token('ERRNO', sqlcode);
1186 fnd_message.set_token('REASON', sqlerrm);
1187 FND_MSG_PUB.ADD;
1188 END IF;
1189
1190 END Get_CoveredInventory;
1191
1192 PROCEDURE Get_InventoryDetails(
1193 p_hdr_rec IN dpp_inv_hdr_rec_type
1194 ,p_inventorydetails_tbl IN OUT NOCOPY inventorydetails_tbl_type
1195 ,x_rec_count OUT NOCOPY NUMBER
1196 ,x_return_status OUT NOCOPY VARCHAR2
1197 )
1198 IS
1199 l_hdr_rec dpp_inv_hdr_rec_type:= p_hdr_rec;
1200 l_inventorydetails_tbl inventorydetails_tbl_type := p_inventorydetails_tbl;
1201
1202 l_inv_details_id NUMBER;
1203 l_user_id NUMBER := FND_GLOBAL.user_id;
1204 l_sysdate DATE := SYSDATE;
1205 l_include_flag VARCHAR2(1);
1206 l_flag VARCHAR2(1);
1207 l_days_out NUMBER;
1208 l_primary_uom_code VARCHAR2(3);
1209
1210 CURSOR get_covered_inventory_csr (p_org_id IN NUMBER, p_effective_start_date DATE, p_effective_end_date DATE, p_inventory_item_id IN NUMBER) IS
1211 SELECT
1212 sum(case when ((NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date)
1213 AND (NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date))
1214 --BETWEEN p_effective_start_date and p_effective_end_date)
1215 then moqd.transaction_quantity else 0 end) covered_qty,
1216 sum(moqd.transaction_quantity) onhand_qty,
1217 moqd.transaction_uom_code
1218 FROM
1219 mtl_onhand_quantities_detail moqd,
1220 org_organization_definitions ood,
1221 mtl_parameters mp
1222 WHERE
1223 moqd.organization_id = ood.organization_id AND
1224 moqd.inventory_item_id = p_inventory_item_id AND
1225 mp.organization_id = ood.organization_id AND
1226 -- NVL(mp.consigned_flag,'N') = 'N' AND
1227 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
1228 ood.operating_unit = p_org_id AND
1229 moqd.planning_organization_id = mp.organization_id
1230 AND moqd.owning_organization_id = mp.organization_id
1231 AND moqd.planning_tp_type = 2
1232 AND moqd.owning_tp_type = 2
1233 AND moqd.is_consigned = 2
1234 GROUP BY moqd.transaction_uom_code;
1235
1236 cursor get_covered_inv_wh_csr(p_org_id IN NUMBER, p_effective_start_date DATE, p_effective_end_date DATE, p_inventory_item_id IN NUMBER) is
1237 SELECT
1238 SUM(moqd.transaction_quantity) Covered_quantity,
1239 ood.organization_name warehouse,
1240 ood.organization_id warehouse_id
1241 FROM
1242 mtl_onhand_quantities_detail moqd,
1243 org_organization_definitions ood,
1244 mtl_parameters mp
1245 WHERE
1246 moqd.organization_id = ood.organization_id AND
1247 moqd.inventory_item_id = p_inventory_item_id AND
1248 ood.operating_unit = p_org_id AND
1249 mp.organization_id = ood.organization_id AND
1250 -- NVL(mp.consigned_flag,'N') = 'N' AND
1251 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
1252 ((NVL(moqd.orig_date_received, moqd.date_received) >= p_effective_start_date)
1253 AND (NVL(moqd.orig_date_received, moqd.date_received) < p_effective_end_date)) AND
1254 --BETWEEN p_effective_start_date and p_effective_end_date AND
1255 moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
1256 AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
1257 AND moqd.PLANNING_TP_TYPE = 2
1258 AND moqd.OWNING_TP_TYPE = 2
1259 AND moqd.IS_CONSIGNED = 2
1260 GROUP BY ood.organization_name,
1261 ood.organization_id;
1262
1266 SUM(moqd.transaction_quantity) Onhand_quantity
1263 cursor get_covered_inv_rct_csr(p_org_id IN NUMBER, p_inventory_item_id IN NUMBER, p_warehouse_id IN NUMBER) is
1264 SELECT
1265 (NVL(moqd.orig_date_received,moqd.date_received)) date_received,
1267 FROM
1268 mtl_onhand_quantities_detail moqd,
1269 org_organization_definitions ood,
1270 mtl_parameters mp
1271 WHERE
1272 moqd.organization_id = ood.organization_id AND
1273 moqd.inventory_item_id = p_inventory_item_id AND
1274 ood.operating_unit = p_org_id AND
1275 mp.organization_id = ood.organization_id AND
1276 -- NVL(mp.consigned_flag,'N') = 'N' AND
1277 NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
1278 moqd.organization_id = p_warehouse_id AND
1279 moqd.planning_organization_id = mp.organization_id
1280 AND moqd.owning_organization_id = mp.organization_id
1281 AND moqd.planning_tp_type = 2
1282 AND moqd.owning_tp_type = 2
1283 AND moqd.is_consigned = 2
1284 GROUP BY (NVL(moqd.orig_date_received,moqd.date_received));
1285
1286 BEGIN
1287
1288 x_rec_count := 0;
1289 x_return_status := fnd_api.g_ret_sts_success;
1290 IF l_hdr_rec.org_id IS NULL THEN
1291 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1292 FND_MESSAGE.set_token('ID', 'Org ID');
1293 FND_MSG_PUB.add;
1294 RAISE FND_API.G_EXC_ERROR;
1295 ELSIF l_hdr_rec.effective_start_date IS NULL THEN
1296 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1297 FND_MESSAGE.set_token('ID', 'Effective Start Date');
1298 FND_MSG_PUB.add;
1299 RAISE FND_API.G_EXC_ERROR;
1300 ELSIF l_hdr_rec.effective_end_date IS NULL THEN
1301 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1302 FND_MESSAGE.set_token('ID', 'Effective End Date');
1303 FND_MSG_PUB.add;
1304 RAISE FND_API.G_EXC_ERROR;
1305 END IF;
1306
1307 FOR i in l_inventorydetails_tbl.FIRST..l_inventorydetails_tbl.LAST
1308 LOOP
1309
1310 IF l_inventorydetails_tbl(i).Inventory_Item_ID IS NULL THEN
1311 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1312 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
1313 FND_MSG_PUB.add;
1314 x_return_status := FND_API.G_RET_STS_ERROR;
1315 ELSE
1316 FOR get_covered_inventory_rec IN get_covered_inventory_csr(l_hdr_rec.org_id, l_hdr_rec.effective_start_date, l_hdr_rec.effective_end_date, l_inventorydetails_tbl(i).Inventory_Item_ID)
1317 LOOP
1318 l_inventorydetails_tbl(i).covered_quantity := NVL(get_covered_inventory_rec.covered_qty,0);
1319 l_inventorydetails_tbl(i).onhand_quantity := NVL(get_covered_inventory_rec.onhand_qty,0);
1320 l_inventorydetails_tbl(i).uom_code := get_covered_inventory_rec.transaction_uom_code;
1321 --IF covered inventory is negative then reassign it to 0
1322 IF l_inventorydetails_tbl(i).covered_quantity < 0 THEN
1323 l_inventorydetails_tbl(i).covered_quantity := 0;
1324 END IF;
1325 -- Delete existing rows in DPP_INVENTORY_DETAILS_ADJ_ALL (if any)
1326 DELETE
1327 FROM DPP_INVENTORY_DETAILS_ADJ_ALL
1328 WHERE INVENTORY_DETAILS_ID IN
1329 (SELECT INVENTORY_DETAILS_ID
1330 FROM DPP_INVENTORY_DETAILS_ALL
1331 WHERE org_id = l_hdr_rec.org_id
1332 AND transaction_line_id = l_inventorydetails_tbl(i).Transaction_Line_Id);
1333
1334 -- Delete existing rows in DPP_INVENTORY_DETAILS_ALL (if any)
1335 DELETE
1336 FROM DPP_INVENTORY_DETAILS_ALL
1337 WHERE org_id = l_hdr_rec.org_id
1338 AND transaction_line_id = l_inventorydetails_tbl(i).Transaction_Line_Id;
1339 --Get the ware house level details only if the covered inventory is > 0
1340 IF l_inventorydetails_tbl(i).covered_quantity > 0 THEN
1341 FOR get_covered_inv_wh_rec IN get_covered_inv_wh_csr(l_hdr_rec.org_id, l_hdr_rec.effective_start_date, l_hdr_rec.effective_end_date, l_inventorydetails_tbl(i).Inventory_ITem_ID)
1342 LOOP
1343
1344 BEGIN
1345
1346 SELECT DPP_INVENTORY_DETAILS_SEQ.nextval INTO l_inv_details_id FROM DUAL;
1347 l_flag := 'N';
1348 -- Insert new row
1349 INSERT INTO DPP_INVENTORY_DETAILS_ALL(
1350 inventory_details_id,
1351 transaction_line_id,
1352 quantity,
1353 uom,
1354 include_flag,
1355 creation_date,
1356 created_by,
1357 last_update_date,
1358 last_updated_by,
1359 last_update_login,
1360 inventory_item_id,
1361 org_id,
1362 organization_id,
1363 object_version_number)
1364 VALUES ( l_inv_details_id,
1365 l_inventorydetails_tbl(i).Transaction_Line_Id,
1366 get_covered_inv_wh_rec.Covered_quantity,
1367 l_inventorydetails_tbl(i).UOM_Code,
1368 'N',
1369 l_sysdate,
1370 l_user_id,
1371 l_sysdate,
1372 l_user_id,
1373 l_user_id,
1374 l_inventorydetails_tbl(i).inventory_item_id,
1375 l_hdr_rec.org_id,
1376 get_covered_inv_wh_rec.Warehouse_id,
1377 1
1378 );
1379
1380 END;
1381
1385 BEGIN
1382 FOR get_covered_inv_rct_rec IN get_covered_inv_rct_csr(l_hdr_rec.org_id, l_inventorydetails_tbl(i).Inventory_ITem_ID, get_covered_inv_wh_rec.warehouse_id)
1383 LOOP
1384
1386 IF ((get_covered_inv_rct_rec.date_received >= l_hdr_rec.effective_start_date) AND (get_covered_inv_rct_rec.date_received < l_hdr_rec.effective_end_date)) THEN
1387 l_include_flag := 'Y';
1388 l_flag := 'Y';
1389 l_days_out := 0;
1390 ELSIF (get_covered_inv_rct_rec.date_received < l_hdr_rec.effective_start_date) THEN
1391 l_include_flag := 'N';
1392 l_days_out := -(l_hdr_rec.effective_start_date - get_covered_inv_rct_rec.date_received);
1393 l_days_out := floor(l_days_out);
1394 ELSIF (get_covered_inv_rct_rec.date_received >= l_hdr_rec.effective_end_date) THEN
1395 l_include_flag := 'N';
1396 l_days_out := (get_covered_inv_rct_rec.date_received - l_hdr_rec.effective_end_date);
1397 l_days_out := ceil(l_days_out);
1398 IF l_days_out = 0 THEN
1399 l_days_out := 1;
1400 END IF;
1401 END IF;
1402 END;
1403
1404 INSERT
1405 INTO DPP_INVENTORY_DETAILS_ADJ_ALL(
1406 inv_details_adj_id,
1407 inventory_details_id,
1408 date_received,
1409 days_out,
1410 quantity,
1411 uom,
1412 comments,
1413 include_flag,
1414 creation_date,
1415 created_by,
1416 last_update_date,
1417 last_updated_by,
1418 last_update_login,
1419 org_id,
1420 object_version_number)
1421 VALUES(
1422 dpp_inv_details_adj_id_seq.nextval,
1423 l_inv_details_id,
1424 get_covered_inv_rct_rec.date_received,
1425 l_days_out,
1426 get_covered_inv_rct_rec.Onhand_quantity,
1427 l_inventorydetails_tbl(i).UOM_Code,
1428 null,
1429 l_include_flag,
1430 l_sysdate,
1431 l_user_id,
1432 l_sysdate,
1433 l_user_id,
1434 l_user_id,
1435 l_hdr_rec.org_id,
1436 1
1437 );
1438
1439 END LOOP; -- rct loop
1440 IF l_flag = 'Y' THEN
1441 UPDATE DPP_INVENTORY_DETAILS_ALL
1442 SET include_flag = 'Y',
1443 object_version_number = object_version_number + 1,
1444 last_update_date = l_sysdate,
1445 last_updated_by = l_user_id,
1446 last_update_login = l_user_id
1447 WHERE inventory_details_id = l_inv_details_id;
1448 END IF;
1449
1450 END LOOP; -- w/h loop
1451 END IF;--check if covered inventory is > 0
1452
1453 END LOOP; -- main loop
1454
1455 IF l_inventorydetails_tbl(i).onhand_quantity IS NULL THEN
1456
1457 l_inventorydetails_tbl(i).covered_quantity := 0;
1458 l_inventorydetails_tbl(i).onhand_quantity := 0;
1459
1460 BEGIN
1461 SELECT primary_uom_code
1462 INTO l_primary_uom_code
1463 FROM mtl_system_items msi,
1464 mtl_parameters mp
1465 WHERE inventory_item_id = l_inventorydetails_tbl(i).inventory_item_id
1466 AND mp.organization_id = msi.organization_id
1467 AND mp.organization_id = mp.master_organization_id
1468 AND rownum = 1;
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 DPP_UTILITY_PVT.debug_message ('Error in fetching primary UOM: ' || SQLERRM);
1472 x_return_status := FND_API.G_RET_STS_ERROR;
1473 END;
1474
1475 l_inventorydetails_tbl(i).uom_code := l_primary_uom_code; -- Default to Primary UOM
1476 END IF;
1477
1478 END IF;
1479
1480 END LOOP; -- tbl loop
1481 x_rec_count := l_inventorydetails_tbl.COUNT;
1482 p_inventorydetails_tbl := l_inventorydetails_tbl;
1483
1484 IF DPP_DEBUG_HIGH_ON THEN
1485 DPP_UTILITY_PVT.debug_message ('get_inventorydetails(): x_return_status: ' || x_return_status);
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN FND_API.G_EXC_ERROR THEN
1490 x_return_status := FND_API.G_RET_STS_ERROR;
1491
1492 WHEN OTHERS THEN
1493 x_return_status := fnd_api.g_ret_sts_unexp_error;
1494
1495 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1496 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1497 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_InventoryDetails');
1498 fnd_message.set_token('ERRNO', sqlcode);
1499 fnd_message.set_token('REASON', sqlerrm);
1500 FND_MSG_PUB.ADD;
1501 END IF;
1502
1503 END Get_InventoryDetails;
1504
1505 PROCEDURE Get_CustomerInventory(
1506 p_hdr_rec IN dpp_inv_hdr_rec_type
1507 ,p_cust_inv_tbl IN OUT NOCOPY dpp_cust_inv_tbl_type
1508 ,x_rec_count OUT NOCOPY NUMBER
1509 ,x_return_status OUT NOCOPY VARCHAR2
1510 )
1511 IS
1515 p_effective_end_date IN DATE ,
1512 l_hdr_rec dpp_inv_hdr_rec_type := p_hdr_rec;
1513 l_cust_inv_tbl dpp_cust_inv_tbl_type := p_cust_inv_tbl;
1514 CURSOR get_customer_inventory_csr (p_effective_start_date IN DATE,
1516 p_customer_id IN NUMBER,
1517 p_inventory_item_id IN NUMBER) IS
1518 SELECT osta.primary_uom_code uom,
1519 sum(decode(osta.transfer_type, 'IN', nvl(osta.common_quantity,0), 0)) - sum(decode(osta.transfer_type, 'OUT', nvl(osta.common_quantity,0), 0)) as end_inventory
1520 FROM
1521 ozf_sales_transactions_all osta
1522 ,hz_parties hp
1523 ,hz_cust_accounts hca
1524 WHERE osta.sold_to_party_id = hp.party_id
1525 AND osta.error_flag='N'
1526 AND osta.sold_to_party_id =hp.party_id
1527 AND hca.cust_account_id = p_customer_id
1528 AND hca.party_id = hp.party_id
1529 AND osta.inventory_item_id =p_inventory_item_id--<<Inventory Item ID>>
1530 AND ((osta.transaction_date >= p_effective_start_date) AND (osta.transaction_date < p_effective_end_date))
1531 --between p_effective_start_date AND p_effective_end_date --<<effective_start_date>> and <<effective_end_date>> --//'DD-Mon-YYYY' fromat
1532 GROUP BY
1533 osta.primary_uom_code;
1534 BEGIN
1535
1536 x_rec_count := 0;
1537 x_return_status := fnd_api.g_ret_sts_success;
1538 IF l_hdr_rec.effective_start_date IS NULL THEN
1539 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1540 FND_MESSAGE.set_token('ID', 'Effective Start Date');
1541 FND_MSG_PUB.add;
1542 RAISE FND_API.G_EXC_ERROR;
1543 ELSIF l_hdr_rec.effective_end_date IS NULL THEN
1544 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1545 FND_MESSAGE.set_token('ID', 'Effective End Date');
1546 FND_MSG_PUB.add;
1547 RAISE FND_API.G_EXC_ERROR;
1548 END IF;
1549
1550 FOR i in l_cust_inv_tbl.FIRST..l_cust_inv_tbl.LAST
1551 LOOP
1552
1553 IF l_cust_inv_tbl(i).Inventory_Item_ID IS NULL THEN
1554 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1555 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
1556 FND_MSG_PUB.add;
1557 x_return_status := FND_API.G_RET_STS_ERROR;
1558 ELSIF l_cust_inv_tbl(i).Customer_ID IS NULL THEN
1559 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1560 FND_MESSAGE.set_token('ID', 'Customer ID');
1561 FND_MSG_PUB.add;
1562 x_return_status := FND_API.G_RET_STS_ERROR;
1563 ELSE
1564 FOR get_customer_inventory_rec IN get_customer_inventory_csr(l_hdr_rec.effective_start_date,
1565 l_hdr_rec.effective_end_date,
1566 l_cust_inv_tbl(i).customer_ID,
1567 l_cust_inv_tbl(i).Inventory_ITem_ID)
1568 LOOP
1569 l_cust_inv_tbl(i).onhand_quantity := get_customer_inventory_rec.end_inventory;
1570 l_cust_inv_tbl(i).uom_code := get_customer_inventory_rec.uom;
1571 END LOOP;
1572 END IF;
1573
1574 END LOOP;
1575 x_rec_count := l_cust_inv_tbl.COUNT;
1576 p_cust_inv_tbl := l_cust_inv_tbl;
1577
1578 IF DPP_DEBUG_HIGH_ON THEN
1579 DPP_UTILITY_PVT.debug_message ('get_customerInventory(): x_return_status: ' || x_return_status);
1580 END IF;
1581 EXCEPTION
1582 WHEN FND_API.G_EXC_ERROR THEN
1583 x_return_status := FND_API.G_RET_STS_ERROR;
1584
1585 WHEN OTHERS THEN
1586 x_return_status := fnd_api.g_ret_sts_unexp_error;
1587 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1588 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1589 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_CustomerInventory');
1590 fnd_message.set_token('ERRNO', sqlcode);
1591 fnd_message.set_token('REASON', sqlerrm);
1592 FND_MSG_PUB.ADD;
1593 END IF;
1594 END Get_CustomerInventory;
1595
1596
1597 PROCEDURE search_customers(
1598 p_search_criteria IN search_criteria_tbl_type
1599 ,x_customer_tbl OUT NOCOPY customer_tbl_type
1600 ,x_rec_count OUT NOCOPY NUMBER
1601 ,x_return_status OUT NOCOPY VARCHAR2
1602 )
1603 IS
1604 l_search_criteria_tbl search_criteria_tbl_type := p_search_criteria;
1605 l_customer_tbl customer_tbl_type;
1606
1607 CURSOR get_customer_csr (
1608 p_search_criteria1 IN VARCHAR2, p_search_text1 IN VARCHAR2,
1609 p_search_criteria2 IN VARCHAR2, p_search_text2 IN VARCHAR2) IS
1610 SELECT
1611 oola.sold_to_org_id,
1612 hz.account_number,
1613 hz.account_name
1614 FROM
1615 oe_order_lines_all oola,
1616 hz_cust_accounts hz,
1617 dpp_transaction_headers_all dtha,
1618 dpp_transaction_lines_all dtla
1619 WHERE
1620 oola.org_id = dtla.org_id and
1621 dtla.transaction_header_id = TO_NUMBER(p_search_text2) and
1622 dtla.transaction_header_id = dtha.transaction_header_id and
1623 p_search_criteria2 = 'TRANSACTION_HEADER_ID' and
1624 oola.inventory_item_id = dtla.inventory_item_id and
1625 (dtla.prior_price - NVL(dtla.supplier_new_price,0)) > 0 and
1626 hz.cust_account_id = oola.sold_to_org_id and
1627 hz.status = 'A' and
1628 (((actual_shipment_date >= (dtha.effective_start_date - dtha.days_covered))
1629 AND (actual_shipment_date < dtha.effective_start_date))
1630 OR (dtha.days_covered IS NULL AND actual_shipment_date < dtha.effective_start_date)) and
1631 ((UPPER(hz.account_name) like UPPER(p_search_text1) AND p_search_criteria1 = 'CUSTOMER_NAME')
1632 OR (UPPER(hz.account_number) like UPPER(p_search_text1) AND p_search_criteria1 = 'CUSTOMER_NUMBER'))
1633 GROUP BY oola.sold_to_org_id,
1634 hz.account_number,
1635 hz.account_name
1636 ORDER BY hz.account_name,hz.account_number;
1637
1638 BEGIN
1639 x_rec_count := 0;
1640 x_return_status := fnd_api.g_ret_sts_success;
1641 IF l_search_criteria_tbl(2).search_text IS NULL THEN
1642 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1643 fnd_message.set_name('DPP', 'DPP_UI_LOV_NO_TRANSACTIONID');
1644 fnd_msg_pub.add;
1645 END IF;
1646 RAISE FND_API.G_EXC_ERROR;
1647 END IF;
1648
1649 OPEN get_customer_csr(l_search_criteria_tbl(1).search_criteria,
1650 NVL(l_search_criteria_tbl(1).search_text,'%'),
1651 NVL(l_search_criteria_tbl(2).search_criteria,'TRANSACTION_HEADER_ID'),
1652 l_search_criteria_tbl(2).search_text);
1653 LOOP
1654 FETCH get_customer_csr BULK COLLECT INTO l_customer_tbl;
1655 EXIT WHEN get_customer_csr%NOTFOUND;
1656 END LOOP;
1657 CLOSE get_customer_csr;
1658
1659 x_rec_count := l_customer_tbl.COUNT;
1660 x_customer_tbl := l_customer_tbl;
1661
1662 IF DPP_DEBUG_HIGH_ON THEN
1663 DPP_UTILITY_PVT.debug_message ('search_customers(): x_return_status: ' || x_return_status);
1664 END IF;
1665
1666 EXCEPTION
1667 WHEN FND_API.G_EXC_ERROR THEN
1668 x_return_status := FND_API.G_RET_STS_ERROR;
1669
1670 WHEN OTHERS THEN
1671 x_return_status := fnd_api.g_ret_sts_unexp_error;
1672
1673 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1674 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1675 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.search_customers');
1676 fnd_message.set_token('ERRNO', sqlcode);
1677 fnd_message.set_token('REASON', sqlerrm);
1678 FND_MSG_PUB.ADD;
1679 END IF;
1680
1681 END search_customers;
1682
1683 PROCEDURE Get_LastPrice(p_hdr_rec IN dpp_inv_hdr_rec_type
1684 ,p_cust_price_tbl IN OUT NOCOPY dpp_cust_price_tbl_type
1685 ,x_rec_count OUT NOCOPY NUMBER
1686 ,x_return_status OUT NOCOPY VARCHAR2
1687 )
1688 IS
1689 l_hdr_rec dpp_inv_hdr_rec_type:= p_hdr_rec;
1690 l_cust_price_tbl dpp_cust_price_tbl_type := p_cust_price_tbl;
1691 l_functional_currency VARCHAR2(15);
1692 l_exchange_rate NUMBER;
1693 l_to_amount NUMBER;
1694 l_return_status VARCHAR2(1);
1695 l_trunc_sysdate DATE := trunc(sysdate);
1696
1697 CURSOR get_last_price_csr (p_org_id IN NUMBER,
1698 p_inventory_item_id IN NUMBER,
1699 p_customer_id IN NUMBER,
1700 p_uom_code IN VARCHAR2)
1701 IS
1702 SELECT
1703 rctl.unit_selling_price last_price,
1704 rct.invoice_currency_code
1705 FROM
1706 ra_customer_trx_lines_all rctl,
1707 ra_customer_trx_all rct,
1708 ra_cust_trx_types_all rctt
1709 WHERE
1710 line_type = 'LINE' AND
1711 inventory_item_id = p_inventory_item_id AND
1712 uom_code = p_uom_code AND
1713 rct.customer_trx_id = rctl.customer_trx_id AND
1714 rct.org_id = p_org_id AND
1715 rctt.cust_trx_type_id = rct.cust_trx_type_id AND
1716 rct.org_id = rctt.org_id AND
1717 rctt.name = 'Invoice' AND
1718 rct.org_id = rctl.org_id AND
1719 rct.sold_to_customer_id = p_customer_id AND
1720 rct.complete_flag = 'Y' AND
1721 rctl.customer_trx_line_id = (
1722 SELECT
1723 MAX(rctl1.customer_trx_line_id)
1724 FROM
1725 ra_customer_trx_lines_all rctl1,
1726 ra_customer_trx_all rct1,
1727 ra_cust_trx_types_all rctt1
1728 WHERE
1729 line_type = 'LINE' AND
1730 inventory_item_id = p_inventory_item_id AND
1731 uom_code = p_uom_code AND
1732 rct1.customer_trx_id = rctl1.customer_trx_id AND
1733 rct1.org_id = p_org_id AND
1734 rctt1.cust_trx_type_id = rct1.cust_trx_type_id AND
1735 rct1.org_id = rctt1.org_id AND
1736 rctt1.name = 'Invoice' AND
1737 rct1.org_id = rctl1.org_id AND
1738 rct1.sold_to_customer_id = p_customer_id AND
1739 rct1.complete_flag = 'Y');
1740
1741 BEGIN
1742
1746 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1743 x_rec_count := 0;
1744 x_return_status := fnd_api.g_ret_sts_success;
1745 IF l_hdr_rec.org_id IS NULL THEN
1747 FND_MESSAGE.set_token('ID', 'Org ID');
1748 FND_MSG_PUB.add;
1749 RAISE FND_API.G_EXC_ERROR;
1750 END IF;
1751
1752 FOR i in l_cust_price_tbl.FIRST..l_cust_price_tbl.LAST
1753 LOOP
1754
1755 IF l_cust_price_tbl(i).Inventory_Item_ID IS NULL THEN
1756 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1757 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
1758 FND_MSG_PUB.add;
1759 x_return_status := FND_API.G_RET_STS_ERROR;
1760 ELSIF l_cust_price_tbl(i).Customer_ID IS NULL THEN
1761 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1762 FND_MESSAGE.set_token('ID', 'Customer ID');
1763 FND_MSG_PUB.add;
1764 x_return_status := FND_API.G_RET_STS_ERROR;
1765 ELSIF l_cust_price_tbl(i).UOM_Code IS NULL THEN
1766 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1767 FND_MESSAGE.set_token('ID', 'UOM Code');
1768 FND_MSG_PUB.add;
1769 x_return_status := FND_API.G_RET_STS_ERROR;
1770 ELSIF l_cust_price_tbl(i).price_change IS NULL THEN
1771 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1772 FND_MESSAGE.set_token('ID', 'Price Change');
1773 FND_MSG_PUB.add;
1774 x_return_status := FND_API.G_RET_STS_ERROR;
1775 ELSE
1776 FOR get_last_price_rec IN get_last_price_csr(l_hdr_rec.org_id,
1777 l_cust_price_tbl(i).Inventory_Item_ID,
1778 l_cust_price_tbl(i).Customer_ID,
1779 l_cust_price_tbl(i).UOM_Code) LOOP
1780 l_cust_price_tbl(i).last_price := NVL(get_last_price_rec.last_price,0);
1781 l_cust_price_tbl(i).invoice_currency_code := nvl(get_last_price_rec.invoice_currency_code,l_hdr_rec.currency_code);
1782 END LOOP;
1783 IF l_cust_price_tbl(i).last_price IS NULL THEN
1784 l_cust_price_tbl(i).last_price := 0 ;
1785 l_cust_price_tbl(i).invoice_currency_code := l_hdr_rec.currency_code;
1786 END IF;
1787 --Get the converted Price change if the currency is different
1788 IF l_hdr_rec.currency_code <> l_cust_price_tbl(i).invoice_currency_code THEN
1789 --Call the convert currency API to get the converted value of Price change
1790 l_to_amount := 0;
1791 DPP_UTILITY_PVT.convert_currency( p_from_currency => l_hdr_rec.currency_code
1792 ,p_to_currency => l_cust_price_tbl(i).invoice_currency_code
1793 ,p_conv_type => FND_API.G_MISS_CHAR
1794 ,p_conv_rate => FND_API.G_MISS_NUM
1795 ,p_conv_date => l_trunc_sysdate
1796 ,p_from_amount => l_cust_price_tbl(i).price_change
1797 ,x_return_status => l_return_status
1798 ,x_to_amount => l_to_amount
1799 ,x_rate => l_exchange_rate);
1800 IF DPP_DEBUG_HIGH_ON THEN
1801 DPP_UTILITY_PVT.debug_message ('convert_currency(): x_return_status: ' || l_return_status);
1802 END IF;
1803 IF l_return_status = 'S' THEN
1804 IF DPP_DEBUG_HIGH_ON THEN
1805 DPP_UTILITY_PVT.debug_message ('convert_currency(): Exchange Rate : ' || l_exchange_rate);
1806 END IF;
1807 l_cust_price_tbl(i).converted_price_change := l_to_amount;
1808 END IF;
1809 ELSE
1810 IF DPP_DEBUG_HIGH_ON THEN
1811 DPP_UTILITY_PVT.debug_message ('No conversion of currency code required');
1812 END IF;
1813 l_cust_price_tbl(i).converted_price_change := l_cust_price_tbl(i).price_change;
1814 END IF;
1815 END IF;
1816 END LOOP;
1817
1818 x_rec_count := l_cust_price_tbl.COUNT;
1819 p_cust_price_tbl := l_cust_price_tbl;
1820
1821 IF DPP_DEBUG_HIGH_ON THEN
1822 DPP_UTILITY_PVT.debug_message ('get_lastprice(): x_return_status: ' || x_return_status);
1823 END IF;
1824
1825 EXCEPTION
1826 WHEN FND_API.G_EXC_ERROR THEN
1827 x_return_status := FND_API.G_RET_STS_ERROR;
1828
1829 WHEN OTHERS THEN
1830 x_return_status := fnd_api.g_ret_sts_unexp_error;
1831
1832 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1833 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1834 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_LastPrice');
1835 fnd_message.set_token('ERRNO', sqlcode);
1836 fnd_message.set_token('REASON', sqlerrm);
1837 FND_MSG_PUB.ADD;
1838 END IF;
1839
1840 END Get_LastPrice;
1841
1842 PROCEDURE Get_ListPrice(
1843 p_hdr_rec IN dpp_inv_hdr_rec_type
1844 ,p_listprice_tbl IN OUT NOCOPY dpp_list_price_tbl_type
1845 ,x_rec_count OUT NOCOPY NUMBER
1846 ,x_return_status OUT NOCOPY VARCHAR2
1847 )
1848 IS
1849
1850 l_header_rec dpp_inv_hdr_rec_type:= p_hdr_rec;
1854 l_exchange_rate NUMBER;
1851 l_listprice_tbl dpp_list_price_tbl_type := p_listprice_tbl;
1852 l_inventory_organization_id NUMBER;
1853 l_functional_currency VARCHAR2(15);
1855 l_to_amount NUMBER;
1856 l_return_status VARCHAR2(1);
1857 l_trunc_sysdate DATE := trunc(sysdate);
1858
1859 CURSOR get_list_price_csr (p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER) IS
1860 SELECT
1861 msi.LIST_PRICE_PER_UNIT list_price
1862 FROM
1863 mtl_system_items msi,
1864 financials_system_params_all fspa
1865 WHERE
1866 msi.organization_id = fspa.inventory_organization_id and
1867 fspa.org_id = p_organization_id and
1868 msi.inventory_item_id = p_inventory_item_id;
1869
1870
1871 BEGIN
1872
1873 x_rec_count := 0;
1874 x_return_status := fnd_api.g_ret_sts_success;
1875
1876 IF l_header_rec.org_id IS NULL THEN
1877 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1878 FND_MESSAGE.set_token('ID', 'Org ID');
1879 FND_MSG_PUB.add;
1880 RAISE FND_API.G_EXC_ERROR;
1881 ELSIF l_header_rec.currency_code IS NULL THEN
1882 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1883 FND_MESSAGE.set_token('ID', 'Currency Code');
1884 FND_MSG_PUB.add;
1885 RAISE FND_API.G_EXC_ERROR;
1886 END IF;
1887 po_moac_utils_pvt.set_org_context(l_header_rec.org_id);
1888
1889 SELECT inventory_organization_id
1890 INTO l_inventory_organization_id
1891 FROM financials_system_parameters;
1892
1893 -- Call Currency Conversion API to convert to Txn Currency
1894 --Changed from ozf_sys_parameters_all to hr_operating_units
1895 SELECT gs.currency_code
1896 INTO l_functional_currency
1897 FROM gl_sets_of_books gs
1898 , hr_operating_units hr
1899 WHERE hr.set_of_books_id = gs.set_of_books_id
1900 AND hr.organization_id = l_header_rec.org_id;
1901
1902 FOR i in l_listprice_tbl.FIRST..l_listprice_tbl.LAST
1903 LOOP
1904
1905 IF l_listprice_tbl(i).Inventory_Item_ID IS NULL THEN
1906 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1907 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
1908 FND_MSG_PUB.add;
1909 x_return_status := FND_API.G_RET_STS_ERROR;
1910 ELSE
1911 FOR get_list_price_rec IN get_list_price_csr(l_header_rec.org_id, l_listprice_tbl(i).Inventory_Item_ID)
1912 LOOP
1913 IF l_functional_currency = l_header_rec.currency_code THEN
1914 l_to_amount := get_list_price_rec.list_price;
1915 ELSE
1916 l_to_amount := 0;
1917 DPP_UTILITY_PVT.convert_currency(
1918 p_from_currency => l_functional_currency
1919 ,p_to_currency => l_header_rec.currency_code
1920 ,p_conv_type => FND_API.G_MISS_CHAR
1921 ,p_conv_rate => FND_API.G_MISS_NUM
1922 ,p_conv_date => l_trunc_sysdate
1923 ,p_from_amount => get_list_price_rec.list_price
1924 ,x_return_status => l_return_status
1925 ,x_to_amount => l_to_amount
1926 ,x_rate => l_exchange_rate);
1927 END IF; --checking for same currency
1928 l_listprice_tbl(i).list_price := l_to_amount;
1929 END LOOP;
1930 END IF;
1931 END LOOP;
1932
1933 x_rec_count := l_listprice_tbl.COUNT;
1934 p_listprice_tbl := l_listprice_tbl;
1935
1936 IF DPP_DEBUG_HIGH_ON THEN
1937 DPP_UTILITY_PVT.debug_message ('get_listprice(): x_return_status: ' || x_return_status);
1938 END IF;
1939
1940 EXCEPTION
1941 WHEN FND_API.G_EXC_ERROR THEN
1942 x_return_status := FND_API.G_RET_STS_ERROR;
1943
1944 WHEN OTHERS THEN
1945 x_return_status := fnd_api.g_ret_sts_unexp_error;
1946
1947 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1948 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1949 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_ListPrice');
1950 fnd_message.set_token('ERRNO', sqlcode);
1951 fnd_message.set_token('REASON', sqlerrm);
1952 FND_MSG_PUB.ADD;
1953 END IF;
1954
1955
1956 END Get_ListPrice;
1957
1958 PROCEDURE Get_Vendor(
1959 p_vendor_rec IN OUT NOCOPY vendor_rec_type
1960 ,x_rec_count OUT NOCOPY NUMBER
1961 ,x_return_status OUT NOCOPY VARCHAR2
1962 )
1963 IS
1964
1965 CURSOR get_vendor_csr(p_vendor_id IN NUMBER)
1966 IS
1967 SELECT segment1 vendor_num,
1968 vendor_name
1969 FROM ap_suppliers pov
1970 WHERE enabled_flag = 'Y'
1971 AND hold_flag = 'N'
1972 AND vendor_id = p_vendor_id;
1973
1974 l_vendor_rec vendor_rec_type := p_vendor_rec;
1975 BEGIN
1976 x_rec_count := 0;
1977 x_return_status := fnd_api.g_ret_sts_success;
1978
1979 IF l_vendor_rec.vendor_ID IS NULL THEN
1980 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
1981 FND_MESSAGE.set_token('ID', 'Vendor ID');
1982 FND_MSG_PUB.add;
1983 RAISE FND_API.G_EXC_ERROR;
1984 END IF;
1988 l_vendor_rec.vendor_name := get_vendor_rec.vendor_name;
1985 FOR get_vendor_rec IN get_vendor_csr(l_vendor_rec.vendor_ID)
1986 LOOP
1987 l_vendor_rec.vendor_number := get_vendor_rec.vendor_num;
1989 x_rec_count := 1;
1990 END LOOP;
1991
1992 p_vendor_rec := l_vendor_rec;
1993
1994 EXCEPTION
1995 WHEN FND_API.G_EXC_ERROR THEN
1996 x_return_status := FND_API.G_RET_STS_ERROR;
1997
1998 WHEN OTHERS THEN
1999 x_return_status := fnd_api.g_ret_sts_unexp_error;
2000
2001 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2002 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2003 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_Vendor');
2004 fnd_message.set_token('ERRNO', sqlcode);
2005 fnd_message.set_token('REASON', sqlerrm);
2006 FND_MSG_PUB.ADD;
2007 END IF;
2008 END Get_Vendor;
2009
2010 PROCEDURE Get_Vendor_Site(
2011 p_vendor_site_rec IN OUT NOCOPY vendor_site_rec_type
2012 ,x_rec_count OUT NOCOPY NUMBER
2013 ,x_return_status OUT NOCOPY VARCHAR2
2014 )
2015 IS
2016 CURSOR get_vendor_site_csr (p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER)
2017 IS
2018 SELECT
2019 vendor_site_code,
2020 address_line1,
2021 address_line2,
2022 address_line3,
2023 city,
2024 state,
2025 zip,
2026 country
2027 FROM
2028 ap_supplier_sites_all
2029 WHERE
2030 vendor_id = p_vendor_id AND
2031 nvl(rfq_only_site_flag, 'N') ='N' AND
2032 vendor_site_id = p_vendor_site_id;
2033
2034 l_vendor_site_rec vendor_site_rec_type := p_vendor_site_rec;
2035 BEGIN
2036 x_rec_count := 0;
2037 x_return_status := fnd_api.g_ret_sts_success;
2038
2039 IF l_vendor_site_rec.vendor_ID IS NULL THEN
2040 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2041 FND_MESSAGE.set_token('ID', 'Vendor ID');
2042 FND_MSG_PUB.add;
2043 RAISE FND_API.G_EXC_ERROR;
2044 ELSIF l_vendor_site_rec.vendor_site_ID IS NULL THEN
2045 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2046 FND_MESSAGE.set_token('ID', 'Vendor Site ID');
2047 FND_MSG_PUB.add;
2048 RAISE FND_API.G_EXC_ERROR;
2049 END IF;
2050
2051 FOR get_vendor_site_rec IN get_vendor_site_csr(l_vendor_site_rec.vendor_ID,l_vendor_site_rec.vendor_site_ID)
2052 LOOP
2053 l_vendor_site_rec.vendor_site_code := get_vendor_site_rec.vendor_site_code;
2054 l_vendor_site_rec.address_line1 := get_vendor_site_rec.address_line1;
2055 l_vendor_site_rec.address_line2 := get_vendor_site_rec.address_line2;
2056 l_vendor_site_rec.address_line3 := get_vendor_site_rec.address_line3;
2057 l_vendor_site_rec.city := get_vendor_site_rec.city;
2058 l_vendor_site_rec.state := get_vendor_site_rec.state;
2059 l_vendor_site_rec.zip := get_vendor_site_rec.zip;
2060 l_vendor_site_rec.country := get_vendor_site_rec.country;
2061 x_rec_count := 1;
2062 END LOOP;
2063
2064 p_vendor_site_rec := l_vendor_site_rec;
2065
2066 EXCEPTION
2067 WHEN FND_API.G_EXC_ERROR THEN
2068 x_return_status := FND_API.G_RET_STS_ERROR;
2069
2070 WHEN OTHERS THEN
2071 x_return_status := fnd_api.g_ret_sts_unexp_error;
2072
2073 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2074 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2075 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_Vendor_Site');
2076 fnd_message.set_token('ERRNO', sqlcode);
2077 fnd_message.set_token('REASON', sqlerrm);
2078 FND_MSG_PUB.ADD;
2079 END IF;
2080 END Get_Vendor_Site;
2081
2082 PROCEDURE Get_Vendor_Contact(
2083 p_vendor_contact_rec IN OUT NOCOPY vendor_contact_rec_type
2084 ,x_rec_count OUT NOCOPY NUMBER
2085 ,x_return_status OUT NOCOPY VARCHAR2
2086 )
2087 IS
2088
2089 CURSOR get_vendor_contact_csr (p_vendor_site_id IN NUMBER, p_vendor_contact_id IN NUMBER)
2090 IS
2091 SELECT poc.first_name ,
2092 poc.middle_name ,
2093 poc.last_name ,
2094 poc.area_code||poc.phone phone,
2095 poc.email_address ,
2096 poc.fax
2097 FROM ap_supplier_sites_all assa,
2098 po_vendor_contacts poc
2099 WHERE assa.vendor_site_id = p_vendor_site_id
2100 AND assa.vendor_site_id = poc.vendor_site_id
2101 AND assa.party_site_id = poc.org_party_site_id
2102 AND poc.vendor_contact_id = p_vendor_contact_id;
2103
2104 l_vendor_contact_rec vendor_contact_rec_type := p_vendor_contact_rec;
2105
2106 BEGIN
2107 x_rec_count := 0;
2108 x_return_status := fnd_api.g_ret_sts_success;
2109
2110 IF l_vendor_contact_rec.vendor_site_ID IS NULL THEN
2111 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2112 FND_MESSAGE.set_token('ID', 'Vendor Site ID');
2113 FND_MSG_PUB.add;
2114 RAISE FND_API.G_EXC_ERROR;
2115 ELSIF l_vendor_contact_rec.vendor_contact_ID IS NULL THEN
2116 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2117 FND_MESSAGE.set_token('ID', 'Vendor Contact ID');
2118 FND_MSG_PUB.add;
2119 RAISE FND_API.G_EXC_ERROR;
2120 END IF;
2121
2125 l_vendor_contact_rec.contact_middle_name := get_vendor_contact_rec.middle_name;
2122 FOR get_vendor_contact_rec IN get_vendor_contact_csr(l_vendor_contact_rec.vendor_site_ID,l_vendor_contact_rec.vendor_contact_ID)
2123 LOOP
2124 l_vendor_contact_rec.contact_first_name := get_vendor_contact_rec.first_name;
2126 l_vendor_contact_rec.contact_last_name := get_vendor_contact_rec.last_name;
2127 l_vendor_contact_rec.contact_phone := get_vendor_contact_rec.phone;
2128 l_vendor_contact_rec.contact_email_address := get_vendor_contact_rec.email_address;
2129 l_vendor_contact_rec.contact_fax := get_vendor_contact_rec.fax;
2130 x_rec_count := 1;
2131
2132 END LOOP;
2133
2134 p_vendor_contact_rec := l_vendor_contact_rec;
2135
2136 EXCEPTION
2137 WHEN FND_API.G_EXC_ERROR THEN
2138 x_return_status := FND_API.G_RET_STS_ERROR;
2139
2140 WHEN OTHERS THEN
2141 x_return_status := fnd_api.g_ret_sts_unexp_error;
2142
2143 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2144 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2145 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_Vendor_Contact');
2146 fnd_message.set_token('ERRNO', sqlcode);
2147 fnd_message.set_token('REASON', sqlerrm);
2148 FND_MSG_PUB.ADD;
2149 END IF;
2150 END Get_Vendor_Contact;
2151
2152 PROCEDURE Get_Warehouse(
2153 p_warehouse_tbl IN OUT NOCOPY warehouse_tbl_type
2154 ,x_rec_count OUT NOCOPY NUMBER
2155 ,x_return_status OUT NOCOPY VARCHAR2
2156 )
2157 IS
2158
2159 cursor get_warehouse_csr(p_organization_id IN NUMBER) is
2160 SELECT
2161 ood.organization_name warehouse
2162 FROM
2163 org_organization_definitions ood
2164 WHERE
2165 ood.organization_id = p_organization_id;
2166 l_warehouse_tbl warehouse_tbl_type := p_warehouse_tbl;
2167
2168 BEGIN
2169 x_rec_count := 0;
2170 x_return_status := fnd_api.g_ret_sts_success;
2171
2172
2173
2174 IF l_warehouse_tbl.COUNT > 0 THEN
2175
2176 FOR i in l_warehouse_tbl.FIRST..l_warehouse_tbl.LAST
2177 LOOP
2178 IF l_warehouse_tbl(i).warehouse_id IS NULL THEN
2179 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2180 FND_MESSAGE.set_token('ID', 'Warehouse ID');
2181 FND_MSG_PUB.add;
2182 x_return_status := FND_API.G_RET_STS_ERROR;
2183 ELSE
2184 FOR get_warehouse_rec IN get_warehouse_csr(l_warehouse_tbl(i).warehouse_id)
2185 LOOP
2186 l_warehouse_tbl(i).warehouse_name := get_warehouse_rec.warehouse;
2187 END LOOP;
2188 END IF;
2189 END LOOP;
2190 END IF;
2191
2192 x_rec_count := l_warehouse_tbl.COUNT;
2193 p_warehouse_tbl := l_warehouse_tbl;
2194
2195 IF DPP_DEBUG_HIGH_ON THEN
2196 DPP_UTILITY_PVT.debug_message ('get_warehouse(): x_return_status: ' || x_return_status);
2197 END IF;
2198
2199 EXCEPTION
2200 WHEN FND_API.G_EXC_ERROR THEN
2201 x_return_status := FND_API.G_RET_STS_ERROR;
2202 WHEN OTHERS THEN
2203 x_return_status := fnd_api.g_ret_sts_unexp_error;
2204
2205 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2206 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2207 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_Warehouse');
2208 fnd_message.set_token('ERRNO', sqlcode);
2209 fnd_message.set_token('REASON', sqlerrm);
2210 FND_MSG_PUB.ADD;
2211 END IF;
2212 END Get_Warehouse;
2213
2214 PROCEDURE Get_Customer(
2215 p_customer_tbl IN OUT NOCOPY customer_tbl_type
2216 ,x_rec_count OUT NOCOPY NUMBER
2217 ,x_return_status OUT NOCOPY VARCHAR2
2218 )
2219 IS
2220 CURSOR get_customer_csr (p_customer_id IN NUMBER) IS
2221 SELECT
2222 hz.account_number,
2223 hz.account_name
2224 FROM
2225 hz_cust_accounts hz
2226 WHERE
2227 hz.cust_account_id = p_customer_id;
2228 l_customer_tbl customer_tbl_type := p_customer_tbl;
2229
2230 BEGIN
2231 x_rec_count := 0;
2232 x_return_status := fnd_api.g_ret_sts_success;
2233
2234 FOR i in l_customer_tbl.FIRST..l_customer_tbl.LAST
2235 LOOP
2236
2237 IF l_customer_tbl(i).customer_id IS NULL THEN
2238 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2239 FND_MESSAGE.set_token('ID', 'Customer ID');
2240 FND_MSG_PUB.add;
2241 x_return_status := FND_API.G_RET_STS_ERROR;
2242 ELSE
2243
2244 FOR get_customer_rec IN get_customer_csr(l_customer_tbl(i).customer_id)
2245 LOOP
2246 l_customer_tbl(i).customer_number := get_customer_rec.account_number;
2247 l_customer_tbl(i).customer_name := get_customer_rec.account_name;
2248 END LOOP;
2249
2250 END IF;
2251
2252 END LOOP;
2253
2254 x_rec_count := l_customer_tbl.COUNT;
2255 p_customer_tbl := l_customer_tbl;
2256
2257 IF DPP_DEBUG_HIGH_ON THEN
2258 DPP_UTILITY_PVT.debug_message ('get_customer(): x_return_status: ' || x_return_status);
2259 END IF;
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 x_return_status := fnd_api.g_ret_sts_unexp_error;
2264 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2268 fnd_message.set_token('REASON', sqlerrm);
2265 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2266 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_Customer');
2267 fnd_message.set_token('ERRNO', sqlcode);
2269 FND_MSG_PUB.ADD;
2270 END IF;
2271 END Get_Customer;
2272
2273 PROCEDURE Get_Product(
2274 p_item_tbl IN OUT NOCOPY item_tbl_type
2275 ,p_org_id IN NUMBER
2276 ,x_rec_count OUT NOCOPY NUMBER
2277 ,x_return_status OUT NOCOPY VARCHAR2
2278 )
2279 IS
2280
2281 l_org_id NUMBER;
2282
2283 CURSOR Get_Product_csr (p_inventory_item_id IN NUMBER, p_org_id IN NUMBER)
2284 IS
2285 SELECT concatenated_segments,
2286 description
2287 FROM mtl_system_items_kfv msi,
2288 financials_system_params_all fspa
2289 WHERE fspa.org_id = p_org_id and
2290 fspa.inventory_organization_id = msi.organization_id and
2291 msi.inventory_item_id = p_inventory_item_id;
2292
2293 l_item_tbl item_tbl_type := p_item_tbl;
2294
2295 BEGIN
2296
2297 IF (p_org_id IS NOT NULL) THEN
2298 l_org_id := p_org_id;
2299 ELSE
2300 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2301 fnd_message.set_name('DPP', 'DPP_ORG_ID_NOTFOUND');
2302 fnd_msg_pub.add;
2303 END IF;
2304
2305 RAISE fnd_api.g_exc_error;
2306 END IF;
2307
2308 IF (DPP_DEBUG_HIGH_ON) THEN
2309 dpp_utility_pvt.debug_message('debug: start ' || l_org_id);
2310 END IF;
2311
2312 x_rec_count := 0;
2313 x_return_status := fnd_api.g_ret_sts_success;
2314
2315 FOR i in l_item_tbl.FIRST..l_item_tbl.LAST
2316 LOOP
2317
2318 IF l_item_tbl(i).inventory_item_id IS NULL THEN
2319 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2320 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
2321 FND_MSG_PUB.add;
2322 x_return_status := FND_API.G_RET_STS_ERROR;
2323
2324 ELSE
2325
2326 FOR Get_Product_rec IN Get_Product_csr(l_item_tbl(i).inventory_item_id, l_org_id)
2327 LOOP
2328 l_item_tbl(i).item_number := Get_Product_rec.concatenated_segments;
2329 l_item_tbl(i).description := Get_Product_rec.description;
2330 END LOOP;
2331 END IF;
2332 END LOOP;
2333
2334 x_rec_count := l_item_tbl.COUNT;
2335 p_item_tbl := l_item_tbl;
2336
2337 IF DPP_DEBUG_HIGH_ON THEN
2338 DPP_UTILITY_PVT.debug_message ('get_product(): x_return_status: ' || x_return_status);
2339 END IF;
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 x_return_status := fnd_api.g_ret_sts_unexp_error;
2343
2344 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2345 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2346 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.Get_Product');
2347 fnd_message.set_token('ERRNO', sqlcode);
2348 fnd_message.set_token('REASON', sqlerrm);
2349 FND_MSG_PUB.ADD;
2350 END IF;
2351 END Get_Product;
2352
2353 --To be used incase we are storing the log messages in the fnd_log_messages table
2354 --Currently all debug messages are going into the DPP_LOG_MESSAGES table
2355
2356 /*
2357 PROCEDURE debug_message (p_log_level IN NUMBER,
2358 p_module_name IN VARCHAR2,
2359 p_text IN VARCHAR2)
2360 IS
2361
2362 BEGIN
2363
2364 IF( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2365 FND_LOG.STRING(p_log_level, p_module_name, p_text);
2366 END IF;
2367
2368 END debug_message;
2369
2370 */
2371
2372 PROCEDURE debug_message(
2373 p_message_text IN VARCHAR2,
2374 p_message_level IN NUMBER := NULL
2375 )
2376 IS
2377 PRAGMA AUTONOMOUS_TRANSACTION;
2378 BEGIN
2379
2380 INSERT INTO DPP_LOG_MESSAGES(LOG_ID,LOG_MESSAGE) VALUES(DPP_DEBUG_LOG_ID_SEQ.nextval, p_message_text);
2381 COMMIT;
2382
2383 FND_MESSAGE.set_name('DPP', 'DPP_API_DEBUG_MESSAGE');
2384 FND_MESSAGE.set_token('TEXT', REPLACE (p_message_text, FND_API.G_MISS_CHAR, 'G_MISS_CHAR'));
2385 FND_MSG_PUB.add;
2386
2387 END debug_message;
2388
2389 PROCEDURE error_message(
2390 p_message_name VARCHAR2,
2391 p_token_name VARCHAR2 := NULL,
2392 P_token_value VARCHAR2 := NULL
2393 )
2394 IS
2395 BEGIN
2396 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2397 FND_MESSAGE.set_name('DPP', p_message_name);
2398 IF p_token_name IS NOT NULL THEN
2399 FND_MESSAGE.set_token(p_token_name, p_token_value);
2400 END IF;
2401 FND_MSG_PUB.add;
2402 END IF;
2403 END error_message;
2404
2405 PROCEDURE get_EmailAddress(
2406 p_user_id IN NUMBER
2407 ,x_email_address OUT NOCOPY VARCHAR2
2408 ,x_return_status OUT NOCOPY VARCHAR2
2409 )
2410 AS
2411 CURSOR Get_Mail_ID_csr (p_user_id IN NUMBER)
2412 IS
2413 select email_address
2414 from fnd_user
2415 where user_id = p_user_id;
2416
2417
2418 l_user_id NUMBER := p_user_id;
2419
2420 BEGIN
2421 x_return_status := fnd_api.g_ret_sts_success;
2425 FND_MESSAGE.set_token('ID', 'User ID');
2422
2423 IF l_user_id IS NULL THEN
2424 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
2426 FND_MSG_PUB.add;
2427 RAISE FND_API.G_EXC_ERROR;
2428 ELSE
2429
2430 FOR Get_Mail_ID_rec IN Get_Mail_ID_csr(l_user_id)
2431 LOOP
2432 x_email_address := Get_Mail_ID_rec.email_address;
2433 END LOOP;
2434
2435 END IF;
2436
2437 IF x_email_address IS NULL THEN
2438 x_return_status := FND_API.G_RET_STS_ERROR;
2439 END IF;
2440
2441 EXCEPTION
2442 WHEN FND_API.G_EXC_ERROR THEN
2443 x_return_status := FND_API.G_RET_STS_ERROR;
2444 WHEN OTHERS THEN
2445 x_return_status := fnd_api.g_ret_sts_unexp_error;
2446
2447 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2448 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2449 fnd_message.set_token('ROUTINE', 'DPP_UTILITY_PVT.get_EmailAddress');
2450 fnd_message.set_token('ERRNO', sqlcode);
2451 fnd_message.set_token('REASON', sqlerrm);
2452 FND_MSG_PUB.ADD;
2453 END IF;
2454 END get_EmailAddress;
2455
2456 PROCEDURE convert_currency(
2457 p_from_currency IN VARCHAR2
2458 ,p_to_currency IN VARCHAR2
2459 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
2460 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
2461 ,p_conv_date IN DATE DEFAULT SYSDATE
2462 ,p_from_amount IN NUMBER
2463 ,x_return_status OUT NOCOPY VARCHAR2
2464 ,x_to_amount OUT NOCOPY NUMBER
2465 ,x_rate OUT NOCOPY NUMBER)
2466 IS
2467 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'DPP_CURRENCY_CONVERSION_TYPE';
2468 l_user_rate CONSTANT NUMBER := 1;
2469 -- Currenty not used.
2470 -- this should be a profile
2471 l_max_roll_days CONSTANT NUMBER := -1;
2472 -- Negative so API rolls back to find the last conversion rate.
2473 -- this should be a profile
2474 l_denominator NUMBER; -- Not used in Marketing.
2475 l_numerator NUMBER; -- Not used in Marketing.
2476 l_conversion_type VARCHAR2(30); -- Curr conversion type; see API doc for details.
2477
2478 BEGIN
2479 -- Initialize return status.
2480 x_return_status := fnd_api.g_ret_sts_success;
2481 -- condition added to pass conversion types
2482 IF p_conv_type = FND_API.G_MISS_CHAR THEN
2483 -- Get the currency conversion type from profile option
2484 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2485 -- Conversion type cannot be null in profile
2486 IF l_conversion_type IS NULL THEN
2487 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2488 fnd_message.set_name('DPP', 'DPP_NO_EXCHANGE_TYPE');
2489 fnd_msg_pub.add;
2490 END IF;
2491 x_return_status := fnd_api.g_ret_sts_error;
2492 RETURN;
2493 END IF;
2494 ELSE
2495 l_conversion_type := p_conv_type;
2496 END IF;
2497
2498 -- Call the proper GL API to convert the amount.
2499 gl_currency_api.convert_closest_amount(
2500 x_from_currency => p_from_currency
2501 ,x_to_currency => p_to_currency
2502 ,x_conversion_date => p_conv_date
2503 ,x_conversion_type => l_conversion_type
2504 ,x_user_rate => p_conv_rate
2505 ,x_amount => p_from_amount
2506 ,x_max_roll_days => l_max_roll_days
2507 ,x_converted_amount => x_to_amount
2508 ,x_denominator => l_denominator
2509 ,x_numerator => l_numerator
2510 ,x_rate => x_rate);
2511 --
2512
2513 EXCEPTION
2514 WHEN gl_currency_api.no_rate THEN
2515 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2516 fnd_message.set_name('DPP', 'DPP_NO_RATE');
2517 fnd_message.set_token('CURRENCY_FROM', p_from_currency);
2518 fnd_message.set_token('CURRENCY_TO', p_to_currency);
2519 fnd_msg_pub.add;
2520 END IF;
2521
2522 x_return_status := fnd_api.g_ret_sts_error;
2523 WHEN gl_currency_api.invalid_currency THEN
2524 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2525 fnd_message.set_name('DPP', 'DPP_INVALID_CURR');
2526 fnd_message.set_token('CURRENCY_FROM', p_from_currency);
2527 fnd_message.set_token('CURRENCY_TO', p_to_currency);
2528 fnd_msg_pub.add;
2529 END IF;
2530
2531 x_return_status := fnd_api.g_ret_sts_error;
2532 WHEN OTHERS THEN
2533 x_return_status := fnd_api.g_ret_sts_unexp_error;
2534 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
2535 FND_MESSAGE.SET_TOKEN('ROUTINE', 'DPP_UTILITY_PVT.convert_currency');
2536 FND_MESSAGE.SET_TOKEN('ERRNO', sqlcode);
2537 FND_MESSAGE.SET_TOKEN('REASON', sqlerrm);
2538 FND_MSG_PUB.ADD;
2539 DPP_UTILITY_PVT.debug_message(sqlerrm);
2540
2541 END convert_currency;
2542
2543 PROCEDURE calculate_functional_curr(
2544 p_from_amount IN NUMBER
2545 ,p_conv_date IN DATE DEFAULT SYSDATE
2546 ,p_tc_currency_code IN VARCHAR2
2547 ,p_org_id IN NUMBER DEFAULT NULL
2548 ,x_to_amount OUT NOCOPY NUMBER
2549 ,x_set_of_books_id OUT NOCOPY NUMBER
2550 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
2551 ,x_fc_currency_code OUT NOCOPY VARCHAR2
2555 IS
2552 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
2553 ,x_exchange_rate IN OUT NOCOPY NUMBER
2554 ,x_return_status OUT NOCOPY VARCHAR2)
2556 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'DPP_CURRENCY_CONVERSION_TYPE';
2557 l_user_rate CONSTANT NUMBER := 1;
2558 -- Currenty not used. -- this should be a profile
2559 l_max_roll_days CONSTANT NUMBER := -1;
2560 -- Negative so API rolls back to find the last conversion rate.
2561 -- this should be a profile
2562 l_denominator NUMBER; -- Not used in Marketing.
2563 l_numerator NUMBER; -- Not used in Marketing.
2564 l_conversion_type VARCHAR2(30);
2565 l_org_id NUMBER;
2566
2567 -- Cursor to get the primary set_of_books_id ,functional_currency_code
2568 CURSOR c_get_gl_info(p_org_id IN NUMBER)
2569 IS
2570 SELECT gs.set_of_books_id
2571 , gs.currency_code
2572 FROM gl_sets_of_books gs
2573 , hr_operating_units hr
2574 WHERE hr.set_of_books_id = gs.set_of_books_id
2575 AND hr.organization_id = p_org_id;
2576
2577 BEGIN
2578 -- Initialize return status.
2579 x_return_status := fnd_api.g_ret_sts_success;
2580
2581 -- Get the currency conversion type from profile option
2582 IF x_exchange_rate_type IS NULL THEN
2583 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2584 ELSE
2585 l_conversion_type := x_exchange_rate_type;
2586 END IF;
2587
2588 IF l_conversion_type IS NULL THEN
2589 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2590 fnd_message.set_name('DPP', 'DPP_NO_EXCHANGE_TYPE');
2591 fnd_msg_pub.add;
2592 END IF;
2593
2594 x_return_status := fnd_api.g_ret_sts_error;
2595 RETURN;
2596 ELSE
2597 IF ozf_utility_pvt.check_fk_exists('GL_DAILY_CONVERSION_TYPES',
2598 'CONVERSION_TYPE'
2599 ,l_conversion_type) = fnd_api.g_false
2600 THEN
2601 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2602 fnd_message.set_name('DPP', 'DPP_WRONG_CONVERSION_TYPE');
2603 fnd_msg_pub.add;
2604 END IF;
2605 x_return_status := fnd_api.g_ret_sts_error;
2606 RETURN;
2607 END IF;
2608 END IF;
2609
2610 /* use org_id if it is passed,
2611 otherwise get from login session */
2612 IF (p_org_id IS NOT NULL) THEN
2613 l_org_id := p_org_id;
2614 ELSE
2615
2616 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2617
2618 IF l_org_id IS NULL THEN
2619 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2620 fnd_message.set_name('DPP', 'DPP_ORG_ID_NOTFOUND');
2621 fnd_msg_pub.add;
2622 END IF;
2623
2624 RAISE fnd_api.g_exc_error;
2625 END IF;
2626
2627 END IF;
2628
2629 IF (DPP_DEBUG_HIGH_ON) THEN
2630 dpp_utility_pvt.debug_message('debug: start ' || l_org_id);
2631 END IF;
2632
2633 x_mrc_sob_type_code := 'P';
2634 OPEN c_get_gl_info(l_org_id);
2635 FETCH c_get_gl_info INTO x_set_of_books_id, x_fc_currency_code;
2636
2637 IF c_get_gl_info%NOTFOUND THEN
2638 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2639 fnd_message.set_name('DPP', 'DPP_GL_SOB_NOTFOUND');
2640 fnd_msg_pub.add;
2641 END IF;
2642
2643 RAISE fnd_api.g_exc_error;
2644 END IF;
2645
2646 CLOSE c_get_gl_info;
2647 -- Call the proper GL API to convert the amount.
2648 gl_currency_api.convert_closest_amount(
2649 x_from_currency => p_tc_currency_code
2650 ,x_to_currency => x_fc_currency_code
2651 ,x_conversion_date => p_conv_date
2652 ,x_conversion_type => l_conversion_type
2653 ,x_user_rate => x_exchange_rate
2654 ,x_amount => p_from_amount
2655 ,x_max_roll_days => l_max_roll_days
2656 ,x_converted_amount => x_to_amount
2657 ,x_denominator => l_denominator
2658 ,x_numerator => l_numerator
2659 ,x_rate => x_exchange_rate);
2660
2661 x_exchange_rate_type := l_conversion_type;
2662 --
2663
2664 EXCEPTION
2665 WHEN fnd_api.g_exc_error THEN
2666 x_return_status := fnd_api.g_ret_sts_error;
2667 WHEN gl_currency_api.no_rate THEN
2668 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2669 fnd_message.set_name('DPP', 'DPP_NO_RATE');
2670 fnd_msg_pub.add;
2671 END IF;
2672
2673 x_return_status := fnd_api.g_ret_sts_error;
2674 WHEN gl_currency_api.invalid_currency THEN
2675 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2676 fnd_message.set_name('DPP', 'DPP_INVALID_CURR');
2677 fnd_msg_pub.add;
2678 END IF;
2679
2680 x_return_status := fnd_api.g_ret_sts_error;
2681 WHEN OTHERS THEN
2682 x_return_status := fnd_api.g_ret_sts_unexp_error;
2683
2684 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2685 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
2686 FND_MESSAGE.SET_TOKEN('ROUTINE', 'DPP_UTILITY_PVT.Convert_functional_curr');
2687 FND_MESSAGE.SET_TOKEN('ERRNO', sqlcode);
2688 FND_MESSAGE.SET_TOKEN('REASON', sqlerrm);
2689 FND_MSG_PUB.ADD;
2690 END IF;
2691 END calculate_functional_curr;
2692 END DPP_UTILITY_PVT;