1 PACKAGE BODY PO_VENDORS_SV as
2 /* $Header: POXVDVEB.pls 120.4 2006/02/08 21:40:59 srnatara noship $*/
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
9 FUNCTION NAME: val_vendor()
10
11 ===========================================================================*/
12 FUNCTION val_vendor(X_vendor_id IN NUMBER) return BOOLEAN IS
13
14 X_progress varchar2(3) := NULL;
15 X_vendor_id_v number := NULL;
16
17 BEGIN
18
19 X_progress := '010';
20
21 /* Check if the given Supplier is active */
22
23 SELECT vendor_id
24 INTO X_vendor_id_v
25 FROM po_vendors
26 WHERE sysdate between nvl(start_date_active, sysdate -1)
27 AND nvl(end_date_active, sysdate + 1)
28 AND enabled_flag = 'Y'
29 AND vendor_id = X_vendor_id;
30
31 return (TRUE);
32
33 EXCEPTION
34
35 when no_data_found then
36 return (FALSE);
37 when others then
38 po_message_s.sql_error('val_vendor',X_progress,sqlcode);
39 raise;
40
41 END val_vendor;
42
43 /*===========================================================================
44
45 PROCEDURE NAME : get_vendor_info()
46
47 ===========================================================================*/
48
49 procedure get_vendor_info (X_vendor_id IN number,
50 X_ship_to_location_id IN OUT NOCOPY number,
51 X_bill_to_location_id IN OUT NOCOPY number,
52 X_ship_via_lookup_code IN OUT NOCOPY varchar2,
53 X_fob_lookup_code IN OUT NOCOPY varchar2,
54 X_freight_terms_lookup_code IN OUT NOCOPY varchar2,
55 X_terms_id IN OUT NOCOPY number,
56 X_type_1099 IN OUT NOCOPY varchar2,
57 X_hold_flag IN OUT NOCOPY varchar2,
58 X_invoice_currency_code IN OUT NOCOPY varchar2,
59 X_receipt_required_flag IN OUT NOCOPY varchar2,
60 X_num_1099 IN OUT NOCOPY varchar2,
61 X_vat_registration_num IN OUT NOCOPY varchar2,
62 X_inspection_required_flag IN OUT NOCOPY varchar2 ) is
63
64 X_progress varchar2(3) := '';
65 /* Bug 4421065:TCA Impact: Removed the obsolete column vat_code in po_vendors table */
66 -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
67 -- been nulled out.
68
69 cursor C is select v.type_1099,
70 v.hold_flag,
71 null,
72 null,
73 null,
74 null,
75 null,
76 v.terms_id,
77 v.invoice_currency_code,
78 v.receipt_required_flag,
79 v.num_1099,
80 v.vat_registration_num,
81 v.inspection_required_flag
82 from po_vendors v
83 where v.vendor_id = X_vendor_id;
84
85 begin
86 if (X_vendor_id is not null) then
87 X_progress := '010';
88 open C;
89 X_progress := '020';
90
91 /* Get the other attributes for a given vendor id */
92
93 fetch C into X_type_1099,
94 X_hold_flag,
95 X_ship_to_location_id,
96 X_bill_to_location_id,
97 X_ship_via_lookup_code ,
98 X_fob_lookup_code,
99 X_freight_terms_lookup_code,
100 X_terms_id,
101 X_invoice_currency_code,
102 X_receipt_required_flag,
103 X_num_1099,
104 X_vat_registration_num,
105 X_inspection_required_flag;
106 close C;
107 end if;
108
109 exception
110 when others then
111 po_message_s.sql_error('get_vendor_info',X_progress,sqlcode);
112 -- app_exception.raise_exception;
113 end get_vendor_info;
114
115 /*===========================================================================
116
117 PROCEDURE NAME : get_vendor_defaults()
118
119 ===========================================================================*/
120
121 procedure get_vendor_defaults ( X_vendor_id IN number,
122 X_org_id IN number,
123 X_set_of_books_id IN number,
124 X_res_fob IN OUT NOCOPY varchar2 ,
125 X_res_ship_via IN OUT NOCOPY varchar2 ,
126 X_res_freight_terms IN OUT NOCOPY varchar2 ,
127 X_res_terms_id IN OUT NOCOPY number ,
128 X_vendor_site_id IN OUT NOCOPY number ,
129 X_vendor_site_code IN OUT NOCOPY VARCHAR2,
130 X_fob_dsp IN OUT NOCOPY varchar2,
131 X_ship_via_dsp IN OUT NOCOPY varchar2,
132 X_freight_terms_dsp IN OUT NOCOPY varchar2,
133 X_terms_dsp IN OUT NOCOPY varchar2,
134 X_res_ship_to_loc_id IN OUT NOCOPY number,
135 X_ship_to_loc_dsp IN OUT NOCOPY varchar2,
136 X_ship_org_code IN OUT NOCOPY varchar2,
137 X_ship_org_name IN OUT NOCOPY varchar2,
138 X_ship_org_id IN OUT NOCOPY number,
139 X_res_bill_to_loc_id IN OUT NOCOPY number,
140 X_bill_to_loc_dsp IN OUT NOCOPY varchar2,
141 X_res_invoice_currency_code IN OUT NOCOPY varchar2,
142 X_type_1099 IN OUT NOCOPY varchar2,
143 X_receipt_required_flag IN OUT NOCOPY varchar2 ,
144 X_vendor_contact_id IN OUT NOCOPY number,
145 X_vendor_contact_name IN OUT NOCOPY varchar2,
146 X_inspection_required_flag IN OUT NOCOPY varchar2,
147 X_document_type IN varchar2 ) is
148
149 X_hold_flag varchar2(1);
150 X_ship_to_location_id number;
151 X_bill_to_location_id number;
152 X_ship_via_lookup_code varchar2(25);
153 -- Bug: 1710995 Define the codes according to the definition in the table.
154 X_fob_lookup_code po_lookup_codes.lookup_code%TYPE;
155 X_freight_terms_lookup_code po_lookup_codes.lookup_code%TYPE;
156 X_terms_id number;
157 X_invoice_currency_code varchar2(15);
158 X_num_1099 varchar2(30);
159 /*
160 ** BUGNO 718328.
161 ** X_vat_registration_num varchar2(15);
162 ** changed to varchar2(20).
163 */
164 X_vat_registration_num varchar2(20);
165
166 /* The following 2 variables are used as parameters to
167 ** get_def_vendor_site() proc. For some reason, passing
168 ** X_vendor_site_id and X_vendor_site_code itself as
169 ** parameters raises a value error in get_def_vendor_site() proc. */
170
171 X_temp_vendor_site_id number;
172 X_temp_vendor_site_code varchar2(15);
173
174
175 X_vs_ship_to_location_id number;
176 X_vs_bill_to_location_id number;
177 X_vs_ship_via_lookup_code varchar2(25);
178 X_vs_pay_on_code varchar2(25);
179 -- Bug: 1710995 Define the codes according to the definition in the table.
180 X_vs_fob_lookup_code po_lookup_codes.lookup_code%TYPE;
181 X_vs_freight_terms_lookup_code po_lookup_codes.lookup_code%TYPE;
182 X_vs_terms_id number;
183 X_vs_invoice_currency_code varchar2(15);
184 x_vs_shipping_control po_lookup_codes.lookup_code%TYPE; -- <INBOUND LOGISTICS FPJ>
185
186
187 X_progress varchar2(3) := '';
188
189
190
191
192 begin
193
194 /* Get the other attributes for a given vendor */
195 get_vendor_info(X_vendor_id ,
196 X_ship_to_location_id ,
197 X_bill_to_location_id ,
198 X_ship_via_lookup_code ,
199 X_fob_lookup_code ,
200 X_freight_terms_lookup_code ,
201 X_terms_id ,
202 X_type_1099 ,
203 X_hold_flag ,
204 X_invoice_currency_code ,
205 X_receipt_required_flag ,
206 X_num_1099 ,
207 X_vat_registration_num ,
208 X_inspection_required_flag ) ;
209
210 /* Default currency code to vendor first */
211
212 IF X_invoice_currency_code is NOT NULL THEN
213 X_res_invoice_currency_code := X_invoice_currency_code;
214 ELSE
215 X_res_invoice_currency_code := '';
216 END IF;
217
218 X_progress := '030';
219
220 /* Get a default vendor site if one exists for the
221 ** given vendor
222 **
223 ** 11/14/95 - MS
224 ** Added X_document_type parameter for RFQ/Quote processing
225 */
226
227 po_vendor_sites_sv.get_def_vendor_site(X_vendor_id ,
228 X_temp_vendor_site_id ,
229 X_temp_vendor_site_code,
230 X_document_type );
231
232 /* Copy from the temp variable back to the actual variable */
233 X_vendor_site_id := X_temp_vendor_site_id;
234 X_vendor_site_code := X_temp_vendor_site_code;
235
236
237 /* If the vendor site is not null,
238 ** proceed to get the vendor site details
239 ** otherwise, simply return to the client.
240 ** The vendor attributes will be validated
241 ** when the vendor site info is filled in */
242
243 -- dbms_output.put_line('The Def Site is ' || X_vendor_site_id );
244
245
246 if (X_vendor_site_id is not null) then
247 X_progress := '040';
248 po_vendor_sites_sv.get_vendor_site_info(X_vendor_site_id ,
249 X_vs_ship_to_location_id ,
250 X_vs_bill_to_location_id ,
251 X_vs_ship_via_lookup_code ,
252 X_vs_fob_lookup_code ,
253 X_vs_pay_on_code ,
254 X_vs_freight_terms_lookup_code ,
255 X_vs_terms_id ,
256 X_vs_invoice_currency_code,
257 x_vs_shipping_control -- <INBOUND LOGISTICS FPJ>
258 ) ;
259 -- dbms_output.put_line('Vendor SIte Info : FOB is ' || X_vs_fob_lookup_code);
260 else
261
262 -- dbms_output.put_line('Returning back without Site');
263 return;
264
265 end if;
266
267 /* Validate Invoice currency code try vendor site info first,
268 ** if that is invalid, try to validate the vendor info */
269
270
271 if X_vs_invoice_currency_code is not null then
272 X_res_invoice_currency_code := X_vs_invoice_currency_code;
273 end if;
274
275
276 /* Validate the FOB lookup code from vendor site if available.
277 ** If not available or vendor site fob is inactive, validate the vendor fob */
278
279 if X_vs_fob_lookup_code is not null then
280 val_fob(X_vs_fob_lookup_code , X_res_fob);
281 end if;
282 if ((X_res_fob is null) and (X_fob_lookup_code is not null)) then
283 val_fob(X_fob_lookup_code , X_res_fob);
284 end if;
285
286 /* Validating Freight Terms lookup code from vendor site if available.
287 ** If not available or vendor site Freight Terms is inactive,
288 ** validate the vendor Freight Terms */
289
290 if X_vs_freight_terms_lookup_code is not null then
291 val_freight_terms( X_vs_freight_terms_lookup_code, X_res_freight_terms);
292 end if;
293
294 if ((X_res_freight_terms is null) and
295 (X_freight_terms_lookup_code is not null)) then
296 val_freight_terms( X_freight_terms_lookup_code, X_res_freight_terms);
297 end if;
298
299 /* Validating Ship Via lookup code from vendor site if available.
300 ** If not available or vendor site Ship Via is inactive,
301 ** validate the vendor Ship Via */
302
303 if X_vs_ship_via_lookup_code is not null then
304 val_freight_carrier(X_vs_ship_via_lookup_code, X_org_id, X_res_ship_via);
305 end if;
306 if ((X_res_ship_via is null) and
307 (X_ship_via_lookup_code is not null)) then
308 val_freight_carrier(X_ship_via_lookup_code, X_org_id, X_res_ship_via);
309 end if;
310
311 /* Validating Terms Id from vendor site if available.
312 ** If not available or vendor site Terms Id is inactive,
313 ** validate the vendor Terms Id */
314
315 if X_vs_terms_id is not null then
316 po_terms_sv.val_ap_terms(X_vs_terms_id, X_res_terms_id);
317 end if;
318
319 if ((X_res_terms_id is null) and
320 (X_terms_id is not null)) then
321 po_terms_sv.val_ap_terms(X_terms_id, X_res_terms_id);
322 end if;
323
324
325 /* Obtain displayed values for the valid ids */
326
327 get_displayed_values(X_res_fob, X_res_freight_terms, X_res_ship_via, X_res_terms_id,
328 X_fob_dsp, X_freight_terms_dsp, X_ship_via_dsp, X_terms_dsp,
329 X_org_id);
330
331 /* Obtain Ship To location atrributes , use the vendor site info first.
332 ** If that is null or it is inactive, use the vendor info */
333
334 if X_vs_ship_to_location_id is not null then
335 get_ship_to_loc_attributes ( X_vs_ship_to_location_id, X_ship_to_loc_dsp,
336 X_ship_org_code, X_ship_org_name ,
337 X_ship_org_id, X_set_of_books_id );
338 X_res_ship_to_loc_id := X_vs_ship_to_location_id;
339 end if;
340
341 if (X_ship_to_loc_dsp is null)
342 and (X_ship_to_location_id is not null) then
343 get_ship_to_loc_attributes ( X_ship_to_location_id , X_ship_to_loc_dsp ,
344 X_ship_org_code , X_ship_org_name ,
345 X_ship_org_id ,X_set_of_books_id );
346 X_res_ship_to_loc_id := X_ship_to_location_id;
347 end if;
348
349 /* Obtain Bill To location atrributes , use the vendor site info first.
350 ** If that is null or it is inactive, use the vendor info */
351
352
353 if X_vs_bill_to_location_id is not null then
354 get_bill_to_loc_attributes ( X_vs_bill_to_location_id , X_bill_to_loc_dsp );
355 X_res_bill_to_loc_id := X_vs_bill_to_location_id;
356 end if;
357
358 if (X_bill_to_loc_dsp is null)
359 and (X_bill_to_location_id is not null) then
360 get_bill_to_loc_attributes ( X_bill_to_location_id , X_bill_to_loc_dsp);
361 X_res_bill_to_loc_id := X_bill_to_location_id;
362 end if;
363
364 /* Default the Vendor Contact if possible */
365
366 po_vendor_contacts_sv.get_vendor_contact(X_vendor_site_id,
367 X_vendor_contact_id,
368 X_vendor_contact_name);
369
370
371 exception
372 when others then
373 po_message_s.sql_error('get_vendor_defaults',X_progress,sqlcode);
374 raise;
375 end get_vendor_defaults;
376
377
378 /*===========================================================================
379
380 PROCEDURE NAME : val_fob()
381
382 ===========================================================================*/
383
384 procedure val_fob( X_temp_fob_lookup_code IN varchar2,
385 X_res_fob IN OUT NOCOPY varchar2) is
386
387 X_progress varchar2(3) := '';
388 x_lookup_type PO_LOOKUP_CODES.LOOKUP_TYPE%type; --bug3808435
389 begin
390 X_progress := '010';
391 --Bug3808435
392 --Replaced the hardcoded literal 'FOB' in the sql with a
393 --bind variable.
394 x_lookup_type := 'FOB';
395 /* Check if the given FOB lookupcode is active */
396
397 select lookup_code
398 into X_res_fob
399 from po_lookup_codes
400 where lookup_type = x_lookup_type
401 and sysdate < nvl(inactive_date, sysdate + 1)
402 and lookup_code = X_temp_fob_lookup_code;
403
404 exception
405
406 when no_data_found then
407 X_res_fob := '';
408 when too_many_rows then
409 X_res_fob := '';
410 when others then
411 po_message_s.sql_error('val_fob',X_progress,sqlcode);
412 raise;
413 end val_fob;
414
415 /*===========================================================================
416
417 PROCEDURE NAME : val_freight_terms()
418
419 ===========================================================================*/
420
421 procedure val_freight_terms ( X_temp_freight_terms IN varchar2,
422 X_res_freight_terms IN OUT NOCOPY varchar2) is
423
424 X_progress varchar2(3) := '';
425 x_lookup_type PO_LOOKUP_CODES.LOOKUP_TYPE%type; --bug3808435
426
427 begin
428 X_progress := '010';
429 --Bug3808435
430 --Replaced the hardcoded literal 'FREIGHT TERMS' in the sql with
431 --a bind variable.
432 x_lookup_type := 'FREIGHT TERMS';
433 /* Check if the given Freight Terms Code is active */
434
435 SELECT lookup_code
436 INTO X_res_freight_terms
437 FROM po_lookup_codes
438 WHERE lookup_type = x_lookup_type
439 AND sysdate < nvl(inactive_date, sysdate + 1)
440 AND lookup_code = X_temp_freight_terms ;
441 exception
442
443 when no_data_found then
444 X_res_freight_terms := '';
445 when too_many_rows then
446 X_res_freight_terms := '';
447 when others then
448 po_message_s.sql_error('val_freght_terms',X_progress,sqlcode);
449 raise;
450 end val_freight_terms;
451
452 /*===========================================================================
453
454 PROCEDURE NAME : val_freight_carrier()
455
456 ===========================================================================*/
457
458 procedure val_freight_carrier (X_temp_ship_via IN varchar2,
459 X_org_id IN number,
460 X_res_ship_via IN OUT NOCOPY varchar2) is
461
462 X_progress varchar2(3) := '';
463
464 begin
465 X_progress := '010';
466
467 /* Check if the given Freight Code is active */
468
469 SELECT freight_code
470 INTO X_res_ship_via
471 FROM org_freight
472 WHERE organization_id = X_org_id
473 AND freight_code = X_temp_ship_via
474 AND nvl(disable_date, sysdate + 1) > sysdate;
475
476 exception
477
478 when no_data_found then
479 X_res_ship_via := '';
480 when too_many_rows then
481 X_res_ship_via := '';
482 when others then
483 po_message_s.sql_error('val_freight_carrier',X_progress,sqlcode);
484 raise;
485
486 end val_freight_carrier;
487
488 /*INBOUND LOGISTICS FPJ START */
489 /**
490 * Private Procedure: val_shipping_control
491 * Requires: None
492 * Modifies: x_res_shipping_control
493 * Effects: If p_tmp_shipping_control is not valid then returns
494 * NULL in x_res_shipping_control. Otherwise returns
495 * sets x_res_shipping_control to p_tmp_shipping_control
496 */
497
498 PROCEDURE val_shipping_control
499 (
500 p_temp_shipping_control IN VARCHAR2,
501 x_res_shipping_control IN OUT NOCOPY VARCHAR2
502 )
503 IS
504
505 X_progress varchar2(3) := '';
506 x_lookup_type PO_LOOKUP_CODES.LOOKUP_TYPE%type; --bug3808435
507 BEGIN
508
509 X_progress := '010';
510 --Bug3808435
511 --Replaced the hardcoded literal 'SHIPPING CONTROL' in the sql with a
512 --bind variable.
513 x_lookup_type := 'SHIPPING CONTROL';
514 /* Check if the given shipping control is active */
515
516 SELECT lookup_code
517 INTO x_res_shipping_control
518 FROM po_lookup_codes
519 WHERE lookup_type = x_lookup_type
520 AND TRUNC(SYSDATE) < NVL( TRUNC(inactive_date), SYSDATE + 1 )
521 AND lookup_code = p_temp_shipping_control;
522
523 EXCEPTION
524
525 WHEN NO_DATA_FOUND THEN
526 x_res_shipping_control := '';
527 WHEN TOO_MANY_ROWS THEN
528 x_res_shipping_control := '';
529 WHEN OTHERS THEN
530 PO_MESSAGE_S.sql_error('val_shipping_control', x_progress, sqlcode);
531 RAISE;
532
533 END val_shipping_control;
534
535 /* INBOUND LOGISTICS FPJ END */
536
537
538 /*=============================================================================
539
540 PROCEDURE: get_terms_conditions <GA FPI>
541
542 DESCRIPTION: Based on the input po_header_id, retrieves the
543 (a) terms_id
544 (b) ship_via_lookup_code
545 (c) fob_lookup_code
546 (d) freight_terms_lookup_code.
547
548 =============================================================================*/
549 PROCEDURE get_terms_conditions
550 (
551 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE,
552 x_terms_id OUT NOCOPY PO_HEADERS_ALL.terms_id%TYPE,
553 x_ship_via_lookup_code OUT NOCOPY PO_HEADERS_ALL.ship_via_lookup_code%TYPE,
554 x_fob_lookup_code OUT NOCOPY PO_HEADERS_ALL.fob_lookup_code%TYPE,
555 x_freight_terms_lookup_code OUT NOCOPY PO_HEADERS_ALL.freight_terms_lookup_code%TYPE,
556 x_shipping_control OUT NOCOPY PO_HEADERS_ALL.shipping_control%TYPE -- <INBOUND LOGISTICS FPJ>
557 )
558 IS
559 BEGIN
560
561 SELECT terms_id,
562 ship_via_lookup_code,
563 fob_lookup_code,
564 freight_terms_lookup_code,
565 shipping_control -- <INBOUND LOGISTICS FPJ>
566 INTO x_terms_id,
567 x_ship_via_lookup_code,
568 x_fob_lookup_code,
569 x_freight_terms_lookup_code,
570 x_shipping_control -- <INBOUND LOGISTICS FPJ>
571 FROM po_headers_all
572 WHERE po_header_id = p_po_header_id;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 PO_MESSAGE_S.sql_error('get_terms_conditions','000',sqlcode);
577 raise;
578
579 END get_terms_conditions;
580
581
582
583 /*===========================================================================
584
585 PROCEDURE NAME : get_displayed_values()
586
587 ===========================================================================*/
588
589 procedure get_displayed_values(X_res_fob IN varchar2, X_res_freight_terms IN varchar2,
590 X_res_ship_via IN varchar2, X_res_terms_id IN number,
591 X_fob_dsp IN OUT NOCOPY varchar2, X_freight_terms_dsp IN OUT NOCOPY varchar2,
592 X_ship_via_dsp IN OUT NOCOPY varchar2, X_terms_dsp IN OUT NOCOPY varchar2,
593 X_org_id IN number) is
594
595 X_progress varchar2(3) := '';
596
597 /* The foll. 2 variables are just defined as the get_displayed_value
598 ** proc insists on returning it. We do not use the description */
599
600 X_fob_desc varchar2(240) ;
601 X_freight_terms_desc varchar2(240);
602
603 /* This is to tell get_displayed_value
604 ** proc that it needs to get the
605 ** displayed field ONLY for a valid code */
606
607 X_validate boolean := TRUE ;
608
609 begin
610
611 /* Get the displayed value for LOKKUP_TYPE = 'FOB' */
612
613 po_core_s.get_displayed_value('FOB', X_res_fob, X_fob_dsp, X_fob_desc,
614 X_validate );
615
616 /* begin
617 X_progress := '010';
618
619 -- Get the displayed value for a given FOB lookup Code
620
621 select displayed_field
622 into X_fob_dsp
623 from po_lookup_codes
624 where lookup_type = 'FOB'
625 and sysdate < nvl(inactive_date, sysdate + 1)
626 and lookup_code = X_res_fob;
627 exception
628
629 when no_data_found then
630 X_fob_dsp := '';
631 when too_many_rows then
632 X_fob_dsp := '';
633 when others then
634 po_message_s.sql_error('get_displayed_values',X_progress,sqlcode);
635 raise;
636 end; */
637
638 /* Get the displayed value for a given Lookup_Type = 'FREIGHT TERMS'*/
639
640 po_core_s.get_displayed_value('FREIGHT TERMS', X_res_freight_terms,
641 X_freight_terms_dsp,
642 X_freight_terms_desc, X_validate );
643
644
645 /* begin
646 X_progress := '020';
647
648 --Get the displayed value for a given Freight Terms Lookup Type
649
650 select displayed_field
651 into X_freight_terms_dsp
652 from po_lookup_codes
653 where lookup_type = 'FREIGHT TERMS'
654 and sysdate < nvl(inactive_date, sysdate + 1)
655 and lookup_code = X_res_freight_terms ;
656
657 exception
658
659 when no_data_found then
660 X_freight_terms_dsp := '';
661 when too_many_rows then
662 X_freight_terms_dsp := '';
663 when others then
664 po_message_s.sql_error('get_displayed_values',X_progress,sqlcode);
665 raise;
666 end; */
667
668 /* There is no po_core rooutine to return this */
669
670 begin
671 X_progress := '030';
672
673 /* Get the displayed value for a given Ship Via Lookup Code */
674
675 select description
676 into X_ship_via_dsp
677 from org_freight
678 where organization_id = X_org_id
679 and freight_code = X_res_ship_via
680 and nvl(disable_date, sysdate + 1) > sysdate;
681
682 exception
683
684 when no_data_found then
685 X_ship_via_dsp := '';
686 when too_many_rows then
687 X_ship_via_dsp := '';
688 when others then
689 po_message_s.sql_error('get_displayed_values',X_progress,sqlcode);
690 raise;
691 end;
692
693 /* No equivalent routine in PO CORE for this one too */
694
695 begin
696 X_progress := '040';
697
698 /* Get the displayed value for a given Terms Id*/
699
700 select name
701 into X_terms_dsp
702 from ap_terms
703 where term_id = X_res_terms_id;
704
705 exception
706
707 when no_data_found then
708 X_terms_dsp := '';
709 when too_many_rows then
710 X_terms_dsp := '';
711 when others then
712 po_message_s.sql_error('get_displayed_values',X_progress,sqlcode);
713 raise;
714 end;
715
716 end get_displayed_values;
717
718
719 /*=============================================================================
720
721 FUNCTION: get_terms_dsp <GA FPI>
722
723 DESCRIPTION: Gets the displayed form of the terms ID.
724
725 =============================================================================*/
726 FUNCTION get_terms_dsp
727 (
728 p_terms_id AP_TERMS.term_id%TYPE
729 )
730 RETURN AP_TERMS.name%TYPE
731 IS
732 x_terms_dsp AP_TERMS.name%TYPE;
733
734 BEGIN
735
736 SELECT name
737 INTO x_terms_dsp
738 FROM ap_terms
739 WHERE term_id = p_terms_id;
740
741 return (x_terms_dsp);
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 return (NULL);
746
747 END get_terms_dsp;
748
749
750 /*===========================================================================
751
752 PROCEDURE NAME : get_ship_to_loc_attributes()
753
754 ===========================================================================*/
755
756 procedure get_ship_to_loc_attributes ( X_temp_ship_to_loc_id IN number, X_ship_to_loc_dsp IN OUT NOCOPY varchar2,
757 X_ship_org_code IN OUT NOCOPY varchar2, X_ship_org_name IN OUT NOCOPY varchar2,
758 X_ship_org_id IN OUT NOCOPY number, X_set_of_books_id IN number) is
759
760 X_progress varchar2(3) := '';
761 X_inv_org_id number;
762
763
764 begin
765
766 X_progress := '010';
767
768 /* select location_code
769 into X_ship_to_loc_dsp
770 from hr_locations
771 where location_id = X_temp_ship_to_loc_id;*/
772
773 /* Get the location_code ,inv_org_id for a given location */
774
775 po_locations_s.get_loc_attributes(X_temp_ship_to_loc_id,X_ship_to_loc_dsp,
776 X_inv_org_id);
777
778 X_progress := '020';
779
780 /* select ood.organization_code,
781 ood.organization_name,
782 ood.organization_id
783 into X_ship_org_code,
784 X_ship_org_name,
785 X_ship_org_id
786 from org_organization_definitions ood,
787 hr_locations hrl
788 where hrl.ship_to_location_id = X_temp_ship_to_loc_id
789 and hrl.inventory_organization_id = ood.organization_id(+)
790 and ( ood.set_of_books_id IS NULL
791 or ood.set_of_books_id = X_set_of_books_id); */
792
793 /* Get the Org name, Org id of the location's inventory Org */
794
795 /* select ood.organization_code,
796 ood.organization_name,
797 ood.organization_id
798 into X_ship_org_code,
799 X_ship_org_name,
800 X_ship_org_id
801 from org_organization_definitions ood
802 where ood.organization_id(+) = X_inv_org_id
803 and ( ood.set_of_books_id IS NULL
804 or ood.set_of_books_id = X_set_of_books_id);*/
805
806 /* Get the Org Code and Name for the given Ship-To-Loc's
807 ** Inventory Org Id */
808
809 po_orgs_sv.get_org_info(X_inv_org_id, X_set_of_books_id,
810 X_ship_org_code, X_ship_org_name);
811
812 /* The procedure get_org_info cannot pass back the ORG ID.
813 ** If there is an ORG exisiting for that location, need to
814 ** populate the ship_to_org_id as well */
815
816 if X_ship_org_code is not null then
817 X_ship_org_id := X_inv_org_id;
818 end if;
819
820 exception
821
822 when no_data_found then
823 X_ship_to_loc_dsp := '';
824 when too_many_rows then
825 X_ship_to_loc_dsp := '';
826 when others then
827 po_message_s.sql_error('get_ship_to_loc_attributes',X_progress,sqlcode);
828 raise;
829
830
831 end get_ship_to_loc_attributes;
832
833
834 /*===========================================================================
835
836 PROCEDURE NAME : get_bill_to_loc_attributes()
837
838 ===========================================================================*/
839
840 procedure get_bill_to_loc_attributes (X_temp_bill_to_loc_id IN number, X_bill_to_loc_dsp IN OUT NOCOPY varchar2) is
841
842 X_progress varchar2(3) := '';
843 X_inv_org_id number;
844 begin
845
846 X_progress := '010';
847
848 /* select location_code
849 into X_bill_to_loc_dsp
850 from hr_locations
851 where location_id = X_temp_bill_to_loc_id; */
852
853 /* Get the Bill_to_location for a given location_id
854 ** The third parameter X_inv_org_id is passed back by the procedure, but never used
855 ** in this case */
856
857 po_locations_s.get_loc_attributes(X_temp_bill_to_loc_id, X_bill_to_loc_dsp, X_inv_org_id);
858
859
860 exception
861
862 when no_data_found then
863 X_bill_to_loc_dsp := '';
864 when too_many_rows then
865 X_bill_to_loc_dsp := '';
866 when others then
867 po_message_s.sql_error('get_bill_to_loc_attributes',X_progress,sqlcode);
868 raise;
869
870 end get_bill_to_loc_attributes;
871
872 /* ========================================================================
873
874 PROCEDURE get_vendor_name()
875
876 ===========================================================================*/
877
878 PROCEDURE get_vendor_name
879 (X_vendor_id IN NUMBER,
880 X_vendor_name IN OUT NOCOPY VARCHAR2) IS
881
882 X_progress varchar2(3) := '';
883
884 /* Get the vendor name associated with the vendor id
885 */
886
887 CURSOR C is
888 SELECT POV.vendor_name
889 FROM PO_VENDORS POV
890 WHERE POV.vendor_id = X_vendor_id;
891
892 BEGIN
893
894 -- dbms_output.put_line('Before open cursor');
895
896 if (X_vendor_id is not null) then
897
898 X_progress := '010';
899 OPEN C;
900 X_progress := '020';
901
902 FETCH C into X_vendor_name;
903
904 CLOSE C;
905
906 -- dbms_output.put_line('Vendor Name'||X_vendor_name);
907
908 end if;
909
910 EXCEPTION
911
912 when others then
913 -- dbms_output.put_line('In exception');
914 po_message_s.sql_error('get_vendor_name', X_progress, sqlcode);
915 raise;
916 END get_vendor_name;
917
918
919 /* =========================================================================
920 PROCEDURE test_get_vendor()
921 ==============================================================================*/
922
923 PROCEDURE test_get_vendor (X_vendor_id IN NUMBER) IS
924
925 X_vendor_namea PO_VENDORS.VENDOR_NAME%TYPE; -- Bug 2823775
926
927 BEGIN
928
929 po_vendors_sv.get_vendor_name(X_vendor_id, X_vendor_namea);
930
931 END test_get_vendor;
932
933
934 /* ========================================================================
935 Bug #508009
936 FUNCTION get_vendor_name_func()
937
938 ===========================================================================*/
939
940 FUNCTION get_vendor_name_func (X_vendor_id IN number)
941
942 RETURN VARCHAR2
943 IS
944 X_vendor_name PO_VENDORS.VENDOR_NAME%TYPE := ''; -- Bug 2823775
945 X_progress varchar2(3) := '';
946
947 BEGIN
948
949 if X_vendor_id is null then
950 X_vendor_name := '';
951 RETURN X_vendor_name;
952 end if;
953
954 X_progress := '010';
955
956 SELECT pov.vendor_name
957 INTO X_vendor_name
958 FROM po_vendors pov
959 WHERE pov.vendor_id = X_vendor_id;
960
961 RETURN X_vendor_name;
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 -- po_message_s.sql_error('get_vendor_name',X_progress,sqlcode);
966 raise;
967
968 END get_vendor_name_func;
969
970
971
972 /*===========================================================================
973
974 PROCEDURE NAME : get_vendor_details
975
976 ===========================================================================*/
977
978 procedure get_vendor_details (x_vendor_id IN NUMBER,
979 x_vendor_site_id IN NUMBER,
980 x_vendor_contact_id IN NUMBER,
981 x_vendor_name IN OUT NOCOPY VARCHAR2,
982 x_vendor_location IN OUT NOCOPY VARCHAR2,
983 x_vendor_contact IN OUT NOCOPY VARCHAR2,
984 x_vendor_phone IN OUT NOCOPY VARCHAR2)
985 IS
986
987 x_progress VARCHAR2(3) := '';
988
989 BEGIN
990
991 x_progress := '010';
992
993 po_vendors_sv.get_vendor_name (x_vendor_id, x_vendor_name);
994
995 x_progress := '020';
996
997 po_vendor_sites_sv.get_vendor_site_name (x_vendor_site_id,
998 x_vendor_location);
999 x_progress := '030';
1000
1001 po_vendor_contacts_sv.get_contact_info (x_vendor_contact_id,
1002 x_vendor_contact,
1003 x_vendor_phone);
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 po_message_s.sql_error('get_vendor_details',x_progress,sqlcode);
1008 raise;
1009
1010 END get_vendor_details;
1011
1012 /*===========================================================================
1013
1014 PROCEDURE NAME : derive_vendor_info()
1015
1016 ===========================================================================*/
1017
1018 PROCEDURE derive_vendor_info (
1019 p_vendor_record IN OUT NOCOPY rcv_shipment_header_sv.VendorRecType) IS
1020
1021 cid INTEGER;
1022 rows_processed INTEGER;
1023 sql_str VARCHAR2(2000);
1024
1025 vendor_name_null BOOLEAN := TRUE;
1026 vendor_id_null BOOLEAN := TRUE;
1027 vendor_num_null BOOLEAN := TRUE;
1028
1029 BEGIN
1030
1031 sql_str := 'select pov.vendor_name, pov.vendor_id, pov.segment1 from po_vendors pov where ';
1032
1033 IF p_vendor_record.vendor_name IS NULL and
1034 p_vendor_record.vendor_id IS NULL and
1035 p_vendor_record.vendor_num IS NULL THEN
1036
1037 p_vendor_record.error_record.error_status := 'W';
1038 RETURN;
1039
1040 END IF;
1041
1042 IF p_vendor_record.vendor_name IS NOT NULL and
1043 p_vendor_record.vendor_id IS NOT NULL and
1044 p_vendor_record.vendor_num IS NOT NULL THEN
1045
1046 p_vendor_record.error_record.error_status := 'S';
1047 RETURN;
1048
1049 END IF;
1050
1051 IF p_vendor_record.vendor_name IS NOT NULL THEN
1052
1053 sql_str := sql_str || ' pov.vendor_name = :v_name and';
1054 vendor_name_null := FALSE;
1055
1056 END IF;
1057
1058 IF p_vendor_record.vendor_id IS NOT NULL THEN
1059
1060 sql_str := sql_str || ' pov.vendor_id = :v_id and';
1061 vendor_id_null := FALSE;
1062
1063 END IF;
1064
1065 IF p_vendor_record.vendor_num IS NOT NULL THEN
1066
1067 sql_str := sql_str || ' pov.segment1 = :v_num and';
1068 vendor_num_null := FALSE;
1069
1070 END IF;
1071
1072 sql_str := substr(sql_str,1,length(sql_str)-3);
1073
1074 -- dbms_output.put_line(substr(sql_str,1,255));
1075 -- dbms_output.put_line(substr(sql_str,256,255));
1076 -- dbms_output.put_line(substr(sql_str,513,255));
1077
1078 cid := dbms_sql.open_cursor;
1079
1080 dbms_sql.parse(cid, sql_str , dbms_sql.native);
1081
1082 /* Bug 4885978: Changed the length of vendor_name, vendor_num
1083 as specified in PO_VENDORS table */
1084
1085 dbms_sql.define_column(cid,1,p_vendor_record.vendor_name,240);
1086 dbms_sql.define_column(cid,2,p_vendor_record.vendor_id);
1087 dbms_sql.define_column(cid,3,p_vendor_record.vendor_num,30);
1088
1089 IF NOT vendor_name_null THEN
1090
1091 dbms_sql.bind_variable(cid,'v_name',p_vendor_record.vendor_name);
1092
1093 END IF;
1094
1095 IF NOT vendor_id_null THEN
1096
1097 dbms_sql.bind_variable(cid,'v_id',p_vendor_record.vendor_id);
1098
1099 END IF;
1100
1101 IF NOT vendor_num_null THEN
1102
1103 dbms_sql.bind_variable(cid,'v_num',p_vendor_record.vendor_num);
1104
1105 END IF;
1106
1107 rows_processed := dbms_sql.execute_and_fetch(cid);
1108
1109 IF rows_processed = 1 THEN
1110
1111 IF vendor_name_null THEN
1112 dbms_sql.column_value(cid,1,p_vendor_record.vendor_name);
1113 END IF;
1114
1115 IF vendor_id_null THEN
1116 dbms_sql.column_value(cid,2,p_vendor_record.vendor_id);
1117 END IF;
1118
1119 IF vendor_num_null THEN
1120 dbms_sql.column_value(cid,3,p_vendor_record.vendor_num);
1121 END IF;
1122
1123
1124 p_vendor_record.error_record.error_status := 'S';
1125
1126 ELSIF rows_processed = 0 THEN
1127
1128 p_vendor_record.error_record.error_status := 'W';
1129
1130 ELSE
1131
1132 p_vendor_record.error_record.error_status := 'W';
1133
1134 END IF;
1135
1136
1137 IF dbms_sql.is_open(cid) THEN
1138 dbms_sql.close_cursor(cid);
1139 END IF;
1140
1141 EXCEPTION
1142 WHEN others THEN
1143 IF dbms_sql.is_open(cid) THEN
1144 dbms_sql.close_cursor(cid);
1145 END IF;
1146 p_vendor_record.error_record.error_status := 'U';
1147 p_vendor_record.error_record.error_message := sqlerrm;
1148 IF (g_asn_debug = 'Y') THEN
1149 asn_debug.put_line(p_vendor_record.error_record.error_message);
1150 END IF;
1151
1152 END derive_vendor_info;
1153
1154 /*===========================================================================
1155
1156 PROCEDURE NAME : validate_vendor_info()
1157
1158 ===========================================================================*/
1159
1160 PROCEDURE validate_vendor_info (p_vendor_record IN OUT NOCOPY
1161 rcv_shipment_header_sv.VendorRecType) IS
1162
1163 X_cid integer;
1164 X_rows_processed integer;
1165 X_sql_str varchar2(2000) := 'SELECT pov.start_date_active, pov.end_date_active, pov.enabled_flag, pov.hold_flag FROM po_vendors pov where ';
1166
1167 X_vendor_name_null boolean := TRUE;
1168 X_vendor_id_null boolean := TRUE;
1169 X_vendor_num_null boolean := TRUE;
1170 X_sysdate date := sysdate;
1171
1172 X_start_date_active date;
1173 X_end_date_active date;
1174 X_enabled_flag varchar2(1);
1175 X_hold_flag varchar2(1);
1176
1177 BEGIN
1178
1179 IF p_vendor_record.vendor_id IS NULL and
1180 p_vendor_record.vendor_name IS NULL and
1181 p_vendor_record.vendor_num IS NULL THEN
1182
1183 -- dbms_output.put_line('Major Problem here');
1184 p_vendor_record.error_record.error_status := 'E';
1185 p_vendor_record.error_record.error_message := 'All Null';
1186 RETURN;
1187
1188 END IF;
1189
1190
1191 -- Build where clause for selection based on which passed columns have values
1192 -- Check whether the vendor is enabled
1193 -- Check that the vendor is not on hold
1194
1195
1196 IF p_vendor_record.vendor_name IS NOT NULL THEN
1197
1198 X_sql_str := X_sql_str || ' pov.vendor_name = :v_name and';
1199 X_vendor_name_null := FALSE;
1200
1201 END IF;
1202
1203 IF p_vendor_record.vendor_id IS NOT NULL THEN
1204
1205 X_sql_str := X_sql_str || ' pov.vendor_id = :v_id and';
1206 X_vendor_id_null := FALSE;
1207
1208 END IF;
1209
1210 IF p_vendor_record.vendor_num IS NOT NULL THEN
1211
1212 X_sql_str := X_sql_str || ' pov.segment1 = :v_num and';
1213 X_vendor_num_null := FALSE;
1214
1215 END IF;
1216
1217 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
1218
1219 -- dbms_output.put_line(substr(X_sql_str,1,255));
1220 -- dbms_output.put_line(substr(X_sql_str,256,255));
1221 -- dbms_output.put_line(substr(X_sql_str,513,255));
1222
1223 X_cid := dbms_sql.open_cursor;
1224
1225 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
1226
1227 dbms_sql.define_column(X_cid,1,X_start_date_active);
1228 dbms_sql.define_column(X_cid,2,X_end_date_active);
1229 dbms_sql.define_column(X_cid,3,X_enabled_flag,1);
1230 dbms_sql.define_column(X_cid,4,X_hold_flag,1);
1231
1232 IF NOT X_vendor_name_null THEN
1233
1234 dbms_sql.bind_variable(X_cid,'v_name',p_vendor_record.vendor_name);
1235
1236 END IF;
1237
1238 IF NOT X_vendor_id_null THEN
1239
1240 dbms_sql.bind_variable(X_cid,'v_id',p_vendor_record.vendor_id);
1241
1242 END IF;
1243
1244 IF NOT X_vendor_num_null THEN
1245
1246 dbms_sql.bind_variable(X_cid,'v_num',p_vendor_record.vendor_num);
1247
1248 END IF;
1249
1250 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
1251
1252 IF X_rows_processed = 1 THEN
1253
1254 dbms_sql.column_value(X_cid,1,X_start_date_active);
1255 dbms_sql.column_value(X_cid,2,X_end_date_active);
1256 dbms_sql.column_value(X_cid,3,X_enabled_flag);
1257 dbms_sql.column_value(X_cid,4,X_hold_flag);
1258
1259 -- Check for whether vendor is enabled in the active date range
1260
1261 IF NOT (X_sysdate BETWEEN nvl(X_start_date_active, X_sysdate -1)
1262 AND nvl(X_end_date_active, X_sysdate + 1)
1263 AND nvl(X_enabled_flag,'Y') = 'Y') then
1264
1265 -- dbms_output.put_line('Vendor not active');
1266 p_vendor_record.error_record.error_status := 'E';
1267 p_vendor_record.error_record.error_message := 'VEN_DISABLED';
1268
1269 IF dbms_sql.is_open(X_cid) THEN
1270 dbms_sql.close_cursor(X_cid);
1271 END IF;
1272
1273 RETURN;
1274
1275 END IF;
1276
1277 -- Check for whether vendor is on hold
1278 IF NOT nvl(X_hold_flag,'N') = 'N' THEN
1279
1280 -- dbms_output.put_line('Vendor is on hold');
1281 p_vendor_record.error_record.error_status := 'E';
1282 p_vendor_record.error_record.error_message := 'VEN_HOLD';
1283
1284 IF dbms_sql.is_open(X_cid) THEN
1285 dbms_sql.close_cursor(X_cid);
1286 END IF;
1287
1288 RETURN;
1289
1290 END IF;
1291
1292 p_vendor_record.error_record.error_status := 'S';
1293 p_vendor_record.error_record.error_message := null;
1294
1295 ELSIF X_rows_processed = 0 THEN -- No rows found so not a valid Vendor
1296
1297 p_vendor_record.error_record.error_status := 'E';
1298 p_vendor_record.error_record.error_message := 'VEN_ID';
1299
1300 ELSE -- More then 1 row.
1301
1302 p_vendor_record.error_record.error_status := 'E';
1303 p_vendor_record.error_record.error_message := 'TOOMANYROWS';
1304
1305 END IF;
1306
1307 IF dbms_sql.is_open(X_cid) THEN
1308 dbms_sql.close_cursor(X_cid);
1309 END IF;
1310
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 IF dbms_sql.is_open(X_cid) THEN
1314 dbms_sql.close_cursor(X_cid);
1315 END IF;
1316 p_vendor_record.error_record.error_message := sqlerrm;
1317 p_vendor_record.error_record.error_status := 'U';
1318 IF (g_asn_debug = 'Y') THEN
1319 asn_debug.put_line(p_vendor_record.error_record.error_message);
1320 END IF;
1321 -- raise;
1322
1323 END validate_vendor_info;
1324
1325 END PO_VENDORS_SV;
[Home] [Help]