1 PACKAGE BODY PO_VENDOR_CONTACTS_SV AS
2 /* $Header: POXVDVCB.pls 115.6 2004/06/22 02:09:24 mbhargav ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME : val_vendor_contact()
7
8 ===========================================================================*/
9 FUNCTION val_vendor_contact( p_vendor_contact_id IN NUMBER,
10 p_vendor_site_id IN NUMBER --<Bug 3692519>
11 ) return BOOLEAN IS
12
13 X_progress varchar2(3) := NULL;
14 X_vendor_contact_id_v number := NULL;
15
16 BEGIN
17
18 X_progress := '010';
19
20 /* Check if the given Vendor Contact is active */
21
22 SELECT vendor_contact_id
23 INTO X_vendor_contact_id_v
24 FROM po_vendor_contacts
25 WHERE sysdate < nvl(inactive_date, sysdate + 1)
26 AND vendor_contact_id = p_vendor_contact_id
27 AND vendor_site_id = p_vendor_site_id; --<Bug 3692519>
28
29 return (TRUE);
30
31 EXCEPTION
32
33 when no_data_found then
34 return (FALSE);
35 when others then
36 po_message_s.sql_error('val_vendor_contact',X_progress,sqlcode);
37 raise;
38
39 END val_vendor_contact;
40
41 /*===========================================================================
42
43 PROCEDURE NAME: get_vendor_contact()
44
45 ===========================================================================*/
46
47 PROCEDURE get_vendor_contact(X_vendor_site_id IN NUMBER,
48 X_vendor_contact_id IN OUT NOCOPY number,
49 X_vendor_contact_name IN OUT NOCOPY varchar2 ) IS
50
51 X_progress VARCHAR2(3) := NULL;
52 X_vendor_contact_count number;
53
54 BEGIN
55 X_Progress := '010';
56
57 /* Return the count of Vendor Contacts
58 ** who are still active for the given site */
59
60
61 SELECT count(vendor_contact_id),
62 -- bug 590653: need to handle null first_name
63 -- max((last_name||', '||first_name)),
64 max((last_name || decode(first_name,null,null,', ') || first_name)),
65 max(vendor_contact_id)
66 INTO X_vendor_contact_count,
67 X_vendor_contact_name,
68 X_vendor_contact_id
69 FROM po_vendor_contacts
70 WHERE vendor_site_id = X_vendor_site_id
71 AND sysdate < nvl(inactive_date, sysdate + 1);
72
73 /* If there is more than 1 ACTIVE vendor contact for the
74 ** site, we cannot determine the default, so
75 ** return NULL for the DEFAULT VENDOR CONTACT */
76
77 if X_vendor_contact_count <> 1 then
78 X_vendor_contact_id := '';
79 X_vendor_contact_name := '';
80 end if;
81
82 EXCEPTION
83 when too_many_rows then
84 X_vendor_contact_id := '';
85 X_vendor_contact_name := '';
86 when no_data_found then
87 X_vendor_contact_id := '';
88 X_vendor_contact_name := '';
89
90
91 when others then
92 po_message_s.sql_error('get_vendor_contact', X_progress, sqlcode);
93 raise;
94
95 END get_vendor_contact;
96
97
98 /*===========================================================================
99
100 PROCEDURE NAME: get_contact_info
101
102 ===========================================================================*/
103
104 PROCEDURE get_contact_info (x_vendor_contact_id IN NUMBER,
105 x_vendor_contact_name IN OUT NOCOPY VARCHAR2,
106 x_vendor_phone IN OUT NOCOPY VARCHAR2) IS
107
108 X_progress VARCHAR2(3) := NULL;
109
110 BEGIN
111 x_progress := '010';
112 --bug#3441462 modified the query because the length of phone number fields in PO is
113 --25 characters. If the contact has a telephone number of 15 digits and area code of
114 --10 digits then with the space the total length goes up to 26 digits. To prevent
115 --this we check if the length is less than 25 if yes then we concatenate
116 --area code and phone number with a space. Or else we concatenate without a space.
117
118 SELECT pvc.last_name || decode(pvc.last_name,null,null,
119 decode(pvc.first_name,null,null,', '))
120 || pvc.first_name,
121 decode(trunc(length(pvc.area_code ||' '||pvc.phone)/26),0,
122 pvc.area_code || decode(pvc.area_code,null,null,decode(pvc.phone,null,null,' '))||pvc.phone,
123 pvc.area_code||pvc.phone )
124
125 INTO x_vendor_contact_name,
126 x_vendor_phone
127 FROM po_vendor_contacts pvc
128 WHERE pvc.vendor_contact_id = x_vendor_contact_id;
129
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 X_vendor_contact_name := '';
133 X_vendor_phone := '';
134
135 --<Bug 3564169 mbhargav START>
136 WHEN TOO_MANY_ROWS THEN
137 X_vendor_contact_name := '';
138 X_vendor_phone := '';
139 --<Bug 3564169 mbhargav END>
140
141 WHEN OTHERS THEN
142 po_message_s.sql_error('get_vendor_contact', X_progress, sqlcode);
143 raise;
144
145 END get_contact_info;
146
147 --==============================================================================
148 -- FUNCTION : get_vendor_contact_id -- <Bug 3692519>
149 -- TYPE : Private
150 --
151 -- REQUIRES : p_po_header_id must be a valid document ID.
152 -- MODIFIES : -
153 --
154 -- DESCRIPTION : Gets the vendor_contact_id specified on a particular PO document.
155 --
156 -- PARAMETERS : p_po_header_id - document ID
157 --
158 -- RETURNS : vendor_contact_id specified for the p_po_header_id
159 -- NULL if no contact is specified for the p_po_header_id
160 -- or if the p_po_header_id does not exist
161 --
162 -- EXCEPTIONS : -
163 --==============================================================================
164 FUNCTION get_vendor_contact_id
165 (
166 p_po_header_id IN NUMBER
167 )
168 RETURN NUMBER
169 IS
170 x_vendor_contact_id PO_HEADERS_ALL.vendor_contact_id%TYPE;
171 BEGIN
172
173 SELECT vendor_contact_id
174 INTO x_vendor_contact_id
175 FROM po_headers_all
176 WHERE po_header_id = p_po_header_id;
177
178 return (x_vendor_contact_id);
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 return (NULL);
183
184 END get_vendor_contact_id;
185
186 END PO_VENDOR_CONTACTS_SV;