1 PACKAGE BODY PO_VENDORS_SV1 AS
2 /* $Header: POXPIVDB.pls 115.2 2002/11/23 02:50:28 sbull ship $ */
3
4 /*================================================================
5
6 FUNCTION NAME: val_vendor_info()
7
8 ==================================================================*/
9 FUNCTION val_vendor_info(X_vendor_id IN NUMBER,
10 X_vendor_site_type IN VARCHAR2,
11 X_vendor_site_id IN NUMBER,
12 X_vendor_contact_id IN NUMBER,
13 X_error_code IN OUT NOCOPY VARCHAR2)
14 RETURN BOOLEAN IS
15
16 x_temp_1 binary_integer;
17 x_temp_2 binary_integer;
18 x_temp_3 binary_integer;
19 x_temp_4 binary_integer;
20 x_progress varchar2(3) := null;
21
22 BEGIN
23 x_progress := '010';
24
25 /* make sure that the vendor_id is valid in po_suppliers_val_v
26 view */
27
28 SELECT count(*)
29 INTO x_temp_1
30 FROM po_suppliers_val_v
31 WHERE vendor_id = X_vendor_id;
32
33 IF x_temp_1 = 0 THEN
34 X_error_code := 'PO_PDOI_INVALID_VENDOR';
35 RETURN FALSE;
36 END IF;
37
38 IF (X_vendor_id IS NOT NULL) AND (X_vendor_site_id IS NOT NULL) THEN
39 /* make sure the vendor_site_id is valid */
40
41 IF (X_vendor_site_type NOT IN ('RFQ SITE')) THEN
42 x_progress := '020';
43 SELECT count(*)
44 INTO x_temp_2
45 FROM po_supplier_po_sites_val_v
46 WHERE vendor_id = X_vendor_id
47 AND vendor_site_id = X_vendor_site_id;
48
49 IF x_temp_2 = 0 THEN
50 X_error_code := 'PO_PDOI_INVALID_VENDOR_SITE';
51 RETURN FALSE;
52 END IF;
53
54 ELSE /* vendor_site_type = 'RFQ SITE' */
55 x_progress := '030';
56 SELECT count(*)
57 INTO x_temp_3
58 FROM po_supplier_sites_val_v
59 WHERE vendor_id = X_vendor_id
60 AND vendor_site_id = X_vendor_site_id;
61
62 IF x_temp_3 = 0 THEN
63 X_error_code := 'PO_PDOI_INVALID_VENDOR_SITE';
64 RETURN FALSE;
65 END IF;
66 END IF;
67 END IF;
68
69 IF (X_vendor_id IS NOT NULL) AND (X_vendor_site_id is not null) AND
70 (X_vendor_contact_id IS NOT NULL) THEN
71 x_progress := '040';
72
73 /* make sure the vendor_contact_id is valid */
74 SELECT count(*)
75 INTO x_temp_4
76 FROM po_vendor_contacts
77 WHERE vendor_site_id = X_vendor_site_id
78 AND vendor_contact_id = X_vendor_contact_id;
79
80 IF x_temp_4 = 0 THEN
81 X_error_code := 'PO_PDOI_INVALID_VDR_CNTCT';
82 RETURN FALSE;
83 END IF;
84 END IF;
85
86 RETURN TRUE;
87
88 EXCEPTION
89 WHEN others THEN
90 po_message_s.sql_error('val_vendor_info', x_progress,sqlcode);
91 raise;
92 END val_vendor_info;
93
94 /*==============================================================
95 FUNCTION NAME : derive_vendor_id
96 ===============================================================*/
97
98 FUNCTION derive_vendor_id(X_vendor_name IN VARCHAR2,
99 X_vendor_num IN VARCHAR2)
100 return NUMBER IS
101
102 X_progress varchar2(3) := NULL;
103 X_vendor_name_v number := NULL;
104 X_vendor_count binary_integer := 0;
105
106 BEGIN
107
108 X_progress := '010';
109 /* We will select the vendor id from the po_suppliers_val_v view
110 based on the input vendor name. */
111
112 SELECT COUNT(*)
113 INTO X_vendor_count
114 FROM po_suppliers_val_v
115 WHERE (vendor_name = X_vendor_name
116 OR
117 segment1 = X_vendor_num);
118
119 IF X_vendor_count = 1 THEN
120 X_progress := '020';
121
122 SELECT vendor_id
123 INTO X_vendor_name_v
124 FROM po_suppliers_val_v
125 WHERE (vendor_name = X_vendor_name
126 OR
127 segment1 = X_vendor_num);
128
129 RETURN X_vendor_name_v;
130
131 ELSIF (X_vendor_count = 0) OR (X_vendor_count > 1) THEN
132 RETURN NULL;
133 END IF;
134
135 EXCEPTION
136 WHEN no_data_found THEN
137 RETURN NULL;
138 WHEN others THEN
139 po_message_s.sql_error('derive_vendor_id',X_progress, sqlcode);
140 raise;
141
142 END derive_vendor_id;
143
144 END PO_VENDORS_SV1;
[Home] [Help]