[Home] [Help]
PACKAGE BODY: APPS.POS_PUB_HISTORY_BO_PKG
Source
1 PACKAGE BODY pos_pub_history_bo_pkg AS
2 /* $Header: POSPUBHIB.pls 120.1 2010/11/25 12:27:30 puppulur noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'pos_pub_history_bo_pkg';
5
6 PROCEDURE add_message(
7 p_pkg_name VARCHAR2,
8 p_api_name VARCHAR2,
9 p_err_code NUMBER) IS
10 BEGIN
11 --
12 -- Private utility procedure to add an FND message to
13 -- indicate an error has occurred.
14 --
15 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
16 fnd_msg_pub.add_exc_msg(
17 p_pkg_name => p_pkg_name,
18 p_procedure_name => p_api_name,
19 p_error_text => sqlerrm(p_err_code));
20 END IF;
21 END add_message;
22
23
24 PROCEDURE add_unique_constraint_message(
25 p_pkg_name VARCHAR2,
26 p_api_name VARCHAR2) IS
27 BEGIN
28 add_message(p_pkg_name, p_api_name, -1);
29 END add_unique_constraint_message;
30
31
32 PROCEDURE add_no_data_found_message(
33 p_pkg_name VARCHAR2,
34 p_api_name VARCHAR2) IS
35 BEGIN
36 add_message(p_pkg_name, p_api_name, 100);
37 END add_no_data_found_message;
38
39
40
41 /*
42 * Use this routine to get publication history bo
43 */
44 PROCEDURE get_pos_pub_history_bo_tbl
45 (
46 p_api_version IN NUMBER DEFAULT NULL,
47 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
48 p_event_id IN NUMBER,
49 p_party_id IN NUMBER,
50 p_orig_system IN VARCHAR2,
51 p_orig_system_reference IN VARCHAR2,
52 x_pos_pub_history_bo_tbl OUT NOCOPY pos_pub_history_bo_tbl,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2
56 ) IS
57
58 l_pos_pub_history_bo_tbl pos_pub_history_bo_tbl := pos_pub_history_bo_tbl();
59 l_party_id NUMBER;
60 l_event_id NUMBER;
61
62 l_api_name CONSTANT VARCHAR2(30) := 'get_published_suppliers';
63 l_api_version CONSTANT NUMBER := 1.0;
64
65 BEGIN
66 x_return_status := fnd_api.g_ret_sts_success;
67 x_msg_data := '';
68
69 -- Standard call to check for call compatibility.
70 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
71 l_api_name, g_pkg_name) THEN
72 RAISE fnd_api.g_exc_unexpected_error;
73 END IF;
74
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 IF fnd_api.to_boolean(nvl(p_init_msg_list, fnd_api.g_true)) THEN
77 fnd_msg_pub.initialize;
78 END IF;
79
80 IF (p_party_id IS NULL OR p_party_id = 0) THEN
81 IF (p_orig_system IS NOT NULL AND p_orig_system_reference IS NOT NULL) THEN
82 l_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
83 p_orig_system_reference);
84 END IF;
85 ELSE
86 l_party_id := p_party_id;
87 END IF;
88
89 IF (p_event_id = 0) THEN
90 l_event_id := NULL;
91 ELSE
92 l_event_id := p_event_id;
93 END IF;
94
95 IF (l_party_id = 0) THEN
96 l_party_id := NULL;
97 END IF;
98
99 IF (l_event_id IS NULL AND l_party_id IS NULL) THEN
100 RETURN;
101 END IF;
102
103 SELECT pos_pub_history_bo(ph.publication_event_id,
104 ph.party_id,
105 ph.publication_date,
106 ph.published_by,
107 ph.publish_detail,
108 ph.xmlcontent,
109 ph.created_by,
110 ph.creation_date,
111 ph.last_updated_by,
112 ph.last_update_date,
113 ph.last_update_login,
114 ph.request_id)
115 BULK COLLECT
116 INTO l_pos_pub_history_bo_tbl
117 FROM pos_supp_pub_history ph
118 WHERE ph.publication_event_id = nvl(l_event_id, publication_event_id)
119 AND ph.party_id = nvl(l_party_id, party_id);
120
121 --
122 -- BULK COLLECT does not raise no data found error automatically.
123 -- So we add a rowcount check.
124 --
125 IF sql%rowcount = 0 THEN
126 add_no_data_found_message(g_pkg_name, l_api_name);
127 RAISE fnd_api.g_exc_error;
128 END IF;
129
130 x_pos_pub_history_bo_tbl := l_pos_pub_history_bo_tbl;
131
132 EXCEPTION
133 WHEN fnd_api.g_exc_error THEN
134 x_return_status := fnd_api.g_ret_sts_error;
135 fnd_msg_pub.count_and_get(
136 p_count => x_msg_count,
137 p_data => x_msg_data);
138
139 WHEN fnd_api.g_exc_unexpected_error THEN
140 x_return_status := fnd_api.g_ret_sts_unexp_error;
141 fnd_msg_pub.count_and_get(
142 p_count => x_msg_count,
143 p_data => x_msg_data);
144
145 WHEN OTHERS THEN
146 x_return_status := fnd_api.g_ret_sts_unexp_error;
147 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
148 fnd_msg_pub.add_exc_msg(
149 p_pkg_name => g_pkg_name,
150 p_procedure_name => l_api_name,
151 p_error_text => sqlerrm);
152 END IF;
153 fnd_msg_pub.count_and_get(
154 p_count => x_msg_count,
155 p_data => x_msg_data);
156
157 END get_pos_pub_history_bo_tbl;
158
159
160 /*
161 Public wrapper of get_pos_pub_history_bo_tbl
162 This procedure name is easier to understand for public callers.
163 */
164 PROCEDURE get_published_suppliers
165 (
166 p_api_version IN NUMBER DEFAULT NULL,
167 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
168 p_event_id IN NUMBER,
169 p_party_id IN NUMBER,
170 p_orig_system IN VARCHAR2,
171 p_orig_system_reference IN VARCHAR2,
172 x_suppliers OUT NOCOPY pos_pub_history_bo_tbl,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2
176 ) IS
177 BEGIN
178 get_pos_pub_history_bo_tbl(
179 p_api_version => p_api_version,
180 p_init_msg_list => p_init_msg_list,
181 p_event_id => p_event_id,
182 p_party_id => p_party_id,
183 p_orig_system => p_orig_system,
184 p_orig_system_reference => p_orig_system_reference,
185 x_pos_pub_history_bo_tbl => x_suppliers,
186 x_return_status => x_return_status,
187 x_msg_count => x_msg_count,
188 x_msg_data => x_msg_data
189 );
190 END get_published_suppliers;
191
192
193 FUNCTION exists_event_party(p_event_id NUMBER, p_party_id NUMBER)
194 RETURN BOOLEAN IS
195 --
196 -- Private utility function to find if event and party already
197 -- exists in the publication history
198 --
199 CURSOR c IS
200 SELECT 1
201 FROM pos_supp_pub_history
202 WHERE publication_event_id = p_event_id AND
203 party_id = p_party_id;
204
205 dummy NUMBER;
206 result BOOLEAN;
207
208 BEGIN
209 OPEN c;
210 FETCH c INTO dummy;
211 result := c%FOUND;
212 CLOSE c;
213
214 RETURN result;
215 END exists_event_party;
216
217
218 FUNCTION exists_target_response(p_target VARCHAR2, p_response_id NUMBER)
219 RETURN BOOLEAN IS
220 --
221 -- Private utility function to find if event and party already
222 -- exists in the publication history
223 --
224 CURSOR c IS
225 SELECT 1
226 FROM pos_supp_pub_responses
227 WHERE target_system = p_target AND
228 response_process_id = p_response_id;
229
230 dummy NUMBER;
231 result BOOLEAN;
232
233 BEGIN
234 OPEN c;
235 FETCH c INTO dummy;
236 result := c%FOUND;
237 CLOSE c;
238
239 RETURN result;
240 END exists_target_response;
241
242
243 PROCEDURE create_response_private(
244 p_target_system IN VARCHAR2,
245 p_response_process_id IN NUMBER,
246 p_response_process_status IN VARCHAR2,
247 p_request_process_id IN NUMBER,
248 p_request_process_status IN VARCHAR2,
249 p_event_id IN NUMBER,
250 p_party_id IN NUMBER,
251 p_message IN VARCHAR2) IS
252 BEGIN
253 INSERT INTO pos_supp_pub_responses(
254 publication_event_id,
255 party_id,
256 target_system,
257 request_process_id,
258 request_process_status,
259 response_process_id,
260 response_process_status,
261 target_system_response_date,
262 error_message,
263 created_by,
264 creation_date,
265 last_updated_by,
266 last_update_date,
267 last_update_login)
268 VALUES (
269 p_event_id,
270 p_party_id,
271 p_target_system,
272 p_request_process_id,
273 p_request_process_status,
274 p_response_process_id,
275 p_response_process_status,
276 sysdate,
277 p_message,
278 fnd_global.user_id,
279 sysdate,
280 fnd_global.user_id,
281 sysdate,
282 fnd_global.login_id
283 );
284
285 END create_response_private;
286
287 /*
288 Public create publication response. See spec for param descriptions.
289 */
290 PROCEDURE create_publication_response(
291 p_api_version IN NUMBER DEFAULT NULL,
292 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
293 p_commit IN VARCHAR2 DEFAULT NULL,
294 p_target_system IN VARCHAR2,
295 p_response_process_id IN NUMBER,
296 p_response_process_status IN VARCHAR2,
297 p_request_process_id IN NUMBER,
298 p_request_process_status IN VARCHAR2,
299 p_event_id IN NUMBER,
300 p_party_id IN NUMBER,
301 p_message IN VARCHAR2,
302 x_return_status OUT NOCOPY VARCHAR2,
303 x_msg_count OUT NOCOPY NUMBER,
304 x_msg_data OUT NOCOPY VARCHAR2
305 ) IS
306
307 l_api_name CONSTANT VARCHAR2(30) := 'create_publication_response';
308 l_api_version CONSTANT NUMBER := 1.0;
309
310 BEGIN
311 x_return_status := fnd_api.g_ret_sts_success;
312 x_msg_data := '';
313
314 -- Standard call to check for call compatibility.
315 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
316 l_api_name, g_pkg_name) THEN
317 RAISE fnd_api.g_exc_unexpected_error;
318 END IF;
319
320 -- Initialize message list if p_init_msg_list is set to TRUE.
321 IF fnd_api.to_boolean(nvl(p_init_msg_list, fnd_api.g_true)) THEN
322 fnd_msg_pub.initialize;
323 END IF;
324
325 -- Verify required fields
326 IF p_target_system is NULL OR
327 p_response_process_id IS NULL OR
328 p_event_id IS NULL OR
329 p_party_id IS NULL THEN
330 add_no_data_found_message(g_pkg_name, l_api_name);
331 RAISE fnd_api.g_exc_error;
332 END IF;
333
334 -- Event and Party IDs must exist in parent table
335 IF NOT exists_event_party(p_event_id, p_party_id) THEN
336 add_no_data_found_message(g_pkg_name, l_api_name);
337 RAISE fnd_api.g_exc_error;
338 END IF;
339
340 -- Target System + Response ID must be unique
341 IF exists_target_response(p_target_system, p_response_process_id) THEN
342 add_unique_constraint_message(g_pkg_name, l_api_name);
343 RAISE fnd_api.g_exc_error;
344 END IF;
345
346 create_response_private(
347 p_target_system,
348 p_response_process_id,
349 p_response_process_status,
350 p_request_process_id,
351 p_request_process_status,
352 p_event_id,
353 p_party_id,
354 p_message
355 );
356
357 IF fnd_api.to_boolean(nvl(p_commit, fnd_api.g_false)) THEN
358 COMMIT;
359 END IF;
360
361 EXCEPTION
362 WHEN fnd_api.g_exc_error THEN
363 x_return_status := fnd_api.g_ret_sts_error;
364 fnd_msg_pub.count_and_get(
365 p_count => x_msg_count,
366 p_data => x_msg_data);
367
368 WHEN fnd_api.g_exc_unexpected_error THEN
369 x_return_status := fnd_api.g_ret_sts_unexp_error;
370 fnd_msg_pub.count_and_get(
371 p_count => x_msg_count,
372 p_data => x_msg_data);
373
374 WHEN OTHERS THEN
375 x_return_status := fnd_api.g_ret_sts_unexp_error;
376 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
377 fnd_msg_pub.add_exc_msg(
378 p_pkg_name => g_pkg_name,
379 p_procedure_name => l_api_name,
380 p_error_text => sqlerrm);
381 END IF;
382 fnd_msg_pub.count_and_get(
383 p_count => x_msg_count,
384 p_data => x_msg_data);
385
386 END create_publication_response;
387
388
389 END pos_pub_history_bo_pkg;