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