1 PACKAGE BODY PO_VENDORS_GRP AS
2 /* $Header: POXGVENB.pls 120.2 2005/12/14 14:52:27 bao noship $ */
3 -------------------------------------------------------------------------------
4 --Start of Comments
5 --Name: get_external_userlist
6 --Pre-reqs:
7 -- None.
8 --Modifies:
9 -- None.
10 --Locks:
11 -- None.
12 --Function:
13 -- This procedure is built as a wrapper over procedure get_supplier_userlist
14 -- in the package PO_VENDORS_PVT to be allowed to be called by other apps.
15 -- This procedure is called by external apps team to determine the supplier users
16 -- to send notifications to.
17 --Parameters:
18 --IN:
19 --p_document_id
20 -- PO header ID
21 --p_document_type
22 -- Contracts business document type ex: PA_BLANKET or PO_STANDARD
23 -- This will be parsed to retrieve the PO document type
24 --p_external_contact_id
25 -- Supplier contact id on the deliverable. Default is null.
26 --p_init_msg_list
27 -- True/False parameter to initialize message list
28 --p_api_version
29 -- API version
30 --OUT:
31 --x_msg_count
32 -- Message count
33 --x_msg_data
34 -- message data
35 --x_return_status
36 -- FND_API.G_RET_STS_ERROR - for expected error
37 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
38 -- FND_API.G_RET_STS_SUCCESS - for success
39 --x_external_user_tbl
40 -- PL/SQL table of supplier user names
41 --Notes:
42 -- SAHEGDE 07/18/2003
43 -- This procedure calls get_supplier_userlist in PO_VENDORS_PVT to
44 -- retrieve supplier user names as VARCHAR2 as well as PL/SQL table, besides
45 -- other OUT parameters. Going forward, signature of the get_supplier_userlist
46 -- might change to return only PL/SQL table. The callout then will need to
47 -- accomodate this change. This however will not change the GRP API signature.
48 --Testing:
49 --
50 --End of Comments
51 -------------------------------------------------------------------------------
52
53 PROCEDURE get_external_userlist
54 (p_api_version IN NUMBER
55 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
56 ,p_document_id IN NUMBER
57 ,p_document_type IN VARCHAR2
58 ,p_external_contact_id IN NUMBER DEFAULT NULL
59 ,x_return_status OUT NOCOPY VARCHAR2
60 ,x_msg_count OUT NOCOPY NUMBER
61 ,x_msg_data OUT NOCOPY VARCHAR2
62 ,x_external_user_tbl OUT NOCOPY external_user_tbl_type) IS
63
64 -- declare local variables
65 l_api_name CONSTANT VARCHAR2(30) := 'get_external_userlist';
66 l_api_version CONSTANT NUMBER := 1.0;
67 l_document_type po_headers.type_lookup_code%TYPE;
68 l_supplier_userlist_for_sql VARCHAR2(32000) := NULL;
69 l_num_users NUMBER := 0;
70 l_supplier_userlist VARCHAR2(31990);
71 l_vendor_id NUMBER;
72 l_return_status VARCHAR2(1);
73 l_external_user_tbl external_user_tbl_type;
74
75
76 BEGIN
77
78 IF NOT (FND_API.compatible_api_call(l_api_version
79 ,p_api_version
80 ,l_api_name
81 ,g_pkg_name)) THEN
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
83 END IF;
84
85 -- initialize API return status to success
86 x_return_status:= FND_API.G_RET_STS_SUCCESS;
87
88 -- initialize meesage list
89 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93
94 -- parse doc type if contracts document type
95 IF (p_document_type IN ('PO_STANDARD', 'PA_BLANKET', 'PA_CONTRACT')) THEN
96 l_document_type := SUBSTR(p_document_type, 1, 2);
97 ELSE
98 l_document_type := p_document_type;
99 END IF;
100
101 -- call procedure to fetch supplier user list.
102 po_vendors_pvt.get_supplier_userlist(
103 p_document_id => p_document_id
104 ,p_document_type => l_document_type
105 ,p_external_contact_id => p_external_contact_id
106 ,x_return_status => l_return_status
107 ,x_supplier_user_tbl => l_external_user_tbl
108 ,x_supplier_userlist => l_supplier_userlist
109 ,x_supplier_userlist_for_sql => l_supplier_userlist_for_sql
110 ,x_num_users => l_num_users
111 ,x_vendor_id => l_vendor_id);
112
113
114 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118 -- populate the out parameter.
119 x_external_user_tbl := l_external_user_tbl;
120
121 EXCEPTION
122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
125 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
126 IF (g_fnd_debug='Y') THEN
127 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
128 FND_LOG.string(FND_LOG.level_unexpected
129 ,g_module_prefix ||l_api_name
130 ,SQLERRM);
131 END IF;
132 END IF;
133 END IF;
134 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
135 ,p_data => x_msg_data);
136 WHEN OTHERS THEN
137 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
139 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
140 IF (g_fnd_debug='Y') THEN
141 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
142 FND_LOG.string(log_level => FND_LOG.level_unexpected
143 ,module => g_module_prefix ||l_api_name
144 ,message => SQLERRM);
145 END IF;
146 END IF;
147 END IF;
148 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
149 ,p_data => x_msg_data);
150 END get_external_userlist;
151
152 -------------------------------------------------------------------------------
153 --Start of Comments
154 --Name: get_external_userlist
155 --Pre-reqs:
156 -- None.
157 --Modifies:
158 -- None.
159 --Locks:
160 -- None.
161 --Function:
162 -- This procedure is built as a wrapper over procedure get_supplier_userlist
163 -- in the package PO_VENDORS_PVT to be allowed to be called by other apps.
164 -- This procedure overloaded to return additional data elements required by
165 -- one of its caller po_reapproval_inti1.locate_notifier
166 --Parameters:
167 --IN:
168 --p_document_id
169 -- PO header ID
170 --p_document_type
171 -- Contracts business document type ex: PA_BLANKET or PO_STANDARD
172 -- This will be parsed to retrieve the PO document type
173 --p_external_contact_id
174 -- Supplier contact id on the deliverable. Default is null.
175 --p_init_msg_list
176 -- True/False parameter to initialize message list
177 --p_api_version
178 -- API version
179 --OUT:
180 --x_msg_count
181 -- Message count
182 --x_msg_data
183 -- message data
184 --x_return_status
185 -- FND_API.G_RET_STS_ERROR - for expected error
186 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
187 -- FND_API.G_RET_STS_SUCCESS - for success
188 --x_external_user_tbl
189 -- PL/SQL table of supplier user names
190 --x_supplier_userlist
191 -- space delimited user list
192 --x_supplier_userlist_for_sql
193 -- comma delimited user list
194 --x_num_users
195 -- number of users
196 --x_vendor_id
197 -- vendor id on the PO
198 --Notes:
199 -- SAHEGDE 07/18/2003
200 -- This procedure calls get_supplier_userlist in PO_VENDORS_PVT to
201 -- retrieve supplier user names as VARCHAR2 as well as PL/SQL table, besides
202 -- other OUT parameters. Going forward, signature of the get_supplier_userlist
203 -- might change to return only PL/SQL table. The callout then will need to
204 -- accomodate this change. This however will not change the GRP API signature.
205 --Testing:
206 --
207 --End of Comments
208 -------------------------------------------------------------------------------
209
210 PROCEDURE get_external_userlist
211 (p_api_version IN NUMBER
212 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
213 ,p_document_id IN NUMBER
214 ,p_document_type IN VARCHAR2
215 ,p_external_contact_id IN NUMBER DEFAULT NULL
216 ,x_return_status OUT NOCOPY VARCHAR2
217 ,x_msg_count OUT NOCOPY NUMBER
218 ,x_msg_data OUT NOCOPY VARCHAR2
219 ,x_external_user_tbl OUT NOCOPY external_user_tbl_type
220 ,x_supplier_userlist OUT NOCOPY VARCHAR2
221 ,x_supplier_userlist_for_sql OUT NOCOPY VARCHAR2
222 ,x_num_users OUT NOCOPY NUMBER
223 ,x_vendor_id OUT NOCOPY NUMBER) IS
224
225 -- declare local variables
226 l_api_name CONSTANT VARCHAR2(30) := 'get_external_userlist';
227 l_api_version CONSTANT NUMBER := 1.0;
228 l_document_type po_headers.type_lookup_code%TYPE;
229 l_supplier_userlist_for_sql VARCHAR2(32000) := NULL;
230 l_num_users NUMBER := 0;
231 l_supplier_userlist VARCHAR2(31990);
232 l_vendor_id NUMBER;
233 l_return_status VARCHAR2(1);
234 l_external_user_tbl external_user_tbl_type;
235
236
237 BEGIN
238
239 IF NOT (FND_API.compatible_api_call(l_api_version
240 ,p_api_version
241 ,l_api_name
242 ,g_pkg_name)) THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 -- initialize API return status to success
247 x_return_status:= FND_API.G_RET_STS_SUCCESS;
248
249 -- initialize meesage list
250 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
251 FND_MSG_PUB.initialize;
252 END IF;
253
254 -- parse doc type if contracts document type
255 IF (p_document_type IN ('PO_STANDARD', 'PA_BLANKET', 'PA_CONTRACT')) THEN
256 l_document_type := SUBSTR(p_document_type, 1, 2);
257 ELSE
258 l_document_type := p_document_type;
259 END IF;
260
261 -- call procedure to fetch supplier user list.
262 po_vendors_pvt.get_supplier_userlist(
263 p_document_id => p_document_id
264 ,p_document_type => l_document_type
265 ,p_external_contact_id => p_external_contact_id
266 ,x_return_status => l_return_status
267 ,x_supplier_user_tbl => l_external_user_tbl
268 ,x_supplier_userlist => l_supplier_userlist
269 ,x_supplier_userlist_for_sql => l_supplier_userlist_for_sql
270 ,x_num_users => l_num_users
271 ,x_vendor_id => l_vendor_id);
272
273
274 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 -- populate the out parameter.
279 x_external_user_tbl := l_external_user_tbl;
280 x_supplier_userlist := l_supplier_userlist;
281 x_supplier_userlist_for_sql := l_supplier_userlist_for_sql;
282 x_num_users := l_num_users;
283 x_vendor_id := l_vendor_id;
284
285 EXCEPTION
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
289 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
290 IF (g_fnd_debug='Y') THEN
291 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
292 FND_LOG.string(FND_LOG.level_unexpected
293 ,g_module_prefix ||l_api_name
294 ,SQLERRM);
295 END IF;
296 END IF;
297 END IF;
298 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
299 ,p_data => x_msg_data);
300 WHEN OTHERS THEN
301 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
303 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
304 IF (g_fnd_debug='Y') THEN
305 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
306 FND_LOG.string(log_level => FND_LOG.level_unexpected
307 ,module => g_module_prefix ||l_api_name
308 ,message => SQLERRM);
309 END IF;
310 END IF;
311 END IF;
312 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
313 ,p_data => x_msg_data);
314 END get_external_userlist;
315
316 END PO_VENDORS_GRP;
[Home] [Help]