DBA Data[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;