1 PACKAGE BODY PO_VENDOR_SITES_SV AS
2 /* $Header: POXVDVSB.pls 120.13.12010000.2 2008/08/04 08:33:55 rramasam ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
6
7 --==============================================================================
8 -- FUNCTION : get_vendor_site_id -- <GA FPI>
9 -- TYPE : Private
10 --
11 -- REQUIRES : p_po_header_id must be a valid document ID.
12 -- MODIFIES : -
13 --
14 -- DESCRIPTION : Gets the vendor_site_id specified on a particular PO document.
15 --
16 -- PARAMETERS : p_po_header_id - document ID
17 --
18 -- RETURNS : vendor_site_id specified for the p_po_header_id
19 -- NULL if no Supplier Site is specified for the p_po_header_id
20 -- or if the p_po_header_id does not exist
21 --
22 -- EXCEPTIONS : -
23 --==============================================================================
24 FUNCTION get_vendor_site_id
25 (
26 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
27 )
28 RETURN PO_HEADERS_ALL.vendor_site_id%TYPE
29 IS
30 x_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
31 BEGIN
32
33 SELECT vendor_site_id
34 INTO x_vendor_site_id
35 FROM po_headers_all
36 WHERE po_header_id = p_po_header_id;
37
38 return (x_vendor_site_id);
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 return (NULL);
43
44 END get_vendor_site_id;
45
46
47 --------------------------------------------------------------------------------
48 --Start of Comments
49 --Name: val_vendor_site_id
50 --Pre-reqs:
51 -- None.
52 --Modifies:
53 -- FND_MESSAGE on error.
54 --Locks:
55 -- None.
56 --Function:
57 -- Checks if p_vendor_site_id is an active purchasing supplier site in
58 -- p_org_id. If p_org_id is NULL, then the current OU is used for validation.
59 -- If the p_document_type is a purchase order ('PO', 'BLANKET'), then the
60 -- supplier site cannot be an RFQ only site.
61 --Parameters:
62 --IN:
63 --p_document_type
64 -- The document type to validate p_vendor_site_id with.
65 --p_vendor_site_id
66 --p_org_id
67 -- The operating unit ID associated with this vendor site, or NULL to use the
68 -- current OU.
69 --Returns:
70 -- TRUE if p_vendor_site_id is valid,
71 -- FALSE otherwise
72 --End of Comments
73 --------------------------------------------------------------------------------
74 FUNCTION val_vendor_site_id
75 (
76 p_document_type IN VARCHAR2,
77 p_vendor_site_id IN NUMBER,
78 p_org_id IN NUMBER --< Shared Proc FPJ >
79 )
80 return BOOLEAN
81 IS
82
83 l_progress varchar2(3) := NULL;
84 l_vendor_site_id_v number := NULL;
85
86 BEGIN
87
88 l_progress := '010';
89
90 /* Check if the given Supplier Site is active. Also check
91 ** that the Site is not an RFQ Only site if validating for use on
92 ** a purchase order (requisitions, rfqs and quotations can use
93 ** RFQ only sites).
94 */
95
96 --< Shared Proc FPJ Start >
97 IF (p_org_id IS NULL) THEN
98
99 --SQL What: Query org-striped view to validate vendor site ID
100 --SQL Why: No org_id specified, so validate in current OU.
101 SELECT vendor_site_id
102 INTO l_vendor_site_id_v
103 FROM po_vendor_sites
104 WHERE vendor_site_id = p_vendor_site_id
105 AND SYSDATE < NVL(inactive_date, SYSDATE + 1)
106 AND purchasing_site_flag = 'Y'
107 AND ( ( p_document_type IN ('PO', 'BLANKET')
108 AND NVL(rfq_only_site_flag,'N') = 'N'
109 )
110 OR (p_document_type IN ('REQ', 'MENU', 'RFQ', 'QUOTATION'))
111 );
112
113 ELSE
114
115 --SQL What: Query base table to validate vendor site ID
116 --SQL Why: Validate site in specified org_id
117 SELECT vendor_site_id
118 INTO l_vendor_site_id_v
119 FROM po_vendor_sites_all
120 WHERE vendor_site_id = p_vendor_site_id
121 AND org_id = p_org_id
122 AND SYSDATE < NVL(inactive_date, SYSDATE + 1)
123 AND purchasing_site_flag = 'Y'
124 AND ( ( p_document_type IN ('PO', 'BLANKET')
125 AND NVL(rfq_only_site_flag,'N') = 'N'
126 )
127 OR (p_document_type IN ('REQ', 'MENU', 'RFQ', 'QUOTATION'))
128 );
129
130 END IF;
131 --< Shared Proc FPJ End >
132
133 return (TRUE);
134
135 EXCEPTION
136
137 when no_data_found then
138 return (FALSE);
139 when others then
140 po_message_s.sql_error('PO_VENDOR_SITES_SV.val_vendor_site',l_progress,sqlcode);
141 raise;
142
143 END val_vendor_site_id;
144
145 /*===========================================================================
146
147 PROCEDURE NAME: get_def_vendor_site()
148
149 ===========================================================================*/
150
151 PROCEDURE get_def_vendor_site(X_vendor_id IN NUMBER,
152 X_vendor_site_id OUT NOCOPY number,
153 X_vendor_site_code OUT NOCOPY varchar2,
154 X_document_type IN varchar2 ) IS
155
156 X_vendor_site_count number;
157 X_progress varchar2(3) := '';
158
159 /* The foll. variable has been introduced as all of a sudden
160 ** although it is all on the server side, this function resulted
161 ** in ORA-6502 errors - For now, defining this temp variable
162 ** and copying it back thru the argument seems to be working ... */
163
164 X_temp_vendor_site_code varchar2(15);
165 BEGIN
166 X_Progress := '010';
167
168 /* Get the number of ACTIVE PURCHASING SITES
169 ** and those that are not ONLY RFQ SITES
170 ** for the give vendor
171 **
172 ** MS 11/14/95: Added if then else to support processing
173 ** for RFQs and Quotations.
174 ** (took out rfq_only_site_flag condition)
175 */
176
177 if (X_document_type not in ('RFQ', 'QUOTATION')) then
178
179 select count(vendor_site_id),
180 max(vendor_site_code),
181 max(vendor_site_id)
182 into X_vendor_site_count,
183 X_temp_vendor_site_code,
184 X_vendor_site_id
185 from po_vendor_sites
186 where vendor_id = X_vendor_id
187 and purchasing_site_flag = 'Y'
188 and sysdate < nvl(inactive_date, sysdate + 1)
189 and nvl(rfq_only_site_flag, 'N') <> 'Y' ;
190
191 else
192 select count(vendor_site_id),
193 max(vendor_site_code),
194 max(vendor_site_id)
195 into X_vendor_site_count,
196 X_temp_vendor_site_code,
197 X_vendor_site_id
198 from po_vendor_sites
199 where vendor_id = X_vendor_id
200 and purchasing_site_flag = 'Y'
201 and sysdate < nvl(inactive_date, sysdate + 1);
202
203 end if;
204
205 /* If there are more than 1 active vendor sites,
206 ** we cannot determine the default, we return
207 ** NULL instead */
208
209 if X_vendor_site_count <> 1 then
210 X_vendor_site_id := '';
211 X_vendor_site_code := '';
212 else
213 X_vendor_site_code := X_temp_vendor_site_code;
214 end if;
215
216 EXCEPTION
217 when too_many_rows then
218 X_vendor_site_id := '';
219 X_vendor_site_code := '';
220 when no_data_found then
221 X_vendor_site_id := '';
222 X_vendor_site_code := '';
223
224 WHEN OTHERS THEN
225 po_message_s.sql_error('get_def_vendor_site', X_progress, sqlcode);
226 raise;
227
228 END get_def_vendor_site;
229
230 /*===========================================================================
231
232 PROCEDURE NAME: get_vendor_site_info()
233
234 ===========================================================================*/
235
236 PROCEDURE get_vendor_site_info(X_vendor_site_id IN number,
237 X_vs_ship_to_location_id IN OUT NOCOPY number,
238 X_vs_bill_to_location_id IN OUT NOCOPY number,
239 X_vs_ship_via_lookup_code IN OUT NOCOPY varchar2,
240 X_vs_fob_lookup_code IN OUT NOCOPY varchar2,
241 X_vs_pay_on_code IN OUT NOCOPY varchar2,
242 X_vs_freight_terms_lookup_code IN OUT NOCOPY varchar2,
243 X_vs_terms_id IN OUT NOCOPY number,
244 X_vs_invoice_currency_code IN OUT NOCOPY varchar2,
245 x_vs_shipping_control IN OUT NOCOPY VARCHAR2 -- <INBOUND LOGISTICS FPJ>
246 ) IS
247
248 cursor C is select nvl(ship_to_location_id,X_vs_ship_to_location_id),
249 nvl(bill_to_location_id,X_vs_bill_to_location_id),
250 ship_via_lookup_code ,
251 fob_lookup_code,
252 pay_on_code,
253 freight_terms_lookup_code,
254 terms_id ,
255 invoice_currency_code,
256 shipping_control -- <INBOUND LOGISTICS FPJ>
257 from po_vendor_sites_all --<Shared Proc FPJ>
258 where vendor_site_id = X_vendor_site_id;
259
260 X_progress varchar2(3) := '';
261
262 BEGIN
263
264 if (X_vendor_site_id is not null) then
265 X_progress := '010';
266 open C;
267 X_progress := '020';
268
269 /* Get the other vendor site attributes for a given vendor site
270 ** If there is no such vendor site, these attributes will have
271 ** NULL value in them */
272
273 fetch C into X_vs_ship_to_location_id ,
274 X_vs_bill_to_location_id ,
275 X_vs_ship_via_lookup_code ,
276 X_vs_fob_lookup_code ,
277 X_vs_pay_on_code,
278 X_vs_freight_terms_lookup_code ,
279 X_vs_terms_id ,
280 X_vs_invoice_currency_code,
281 x_vs_shipping_control; -- <INBOUND LOGISTICS FPJ>
282 close C;
283
284 else
285
286 X_vs_ship_to_location_id := '' ;
287 X_vs_bill_to_location_id := '';
288 X_vs_ship_via_lookup_code := '' ;
289 X_vs_fob_lookup_code := '' ;
290 X_vs_pay_on_code := '';
291 X_vs_freight_terms_lookup_code := '' ;
292 X_vs_terms_id := '';
293 X_vs_invoice_currency_code := '';
294 x_vs_shipping_control := ''; -- <INBOUND LOGISTICS FPJ>
295
296 end if;
297
298 exception
299 when others then
300 po_message_s.sql_error('get_vendor_site_info', X_progress, sqlcode);
301 raise;
302
303 END get_vendor_site_info;
304
305 /*===========================================================================
306
307 PROCEDURE NAME: val_vendor_site()
308
309 ===========================================================================*/
310
311 PROCEDURE val_vendor_site (X_vendor_id IN number,
312 X_vendor_site_id IN number,
313 X_org_id IN number,
314 X_set_of_books_id IN number,
315 X_res_ship_to_loc_id IN OUT NOCOPY number,
316 X_ship_to_loc_dsp IN OUT NOCOPY varchar2,
317 X_ship_org_code IN OUT NOCOPY varchar2,
318 X_ship_org_name IN OUT NOCOPY varchar2,
319 X_ship_org_id IN OUT NOCOPY number,
320 X_res_bill_to_loc_id IN OUT NOCOPY number ,
321 X_bill_to_loc_dsp IN OUT NOCOPY varchar2,
322 X_res_fob IN OUT NOCOPY varchar2 ,
323 X_res_pay_on_code IN OUT NOCOPY varchar2,
324 X_res_ship_via IN OUT NOCOPY varchar2 ,
325 X_res_freight_terms IN OUT NOCOPY varchar2 ,
326 X_res_terms_id IN OUT NOCOPY number,
327 X_res_invoice_currency_code IN OUT NOCOPY varchar2,
328 X_fob_dsp IN OUT NOCOPY varchar2,
329 X_pay_on_dsp IN OUT NOCOPY varchar2,
330 X_ship_via_dsp IN OUT NOCOPY varchar2,
331 X_freight_terms_dsp IN OUT NOCOPY varchar2,
332 X_terms_dsp IN OUT NOCOPY varchar2,
333 X_vendor_contact_id IN OUT NOCOPY number,
334 X_vendor_contact_name IN OUT NOCOPY varchar2,
335 x_res_shipping_control IN OUT NOCOPY VARCHAR2 -- <INBOUND LOGISTICS FPJ>
336 )
337 IS
338
339 X_progress varchar2(3) := '';
340
341 X_type_1099 varchar2(10);
342 X_hold_flag varchar2(1);
343 X_ship_to_location_id number;
344 X_bill_to_location_id number;
345 X_ship_via_lookup_code varchar2(25);
346 -- Bug: 1710995 Define the codes according to the definition in the table.
347 X_fob_lookup_code po_lookup_codes.lookup_code%TYPE;
348 X_freight_terms_lookup_code po_lookup_codes.lookup_code%TYPE;
349 X_terms_id number;
350 X_invoice_currency_code varchar2(15);
351 X_receipt_required_flag varchar2(1);
352 X_num_1099 varchar2(30);
353 /*
354 ** BUGNO 718328.
355 ** X_vat_registration_num varchar2(15);
356 ** changed to varchar2(20).
357 */
358 X_vat_registration_num varchar2(20);
359 X_inspection_required_flag varchar2(1);
360
361 X_vs_ship_to_location_id number;
362 X_vs_bill_to_location_id number;
363 X_vs_ship_via_lookup_code varchar2(25);
364 -- Bug: 1710995 Define the codes according to the definition in the table.
365 X_vs_fob_lookup_code po_lookup_codes.lookup_code%TYPE;
366 X_vs_freight_terms_lookup_code po_lookup_codes.lookup_code%TYPE;
367 X_vs_pay_on_code varchar2(25);
368 X_vs_terms_id number;
369 X_vs_invoice_currency_code varchar2(15);
370 l_vs_shipping_control PO_LOOKUP_CODES.lookup_code%TYPE := NULL; -- <INBOUND LOGISTICS FPJ>
371
372 BEGIN
373 --dbms_output.put_line('Before get_vendor_info');
374
375 /* Get Vendor Information. May be required for
376 ** defaulting values if the vendor site info
377 ** is invalid */
378
379 po_vendors_sv.get_vendor_info (X_vendor_id ,
380 X_ship_to_location_id ,
381 X_bill_to_location_id ,
382 X_ship_via_lookup_code ,
383 X_fob_lookup_code ,
384 X_freight_terms_lookup_code ,
385 X_terms_id ,
386 X_type_1099 ,
387 X_hold_flag ,
388 X_invoice_currency_code ,
389 X_receipt_required_flag ,
390 X_num_1099 ,
391 X_vat_registration_num,
392 X_inspection_required_flag );
393
394
395 --dbms_output.put_line('Before get_vendor_Site_info');
396
397
398 /* Get vendor site information */
399
400 get_vendor_site_info(X_vendor_site_id ,
401 X_vs_ship_to_location_id ,
402 X_vs_bill_to_location_id ,
403 X_vs_ship_via_lookup_code ,
404 X_vs_fob_lookup_code ,
405 X_vs_pay_on_code,
406 X_vs_freight_terms_lookup_code ,
407 X_vs_terms_id ,
408 X_vs_invoice_currency_code,
409 l_vs_shipping_control -- <INBOUND LOGISTICS FPJ>
410 ) ;
411
412 /* Validate Invoice currency code with vendor site info first.
413 ** If this turns out to be invalid, use the vendor information */
414
415
416 if X_vs_invoice_currency_code is not null then
417 X_res_invoice_currency_code := X_vs_invoice_currency_code;
418 else
419 X_res_invoice_currency_code := X_invoice_currency_code;
420 end if;
421
422 /* Validating FOB lookup code from vendor site if available.
423 ** If not available or vendor site fob is inactive, validate the vendor fob */
424
425 if X_vs_fob_lookup_code is not null then
426 po_vendors_sv.val_fob(X_vs_fob_lookup_code , X_res_fob);
427 end if;
428 if ((X_res_fob is null) and (X_fob_lookup_code is not null)) then
429 po_vendors_sv.val_fob(X_fob_lookup_code , X_res_fob);
430 end if;
431
432 /* Validating Freight Terms lookup code from vendor site if available.
433 ** If not available or vendor site Freight Terms is inactive,
434 ** validate the vendor Freight Terms */
435
436 if X_vs_freight_terms_lookup_code is not null then
437 po_vendors_sv.val_freight_terms( X_vs_freight_terms_lookup_code,
438 X_res_freight_terms);
439 end if;
440
441 if ((X_res_freight_terms is null) and
442 (X_freight_terms_lookup_code is not null)) then
443 po_vendors_sv.val_freight_terms( X_freight_terms_lookup_code,
444 X_res_freight_terms);
445 end if;
446
447 /* Validating Ship Via lookup code from vendor site if available.
448 ** If not available or vendor site Ship Via is inactive,
449 ** validate the vendor Ship Via */
450
451 if X_vs_ship_via_lookup_code is not null then
452 po_vendors_sv.val_freight_carrier(X_vs_ship_via_lookup_code,
453 X_org_id,
454 X_res_ship_via);
455 end if;
456 if ((X_res_ship_via is null) and
457 (X_ship_via_lookup_code is not null)) then
458 po_vendors_sv.val_freight_carrier(X_ship_via_lookup_code,
459 X_org_id,
460 X_res_ship_via);
461 end if;
462
463 /* Validating Terms Id from vendor site if available.
464 ** If not available or vendor site Terms Id is inactive,
465 ** validate the vendor Terms Id */
466
467 if X_vs_terms_id is not null then
468 po_terms_sv.val_ap_terms(X_vs_terms_id, X_res_terms_id);
469 end if;
470
471 if ((X_res_terms_id is null) and
472 (X_terms_id is not null)) then
473 po_terms_sv.val_ap_terms(X_terms_id, X_res_terms_id);
474 end if;
475
476
477 /* Validating shipping control from vendor site */
478 PO_VENDORS_SV.val_shipping_control
479 (
480 p_temp_shipping_control => l_vs_shipping_control,
481 x_res_shipping_control => x_res_shipping_control
482 ); -- <INBOUND LOGISTICS FPJ>
483
484 /* Obtain displayed values for the valid ids */
485
486 po_vendors_sv.get_displayed_values(X_res_fob,
487 X_res_freight_terms,
488 X_res_ship_via,
489 X_res_terms_id,
490 X_fob_dsp,
491 X_freight_terms_dsp,
492 X_ship_via_dsp,
493 X_terms_dsp,
494 X_org_id);
495
496 /* Get display value for pay on */
497
498 X_res_pay_on_code := X_vs_pay_on_code;
499
500 if X_res_pay_on_code is not null then
501 po_core_s.get_displayed_value('PAY ON CODE',
502 X_res_pay_on_code,
503 X_pay_on_dsp);
504 else
505 X_pay_on_dsp := '';
506 end if;
507
508 /* Validate Ship To location with vendor site info first.
509 ** If it is invalid/null, use the vendor info to get it.
510 ** Also bring in the appropriate atrributes */
511
512 if X_vs_ship_to_location_id is not null then
513 po_vendors_sv.get_ship_to_loc_attributes (X_vs_ship_to_location_id,
514 X_ship_to_loc_dsp,
515 X_ship_org_code, X_ship_org_name ,
516 X_ship_org_id, X_set_of_books_id );
517 X_res_ship_to_loc_id := X_vs_ship_to_location_id;
518 end if;
519
520 if (X_ship_to_loc_dsp is null)
521 and (X_ship_to_location_id is not null) then
522 po_vendors_sv.get_ship_to_loc_attributes ( X_ship_to_location_id ,
523 X_ship_to_loc_dsp ,
524 X_ship_org_code , X_ship_org_name ,
525 X_ship_org_id ,X_set_of_books_id );
526 X_res_ship_to_loc_id := X_ship_to_location_id;
527 end if;
528
529 /* Validate Bill To location with vendor site info first.
530 ** If it is invalid/null, use the vendor info to get it.
531 ** Also bring in the appropriate atrributes */
532
533
534 if X_vs_bill_to_location_id is not null then
535 po_vendors_sv.get_bill_to_loc_attributes ( X_vs_bill_to_location_id ,
536 X_bill_to_loc_dsp );
537 X_res_bill_to_loc_id := X_vs_bill_to_location_id;
538 end if;
539
540
541 if (X_bill_to_loc_dsp is null)
542 and (X_bill_to_location_id is not null) then
543 po_vendors_sv.get_bill_to_loc_attributes ( X_bill_to_location_id ,
544 X_bill_to_loc_dsp);
545 X_res_bill_to_loc_id := X_bill_to_location_id;
546 end if;
547
548 /* Default the Vendor Contact if possible */
549
550 po_vendor_contacts_sv.get_vendor_contact(X_vendor_site_id,
551 X_vendor_contact_id,
552 X_vendor_contact_name);
553
554 exception
555 when others then
556 po_message_s.sql_error('val_vendor_site', X_progress, sqlcode);
557 raise;
558
559 END val_vendor_site;
560
561 /*===================================================================
562
563 PROCEDURE : get_vendor_site_name()
564
565 ======================================================================*/
566
567
568
569 PROCEDURE get_vendor_site_name
570 (X_vendor_site_id IN NUMBER,
571 X_vendor_site_name IN OUT NOCOPY VARCHAR2) IS
572
573 X_progress varchar2(3) := '';
574
575 /* Get the vendor site associated with the vendor site id
576 */
577
578 CURSOR C is
579 SELECT PVS.vendor_site_code
580 FROM PO_VENDOR_SITES_ALL PVS --<Shared Proc FPJ>
581 WHERE PVS.vendor_site_id = X_vendor_site_id;
582
583 BEGIN
584
585 --dbms_output.put_line('Before open cursor');
586
587 if (X_vendor_site_id is not null) then
588 X_progress := '010';
589 OPEN C;
590 X_progress := '020';
591
592 FETCH C into X_vendor_site_name;
593
594 CLOSE C;
595
596 --dbms_output.put_line('Vendor Site Name'||X_vendor_site_name);
597
598 end if;
599
600 EXCEPTION
601 when others then
602 --dbms_output.put_line('In exception');
603 po_message_s.sql_error('get_vendor_site_name', X_progress, sqlcode);
604
605 END get_vendor_site_name;
606
607 /*===================================================================
608
609 PROCEDURE : derive_vendor_site_info()
610
611 ======================================================================*/
612
613 PROCEDURE derive_vendor_site_info (
614 p_vendor_site_record IN OUT NOCOPY rcv_shipment_header_sv.VendorSiteRecType) IS
615
616 cid INTEGER;
617 rows_processed INTEGER;
618 sql_str VARCHAR2(2000);
619
620 vendor_site_code_null BOOLEAN := TRUE;
621 vendor_id_null BOOLEAN := TRUE;
622 /* organization_id_null BOOLEAN := TRUE; Bug 607639 */
623 vendor_site_id_null BOOLEAN := TRUE;
624
625 BEGIN
626
627 sql_str := 'SELECT vendor_site_id, vendor_id, vendor_site_code,org_id FROM po_vendor_sites WHERE ';
628
629 IF p_vendor_site_record.vendor_site_code IS NULL and
630 p_vendor_site_record.vendor_site_id IS NULL THEN
631
632 p_vendor_site_record.error_record.error_status := 'W';
633 RETURN;
634
635 END IF;
636
637 IF p_vendor_site_record.vendor_site_id IS NOT NULL and
638 p_vendor_site_record.vendor_site_code IS NOT NULL THEN
639
640 p_vendor_site_record.error_record.error_status := 'S';
641 RETURN;
642
643 END IF;
644
645 IF p_vendor_site_record.vendor_site_id IS NOT NULL THEN
646
647 sql_str := sql_str || ' vendor_site_id = :v_site_id and';
648 vendor_site_id_null := FALSE;
649
650 END IF;
651
652 IF p_vendor_site_record.vendor_site_code IS NOT NULL THEN
653
654 sql_str := sql_str || ' vendor_site_code = :v_site_code and';
655 vendor_site_code_null := FALSE;
656
657 END IF;
658
659 IF p_vendor_site_record.vendor_id IS NOT NULL THEN
660
661 sql_str := sql_str || ' vendor_id = :v_id and';
662 vendor_id_null := FALSE;
663
664 END IF;
665
666 /* IF p_vendor_site_record.organization_id IS NOT NULL THEN
667
668 sql_str := sql_str || ' org_id = :v_organization_id and';
669 organization_id_null := FALSE;
670
671 END IF; bug 607639 org_id is actually the operating unit */
672
673 sql_str := substr(sql_str,1,length(sql_str)-3);
674
675 --dbms_output.put_line(substr(sql_str,1,255));
676 --dbms_output.put_line(substr(sql_str,256,255));
677 --dbms_output.put_line(substr(sql_str,513,255));
678
679 cid := dbms_sql.open_cursor;
680
681 dbms_sql.parse(cid, sql_str , dbms_sql.native);
682
683 dbms_sql.define_column(cid,1,p_vendor_site_record.vendor_site_id);
684 dbms_sql.define_column(cid,2,p_vendor_site_record.vendor_id);
685 dbms_sql.define_column(cid,3,p_vendor_site_record.vendor_site_code,40);
686 dbms_sql.define_column(cid,4,p_vendor_site_record.organization_id);
687
688 IF NOT vendor_site_id_null THEN
689
690 dbms_sql.bind_variable(cid,'v_site_id',p_vendor_site_record.vendor_site_id);
691
692 END IF;
693
694 IF NOT vendor_id_null THEN
695
696 dbms_sql.bind_variable(cid,'v_id',p_vendor_site_record.vendor_id);
697
698 END IF;
699
700 IF NOT vendor_site_code_null THEN
701
702 dbms_sql.bind_variable(cid,'v_site_code',p_vendor_site_record.vendor_site_code);
703
704 END IF;
705
706 /* IF NOT organization_id_null THEN
707
708 dbms_sql.bind_variable(cid,'v_organization_id',p_vendor_site_record.organization_id);
709
710 END IF; bug 607639 */
711
712 rows_processed := dbms_sql.execute_and_fetch(cid);
713
714 IF rows_processed = 1 THEN
715
716 IF vendor_site_id_null THEN
717 dbms_sql.column_value(cid,1,p_vendor_site_record.vendor_site_id);
718 END IF;
719
720 IF vendor_site_code_null THEN
721 dbms_sql.column_value(cid,3,p_vendor_site_record.vendor_site_code);
722 END IF;
723
724 p_vendor_site_record.error_record.error_status := 'S';
725
726 ELSIF rows_processed = 0 THEN
727
728 p_vendor_site_record.error_record.error_status := 'W';
729
730 ELSE
731
732 p_vendor_site_record.error_record.error_status := 'W';
733
734 END IF;
735
736 IF dbms_sql.is_open(cid) THEN
737 dbms_sql.close_cursor(cid);
738 END IF;
739
740 EXCEPTION
741 WHEN others THEN
742
743 IF dbms_sql.is_open(cid) THEN
744 dbms_sql.close_cursor(cid);
745 END IF;
746
747 p_vendor_site_record.error_record.error_status := 'U';
748 p_vendor_site_record.error_record.error_message := sqlerrm;
749 IF (g_asn_debug = 'Y') THEN
750 asn_debug.put_line(p_vendor_site_record.error_record.error_message);
751 END IF;
752
753 END derive_vendor_site_info;
754
755 -- Bug# 3532503
756 PROCEDURE validate_remit_to_site
757 (
758 p_remit_to_site_id IN NUMBER,
759 x_error_status OUT NOCOPY VARCHAR2,
760 x_error_message OUT NOCOPY VARCHAR2
761 )
762 IS
763 l_hold_all_payments_flag PO_VENDOR_SITES_ALL.hold_all_payments_flag%TYPE;
764 l_pay_site_flag PO_VENDOR_SITES_ALL.pay_site_flag%TYPE;
765 BEGIN
766 IF p_remit_to_site_id IS NULL THEN
767 x_error_status := 'E';
768 x_error_message := 'All blanks';
769 RETURN;
770 END IF;
771
772 SELECT hold_all_payments_flag, pay_site_flag
773 INTO l_hold_all_payments_flag, l_pay_site_flag
774 FROM PO_VENDOR_SITES
775 WHERE vendor_site_id = p_remit_to_site_id;
776
777 -- Check for hold payments flag
778 IF nvl(l_hold_all_payments_flag, 'N') = 'Y' THEN
779 x_error_status := 'E';
780 x_error_message := 'VEN_SITE_HOLD_PMT';
781 RETURN;
782 END IF;
783
784 -- Check for Pay on Receipt site flag
785 IF nvl(l_pay_site_flag, 'Y') = 'N' THEN
786 x_error_status := 'E';
787 x_error_message := 'VEN_SITE_NOT_POR_SITE';
788 RETURN;
789 END IF;
790
791 x_error_status := 'S';
792 x_error_message := NULL;
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 x_error_status := 'E';
796 x_error_message := 'VEN_SITE_ID';
797 WHEN TOO_MANY_ROWS THEN
798 x_error_status := 'E';
799 x_error_message := 'TOOMANYROWS';
800 WHEN OTHERS THEN
801 RAISE;
802 END validate_remit_to_site;
803
804 /*===================================================================
805
806 PROCEDURE : validate_vendor_site_info()
807
808 ======================================================================*/
809
810 PROCEDURE validate_vendor_site_info
811 (p_vendor_site_record IN OUT NOCOPY rcv_shipment_header_sv.VendorSiteRecType,
812 p_remit_to_site_id NUMBER DEFAULT NULL) -- Bug# 3532503
813 IS
814
815 X_cid INTEGER;
816 X_rows_processed INTEGER;
817 X_sql_str VARCHAR2(2000) := 'SELECT povs.inactive_date, povs.purchasing_site_flag, povs.pay_site_flag , povs.hold_all_payments_flag FROM po_vendor_sites povs where ';
818
819 X_vendor_site_code_null BOOLEAN := TRUE;
820 X_vendor_id_null BOOLEAN := TRUE;
821 -- X_organization_id_null BOOLEAN := TRUE; bug 621385
822 X_vendor_site_id_null BOOLEAN := TRUE;
823
824 X_sysdate DATE := sysdate;
825 X_inactive_date DATE;
826 X_purchasing_site_flag VARCHAR2(1);
827 X_pay_site_flag VARCHAR2(1);
828 X_hold_all_payments_flag VARCHAR2(1);
829
830 BEGIN
831
832 IF p_vendor_site_record.vendor_site_code IS NULL and
833 p_vendor_site_record.vendor_site_id IS NULL THEN
834
835 --dbms_output.put_line('No values');
836 p_vendor_site_record.error_record.error_status := 'E';
837 p_vendor_site_record.error_record.error_message := 'All blanks';
838 RETURN;
839
840 END IF;
841
842 IF p_vendor_site_record.vendor_site_id IS NOT NULL THEN
843
844 X_sql_str := X_sql_str || ' vendor_site_id = :v_site_id and';
845 X_vendor_site_id_null := FALSE;
846
847 END IF;
848
849 IF p_vendor_site_record.vendor_site_code IS NOT NULL THEN
850
851 X_sql_str := X_sql_str || ' vendor_site_code = :v_site_code and';
852 X_vendor_site_code_null := FALSE;
853
854 END IF;
855
856 IF p_vendor_site_record.vendor_id IS NOT NULL THEN
857
858 X_sql_str := X_sql_str || ' vendor_id = :v_id and';
859 X_vendor_id_null := FALSE;
860
861 END IF;
862
863 /* IF p_vendor_site_record.organization_id IS NOT NULL THEN
864
865 X_sql_str := X_sql_str || ' org_id = :v_organization_id and';
866 X_organization_id_null := FALSE;
867
868 END IF; bug 621385 org_id is actually the operating unit*/
869
870 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
871
872 --dbms_output.put_line(substr(X_sql_str,1,255));
873 --dbms_output.put_line(substr(X_sql_str,256,255));
874 --dbms_output.put_line(substr(X_sql_str,513,255));
875
876 X_cid := dbms_sql.open_cursor;
877
878 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
879
880 dbms_sql.define_column(X_cid,1,X_inactive_date);
881 dbms_sql.define_column(X_cid,2,X_purchasing_site_flag,1);
882 dbms_sql.define_column(X_cid,3,X_pay_site_flag,1);
883 dbms_sql.define_column(X_cid,4,X_hold_all_payments_flag,1);
884
885 IF NOT X_vendor_site_id_null THEN
886
887 dbms_sql.bind_variable(X_cid,'v_site_id',p_vendor_site_record.vendor_site_id);
888
889 END IF;
890
891 IF NOT X_vendor_id_null THEN
892
893 dbms_sql.bind_variable(X_cid,'v_id',p_vendor_site_record.vendor_id);
894
895 END IF;
896
897 IF NOT X_vendor_site_code_null THEN
898
899 dbms_sql.bind_variable(X_cid,'v_site_code',p_vendor_site_record.vendor_site_code);
900
901 END IF;
902
903 /* IF NOT X_organization_id_null THEN
904
905 dbms_sql.bind_variable(X_cid,'v_organization_id',p_vendor_site_record.organization_id);
906
907 END IF; bug 621385 */
908
909 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
910
911 IF X_rows_processed = 1 THEN
912
913 dbms_sql.column_value(X_cid,1,X_inactive_date);
914 dbms_sql.column_value(X_cid,2,X_purchasing_site_flag);
915 dbms_sql.column_value(X_cid,3,X_pay_site_flag);
916 dbms_sql.column_value(X_cid,4,X_hold_all_payments_flag);
917
918 -- check whether Vendor_site_id is enabled
919 -- Active if Inactive date is in the future ie > sysdate
920
921 IF nvl(X_inactive_date,X_sysdate+1) < X_sysdate THEN
922
923 --dbms_output.put_line('Disabled Vendor Site');
924 p_vendor_site_record.error_record.error_status := 'E';
925 p_vendor_site_record.error_record.error_message := 'VEN_SITE_DISABLED';
926
927 IF dbms_sql.is_open(X_cid) THEN
928 dbms_sql.close_cursor(X_cid);
929 END IF;
930
931 RETURN;
932
933 END IF;
934
935 -- Bug# 3532503: If remit_to_site id is not null, then perform the
936 -- validations for hold_all_payments_flag and pay_site_flag using
937 -- the remit_to_site_id instead of the vendor_site_id.
938 IF p_remit_to_site_id IS NOT NULL THEN
939 validate_remit_to_site(p_remit_to_site_id => p_remit_to_site_id,
940 x_error_status => p_vendor_site_record.error_record.error_status,
941 x_error_message => p_vendor_site_record.error_record.error_message);
942 -- In case the status is not 'S', close the cursor and return
943 IF (p_vendor_site_record.error_record.error_status <> 'S') THEN
944 IF dbms_sql.is_open(X_cid) THEN
945 dbms_sql.close_cursor(X_cid);
946 END IF;
947 RETURN;
948 END IF;
949 ELSE
950 -- Bug# 3532503: End
951
952 -- Check for hold payments flag
953
954 IF nvl(X_hold_all_payments_flag,'N') = 'Y' THEN
955
956 --dbms_output.put_line('Payment Hold');
957 p_vendor_site_record.error_record.error_status := 'E';
958 p_vendor_site_record.error_record.error_message := 'VEN_SITE_HOLD_PMT';
959
960 IF dbms_sql.is_open(X_cid) THEN
961 dbms_sql.close_cursor(X_cid);
962 END IF;
963
964 RETURN;
965
966 END IF;
967
968 -- Check for Pay on Receipt site flag
969
970 IF nvl(X_pay_site_flag,'Y') = 'N' THEN
971
972 --dbms_output.put_line('Not pay on receipt site');
973 p_vendor_site_record.error_record.error_status := 'E';
974 p_vendor_site_record.error_record.error_message := 'VEN_SITE_NOT_POR_SITE';
975
976 IF dbms_sql.is_open(X_cid) THEN
977 dbms_sql.close_cursor(X_cid);
978 END IF;
979
980 RETURN;
981
982 END IF;
983
984 END IF; -- IF p_remit_to_site_id IS NOT NULL (Bug# 3532503)
985
986 -- Check for purchasing site flag
987
988 IF nvl(X_purchasing_site_flag,'Y') = 'N' THEN
989
990 --dbms_output.put_line('Not purchasing site');
991 p_vendor_site_record.error_record.error_status := 'E';
992 p_vendor_site_record.error_record.error_message := 'VEN_SITE_NOT_PURCH';
993
994 IF dbms_sql.is_open(X_cid) THEN
995 dbms_sql.close_cursor(X_cid);
996 END IF;
997
998 RETURN;
999
1000 END IF;
1001
1002 p_vendor_site_record.error_record.error_status := 'S';
1003 p_vendor_site_record.error_record.error_message := NULL;
1004
1005 ELSIF X_rows_processed = 0 THEN -- No rows found
1006
1007 p_vendor_site_record.error_record.error_status := 'E';
1008 p_vendor_site_record.error_record.error_message := 'VEN_SITE_ID';
1009
1010 ELSE -- More than 1 row found
1011
1012 p_vendor_site_record.error_record.error_status := 'E';
1013 p_vendor_site_record.error_record.error_message := 'TOOMANYROWS';
1014
1015 END IF;
1016
1017 IF dbms_sql.is_open(X_cid) THEN
1018 dbms_sql.close_cursor(X_cid);
1019 END IF;
1020
1021 EXCEPTION
1022 WHEN others THEN
1023 IF dbms_sql.is_open(X_cid) THEN
1024 dbms_sql.close_cursor(X_cid);
1025 END IF;
1026 p_vendor_site_record.error_record.error_status := 'U';
1027 p_vendor_site_record.error_record.error_message := sqlerrm;
1028 IF (g_asn_debug = 'Y') THEN
1029 asn_debug.put_line(p_vendor_site_record.error_record.error_message);
1030 END IF;
1031
1032 END validate_vendor_site_info;
1033
1034 -- Bug 5407459 Added the p_retrieve_only_flag parameter. Also modified the
1035 -- queries in Get_Transmission_Defaults to use the _ALL tables, so that this
1036 -- procedure can be invoked from HTML.
1037
1038 /* RETROACTIVE FPI START */
1039 Procedure Get_Transmission_Defaults(p_document_id IN NUMBER,
1040 p_document_type IN VARCHAR2,
1041 p_document_subtype IN VARCHAR2,
1042 p_preparer_id IN OUT NOCOPY NUMBER,
1043 x_default_method OUT NOCOPY VARCHAR2,
1044 x_email_address OUT NOCOPY VARCHAR2,
1045 x_fax_number OUT NOCOPY VARCHAR2,
1046 x_document_num OUT NOCOPY VARCHAR2,
1047 p_retrieve_only_flag IN VARCHAR2)
1048 IS
1049
1050 l_fax_area po_vendor_sites_all.fax_area_code%type;
1051
1052 l_faxnum varchar2(30); -- Need to change this also as part of bug 5765243
1053
1054 l_party_site_id po_headers_all.vendor_site_id%type;
1055 l_party_id po_headers_all.vendor_id%type;
1056 l_retcode pls_integer;
1057 l_errmsg varchar2(2000);
1058 l_result boolean := FALSE;
1059 l_preparer_id po_headers.agent_id%type;
1060 l_authorization_status po_headers.authorization_status%type;
1061 l_transaction_subtype VARCHAR2(240);
1062
1063 l_progress varchar2(3);
1064 l_consigned_consumption_flag po_headers_all.consigned_consumption_flag%TYPE;
1065 l_api_name CONSTANT VARCHAR2(50) := 'Get_Transmission_Defaults';
1066 l_xml_flag po_headers_all.xml_flag%TYPE; --bug fix 2764348
1067 l_approved_date po_headers_all.approved_date%TYPE; --Bug 6074733
1068
1069
1070 BEGIN
1071 l_progress := '000';
1072
1073 /* Get the authorization_status for the document */
1074 If ((p_document_type = 'PO') OR (p_document_type = 'PA')) then
1075 begin
1076 SELECT poh.authorization_status,
1077 poh.consigned_consumption_flag,
1078 poh.agent_id,
1079 poh.segment1,
1080 poh.vendor_site_id,
1081 poh.vendor_id,
1082 poh.approved_date --Bug 6074733
1083 into l_authorization_status,
1084 l_consigned_consumption_flag,
1085 l_preparer_id,
1086 x_document_num,
1087 l_party_site_id,
1088 l_party_id,
1089 l_approved_date --Bug 6074733
1090 FROM po_headers_all poh
1091 WHERE poh.po_header_id = p_document_id;
1092 exception
1093 when others then
1094 po_message_s.sql_error('Get_Transmission_Defaults',
1095 l_progress, sqlcode);
1096 raise;
1097 end;
1098 /* Bug 7232666, get approved_date from po_releases_all */
1099 elsif (p_document_type = 'RELEASE') then
1100 begin
1101 SELECT por.authorization_status ,
1102 por.consigned_consumption_flag,
1103 por.agent_id,
1104 poh.segment1,
1105 poh.vendor_site_id,
1106 poh.vendor_id,
1107 por.approved_date --Bug 6074733 Bug 7232666
1108 into l_authorization_status,
1109 l_consigned_consumption_flag,
1110 l_preparer_id,
1111 x_document_num,
1112 l_party_site_id,
1113 l_party_id,
1114 l_approved_date --Bug 6074733
1115 from po_headers_all poh,
1116 po_releases_all por
1117 where por.po_release_id = p_document_id and
1118 poh.po_header_id = por.po_header_id ;
1119 exception
1120 when others then
1121 po_message_s.sql_error('Get_Transmission_Defaults',
1122 l_progress, sqlcode);
1123 end;
1124 end if; /*If ((p_document_type = 'PO') OR (p_document_type = 'PA'))*/
1125
1126 /* NO communication is to be sent if
1127 * l_consigned_consumption_flag is Y.
1128 * return after setting default_method to null.
1129 */
1130 if (nvl(l_consigned_consumption_flag,'N') = 'Y') then
1131 x_default_method := null;
1132 return;
1133 end if;
1134
1135 l_progress := '010';
1136
1137 if(p_document_type = 'PO' OR p_document_type = 'RELEASE') then
1138
1139 /* Bug 2734857. Authorization status must be 'REQUIRES REAPPROVA"' and
1140 * not REQUIRES_REAPPROVAL. Because of this, transaction_type was always
1141 * PRO and hence we were not showing the correct method in the approval
1142 * window.
1143 */
1144 /* Bug 6074733, added new parameter l_approved_date and modified
1145 the if condition to ensure the delivery option does not default
1146 in case of document revision. */
1147 If ((nvl(l_authorization_status, 'INCOMPLETE') IN ('REQUIRES REAPPROVAL', 'APPROVED')) OR (nvl(l_authorization_status, 'INCOMPLETE') = 'REJECTED' AND l_approved_date IS NOT NULL)) then
1148 l_transaction_subtype := 'POCO';
1149 elsif (nvl(l_authorization_status, 'INCOMPLETE') IN ('INCOMPLETE', 'REJECTED') AND l_approved_date IS NULL) then
1150 l_transaction_subtype := 'PRO';
1151 end if;
1152 /* End Bug 6074733 */
1153
1154 -- bug 2764348. The following call raises an exception when TP setup is not done correctly
1155 -- In addition to reading the resultout we need to trap any exceptions and set the xml_flag to N
1156 begin
1157 ecx_document.isDeliveryRequired
1158 (
1159 transaction_type => 'PO',
1160 transaction_subtype => l_Transaction_SubType,
1161 party_id => l_party_id,
1162 party_site_id => l_party_site_id,
1163 resultout => l_result,
1164 retcode => l_retcode,
1165 errmsg => l_errmsg
1166 );
1167 --bug 2764348
1168 If (l_result) then
1169 x_default_method := 'XML';
1170 l_xml_flag := 'Y';
1171 else
1172 l_xml_flag := 'N';
1173 end if;
1174 exception
1175 when others then
1176 l_xml_flag := 'N'; -- bug 2764348
1177 end;
1178
1179 -- Bug 5407459 Do not update the database if
1180 -- p_retrieve_only_flag = 'Y'.
1181 if (NVL(p_retrieve_only_flag,'N') <> 'Y') THEN
1182
1183 if ((p_document_type = 'RELEASE') and
1184 (p_document_subtype = 'BLANKET')) then
1185
1186 update po_releases_all
1187 set xml_flag = l_xml_flag -- bug 2764348
1188 where po_release_id = p_document_id;
1189
1190 elsif ((p_document_type = 'PO') and
1191 (p_document_subtype = 'STANDARD')) then
1192
1193 update po_headers_all
1194 set xml_flag = l_xml_flag -- bug 2764348
1195 where po_header_id = p_document_id;
1196
1197 end if;
1198 END IF;
1199
1200
1201 end if; /* (p_document_type = 'PO' OR p_document_type = 'RELEASE') */
1202
1203 l_progress := '020';
1204 If (p_preparer_id is null) then
1205 p_preparer_id := l_preparer_id;
1206 end if;
1207
1208 If (x_default_method is null) then
1209
1210 If ((p_document_type = 'PO') OR (p_document_type = 'PA')) then
1211 begin
1212 -- Bug 5295179 If the supplier notif method is defined on the
1213 -- PO (i.e. for POs created/modified in HTML), use that instead
1214 -- of the Supplier Site defaults. Also, we default email/fax
1215 -- from 3 places, in order of priority:
1216 -- 1. po_headers_all
1217 -- 2. po_vendor_contacts (see Bug 4597324)
1218 -- 3. po_vendor_sites_all
1219
1220 -- Bug 6625807 / 6526600
1221 -- Added pvs.vendor_site_id = NVL(pvc.vendor_site_id,
1222 -- pvs.vendor_site_id) condition so that following query returns
1223 -- only 1 record in case one vendor contact id is attached with
1224 -- multiple sites.
1225 select NVL(poh.supplier_notif_method,
1226 pvs.supplier_notif_method),
1227 DECODE(poh.supplier_notif_method, NULL,
1228 NVL(pvc.email_address, pvs.email_address),
1229 poh.email_address),
1230 DECODE(poh.supplier_notif_method, NULL,
1231 NVL(pvc.fax_area_code, pvs.fax_area_code),
1232 ''), -- poh.fax includes the area code
1233 DECODE(poh.supplier_notif_method, NULL,
1234 NVL(pvc.fax, pvs.fax),
1235 poh.fax)
1236 into x_default_method,
1237 x_email_address,
1238 l_fax_area,
1239 l_faxnum
1240 from po_headers_all poh,
1241 po_vendor_sites_all pvs,
1242 po_vendor_contacts pvc
1243 where poh.vendor_site_id = pvs.vendor_site_id and
1244 poh.vendor_contact_id = pvc.vendor_contact_id (+) and
1245 poh.po_header_id = p_document_id and
1246 pvs.vendor_site_id = NVL(pvc.vendor_site_id, pvs.vendor_site_id);
1247
1248 -- Bug 5295179 In HTML, we store the method as 'NONE' to
1249 -- distinguish from POs created in Forms. It means that the PO
1250 -- should not be communicated.
1251 IF (x_default_method = 'NONE') THEN
1252 x_default_method := null;
1253 END IF;
1254 exception
1255 when others then
1256 po_message_s.sql_error('Get_Transmission_Defaults',
1257 l_progress, sqlcode);
1258 raise;
1259 end;
1260
1261
1262 elsif (p_document_type = 'RELEASE') then
1263 begin
1264 -- We default email/fax from po_vendor_contacts (see Bug
1265 -- 4597324), then po_vendor_sites_all.
1266
1267 -- Bug 6625807 / 6526600
1268 -- Added pvs.vendor_site_id = NVL(pvc.vendor_site_id,
1269 -- pvs.vendor_site_id) condition so that following query returns
1270 -- only 1 record in case one vendor contact id is attached with
1271 -- multiple sites.
1272 select pvs.supplier_notif_method,
1273 NVL(pvc.email_address, pvs.email_address),
1274 NVL(pvc.fax_area_code, pvs.fax_area_code),
1275 NVL(pvc.fax, pvs.fax)
1276 into x_default_method,
1277 x_email_address,
1278 l_fax_area,
1279 l_faxnum
1280 from po_headers_all poh,
1281 po_vendor_sites_all pvs,
1282 po_vendor_contacts pvc,
1283 po_releases por
1284 where poh.vendor_site_id = pvs.vendor_site_id and
1285 poh.vendor_contact_id = pvc.vendor_contact_id (+) and
1286 poh.po_header_id = por.po_header_id and
1287 por.po_release_id = p_document_id and
1288 pvs.vendor_site_id = NVL(pvc.vendor_site_id, pvs.vendor_site_id);
1289 exception
1290 when others then
1291 po_message_s.sql_error('Get_Transmission_Defaults',
1292 l_progress, sqlcode);
1293 raise;
1294 end;
1295
1296 end if; /* p_document_type = 'PO' */
1297
1298 l_progress := '030';
1299
1300 If (l_faxnum is not null) then
1301 x_fax_number := l_fax_area || l_faxnum ;
1302 end if;
1303
1304 end if; /* x_default_method is null */
1305
1306 EXCEPTION
1307 when others then
1308 po_message_s.sql_error('Get_Transmission_Defaults',
1309 l_progress, sqlcode);
1310 raise;
1311
1312 END Get_Transmission_Defaults;
1313
1314 /* RETROACTIVE FPI END */
1315
1316 -- Bug 5407459 Added this procedure.
1317 ---------------------------------------------------------------------------
1318 --Start of Comments
1319 --Name: get_transmission_defaults_edi
1320 --Pre-reqs:
1321 -- N/A
1322 --Modifies:
1323 -- po_headers_all.xml_flag, po_releases_all.xml_flag (unless
1324 -- p_retrieve_only_flag = 'Y')
1325 --Locks:
1326 -- N/A
1327 --Function:
1328 -- Retrieves the document transmission setups, including EDI.
1329 -- (Note that the original get_transmission_defaults procedure does not check
1330 -- the EDI setups.)
1331 -- By default, we will update the xml_flag in the database based on whether
1332 -- the vendor site is set up for XML, unless p_retrieve_only_flag = 'Y'.
1333 --Parameters:
1334 --IN:
1335 --p_document_id
1336 -- po_header_id for POs and PAs, po_release_id for releases
1337 --p_document_type
1338 -- 'PO', 'PA', 'RELEASE'
1339 --p_document_subtype
1340 -- 'STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT', 'SCHEDULED'
1341 --p_preparer_id
1342 -- IN OUT parameter for the buyer of the PO; can be passed as null
1343 --p_retrieve_only_flag
1344 -- if this is 'Y', we will not update the xml_flag in the database. This is
1345 -- necessary to avoid locking issues from HTML.
1346 --OUT:
1347 --x_default_method
1348 -- the communication method: null, 'PRINT', 'FAX', 'EMAIL', 'XML', 'EDI'
1349 --x_email_address
1350 -- the default email address
1351 --x_fax_number
1352 -- the default fax number
1353 --x_document_num
1354 -- the document number
1355 --Testing:
1356 --End of Comments
1357 ---------------------------------------------------------------------------
1358 procedure get_transmission_defaults_edi (
1359 p_document_id IN NUMBER,
1360 p_document_type IN VARCHAR2,
1361 p_document_subtype IN VARCHAR2,
1362 p_preparer_id IN OUT NOCOPY NUMBER,
1363 x_default_method OUT NOCOPY VARCHAR2,
1364 x_email_address OUT NOCOPY VARCHAR2,
1365 x_fax_number OUT NOCOPY VARCHAR2,
1366 x_document_num OUT NOCOPY VARCHAR2,
1367 p_retrieve_only_flag IN VARCHAR2) IS
1368 l_tp_header_id PO_VENDOR_SITES.tp_header_id%TYPE;
1369 l_edi_flag ECE_TP_DETAILS.edi_flag%TYPE;
1370 BEGIN
1371
1372 -- Retrieve the non-EDI tranmission setups.
1373 get_transmission_defaults (
1374 p_document_id => p_document_id,
1375 p_document_type => p_document_type,
1376 p_document_subtype => p_document_subtype,
1377 p_preparer_id => p_preparer_id,
1378 x_default_method => x_default_method,
1379 x_email_address => x_email_address,
1380 x_fax_number => x_fax_number,
1381 x_document_num => x_document_num,
1382 p_retrieve_only_flag => p_retrieve_only_flag );
1383
1384 -- Next, check the EDI settings. We can skip this check if the method is XML,
1385 -- since XML takes precedence over EDI.
1386 IF ((x_default_method IS NULL) OR (x_default_method <> 'XML')) THEN
1387
1388 -- Bug 5593568 Fixed the queries below to use a subquery to handle the
1389 -- outer join with ece_tp_details.
1390
1391 IF (p_document_type IN ('PO', 'PA')) THEN
1392
1393 select phv.tp_header_id, nvl(etd.edi_flag,'N')
1394 into l_tp_header_id, l_edi_flag
1395 from
1396 (select pvs.tp_header_id, ph.authorization_status, ph.type_lookup_code
1397 from po_vendor_sites_all pvs,
1398 po_vendors pv,
1399 po_headers_all ph
1400 where ph.vendor_id = pv.vendor_id (+)
1401 and ph.vendor_site_id = pvs.vendor_site_id (+)
1402 and ph.vendor_id = pvs.vendor_id (+)
1403 and ph.po_header_id = p_document_id
1404 ) phv,
1405 ece_tp_details etd
1406 where etd.tp_header_id (+) = phv.tp_header_id
1407 and etd.document_id (+)
1408 = decode(phv.authorization_status,'REQUIRES REAPPROVAL','POCO','POO')
1409 and etd.document_type (+) = phv.type_lookup_code;
1410
1411 ELSIF (p_document_type = 'RELEASE') THEN
1412
1413 select phv.tp_header_id, nvl(etd.edi_flag,'N')
1414 into l_tp_header_id, l_edi_flag
1415 from
1416 (select pvs.tp_header_id, pr.authorization_status
1417 from po_vendor_sites_all pvs,
1418 po_vendors pv,
1419 po_headers_all ph,
1420 po_releases_all pr
1421 where ph.vendor_id = pv.vendor_id (+)
1422 and ph.vendor_site_id = pvs.vendor_site_id (+)
1423 and ph.vendor_id = pvs.vendor_id (+)
1424 and ph.po_header_id = pr.po_header_id
1425 and pr.po_release_id = p_document_id
1426 ) phv,
1427 ece_tp_details etd
1428 where etd.tp_header_id (+) = phv.tp_header_id
1429 and etd.document_id (+)
1430 = decode(phv.authorization_status,'REQUIRES REAPPROVAL','POCO','POO')
1431 and rownum = 1;
1432
1433 END IF; --End of IF l_doc_type IN ('PO', 'PA')
1434
1435 IF ((l_tp_header_id IS NOT NULL) AND (l_edi_flag = 'Y')) THEN
1436 x_default_method := 'EDI';
1437 x_email_address := null;
1438 x_fax_number := null;
1439 END IF;
1440
1441 END IF;
1442
1443 END get_transmission_defaults_edi;
1444
1445 --<Shared Proc FPJ START>
1446 -------------------------------------------------------------------------------
1447 --Start of Comments
1448 --Name: get_org_id_from_vendor_site
1449 --Pre-reqs:
1450 -- None
1451 --Modifies:
1452 -- None
1453 --Locks:
1454 -- None.
1455 --Function:
1456 -- Returns the operating unit id associated with given vendor_site_id
1457 --Parameters:
1458 --p_vendor_site_id
1459 -- site_id from which to derive the OU id
1460 --Notes:
1461 -- None
1462 --Testing:
1463 -- None
1464 --End of Comments
1465 -------------------------------------------------------------------------------
1466 FUNCTION get_org_id_from_vendor_site
1467 (
1468 p_vendor_site_id IN NUMBER
1469 )
1470 RETURN PO_HEADERS_ALL.org_id%TYPE
1471 IS
1472 x_org_id PO_HEADERS_ALL.org_id%TYPE;
1473
1474 BEGIN
1475
1476 SELECT org_id
1477 INTO x_org_id
1478 FROM po_vendor_sites_all
1479 WHERE vendor_site_id = p_vendor_site_id;
1480
1481 return (x_org_id);
1482
1483 EXCEPTION
1484
1485 WHEN OTHERS THEN
1486 return (NULL);
1487
1488 END get_org_id_from_vendor_site;
1489 --<Shared Proc FPJ END>
1490
1491 END PO_VENDOR_SITES_SV;
[Home] [Help]