[Home] [Help]
PACKAGE BODY: APPS.PO_VERIFY_VENDOR_ELIGIBILITY
Source
1 PACKAGE BODY PO_VERIFY_VENDOR_ELIGIBILITY AS
2 /* $Header: PO_VERIFY_VENDOR_ELIGIBILITY.plb 120.2.12020000.6 2013/03/27 09:19:31 harchand noship $ */
3
4
5 PROCEDURE refresh_details (
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 p_document_type IN VARCHAR2,
10 p_document_id IN NUMBER,
11 p_draft_id IN NUMBER,
12 p_doc_type_class IN VARCHAR2
13 )
14 IS
15 CURSOR c_po_supplier IS
16 SELECT p_document_id doc_id,
17 p_document_type doc_type,
18 p_draft_id draft_id,
19 'Y' select_flag,
20 hdr.vendor_id vendor_id,
21 vendor.vendor_name supplier_name,
22 hdr.vendor_site_id vendor_site_id,
23 vendor_sites.vendor_site_code vendor_site_code,
24 offer.bid_number,
25 vendor_sites.duns_number,
26 PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_HEADER_EXT_ATTRS',
27 pk1_value => hdr.po_header_id,
28 pk2_value => -1,
29 p_attr_grp_int_name => 'SUPPLIER_DTLS',
30 p_attr_int_name => 'TIN Number') tin_number,
31 NULL eligibility_status,
32 NULL received_date,
33 NULL performed_by,
34 NULL Error,
35 NULL approved_without_check,
36 NULL Reason
37 FROM PO_HEADERS_MERGE_V hdr,
38 PO_VENDORS vendor,
39 PO_VENDOR_SITES_ALL vendor_sites,
40 PON_BID_HEADERS offer
41 WHERE hdr.po_header_id = p_document_id AND draft_id = -1
42 AND hdr.vendor_id = vendor.vendor_id(+)
43 AND vendor.vendor_id = vendor_sites.vendor_id(+)
44 AND hdr.vendor_site_id = vendor_sites.vendor_site_id
45 AND hdr.po_header_id = offer.po_header_id(+)
46 AND (p_document_id,p_document_type,p_draft_id, hdr.vendor_id, hdr.vendor_site_id) NOT IN (SELECT doc_id, doc_type, draft_id, vdr_elgb.vendor_id, vdr_elgb.vendor_site_id FROM po_vendor_eligibility vdr_elgb);
47
48 CURSOR c_pon_new_suppliers IS
49 SELECT p_document_id doc_id,
50 p_document_type doc_type,
51 NULL draft_id,
52 'N' select_flag,
53 bh.trading_partner_id vendor_id,
54 bh.trading_partner_name supplier_name,
55 bh.vendor_site_id vendor_site_id,
56 bh.vendor_site_code vendor_site_code,
57 bh.bid_number,
58 (SELECT vendor_sites.duns_number FROM PO_VENDOR_SITES_ALL vendor_sites WHERE vendor_sites.vendor_site_id = bh.vendor_site_id AND ROWNUM=1) duns_number,
59 NULL tin_number,
60 NULL eligibility_status,
61 NULL received_date,
62 NULL performed_by,
63 NULL Error,
64 NULL approved_without_check,
65 NULL Reason
66 FROM PON_AUCTION_HEADERS_ALL_V ah,
67 PON_BID_HEADERS bh,
68 PO_VENDORS vendor
69 WHERE ah.auction_header_id = p_document_id
70 AND ah.auction_header_id = bh.auction_header_id
71 AND bh.trading_partner_id = vendor.vendor_id(+)
72 AND (p_document_id,p_document_type, bh.trading_partner_id, Nvl(bh.vendor_site_id,-99)) NOT IN (SELECT doc_id, doc_type, vdr_elgb.vendor_id, Nvl(vdr_elgb.vendor_site_id,-99) FROM po_vendor_eligibility vdr_elgb);
73
74
75
76 CURSOR c_pon_bidding_parties IS
77 SELECT p_document_id doc_id,
78 p_document_type doc_type,
79 NULL draft_id,
80 'N' select_flag,
81 trading_partner_id vendor_id,
82 trading_partner_name supplier_name,
83 suppliers.vendor_site_id vendor_site_id,
84 suppliers.vendor_site_code vendor_site_code,
85 NULL bid_number,
86 (SELECT vendor_sites.duns_number FROM PO_VENDOR_SITES_ALL vendor_sites WHERE vendor_sites.vendor_site_id = suppliers.vendor_site_id AND ROWNUM=1) duns_number,
87 NULL tin_number,
88 NULL eligibility_status,
89 NULL received_date,
90 NULL performed_by,
91 NULL Error,
92 NULL approved_without_check,
93 NULL Reason
94 FROM PON_BIDDING_PARTIES suppliers, PO_VENDORS vendor
95 WHERE suppliers.auction_header_id = p_document_id
96 AND suppliers.trading_partner_id = vendor.vendor_id(+)
97 AND (p_document_id, p_document_type, suppliers.trading_partner_id, Nvl(suppliers.vendor_site_id, -99)) NOT IN (SELECT doc_id, doc_type, vdr_elgb.vendor_id, Nvl(vdr_elgb.vendor_site_id,-99) FROM po_vendor_eligibility vdr_elgb);
98
99 CURSOR c_pon_movetohistory IS
100 SELECT doc_id,
101 doc_type,
102 draft_id,
103 'N' select_flag,
104 vendor_id,
105 supplier_name,
106 vendor_site_id,
107 vendor_site_code,
108 duns_number,
109 tin_number,
110 bid_number,
111 eligibility_status,
112 received_date,
113 performed_by,
114 error,
115 approved_without_check,
116 reason,
117 last_update_date,
118 last_updated_by,
119 creation_date,
120 created_by,
121 last_update_login
122 FROM po_vendor_eligibility vdr
123 WHERE vdr.doc_id = p_document_id
124 AND vdr.doc_type = p_document_type
125 AND (vdr.vendor_id , Nvl(vdr.vendor_site_id, -99)) NOT IN (SELECT bh.trading_partner_id , Nvl(bh.vendor_site_id,-99)
126 FROM PON_AUCTION_HEADERS_ALL_V ah,
127 PON_BID_HEADERS bh
128 WHERE ah.auction_header_id = p_document_id
129 AND ah.auction_header_id = bh.auction_header_id(+)
130 )
131 AND (vdr.vendor_id , Nvl(vdr.vendor_site_id, -99)) NOT IN (SELECT suppliers.trading_partner_id , Nvl(suppliers.vendor_site_id,-99)
132 FROM PON_BIDDING_PARTIES suppliers
133 WHERE suppliers.auction_header_id = p_document_id
134 );
135
136 CURSOR sol_vendor_check_history IS
137 SELECT p_document_id doc_id,
138 p_document_type doc_type,
139 p_draft_id draft_id,
140 'N' select_flag,
141 vendor_id,
142 supplier_name,
143 vendor_site_id,
144 vendor_site_code,
145 duns_number,
146 tin_number,
147 bid_number,
148 eligibility_status,
149 received_date,
150 performed_by,
151 error,
152 approved_without_check,
153 reason,
154 last_update_date,
155 last_updated_by,
156 creation_date,
157 created_by,
158 last_update_login
159 FROM po_vendor_eligibility_h vdr
160 WHERE vdr.doc_id IN (SELECT auction_header_id FROM pon_bid_item_prices WHERE po_header_id = p_document_id)
161 AND vdr.doc_type = 'SOLICITATION'
162 AND vdr.vendor_id = (SELECT vendor_id FROM po_vendor_eligibility WHERE doc_id = p_document_id AND draft_id = p_draft_id)
163 AND Nvl(vdr.vendor_site_id,-99) = (SELECT Nvl(vendor_site_id,-99) FROM po_vendor_eligibility WHERE doc_id = p_document_id AND draft_id = p_draft_id);
164
165 BEGIN
166
167 IF p_doc_type_class = 'PO' THEN
168
169 FOR c IN c_po_supplier LOOP
170
171 INSERT INTO po_vendor_eligibility
172 (
173 id ,
174 doc_id ,
175 doc_type ,
176 draft_id ,
177 select_flag ,
178 vendor_id ,
179 supplier_name ,
180 vendor_site_id ,
181 vendor_site_code ,
182 duns_number ,
183 tin_number ,
184 bid_number ,
185 eligibility_status ,
186 received_date ,
187 performed_by ,
188 error ,
189 approved_without_check ,
190 reason ,
191 last_update_date ,
192 last_updated_by ,
193 creation_date ,
194 created_by ,
195 last_update_login
196 )
197 VALUES(po_vendor_eligibility_s.NEXTVAL,
198 c.doc_id,
199 c.doc_type,
200 c.draft_id,
201 c.select_flag,
202 c.vendor_id,
203 c.supplier_name,
204 c.vendor_site_id,
205 c.vendor_site_code,
206 c.duns_number,
207 c.tin_number,
208 c.bid_number,
209 c.eligibility_status,
210 c.received_date,
211 c.performed_by,
212 c.error,
213 c.approved_without_check,
214 c.Reason,
215 SYSDATE,
216 Fnd_Global.User_Id,
217 SYSDATE,
218 Fnd_Global.User_Id,
219 Fnd_Global.Login_Id
220 );
221
222 END LOOP;
223
224 FOR c IN sol_vendor_check_history LOOP
225
226 INSERT INTO po_vendor_eligibility_h
227 (
228 id ,
229 doc_id ,
230 doc_type ,
231 draft_id ,
232 select_flag ,
233 vendor_id ,
234 supplier_name ,
235 vendor_site_id ,
236 vendor_site_code ,
237 duns_number ,
238 tin_number ,
239 bid_number ,
240 eligibility_status ,
241 received_date ,
242 performed_by ,
243 error ,
244 approved_without_check ,
245 reason ,
246 last_update_date ,
247 last_updated_by ,
248 creation_date ,
249 created_by ,
250 last_update_login
251 )
252 VALUES (po_vendor_eligibility_s.NEXTVAL,
253 c.doc_id,
254 c.doc_type,
255 c.draft_id,
256 c.select_flag,
257 c.vendor_id,
258 c.supplier_name,
259 c.vendor_site_id,
260 c.vendor_site_code,
261 c.duns_number,
262 c.tin_number,
263 c.bid_number,
264 c.eligibility_status,
265 c.received_date,
266 c.performed_by,
267 c.error,
268 c.approved_without_check,
269 c.reason,
270 c.last_update_date,
271 c.last_updated_by,
272 c.creation_date,
273 c.created_by,
274 c.last_update_login);
275
276 END LOOP;
277
278 END IF;
279
280 IF p_doc_type_class = 'SOURCING' THEN
281
282 FOR c IN c_pon_new_suppliers LOOP
283
284
285
286 INSERT INTO po_vendor_eligibility
287 (
288 id ,
289 doc_id ,
290 doc_type ,
291 draft_id ,
292 select_flag ,
293 vendor_id ,
294 supplier_name ,
295 vendor_site_id ,
296 vendor_site_code ,
297 duns_number ,
298 tin_number ,
299 bid_number ,
300 eligibility_status ,
301 received_date ,
302 performed_by ,
303 error ,
304 approved_without_check ,
305 reason ,
306 last_update_date ,
307 last_updated_by ,
308 creation_date ,
309 created_by ,
310 last_update_login
311 )
312 VALUES(po_vendor_eligibility_s.NEXTVAL,
313 c.doc_id,
314 c.doc_type,
315 c.draft_id,
316 c.select_flag,
317 c.vendor_id,
318 c.supplier_name,
319 c.vendor_site_id,
320 c.vendor_site_code,
321 c.duns_number,
322 c.tin_number,
323 c.bid_number,
324 c.eligibility_status,
325 c.received_date,
326 c.performed_by,
327 c.error,
328 c.approved_without_check,
329 c.Reason,
330 SYSDATE,
331 Fnd_Global.User_Id,
332 SYSDATE,
333 Fnd_Global.User_Id,
334 Fnd_Global.Login_Id
335 );
336
337 END LOOP;
338
339 FOR c IN c_pon_bidding_parties LOOP
340
341
342 INSERT INTO po_vendor_eligibility
343 (
344 id ,
345 doc_id ,
346 doc_type ,
347 draft_id ,
348 select_flag ,
349 vendor_id ,
350 supplier_name ,
351 vendor_site_id ,
352 vendor_site_code ,
353 duns_number ,
354 tin_number ,
355 bid_number ,
356 eligibility_status ,
357 received_date ,
358 performed_by ,
359 error ,
360 approved_without_check ,
361 reason ,
362 last_update_date ,
363 last_updated_by ,
364 creation_date ,
365 created_by ,
366 last_update_login
367 )
368 VALUES(po_vendor_eligibility_s.NEXTVAL,
369 c.doc_id,
370 c.doc_type,
371 c.draft_id,
372 c.select_flag,
373 c.vendor_id,
374 c.supplier_name,
375 c.vendor_site_id,
376 c.vendor_site_code,
377 c.duns_number,
378 c.tin_number,
379 c.bid_number,
380 c.eligibility_status,
381 c.received_date,
382 c.performed_by,
383 c.error,
384 c.approved_without_check,
385 c.Reason,
386 SYSDATE,
387 Fnd_Global.User_Id,
388 SYSDATE,
389 Fnd_Global.User_Id,
390 Fnd_Global.Login_Id
391 );
392
393 END LOOP;
394
395 FOR c IN c_pon_movetohistory LOOP
396
397 INSERT INTO po_vendor_eligibility_h
398 (
399 id ,
400 doc_id ,
401 doc_type ,
402 draft_id ,
403 select_flag ,
404 vendor_id ,
405 supplier_name ,
406 vendor_site_id ,
407 vendor_site_code ,
408 duns_number ,
409 tin_number ,
410 bid_number ,
411 eligibility_status ,
412 received_date ,
413 performed_by ,
414 error ,
415 approved_without_check ,
416 reason ,
417 last_update_date ,
418 last_updated_by ,
419 creation_date ,
420 created_by ,
421 last_update_login
422 )
423 VALUES (po_vendor_eligibility_s.NEXTVAL,
424 c.doc_id,
425 c.doc_type,
426 c.draft_id,
427 c.select_flag,
428 c.vendor_id,
429 c.supplier_name,
430 c.vendor_site_id,
431 c.vendor_site_code,
432 c.duns_number,
433 c.tin_number,
434 c.bid_number,
435 c.eligibility_status,
436 c.received_date,
437 c.performed_by,
438 c.error,
439 c.approved_without_check,
440 c.reason,
441 c.last_update_date,
442 c.last_updated_by,
443 c.creation_date,
444 c.created_by,
445 c.last_update_login);
446
447 DELETE FROM po_vendor_eligibility
448 WHERE doc_id = c.doc_id
449 AND doc_type = c.doc_type
450 AND vendor_id = c.vendor_id
451 AND Nvl(vendor_site_code,'aa') = Nvl(c.vendor_site_code,'aa');
452
453 END LOOP;
454
455 END IF;
456
457
458
459 COMMIT;
460
461 EXCEPTION WHEN OTHERS THEN
462
463 NULL;
464 END refresh_details;
465
466 FUNCTION get_lookup_value (p_lookup_code VARCHAR2) RETURN VARCHAR2 IS
467
468 CURSOR c_get_lookup_value IS
469 SELECT meaning
470 FROM fnd_lookups
471 WHERE lookup_type = 'PO_VENDOR_CHECK_STATUS'
472 AND (SYSDATE BETWEEN start_date_active AND Nvl(end_date_active,SYSDATE+1))
473 AND lookup_code = p_lookup_code ;
474
475 l_lookup_value VARCHAR2(30):= NULL ;
476
477 BEGIN
478
479 OPEN c_get_lookup_value;
480 FETCH c_get_lookup_value INTO l_lookup_value;
481 CLOSE c_get_lookup_value;
482
483 RETURN l_lookup_value;
484
485 END get_lookup_value;
486
487 FUNCTION gen_xml_payload ( p_business_name VARCHAR2 DEFAULT NULL,
488 p_duns_number VARCHAR2 DEFAULT NULL,
489 p_ssn_number VARCHAR2 DEFAULT NULL
490 ) RETURN CLOB IS
491
492 l_event_data clob;
493 l_text VARCHAR2(32000);
494
495 BEGIN
496
497 dbms_lob.createtemporary(l_event_data
498 ,FALSE
499 ,dbms_lob.CALL);
500
501 IF p_ssn_number IS NOT NULL THEN
502
503 l_text := '<ns1:doSsnSearch_3_15 xmlns:ns1="http://ws.epls.gsa.gov"><query xsi:type="ns3:OperationSearch" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns3="urn:BeanService" xmlns:urn="urn:BeanService">';
504 dbms_lob.writeappend(l_event_data
505 ,length(l_text)
506 ,l_text);
507
508 IF p_business_name IS NOT NULL THEN
509 l_text := '<exactName xsi:type="xsd:string">'||p_business_name||'</exactName>';
510 dbms_lob.writeappend(l_event_data
511 ,length(l_text)
512 ,l_text);
513 END IF;
514
515 IF p_ssn_number IS NOT NULL THEN
516 l_text := '<ssnOrTin xsi:type="xsd:string">'||p_ssn_number||'</ssnOrTin>';
517 dbms_lob.writeappend(l_event_data
518 ,length(l_text)
519 ,l_text);
520 END IF;
521
522 l_text := '</query></ns1:doSsnSearch_3_15>';
523 dbms_lob.writeappend(l_event_data
524 ,length(l_text)
525 ,l_text);
526
527
528 ELSE
529
530 l_text := '<ns1:doSearch_3_15 xmlns:ns1="http://ws.epls.gsa.gov"><query xsi:type="ns3:OperationSearch" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns3="urn:BeanService" xmlns:urn="urn:BeanService">';
531 dbms_lob.writeappend(l_event_data
532 ,length(l_text)
533 ,l_text);
534
535 IF p_business_name IS NOT NULL THEN
536 l_text := '<exactName xsi:type="xsd:string">'||p_business_name||'</exactName>';
537 dbms_lob.writeappend(l_event_data
538 ,length(l_text)
539 ,l_text);
540 END IF;
541
542 IF p_duns_number IS NOT NULL THEN
543 l_text := '<duns xsi:type="xsd:string">'||p_duns_number||'</duns>';
544 dbms_lob.writeappend(l_event_data
545 ,length(l_text)
546 ,l_text);
547 END IF;
548
549 l_text := '</query></ns1:doSearch_3_15>';
550 dbms_lob.writeappend(l_event_data
551 ,length(l_text)
552 ,l_text);
553
554
555 END IF;
556
557 RETURN l_event_data;
558
559 END gen_xml_payload;
560
561 FUNCTION BLOB_TO_CLOB(p_text_in_word IN BLOB) RETURN CLOB
562 IS
563
564 l_api_name VARCHAR2(30) := 'BLOB_TO_CLOB';
565 v_clob CLOB; --Terms
566 v_varchar VARCHAR2(32767);
567 v_start PLS_INTEGER := 1;
568 v_buffer PLS_INTEGER := 32767;
569 l_xpath VARCHAR2(1000);
570
571 BEGIN
572
573 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
574
575 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_text_in_word) / v_buffer) LOOP
576 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_text_in_word, v_buffer, v_start));
577 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
578 v_start := v_start + v_buffer;
579 END LOOP;
580
581 RETURN v_clob;
582 EXCEPTION WHEN OTHERS THEN
583 NULL;
584 END BLOB_TO_CLOB;
585
586 FUNCTION Clob_to_blob(p_clob IN CLOB) return BLOB AS
587 l_api_name VARCHAR2(30) := 'Clob_to_blob';
588 l_art_blob BLOB;
589 v_in Pls_Integer := 1;
590 v_out Pls_Integer := 1;
591 v_lang Pls_Integer := 0;
592 v_warning Pls_Integer := 0;
593 BEGIN
594
595 DBMS_LOB.CREATETEMPORARY(l_art_blob, FALSE);
596
597 DBMS_LOB.convertToBlob(l_art_blob,p_clob,DBMS_lob.getlength(p_clob),
598 v_in,v_out,DBMS_LOB.default_csid,v_lang,v_warning);
599
600 return l_art_blob;
601 EXCEPTION WHEN OTHERS THEN
602 NULL;
603
604 END Clob_to_blob;
605
606
607 PROCEDURE vendor_check (
608 x_return_status OUT NOCOPY VARCHAR2,
609 x_msg_count OUT NOCOPY NUMBER,
610 x_msg_data OUT NOCOPY VARCHAR2,
611 p_document_type IN VARCHAR2,
612 p_document_id IN NUMBER,
613 p_draft_id IN NUMBER,
614 p_doc_type_class IN VARCHAR2
615 )
616
617 IS
618
619 CURSOR c_get_selected_suppliers IS
620 SELECT id , eligibility_status , supplier_name , vendor_site_code , duns_number , tin_number, vendor_site_id, vendor_id
621 FROM po_vendor_eligibility
622 WHERE doc_id = p_document_id
623 AND doc_type = p_document_type
624 AND Nvl(draft_id,-1) = Nvl(p_draft_id, -1)
625 AND select_flag = 'Y'
626 AND p_doc_type_class = 'SOURCING'
627 UNION
628 SELECT id , eligibility_status , supplier_name , vendor_site_code , duns_number , tin_number, vendor_site_id, vendor_id
629 FROM po_vendor_eligibility
630 WHERE doc_id = p_document_id
631 AND doc_type = p_document_type
632 AND Nvl(draft_id,-1) = Nvl(p_draft_id, -1)
633 AND p_doc_type_class = 'PO';
634
635 l_lookup_code VARCHAR2(30):=NULL;
636
637 l_event_key VARCHAR2(300);
638 l_event_data clob;
639 l_event_name varchar2(250);
640
641 ParamList wf_parameter_list_t := wf_parameter_list_t();
642 l_param wf_parameter_t;
643 l_parameter_index NUMBER := 0;
644
645 l_message varchar2(10);
646
647 CURSOR get_error(p_id NUMBER) IS
648 SELECT error
649 FROM po_Vendor_eligibility
650 WHERE id = p_id;
651
652 l_error VARCHAR2(4000);
653 l_fnd_lob_seq NUMBER;
654 l_blob BLOB;
655
656 BEGIN
657
658 FOR c IN c_get_selected_suppliers LOOP
659
660 IF c.eligibility_status IS NOT NULL OR l_error IS NOT NULL THEN
661 INSERT INTO po_vendor_eligibility_h
662 (
663 id ,
664 doc_id ,
665 doc_type ,
666 draft_id ,
667 select_flag ,
668 vendor_id ,
669 supplier_name ,
670 vendor_site_id ,
671 vendor_site_code ,
672 duns_number ,
673 tin_number ,
674 bid_number ,
675 eligibility_status ,
676 received_date ,
677 performed_by ,
678 error ,
679 approved_without_check ,
680 reason ,
681 file_id_request ,
682 file_id_response ,
683 last_update_date ,
684 last_updated_by ,
685 creation_date ,
686 created_by ,
687 last_update_login
688 )
689 SELECT po_vendor_eligibility_h_s.NEXTVAL,
690 doc_id,
691 doc_type,
692 draft_id,
693 select_flag,
694 vendor_id,
695 supplier_name,
696 vendor_site_id,
697 vendor_site_code,
698 duns_number,
699 tin_number,
700 bid_number,
701 eligibility_status,
702 received_date,
703 performed_by,
704 error,
705 approved_without_check,
706 reason,
707 file_id_request,
708 file_id_response,
709 last_update_date,
710 last_updated_by,
711 creation_date,
712 created_by,
713 last_update_login
714 FROM po_vendor_eligibility
715 WHERE id = c.id;
716
717 END IF;
718
719
720 SELECT po_epls_evnt_s.NEXTVAL INTO l_event_key FROM dual;
721 l_event_key:='EPLS'||l_event_key;
722 l_event_name := 'oracle.apps.po.integration.epls.post';
723 l_message := wf_event.test(l_event_name);
724
725
726 l_event_data := gen_xml_payload(p_business_name => c.supplier_name,
727 p_duns_number => c.duns_number ,
728 p_ssn_number => c.tin_number);
729
730
731 wf_event.AddParameterToList( p_name => 'ID'
732 , p_value => c.id
733 , p_parameterList => ParamList );
734
735 wf_event.AddParameterToList( p_name => 'PO_EPLS_DOC_ID'
736 , p_value => p_document_id
737 , p_parameterList => ParamList );
738
739 wf_event.AddParameterToList( p_name => 'PO_EPLS_DOC_TYPE'
740 , p_value => p_document_type
741 , p_parameterList => ParamList );
742
743 wf_event.AddParameterToList( p_name => 'PO_EPLS_DRAFT_ID'
744 , p_value => p_draft_id
745 , p_parameterList => ParamList );
746
747 wf_event.AddParameterToList( p_name => 'PO_EPLS_DOCTYPE_CLASS'
748 , p_value => p_doc_type_class
749 , p_parameterList => ParamList );
750
751
752 wf_event.AddParameterToList( p_name => 'PO_EPLS_SUPPLIER_NAME'
753 , p_value => c.supplier_name
754 , p_parameterList => ParamList );
755
756 wf_event.AddParameterToList( p_name => 'PO_EPLS_VENDOR_SITE'
757 , p_value => c.vendor_site_code
758 , p_parameterList => ParamList );
759
760 wf_event.AddParameterToList( p_name => 'PO_EPLS_DUNS_NUMBER'
761 , p_value => c.duns_number
762 , p_parameterList => ParamList );
763
764 wf_event.AddParameterToList( p_name => 'PO_EPLS_TIN_NUMBER'
765 , p_value => c.tin_number
766 , p_parameterList => ParamList );
767
768 wf_event.Raise( p_event_name => l_event_name
769 , p_event_key => l_event_key
770 , p_parameters => ParamList
771 , p_event_data => l_event_data );
772
773 ParamList.DELETE;
774
775 l_fnd_lob_seq := fnd_lobs_s.NEXTVAL;
776 l_blob := clob_to_blob(l_event_data);
777
778 INSERT INTO fnd_lobs
779 (
780 file_id,
781 file_name,
782 file_content_type,
783 file_data,
784 upload_date,
785 expiration_date,
786 program_name,
787 program_tag,
788 language,
789 oracle_charset,
790 file_format
791 )
792 VALUES
793 (l_fnd_lob_seq,
794 'Verify Vendor Eligibility Check Request XML',
795 'text/xml',
796 l_blob,
797 SYSDATE,
798 NULL,
799 NULL,
800 NULL,
801 null,
802 null,
803 'text');
804
805
806 l_lookup_code:=get_lookup_value('SUBMITTED');
807
808 UPDATE po_vendor_eligibility
809 SET eligibility_status = l_lookup_code,
810 received_date = SYSDATE,
811 performed_by = Fnd_Global.User_Id,
812 file_id_request = l_fnd_lob_seq,
813 last_update_date = SYSDATE,
814 last_updated_by = Fnd_Global.User_Id,
815 last_update_login = Fnd_Global.Login_Id,
816 select_flag = 'N'
817 WHERE id = c.id;
818
819 OPEN get_error(c.id);
820 FETCH get_error INTO l_error;
821 CLOSE get_error;
822
823
824 END LOOP;
825
826 COMMIT;
827
828
829 EXCEPTION WHEN OTHERS THEN
830 NULL;
831 END vendor_check;
832
833 PROCEDURE verify_vendor_check(
834 x_invalid_suppliers OUT NOCOPY VARCHAR2,
835 p_auction_header_id IN NUMBER
836 ) IS
837
838 CURSOR c_get_org_id IS
839 SELECT org_id
840 FROM pon_auction_headers_all
841 WHERE auction_header_id = p_auction_header_id;
842
843 l_org_id NUMBER;
844
845
846 CURSOR c_vendor_check_params IS
847 SELECT ENABLE_EPLS , Trunc(EPLS_TIMEFRAME) FROM po_system_parameters_all WHERE org_id = l_org_id;
848
849 l_enable_epls VARCHAR2(1):='N';
850 l_epls_timeframe NUMBER;
851
852 CURSOR c_eligibility_status_lookup(cp_lookup_code VARCHAR2) IS
853 SELECT meaning
854 FROM fnd_lookups
855 WHERE lookup_type = 'PO_VENDOR_CHECK_STATUS'
856 AND lookup_code = cp_lookup_code
857 AND (SYSDATE BETWEEN start_date_active AND Nvl(end_date_active,SYSDATE+1));
858
859 l_eligible VARCHAR2(30);
860 l_ineligible VARCHAR2(30);
861 l_eligibility VARCHAR2(30):='NOTRUN';
862 l_supplier_name VARCHAR2(100);
863
864 CURSOR c_vendor_eligibility IS
865 SELECT supplier_name, (CASE
866 WHEN eligibility_status = l_eligible AND Trunc((SYSDATE - received_Date)*24) <= l_epls_timeframe THEN 'ELIGIBLE'
867 WHEN approved_without_check = 'Y' THEN 'APPROVED'
868 WHEN eligibility_status = l_ineligible AND Trunc((SYSDATE - received_Date)*24) <= l_epls_timeframe THEN 'INELIGIBLE'
869 ELSE 'NOTRUN' END) supplierstatus
870 FROM po_vendor_eligibility
871 WHERE doc_id = p_auction_header_id
872 AND doc_type = 'SOLICITATION'
873 --AND vendor_id IN (SELECT trading_partner_id FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id AND award_status IN ('AWARDED','PARTIAL'));
874 AND ((vendor_id IN (SELECT trading_partner_id FROM pon_bid_headers WHERE auction_header_id = p_auction_header_id))
875 OR
876 (vendor_id IN (SELECT trading_partner_id FROM PON_BIDDING_PARTIES WHERE auction_header_id = p_auction_header_id)));
877
878 l_no_rec VARCHAR2(1):='Y';
879
880 BEGIN
881
882
883
884 OPEN c_get_org_id;
885 FETCH c_get_org_id INTO l_org_id;
886 CLOSE c_get_org_id;
887
888 OPEN c_vendor_check_params;
889 FETCH c_vendor_check_params INTO l_enable_epls, l_epls_timeframe;
890 CLOSE c_vendor_check_params;
891
892 IF l_enable_epls = 'Y' THEN
893
894 OPEN c_eligibility_status_lookup('ELIGIBLE');
895 FETCH c_eligibility_status_lookup INTO l_eligible;
896 CLOSE c_eligibility_status_lookup;
897
898 OPEN c_eligibility_status_lookup('INELIGIBLE');
899 FETCH c_eligibility_status_lookup INTO l_ineligible;
900 CLOSE c_eligibility_status_lookup;
901
902
903 FOR c IN c_vendor_eligibility LOOP
904 l_no_rec := 'N';
905 IF c.supplierstatus IN ('INELIGIBLE','NOTRUN') THEN
906 IF x_invalid_suppliers IS NOT NULL OR Length(x_invalid_suppliers) > 0 THEN
907 x_invalid_suppliers := x_invalid_suppliers || '@' || c.supplier_name || '#' || c.supplierstatus;
908 ELSE
909 x_invalid_suppliers := c.supplier_name || '#' || c.supplierstatus;
910 END IF;
911 END IF;
912 END LOOP;
913
914 IF l_no_rec = 'Y' THEN
915 x_invalid_suppliers := 'NOTRUN';
916 END IF;
917
918 END IF;
919
920 END verify_vendor_check;
921
922 FUNCTION PROCESS_EPLS_RESPONSE(p_subscription_guid in raw,
923 p_event in out NOCOPY WF_EVENT_T)
924 RETURN VARCHAR2 IS
925 l_module VARCHAR2(100) := 'PROCESS_FBO_RESPONSE';
926 l_response_clob CLOB;
927 l_response_xml xmltype;
928 l_result VARCHAR2(100);
929 l_res_message VARCHAR2(4000);
930
931 l_id VARCHAR2(100);
932 l_supplier_name VARCHAR2(100);
933 l_lookup_code VARCHAR2(30);
934 l_doc_type_class VARCHAR2(30);
935
936 l_supplier_status VARCHAR2(30) :='ELIGIBLE';
937 l_error VARCHAR2(4000);
938
939 l_result CLOB;
940 l_result_xml xmltype;
941 l_success_flag VARCHAR2(400);
942 l_count NUMBER;
943
944 l_error_msg VARCHAR2(4000):=NULL;
945
946 CURSOR get_supp_details IS
947 SELECT EXTRACTVALUE (VALUE (a), '*//city') city,
948 EXTRACTVALUE (VALUE (a), '*//country') country,
949 EXTRACTVALUE (VALUE (a), '*//duns') duns_number,
950 EXTRACTVALUE (VALUE (a), '*//province') province,
951 EXTRACTVALUE (VALUE (a), '*//state') state,
952 EXTRACTVALUE (VALUE (a), '*//street1') address_line1,
953 EXTRACTVALUE (VALUE (a), '*//street2') address_line2,
954 EXTRACTVALUE (VALUE (a), '*//zip') zip ,
955 EXTRACTVALUE (VALUE (a), '*//classification') classification,
956 EXTRACTVALUE (VALUE (a), '*//exclusionType') exclusionType,
957 EXTRACTVALUE (VALUE (a), '*/first') first,
958 EXTRACTVALUE (VALUE (a), '*/last') last,
959 EXTRACTVALUE (VALUE (a), '*/middle') middle,
960 EXTRACTVALUE (VALUE (a), '*/name') name
961 FROM TABLE (XMLSEQUENCE (EXTRACT (l_result_xml,'*//results/results'))) a ;
962
963 CURSOR c_get_vendor_site_details(p_id VARCHAR2) IS
964 SELECT CITY,COUNTRY,vdr.DUNS_NUMBER,PROVINCE,STATE,ADDRESS_LINE1,ADDRESS_LINE2,ZIP,SUPPLIER_NAME
965 FROM po_vendor_eligibility vdr, po_vendor_sites_all site
966 WHERE id = to_number(p_id)
967 AND vdr.vendor_site_id = site.vendor_site_id;
968
969 l_fnd_lob_seq NUMBER;
970 l_blob BLOB;
971
972 BEGIN
973
974 l_response_clob := p_event.getEventData;
975
976 l_id := p_event.getValueForParameter('ID');
977
978 l_fnd_lob_seq := fnd_lobs_s.NEXTVAL;
979 l_blob := clob_to_blob(l_response_clob);
980
981
982 INSERT INTO fnd_lobs
983 (
984 file_id,
985 file_name,
986 file_content_type,
987 file_data,
988 upload_date,
989 expiration_date,
990 program_name,
991 program_tag,
992 language,
993 oracle_charset,
994 file_format
995 )
996 VALUES
997 (l_fnd_lob_seq,
998 'Verify Vendor Eligibility Check Response XML',
999 'text/xml',
1000 l_blob,
1001 SYSDATE,
1002 NULL,
1003 NULL,
1004 NULL,
1005 null,
1006 null,
1007 'text');
1008
1009 UPDATE po_vendor_eligibility
1010 SET file_id_response = l_fnd_lob_seq
1011 WHERE id = l_id;
1012 COMMIT;
1013
1014 l_supplier_name := p_event.getValueForParameter('PO_EPLS_SUPPLIER_NAME');
1015
1016
1017 l_result_xml := XMLType(l_response_clob);
1018
1019 IF l_result_xml.existsnode('//successful') > 0 THEN
1020
1021 l_success_flag := l_result_xml.extract('//successful/text()').getStringVal();
1022
1023 IF l_success_flag = 'true' THEN
1024
1025 IF l_result_xml.existsnode('//count') > 0 THEN
1026
1027 l_count := l_result_xml.extract('//count/text()').getNumberVal();
1028
1029
1030
1031 IF l_count > 0 THEN
1032
1033 FOR c IN get_supp_details LOOP
1034
1035
1036
1037 FOR c2 IN c_get_vendor_site_details(l_id) LOOP
1038
1039
1040
1041 IF Nvl(c2.city,'$$') = Nvl(c.city,'##') AND
1042 Nvl(c2.country,'$$') = Nvl(c.country,'##') AND
1043 Nvl(c2.state,'$$') = Nvl(c.state,'##') AND
1044 Nvl(c2.province,'$$') = Nvl(c.province,'##') AND
1045 Nvl(c2.zip,'$$') = Nvl(c.zip,'##') AND
1046 l_supplier_name = c2.SUPPLIER_NAME THEN
1047
1048 l_supplier_status := 'INELIGIBLE';
1049
1050
1051 END IF;
1052
1053 END LOOP;
1054
1055 END LOOP;
1056
1057 ELSE
1058 NULL;
1059
1060
1061 END IF;
1062 END IF;
1063 ELSE
1064 l_error_msg := l_result_xml.extract('//errorMessage/text()').getStringVal();
1065
1066
1067
1068 END IF;
1069
1070 END IF;
1071
1072 IF l_error_msg IS NOT NULL THEN
1073
1074 l_supplier_status := '';
1075
1076
1077 END IF;
1078
1079 l_lookup_code:=get_lookup_value(l_supplier_status);
1080
1081
1082
1083 UPDATE po_vendor_eligibility
1084 SET eligibility_status = l_lookup_code,
1085 received_date = SYSDATE,
1086 -- performed_by = Fnd_Global.User_Id,
1087 last_update_date = SYSDATE,
1088 -- last_updated_by = Fnd_Global.User_Id,
1089 -- last_update_login = Fnd_Global.Login_Id,
1090 select_flag = 'N',
1091 error = l_error_msg
1092 WHERE id = l_id;
1093 COMMIT;
1094
1095 RETURN 'SUCCESS';
1096
1097 COMMIT;
1098
1099 END PROCESS_EPLS_RESPONSE;
1100
1101
1102 FUNCTION is_modaction_valid (p_po_header_id NUMBER, p_draft_id NUMBER)
1103 RETURN VARCHAR2 IS
1104
1105 CURSOR exists_exercised_options IS
1106 SELECT 'Y'
1107 FROM po_lines_merge_v modif
1108 WHERE po_header_id = p_po_header_id
1109 AND draft_id = p_draft_id
1110 AND CLM_EXERCISED_FLAG = 'Y'
1111 AND EXISTS (SELECT 1
1112 FROM po_lines_merge_v base
1113 WHERE modif.po_line_id = base.po_line_id
1114 AND po_header_id = p_po_header_id
1115 AND draft_id = -1
1116 AND Nvl(CLM_EXERCISED_FLAG,'N')='N');
1117
1118 CURSOR exists_new_lines IS
1119 SELECT 'Y'
1120 FROM po_lines_merge_v
1121 WHERE po_header_id = p_po_header_id
1122 AND draft_id = p_draft_id
1123 AND CHANGE_STATUS = 'NEW';
1124
1125 CURSOR exists_pop_increase IS
1126 SELECT 'N' FROM dual;
1127
1128 CURSOR c_not_par IS
1129 SELECT 'Y'
1130 FROM po_drafts
1131 WHERE draft_id = p_draft_id
1132 AND draft_type = 'MOD';
1133
1134 /*SELECT 'Y'
1135 FROM po_line_locations_merge_v modif
1136 WHERE po_header_id = p_po_header_id
1137 AND draft_id = p_draft_id
1138 AND CLM_POP_DURATION > (SELECT CLM_POP_DURATION
1139 FROM po_line_locations_merge_v base
1140 WHERE modif.po_line_location_id = base.po_line_location_id
1141 AND po_header_id = p_po_header_id
1142 AND draft_id = -1
1143 );*/
1144
1145 l_exercised_options VARCHAR2(1):='N';
1146 l_added_lines VARCHAR2(1):='N';
1147 l_pop_increase VARCHAR2(1):='N';
1148 l_not_par VARCHAR2(1):='N';
1149
1150 BEGIN
1151
1152 OPEN exists_exercised_options;
1153 FETCH exists_exercised_options INTO l_exercised_options;
1154 CLOSE exists_exercised_options;
1155
1156
1157 OPEN exists_new_lines;
1158 FETCH exists_new_lines INTO l_added_lines;
1159 CLOSE exists_new_lines;
1160
1161 OPEN exists_pop_increase;
1162 FETCH exists_pop_increase INTO l_pop_increase;
1163 CLOSE exists_pop_increase;
1164
1165 OPEN c_not_par;
1166 FETCH c_not_par INTO l_not_par;
1167 CLOSE c_not_par;
1168
1169 IF (l_exercised_options = 'Y' OR l_added_lines = 'Y' OR l_pop_increase = 'Y') AND l_not_par = 'Y' THEN
1170 RETURN 'Y';
1171 END IF;
1172
1173 RETURN 'N';
1174 END IS_MODACTION_VALID;
1175
1176 FUNCTION IS_WEBSERVICE_DOWN (p_po_header_id NUMBER, p_draft_id NUMBER, p_document_type varchar2)
1177 RETURN VARCHAR2
1178 is
1179 CURSOR cur IS
1180 SELECT 'Y'
1181 FROM po_vendor_eligibility
1182 WHERE eligibility_status = 'Submitted'
1183 AND RECEIVED_DATE < (SYSDATE - (1/24))
1184 AND doc_id = p_po_header_id
1185 AND doc_type = p_document_type
1186 AND draft_id = p_draft_id;
1187
1188 l_flag VARCHAR2(1):='N';
1189
1190 begin
1191
1192 OPEN cur;
1193 FETCH cur INTO l_flag;
1194 CLOSE cur;
1195
1196 RETURN l_flag;
1197
1198 END IS_WEBSERVICE_DOWN;
1199
1200
1201
1202 END PO_VERIFY_VENDOR_ELIGIBILITY;