[Home] [Help]
PACKAGE BODY: APPS.POS_PRODUCT_SERVICE_BO_PKG
Source
1 PACKAGE BODY pos_product_service_bo_pkg AS
2 /* $Header: POSSPPRSB.pls 120.1 2010/11/25 12:15:18 puppulur noship $ */
3 PROCEDURE validate_vendor_prods_services
4 (
5 p_vendor_prodsrv_rec IN pos_product_service_bo,
6 p_party_id IN hz_parties.party_id%TYPE,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 x_prod_valid OUT NOCOPY VARCHAR2,
11 x_segment_code OUT NOCOPY VARCHAR2
12 ) IS
13 TYPE cursor_ref_type IS REF CURSOR;
14 l_product_segment_definition VARCHAR2(2000);
15 l_product_segment_count NUMBER;
16 l_default_po_category_set_id NUMBER;
17 l_delimiter VARCHAR2(10);
18 l_status VARCHAR(2000);
19 l_error_message VARCHAR(4000);
20
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(2000);
23 l_api_name CONSTANT VARCHAR2(50) := 'VALIDATE_VENDOR_PRODS_SERVICES';
24 l_request_id NUMBER := fnd_global.conc_request_id;
25
26 l_pscur cursor_ref_type;
27 l_sql VARCHAR2(4000) := NULL;
28 l_seg_def fnd_profile_option_values.profile_option_value%TYPE;
29
30 l_start_pos NUMBER := 0;
31 l_index NUMBER := 0;
32 l_segment_code VARCHAR2(4000) := NULL;
33 l_segment_count NUMBER;
34 l_segment_concat VARCHAR2(4000) := NULL;
35 l_concatenated_segments VARCHAR2(4000) := NULL;
36 l_category_id VARCHAR2(10);
37 BEGIN
38 -- Initialize API return status to success
39 x_return_status := fnd_api.g_ret_sts_success;
40 x_prod_valid := 'Y';
41
42 -- Below API would give the delimiter and the segment definition to be inserted
43 pos_product_service_utl_pkg.initialize(x_status => l_status,
44 x_error_message => l_error_message);
45
46 pos_product_service_utl_pkg.get_product_meta_data(x_product_segment_definition => l_product_segment_definition,
47 x_product_segment_count => l_product_segment_count,
48 x_default_po_category_set_id => l_default_po_category_set_id,
49 x_delimiter => l_delimiter);
50
51 -- Check if the number of segments into which the data has been
52 -- inserted is equal to the product segment count
53 IF (p_vendor_prodsrv_rec.segment_definition <>
54 l_product_segment_definition) THEN
55 x_prod_valid := 'N';
56 x_return_status := fnd_api.g_ret_sts_error;
57 x_msg_data := 'AP_INVALID_SEGMENT_DEF';
58 RETURN;
59 END IF;
60
61 l_seg_def := p_vendor_prodsrv_rec.segment_definition;
62
63 WHILE (length(l_seg_def)) > l_start_pos LOOP
64 l_index := instr(l_seg_def, l_delimiter, l_start_pos + 1);
65 IF (l_index = 0) THEN
66 EXIT;
67 END IF;
68 l_segment_code := l_segment_code || 'segment' ||
69 substr(l_seg_def,
70 l_start_pos + 1,
71 (l_index - l_start_pos - 1)) || '||' || '''' ||
72 l_delimiter || '''' || '||';
73 l_start_pos := l_index;
74 END LOOP;
75
76 l_segment_code := l_segment_code || 'segment' ||
77 substr(l_seg_def, l_start_pos + 1);
78
79 SELECT nvl2(p_vendor_prodsrv_rec.segment1,
80 p_vendor_prodsrv_rec.segment1 || '.',
81 p_vendor_prodsrv_rec.segment1) ||
82 nvl2(p_vendor_prodsrv_rec.segment2,
83 p_vendor_prodsrv_rec.segment2 || '.',
84 p_vendor_prodsrv_rec.segment2) ||
85 nvl2(p_vendor_prodsrv_rec.segment3,
86 p_vendor_prodsrv_rec.segment3 || '.',
87 p_vendor_prodsrv_rec.segment3) ||
88 nvl2(p_vendor_prodsrv_rec.segment4,
89 p_vendor_prodsrv_rec.segment4 || '.',
90 p_vendor_prodsrv_rec.segment4) ||
91 nvl2(p_vendor_prodsrv_rec.segment5,
92 p_vendor_prodsrv_rec.segment5 || '.',
93 p_vendor_prodsrv_rec.segment5) ||
94 nvl2(p_vendor_prodsrv_rec.segment6,
95 p_vendor_prodsrv_rec.segment6 || '.',
96 p_vendor_prodsrv_rec.segment6) ||
97 nvl2(p_vendor_prodsrv_rec.segment7,
98 p_vendor_prodsrv_rec.segment7 || '.',
99 p_vendor_prodsrv_rec.segment7) ||
100 nvl2(p_vendor_prodsrv_rec.segment8,
101 p_vendor_prodsrv_rec.segment8 || '.',
102 p_vendor_prodsrv_rec.segment8) ||
103 nvl2(p_vendor_prodsrv_rec.segment9,
104 p_vendor_prodsrv_rec.segment9 || '.',
105 p_vendor_prodsrv_rec.segment9) ||
106 nvl2(p_vendor_prodsrv_rec.segment10,
107 p_vendor_prodsrv_rec.segment10 || '.',
108 p_vendor_prodsrv_rec.segment10) ||
109 nvl2(p_vendor_prodsrv_rec.segment11,
110 p_vendor_prodsrv_rec.segment11 || '.',
111 p_vendor_prodsrv_rec.segment11) ||
112 nvl2(p_vendor_prodsrv_rec.segment12,
113 p_vendor_prodsrv_rec.segment12 || '.',
114 p_vendor_prodsrv_rec.segment12) ||
115 nvl2(p_vendor_prodsrv_rec.segment13,
116 p_vendor_prodsrv_rec.segment13 || '.',
117 p_vendor_prodsrv_rec.segment13) ||
118 nvl2(p_vendor_prodsrv_rec.segment14,
119 p_vendor_prodsrv_rec.segment14 || '.',
120 p_vendor_prodsrv_rec.segment14) ||
121 nvl2(p_vendor_prodsrv_rec.segment15,
122 p_vendor_prodsrv_rec.segment15 || '.',
123 p_vendor_prodsrv_rec.segment15) ||
124 nvl2(p_vendor_prodsrv_rec.segment16,
125 p_vendor_prodsrv_rec.segment16 || '.',
126 p_vendor_prodsrv_rec.segment16) ||
127 nvl2(p_vendor_prodsrv_rec.segment17,
128 p_vendor_prodsrv_rec.segment17 || '.',
129 p_vendor_prodsrv_rec.segment17) ||
130 nvl2(p_vendor_prodsrv_rec.segment18,
131 p_vendor_prodsrv_rec.segment18 || '.',
132 p_vendor_prodsrv_rec.segment18) ||
133 nvl2(p_vendor_prodsrv_rec.segment19,
134 p_vendor_prodsrv_rec.segment19 || '.',
135 p_vendor_prodsrv_rec.segment19) ||
136 nvl2(p_vendor_prodsrv_rec.segment20,
137 p_vendor_prodsrv_rec.segment20 || '.',
138 p_vendor_prodsrv_rec.segment20)
139 INTO l_segment_concat
140 FROM dual;
141
142 l_segment_count := (length(l_segment_concat) -
143 length(REPLACE(l_segment_concat, '.', '')));
144
145 IF (l_segment_count <> l_product_segment_count) THEN
146 x_prod_valid := 'N';
147 x_return_status := fnd_api.g_ret_sts_error;
148 x_msg_data := 'AP_INVALID_SEGMENT_COUNT';
149 RETURN;
150 END IF;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 x_prod_valid := 'N';
155 x_return_status := fnd_api.g_ret_sts_unexp_error;
156
157 fnd_msg_pub.count_and_get(p_count => x_msg_count,
158 p_data => x_msg_data);
159 END validate_vendor_prods_services;
160
161 /* $Header: POSSPPRSB.pls 120.1 2010/11/25 12:15:18 puppulur noship $ */
162 /*#
163 * Use this routine to get product service bo
164 * @param p_api_version The version of API
165 * @param p_init_msg_list The Initialization message list
166 * @param p_party_id The Party id
167 * @param p_orig_system The Orig System
168 * @param p_orig_system_reference The Orig System Reference
169 * @param x_pos_product_service_bo_tbl The product service bo list
170 * @param x_return_status The return status
171 * @param x_msg_count The message count
172 * @param x_msg_data The message data
173 * @rep:scope public
174 * @rep:lifecycle active
175 * @rep:displayname Get Supplier Product Service
176 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
177 */
178 PROCEDURE get_pos_product_service_bo_tbl
179 (
180 p_api_version IN NUMBER DEFAULT NULL,
181 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
182 p_party_id IN NUMBER,
183 p_orig_system IN VARCHAR2,
184 p_orig_system_reference IN VARCHAR2,
185 x_pos_product_service_bo_tbl OUT NOCOPY pos_product_service_bo_tbl,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2
189 ) IS
190
191 l_pos_product_service_bo_tbl pos_product_service_bo_tbl := pos_product_service_bo_tbl();
192 l_party_id NUMBER;
193
194 BEGIN
195
196 IF p_party_id IS NULL OR p_party_id = 0 THEN
197
198 l_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
199 p_orig_system_reference);
200 ELSE
201 l_party_id := p_party_id;
202 END IF;
203 SELECT pos_product_service_bo(ps.classification_id,
204 ps.vendor_id,
205 ps.segment1,
206 ps.segment2,
207 ps.segment3,
208 ps.segment4,
209 ps.segment5,
210 ps.segment6,
211 ps.segment7,
212 ps.segment8,
213 ps.segment9,
214 ps.segment10,
215 ps.segment11,
216 ps.segment12,
217 ps.segment13,
218 ps.segment14,
219 ps.segment15,
220 ps.segment16,
221 ps.segment17,
222 ps.segment18,
223 ps.segment19,
224 ps.segment20,
225 ps.status,
226 ps.segment_definition,
227 ps.created_by,
228 ps.creation_date,
229 ps.last_updated_by,
230 ps.last_update_date,
231 ps.last_update_login) BULK COLLECT
232 INTO l_pos_product_service_bo_tbl
233 FROM pos_sup_products_services ps,
234 ap_suppliers ap
235 WHERE ap.party_id = l_party_id
236 AND ps.vendor_id = ap.vendor_id;
237
238 x_pos_product_service_bo_tbl := l_pos_product_service_bo_tbl;
239 x_return_status := fnd_api.g_ret_sts_success;
240 x_msg_data := 'SUCCESS';
241 EXCEPTION
242 WHEN fnd_api.g_exc_error THEN
243
244 x_return_status := fnd_api.g_ret_sts_error;
245 x_msg_count := 1;
246 x_msg_data := SQLCODE || SQLERRM;
247 WHEN fnd_api.g_exc_unexpected_error THEN
248
249 x_return_status := fnd_api.g_ret_sts_unexp_error;
250 x_msg_count := 1;
251 x_msg_data := SQLCODE || SQLERRM;
252 WHEN OTHERS THEN
253
254 x_return_status := fnd_api.g_ret_sts_unexp_error;
255
256 x_msg_count := 1;
257 x_msg_data := SQLCODE || SQLERRM;
258 END get_pos_product_service_bo_tbl;
259
260 /*#
261 * Use this routine to create approved product service
262 * @param p_api_version The version of API
263 * @param p_init_msg_list The Initialization message list
264 * @param p_vendor_prodsrv_rec The product service bo
265 * @param p_party_id The Party Id
266 * @param p_orig_system The Orig System
267 * @param p_orig_system_reference The Orig System Reference
268 * @param x_return_status The return status
269 * @param x_msg_count The message count
270 * @param x_msg_data The message data
271 * @rep:scope public
272 * @rep:lifecycle active
273 * @rep:displayname Create Supplier Product Service
274 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
275 */
276 PROCEDURE create_pos_product_service
277 (
278 p_api_version IN NUMBER DEFAULT NULL,
279 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
280 p_vendor_prodsrv_rec IN pos_product_service_bo_tbl,
281 p_request_status IN VARCHAR2,
282 p_party_id IN NUMBER,
283 p_orig_system IN VARCHAR2,
284 p_orig_system_reference IN VARCHAR2,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER,
287 x_msg_data OUT NOCOPY VARCHAR2
288 ) IS
289 l_return_status VARCHAR2(100);
290 l_msg_count NUMBER;
291 l_msg_data VARCHAR2(100);
292 l_prod_valid VARCHAR2(1);
293
294 l_mapping_id NUMBER;
295 l_segment_code VARCHAR2(4000) := NULL;
296 l_req_id NUMBER;
297 l_status VARCHAR(2000);
298 l_error_message VARCHAR(4000);
299 l_party_id NUMBER;
300 l_vendor_id NUMBER;
301 BEGIN
302 IF p_party_id IS NULL OR p_party_id = 0 THEN
303
304 l_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
305 p_orig_system_reference);
306 ELSE
307 l_party_id := p_party_id;
308 END IF;
309
310 FOR i IN p_vendor_prodsrv_rec.first .. p_vendor_prodsrv_rec.last LOOP
311 -- Call Validate_Vendor_Prods_Services to validate the Products and Services data
312 validate_vendor_prods_services(p_vendor_prodsrv_rec => p_vendor_prodsrv_rec(i),
313 p_party_id => l_party_id,
314 x_return_status => l_return_status,
315 x_msg_count => l_msg_count,
316 x_msg_data => l_msg_data,
317 x_prod_valid => l_prod_valid,
318 x_segment_code => l_segment_code);
319
320 IF (l_prod_valid = 'Y') THEN
321 -- Insert the data into the pos_product_service_requests table using the follwing API
325 .segment1,
322 pos_product_service_utl_pkg.add_new_ps_req(p_vendor_id => p_vendor_prodsrv_rec(i)
323 .vendor_id,
324 p_segment1 => p_vendor_prodsrv_rec(i)
326 p_segment2 => p_vendor_prodsrv_rec(i)
327 .segment2,
328 p_segment3 => p_vendor_prodsrv_rec(i)
329 .segment3,
330 p_segment4 => p_vendor_prodsrv_rec(i)
331 .segment4,
332 p_segment5 => p_vendor_prodsrv_rec(i)
333 .segment5,
334 p_segment6 => p_vendor_prodsrv_rec(i)
335 .segment6,
336 p_segment7 => p_vendor_prodsrv_rec(i)
337 .segment7,
338 p_segment8 => p_vendor_prodsrv_rec(i)
339 .segment8,
340 p_segment9 => p_vendor_prodsrv_rec(i)
341 .segment9,
342 p_segment10 => p_vendor_prodsrv_rec(i)
343 .segment10,
344 p_segment11 => p_vendor_prodsrv_rec(i)
345 .segment11,
346 p_segment12 => p_vendor_prodsrv_rec(i)
347 .segment12,
348 p_segment13 => p_vendor_prodsrv_rec(i)
349 .segment13,
350 p_segment14 => p_vendor_prodsrv_rec(i)
351 .segment14,
352 p_segment15 => p_vendor_prodsrv_rec(i)
353 .segment15,
354 p_segment16 => p_vendor_prodsrv_rec(i)
355 .segment16,
356 p_segment17 => p_vendor_prodsrv_rec(i)
357 .segment17,
358 p_segment18 => p_vendor_prodsrv_rec(i)
359 .segment18,
360 p_segment19 => p_vendor_prodsrv_rec(i)
361 .segment19,
362 p_segment20 => p_vendor_prodsrv_rec(i)
363 .segment20,
364 p_segment_definition => p_vendor_prodsrv_rec(i)
365 .segment_definition,
366 x_return_status => l_return_status,
367 x_msg_count => l_msg_count,
368 x_msg_data => l_msg_data);
369
370 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
371 x_return_status := l_return_status;
372 x_msg_count := l_msg_count;
373 x_msg_data := l_msg_data;
374 RETURN;
375 ELSE
376 -- If the request_status is "APPROVED" then do the following
377 IF (p_request_status = 'APPROVED') THEN
378
379 -- Get the mapping_id using the following SQLL
380 SELECT mapping_id
381 INTO l_mapping_id
382 FROM pos_supplier_mappings
383 WHERE vendor_id = p_vendor_prodsrv_rec(i).vendor_id
384 AND party_id = l_party_id;
385
386 pos_product_service_utl_pkg.initialize(x_status => l_status,
387 x_error_message => l_error_message);
388
389 -- Using the mapping_id make a call to the following API
390 l_req_id := pos_product_service_utl_pkg.get_requestid(x_segment_code => l_segment_code,
391 x_mapp_id => l_mapping_id);
392
393 -- Using the request_id make a call to the following Api to approve the data and insert it into the
394 -- pos_sup_products_services table
395 pos_profile_change_request_pkg.approve_ps_req(p_request_id => l_req_id,
396 x_return_status => l_return_status,
397 x_msg_count => l_msg_count,
398 x_msg_data => l_msg_data);
399
400 x_return_status := l_return_status;
401 x_msg_count := l_msg_count;
402 x_msg_data := l_msg_data;
403
404 END IF;
405 END IF;
406 ELSE
407 x_return_status := l_return_status;
408 x_msg_count := l_msg_count;
409 x_msg_data := l_msg_data;
410 RETURN;
411 END IF;
412 END LOOP;
413
414 EXCEPTION
415 WHEN fnd_api.g_exc_error THEN
416 x_return_status := fnd_api.g_ret_sts_error;
417 fnd_msg_pub.count_and_get(p_count => x_msg_count,
418 p_data => x_msg_data);
419 WHEN fnd_api.g_exc_unexpected_error THEN
420 x_return_status := fnd_api.g_ret_sts_unexp_error;
421 fnd_msg_pub.count_and_get(p_count => x_msg_count,
422 p_data => x_msg_data);
423 WHEN OTHERS THEN
424 x_return_status := fnd_api.g_ret_sts_unexp_error;
425
426 fnd_msg_pub.count_and_get(p_count => x_msg_count,
427 p_data => x_msg_data);
428 END create_pos_product_service;
429 /* /*#
430 * Use this routine to update status field of product service table
431 * @param p_api_version The version of API
432 * @param p_init_msg_list The Initialization message list
433 * @param p_req_id The classification id
434 * @param p_status The status to be updated with
435 * @param x_return_status The return status
436 * @param x_msg_count The message count
437 * @param x_msg_data The message data
438 * @rep:scope public
439 * @rep:lifecycle active
440 * @rep:displayname Create Supplier Contact
441 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
442 */
443 /* PROCEDURE update_pos_prod_service_status(p_req_id IN NUMBER,
444 p_status IN VARCHAR2,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_count OUT NOCOPY NUMBER,
447 x_msg_data OUT NOCOPY VARCHAR2) IS
448 BEGIN
449
450 UPDATE pos_sup_products_services
451 SET status = p_status,
452 last_updated_by = fnd_global.user_id,
453 last_update_date = SYSDATE,
454 last_update_login = fnd_global.login_id
455 WHERE classification_id = p_req_id;
456
457 x_return_status := fnd_api.g_ret_sts_success;
458 EXCEPTION
459 WHEN fnd_api.g_exc_error THEN
460 x_return_status := fnd_api.g_ret_sts_error;
461 fnd_msg_pub.count_and_get(p_count => x_msg_count,
462 p_data => x_msg_data);
463 WHEN fnd_api.g_exc_unexpected_error THEN
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465 fnd_msg_pub.count_and_get(p_count => x_msg_count,
466 p_data => x_msg_data);
467 WHEN OTHERS THEN
468 x_return_status := fnd_api.g_ret_sts_unexp_error;
469
470 fnd_msg_pub.count_and_get(p_count => x_msg_count,
471 p_data => x_msg_data);
472 END update_pos_prod_service_status;
473 */
474 END pos_product_service_bo_pkg;