1 PACKAGE BODY PO_VENDOR_CONTACTS_SV AS
2 /* $Header: POXVDVCB.pls 120.0.12020000.3 2013/02/10 23:14:58 vegajula 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_site_id IN NUMBER,
106 x_vendor_contact_name IN OUT NOCOPY VARCHAR2,
107 x_vendor_phone IN OUT NOCOPY VARCHAR2) IS
108
109 X_progress VARCHAR2(3) := NULL;
110
111 BEGIN
112 x_progress := '010';
113 --bug#3441462 modified the query because the length of phone number fields in PO is
114 --25 characters. If the contact has a telephone number of 15 digits and area code of
115 --10 digits then with the space the total length goes up to 26 digits. To prevent
116 --this we check if the length is less than 25 if yes then we concatenate
117 --area code and phone number with a space. Or else we concatenate without a space.
118
119 SELECT pvc.last_name || decode(pvc.last_name,null,null,
120 decode(pvc.first_name,null,null,', '))
121 || pvc.first_name,
122 decode(trunc(length(pvc.area_code ||' '||pvc.phone)/26),0,
123 pvc.area_code || decode(pvc.area_code,null,null,decode(pvc.phone,null,null,' '))||pvc.phone,
124 pvc.area_code||pvc.phone )
125
126 INTO x_vendor_contact_name,
127 x_vendor_phone
128 FROM po_vendor_contacts pvc
129 WHERE pvc.vendor_contact_id = x_vendor_contact_id
130 AND pvc.vendor_site_id = nvl(x_vendor_site_id,pvc.vendor_site_id);
131
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 X_vendor_contact_name := '';
135 X_vendor_phone := '';
136
137 --<Bug 3564169 mbhargav START>
138 WHEN TOO_MANY_ROWS THEN
139 X_vendor_contact_name := '';
140 X_vendor_phone := '';
141 --<Bug 3564169 mbhargav END>
142
143 WHEN OTHERS THEN
144 po_message_s.sql_error('get_vendor_contact', X_progress, sqlcode);
145 raise;
146
147 END get_contact_info;
148
149 --==============================================================================
150 -- FUNCTION : get_vendor_contact_id -- <Bug 3692519>
151 -- TYPE : Private
152 --
153 -- REQUIRES : p_po_header_id must be a valid document ID.
154 -- MODIFIES : -
155 --
156 -- DESCRIPTION : Gets the vendor_contact_id specified on a particular PO document.
157 --
158 -- PARAMETERS : p_po_header_id - document ID
159 --
160 -- RETURNS : vendor_contact_id specified for the p_po_header_id
161 -- NULL if no contact is specified for the p_po_header_id
162 -- or if the p_po_header_id does not exist
163 --
164 -- EXCEPTIONS : -
165 --==============================================================================
166 FUNCTION get_vendor_contact_id
167 (
168 p_po_header_id IN NUMBER
169 )
170 RETURN NUMBER
171 IS
172 x_vendor_contact_id PO_HEADERS_ALL.vendor_contact_id%TYPE;
173 BEGIN
174
175 SELECT vendor_contact_id
176 INTO x_vendor_contact_id
177 FROM po_headers_all
178 WHERE po_header_id = p_po_header_id;
179
180 return (x_vendor_contact_id);
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 return (NULL);
185
186 END get_vendor_contact_id;
187
188 END PO_VENDOR_CONTACTS_SV;
[Home] [Help]