[Home] [Help]
1 PACKAGE BODY POS_MERGE_SUPPLIER_PKG AS
2 /* $Header: POSMRGSUPB.pls 120.1.12020000.3 2012/07/23 20:30:32 yaoli ship $ */
3
4 TYPE bank_dtls_rec_type IS RECORD(
5 instrument_type iby_pmt_instr_uses_all.instrument_type%TYPE,
6 instrument_id iby_pmt_instr_uses_all.instrument_id%TYPE,
7 payment_function iby_pmt_instr_uses_all.payment_function%TYPE);
8
9 TYPE bank_dtls_tab_type IS TABLE OF bank_dtls_rec_type INDEX BY BINARY_INTEGER;
10
11 TYPE uda_rec_tbl_type IS TABLE OF pos_supp_prof_ext_b%ROWTYPE INDEX BY BINARY_INTEGER;
12
13 TYPE attributes_rec IS RECORD(
14 attr_name ego_attrs_v.attr_name%TYPE,
15 database_column ego_attrs_v.database_column%TYPE);
16
17 TYPE attributes_coll_tab IS TABLE OF attributes_rec INDEX BY BINARY_INTEGER;
18 attributes_coll attributes_coll_tab;
19
20 PROCEDURE create_bus_attr
21 (
22 p_buss_class_rec IN pos_bus_class_attr%ROWTYPE,
23 p_party_id IN hz_parties.party_id%TYPE,
24 p_classification_id OUT NOCOPY NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 ) IS
29 l_classification_id NUMBER;
30 l_status VARCHAR2(100);
31 l_exception_msg VARCHAR2(100);
32 BEGIN
33 x_return_status := fnd_api.g_ret_sts_success;
34
35 fnd_file.put_line(fnd_file.log,
36 'Inside POS_MERGE_SUPPLIER_PKG.create_bus_attr ');
37 BEGIN
38 pos_supp_classification_pkg.add_bus_class_attr(p_party_id,
39 p_buss_class_rec.vendor_id,
40 p_buss_class_rec.lookup_code,
41 p_buss_class_rec.expiration_date,
42 p_buss_class_rec.certificate_number,
43 p_buss_class_rec.certifying_agency,
44 p_buss_class_rec.ext_attr_1,
45 p_buss_class_rec.class_status,
46 '',
47 p_classification_id,
48 l_status,
49 l_exception_msg);
50
51 fnd_file.put_line(fnd_file.log,
52 'l_exception_msg from pos_supp_classification_pkg.add_bus_class_attr : ' ||
53 l_exception_msg || ' for party id : ' || p_party_id);
54
55 END;
56
57 BEGIN
58 pos_supp_classification_pkg.synchronize_class_tca_to_po(p_party_id,
59 p_buss_class_rec.vendor_id);
60 END;
61 EXCEPTION
62 WHEN OTHERS THEN
63 x_return_status := fnd_api.g_ret_sts_unexp_error;
64
65 fnd_msg_pub.count_and_get(p_count => x_msg_count,
66 p_data => x_msg_data);
67 END create_bus_attr;
68
69 PROCEDURE get_bus_attr_rec
70 (
71 p_party_id IN hz_parties.party_id%TYPE,
72 p_classification_id IN pos_bus_class_attr.classification_id%TYPE,
73 x_buss_class_rec OUT NOCOPY pos_bus_class_attr%ROWTYPE,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2
77 ) IS
78
79 BEGIN
80 x_return_status := fnd_api.g_ret_sts_success;
81 fnd_file.put_line(fnd_file.log,
82 'Inside POS_MERGE_SUPPLIER_PKG.get_bus_attr_rec ' ||
83 p_party_id);
84
85 SELECT --classification_id,
86 --vendor_id,
87 lookup_type,
88 lookup_code,
89 start_date_active,
90 end_date_active,
91 status,
92 ext_attr_1,
93 expiration_date,
94 certificate_number,
95 certifying_agency,
96 class_status,
97 attribute1,
98 attribute2,
99 attribute3,
100 attribute4,
101 attribute5
102 INTO --x_buss_class_rec.classification_id,
103 --x_buss_class_rec.vendor_id,
104 x_buss_class_rec.lookup_type,
105 x_buss_class_rec.lookup_code,
106 x_buss_class_rec.start_date_active,
107 x_buss_class_rec.end_date_active,
108 x_buss_class_rec.status,
109 x_buss_class_rec.ext_attr_1,
110 x_buss_class_rec.expiration_date,
111 x_buss_class_rec.certificate_number,
112 x_buss_class_rec.certifying_agency,
113 x_buss_class_rec.class_status,
114 x_buss_class_rec.attribute1,
115 x_buss_class_rec.attribute2,
116 x_buss_class_rec.attribute3,
117 x_buss_class_rec.attribute4,
118 x_buss_class_rec.attribute5
119 FROM pos_bus_class_attr
120 WHERE party_id = p_party_id
121 AND classification_id = p_classification_id;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 x_return_status := fnd_api.g_ret_sts_unexp_error;
126
127 fnd_msg_pub.count_and_get(p_count => x_msg_count,
128 p_data => x_msg_data);
129 END get_bus_attr_rec;
130
131 PROCEDURE buss_class_merge
132 (
133 p_entity_name IN VARCHAR2,
134 p_from_id IN NUMBER,
135 x_to_id IN OUT NOCOPY NUMBER,
136 p_from_fk_id IN NUMBER,
137 p_to_fk_id IN NUMBER,
138 p_parent_entity_name IN VARCHAR2,
139 p_batch_id IN NUMBER,
140 p_batch_party_id IN NUMBER,
141 x_return_status OUT NOCOPY VARCHAR2
142 ) IS
143 l_msg_count NUMBER;
144 l_msg_data VARCHAR2(100);
145
146 l_count NUMBER;
147 buss_class_rec pos_bus_class_attr%ROWTYPE;
148 l_to_vendor_id NUMBER;
149
150 BEGIN
151 x_return_status := fnd_api.g_ret_sts_success;
152
153 fnd_file.put_line(fnd_file.log,
154 'Inside POS_MERGE_SUPPLIER_PKG.Buss_Class_Merge p_to_fk_id: ' ||
155 p_to_fk_id || ' p_from_fk_id: ' || p_from_fk_id ||
156 ' p_from_id: ' || p_from_id);
157
158 /* Set the status to MERGED */
159 UPDATE pos_bus_class_attr
160 SET status = 'M',
161 last_update_date = hz_utility_pub.last_update_date,
162 last_updated_by = hz_utility_pub.user_id,
163 last_update_login = hz_utility_pub.last_update_login
164 WHERE party_id = p_from_fk_id
165 AND classification_id = p_from_id;
166
167 fnd_file.put_line(fnd_file.log, 'Rowcount: ' || SQL%ROWCOUNT);
168
169 SELECT vendor_id
170 INTO l_to_vendor_id
171 FROM ap_suppliers
172 WHERE party_id = p_to_fk_id;
173
174 fnd_file.put_line(fnd_file.log,
175 'Inside POS_MERGE_SUPPLIER_PKG.Buss_Class_Merge l_to_vendor_id: ' ||
176 l_to_vendor_id);
177
178 DELETE FROM pos_supplier_mappings a
179 WHERE a.party_id = p_to_fk_id
180 AND a.vendor_id <> l_to_vendor_id
181 AND EXISTS (SELECT 1
182 FROM pos_supplier_mappings b
183 WHERE b.party_id = p_to_fk_id
184 AND vendor_id = l_to_vendor_id);
185
186 UPDATE pos_supplier_mappings
187 SET vendor_id = l_to_vendor_id,
188 last_updated_by = fnd_global.user_id,
189 last_update_date = SYSDATE,
190 last_update_login = fnd_global.login_id
191 WHERE mapping_id IN (SELECT mapping_id
192 FROM pos_supplier_mappings
193 WHERE party_id = p_to_fk_id);
194
195 fnd_file.put_line(fnd_file.log, 'Rowcount1: ' || SQL%ROWCOUNT);
196
197 /* Check for the duplicate business classification details */
198 SELECT COUNT(1)
199 INTO l_count
200 FROM pos_bus_class_attr attr_from,
201 pos_bus_class_attr attr_to
202 WHERE attr_from.lookup_code = attr_to.lookup_code
203 AND attr_from.lookup_type = attr_to.lookup_type
204 /*AND nvl(attr_from.ext_attr_1, ' ') = nvl(attr_to.ext_attr_1, ' ')*/
205 AND attr_from.party_id = p_from_fk_id
206 AND attr_to.party_id = p_to_fk_id
207 AND attr_from.classification_id = p_from_id;
208 /*AND attr_from.classification_id = p_from_fk_id
209 AND attr_to.classification_id = p_to_fk_id;*/
210
211 fnd_file.put_line(fnd_file.log,
212 'Inside POS_MERGE_SUPPLIER_PKG.Buss_Class_Merge l_count: ' ||
213 l_count);
214
215 IF (l_count = 0) THEN
216 BEGIN
217
218 /* Get the details for the from party id */
219 get_bus_attr_rec(p_party_id => p_from_fk_id,
220 p_classification_id => p_from_id,
221 x_buss_class_rec => buss_class_rec,
222 x_return_status => x_return_status,
223 x_msg_count => l_msg_count,
224 x_msg_data => l_msg_data);
225
226 IF x_return_status <> fnd_api.g_ret_sts_success THEN
227 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
228 fnd_message.set_token('ERROR',
229 'Cannot get classification ID : ' ||
230 p_from_id);
231 fnd_msg_pub.add;
232
233 fnd_file.put_line(fnd_file.log,
234 'No. of Messages: ' || l_msg_count ||
235 ', Message: ' || l_msg_data ||
236 ' From get_bus_attr_rec API.');
237 x_return_status := fnd_api.g_ret_sts_error;
238
239 RETURN;
240 END IF;
241
242 --buss_class_rec.party_id := p_to_fk_id;
243 buss_class_rec.vendor_id := l_to_vendor_id;
244
245 /* Create the details for the to party id */
246 create_bus_attr(p_buss_class_rec => buss_class_rec,
247 p_party_id => p_to_fk_id,
248 p_classification_id => x_to_id,
249 x_return_status => x_return_status,
250 x_msg_count => l_msg_count,
251 x_msg_data => l_msg_data);
252
253 IF x_return_status <> fnd_api.g_ret_sts_success THEN
254 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
255 fnd_message.set_token('ERROR',
256 'Cannot copy classification ID : ' ||
257 p_from_id);
258 fnd_msg_pub.add;
259
260 fnd_file.put_line(fnd_file.log,
261 'No. of Messages: ' || l_msg_count ||
262 ', Message: ' || l_msg_data ||
263 ' From create_bus_attr API.');
264 x_return_status := fnd_api.g_ret_sts_error;
265 RETURN;
266 END IF;
267 END;
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 fnd_file.put_line(fnd_file.log, 'In others : ' || SQLERRM);
273 x_return_status := fnd_api.g_ret_sts_unexp_error;
274 END buss_class_merge;
275
276 PROCEDURE create_prod_serv
277 (
278 p_vendor_prodsrv_rec IN pos_sup_products_services%ROWTYPE,
279 p_party_id IN hz_parties.party_id%TYPE,
280 p_vendor_id IN ap_suppliers.vendor_id%TYPE,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2
284 ) IS
285 l_return_status VARCHAR2(2000);
286 l_msg_count NUMBER;
287 l_msg_data VARCHAR2(2000);
288
289 l_status VARCHAR(2000);
290 l_error_message VARCHAR(4000);
291 l_segment_concat VARCHAR2(4000) := NULL;
292
293 l_mapping_id NUMBER;
294 l_req_id NUMBER := 0;
295
296 BEGIN
297 x_return_status := fnd_api.g_ret_sts_success;
298
299 -- Insert the data into the pos_product_service_requests table using the follwing API
300 pos_product_service_utl_pkg.add_new_ps_req(p_vendor_id => p_vendor_prodsrv_rec.vendor_id,
301 p_segment1 => p_vendor_prodsrv_rec.segment1,
302 p_segment2 => p_vendor_prodsrv_rec.segment2,
303 p_segment3 => p_vendor_prodsrv_rec.segment3,
304 p_segment4 => p_vendor_prodsrv_rec.segment4,
305 p_segment5 => p_vendor_prodsrv_rec.segment5,
306 p_segment6 => p_vendor_prodsrv_rec.segment6,
307 p_segment7 => p_vendor_prodsrv_rec.segment7,
308 p_segment8 => p_vendor_prodsrv_rec.segment8,
309 p_segment9 => p_vendor_prodsrv_rec.segment9,
310 p_segment10 => p_vendor_prodsrv_rec.segment10,
311 p_segment11 => p_vendor_prodsrv_rec.segment11,
312 p_segment12 => p_vendor_prodsrv_rec.segment12,
313 p_segment13 => p_vendor_prodsrv_rec.segment13,
314 p_segment14 => p_vendor_prodsrv_rec.segment14,
315 p_segment15 => p_vendor_prodsrv_rec.segment15,
316 p_segment16 => p_vendor_prodsrv_rec.segment16,
317 p_segment17 => p_vendor_prodsrv_rec.segment17,
318 p_segment18 => p_vendor_prodsrv_rec.segment18,
319 p_segment19 => p_vendor_prodsrv_rec.segment19,
320 p_segment20 => p_vendor_prodsrv_rec.segment20,
321 p_segment_definition => p_vendor_prodsrv_rec.segment_definition,
322 x_return_status => l_return_status,
323 x_msg_count => l_msg_count,
324 x_msg_data => l_msg_data);
325
326 IF x_return_status <> fnd_api.g_ret_sts_success THEN
327 fnd_file.put_line(fnd_file.log,
328 'No. of Messages: ' || l_msg_count || ', Message: ' ||
329 l_msg_data ||
330 ' From pos_product_service_utl_pkg.add_new_ps_req API.');
331 RETURN;
332 END IF;
333
334 -- If the request_status is "APPROVED" then do the following
335 -- IF (p_vendor_prodsrv_rec.request_status = 'APPROVED') THEN
336
337 -- Get the mapping_id using the following SQLL
338 SELECT mapping_id
339 INTO l_mapping_id
340 FROM pos_supplier_mappings
341 WHERE vendor_id = p_vendor_prodsrv_rec.vendor_id
342 AND party_id = p_party_id;
343
344 pos_product_service_utl_pkg.initialize(x_status => l_status,
345 x_error_message => l_error_message);
346
347 SELECT rtrim(nvl2(p_vendor_prodsrv_rec.segment1,
348 p_vendor_prodsrv_rec.segment1 || '.',
349 p_vendor_prodsrv_rec.segment1) ||
350 nvl2(p_vendor_prodsrv_rec.segment2,
351 p_vendor_prodsrv_rec.segment2 || '.',
352 p_vendor_prodsrv_rec.segment2) ||
353 nvl2(p_vendor_prodsrv_rec.segment3,
354 p_vendor_prodsrv_rec.segment3 || '.',
355 p_vendor_prodsrv_rec.segment3) ||
356 nvl2(p_vendor_prodsrv_rec.segment4,
357 p_vendor_prodsrv_rec.segment4 || '.',
358 p_vendor_prodsrv_rec.segment4) ||
359 nvl2(p_vendor_prodsrv_rec.segment5,
360 p_vendor_prodsrv_rec.segment5 || '.',
361 p_vendor_prodsrv_rec.segment5) ||
362 nvl2(p_vendor_prodsrv_rec.segment6,
363 p_vendor_prodsrv_rec.segment6 || '.',
364 p_vendor_prodsrv_rec.segment6) ||
365 nvl2(p_vendor_prodsrv_rec.segment7,
366 p_vendor_prodsrv_rec.segment7 || '.',
367 p_vendor_prodsrv_rec.segment7) ||
368 nvl2(p_vendor_prodsrv_rec.segment8,
369 p_vendor_prodsrv_rec.segment8 || '.',
370 p_vendor_prodsrv_rec.segment8) ||
371 nvl2(p_vendor_prodsrv_rec.segment9,
372 p_vendor_prodsrv_rec.segment9 || '.',
373 p_vendor_prodsrv_rec.segment9) ||
374 nvl2(p_vendor_prodsrv_rec.segment10,
375 p_vendor_prodsrv_rec.segment10 || '.',
376 p_vendor_prodsrv_rec.segment10) ||
377 nvl2(p_vendor_prodsrv_rec.segment11,
378 p_vendor_prodsrv_rec.segment11 || '.',
379 p_vendor_prodsrv_rec.segment11) ||
380 nvl2(p_vendor_prodsrv_rec.segment12,
381 p_vendor_prodsrv_rec.segment12 || '.',
382 p_vendor_prodsrv_rec.segment12) ||
383 nvl2(p_vendor_prodsrv_rec.segment13,
384 p_vendor_prodsrv_rec.segment13 || '.',
385 p_vendor_prodsrv_rec.segment13) ||
386 nvl2(p_vendor_prodsrv_rec.segment14,
387 p_vendor_prodsrv_rec.segment14 || '.',
388 p_vendor_prodsrv_rec.segment14) ||
389 nvl2(p_vendor_prodsrv_rec.segment15,
390 p_vendor_prodsrv_rec.segment15 || '.',
391 p_vendor_prodsrv_rec.segment15) ||
392 nvl2(p_vendor_prodsrv_rec.segment16,
393 p_vendor_prodsrv_rec.segment16 || '.',
394 p_vendor_prodsrv_rec.segment16) ||
395 nvl2(p_vendor_prodsrv_rec.segment17,
396 p_vendor_prodsrv_rec.segment17 || '.',
397 p_vendor_prodsrv_rec.segment17) ||
398 nvl2(p_vendor_prodsrv_rec.segment18,
399 p_vendor_prodsrv_rec.segment18 || '.',
400 p_vendor_prodsrv_rec.segment18) ||
401 nvl2(p_vendor_prodsrv_rec.segment19,
402 p_vendor_prodsrv_rec.segment19 || '.',
403 p_vendor_prodsrv_rec.segment19) ||
404 nvl2(p_vendor_prodsrv_rec.segment20,
405 p_vendor_prodsrv_rec.segment20 || '.',
406 p_vendor_prodsrv_rec.segment20),
407 '.')
408 INTO l_segment_concat
409 FROM dual;
410
411 l_req_id := pos_product_service_utl_pkg.get_requestid(x_segment_code => l_segment_concat,
412 x_mapp_id => l_mapping_id);
413
414 -- Using the request_id make a call to the following Api to approve the data and insert it into the
415 -- pos_sup_products_services table
416 pos_profile_change_request_pkg.approve_ps_req(p_request_id => l_req_id,
417 x_return_status => l_return_status,
418 x_msg_count => l_msg_count,
419 x_msg_data => l_msg_data);
420
421 IF x_return_status <> fnd_api.g_ret_sts_success THEN
422 fnd_file.put_line(fnd_file.log,
423 'No. of Messages: ' || l_msg_count || ', Message: ' ||
424 l_msg_data ||
425 ' From pos_profile_change_request_pkg.approve_ps_req API.');
426 RETURN;
427 END IF;
428 --END IF;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 x_return_status := fnd_api.g_ret_sts_unexp_error;
433 fnd_msg_pub.count_and_get(p_count => x_msg_count,
434 p_data => x_msg_data);
435 END create_prod_serv;
436
437 FUNCTION get_prod_serv_rec
438 (
439 p_from_vendor_id IN ap_suppliers.vendor_id%TYPE,
440 p_to_vendor_id IN ap_suppliers.vendor_id%TYPE,
441 p_classification_id IN pos_bus_class_attr.classification_id%TYPE,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2,
445 x_prod_services_rec OUT NOCOPY pos_sup_products_services%ROWTYPE
446 ) RETURN NUMBER IS
447 BEGIN
448 x_return_status := fnd_api.g_ret_sts_success;
449
450 SELECT --classification_id,
451 p_to_vendor_id,
452 segment1,
453 segment2,
454 segment3,
455 segment4,
456 segment5,
457 segment6,
458 segment7,
459 segment8,
460 segment9,
461 segment10,
462 segment11,
463 segment12,
464 segment13,
465 segment14,
466 segment15,
467 segment16,
468 segment17,
469 segment18,
470 segment19,
471 segment20,
472 status,
473 segment_definition
474 INTO --x_prod_services_rec . classification_id,
475 x_prod_services_rec.vendor_id,
476 x_prod_services_rec.segment1,
477 x_prod_services_rec.segment2,
478 x_prod_services_rec.segment3,
479 x_prod_services_rec.segment4,
480 x_prod_services_rec.segment5,
481 x_prod_services_rec.segment6,
482 x_prod_services_rec.segment7,
483 x_prod_services_rec.segment8,
484 x_prod_services_rec.segment9,
485 x_prod_services_rec.segment10,
486 x_prod_services_rec.segment11,
487 x_prod_services_rec.segment12,
488 x_prod_services_rec.segment13,
489 x_prod_services_rec.segment14,
490 x_prod_services_rec.segment15,
491 x_prod_services_rec.segment16,
492 x_prod_services_rec.segment17,
493 x_prod_services_rec.segment18,
494 x_prod_services_rec.segment19,
495 x_prod_services_rec.segment20,
496 x_prod_services_rec.status,
497 x_prod_services_rec.segment_definition
498 FROM pos_sup_products_services
499 WHERE vendor_id = p_from_vendor_id
500 AND classification_id = p_classification_id
501 /* Picking the products and services that are not already
502 associated with the To Party*/
503 AND (segment1, segment2, segment3, segment4, segment5, segment6,
504 segment7, segment8, segment9, segment10, segment11, segment12,
505 segment13, segment14, segment15, segment16, segment17,
506 segment18, segment19, segment20, segment_definition) NOT IN
507 (SELECT segment1,
508 segment2,
509 segment3,
510 segment4,
511 segment5,
512 segment6,
513 segment7,
514 segment8,
515 segment9,
516 segment10,
517 segment11,
518 segment12,
519 segment13,
520 segment14,
521 segment15,
522 segment16,
523 segment17,
524 segment18,
525 segment19,
526 segment20,
527 segment_definition
528 FROM pos_sup_products_services
529 WHERE vendor_id = p_to_vendor_id);
530
531 RETURN 1;
532
533 EXCEPTION
534 WHEN no_data_found THEN
535 RETURN 0;
536 WHEN OTHERS THEN
537 x_return_status := fnd_api.g_ret_sts_unexp_error;
538
539 fnd_msg_pub.count_and_get(p_count => x_msg_count,
540 p_data => x_msg_data);
541 RETURN 0;
542 END get_prod_serv_rec;
543
544 PROCEDURE prod_service_merge
545 (
546 p_entity_name IN VARCHAR2,
547 p_from_id IN NUMBER,
548 x_to_id IN OUT NOCOPY NUMBER,
549 p_from_fk_id IN NUMBER,
550 p_to_fk_id IN NUMBER,
551 p_parent_entity_name IN VARCHAR2,
552 p_batch_id IN NUMBER,
553 p_batch_party_id IN NUMBER,
554 x_return_status OUT NOCOPY VARCHAR2
555 ) IS
556 l_prod_services_rec pos_sup_products_services%ROWTYPE;
557 l_from_vendor_id NUMBER;
558 l_to_vendor_id NUMBER;
559 l_msg_count NUMBER;
560 l_msg_data VARCHAR2(2000);
561 l_row_count NUMBER := 0;
562 BEGIN
563 x_return_status := fnd_api.g_ret_sts_success;
564
565 fnd_file.put_line(fnd_file.log,
566 'Inside POS_MERGE_SUPPLIER_PKG.prod_service_merge p_to_fk_id: ' ||
567 p_to_fk_id || ' p_from_fk_id: ' || p_from_fk_id ||
568 ' p_from_id : ' || p_from_id);
569
570 SELECT vendor_id
571 INTO l_from_vendor_id
572 FROM ap_suppliers
573 WHERE party_id = p_from_fk_id;
574
575 SELECT vendor_id
576 INTO l_to_vendor_id
577 FROM ap_suppliers
578 WHERE party_id = p_to_fk_id;
579
580 /* Set the status to MERGED */
581 UPDATE pos_sup_products_services
582 SET status = 'M',
583 last_update_date = SYSDATE,
584 last_updated_by = hz_utility_pub.user_id,
585 last_update_login = hz_utility_pub.last_update_login
586 WHERE vendor_id = l_from_vendor_id
587 AND classification_id = p_from_id;
588
589 DELETE FROM pos_supplier_mappings a
590 WHERE a.party_id = p_to_fk_id
591 AND a.vendor_id <> l_to_vendor_id
592 AND EXISTS (SELECT 1
593 FROM pos_supplier_mappings b
594 WHERE b.party_id = p_to_fk_id
595 AND vendor_id = l_to_vendor_id);
596
597 UPDATE pos_supplier_mappings
598 SET vendor_id = l_to_vendor_id,
599 last_updated_by = fnd_global.user_id,
600 last_update_date = SYSDATE,
601 last_update_login = fnd_global.login_id
602 WHERE mapping_id IN (SELECT mapping_id
603 FROM pos_supplier_mappings
604 WHERE party_id = p_to_fk_id);
605
606 /* Get the details for the from party id */
607
608 l_row_count := get_prod_serv_rec(l_from_vendor_id,
609 l_to_vendor_id,
610 p_from_id,
611 x_return_status,
612 l_msg_count,
613 l_msg_data,
614 l_prod_services_rec);
615
616 IF x_return_status <> fnd_api.g_ret_sts_success THEN
617 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
618 fnd_message.set_token('ERROR',
619 'Cannot get classification ID : ' || p_from_id);
620 fnd_msg_pub.add;
621
622 fnd_file.put_line(fnd_file.log,
623 'No. of Messages: ' || l_msg_count || ', Message: ' ||
624 l_msg_data || ' From get_prod_serv_rec API.');
625 x_return_status := fnd_api.g_ret_sts_error;
626
627 RETURN;
628 END IF;
629
630 IF (l_row_count <> 0) THEN
631 create_prod_serv(l_prod_services_rec,
632 p_to_fk_id,
633 l_to_vendor_id,
634 x_return_status,
635 l_msg_count,
636 l_msg_data);
637
638 IF x_return_status <> fnd_api.g_ret_sts_success THEN
639 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
640 fnd_message.set_token('ERROR',
641 'Cannot copy classification ID : ' ||
642 p_from_id);
643 fnd_msg_pub.add;
644
645 fnd_file.put_line(fnd_file.log,
646 'No. of Messages: ' || l_msg_count ||
647 ', Message: ' || l_msg_data ||
648 ' From create_prod_serv API.');
649 x_return_status := fnd_api.g_ret_sts_error;
650 RETURN;
651 END IF;
652 END IF;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 x_return_status := fnd_api.g_ret_sts_unexp_error;
657 END prod_service_merge;
658
659 PROCEDURE party_contact_merge
660 (
661 p_entity_name IN VARCHAR2,
662 p_from_id IN NUMBER,
663 x_to_id IN OUT NOCOPY NUMBER,
664 p_from_fk_id IN NUMBER,
665 p_to_fk_id IN NUMBER,
666 p_parent_entity_name IN VARCHAR2,
667 p_batch_id IN NUMBER,
668 p_batch_party_id IN NUMBER,
669 x_return_status OUT NOCOPY VARCHAR2
670 ) IS
671 l_msg_count NUMBER;
672 l_msg_data VARCHAR2(2000);
673 l_party_id NUMBER;
674 l_party_usg_rec hz_party_usg_assignment_pvt.party_usg_assignment_rec_type;
675 l_party_usg_validation_level NUMBER;
676
677 BEGIN
678 x_return_status := fnd_api.g_ret_sts_success;
679
680 fnd_file.put_line(fnd_file.log,
681 'Inside POS_MERGE_SUPPLIER_PKG.party_contact_merge p_to_fk_id: ' ||
682 p_to_fk_id || ' p_from_fk_id: ' || p_from_fk_id ||
683 ' p_from_id : ' || p_from_id);
684
685 l_party_id := p_to_fk_id;
686
687 l_party_usg_validation_level := hz_party_usg_assignment_pvt.g_valid_level_none;
688 l_party_usg_rec.party_id := l_party_id;
689 l_party_usg_rec.party_usage_code := 'SUPPLIER_CONTACT';
690 l_party_usg_rec.created_by_module := 'AP_SUPPLIERS_MERGE';
691
692 /* Enable party contact as supplier contact by
693 setting usage code as SUPPLIER_CONTACT */
694 hz_party_usg_assignment_pvt.assign_party_usage(p_validation_level => l_party_usg_validation_level,
695 p_party_usg_assignment_rec => l_party_usg_rec,
696 x_return_status => x_return_status,
697 x_msg_count => l_msg_count,
698 x_msg_data => l_msg_data);
699
700 IF x_return_status <> fnd_api.g_ret_sts_success THEN
701 fnd_file.put_line(fnd_file.log,
702 'No. of Messages: ' || l_msg_count || ', Message: ' ||
703 l_msg_data ||
704 ' From hz_party_usg_assignment_pvt.assign_party_usage API.');
705 END IF;
706
707 EXCEPTION
708 WHEN OTHERS THEN
709 x_return_status := fnd_api.g_ret_sts_unexp_error;
710 END party_contact_merge;
711
712 PROCEDURE create_bank_dtls
713 (
714 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
715 p_payee IN iby_disbursement_setup_pub.payeecontext_rec_type,
716 p_assignment_attribs IN iby_fndcpt_setup_pub.pmtinstrassignment_rec_type,
717 x_return_status OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_msg_data OUT NOCOPY VARCHAR2
720 ) IS
721 l_assign_id NUMBER;
722 l_response iby_fndcpt_common_pub.result_rec_type;
723 BEGIN
724 x_return_status := fnd_api.g_ret_sts_success;
725 BEGIN
726 iby_disbursement_setup_pub.set_payee_instr_assignment(p_api_version => '1.0',
727 x_return_status => x_return_status,
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data,
730 p_payee => p_payee,
731 p_assignment_attribs => p_assignment_attribs,
732 x_assign_id => l_assign_id,
733 x_response => l_response);
734
735 IF x_return_status <> fnd_api.g_ret_sts_success THEN
736 fnd_file.put_line(fnd_file.log,
737 'No. of Messages: ' || x_msg_count ||
738 ', Message: ' || x_msg_data ||
739 ' From iby_disbursement_setup_pub.set_payee_instr_assignment API.');
740 END IF;
741 END;
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 x_return_status := fnd_api.g_ret_sts_unexp_error;
746 fnd_msg_pub.count_and_get(p_count => x_msg_count,
747 p_data => x_msg_data);
748 END create_bank_dtls;
749
750 PROCEDURE get_bank_dtls_rec
751 (
752 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
753 p_from_party IN hz_parties.party_id%TYPE,
754 p_to_party IN hz_parties.party_id%TYPE,
755 x_bank_dtls_rec_tbl OUT NOCOPY bank_dtls_tab_type,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2
759 ) IS
760 -- Bug 14261475 - merge performance: full table scan on iby_pmt_instr_uses_all
761 CURSOR bank_cur IS
762 SELECT paymentinstrumentuseseo.instrument_type,
763 paymentinstrumentuseseo.instrument_id,
764 paymentinstrumentuseseo.payment_function
765 FROM iby_pmt_instr_uses_all paymentinstrumentuseseo
766 --iby_ext_bank_accounts_v ibyextbankaccts
767 WHERE --paymentinstrumentuseseo.instrument_id = ibyextbankaccts.bank_account_id AND
768 paymentinstrumentuseseo.instrument_type = 'BANKACCOUNT'
769 --Enforce the IBY_PMT_INSTR_USES_ALL_N1 index PAYMENT_FLOW,EXT_PMT_PARTY_ID
770 AND paymentinstrumentuseseo.payment_flow IN
771 (SELECT lookup_code FROM fnd_lookup_values
772 WHERE lookup_type = 'IBY_PAYMENT_FLOW'
773 AND language = userenv('LANG'))
774 AND ext_pmt_party_id IN
775 (SELECT ext_payee_id
776 FROM iby_external_payees_all
777 WHERE payee_party_id = p_from_party
778 AND org_id IS NULL
779 AND party_site_id IS NULL
780 AND supplier_site_id IS NULL)
781 AND /* Excluding the accounts that are already associated*/
782 paymentinstrumentuseseo.instrument_id NOT IN
783 (SELECT instrument_id
784 FROM iby_external_payees_all extpayee,
785 iby_pmt_instr_uses_all instr
786 WHERE extpayee.payee_party_id = p_to_party
787 AND extpayee.org_id IS NULL
788 AND extpayee.party_site_id IS NULL
789 AND extpayee.supplier_site_id IS NULL
790 AND extpayee.ext_payee_id = ext_pmt_party_id
791 AND instr.instrument_type = 'BANKACCOUNT');
792
793 BEGIN
794 x_return_status := fnd_api.g_ret_sts_success;
795
796 OPEN bank_cur;
797 FETCH bank_cur BULK COLLECT
798 INTO x_bank_dtls_rec_tbl;
799 CLOSE bank_cur;
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 x_return_status := fnd_api.g_ret_sts_unexp_error;
804 fnd_msg_pub.count_and_get(p_count => x_msg_count,
805 p_data => x_msg_data);
806 END get_bank_dtls_rec;
807
808 PROCEDURE bank_dtls_merge
809 (
810 p_entity_name IN VARCHAR2,
811 p_from_id IN NUMBER,
812 x_to_id IN OUT NOCOPY NUMBER,
813 p_from_fk_id IN NUMBER,
814 p_to_fk_id IN NUMBER,
815 p_parent_entity_name IN VARCHAR2,
816 p_batch_id IN NUMBER,
817 p_batch_party_id IN NUMBER,
818 x_return_status OUT NOCOPY VARCHAR2
819 ) IS
820 l_return_status VARCHAR2(100);
821 l_msg_count NUMBER;
822 l_msg_data VARCHAR2(100);
823
824 l_bank_dtls_rec_tbl bank_dtls_tab_type;
825 l_rec iby_disbursement_setup_pub.payeecontext_rec_type;
826 l_assign iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
827 BEGIN
828 x_return_status := fnd_api.g_ret_sts_success;
829
830 fnd_file.put_line(fnd_file.log,
831 'Inside POS_MERGE_SUPPLIER_PKG.bank_dtls_merge p_to_fk_id: ' ||
832 p_to_fk_id || ' p_to_fk_id: ' || p_from_fk_id);
833
834 get_bank_dtls_rec(p_init_msg_list => 'T',
835 p_from_party => p_from_fk_id,
836 p_to_party => p_to_fk_id,
837 x_bank_dtls_rec_tbl => l_bank_dtls_rec_tbl,
838 x_return_status => x_return_status,
839 x_msg_count => l_msg_count,
840 x_msg_data => l_msg_data);
841
842 IF x_return_status <> fnd_api.g_ret_sts_success THEN
843 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
844 fnd_message.set_token('ERROR',
845 'Cannot get Bank Details : ' || p_from_id);
846 fnd_msg_pub.add;
847
848 fnd_file.put_line(fnd_file.log,
849 'No. of Messages: ' || l_msg_count || ', Message: ' ||
850 l_msg_data || ' From get_bank_dtls_rec API.');
851 x_return_status := fnd_api.g_ret_sts_error;
852
853 RETURN;
854 END IF;
855
856 -- Add all the accounts in the collection
857
858 FOR cntr IN 1 .. l_bank_dtls_rec_tbl.count LOOP
859
860 l_rec.party_id := p_to_fk_id;
861 l_rec.payment_function := l_bank_dtls_rec_tbl(cntr).payment_function;
862 l_rec.org_type := NULL;
863 l_rec.org_id := NULL;
864 l_rec.party_site_id := NULL;
865 l_rec.supplier_site_id := NULL;
866
867 l_assign.instrument.instrument_type := l_bank_dtls_rec_tbl(cntr)
868 .instrument_type;
869 l_assign.instrument.instrument_id := l_bank_dtls_rec_tbl(cntr)
870 .instrument_id;
871
872 create_bank_dtls(p_init_msg_list => 'T',
873 p_payee => l_rec,
874 p_assignment_attribs => l_assign,
875 x_return_status => x_return_status,
876 x_msg_count => l_msg_count,
877 x_msg_data => l_msg_data);
878
879 IF x_return_status <> fnd_api.g_ret_sts_success THEN
880 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
881 fnd_message.set_token('ERROR',
882 'Cannot copy Bank Details : ' || p_from_id);
883 fnd_msg_pub.add;
884
885 fnd_file.put_line(fnd_file.log,
886 'No. of Messages: ' || l_msg_count ||
887 ', Message: ' || l_msg_data ||
888 ' From create_bank_dtls API.');
889 x_return_status := fnd_api.g_ret_sts_error;
890 RETURN;
891 END IF;
892 END LOOP;
893
894 EXCEPTION
895 WHEN OTHERS THEN
896 x_return_status := fnd_api.g_ret_sts_unexp_error;
897 END bank_dtls_merge;
898
899 PROCEDURE get_batch_user_attr_data
900 (
901 p_to_party_id IN NUMBER,
902 p_batch_id IN NUMBER,
903 p_multirow_flag IN VARCHAR2,
904 p_attribute_group_id IN NUMBER,
905 p_data_level_id IN NUMBER,
906 x_uda_rec_tbl OUT NOCOPY uda_rec_tbl_type,
907 x_return_status OUT NOCOPY VARCHAR2,
908 x_msg_count OUT NOCOPY NUMBER,
909 x_msg_data OUT NOCOPY VARCHAR2
910 ) IS
911 CURSOR multirow_cur IS
912 SELECT *
913 FROM pos_supp_prof_ext_b
914 WHERE attr_group_id = p_attribute_group_id
915 AND data_level_id = p_data_level_id
916 AND party_id IN ( -- Only from parties
917 SELECT from_party_id
918 FROM hz_merge_parties
919 WHERE batch_id = p_batch_id
920 --AND merge_status = 'DONE'
921 AND merge_type <> 'SAME_PARTY_MERGE'
922 AND to_party_id = p_to_party_id)
923 ORDER BY last_update_date;
924
925 CURSOR singlerow_cur IS
926 SELECT *
927 FROM (SELECT *
928 FROM pos_supp_prof_ext_b
929 WHERE attr_group_id = p_attribute_group_id
930 AND data_level_id = p_data_level_id
931 AND party_id IN
932 (SELECT from_party_id
933 FROM hz_merge_parties
934 WHERE batch_id = p_batch_id
935 AND merge_type <> 'SAME_PARTY_MERGE'
936 AND to_party_id = p_to_party_id)
937 ORDER BY last_update_date)
938 UNION ALL (SELECT *
939 FROM pos_supp_prof_ext_b
940 WHERE attr_group_id = p_attribute_group_id
941 AND data_level_id = p_data_level_id
942 AND party_id = p_to_party_id);
943 BEGIN
944 x_return_status := fnd_api.g_ret_sts_success;
945 /* If the attribute group is single row then we have to get the
946 data for the from as well as the to party.
947 For Multirow attribute we need to get the data only for the
948 from parties */
949
950 IF (p_multirow_flag = 'Y') THEN
951
952 /* Multirow processing */
953 /* Bulk collect the data into the collection defined as out
954 parameter*/
955 OPEN multirow_cur;
956 FETCH multirow_cur BULK COLLECT
957 INTO x_uda_rec_tbl;
958 CLOSE multirow_cur;
959 ELSE
960 /* single Row processing */
961 /* Bulk collect the data into the collection defined as out
962 parameter*/
963 OPEN singlerow_cur;
964 FETCH singlerow_cur BULK COLLECT
965 INTO x_uda_rec_tbl;
966 CLOSE singlerow_cur;
967
968 END IF;
969
970 EXCEPTION
971 WHEN OTHERS THEN
972 x_return_status := fnd_api.g_ret_sts_unexp_error;
973 END get_batch_user_attr_data;
974
975 PROCEDURE get_party_site_attr_data
976 (
977 p_from_party_id IN NUMBER,
978 p_to_party_id IN NUMBER,
979 p_batch_id IN NUMBER,
980 p_multirow_flag IN VARCHAR2,
981 p_attribute_group_id IN NUMBER,
982 p_data_level_id IN NUMBER,
983 x_uda_rec_tbl OUT NOCOPY uda_rec_tbl_type,
984 x_return_status OUT NOCOPY VARCHAR2,
985 x_msg_count OUT NOCOPY NUMBER,
986 x_msg_data OUT NOCOPY VARCHAR2
987 ) IS
988
989 CURSOR multirow_cur IS
990 SELECT *
991 FROM pos_supp_prof_ext_b
992 WHERE attr_group_id = p_attribute_group_id
993 AND data_level_id = p_data_level_id
994 AND (party_id, pk1_value) IN
995 (SELECT fromparty.party_id,
996 merge_from_entity_id
997 FROM hz_merge_party_details site,
998 hz_party_sites fromparty,
999 hz_party_sites toparty,
1000 hz_merge_parties batch
1001 WHERE fromparty.party_site_id = merge_from_entity_id
1002 AND toparty.party_site_id = merge_to_entity_id
1003 AND batch.batch_id = p_batch_id
1004 AND batch.batch_party_id = site.batch_party_id
1005 AND merge_to_entity_id IN
1006 (SELECT merge_to_entity_id
1007 FROM hz_merge_party_details site2
1008 WHERE site2.batch_party_id = site.batch_party_id
1009 AND merge_from_entity_id = p_from_party_id))
1010 ORDER BY last_update_date;
1011
1012 CURSOR singlerow_cur IS
1013 SELECT *
1014 FROM (SELECT *
1015 FROM pos_supp_prof_ext_b
1016 WHERE attr_group_id = p_attribute_group_id
1017 AND data_level_id = p_data_level_id
1018 AND (party_id, pk1_value) IN
1019 (SELECT fromparty.party_id,
1020 merge_from_entity_id
1021 FROM hz_merge_party_details site,
1022 hz_party_sites fromparty,
1023 hz_party_sites toparty,
1024 hz_merge_parties batch
1025 WHERE fromparty.party_site_id = merge_from_entity_id
1026 AND toparty.party_site_id = merge_to_entity_id
1027 AND batch.batch_id = p_batch_id
1028 AND batch.batch_party_id = site.batch_party_id
1029 AND merge_to_entity_id IN
1030 (SELECT merge_to_entity_id
1031 FROM hz_merge_party_details site2
1032 WHERE site2.batch_party_id =
1033 site.batch_party_id
1034 AND merge_from_entity_id = p_from_party_id))
1035 ORDER BY last_update_date)
1036 UNION ALL
1037 SELECT *
1038 FROM pos_supp_prof_ext_b
1039 WHERE attr_group_id = p_attribute_group_id
1040 AND data_level_id = p_data_level_id
1041 AND pk1_value = p_to_party_id;
1042 BEGIN
1043 x_return_status := fnd_api.g_ret_sts_success;
1044
1045 IF (p_multirow_flag = 'Y') THEN
1046 OPEN multirow_cur;
1047 FETCH multirow_cur BULK COLLECT
1048 INTO x_uda_rec_tbl;
1049 CLOSE multirow_cur;
1050 ELSE
1051 OPEN singlerow_cur;
1052 FETCH singlerow_cur BULK COLLECT
1053 INTO x_uda_rec_tbl;
1054 CLOSE singlerow_cur;
1055 END IF;
1056
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 x_return_status := fnd_api.g_ret_sts_unexp_error;
1060 END get_party_site_attr_data;
1061
1062 PROCEDURE get_supp_site_attr_data
1063 (
1064 p_from_party_id IN NUMBER,
1065 p_to_party_id IN NUMBER,
1066 p_multirow_flag IN VARCHAR2,
1067 p_attribute_group_id IN NUMBER,
1068 p_data_level_id IN NUMBER,
1069 x_uda_rec_tbl OUT NOCOPY uda_rec_tbl_type,
1070 x_return_status OUT NOCOPY VARCHAR2,
1071 x_msg_count OUT NOCOPY NUMBER,
1072 x_msg_data OUT NOCOPY VARCHAR2
1073 ) IS
1074
1075 CURSOR multirow_cur IS
1076 SELECT *
1077 FROM pos_supp_prof_ext_b
1078 WHERE attr_group_id = p_attribute_group_id
1079 AND data_level_id = p_data_level_id
1080 AND pk2_value = p_from_party_id
1081 ORDER BY last_update_date;
1082
1083 CURSOR singlerow_cur IS
1084 SELECT *
1085 FROM pos_supp_prof_ext_b
1086 WHERE attr_group_id = p_attribute_group_id
1087 AND data_level_id = p_data_level_id
1088 AND pk2_value = p_from_party_id
1089 UNION ALL
1090 SELECT *
1091 FROM pos_supp_prof_ext_b
1092 WHERE attr_group_id = p_attribute_group_id
1093 AND data_level_id = p_data_level_id
1094 AND pk2_value = p_to_party_id;
1095 BEGIN
1096 x_return_status := fnd_api.g_ret_sts_success;
1097
1098 IF (p_multirow_flag = 'Y') THEN
1099 OPEN multirow_cur;
1100 FETCH multirow_cur BULK COLLECT
1101 INTO x_uda_rec_tbl;
1102 CLOSE multirow_cur;
1103 ELSE
1104 OPEN singlerow_cur;
1105 FETCH singlerow_cur BULK COLLECT
1106 INTO x_uda_rec_tbl;
1107 CLOSE singlerow_cur;
1108 END IF;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 x_return_status := fnd_api.g_ret_sts_unexp_error;
1113 END get_supp_site_attr_data;
1114
1115 PROCEDURE build_uda_data_payload
1116 (
1117 p_attribute_group_id IN NUMBER,
1118 p_attribute_group_type IN VARCHAR2,
1119 p_attribute_group_name IN VARCHAR2,
1120 p_row_identifier IN NUMBER,
1121 p_uda_data_rec IN pos_supp_prof_ext_b%ROWTYPE,
1122 x_attributes_data_table IN OUT NOCOPY ego_user_attr_data_table
1123 ) IS
1124
1125 CURSOR get_attributes IS
1126 SELECT attr_name,
1127 database_column
1128 FROM ego_attrs_v
1129 WHERE application_id = 177
1130 AND attr_group_name = p_attribute_group_name
1131 AND attr_group_type = p_attribute_group_type;
1132
1133 BEGIN
1134
1135 /* Open the cursor and bulk collect the attributes information into
1136 a collection */
1137 OPEN get_attributes;
1138 FETCH get_attributes BULK COLLECT
1139 INTO attributes_coll;
1140 CLOSE get_attributes;
1141
1142 /* Loop through the attributes collection and traverse the following case structure */
1143
1144 FOR attrcntr IN 1 .. attributes_coll.count LOOP
1145 --x_attributes_data_table.extend;
1146 CASE attributes_coll(attrcntr).database_column
1147
1148 /* Process Character attributes */
1149
1150 WHEN 'C_EXT_ATTR1' THEN
1151 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1152 attributes_coll(attrcntr)
1153 .attr_name,
1154 p_uda_data_rec.c_ext_attr1 --ATTR_VALUE_STR
1155 ,
1156 NULL --ATTR_VALUE_NUM
1157 ,
1158 NULL --ATTR_VALUE_DATE
1159 ,
1160 NULL --ATTR_DISP_VALUE
1161 ,
1162 NULL --ATTR_UNIT_OF_MEASURE
1163 ,
1164 NULL);
1165 WHEN 'C_EXT_ATTR2' THEN
1166 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1167 attributes_coll(attrcntr)
1168 .attr_name,
1169 p_uda_data_rec.c_ext_attr2 --ATTR_VALUE_STR
1170 ,
1171 NULL --ATTR_VALUE_NUM
1172 ,
1173 NULL --ATTR_VALUE_DATE
1174 ,
1175 NULL --ATTR_DISP_VALUE
1176 ,
1177 NULL --ATTR_UNIT_OF_MEASURE
1178 ,
1179 NULL);
1180 WHEN 'C_EXT_ATTR3' THEN
1181 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1182 attributes_coll(attrcntr)
1183 .attr_name,
1184 p_uda_data_rec.c_ext_attr3 --ATTR_VALUE_STR
1185 ,
1186 NULL --ATTR_VALUE_NUM
1187 ,
1188 NULL --ATTR_VALUE_DATE
1189 ,
1190 NULL --ATTR_DISP_VALUE
1191 ,
1192 NULL --ATTR_UNIT_OF_MEASURE
1193 ,
1194 NULL);
1195 WHEN 'C_EXT_ATTR4' THEN
1196 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1197 attributes_coll(attrcntr)
1198 .attr_name,
1199 p_uda_data_rec.c_ext_attr4 --ATTR_VALUE_STR
1200 ,
1201 NULL --ATTR_VALUE_NUM
1202 ,
1203 NULL --ATTR_VALUE_DATE
1204 ,
1205 NULL --ATTR_DISP_VALUE
1206 ,
1207 NULL --ATTR_UNIT_OF_MEASURE
1208 ,
1209 NULL);
1210 WHEN 'C_EXT_ATTR5' THEN
1211 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1212 attributes_coll(attrcntr)
1213 .attr_name,
1214 p_uda_data_rec.c_ext_attr5 --ATTR_VALUE_STR
1215 ,
1216 NULL --ATTR_VALUE_NUM
1217 ,
1218 NULL --ATTR_VALUE_DATE
1219 ,
1220 NULL --ATTR_DISP_VALUE
1221 ,
1222 NULL --ATTR_UNIT_OF_MEASURE
1223 ,
1224 NULL);
1225 WHEN 'C_EXT_ATTR6' THEN
1226 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1227 attributes_coll(attrcntr)
1228 .attr_name,
1229 p_uda_data_rec.c_ext_attr6 --ATTR_VALUE_STR
1230 ,
1231 NULL --ATTR_VALUE_NUM
1232 ,
1233 NULL --ATTR_VALUE_DATE
1234 ,
1235 NULL --ATTR_DISP_VALUE
1236 ,
1237 NULL --ATTR_UNIT_OF_MEASURE
1238 ,
1239 NULL);
1240 WHEN 'C_EXT_ATTR7' THEN
1241 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1242 attributes_coll(attrcntr)
1243 .attr_name,
1244 p_uda_data_rec.c_ext_attr7 --ATTR_VALUE_STR
1245 ,
1246 NULL --ATTR_VALUE_NUM
1247 ,
1248 NULL --ATTR_VALUE_DATE
1249 ,
1250 NULL --ATTR_DISP_VALUE
1251 ,
1252 NULL --ATTR_UNIT_OF_MEASURE
1253 ,
1254 NULL);
1255 WHEN 'C_EXT_ATTR8' THEN
1256 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1257 attributes_coll(attrcntr)
1258 .attr_name,
1259 p_uda_data_rec.c_ext_attr8 --ATTR_VALUE_STR
1260 ,
1261 NULL --ATTR_VALUE_NUM
1262 ,
1263 NULL --ATTR_VALUE_DATE
1264 ,
1265 NULL --ATTR_DISP_VALUE
1266 ,
1267 NULL --ATTR_UNIT_OF_MEASURE
1268 ,
1269 NULL);
1270
1271 WHEN 'C_EXT_ATTR9' THEN
1272 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1273 attributes_coll(attrcntr)
1274 .attr_name,
1275 p_uda_data_rec.c_ext_attr9 --ATTR_VALUE_STR
1276 ,
1277 NULL --ATTR_VALUE_NUM
1278 ,
1279 NULL --ATTR_VALUE_DATE
1280 ,
1281 NULL --ATTR_DISP_VALUE
1282 ,
1283 NULL --ATTR_UNIT_OF_MEASURE
1284 ,
1285 NULL);
1286 WHEN 'C_EXT_ATTR10' THEN
1287 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1288 attributes_coll(attrcntr)
1289 .attr_name,
1290 p_uda_data_rec.c_ext_attr10 --ATTR_VALUE_STR
1291 ,
1292 NULL --ATTR_VALUE_NUM
1293 ,
1294 NULL --ATTR_VALUE_DATE
1295 ,
1296 NULL --ATTR_DISP_VALUE
1297 ,
1298 NULL --ATTR_UNIT_OF_MEASURE
1299 ,
1300 NULL);
1301 WHEN 'C_EXT_ATTR11' THEN
1302 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1303 attributes_coll(attrcntr)
1304 .attr_name,
1305 p_uda_data_rec.c_ext_attr11 --ATTR_VALUE_STR
1306 ,
1307 NULL --ATTR_VALUE_NUM
1308 ,
1309 NULL --ATTR_VALUE_DATE
1310 ,
1311 NULL --ATTR_DISP_VALUE
1312 ,
1313 NULL --ATTR_UNIT_OF_MEASURE
1314 ,
1315 NULL);
1316 WHEN 'C_EXT_ATTR12' THEN
1317 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1318 attributes_coll(attrcntr)
1319 .attr_name,
1320 p_uda_data_rec.c_ext_attr12 --ATTR_VALUE_STR
1321 ,
1322 NULL --ATTR_VALUE_NUM
1323 ,
1324 NULL --ATTR_VALUE_DATE
1325 ,
1326 NULL --ATTR_DISP_VALUE
1327 ,
1328 NULL --ATTR_UNIT_OF_MEASURE
1329 ,
1330 NULL);
1331 WHEN 'C_EXT_ATTR13' THEN
1332 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1333 attributes_coll(attrcntr)
1334 .attr_name,
1335 p_uda_data_rec.c_ext_attr13 --ATTR_VALUE_STR
1336 ,
1337 NULL --ATTR_VALUE_NUM
1338 ,
1339 NULL --ATTR_VALUE_DATE
1340 ,
1341 NULL --ATTR_DISP_VALUE
1342 ,
1343 NULL --ATTR_UNIT_OF_MEASURE
1344 ,
1345 NULL);
1346 WHEN 'C_EXT_ATTR14' THEN
1347 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1348 attributes_coll(attrcntr)
1349 .attr_name,
1350 p_uda_data_rec.c_ext_attr14 --ATTR_VALUE_STR
1351 ,
1352 NULL --ATTR_VALUE_NUM
1353 ,
1354 NULL --ATTR_VALUE_DATE
1355 ,
1356 NULL --ATTR_DISP_VALUE
1357 ,
1358 NULL --ATTR_UNIT_OF_MEASURE
1359 ,
1360 NULL);
1361 WHEN 'C_EXT_ATTR15' THEN
1362 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1363 attributes_coll(attrcntr)
1364 .attr_name,
1365 p_uda_data_rec.c_ext_attr15 --ATTR_VALUE_STR
1366 ,
1367 NULL --ATTR_VALUE_NUM
1368 ,
1369 NULL --ATTR_VALUE_DATE
1370 ,
1371 NULL --ATTR_DISP_VALUE
1372 ,
1373 NULL --ATTR_UNIT_OF_MEASURE
1374 ,
1375 NULL);
1376 WHEN 'C_EXT_ATTR16' THEN
1377 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1378 attributes_coll(attrcntr)
1379 .attr_name,
1380 p_uda_data_rec.c_ext_attr16 --ATTR_VALUE_STR
1381 ,
1382 NULL --ATTR_VALUE_NUM
1383 ,
1384 NULL --ATTR_VALUE_DATE
1385 ,
1386 NULL --ATTR_DISP_VALUE
1387 ,
1388 NULL --ATTR_UNIT_OF_MEASURE
1389 ,
1390 NULL);
1391 WHEN 'C_EXT_ATTR17' THEN
1392 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1393 attributes_coll(attrcntr)
1394 .attr_name,
1395 p_uda_data_rec.c_ext_attr17 --ATTR_VALUE_STR
1396 ,
1397 NULL --ATTR_VALUE_NUM
1398 ,
1399 NULL --ATTR_VALUE_DATE
1400 ,
1401 NULL --ATTR_DISP_VALUE
1402 ,
1403 NULL --ATTR_UNIT_OF_MEASURE
1404 ,
1405 NULL);
1406 WHEN 'C_EXT_ATTR18' THEN
1407 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1408 attributes_coll(attrcntr)
1409 .attr_name,
1410 p_uda_data_rec.c_ext_attr18 --ATTR_VALUE_STR
1411 ,
1412 NULL --ATTR_VALUE_NUM
1413 ,
1414 NULL --ATTR_VALUE_DATE
1415 ,
1416 NULL --ATTR_DISP_VALUE
1417 ,
1418 NULL --ATTR_UNIT_OF_MEASURE
1419 ,
1420 NULL);
1421 WHEN 'C_EXT_ATTR19' THEN
1422 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1423 attributes_coll(attrcntr)
1424 .attr_name,
1425 p_uda_data_rec.c_ext_attr19 --ATTR_VALUE_STR
1426 ,
1427 NULL --ATTR_VALUE_NUM
1428 ,
1429 NULL --ATTR_VALUE_DATE
1430 ,
1431 NULL --ATTR_DISP_VALUE
1432 ,
1433 NULL --ATTR_UNIT_OF_MEASURE
1434 ,
1435 NULL);
1436
1437 WHEN 'C_EXT_ATTR20' THEN
1438 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1439 attributes_coll(attrcntr)
1440 .attr_name,
1441 p_uda_data_rec.c_ext_attr20 --ATTR_VALUE_STR
1442 ,
1443 NULL --ATTR_VALUE_NUM
1444 ,
1445 NULL --ATTR_VALUE_DATE
1446 ,
1447 NULL --ATTR_DISP_VALUE
1448 ,
1449 NULL --ATTR_UNIT_OF_MEASURE
1450 ,
1451 NULL);
1452 WHEN 'C_EXT_ATTR21' THEN
1453 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1454 attributes_coll(attrcntr)
1455 .attr_name,
1456 p_uda_data_rec.c_ext_attr21 --ATTR_VALUE_STR
1457 ,
1458 NULL --ATTR_VALUE_NUM
1459 ,
1460 NULL --ATTR_VALUE_DATE
1461 ,
1462 NULL --ATTR_DISP_VALUE
1463 ,
1464 NULL --ATTR_UNIT_OF_MEASURE
1465 ,
1466 NULL);
1467 WHEN 'C_EXT_ATTR22' THEN
1468 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1469 attributes_coll(attrcntr)
1470 .attr_name,
1471 p_uda_data_rec.c_ext_attr22 --ATTR_VALUE_STR
1472 ,
1473 NULL --ATTR_VALUE_NUM
1474 ,
1475 NULL --ATTR_VALUE_DATE
1476 ,
1477 NULL --ATTR_DISP_VALUE
1478 ,
1479 NULL --ATTR_UNIT_OF_MEASURE
1480 ,
1481 NULL);
1482 WHEN 'C_EXT_ATTR23' THEN
1483 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1484 attributes_coll(attrcntr)
1485 .attr_name,
1486 p_uda_data_rec.c_ext_attr23 --ATTR_VALUE_STR
1487 ,
1488 NULL --ATTR_VALUE_NUM
1489 ,
1490 NULL --ATTR_VALUE_DATE
1491 ,
1492 NULL --ATTR_DISP_VALUE
1493 ,
1494 NULL --ATTR_UNIT_OF_MEASURE
1495 ,
1496 NULL);
1497 WHEN 'C_EXT_ATTR24' THEN
1498 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1499 attributes_coll(attrcntr)
1500 .attr_name,
1501 p_uda_data_rec.c_ext_attr24 --ATTR_VALUE_STR
1502 ,
1503 NULL --ATTR_VALUE_NUM
1504 ,
1505 NULL --ATTR_VALUE_DATE
1506 ,
1507 NULL --ATTR_DISP_VALUE
1508 ,
1509 NULL --ATTR_UNIT_OF_MEASURE
1510 ,
1511 NULL);
1512 WHEN 'C_EXT_ATTR25' THEN
1513 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1514 attributes_coll(attrcntr)
1515 .attr_name,
1516 p_uda_data_rec.c_ext_attr25 --ATTR_VALUE_STR
1517 ,
1518 NULL --ATTR_VALUE_NUM
1519 ,
1520 NULL --ATTR_VALUE_DATE
1521 ,
1522 NULL --ATTR_DISP_VALUE
1523 ,
1524 NULL --ATTR_UNIT_OF_MEASURE
1525 ,
1526 NULL);
1527 WHEN 'C_EXT_ATTR26' THEN
1528 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1529 attributes_coll(attrcntr)
1530 .attr_name,
1531 p_uda_data_rec.c_ext_attr26 --ATTR_VALUE_STR
1532 ,
1533 NULL --ATTR_VALUE_NUM
1534 ,
1535 NULL --ATTR_VALUE_DATE
1536 ,
1537 NULL --ATTR_DISP_VALUE
1538 ,
1539 NULL --ATTR_UNIT_OF_MEASURE
1540 ,
1541 NULL);
1542 WHEN 'C_EXT_ATTR27' THEN
1543 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1544 attributes_coll(attrcntr)
1545 .attr_name,
1546 p_uda_data_rec.c_ext_attr27 --ATTR_VALUE_STR
1547 ,
1548 NULL --ATTR_VALUE_NUM
1549 ,
1550 NULL --ATTR_VALUE_DATE
1551 ,
1552 NULL --ATTR_DISP_VALUE
1553 ,
1554 NULL --ATTR_UNIT_OF_MEASURE
1555 ,
1556 NULL);
1557 WHEN 'C_EXT_ATTR28' THEN
1558 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1559 attributes_coll(attrcntr)
1560 .attr_name,
1561 p_uda_data_rec.c_ext_attr28 --ATTR_VALUE_STR
1562 ,
1563 NULL --ATTR_VALUE_NUM
1564 ,
1565 NULL --ATTR_VALUE_DATE
1566 ,
1567 NULL --ATTR_DISP_VALUE
1568 ,
1569 NULL --ATTR_UNIT_OF_MEASURE
1570 ,
1571 NULL);
1572 WHEN 'C_EXT_ATTR29' THEN
1573 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1574 attributes_coll(attrcntr)
1575 .attr_name,
1576 p_uda_data_rec.c_ext_attr29 --ATTR_VALUE_STR
1577 ,
1578 NULL --ATTR_VALUE_NUM
1579 ,
1580 NULL --ATTR_VALUE_DATE
1581 ,
1582 NULL --ATTR_DISP_VALUE
1583 ,
1584 NULL --ATTR_UNIT_OF_MEASURE
1585 ,
1586 NULL);
1587 WHEN 'C_EXT_ATTR30' THEN
1588 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1589 attributes_coll(attrcntr)
1590 .attr_name,
1591 p_uda_data_rec.c_ext_attr30 --ATTR_VALUE_STR
1592 ,
1593 NULL --ATTR_VALUE_NUM
1594 ,
1595 NULL --ATTR_VALUE_DATE
1596 ,
1597 NULL --ATTR_DISP_VALUE
1598 ,
1599 NULL --ATTR_UNIT_OF_MEASURE
1600 ,
1601 NULL);
1602 WHEN 'C_EXT_ATTR31' THEN
1603 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1604 attributes_coll(attrcntr)
1605 .attr_name,
1606 p_uda_data_rec.c_ext_attr31 --ATTR_VALUE_STR
1607 ,
1608 NULL --ATTR_VALUE_NUM
1609 ,
1610 NULL --ATTR_VALUE_DATE
1611 ,
1612 NULL --ATTR_DISP_VALUE
1613 ,
1614 NULL --ATTR_UNIT_OF_MEASURE
1615 ,
1616 NULL);
1617 WHEN 'C_EXT_ATTR32' THEN
1618 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1619 attributes_coll(attrcntr)
1620 .attr_name,
1621 p_uda_data_rec.c_ext_attr32 --ATTR_VALUE_STR
1622 ,
1623 NULL --ATTR_VALUE_NUM
1624 ,
1625 NULL --ATTR_VALUE_DATE
1626 ,
1627 NULL --ATTR_DISP_VALUE
1628 ,
1629 NULL --ATTR_UNIT_OF_MEASURE
1630 ,
1631 NULL);
1632 WHEN 'C_EXT_ATTR33' THEN
1633 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1634 attributes_coll(attrcntr)
1635 .attr_name,
1636 p_uda_data_rec.c_ext_attr33 --ATTR_VALUE_STR
1637 ,
1638 NULL --ATTR_VALUE_NUM
1639 ,
1640 NULL --ATTR_VALUE_DATE
1641 ,
1642 NULL --ATTR_DISP_VALUE
1643 ,
1644 NULL --ATTR_UNIT_OF_MEASURE
1645 ,
1646 NULL);
1647 WHEN 'C_EXT_ATTR34' THEN
1648 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1649 attributes_coll(attrcntr)
1650 .attr_name,
1651 p_uda_data_rec.c_ext_attr34 --ATTR_VALUE_STR
1652 ,
1653 NULL --ATTR_VALUE_NUM
1654 ,
1655 NULL --ATTR_VALUE_DATE
1656 ,
1657 NULL --ATTR_DISP_VALUE
1658 ,
1659 NULL --ATTR_UNIT_OF_MEASURE
1660 ,
1661 NULL);
1662 WHEN 'C_EXT_ATTR35' THEN
1663 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1664 attributes_coll(attrcntr)
1665 .attr_name,
1666 p_uda_data_rec.c_ext_attr35 --ATTR_VALUE_STR
1667 ,
1668 NULL --ATTR_VALUE_NUM
1669 ,
1670 NULL --ATTR_VALUE_DATE
1671 ,
1672 NULL --ATTR_DISP_VALUE
1673 ,
1674 NULL --ATTR_UNIT_OF_MEASURE
1675 ,
1676 NULL);
1677 WHEN 'C_EXT_ATTR36' THEN
1678 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1679 attributes_coll(attrcntr)
1680 .attr_name,
1681 p_uda_data_rec.c_ext_attr36 --ATTR_VALUE_STR
1682 ,
1683 NULL --ATTR_VALUE_NUM
1684 ,
1685 NULL --ATTR_VALUE_DATE
1686 ,
1687 NULL --ATTR_DISP_VALUE
1688 ,
1689 NULL --ATTR_UNIT_OF_MEASURE
1690 ,
1691 NULL);
1692 WHEN 'C_EXT_ATTR37' THEN
1693 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1694 attributes_coll(attrcntr)
1695 .attr_name,
1696 p_uda_data_rec.c_ext_attr37 --ATTR_VALUE_STR
1697 ,
1698 NULL --ATTR_VALUE_NUM
1699 ,
1700 NULL --ATTR_VALUE_DATE
1701 ,
1702 NULL --ATTR_DISP_VALUE
1703 ,
1704 NULL --ATTR_UNIT_OF_MEASURE
1705 ,
1706 NULL);
1707 WHEN 'C_EXT_ATTR38' THEN
1708 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1709 attributes_coll(attrcntr)
1710 .attr_name,
1711 p_uda_data_rec.c_ext_attr38 --ATTR_VALUE_STR
1712 ,
1713 NULL --ATTR_VALUE_NUM
1714 ,
1715 NULL --ATTR_VALUE_DATE
1716 ,
1717 NULL --ATTR_DISP_VALUE
1718 ,
1719 NULL --ATTR_UNIT_OF_MEASURE
1720 ,
1721 NULL);
1722 WHEN 'C_EXT_ATTR39' THEN
1723 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1724 attributes_coll(attrcntr)
1725 .attr_name,
1726 p_uda_data_rec.c_ext_attr39 --ATTR_VALUE_STR
1727 ,
1728 NULL --ATTR_VALUE_NUM
1729 ,
1730 NULL --ATTR_VALUE_DATE
1731 ,
1732 NULL --ATTR_DISP_VALUE
1733 ,
1734 NULL --ATTR_UNIT_OF_MEASURE
1735 ,
1736 NULL);
1737 WHEN 'C_EXT_ATTR40' THEN
1738 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1739 attributes_coll(attrcntr)
1740 .attr_name,
1741 p_uda_data_rec.c_ext_attr40 --ATTR_VALUE_STR
1742 ,
1743 NULL --ATTR_VALUE_NUM
1744 ,
1745 NULL --ATTR_VALUE_DATE
1746 ,
1747 NULL --ATTR_DISP_VALUE
1748 ,
1749 NULL --ATTR_UNIT_OF_MEASURE
1750 ,
1751 NULL);
1752
1753 /* Process Numeric attributes */
1754
1755 WHEN 'N_EXT_ATTR1' THEN
1756 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1757 attributes_coll(attrcntr)
1758 .attr_name,
1759 NULL --ATTR_VALUE_STR
1760 ,
1761 p_uda_data_rec.n_ext_attr1 --ATTR_VALUE_NUM
1762 ,
1763 NULL --ATTR_VALUE_DATE
1764 ,
1765 NULL --ATTR_DISP_VALUE
1766 ,
1767 NULL --ATTR_UNIT_OF_MEASURE
1768 ,
1769 NULL);
1770 WHEN 'N_EXT_ATTR2' THEN
1771 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1772 attributes_coll(attrcntr)
1773 .attr_name,
1774 NULL --ATTR_VALUE_STR
1775 ,
1776 p_uda_data_rec.n_ext_attr2 --ATTR_VALUE_NUM
1777 ,
1778 NULL --ATTR_VALUE_DATE
1779 ,
1780 NULL --ATTR_DISP_VALUE
1781 ,
1782 NULL --ATTR_UNIT_OF_MEASURE
1783 ,
1784 NULL);
1785 WHEN 'N_EXT_ATTR3' THEN
1786 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1787 attributes_coll(attrcntr)
1788 .attr_name,
1789 NULL --ATTR_VALUE_STR
1790 ,
1791 p_uda_data_rec.n_ext_attr3 --ATTR_VALUE_NUM
1792 ,
1793 NULL --ATTR_VALUE_DATE
1794 ,
1795 NULL --ATTR_DISP_VALUE
1796 ,
1797 NULL --ATTR_UNIT_OF_MEASURE
1798 ,
1799 NULL);
1800 WHEN 'N_EXT_ATTR4' THEN
1801 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1802 attributes_coll(attrcntr)
1803 .attr_name,
1804 NULL --ATTR_VALUE_STR
1805 ,
1806 p_uda_data_rec.n_ext_attr4 --ATTR_VALUE_NUM
1807 ,
1808 NULL --ATTR_VALUE_DATE
1809 ,
1810 NULL --ATTR_DISP_VALUE
1811 ,
1812 NULL --ATTR_UNIT_OF_MEASURE
1813 ,
1814 NULL);
1815 WHEN 'N_EXT_ATTR5' THEN
1816 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1817 attributes_coll(attrcntr)
1818 .attr_name,
1819 NULL --ATTR_VALUE_STR
1820 ,
1821 p_uda_data_rec.n_ext_attr5 --ATTR_VALUE_NUM
1822 ,
1823 NULL --ATTR_VALUE_DATE
1824 ,
1825 NULL --ATTR_DISP_VALUE
1826 ,
1827 NULL --ATTR_UNIT_OF_MEASURE
1828 ,
1829 NULL);
1830 WHEN 'N_EXT_ATTR6' THEN
1831 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1832 attributes_coll(attrcntr)
1833 .attr_name,
1834 NULL --ATTR_VALUE_STR
1835 ,
1836 p_uda_data_rec.n_ext_attr6 --ATTR_VALUE_NUM
1837 ,
1838 NULL --ATTR_VALUE_DATE
1839 ,
1840 NULL --ATTR_DISP_VALUE
1841 ,
1842 NULL --ATTR_UNIT_OF_MEASURE
1843 ,
1844 NULL);
1845 WHEN 'N_EXT_ATTR7' THEN
1846 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1847 attributes_coll(attrcntr)
1848 .attr_name,
1849 NULL --ATTR_VALUE_STR
1850 ,
1851 p_uda_data_rec.n_ext_attr7 --ATTR_VALUE_NUM
1852 ,
1853 NULL --ATTR_VALUE_DATE
1854 ,
1855 NULL --ATTR_DISP_VALUE
1856 ,
1857 NULL --ATTR_UNIT_OF_MEASURE
1858 ,
1859 NULL);
1860 WHEN 'N_EXT_ATTR8' THEN
1861 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1862 attributes_coll(attrcntr)
1863 .attr_name,
1864 NULL --ATTR_VALUE_STR
1865 ,
1866 p_uda_data_rec.n_ext_attr8 --ATTR_VALUE_NUM
1867 ,
1868 NULL --ATTR_VALUE_DATE
1869 ,
1870 NULL --ATTR_DISP_VALUE
1871 ,
1872 NULL --ATTR_UNIT_OF_MEASURE
1873 ,
1874 NULL);
1875
1876 WHEN 'N_EXT_ATTR9' THEN
1877 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1878 attributes_coll(attrcntr)
1879 .attr_name,
1880 NULL --ATTR_VALUE_STR
1881 ,
1882 p_uda_data_rec.n_ext_attr9 --ATTR_VALUE_NUM
1883 ,
1884 NULL --ATTR_VALUE_DATE
1885 ,
1886 NULL --ATTR_DISP_VALUE
1887 ,
1888 NULL --ATTR_UNIT_OF_MEASURE
1889 ,
1890 NULL);
1891 WHEN 'N_EXT_ATTR10' THEN
1892 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1893 attributes_coll(attrcntr)
1894 .attr_name,
1895 NULL --ATTR_VALUE_STR
1896 ,
1897 p_uda_data_rec.n_ext_attr10 --ATTR_VALUE_NUM
1898 ,
1899 NULL --ATTR_VALUE_DATE
1900 ,
1901 NULL --ATTR_DISP_VALUE
1902 ,
1903 NULL --ATTR_UNIT_OF_MEASURE
1904 ,
1905 NULL);
1906 WHEN 'N_EXT_ATTR11' THEN
1907 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1908 attributes_coll(attrcntr)
1909 .attr_name,
1910 NULL --ATTR_VALUE_STR
1911 ,
1912 p_uda_data_rec.n_ext_attr11 --ATTR_VALUE_NUM
1913 ,
1914 NULL --ATTR_VALUE_DATE
1915 ,
1916 NULL --ATTR_DISP_VALUE
1917 ,
1918 NULL --ATTR_UNIT_OF_MEASURE
1919 ,
1920 NULL);
1921 WHEN 'N_EXT_ATTR12' THEN
1922 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1923 attributes_coll(attrcntr)
1924 .attr_name,
1925 NULL --ATTR_VALUE_STR
1926 ,
1927 p_uda_data_rec.n_ext_attr12 --ATTR_VALUE_NUM
1928 ,
1929 NULL --ATTR_VALUE_DATE
1930 ,
1931 NULL --ATTR_DISP_VALUE
1932 ,
1933 NULL --ATTR_UNIT_OF_MEASURE
1934 ,
1935 NULL);
1936 WHEN 'N_EXT_ATTR13' THEN
1937 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1938 attributes_coll(attrcntr)
1939 .attr_name,
1940 NULL --ATTR_VALUE_STR
1941 ,
1942 p_uda_data_rec.n_ext_attr13 --ATTR_VALUE_NUM
1943 ,
1944 NULL --ATTR_VALUE_DATE
1945 ,
1946 NULL --ATTR_DISP_VALUE
1947 ,
1948 NULL --ATTR_UNIT_OF_MEASURE
1949 ,
1950 NULL);
1951 WHEN 'N_EXT_ATTR14' THEN
1952 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1953 attributes_coll(attrcntr)
1954 .attr_name,
1955 NULL --ATTR_VALUE_STR
1956 ,
1957 p_uda_data_rec.n_ext_attr14 --ATTR_VALUE_NUM
1958 ,
1959 NULL --ATTR_VALUE_DATE
1960 ,
1961 NULL --ATTR_DISP_VALUE
1962 ,
1963 NULL --ATTR_UNIT_OF_MEASURE
1964 ,
1965 NULL);
1966 WHEN 'N_EXT_ATTR15' THEN
1967 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1968 attributes_coll(attrcntr)
1969 .attr_name,
1970 NULL --ATTR_VALUE_STR
1971 ,
1972 p_uda_data_rec.n_ext_attr15 --ATTR_VALUE_NUM
1973 ,
1974 NULL --ATTR_VALUE_DATE
1975 ,
1976 NULL --ATTR_DISP_VALUE
1977 ,
1978 NULL --ATTR_UNIT_OF_MEASURE
1979 ,
1980 NULL);
1981 WHEN 'N_EXT_ATTR16' THEN
1982 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1983 attributes_coll(attrcntr)
1984 .attr_name,
1985 NULL --ATTR_VALUE_STR
1986 ,
1987 p_uda_data_rec.n_ext_attr16 --ATTR_VALUE_NUM
1988 ,
1989 NULL --ATTR_VALUE_DATE
1990 ,
1991 NULL --ATTR_DISP_VALUE
1992 ,
1993 NULL --ATTR_UNIT_OF_MEASURE
1994 ,
1995 NULL);
1996 WHEN 'N_EXT_ATTR17' THEN
1997 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
1998 attributes_coll(attrcntr)
1999 .attr_name,
2000 NULL --ATTR_VALUE_STR
2001 ,
2002 p_uda_data_rec.n_ext_attr17 --ATTR_VALUE_NUM
2003 ,
2004 NULL --ATTR_VALUE_DATE
2005 ,
2006 NULL --ATTR_DISP_VALUE
2007 ,
2008 NULL --ATTR_UNIT_OF_MEASURE
2009 ,
2010 NULL);
2011 WHEN 'N_EXT_ATTR18' THEN
2012 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2013 attributes_coll(attrcntr)
2014 .attr_name,
2015 NULL --ATTR_VALUE_STR
2016 ,
2017 p_uda_data_rec.n_ext_attr18 --ATTR_VALUE_NUM
2018 ,
2019 NULL --ATTR_VALUE_DATE
2020 ,
2021 NULL --ATTR_DISP_VALUE
2022 ,
2023 NULL --ATTR_UNIT_OF_MEASURE
2024 ,
2025 NULL);
2026 WHEN 'N_EXT_ATTR19' THEN
2027 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2028 attributes_coll(attrcntr)
2029 .attr_name,
2030 NULL --ATTR_VALUE_STR
2031 ,
2032 p_uda_data_rec.n_ext_attr19 --ATTR_VALUE_NUM
2033 ,
2034 NULL --ATTR_VALUE_DATE
2035 ,
2036 NULL --ATTR_DISP_VALUE
2037 ,
2038 NULL --ATTR_UNIT_OF_MEASURE
2039 ,
2040 NULL);
2041
2042 WHEN 'N_EXT_ATTR20' THEN
2043 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2044 attributes_coll(attrcntr)
2045 .attr_name,
2046 NULL --ATTR_VALUE_STR
2047 ,
2048 p_uda_data_rec.n_ext_attr20 --ATTR_VALUE_NUM
2049 ,
2050 NULL --ATTR_VALUE_DATE
2051 ,
2052 NULL --ATTR_DISP_VALUE
2053 ,
2054 NULL --ATTR_UNIT_OF_MEASURE
2055 ,
2056 NULL);
2057
2058 /* Process Date attributes */
2059
2060 WHEN 'D_EXT_ATTR1' THEN
2061 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2062 attributes_coll(attrcntr)
2063 .attr_name,
2064 NULL --ATTR_VALUE_STR
2065 ,
2066 NULL --ATTR_VALUE_NUM
2067 ,
2068 p_uda_data_rec.d_ext_attr1 --ATTR_VALUE_DATE
2069 ,
2070 NULL --ATTR_DISP_VALUE
2071 ,
2072 NULL --ATTR_UNIT_OF_MEASURE
2073 ,
2074 NULL);
2075 WHEN 'D_EXT_ATTR2' THEN
2076 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2077 attributes_coll(attrcntr)
2078 .attr_name,
2079 NULL --ATTR_VALUE_STR
2080 ,
2081 NULL --ATTR_VALUE_NUM
2082 ,
2083 p_uda_data_rec.d_ext_attr2 --ATTR_VALUE_DATE
2084 ,
2085 NULL --ATTR_DISP_VALUE
2086 ,
2087 NULL --ATTR_UNIT_OF_MEASURE
2088 ,
2089 NULL);
2090 WHEN 'D_EXT_ATTR3' THEN
2091 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2092 attributes_coll(attrcntr)
2093 .attr_name,
2094 NULL --ATTR_VALUE_STR
2095 ,
2096 NULL --ATTR_VALUE_NUM
2097 ,
2098 p_uda_data_rec.d_ext_attr3 --ATTR_VALUE_DATE
2099 ,
2100 NULL --ATTR_DISP_VALUE
2101 ,
2102 NULL --ATTR_UNIT_OF_MEASURE
2103 ,
2104 NULL);
2105 WHEN 'D_EXT_ATTR4' THEN
2106 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2107 attributes_coll(attrcntr)
2108 .attr_name,
2109 NULL --ATTR_VALUE_STR
2110 ,
2111 NULL --ATTR_VALUE_NUM
2112 ,
2113 p_uda_data_rec.d_ext_attr4 --ATTR_VALUE_DATE
2114 ,
2115 NULL --ATTR_DISP_VALUE
2116 ,
2117 NULL --ATTR_UNIT_OF_MEASURE
2118 ,
2119 NULL);
2120 WHEN 'D_EXT_ATTR5' THEN
2121 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2122 attributes_coll(attrcntr)
2123 .attr_name,
2124 NULL --ATTR_VALUE_STR
2125 ,
2126 NULL --ATTR_VALUE_NUM
2127 ,
2128 p_uda_data_rec.d_ext_attr5 --ATTR_VALUE_DATE
2129 ,
2130 NULL --ATTR_DISP_VALUE
2131 ,
2132 NULL --ATTR_UNIT_OF_MEASURE
2133 ,
2134 NULL);
2135 WHEN 'D_EXT_ATTR6' THEN
2136 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2137 attributes_coll(attrcntr)
2138 .attr_name,
2139 NULL --ATTR_VALUE_STR
2140 ,
2141 NULL --ATTR_VALUE_NUM
2142 ,
2143 p_uda_data_rec.d_ext_attr6 --ATTR_VALUE_DATE
2144 ,
2145 NULL --ATTR_DISP_VALUE
2146 ,
2147 NULL --ATTR_UNIT_OF_MEASURE
2148 ,
2149 NULL);
2150 WHEN 'D_EXT_ATTR7' THEN
2151 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2152 attributes_coll(attrcntr)
2153 .attr_name,
2154 NULL --ATTR_VALUE_STR
2155 ,
2156 NULL --ATTR_VALUE_NUM
2157 ,
2158 p_uda_data_rec.d_ext_attr7 --ATTR_VALUE_DATE
2159 ,
2160 NULL --ATTR_DISP_VALUE
2161 ,
2162 NULL --ATTR_UNIT_OF_MEASURE
2163 ,
2164 NULL);
2165 WHEN 'D_EXT_ATTR8' THEN
2166 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2167 attributes_coll(attrcntr)
2168 .attr_name,
2169 NULL --ATTR_VALUE_STR
2170 ,
2171 NULL --ATTR_VALUE_NUM
2172 ,
2173 p_uda_data_rec.d_ext_attr8 --ATTR_VALUE_DATE
2174 ,
2175 NULL --ATTR_DISP_VALUE
2176 ,
2177 NULL --ATTR_UNIT_OF_MEASURE
2178 ,
2179 NULL);
2180
2181 WHEN 'D_EXT_ATTR9' THEN
2182 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2183 attributes_coll(attrcntr)
2184 .attr_name,
2185 NULL --ATTR_VALUE_STR
2186 ,
2187 NULL --ATTR_VALUE_NUM
2188 ,
2189 p_uda_data_rec.d_ext_attr9 --ATTR_VALUE_DATE
2190 ,
2191 NULL --ATTR_DISP_VALUE
2192 ,
2193 NULL --ATTR_UNIT_OF_MEASURE
2194 ,
2195 NULL);
2196
2197 WHEN 'D_EXT_ATTR10' THEN
2198 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2199 attributes_coll(attrcntr)
2200 .attr_name,
2201 NULL --ATTR_VALUE_STR
2202 ,
2203 NULL --ATTR_VALUE_NUM
2204 ,
2205 p_uda_data_rec.d_ext_attr10 --ATTR_VALUE_DATE
2206 ,
2207 NULL --ATTR_DISP_VALUE
2208 ,
2209 NULL --ATTR_UNIT_OF_MEASURE
2210 ,
2211 NULL);
2212
2213 /* Process UOM attributes */
2214
2215 WHEN 'UOM_EXT_ATTR1' THEN
2216 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2217 attributes_coll(attrcntr)
2218 .attr_name,
2219 NULL --ATTR_VALUE_STR
2220 ,
2221 NULL --ATTR_VALUE_NUM
2222 ,
2223 NULL --ATTR_VALUE_DATE
2224 ,
2225 NULL --ATTR_DISP_VALUE
2226 ,
2227 p_uda_data_rec.uom_ext_attr1,
2228 NULL);
2229 WHEN 'UOM_EXT_ATTR2' THEN
2230 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2231 attributes_coll(attrcntr)
2232 .attr_name,
2233 NULL --ATTR_VALUE_STR
2234 ,
2235 NULL --ATTR_VALUE_NUM
2236 ,
2237 NULL --ATTR_VALUE_DATE
2238 ,
2239 NULL --ATTR_DISP_VALUE
2240 ,
2241 p_uda_data_rec.uom_ext_attr2,
2242 NULL);
2243 WHEN 'UOM_EXT_ATTR3' THEN
2244 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2245 attributes_coll(attrcntr)
2246 .attr_name,
2247 NULL --ATTR_VALUE_STR
2248 ,
2249 NULL --ATTR_VALUE_NUM
2250 ,
2251 NULL --ATTR_VALUE_DATE
2252 ,
2253 NULL --ATTR_DISP_VALUE
2254 ,
2255 p_uda_data_rec.uom_ext_attr3,
2256 NULL);
2257 WHEN 'UOM_EXT_ATTR4' THEN
2258 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2259 attributes_coll(attrcntr)
2260 .attr_name,
2261 NULL --ATTR_VALUE_STR
2262 ,
2263 NULL --ATTR_VALUE_NUM
2264 ,
2265 NULL --ATTR_VALUE_DATE
2266 ,
2267 NULL --ATTR_DISP_VALUE
2268 ,
2269 p_uda_data_rec.uom_ext_attr4,
2270 NULL);
2271 WHEN 'UOM_EXT_ATTR5' THEN
2272 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2273 attributes_coll(attrcntr)
2274 .attr_name,
2275 NULL --ATTR_VALUE_STR
2276 ,
2277 NULL --ATTR_VALUE_NUM
2278 ,
2279 NULL --ATTR_VALUE_DATE
2280 ,
2281 NULL --ATTR_DISP_VALUE
2282 ,
2283 p_uda_data_rec.uom_ext_attr5,
2284 NULL);
2285 WHEN 'UOM_EXT_ATTR6' THEN
2286 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2287 attributes_coll(attrcntr)
2288 .attr_name,
2289 NULL --ATTR_VALUE_STR
2290 ,
2291 NULL --ATTR_VALUE_NUM
2292 ,
2293 NULL --ATTR_VALUE_DATE
2294 ,
2295 NULL --ATTR_DISP_VALUE
2296 ,
2297 p_uda_data_rec.uom_ext_attr6,
2298 NULL);
2299 WHEN 'UOM_EXT_ATTR7' THEN
2300 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2301 attributes_coll(attrcntr)
2302 .attr_name,
2303 NULL --ATTR_VALUE_STR
2304 ,
2305 NULL --ATTR_VALUE_NUM
2306 ,
2307 NULL --ATTR_VALUE_DATE
2308 ,
2309 NULL --ATTR_DISP_VALUE
2310 ,
2311 p_uda_data_rec.uom_ext_attr7,
2312 NULL);
2313 WHEN 'UOM_EXT_ATTR8' THEN
2314 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2315 attributes_coll(attrcntr)
2316 .attr_name,
2317 NULL --ATTR_VALUE_STR
2318 ,
2319 NULL --ATTR_VALUE_NUM
2320 ,
2321 NULL --ATTR_VALUE_DATE
2322 ,
2323 NULL --ATTR_DISP_VALUE
2324 ,
2325 p_uda_data_rec.uom_ext_attr8,
2326 NULL);
2327 WHEN 'UOM_EXT_ATTR9' THEN
2328 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2329 attributes_coll(attrcntr)
2330 .attr_name,
2331 NULL --ATTR_VALUE_STR
2332 ,
2333 NULL --ATTR_VALUE_NUM
2334 ,
2335 NULL --ATTR_VALUE_DATE
2336 ,
2337 NULL --ATTR_DISP_VALUE
2338 ,
2339 p_uda_data_rec.uom_ext_attr9,
2340 NULL);
2341
2342 WHEN 'UOM_EXT_ATTR10' THEN
2343 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2344 attributes_coll(attrcntr)
2345 .attr_name,
2346 NULL --ATTR_VALUE_STR
2347 ,
2348 NULL --ATTR_VALUE_NUM
2349 ,
2350 NULL --ATTR_VALUE_DATE
2351 ,
2352 NULL --ATTR_DISP_VALUE
2353 ,
2354 p_uda_data_rec.uom_ext_attr10,
2355 NULL);
2356 WHEN 'UOM_EXT_ATTR11' THEN
2357 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2358 attributes_coll(attrcntr)
2359 .attr_name,
2360 NULL --ATTR_VALUE_STR
2361 ,
2362 NULL --ATTR_VALUE_NUM
2363 ,
2364 NULL --ATTR_VALUE_DATE
2365 ,
2366 NULL --ATTR_DISP_VALUE
2367 ,
2368 p_uda_data_rec.uom_ext_attr11,
2369 NULL);
2370 WHEN 'UOM_EXT_ATTR12' THEN
2371 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2372 attributes_coll(attrcntr)
2373 .attr_name,
2374 NULL --ATTR_VALUE_STR
2375 ,
2376 NULL --ATTR_VALUE_NUM
2377 ,
2378 NULL --ATTR_VALUE_DATE
2379 ,
2380 NULL --ATTR_DISP_VALUE
2381 ,
2382 p_uda_data_rec.uom_ext_attr12,
2383 NULL);
2384 WHEN 'UOM_EXT_ATTR13' THEN
2385 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2386 attributes_coll(attrcntr)
2387 .attr_name,
2388 NULL --ATTR_VALUE_STR
2389 ,
2390 NULL --ATTR_VALUE_NUM
2391 ,
2392 NULL --ATTR_VALUE_DATE
2393 ,
2394 NULL --ATTR_DISP_VALUE
2395 ,
2396 p_uda_data_rec.uom_ext_attr13,
2397 NULL);
2398 WHEN 'UOM_EXT_ATTR14' THEN
2399 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2400 attributes_coll(attrcntr)
2401 .attr_name,
2402 NULL --ATTR_VALUE_STR
2403 ,
2404 NULL --ATTR_VALUE_NUM
2405 ,
2406 NULL --ATTR_VALUE_DATE
2407 ,
2408 NULL --ATTR_DISP_VALUE
2409 ,
2410 p_uda_data_rec.uom_ext_attr14,
2411 NULL);
2412 WHEN 'UOM_EXT_ATTR15' THEN
2413 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2414 attributes_coll(attrcntr)
2415 .attr_name,
2416 NULL --ATTR_VALUE_STR
2417 ,
2418 NULL --ATTR_VALUE_NUM
2419 ,
2420 NULL --ATTR_VALUE_DATE
2421 ,
2422 NULL --ATTR_DISP_VALUE
2423 ,
2424 p_uda_data_rec.uom_ext_attr15,
2425 NULL);
2426 WHEN 'UOM_EXT_ATTR16' THEN
2427 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2428 attributes_coll(attrcntr)
2429 .attr_name,
2430 NULL --ATTR_VALUE_STR
2431 ,
2432 NULL --ATTR_VALUE_NUM
2433 ,
2434 NULL --ATTR_VALUE_DATE
2435 ,
2436 NULL --ATTR_DISP_VALUE
2437 ,
2438 p_uda_data_rec.uom_ext_attr16,
2439 NULL);
2440 WHEN 'UOM_EXT_ATTR17' THEN
2441 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2442 attributes_coll(attrcntr)
2443 .attr_name,
2444 NULL --ATTR_VALUE_STR
2445 ,
2446 NULL --ATTR_VALUE_NUM
2447 ,
2448 NULL --ATTR_VALUE_DATE
2449 ,
2450 NULL --ATTR_DISP_VALUE
2451 ,
2452 p_uda_data_rec.uom_ext_attr17,
2453 NULL);
2454 WHEN 'UOM_EXT_ATTR18' THEN
2455 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2456 attributes_coll(attrcntr)
2457 .attr_name,
2458 NULL --ATTR_VALUE_STR
2459 ,
2460 NULL --ATTR_VALUE_NUM
2461 ,
2462 NULL --ATTR_VALUE_DATE
2463 ,
2464 NULL --ATTR_DISP_VALUE
2465 ,
2466 p_uda_data_rec.uom_ext_attr18,
2467 NULL);
2468 WHEN 'UOM_EXT_ATTR19' THEN
2469 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2470 attributes_coll(attrcntr)
2471 .attr_name,
2472 NULL --ATTR_VALUE_STR
2473 ,
2474 NULL --ATTR_VALUE_NUM
2475 ,
2476 NULL --ATTR_VALUE_DATE
2477 ,
2478 NULL --ATTR_DISP_VALUE
2479 ,
2480 p_uda_data_rec.uom_ext_attr19,
2481 NULL);
2482 WHEN 'UOM_EXT_ATTR20' THEN
2483 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2484 attributes_coll(attrcntr)
2485 .attr_name,
2486 NULL --ATTR_VALUE_STR
2487 ,
2488 NULL --ATTR_VALUE_NUM
2489 ,
2490 NULL --ATTR_VALUE_DATE
2491 ,
2492 NULL --ATTR_DISP_VALUE
2493 ,
2494 p_uda_data_rec.uom_ext_attr20,
2495 NULL);
2496
2497 ELSE
2498 x_attributes_data_table(attrcntr) := ego_user_attr_data_obj(p_row_identifier,
2499 attributes_coll(attrcntr)
2500 .attr_name,
2501 NULL --ATTR_VALUE_STR
2502 ,
2503 NULL --ATTR_VALUE_NUM
2504 ,
2505 NULL --ATTR_VALUE_DATE
2506 ,
2507 NULL --ATTR_DISP_VALUE
2508 ,
2509 NULL,
2510 NULL);
2511
2512 END CASE;
2513 x_attributes_data_table.extend;
2514 END LOOP;
2515 x_attributes_data_table.trim;
2516
2517 /*EXCEPTION
2518 WHEN OTHERS THEN
2519 x_return_status := fnd_api.g_ret_sts_unexp_error;*/
2520 END build_uda_data_payload;
2521
2522 PROCEDURE do_single_row_uda_merge
2523 (
2524 p_uda_rec_tbl IN OUT NOCOPY uda_rec_tbl_type,
2525 x_uda_rec OUT NOCOPY pos_supp_prof_ext_b%ROWTYPE,
2526 x_return_status OUT NOCOPY VARCHAR2,
2527 x_msg_count OUT NOCOPY NUMBER,
2528 x_msg_data OUT NOCOPY VARCHAR2
2529 ) IS
2530
2531 BEGIN
2532 x_return_status := fnd_api.g_ret_sts_success;
2533
2534 /* The collection contains the UDA data in the ascending
2535 order of the creation date. The idea is to have the
2536 latest data in the final merged record. So we would
2537 start with the index 2 in the collection and merge it
2538 with the index 1 record and repeat the same until we
2539 reach the last record in the collection. We would use
2540 NVL on the attributes to ensure that if the any record has a NULL
2541 value for a n attribute it does not override the attribute
2542 containing the data even if for an older date*/
2543
2544 FOR cntr IN 2 .. p_uda_rec_tbl.count LOOP
2545
2546 /* Merge character extension attributes */
2547
2548 p_uda_rec_tbl(1).c_ext_attr1 := nvl(p_uda_rec_tbl(cntr).c_ext_attr1,
2549 p_uda_rec_tbl(1).c_ext_attr1);
2550 p_uda_rec_tbl(1).c_ext_attr2 := nvl(p_uda_rec_tbl(cntr).c_ext_attr2,
2551 p_uda_rec_tbl(1).c_ext_attr2);
2552 p_uda_rec_tbl(1).c_ext_attr3 := nvl(p_uda_rec_tbl(cntr).c_ext_attr3,
2553 p_uda_rec_tbl(1).c_ext_attr3);
2554 p_uda_rec_tbl(1).c_ext_attr4 := nvl(p_uda_rec_tbl(cntr).c_ext_attr4,
2555 p_uda_rec_tbl(1).c_ext_attr4);
2556 p_uda_rec_tbl(1).c_ext_attr5 := nvl(p_uda_rec_tbl(cntr).c_ext_attr5,
2557 p_uda_rec_tbl(1).c_ext_attr5);
2558 p_uda_rec_tbl(1).c_ext_attr6 := nvl(p_uda_rec_tbl(cntr).c_ext_attr6,
2559 p_uda_rec_tbl(1).c_ext_attr6);
2560 p_uda_rec_tbl(1).c_ext_attr7 := nvl(p_uda_rec_tbl(cntr).c_ext_attr7,
2561 p_uda_rec_tbl(1).c_ext_attr7);
2562 p_uda_rec_tbl(1).c_ext_attr8 := nvl(p_uda_rec_tbl(cntr).c_ext_attr8,
2563 p_uda_rec_tbl(1).c_ext_attr8);
2564 p_uda_rec_tbl(1).c_ext_attr9 := nvl(p_uda_rec_tbl(cntr).c_ext_attr9,
2565 p_uda_rec_tbl(1).c_ext_attr9);
2566 p_uda_rec_tbl(1).c_ext_attr10 := nvl(p_uda_rec_tbl(cntr).c_ext_attr10,
2567 p_uda_rec_tbl(1).c_ext_attr10);
2568
2569 p_uda_rec_tbl(1).c_ext_attr11 := nvl(p_uda_rec_tbl(cntr).c_ext_attr11,
2570 p_uda_rec_tbl(1).c_ext_attr11);
2571 p_uda_rec_tbl(1).c_ext_attr12 := nvl(p_uda_rec_tbl(cntr).c_ext_attr12,
2572 p_uda_rec_tbl(1).c_ext_attr12);
2573 p_uda_rec_tbl(1).c_ext_attr13 := nvl(p_uda_rec_tbl(cntr).c_ext_attr13,
2574 p_uda_rec_tbl(1).c_ext_attr13);
2575 p_uda_rec_tbl(1).c_ext_attr14 := nvl(p_uda_rec_tbl(cntr).c_ext_attr14,
2576 p_uda_rec_tbl(1).c_ext_attr14);
2577 p_uda_rec_tbl(1).c_ext_attr15 := nvl(p_uda_rec_tbl(cntr).c_ext_attr15,
2578 p_uda_rec_tbl(1).c_ext_attr15);
2579 p_uda_rec_tbl(1).c_ext_attr16 := nvl(p_uda_rec_tbl(cntr).c_ext_attr16,
2580 p_uda_rec_tbl(1).c_ext_attr16);
2581 p_uda_rec_tbl(1).c_ext_attr17 := nvl(p_uda_rec_tbl(cntr).c_ext_attr17,
2582 p_uda_rec_tbl(1).c_ext_attr17);
2583 p_uda_rec_tbl(1).c_ext_attr18 := nvl(p_uda_rec_tbl(cntr).c_ext_attr18,
2584 p_uda_rec_tbl(1).c_ext_attr18);
2585 p_uda_rec_tbl(1).c_ext_attr19 := nvl(p_uda_rec_tbl(cntr).c_ext_attr19,
2586 p_uda_rec_tbl(1).c_ext_attr19);
2587 p_uda_rec_tbl(1).c_ext_attr20 := nvl(p_uda_rec_tbl(cntr).c_ext_attr20,
2588 p_uda_rec_tbl(1).c_ext_attr20);
2589
2590 p_uda_rec_tbl(1).c_ext_attr21 := nvl(p_uda_rec_tbl(cntr).c_ext_attr21,
2591 p_uda_rec_tbl(1).c_ext_attr21);
2592 p_uda_rec_tbl(1).c_ext_attr22 := nvl(p_uda_rec_tbl(cntr).c_ext_attr22,
2593 p_uda_rec_tbl(1).c_ext_attr22);
2594 p_uda_rec_tbl(1).c_ext_attr23 := nvl(p_uda_rec_tbl(cntr).c_ext_attr23,
2595 p_uda_rec_tbl(1).c_ext_attr23);
2596 p_uda_rec_tbl(1).c_ext_attr24 := nvl(p_uda_rec_tbl(cntr).c_ext_attr24,
2597 p_uda_rec_tbl(1).c_ext_attr24);
2598 p_uda_rec_tbl(1).c_ext_attr25 := nvl(p_uda_rec_tbl(cntr).c_ext_attr25,
2599 p_uda_rec_tbl(1).c_ext_attr25);
2600 p_uda_rec_tbl(1).c_ext_attr26 := nvl(p_uda_rec_tbl(cntr).c_ext_attr26,
2601 p_uda_rec_tbl(1).c_ext_attr26);
2602 p_uda_rec_tbl(1).c_ext_attr27 := nvl(p_uda_rec_tbl(cntr).c_ext_attr27,
2603 p_uda_rec_tbl(1).c_ext_attr27);
2604 p_uda_rec_tbl(1).c_ext_attr28 := nvl(p_uda_rec_tbl(cntr).c_ext_attr28,
2605 p_uda_rec_tbl(1).c_ext_attr28);
2606 p_uda_rec_tbl(1).c_ext_attr29 := nvl(p_uda_rec_tbl(cntr).c_ext_attr29,
2607 p_uda_rec_tbl(1).c_ext_attr29);
2608 p_uda_rec_tbl(1).c_ext_attr30 := nvl(p_uda_rec_tbl(cntr).c_ext_attr30,
2609 p_uda_rec_tbl(1).c_ext_attr30);
2610
2611 p_uda_rec_tbl(1).c_ext_attr31 := nvl(p_uda_rec_tbl(cntr).c_ext_attr31,
2612 p_uda_rec_tbl(1).c_ext_attr31);
2613 p_uda_rec_tbl(1).c_ext_attr32 := nvl(p_uda_rec_tbl(cntr).c_ext_attr32,
2614 p_uda_rec_tbl(1).c_ext_attr32);
2615 p_uda_rec_tbl(1).c_ext_attr33 := nvl(p_uda_rec_tbl(cntr).c_ext_attr33,
2616 p_uda_rec_tbl(1).c_ext_attr33);
2617 p_uda_rec_tbl(1).c_ext_attr34 := nvl(p_uda_rec_tbl(cntr).c_ext_attr34,
2618 p_uda_rec_tbl(1).c_ext_attr34);
2619 p_uda_rec_tbl(1).c_ext_attr35 := nvl(p_uda_rec_tbl(cntr).c_ext_attr35,
2620 p_uda_rec_tbl(1).c_ext_attr35);
2621 p_uda_rec_tbl(1).c_ext_attr36 := nvl(p_uda_rec_tbl(cntr).c_ext_attr36,
2622 p_uda_rec_tbl(1).c_ext_attr36);
2623 p_uda_rec_tbl(1).c_ext_attr37 := nvl(p_uda_rec_tbl(cntr).c_ext_attr37,
2624 p_uda_rec_tbl(1).c_ext_attr37);
2625 p_uda_rec_tbl(1).c_ext_attr38 := nvl(p_uda_rec_tbl(cntr).c_ext_attr38,
2626 p_uda_rec_tbl(1).c_ext_attr38);
2627 p_uda_rec_tbl(1).c_ext_attr39 := nvl(p_uda_rec_tbl(cntr).c_ext_attr39,
2628 p_uda_rec_tbl(1).c_ext_attr39);
2629 p_uda_rec_tbl(1).c_ext_attr40 := nvl(p_uda_rec_tbl(cntr).c_ext_attr40,
2630 p_uda_rec_tbl(1).c_ext_attr40);
2631
2632 /* Merge numeric extension attributes */
2633
2634 p_uda_rec_tbl(1).n_ext_attr1 := nvl(p_uda_rec_tbl(cntr).n_ext_attr1,
2635 p_uda_rec_tbl(1).n_ext_attr1);
2636 p_uda_rec_tbl(1).n_ext_attr2 := nvl(p_uda_rec_tbl(cntr).n_ext_attr2,
2637 p_uda_rec_tbl(1).n_ext_attr2);
2638 p_uda_rec_tbl(1).n_ext_attr3 := nvl(p_uda_rec_tbl(cntr).n_ext_attr3,
2639 p_uda_rec_tbl(1).n_ext_attr3);
2640 p_uda_rec_tbl(1).n_ext_attr4 := nvl(p_uda_rec_tbl(cntr).n_ext_attr4,
2641 p_uda_rec_tbl(1).n_ext_attr4);
2642 p_uda_rec_tbl(1).n_ext_attr5 := nvl(p_uda_rec_tbl(cntr).n_ext_attr5,
2643 p_uda_rec_tbl(1).n_ext_attr5);
2644 p_uda_rec_tbl(1).n_ext_attr6 := nvl(p_uda_rec_tbl(cntr).n_ext_attr6,
2645 p_uda_rec_tbl(1).n_ext_attr6);
2646 p_uda_rec_tbl(1).n_ext_attr7 := nvl(p_uda_rec_tbl(cntr).n_ext_attr7,
2647 p_uda_rec_tbl(1).n_ext_attr7);
2648 p_uda_rec_tbl(1).n_ext_attr8 := nvl(p_uda_rec_tbl(cntr).n_ext_attr8,
2649 p_uda_rec_tbl(1).n_ext_attr8);
2650 p_uda_rec_tbl(1).n_ext_attr9 := nvl(p_uda_rec_tbl(cntr).n_ext_attr9,
2651 p_uda_rec_tbl(1).n_ext_attr9);
2652 p_uda_rec_tbl(1).n_ext_attr10 := nvl(p_uda_rec_tbl(cntr).n_ext_attr10,
2653 p_uda_rec_tbl(1).n_ext_attr10);
2654
2655 p_uda_rec_tbl(1).n_ext_attr11 := nvl(p_uda_rec_tbl(cntr).n_ext_attr11,
2656 p_uda_rec_tbl(1).n_ext_attr11);
2657 p_uda_rec_tbl(1).n_ext_attr12 := nvl(p_uda_rec_tbl(cntr).n_ext_attr12,
2658 p_uda_rec_tbl(1).n_ext_attr12);
2659 p_uda_rec_tbl(1).n_ext_attr13 := nvl(p_uda_rec_tbl(cntr).n_ext_attr13,
2660 p_uda_rec_tbl(1).n_ext_attr13);
2661 p_uda_rec_tbl(1).n_ext_attr14 := nvl(p_uda_rec_tbl(cntr).n_ext_attr14,
2662 p_uda_rec_tbl(1).n_ext_attr14);
2663 p_uda_rec_tbl(1).n_ext_attr15 := nvl(p_uda_rec_tbl(cntr).n_ext_attr15,
2664 p_uda_rec_tbl(1).n_ext_attr15);
2665 p_uda_rec_tbl(1).n_ext_attr16 := nvl(p_uda_rec_tbl(cntr).n_ext_attr16,
2666 p_uda_rec_tbl(1).n_ext_attr16);
2667 p_uda_rec_tbl(1).n_ext_attr17 := nvl(p_uda_rec_tbl(cntr).n_ext_attr17,
2668 p_uda_rec_tbl(1).n_ext_attr17);
2669 p_uda_rec_tbl(1).n_ext_attr18 := nvl(p_uda_rec_tbl(cntr).n_ext_attr18,
2670 p_uda_rec_tbl(1).n_ext_attr18);
2671 p_uda_rec_tbl(1).n_ext_attr19 := nvl(p_uda_rec_tbl(cntr).n_ext_attr19,
2672 p_uda_rec_tbl(1).n_ext_attr19);
2673 p_uda_rec_tbl(1).n_ext_attr20 := nvl(p_uda_rec_tbl(cntr).n_ext_attr20,
2674 p_uda_rec_tbl(1).n_ext_attr20);
2675
2676 /* Merge date extension attributes */
2677
2678 p_uda_rec_tbl(1).d_ext_attr1 := nvl(p_uda_rec_tbl(cntr).d_ext_attr1,
2679 p_uda_rec_tbl(1).d_ext_attr1);
2680 p_uda_rec_tbl(1).d_ext_attr2 := nvl(p_uda_rec_tbl(cntr).d_ext_attr2,
2681 p_uda_rec_tbl(1).d_ext_attr2);
2682 p_uda_rec_tbl(1).d_ext_attr3 := nvl(p_uda_rec_tbl(cntr).d_ext_attr3,
2683 p_uda_rec_tbl(1).d_ext_attr3);
2684 p_uda_rec_tbl(1).d_ext_attr4 := nvl(p_uda_rec_tbl(cntr).d_ext_attr4,
2685 p_uda_rec_tbl(1).d_ext_attr4);
2686 p_uda_rec_tbl(1).d_ext_attr5 := nvl(p_uda_rec_tbl(cntr).d_ext_attr5,
2687 p_uda_rec_tbl(1).d_ext_attr5);
2688 p_uda_rec_tbl(1).d_ext_attr6 := nvl(p_uda_rec_tbl(cntr).d_ext_attr6,
2689 p_uda_rec_tbl(1).d_ext_attr6);
2690 p_uda_rec_tbl(1).d_ext_attr7 := nvl(p_uda_rec_tbl(cntr).d_ext_attr7,
2691 p_uda_rec_tbl(1).d_ext_attr7);
2692 p_uda_rec_tbl(1).d_ext_attr8 := nvl(p_uda_rec_tbl(cntr).d_ext_attr8,
2693 p_uda_rec_tbl(1).d_ext_attr8);
2694 p_uda_rec_tbl(1).d_ext_attr9 := nvl(p_uda_rec_tbl(cntr).d_ext_attr9,
2695 p_uda_rec_tbl(1).d_ext_attr9);
2696 p_uda_rec_tbl(1).d_ext_attr10 := nvl(p_uda_rec_tbl(cntr).d_ext_attr10,
2697 p_uda_rec_tbl(1).d_ext_attr10);
2698
2699 /* Merge UOM extension attributes */
2700
2701 p_uda_rec_tbl(1).uom_ext_attr1 := nvl(p_uda_rec_tbl(cntr)
2702 .uom_ext_attr1,
2703 p_uda_rec_tbl(1).uom_ext_attr1);
2704 p_uda_rec_tbl(1).uom_ext_attr2 := nvl(p_uda_rec_tbl(cntr)
2705 .uom_ext_attr2,
2706 p_uda_rec_tbl(1).uom_ext_attr2);
2707 p_uda_rec_tbl(1).uom_ext_attr3 := nvl(p_uda_rec_tbl(cntr)
2708 .uom_ext_attr3,
2709 p_uda_rec_tbl(1).uom_ext_attr3);
2710 p_uda_rec_tbl(1).uom_ext_attr4 := nvl(p_uda_rec_tbl(cntr)
2711 .uom_ext_attr4,
2712 p_uda_rec_tbl(1).uom_ext_attr4);
2713 p_uda_rec_tbl(1).uom_ext_attr5 := nvl(p_uda_rec_tbl(cntr)
2714 .uom_ext_attr5,
2715 p_uda_rec_tbl(1).uom_ext_attr5);
2716 p_uda_rec_tbl(1).uom_ext_attr6 := nvl(p_uda_rec_tbl(cntr)
2717 .uom_ext_attr6,
2718 p_uda_rec_tbl(1).uom_ext_attr6);
2719 p_uda_rec_tbl(1).uom_ext_attr7 := nvl(p_uda_rec_tbl(cntr)
2720 .uom_ext_attr7,
2721 p_uda_rec_tbl(1).uom_ext_attr7);
2722 p_uda_rec_tbl(1).uom_ext_attr8 := nvl(p_uda_rec_tbl(cntr)
2723 .uom_ext_attr8,
2724 p_uda_rec_tbl(1).uom_ext_attr8);
2725 p_uda_rec_tbl(1).uom_ext_attr9 := nvl(p_uda_rec_tbl(cntr)
2726 .uom_ext_attr9,
2727 p_uda_rec_tbl(1).uom_ext_attr9);
2728 p_uda_rec_tbl(1).uom_ext_attr10 := nvl(p_uda_rec_tbl(cntr)
2729 .uom_ext_attr10,
2730 p_uda_rec_tbl(1).uom_ext_attr10);
2731
2732 p_uda_rec_tbl(1).uom_ext_attr11 := nvl(p_uda_rec_tbl(cntr)
2733 .uom_ext_attr11,
2734 p_uda_rec_tbl(1).uom_ext_attr11);
2735 p_uda_rec_tbl(1).uom_ext_attr12 := nvl(p_uda_rec_tbl(cntr)
2736 .uom_ext_attr12,
2737 p_uda_rec_tbl(1).uom_ext_attr12);
2738 p_uda_rec_tbl(1).uom_ext_attr13 := nvl(p_uda_rec_tbl(cntr)
2739 .uom_ext_attr13,
2740 p_uda_rec_tbl(1).uom_ext_attr13);
2741 p_uda_rec_tbl(1).uom_ext_attr14 := nvl(p_uda_rec_tbl(cntr)
2742 .uom_ext_attr14,
2743 p_uda_rec_tbl(1).uom_ext_attr14);
2744 p_uda_rec_tbl(1).uom_ext_attr15 := nvl(p_uda_rec_tbl(cntr)
2745 .uom_ext_attr15,
2746 p_uda_rec_tbl(1).uom_ext_attr15);
2747 p_uda_rec_tbl(1).uom_ext_attr16 := nvl(p_uda_rec_tbl(cntr)
2748 .uom_ext_attr16,
2749 p_uda_rec_tbl(1).uom_ext_attr16);
2750 p_uda_rec_tbl(1).uom_ext_attr17 := nvl(p_uda_rec_tbl(cntr)
2751 .uom_ext_attr17,
2752 p_uda_rec_tbl(1).uom_ext_attr17);
2753 p_uda_rec_tbl(1).uom_ext_attr18 := nvl(p_uda_rec_tbl(cntr)
2754 .uom_ext_attr18,
2755 p_uda_rec_tbl(1).uom_ext_attr18);
2756 p_uda_rec_tbl(1).uom_ext_attr19 := nvl(p_uda_rec_tbl(cntr)
2757 .uom_ext_attr19,
2758 p_uda_rec_tbl(1).uom_ext_attr19);
2759 p_uda_rec_tbl(1).uom_ext_attr20 := nvl(p_uda_rec_tbl(cntr)
2760 .uom_ext_attr20,
2761 p_uda_rec_tbl(1).uom_ext_attr20);
2762
2763 END LOOP;
2764 x_uda_rec := p_uda_rec_tbl(1);
2765
2766 EXCEPTION
2767 WHEN OTHERS THEN
2768 x_return_status := fnd_api.g_ret_sts_unexp_error;
2769 END do_single_row_uda_merge;
2770
2771 PROCEDURE do_uda_row_dml
2772 (
2773 p_attribute_group_id IN NUMBER,
2774 p_data_level_id IN NUMBER,
2775 p_attribute_group_type IN VARCHAR2,
2776 p_attribute_group_name IN VARCHAR2,
2777 p_to_party_id IN NUMBER,
2778 p_mode IN VARCHAR2,
2779 p_uda_rec_tbl IN uda_rec_tbl_type,
2780 p_class_code IN VARCHAR2,
2781 p_vendor_id IN NUMBER DEFAULT NULL,
2782 x_return_status OUT NOCOPY VARCHAR2,
2783 x_msg_count OUT NOCOPY NUMBER,
2784 x_msg_data OUT NOCOPY VARCHAR2
2785 ) IS
2786 l_request_table ego_attr_group_request_table := ego_attr_group_request_table(NULL);
2787 l_pk_column_values ego_col_name_value_pair_array;
2788 l_class_code_name_value_pairs ego_col_name_value_pair_array := ego_col_name_value_pair_array(NULL);
2789 l_party_id NUMBER;
2790 l_party_site_id NUMBER;
2791
2792 l_attributes_row_table ego_user_attr_row_table := ego_user_attr_row_table(NULL);
2793 l_attributes_data_table ego_user_attr_data_table := ego_user_attr_data_table(NULL);
2794 l_all_attributes_data_table ego_user_attr_data_table := ego_user_attr_data_table();
2795
2796 l_attributes_row_table2 ego_user_attr_row_table := ego_user_attr_row_table(NULL);
2797 l_attributes_data_table2 ego_user_attr_data_table := ego_user_attr_data_table(NULL);
2798 l_all_attributes_data_table2 ego_user_attr_data_table := ego_user_attr_data_table();
2799
2800 l_row_identifier NUMBER;
2801 l_uda_rec_tbl uda_rec_tbl_type;
2802
2803 l_uda_data_rec pos_supp_prof_ext_b%ROWTYPE;
2804 l_failed_row_id_buffer VARCHAR2(1000);
2805
2806 l_return_status VARCHAR2(2000);
2807 l_msg_count NUMBER;
2808 l_msg_data VARCHAR2(100);
2809 l_errorcode NUMBER;
2810 l_error_msg_tbl error_handler.error_tbl_type;
2811 where_clause VARCHAR2(5000);
2812
2813 TYPE cursor_ref_type IS REF CURSOR;
2814 l_pscur cursor_ref_type;
2815 l_sql VARCHAR2(4000) := NULL;
2816 l_ext1 NUMBER;
2817 l_count NUMBER;
2818
2819 CURSOR attr_unique_key IS
2820 SELECT database_column
2821 FROM ego_attrs_v
2822 WHERE application_id = 177
2823 AND attr_group_name = p_attribute_group_name
2824 AND attr_group_type = p_attribute_group_type
2825 AND unique_key_flag = 'Y';
2826
2827 TYPE attr_unique_key_tab IS TABLE OF attr_unique_key%ROWTYPE INDEX BY BINARY_INTEGER;
2828 attr_unique_key_coll attr_unique_key_tab;
2829
2830 key_vals_str VARCHAR2(2000);
2831 key_col_val VARCHAR2(2000);
2832
2833 TYPE key_cols_array_tab_typ IS TABLE OF VARCHAR2(1000) INDEX BY VARCHAR2(2000);
2834 key_cols_array_tab key_cols_array_tab_typ;
2835
2836 key_combination_exists BOOLEAN := FALSE;
2837
2838 BEGIN
2839 x_return_status := fnd_api.g_ret_sts_success;
2840 l_row_identifier := 1020;
2841
2842 /* Build the Primary key Name value pairs */
2843 IF (p_data_level_id = 17701) THEN
2844
2845 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
2846 to_char(p_to_party_id)));
2847 ELSIF (p_data_level_id = 17702) THEN
2848 BEGIN
2849 SELECT party_id
2850 INTO l_party_id
2851 FROM hz_party_sites
2852 WHERE party_site_id = p_to_party_id;
2853
2854 EXCEPTION
2855 WHEN no_data_found THEN
2856 fnd_file.put_line(fnd_file.log,
2857 'No party id found for party site id : ' ||
2858 p_to_party_id);
2859 x_return_status := fnd_api.g_ret_sts_error;
2860 RETURN;
2861 END;
2862 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
2863 to_char(l_party_id)));
2864
2865 ELSIF (p_data_level_id = 17703) THEN
2866 BEGIN
2867 SELECT party_id
2868 INTO l_party_id
2869 FROM ap_suppliers
2870 WHERE vendor_id = p_vendor_id;
2871
2872 EXCEPTION
2873 WHEN no_data_found THEN
2874 fnd_file.put_line(fnd_file.log,
2875 'No party id found for vendor id : ' ||
2876 p_vendor_id);
2877 x_return_status := fnd_api.g_ret_sts_error;
2878 RETURN;
2879 END;
2880
2881 BEGIN
2882 SELECT party_site_id
2883 INTO l_party_site_id
2884 FROM ap_supplier_sites_all
2885 WHERE vendor_site_id = p_to_party_id;
2886
2887 EXCEPTION
2888 WHEN no_data_found THEN
2889 fnd_file.put_line(fnd_file.log,
2890 'No party site id found for vendor site id : ' ||
2891 p_to_party_id);
2892 x_return_status := fnd_api.g_ret_sts_error;
2893 RETURN;
2894 END;
2895 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
2896 to_char(l_party_id)));
2897 END IF;
2898
2899 /* Build the Attribute group Object */
2900 l_request_table(l_request_table.last) := ego_attr_group_request_obj(p_attribute_group_id,
2901 NULL, -- application id
2902 NULL, -- group type
2903 NULL, -- group name
2904 'SUPP_LEVEL', -- data level
2905 '''N''', -- DATA_LEVEL_1
2906 NULL, -- DATA_LEVEL_2
2907 NULL, -- DATA_LEVEL_3
2908 NULL, -- DATA_LEVEL_4
2909 NULL, -- DATA_LEVEL_5
2910 NULL -- ATTR_NAME_LIST
2911 );
2912 OPEN attr_unique_key;
2913 FETCH attr_unique_key BULK COLLECT
2914 INTO attr_unique_key_coll;
2915 CLOSE attr_unique_key;
2916
2917 FOR datacntr IN 1 .. p_uda_rec_tbl.count LOOP
2918 FOR keycntr IN 1 .. attr_unique_key_coll.count LOOP
2919 where_clause := where_clause || ' AND pos1.' || attr_unique_key_coll(keycntr)
2920 .database_column || ' = pos2.' || attr_unique_key_coll(keycntr)
2921 .database_column;
2922
2923 CASE attr_unique_key_coll(keycntr).database_column
2924
2925 /* Process Character attributes */
2926
2927 WHEN 'C_EXT_ATTR1' THEN
2928 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr1;
2929
2930 WHEN 'C_EXT_ATTR2' THEN
2931 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr2;
2932 WHEN 'C_EXT_ATTR3' THEN
2933 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr3;
2934 WHEN 'C_EXT_ATTR4' THEN
2935 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr4;
2936 WHEN 'C_EXT_ATTR5' THEN
2937 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr5;
2938 WHEN 'C_EXT_ATTR6' THEN
2939 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr6;
2940 WHEN 'C_EXT_ATTR7' THEN
2941 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr7;
2942 WHEN 'C_EXT_ATTR8' THEN
2943 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr8;
2944 WHEN 'C_EXT_ATTR9' THEN
2945 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr9;
2946 WHEN 'C_EXT_ATTR10' THEN
2947 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr10;
2948 WHEN 'C_EXT_ATTR11' THEN
2949 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr11;
2950 WHEN 'C_EXT_ATTR12' THEN
2951 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr12;
2952 WHEN 'C_EXT_ATTR13' THEN
2953 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr13;
2954 WHEN 'C_EXT_ATTR14' THEN
2955 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr14;
2956 WHEN 'C_EXT_ATTR15' THEN
2957 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr15;
2958 WHEN 'C_EXT_ATTR16' THEN
2959 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr16;
2960 WHEN 'C_EXT_ATTR17' THEN
2961 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr17;
2962 WHEN 'C_EXT_ATTR18' THEN
2963 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr18;
2964 WHEN 'C_EXT_ATTR19' THEN
2965 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr19;
2966 WHEN 'C_EXT_ATTR20' THEN
2967 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr20;
2968 WHEN 'C_EXT_ATTR21' THEN
2969 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr21;
2970 WHEN 'C_EXT_ATTR22' THEN
2971 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr22;
2972 WHEN 'C_EXT_ATTR23' THEN
2973 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr23;
2974 WHEN 'C_EXT_ATTR24' THEN
2975 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr24;
2976 WHEN 'C_EXT_ATTR25' THEN
2977 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr25;
2978 WHEN 'C_EXT_ATTR26' THEN
2979 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr26;
2980 WHEN 'C_EXT_ATTR27' THEN
2981 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr27;
2982 WHEN 'C_EXT_ATTR28' THEN
2983 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr28;
2984 WHEN 'C_EXT_ATTR29' THEN
2985 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr29;
2986 WHEN 'C_EXT_ATTR30' THEN
2987 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr30;
2988 WHEN 'C_EXT_ATTR31' THEN
2989 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr31;
2990 WHEN 'C_EXT_ATTR32' THEN
2991 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr32;
2992 WHEN 'C_EXT_ATTR33' THEN
2993 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr33;
2994 WHEN 'C_EXT_ATTR34' THEN
2995 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr34;
2996 WHEN 'C_EXT_ATTR35' THEN
2997 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr35;
2998 WHEN 'C_EXT_ATTR36' THEN
2999 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr36;
3000 WHEN 'C_EXT_ATTR37' THEN
3001 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr37;
3002 WHEN 'C_EXT_ATTR38' THEN
3003 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr38;
3004 WHEN 'C_EXT_ATTR39' THEN
3005 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr39;
3006 WHEN 'C_EXT_ATTR40' THEN
3007 key_col_val := p_uda_rec_tbl(datacntr).c_ext_attr40;
3008
3009 /* Process Numeric attributes */
3010
3011 WHEN 'N_EXT_ATTR1' THEN
3012 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr1;
3013 WHEN 'N_EXT_ATTR2' THEN
3014 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr2;
3015 WHEN 'N_EXT_ATTR3' THEN
3016 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr3;
3017 WHEN 'N_EXT_ATTR4' THEN
3018 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr4;
3019 WHEN 'N_EXT_ATTR5' THEN
3020 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr5;
3021 WHEN 'N_EXT_ATTR6' THEN
3022 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr6;
3023 WHEN 'N_EXT_ATTR7' THEN
3024 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr7;
3025 WHEN 'N_EXT_ATTR8' THEN
3026 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr8;
3027 WHEN 'N_EXT_ATTR9' THEN
3028 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr9;
3029 WHEN 'N_EXT_ATTR10' THEN
3030 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr10;
3031 WHEN 'N_EXT_ATTR11' THEN
3032 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr11;
3033 WHEN 'N_EXT_ATTR12' THEN
3034 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr12;
3035 WHEN 'N_EXT_ATTR13' THEN
3036 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr13;
3037 WHEN 'N_EXT_ATTR14' THEN
3038 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr14;
3039 WHEN 'N_EXT_ATTR15' THEN
3040 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr15;
3041 WHEN 'N_EXT_ATTR16' THEN
3042 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr16;
3043 WHEN 'N_EXT_ATTR17' THEN
3044 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr17;
3045 WHEN 'N_EXT_ATTR18' THEN
3046 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr18;
3047 WHEN 'N_EXT_ATTR19' THEN
3048 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr19;
3049 WHEN 'N_EXT_ATTR20' THEN
3050 key_col_val := p_uda_rec_tbl(datacntr).n_ext_attr20;
3051
3052 /* Process Date attributes */
3053
3054 WHEN 'D_EXT_ATTR1' THEN
3055 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr1;
3056 WHEN 'D_EXT_ATTR2' THEN
3057 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr2;
3058 WHEN 'D_EXT_ATTR3' THEN
3059 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr3;
3060 WHEN 'D_EXT_ATTR4' THEN
3061 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr4;
3062 WHEN 'D_EXT_ATTR5' THEN
3063 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr5;
3064 WHEN 'D_EXT_ATTR6' THEN
3065 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr6;
3066 WHEN 'D_EXT_ATTR7' THEN
3067 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr7;
3068 WHEN 'D_EXT_ATTR8' THEN
3069 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr8;
3070 WHEN 'D_EXT_ATTR9' THEN
3071 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr9;
3072 WHEN 'D_EXT_ATTR10' THEN
3073 key_col_val := p_uda_rec_tbl(datacntr).d_ext_attr10;
3074
3075 /* Process UOM attributes */
3076
3077 WHEN 'UOM_EXT_ATTR1' THEN
3078 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr1;
3079 WHEN 'UOM_EXT_ATTR2' THEN
3080 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr2;
3081 WHEN 'UOM_EXT_ATTR3' THEN
3082 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr3;
3083 WHEN 'UOM_EXT_ATTR4' THEN
3084 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr4;
3085 WHEN 'UOM_EXT_ATTR5' THEN
3086 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr5;
3087 WHEN 'UOM_EXT_ATTR6' THEN
3088 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr6;
3089 WHEN 'UOM_EXT_ATTR7' THEN
3090 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr7;
3091 WHEN 'UOM_EXT_ATTR8' THEN
3092 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr8;
3093 WHEN 'UOM_EXT_ATTR9' THEN
3094 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr9;
3095 WHEN 'UOM_EXT_ATTR10' THEN
3096 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr10;
3097 WHEN 'UOM_EXT_ATTR11' THEN
3098 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr11;
3099 WHEN 'UOM_EXT_ATTR12' THEN
3100 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr12;
3101 WHEN 'UOM_EXT_ATTR13' THEN
3102 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr13;
3103 WHEN 'UOM_EXT_ATTR14' THEN
3104 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr14;
3105 WHEN 'UOM_EXT_ATTR15' THEN
3106 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr15;
3107 WHEN 'UOM_EXT_ATTR16' THEN
3108 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr16;
3109 WHEN 'UOM_EXT_ATTR17' THEN
3110 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr17;
3111 WHEN 'UOM_EXT_ATTR18' THEN
3112 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr18;
3113 WHEN 'UOM_EXT_ATTR19' THEN
3114 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr19;
3115 WHEN 'UOM_EXT_ATTR20' THEN
3116 key_col_val := p_uda_rec_tbl(datacntr).uom_ext_attr20;
3117 ELSE
3118 key_col_val := NULL;
3119 END CASE;
3120
3121 key_vals_str := key_col_val || '-' || key_vals_str;
3122 END LOOP;
3123
3124 IF NOT key_cols_array_tab.exists(key_vals_str) THEN
3125 key_cols_array_tab(key_vals_str) := 1;
3126 key_combination_exists := FALSE;
3127 ELSE
3128 key_combination_exists := TRUE;
3129 END IF;
3130 key_vals_str := NULL;
3131 key_col_val := NULL;
3132
3133 IF (p_data_level_id = 17701) THEN
3134 l_sql := 'SELECT pos1.extension_id to_ext_id
3135 FROM pos_supp_prof_ext_b pos1,
3136 pos_supp_prof_ext_b pos2
3137 WHERE pos1.attr_group_id = ' || p_attribute_group_id ||
3138 ' AND pos1.data_level_id = ' || p_data_level_id ||
3139 ' AND pos1.attr_group_id = pos2.attr_group_id
3140 AND pos1.data_level_id = pos2.data_level_id
3141 AND pos2.party_id = ' || p_uda_rec_tbl(datacntr)
3142 .party_id || ' AND pos2.extension_id = ' || p_uda_rec_tbl(datacntr)
3143 .extension_id || ' AND pos1.party_id = ' ||
3144 p_to_party_id || where_clause;
3145
3146 ELSIF (p_data_level_id = 17702) THEN
3147 l_sql := 'SELECT pos1.extension_id to_ext_id
3148 FROM pos_supp_prof_ext_b pos1,
3149 pos_supp_prof_ext_b pos2
3150 WHERE pos1.attr_group_id = ' || p_attribute_group_id ||
3151 ' AND pos1.data_level_id = ' || p_data_level_id ||
3152 ' AND pos1.attr_group_id = pos2.attr_group_id
3153 AND pos1.data_level_id = pos2.data_level_id
3154 AND pos2.party_id = ' || p_uda_rec_tbl(datacntr)
3155 .party_id || ' AND pos2.extension_id = ' || p_uda_rec_tbl(datacntr)
3156 .extension_id || ' AND pos2.pk1_value = ' || p_uda_rec_tbl(datacntr)
3157 .pk1_value || ' AND pos1.pk1_value = ' || p_to_party_id ||
3158 where_clause;
3159
3160 ELSIF (p_data_level_id = 17703) THEN
3161 l_sql := 'SELECT pos1.extension_id to_ext_id
3162 FROM pos_supp_prof_ext_b pos1,
3163 pos_supp_prof_ext_b pos2
3164 WHERE pos1.attr_group_id = ' || p_attribute_group_id ||
3165 ' AND pos1.data_level_id = ' || p_data_level_id ||
3166 ' AND pos1.attr_group_id = pos2.attr_group_id
3167 AND pos1.data_level_id = pos2.data_level_id
3168 AND pos2.party_id = ' || p_uda_rec_tbl(datacntr)
3169 .party_id || ' AND pos2.extension_id = ' || p_uda_rec_tbl(datacntr)
3170 .extension_id || ' AND pos2.pk2_value = ' || p_uda_rec_tbl(datacntr)
3171 .pk2_value || ' AND pos1.pk2_value = ' || p_to_party_id ||
3172 where_clause;
3173 END IF;
3174
3175 l_ext1 := NULL;
3176
3177 OPEN l_pscur FOR l_sql;
3178 FETCH l_pscur
3179 INTO l_ext1;
3180 CLOSE l_pscur;
3181
3182 IF (l_ext1 IS NOT NULL) THEN
3183 IF key_combination_exists = FALSE THEN
3184 -- update multirow suchita
3185 IF (p_data_level_id = 17701) THEN
3186 l_sql := 'SELECT *
3187 FROM pos_supp_prof_ext_b
3188 WHERE party_id = ' || p_uda_rec_tbl(datacntr)
3189 .party_id || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
3190 .extension_id ||
3191 ' UNION ALL
3192 SELECT *
3193 FROM pos_supp_prof_ext_b
3194 WHERE party_id = ' ||
3195 p_to_party_id || ' AND extension_id = ' || l_ext1;
3196
3197 ELSIF (p_data_level_id = 17702) THEN
3198 l_sql := 'SELECT *
3199 FROM pos_supp_prof_ext_b
3200 WHERE pk1_value = ' || p_uda_rec_tbl(datacntr)
3201 .pk1_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
3202 .extension_id ||
3203 ' UNION ALL
3204 SELECT *
3205 FROM pos_supp_prof_ext_b
3206 WHERE pk1_value = ' ||
3207 p_to_party_id || ' AND extension_id = ' || l_ext1;
3208
3209 ELSIF (p_data_level_id = 17703) THEN
3210 l_sql := 'SELECT *
3211 FROM pos_supp_prof_ext_b
3212 WHERE pk2_value = ' || p_uda_rec_tbl(datacntr)
3213 .pk2_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
3214 .extension_id ||
3215 ' UNION ALL
3216 SELECT *
3217 FROM pos_supp_prof_ext_b
3218 WHERE pk2_value = ' ||
3219 p_to_party_id || ' AND extension_id = ' || l_ext1;
3220 END IF;
3221 ELSE
3222 -- update multirow suchita
3223 IF (p_data_level_id = 17701) THEN
3224 l_sql := 'SELECT *
3225 FROM pos_supp_prof_ext_b
3226 WHERE party_id = ' || p_to_party_id ||
3227 ' AND extension_id = ' || l_ext1 ||
3228 'UNION ALL
3229 SELECT *
3230 FROM pos_supp_prof_ext_b
3231 WHERE party_id = ' || p_uda_rec_tbl(datacntr)
3232 .party_id || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
3233 .extension_id;
3234
3235 ELSIF (p_data_level_id = 17702) THEN
3236 l_sql := 'SELECT *
3237 FROM pos_supp_prof_ext_b
3238 WHERE pk1_value = ' ||
3239 p_to_party_id || ' AND extension_id = ' || l_ext1 ||
3240 ' UNION ALL
3241 SELECT *
3242 FROM pos_supp_prof_ext_b
3243 WHERE pk1_value = ' || p_uda_rec_tbl(datacntr)
3244 .pk1_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
3245 .extension_id;
3246
3247 ELSIF (p_data_level_id = 17703) THEN
3248 l_sql := 'SELECT *
3249 FROM pos_supp_prof_ext_b
3250 WHERE pk2_value = ' ||
3251 p_to_party_id || ' AND extension_id = ' || l_ext1 ||
3252 ' UNION ALL
3253 SELECT *
3254 FROM pos_supp_prof_ext_b
3255 WHERE pk2_value = ' || p_uda_rec_tbl(datacntr)
3256 .pk2_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
3257 .extension_id;
3258 END IF;
3259
3260 END IF;
3261
3262 OPEN l_pscur FOR l_sql;
3263 FETCH l_pscur BULK COLLECT
3264 INTO l_uda_rec_tbl;
3265 CLOSE l_pscur;
3266
3267 do_single_row_uda_merge(l_uda_rec_tbl,
3268 l_uda_data_rec,
3269 l_return_status,
3270 l_msg_count,
3271 l_msg_data);
3272
3273 /* Build the Row Object */
3274 IF (p_data_level_id = 17701) THEN
3275 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3276 p_attribute_group_id,
3277 177,
3278 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3279 p_attribute_group_name, --p_attribute_group_name,
3280 'SUPP_LEVEL', -- data level
3281 'N',
3282 NULL,
3283 NULL,
3284 NULL,
3285 NULL,
3286 ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
3287 );
3288 ELSIF (p_data_level_id = 17702) THEN
3289 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3290 p_attribute_group_id,
3291 177,
3292 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3293 p_attribute_group_name, --p_attribute_group_name,
3294 'SUPP_ADDR_LEVEL', -- data level
3295 'N',
3296 p_to_party_id,
3297 NULL,
3298 NULL,
3299 NULL,
3300 ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
3301 );
3302 ELSIF (p_data_level_id = 17703) THEN
3303 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3304 p_attribute_group_id,
3305 177,
3306 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3307 p_attribute_group_name, --p_attribute_group_name,
3308 'SUPP_ADDR_SITE_LEVEL', -- data level
3309 'N',
3310 l_party_site_id,
3311 p_to_party_id,
3312 NULL,
3313 NULL,
3314 ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
3315 );
3316 END IF;
3317
3318 /* Build the data payload object */
3319 l_attributes_data_table := ego_user_attr_data_table(NULL);
3320
3321 build_uda_data_payload(p_attribute_group_id => p_attribute_group_id,
3322 p_attribute_group_name => p_attribute_group_name,
3323 p_attribute_group_type => p_attribute_group_type,
3324 p_row_identifier => l_row_identifier,
3325 p_uda_data_rec => l_uda_data_rec,
3326 x_attributes_data_table => l_attributes_data_table);
3327
3328 /*l_all_attributes_data_table := l_all_attributes_data_table MULTISET
3329 UNION l_attributes_data_table;*/
3330
3331 l_count := l_all_attributes_data_table.count;
3332
3333 FOR i IN 1 .. l_attributes_data_table.count LOOP
3334 l_all_attributes_data_table.extend;
3335 l_all_attributes_data_table(l_count + i) := ego_user_attr_data_obj(l_attributes_data_table(i)
3336 .row_identifier,
3337 l_attributes_data_table(i)
3338 .attr_name,
3339 l_attributes_data_table(i)
3340 .attr_value_str,
3341 l_attributes_data_table(i)
3342 .attr_value_num,
3343 l_attributes_data_table(i)
3344 .attr_value_date,
3345 l_attributes_data_table(i)
3346 .attr_disp_value,
3347 l_attributes_data_table(i)
3348 .attr_unit_of_measure,
3349 l_attributes_data_table(i)
3350 .user_row_identifier);
3351 END LOOP;
3352
3353 ELSE
3354 /* Build the Row Object */
3355 IF (p_data_level_id = 17701) THEN
3356 l_attributes_row_table2(l_attributes_row_table2.last) := ego_user_attr_row_obj(l_row_identifier,
3357 p_attribute_group_id,
3358 177,
3359 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3360 p_attribute_group_name, --p_attribute_group_name,
3361 'SUPP_LEVEL', -- data level
3362 'N',
3363 NULL,
3364 NULL,
3365 NULL,
3366 NULL,
3367 ego_user_attrs_data_pvt.g_create_mode --TRANSACTION_TYPE
3368 );
3369 ELSIF (p_data_level_id = 17702) THEN
3370 l_attributes_row_table2(l_attributes_row_table2.last) := ego_user_attr_row_obj(l_row_identifier,
3371 p_attribute_group_id,
3372 177,
3373 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3374 p_attribute_group_name, --p_attribute_group_name,
3375 'SUPP_ADDR_LEVEL', -- data level
3376 'N',
3377 p_to_party_id,
3378 NULL,
3379 NULL,
3380 NULL,
3381 ego_user_attrs_data_pvt.g_create_mode --TRANSACTION_TYPE
3382 );
3383 ELSIF (p_data_level_id = 17703) THEN
3384 l_attributes_row_table2(l_attributes_row_table2.last) := ego_user_attr_row_obj(l_row_identifier,
3385 p_attribute_group_id,
3386 177,
3387 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3388 p_attribute_group_name, --p_attribute_group_name,
3389 'SUPP_ADDR_SITE_LEVEL', -- data level
3390 'N',
3391 l_party_site_id,
3392 p_to_party_id,
3393 NULL,
3394 NULL,
3395 ego_user_attrs_data_pvt.g_create_mode --TRANSACTION_TYPE
3396 );
3397 END IF;
3398
3399 /* Build the Data Object */
3400 l_uda_data_rec := p_uda_rec_tbl(datacntr);
3401 l_attributes_data_table2 := ego_user_attr_data_table(NULL);
3402
3403 build_uda_data_payload(p_attribute_group_id => p_attribute_group_id,
3404 p_attribute_group_name => p_attribute_group_name,
3405 p_attribute_group_type => p_attribute_group_type,
3406 p_row_identifier => l_row_identifier,
3407 p_uda_data_rec => l_uda_data_rec,
3408 x_attributes_data_table => l_attributes_data_table2);
3409
3410 /*l_all_attributes_data_table2 := l_all_attributes_data_table2
3411 MULTISET UNION
3412 l_attributes_data_table2;*/
3413
3414 l_count := l_all_attributes_data_table2.count;
3415
3416 FOR i IN 1 .. l_attributes_data_table2.count LOOP
3417 l_all_attributes_data_table2.extend;
3418 l_all_attributes_data_table2(l_count + i) := ego_user_attr_data_obj(l_attributes_data_table2(i)
3419 .row_identifier,
3420 l_attributes_data_table2(i)
3421 .attr_name,
3422 l_attributes_data_table2(i)
3423 .attr_value_str,
3424 l_attributes_data_table2(i)
3425 .attr_value_num,
3426 l_attributes_data_table2(i)
3427 .attr_value_date,
3428 l_attributes_data_table2(i)
3429 .attr_disp_value,
3430 l_attributes_data_table2(i)
3431 .attr_unit_of_measure,
3432 l_attributes_data_table2(i)
3433 .user_row_identifier);
3434 END LOOP;
3435
3436 l_class_code_name_value_pairs := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('CLASSIFICATION_CODE',
3437 p_class_code));
3438
3439 ego_user_attrs_data_pub.process_user_attrs_data(p_api_version => 1.0,
3440 p_object_name => 'HZ_PARTIES',
3441 p_attributes_row_table => l_attributes_row_table2,
3442 p_attributes_data_table => l_all_attributes_data_table2,
3443 p_pk_column_name_value_pairs => l_pk_column_values,
3444 p_class_code_name_value_pairs => l_class_code_name_value_pairs,
3445 p_entity_id => NULL,
3446 p_entity_index => NULL,
3447 p_entity_code => NULL,
3448 p_debug_level => NULL, --p_debug_level,
3449 p_commit => fnd_api.g_false,
3450 p_init_error_handler => 'T',
3451 p_init_fnd_msg_list => 'T',
3452 x_failed_row_id_list => l_failed_row_id_buffer,
3453 x_return_status => l_return_status,
3454 x_errorcode => l_errorcode,
3455 x_msg_count => l_msg_count,
3456 x_msg_data => l_msg_data);
3457
3458 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3459 error_handler.get_message_list(l_error_msg_tbl);
3460 IF l_error_msg_tbl.first IS NOT NULL THEN
3461 l_msg_count := l_error_msg_tbl.first;
3462 WHILE l_msg_count IS NOT NULL LOOP
3463 fnd_file.put_line(fnd_file.log,
3464 'Error Message: ' || l_error_msg_tbl(l_msg_count)
3465 .message_text ||
3466 ' From ego_user_attrs_data_pub.process_user_attrs_data API.');
3467 l_msg_count := l_error_msg_tbl.next(l_msg_count);
3468 END LOOP;
3469 END IF;
3470 END IF;
3471 l_attributes_row_table2 := ego_user_attr_row_table(NULL);
3472 l_attributes_data_table2 := ego_user_attr_data_table(NULL);
3473 l_all_attributes_data_table2 := ego_user_attr_data_table();
3474 l_attributes_row_table.trim;
3475 END IF;
3476 /* Increment the row identifier */
3477 l_row_identifier := l_row_identifier + 1;
3478 l_attributes_row_table.extend;
3479 END LOOP;
3480
3481 l_attributes_row_table.trim;
3482
3483 l_class_code_name_value_pairs := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('CLASSIFICATION_CODE',
3484 p_class_code));
3485
3486 /* Call the EGO API to process the attributes based on the mode */
3487
3488 ego_user_attrs_data_pub.process_user_attrs_data(p_api_version => 1.0,
3489 p_object_name => 'HZ_PARTIES',
3490 p_attributes_row_table => l_attributes_row_table,
3491 p_attributes_data_table => l_all_attributes_data_table,
3492 p_pk_column_name_value_pairs => l_pk_column_values,
3493 p_class_code_name_value_pairs => l_class_code_name_value_pairs,
3494 p_entity_id => NULL,
3495 p_entity_index => NULL,
3496 p_entity_code => NULL,
3497 p_debug_level => NULL, --p_debug_level,
3498 p_commit => fnd_api.g_false,
3499 p_init_error_handler => 'T',
3500 p_init_fnd_msg_list => 'T',
3501 x_failed_row_id_list => l_failed_row_id_buffer,
3502 x_return_status => l_return_status,
3503 x_errorcode => l_errorcode,
3504 x_msg_count => l_msg_count,
3505 x_msg_data => l_msg_data);
3506
3507 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3508 error_handler.get_message_list(l_error_msg_tbl);
3509 IF l_error_msg_tbl.first IS NOT NULL THEN
3510 l_msg_count := l_error_msg_tbl.first;
3511 WHILE l_msg_count IS NOT NULL LOOP
3512 fnd_file.put_line(fnd_file.log,
3513 'Error Message: ' || l_error_msg_tbl(l_msg_count)
3514 .message_text ||
3515 ' From ego_user_attrs_data_pub.process_user_attrs_data API.');
3516 l_msg_count := l_error_msg_tbl.next(l_msg_count);
3517 END LOOP;
3518 END IF;
3519 END IF;
3520
3521 EXCEPTION
3522 WHEN OTHERS THEN
3523 x_return_status := fnd_api.g_ret_sts_unexp_error;
3524 END do_uda_row_dml;
3525
3526 PROCEDURE do_uda_row_dml
3527 (
3528 p_attribute_group_id IN NUMBER,
3529 p_data_level_id IN NUMBER,
3530 p_attribute_group_type IN VARCHAR2,
3531 p_attribute_group_name IN VARCHAR2,
3532 p_to_party_id IN NUMBER,
3533 p_mode IN VARCHAR2,
3534 p_uda_rec IN pos_supp_prof_ext_b%ROWTYPE,
3535 p_class_code IN VARCHAR2,
3536 p_vendor_id IN NUMBER DEFAULT NULL,
3537 --p_uda_rec_tbl IN uda_rec_tbl_type,
3538 x_return_status OUT NOCOPY VARCHAR2,
3539 x_msg_count OUT NOCOPY NUMBER,
3540 x_msg_data OUT NOCOPY VARCHAR2
3541 ) IS
3542 l_request_table ego_attr_group_request_table := ego_attr_group_request_table(NULL);
3543 l_pk_column_values ego_col_name_value_pair_array;
3544
3545 l_attributes_row_table ego_user_attr_row_table := ego_user_attr_row_table(NULL);
3546 l_attributes_data_table ego_user_attr_data_table := ego_user_attr_data_table(NULL);
3547 l_row_identifier NUMBER;
3548 l_failed_row_id_buffer VARCHAR2(1000);
3549
3550 l_return_status VARCHAR2(2000);
3551 l_msg_count NUMBER;
3552 l_msg_data VARCHAR2(100);
3553 l_errorcode NUMBER;
3554 l_error_msg_tbl error_handler.error_tbl_type;
3555
3556 l_class_code_name_value_pairs ego_col_name_value_pair_array := ego_col_name_value_pair_array(NULL);
3557 l_party_id NUMBER;
3558 l_party_site_id NUMBER;
3559 BEGIN
3560 x_return_status := fnd_api.g_ret_sts_success;
3561 l_row_identifier := 1030;
3562 /* This API works in 2 modes Insert and Update. If run in the update mode then
3563 it would update the UDA data for the to_party_id that is passed.
3564 If run in the insert mode, it would insert the data for the party */
3565
3566 /* Build the Primary key Name value pairs */
3567 IF (p_data_level_id = 17701) THEN
3568
3569 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
3570 to_char(p_to_party_id)));
3571 ELSIF (p_data_level_id = 17702) THEN
3572
3573 BEGIN
3574 SELECT party_id
3575 INTO l_party_id
3576 FROM hz_party_sites
3577 WHERE party_site_id = p_to_party_id;
3578
3579 EXCEPTION
3580 WHEN no_data_found THEN
3581 fnd_file.put_line(fnd_file.log,
3582 'No party id found for party site id : ' ||
3583 p_to_party_id);
3584 x_return_status := fnd_api.g_ret_sts_error;
3585 RETURN;
3586 END;
3587 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
3588 to_char(l_party_id)));
3589
3590 ELSIF (p_data_level_id = 17703) THEN
3591 BEGIN
3592 SELECT party_id
3593 INTO l_party_id
3594 FROM ap_suppliers
3595 WHERE vendor_id = p_vendor_id;
3596
3597 EXCEPTION
3598 WHEN no_data_found THEN
3599 fnd_file.put_line(fnd_file.log,
3600 'No party id found for vendor id : ' ||
3601 p_vendor_id);
3602 x_return_status := fnd_api.g_ret_sts_error;
3603 RETURN;
3604 END;
3605
3606 BEGIN
3607 SELECT party_site_id
3608 INTO l_party_site_id
3609 FROM ap_supplier_sites_all
3610 WHERE vendor_site_id = p_to_party_id;
3611
3612 EXCEPTION
3613 WHEN no_data_found THEN
3614 fnd_file.put_line(fnd_file.log,
3615 'No party site id found for vendor site id : ' ||
3616 p_to_party_id);
3617 x_return_status := fnd_api.g_ret_sts_error;
3618 RETURN;
3619 END;
3620
3621 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
3622 to_char(l_party_id)));
3623 END IF;
3624
3625 /* Build the Attribute group Object */
3626 l_request_table(l_request_table.last) := ego_attr_group_request_obj(p_attribute_group_id,
3627 NULL, -- application id
3628 NULL, -- group type
3629 NULL, -- group name
3630 'SUPP_LEVEL', -- data level
3631 '''N''', -- DATA_LEVEL_1
3632 NULL, -- DATA_LEVEL_2
3633 NULL, -- DATA_LEVEL_3
3634 NULL, -- DATA_LEVEL_4
3635 NULL, -- DATA_LEVEL_5
3636 NULL -- ATTR_NAME_LIST
3637 );
3638
3639 /* Build the data payload object */
3640
3641 IF (p_mode = 'UPDATE') THEN
3642
3643 /* Build the Row Object */
3644 IF (p_data_level_id = 17701) THEN
3645 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3646 p_attribute_group_id,
3647 177,
3648 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3649 p_attribute_group_name, --p_attribute_group_name,
3650 'SUPP_LEVEL', -- data level
3651 'N',
3652 NULL,
3653 NULL,
3654 NULL,
3655 NULL,
3656 ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
3657 );
3658 ELSIF (p_data_level_id = 17702) THEN
3659 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3660 p_attribute_group_id,
3661 177,
3662 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3663 p_attribute_group_name, --p_attribute_group_name,
3664 'SUPP_ADDR_LEVEL', -- data level
3665 'N',
3666 p_to_party_id,
3667 NULL,
3668 NULL,
3669 NULL,
3670 ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
3671 );
3672 ELSIF (p_data_level_id = 17703) THEN
3673 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3674 p_attribute_group_id,
3675 177,
3676 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3677 p_attribute_group_name, --p_attribute_group_name,
3678 'SUPP_ADDR_SITE_LEVEL', -- data level
3679 'N',
3680 l_party_site_id,
3681 p_to_party_id,
3682 NULL,
3683 NULL,
3684 ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
3685 );
3686 END IF;
3687 /* Build the Data Object */
3688
3689 build_uda_data_payload(p_attribute_group_id => p_attribute_group_id,
3690 p_attribute_group_name => p_attribute_group_name,
3691 p_attribute_group_type => p_attribute_group_type,
3692 p_row_identifier => l_row_identifier,
3693 p_uda_data_rec => p_uda_rec,
3694 x_attributes_data_table => l_attributes_data_table);
3695
3696 ELSE
3697 /* Build the Row Object */
3698 IF (p_data_level_id = 17701) THEN
3699 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3700 p_attribute_group_id,
3701 177,
3702 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3703 p_attribute_group_name, --p_attribute_group_name,
3704 'SUPP_LEVEL', -- data level
3705 'N',
3706 NULL,
3707 NULL,
3708 NULL,
3709 NULL,
3710 ego_user_attrs_data_pvt.g_create_mode --TRANSACTION_TYPE
3711 );
3712 ELSIF (p_data_level_id = 17702) THEN
3713 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3714 p_attribute_group_id,
3715 177,
3716 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3717 p_attribute_group_name, --p_attribute_group_name,
3718 'SUPP_ADDR_LEVEL', -- data level
3719 'N',
3720 p_to_party_id,
3721 NULL,
3722 NULL,
3723 NULL,
3724 ego_user_attrs_data_pvt.g_create_mode --TRANSACTION_TYPE
3725 );
3726 ELSIF (p_data_level_id = 17703) THEN
3727 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
3728 p_attribute_group_id,
3729 177,
3730 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
3731 p_attribute_group_name, --p_attribute_group_name,
3732 'SUPP_ADDR_SITE_LEVEL', -- data level
3733 'N',
3734 l_party_site_id,
3735 p_to_party_id,
3736 NULL,
3737 NULL,
3738 ego_user_attrs_data_pvt.g_create_mode --TRANSACTION_TYPE
3739 );
3740 END IF;
3741
3742 /* Build the Data Object */
3743 build_uda_data_payload(p_attribute_group_id => p_attribute_group_id,
3744 p_attribute_group_name => p_attribute_group_name,
3745 p_attribute_group_type => p_attribute_group_type,
3746 p_row_identifier => l_row_identifier,
3747 p_uda_data_rec => p_uda_rec,
3748 x_attributes_data_table => l_attributes_data_table);
3749 END IF;
3750
3751 l_class_code_name_value_pairs := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('CLASSIFICATION_CODE',
3752 p_class_code));
3753
3754 /* Call the EGO API to process the attributes based on the mode */
3755
3756 ego_user_attrs_data_pub.process_user_attrs_data(p_api_version => 1.0,
3757 p_object_name => 'HZ_PARTIES',
3758 p_attributes_row_table => l_attributes_row_table,
3759 p_attributes_data_table => l_attributes_data_table,
3760 p_pk_column_name_value_pairs => l_pk_column_values,
3761 p_class_code_name_value_pairs => l_class_code_name_value_pairs,
3762 p_entity_id => NULL,
3763 p_entity_index => NULL,
3764 p_entity_code => NULL,
3765 p_debug_level => NULL, --p_debug_level,
3766 p_commit => fnd_api.g_false,
3767 p_init_error_handler => 'T',
3768 p_init_fnd_msg_list => 'T',
3769 x_failed_row_id_list => l_failed_row_id_buffer,
3770 x_return_status => l_return_status,
3771 x_errorcode => l_errorcode,
3772 x_msg_count => l_msg_count,
3773 x_msg_data => l_msg_data);
3774
3775 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3776 error_handler.get_message_list(l_error_msg_tbl);
3777 IF l_error_msg_tbl.first IS NOT NULL THEN
3778 l_msg_count := l_error_msg_tbl.first;
3779 WHILE l_msg_count IS NOT NULL LOOP
3780 fnd_file.put_line(fnd_file.log,
3781 'Error Message: ' || l_error_msg_tbl(l_msg_count)
3782 .message_text ||
3783 ' From ego_user_attrs_data_pub.process_user_attrs_data API.');
3784 l_msg_count := l_error_msg_tbl.next(l_msg_count);
3785 END LOOP;
3786 END IF;
3787 END IF;
3788
3789 EXCEPTION
3790 WHEN OTHERS THEN
3791 x_return_status := fnd_api.g_ret_sts_unexp_error;
3792 END do_uda_row_dml;
3793
3794 PROCEDURE supplier_uda_merge
3795 (
3796 p_entity_name IN VARCHAR2,
3797 p_from_id IN NUMBER,
3798 x_to_id IN OUT NOCOPY NUMBER,
3799 p_from_fk_id IN NUMBER,
3800 p_to_fk_id IN NUMBER,
3801 p_parent_entity_name IN VARCHAR2,
3802 p_batch_id IN NUMBER,
3803 p_batch_party_id IN NUMBER,
3804 x_return_status OUT NOCOPY VARCHAR2
3805 ) IS
3806 l_ret_status VARCHAR2(2000);
3807 l_msg_count NUMBER;
3808 l_msg_data VARCHAR2(2000);
3809
3810 l_uda_rec_tbl uda_rec_tbl_type;
3811 merged_uda_rec pos_supp_prof_ext_b%ROWTYPE;
3812 l_party_count NUMBER := 0;
3813 l_to_party_cntr NUMBER := 0;
3814
3815 CURSOR get_supplier_attribute_groups IS
3816 SELECT ag.attr_group_id,
3817 ag.application_id,
3818 eas.data_level_id,
3819 ag.multi_row,
3820 eas.attr_group_type,
3821 ag.descriptive_flex_context_code,
3822 ag.descriptive_flexfield_name,
3823 eas.classification_code
3824 FROM ego_fnd_dsc_flx_ctx_ext ag,
3825 ego_obj_attr_grp_assocs_v eas
3826 WHERE ag.application_id = 177
3827 AND ag.attr_group_id = eas.attr_group_id
3828 AND eas.application_id = ag.application_id
3829 AND eas.data_level_int_name = 'SUPP_LEVEL';
3830
3831 TYPE attributes_group_tab IS TABLE OF get_supplier_attribute_groups%ROWTYPE INDEX BY BINARY_INTEGER;
3832 attributes_group_coll attributes_group_tab;
3833
3834 BEGIN
3835 x_return_status := fnd_api.g_ret_sts_success;
3836
3837 SELECT COUNT(1)
3838 INTO l_to_party_cntr
3839 FROM pos_supplier_uda_merge_gtt
3840 WHERE batch_id = p_batch_id
3841 AND to_party_id = p_to_fk_id
3842 AND rownum = 1;
3843
3844 IF (l_to_party_cntr = 0) THEN
3845 INSERT INTO pos_supplier_uda_merge_gtt
3846 (batch_id, to_party_id)
3847 VALUES
3848 (p_batch_id, p_to_fk_id);
3849 ELSE
3850 RETURN;
3851 END IF;
3852
3853 fnd_file.put_line(fnd_file.log,
3854 'Inside POS_MERGE_SUPPLIER_PKG.supplier_uda_merge p_to_fk_id: ' ||
3855 p_to_fk_id || ' p_to_fk_id: ' || p_from_fk_id);
3856
3857 /* Update the status of all the records in the bath to
3858 PERFORM_UDA_MERGE*/
3859
3860 /* Get the attribute group data from the cursor
3861 get_supplier_attribute_groups*/
3862
3863 OPEN get_supplier_attribute_groups;
3864 FETCH get_supplier_attribute_groups BULK COLLECT
3865 INTO attributes_group_coll;
3866 CLOSE get_supplier_attribute_groups;
3867
3868 /* Loop through the attribute group Ids */
3869 FOR cntr IN 1 .. attributes_group_coll.count LOOP
3870
3871 /* check if the attribute group is a single row attribute or a
3872 multirow attribute*/
3873
3874 /* If the attribute group is single row */
3875 IF (attributes_group_coll(cntr).multi_row = 'N') THEN
3876
3877 /* Query the extension table for the to and from parties
3878 and get the rows in the ascending order of the creation
3879 date */
3880 fnd_file.put_line(fnd_file.log,
3881 'supplier_uda_merge single row processing for attr group id : ' || attributes_group_coll(cntr)
3882 .attr_group_id);
3883
3884 get_batch_user_attr_data(p_to_fk_id,
3885 p_batch_id,
3886 attributes_group_coll(cntr).multi_row,
3887 attributes_group_coll(cntr).attr_group_id,
3888 attributes_group_coll(cntr).data_level_id,
3889 l_uda_rec_tbl,
3890 l_ret_status,
3891 l_msg_count,
3892 l_msg_data);
3893
3894 /* Do the merging */
3895 IF (l_uda_rec_tbl.count <> 0) THEN
3896 do_single_row_uda_merge(l_uda_rec_tbl,
3897 merged_uda_rec,
3898 l_ret_status,
3899 l_msg_count,
3900 l_msg_data);
3901
3902 SELECT COUNT(1)
3903 INTO l_party_count
3904 FROM pos_supp_prof_ext_b
3905 WHERE attr_group_id = attributes_group_coll(cntr).attr_group_id
3906 AND party_id = p_to_fk_id
3907 AND rownum = 1;
3908
3909 IF (l_party_count = 0) THEN
3910 /* Do the insert */
3911 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
3912 .attr_group_id,
3913 p_data_level_id => attributes_group_coll(cntr)
3914 .data_level_id,
3915 p_attribute_group_type => attributes_group_coll(cntr)
3916 .attr_group_type,
3917 p_attribute_group_name => attributes_group_coll(cntr)
3918 .descriptive_flex_context_code,
3919 p_to_party_id => p_to_fk_id,
3920 p_mode => 'INSERT',
3921 p_uda_rec => merged_uda_rec,
3922 p_class_code => attributes_group_coll(cntr)
3923 .classification_code,
3924 x_return_status => l_ret_status,
3925 x_msg_count => l_msg_count,
3926 x_msg_data => l_msg_data);
3927
3928 ELSE
3929 /* Do the update */
3930 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
3931 .attr_group_id,
3932 p_data_level_id => attributes_group_coll(cntr)
3933 .data_level_id,
3934 p_attribute_group_type => attributes_group_coll(cntr)
3935 .attr_group_type,
3936 p_attribute_group_name => attributes_group_coll(cntr)
3937 .descriptive_flex_context_code,
3938 p_to_party_id => p_to_fk_id,
3939 p_mode => 'UPDATE',
3940 p_uda_rec => merged_uda_rec,
3941 p_class_code => attributes_group_coll(cntr)
3942 .classification_code,
3943 x_return_status => l_ret_status,
3944 x_msg_count => l_msg_count,
3945 x_msg_data => l_msg_data);
3946 END IF;
3947 END IF;
3948 -- End of single row processing
3949 ELSE
3950
3951 /* If the attribute group is multi row */
3952 -- Multirow processing
3953 fnd_file.put_line(fnd_file.log,
3954 'supplier_uda_merge multi row processing for attr group id : ' || attributes_group_coll(cntr)
3955 .attr_group_id);
3956
3957 get_batch_user_attr_data(p_to_fk_id,
3958 p_batch_id,
3959 attributes_group_coll(cntr).multi_row,
3960 attributes_group_coll(cntr).attr_group_id,
3961 attributes_group_coll(cntr).data_level_id,
3962 l_uda_rec_tbl,
3963 l_ret_status,
3964 l_msg_count,
3965 l_msg_data);
3966
3967 /* Insert the rows obtained */
3968
3969 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
3970 .attr_group_id,
3971 p_data_level_id => attributes_group_coll(cntr)
3972 .data_level_id,
3973 p_attribute_group_type => attributes_group_coll(cntr)
3974 .attr_group_type,
3975 p_attribute_group_name => attributes_group_coll(cntr)
3976 .descriptive_flex_context_code,
3977 p_to_party_id => p_to_fk_id,
3978 p_mode => 'INSERT',
3979 p_uda_rec_tbl => l_uda_rec_tbl,
3980 p_class_code => attributes_group_coll(cntr)
3981 .classification_code,
3982 x_return_status => l_ret_status,
3983 x_msg_count => l_msg_count,
3984 x_msg_data => l_msg_data);
3985 END IF;
3986
3987 --END LOOP; -- End of party loop
3988
3989 END LOOP; -- end of attribute group loop
3990
3991 EXCEPTION
3992 WHEN OTHERS THEN
3993 x_return_status := fnd_api.g_ret_sts_unexp_error;
3994 END supplier_uda_merge;
3995
3996 PROCEDURE supplier_site_uda_merge
3997 (
3998 p_from_id IN NUMBER,
3999 p_from_fk_id IN NUMBER,
4000 p_to_fk_id IN NUMBER,
4001 x_return_status OUT NOCOPY VARCHAR2
4002 ) IS
4003 l_ret_status VARCHAR2(2000);
4004 l_msg_count NUMBER;
4005 l_msg_data VARCHAR2(2000);
4006
4007 l_uda_rec_tbl uda_rec_tbl_type;
4008 merged_uda_rec pos_supp_prof_ext_b%ROWTYPE;
4009 l_party_count NUMBER := 0;
4010 l_to_party_cntr NUMBER := 0;
4011
4012 CURSOR get_supplier_attribute_groups IS
4013 SELECT ag.attr_group_id,
4014 ag.application_id,
4015 eas.data_level_id,
4016 ag.multi_row,
4017 eas.attr_group_type,
4018 ag.descriptive_flex_context_code,
4019 ag.descriptive_flexfield_name,
4020 eas.classification_code
4021 FROM ego_fnd_dsc_flx_ctx_ext ag,
4022 ego_obj_attr_grp_assocs_v eas
4023 WHERE ag.application_id = 177
4024 AND ag.attr_group_id = eas.attr_group_id
4025 AND eas.application_id = ag.application_id
4026 AND eas.data_level_int_name = 'SUPP_ADDR_SITE_LEVEL';
4027
4028 TYPE attributes_group_tab IS TABLE OF get_supplier_attribute_groups%ROWTYPE INDEX BY BINARY_INTEGER;
4029 attributes_group_coll attributes_group_tab;
4030
4031 BEGIN
4032 x_return_status := fnd_api.g_ret_sts_success;
4033
4034 fnd_file.put_line(fnd_file.log,
4035 'Inside POS_MERGE_SUPPLIER_PKG.supplier_site_uda_merge p_to_fk_id: ' ||
4036 p_to_fk_id || ' p_to_fk_id: ' || p_from_fk_id ||
4037 ' p_from_id : ' || p_from_id);
4038
4039 /* Update the status of all the records in the bath to
4040 PERFORM_UDA_MERGE*/
4041
4042 /* Get the attribute group data from the cursor
4043 get_supplier_attribute_groups*/
4044
4045 OPEN get_supplier_attribute_groups;
4046 FETCH get_supplier_attribute_groups BULK COLLECT
4047 INTO attributes_group_coll;
4048 CLOSE get_supplier_attribute_groups;
4049
4050 /* Loop through the attribute group Ids */
4051 FOR cntr IN 1 .. attributes_group_coll.count LOOP
4052
4053 /* check if the attribute group is a single row attribute or a
4054 multirow attribute*/
4055
4056 /* If the attribute group is single row */
4057 IF (attributes_group_coll(cntr).multi_row = 'N') THEN
4058
4059 /* Query the extension table for the to and from parties
4060 and get the rows in the ascending order of the creation
4061 date */
4062 fnd_file.put_line(fnd_file.log,
4063 'supplier_site_uda_merge single row processing for attr group id : ' || attributes_group_coll(cntr)
4064 .attr_group_id);
4065
4066 get_supp_site_attr_data(p_from_fk_id,
4067 p_to_fk_id,
4068 attributes_group_coll(cntr).multi_row,
4069 attributes_group_coll(cntr).attr_group_id,
4070 attributes_group_coll(cntr).data_level_id,
4071 l_uda_rec_tbl,
4072 l_ret_status,
4073 l_msg_count,
4074 l_msg_data);
4075
4076 /* Do the merging */
4077 IF (l_uda_rec_tbl.count <> 0) THEN
4078 do_single_row_uda_merge(l_uda_rec_tbl,
4079 merged_uda_rec,
4080 l_ret_status,
4081 l_msg_count,
4082 l_msg_data);
4083
4084 SELECT COUNT(1)
4085 INTO l_party_count
4086 FROM pos_supp_prof_ext_b
4087 WHERE attr_group_id = attributes_group_coll(cntr).attr_group_id
4088 AND pk2_value = p_to_fk_id
4089 AND rownum = 1;
4090
4091 IF (l_party_count = 0) THEN
4092 /* Do the insert */
4093 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
4094 .attr_group_id,
4095 p_data_level_id => attributes_group_coll(cntr)
4096 .data_level_id,
4097 p_attribute_group_type => attributes_group_coll(cntr)
4098 .attr_group_type,
4099 p_attribute_group_name => attributes_group_coll(cntr)
4100 .descriptive_flex_context_code,
4101 p_to_party_id => p_to_fk_id,
4102 p_mode => 'INSERT',
4103 p_uda_rec => merged_uda_rec,
4104 p_class_code => attributes_group_coll(cntr)
4105 .classification_code,
4106 p_vendor_id => p_from_id,
4107 x_return_status => l_ret_status,
4108 x_msg_count => l_msg_count,
4109 x_msg_data => l_msg_data);
4110
4111 ELSE
4112 /* Do the update */
4113 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
4114 .attr_group_id,
4115 p_data_level_id => attributes_group_coll(cntr)
4116 .data_level_id,
4117 p_attribute_group_type => attributes_group_coll(cntr)
4118 .attr_group_type,
4119 p_attribute_group_name => attributes_group_coll(cntr)
4120 .descriptive_flex_context_code,
4121 p_to_party_id => p_to_fk_id,
4122 p_mode => 'UPDATE',
4123 p_uda_rec => merged_uda_rec,
4124 p_class_code => attributes_group_coll(cntr)
4125 .classification_code,
4126 p_vendor_id => p_from_id,
4127 x_return_status => l_ret_status,
4128 x_msg_count => l_msg_count,
4129 x_msg_data => l_msg_data);
4130 END IF;
4131 END IF;
4132 -- End of single row processing
4133 ELSE
4134
4135 /* If the attribute group is multi row */
4136 -- Multirow processing
4137 fnd_file.put_line(fnd_file.log,
4138 'supplier_site_uda_merge multi row processing for attr group id : ' || attributes_group_coll(cntr)
4139 .attr_group_id);
4140
4141 get_supp_site_attr_data(p_from_fk_id,
4142 p_to_fk_id,
4143 attributes_group_coll(cntr).multi_row,
4144 attributes_group_coll(cntr).attr_group_id,
4145 attributes_group_coll(cntr).data_level_id,
4146 l_uda_rec_tbl,
4147 l_ret_status,
4148 l_msg_count,
4149 l_msg_data);
4150
4151 /* Insert the rows obtained */
4152
4153 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
4154 .attr_group_id,
4155 p_data_level_id => attributes_group_coll(cntr)
4156 .data_level_id,
4157 p_attribute_group_type => attributes_group_coll(cntr)
4158 .attr_group_type,
4159 p_attribute_group_name => attributes_group_coll(cntr)
4160 .descriptive_flex_context_code,
4161 p_to_party_id => p_to_fk_id,
4162 p_mode => 'INSERT',
4163 p_uda_rec_tbl => l_uda_rec_tbl,
4164 p_class_code => attributes_group_coll(cntr)
4165 .classification_code,
4166 p_vendor_id => p_from_id,
4167 x_return_status => l_ret_status,
4168 x_msg_count => l_msg_count,
4169 x_msg_data => l_msg_data);
4170 END IF;
4171
4172 --END LOOP; -- End of party loop
4173
4174 END LOOP; -- end of attribute group loop
4175
4176 EXCEPTION
4177 WHEN OTHERS THEN
4178 x_return_status := fnd_api.g_ret_sts_unexp_error;
4179 END supplier_site_uda_merge;
4180
4181 PROCEDURE party_site_uda_merge
4182 (
4183 p_entity_name IN VARCHAR2,
4184 p_from_id IN NUMBER,
4185 x_to_id IN OUT NOCOPY NUMBER,
4186 p_from_fk_id IN NUMBER,
4187 p_to_fk_id IN NUMBER,
4188 p_parent_entity_name IN VARCHAR2,
4189 p_batch_id IN NUMBER,
4190 p_batch_party_id IN NUMBER,
4191 x_return_status OUT NOCOPY VARCHAR2
4192 ) IS
4193 l_ret_status VARCHAR2(2000);
4194 l_msg_count NUMBER;
4195 l_msg_data VARCHAR2(2000);
4196
4197 l_uda_rec_tbl uda_rec_tbl_type;
4198 merged_uda_rec pos_supp_prof_ext_b%ROWTYPE;
4199 l_party_count NUMBER := 0;
4200 l_to_party_cntr NUMBER := 0;
4201
4202 CURSOR get_supplier_attribute_groups IS
4203 SELECT ag.attr_group_id,
4204 ag.application_id,
4205 eas.data_level_id,
4206 ag.multi_row,
4207 eas.attr_group_type,
4208 ag.descriptive_flex_context_code,
4209 ag.descriptive_flexfield_name,
4210 eas.classification_code,
4211 eas.data_level_int_name
4212 FROM ego_fnd_dsc_flx_ctx_ext ag,
4213 ego_obj_attr_grp_assocs_v eas
4214 WHERE ag.application_id = 177
4215 AND ag.attr_group_id = eas.attr_group_id
4216 AND eas.application_id = ag.application_id
4217 AND eas.data_level_int_name = 'SUPP_ADDR_LEVEL';
4218
4219 TYPE attributes_group_tab IS TABLE OF get_supplier_attribute_groups%ROWTYPE INDEX BY BINARY_INTEGER;
4220 attributes_group_coll attributes_group_tab;
4221
4222 BEGIN
4223 x_return_status := fnd_api.g_ret_sts_success;
4224
4225 SELECT COUNT(1)
4226 INTO l_to_party_cntr
4227 FROM pos_supplier_uda_merge_gtt
4228 WHERE batch_id = p_batch_id
4229 AND to_party_id = p_to_fk_id
4230 AND rownum = 1;
4231
4232 IF (l_to_party_cntr = 0) THEN
4233 INSERT INTO pos_supplier_uda_merge_gtt
4234 (batch_id, to_party_id)
4235 VALUES
4236 (p_batch_id, p_to_fk_id);
4237 ELSE
4238 RETURN;
4239 END IF;
4240
4241 fnd_file.put_line(fnd_file.log,
4242 'Inside POS_MERGE_SUPPLIER_PKG.party_site_uda_merge p_to_fk_id: ' ||
4243 p_to_fk_id || ' p_from_fk_id: ' || p_from_fk_id ||
4244 ' p_from_id: ' || p_from_id || ' p_batch_id: ' ||
4245 p_batch_id);
4246
4247 /* Update the status of all the records in the bath to
4248 PERFORM_UDA_MERGE*/
4249
4250 /* Get the attribute group data from the cursor
4251 get_supplier_attribute_groups*/
4252
4253 OPEN get_supplier_attribute_groups;
4254 FETCH get_supplier_attribute_groups BULK COLLECT
4255 INTO attributes_group_coll;
4256 CLOSE get_supplier_attribute_groups;
4257
4258 /* Loop through the attribute group Ids */
4259 FOR cntr IN 1 .. attributes_group_coll.count LOOP
4260
4261 /* check if the attribute group is a single row attribute or a
4262 multirow attribute*/
4263
4264 /* If the attribute group is single row */
4265 IF (attributes_group_coll(cntr).multi_row = 'N') THEN
4266
4267 /* Query the extension table for the to and from parties
4268 and get the rows in the ascending order of the creation
4269 date */
4270 fnd_file.put_line(fnd_file.log,
4271 'party_site_uda_merge single row processing for attr group id : ' || attributes_group_coll(cntr)
4272 .attr_group_id);
4273
4274 get_party_site_attr_data(p_from_fk_id,
4275 p_to_fk_id,
4276 p_batch_id,
4277 attributes_group_coll(cntr).multi_row,
4278 attributes_group_coll(cntr).attr_group_id,
4279 attributes_group_coll(cntr).data_level_id,
4280 l_uda_rec_tbl,
4281 l_ret_status,
4282 l_msg_count,
4283 l_msg_data);
4284
4285 /* Do the merging */
4286 IF (l_uda_rec_tbl.count <> 0) THEN
4287 do_single_row_uda_merge(l_uda_rec_tbl,
4288 merged_uda_rec,
4289 l_ret_status,
4290 l_msg_count,
4291 l_msg_data);
4292
4293 SELECT COUNT(1)
4294 INTO l_party_count
4295 FROM pos_supp_prof_ext_b
4296 WHERE attr_group_id = attributes_group_coll(cntr).attr_group_id
4297 AND pk1_value = p_to_fk_id
4298 AND rownum = 1;
4299
4300 IF (l_party_count = 0) THEN
4301 /* Do the insert */
4302 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
4303 .attr_group_id,
4304 p_data_level_id => attributes_group_coll(cntr)
4305 .data_level_id,
4306 p_attribute_group_type => attributes_group_coll(cntr)
4307 .attr_group_type,
4308 p_attribute_group_name => attributes_group_coll(cntr)
4309 .descriptive_flex_context_code,
4310 p_to_party_id => p_to_fk_id,
4311 p_mode => 'INSERT',
4312 p_uda_rec => merged_uda_rec,
4313 p_class_code => attributes_group_coll(cntr)
4314 .classification_code,
4315 x_return_status => l_ret_status,
4316 x_msg_count => l_msg_count,
4317 x_msg_data => l_msg_data);
4318
4319 ELSE
4320 /* Do the update */
4321 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
4322 .attr_group_id,
4323 p_data_level_id => attributes_group_coll(cntr)
4324 .data_level_id,
4325 p_attribute_group_type => attributes_group_coll(cntr)
4326 .attr_group_type,
4327 p_attribute_group_name => attributes_group_coll(cntr)
4328 .descriptive_flex_context_code,
4329 p_to_party_id => p_to_fk_id,
4330 p_mode => 'UPDATE',
4331 p_uda_rec => merged_uda_rec,
4332 p_class_code => attributes_group_coll(cntr)
4333 .classification_code,
4334 x_return_status => l_ret_status,
4335 x_msg_count => l_msg_count,
4336 x_msg_data => l_msg_data);
4337 END IF;
4338 END IF;
4339 -- End of single row processing
4340 ELSE
4341
4342 /* If the attribute group is multi row */
4343 -- Multirow processing
4344 fnd_file.put_line(fnd_file.log,
4345 'party_site_uda_merge multi row processing for attr group id : ' || attributes_group_coll(cntr)
4346 .attr_group_id);
4347
4348 get_party_site_attr_data(p_from_fk_id,
4349 p_to_fk_id,
4350 p_batch_id,
4351 attributes_group_coll(cntr).multi_row,
4352 attributes_group_coll(cntr).attr_group_id,
4353 attributes_group_coll(cntr).data_level_id,
4354 l_uda_rec_tbl,
4355 l_ret_status,
4356 l_msg_count,
4357 l_msg_data);
4358
4359 /* Insert the rows obtained */
4360
4361 do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
4362 .attr_group_id,
4363 p_data_level_id => attributes_group_coll(cntr)
4364 .data_level_id,
4365 p_attribute_group_type => attributes_group_coll(cntr)
4366 .attr_group_type,
4367 p_attribute_group_name => attributes_group_coll(cntr)
4368 .descriptive_flex_context_code,
4369 p_to_party_id => p_to_fk_id,
4370 p_mode => 'INSERT',
4371 p_uda_rec_tbl => l_uda_rec_tbl,
4372 p_class_code => attributes_group_coll(cntr)
4373 .classification_code,
4374 x_return_status => l_ret_status,
4375 x_msg_count => l_msg_count,
4376 x_msg_data => l_msg_data);
4377 END IF;
4378
4379 --END LOOP; -- End of party loop
4380
4381 END LOOP; -- end of attribute group loop
4382
4383 EXCEPTION
4384 WHEN OTHERS THEN
4385 x_return_status := fnd_api.g_ret_sts_unexp_error;
4386 END party_site_uda_merge;
4387
4388 PROCEDURE enable_party_as_supplier
4389 (
4390 p_entity_name IN VARCHAR2,
4391 p_from_id IN NUMBER,
4392 x_to_id IN OUT NOCOPY NUMBER,
4393 p_from_fk_id IN NUMBER,
4394 p_to_fk_id IN NUMBER,
4395 p_parent_entity_name IN VARCHAR2,
4396 p_batch_id IN NUMBER,
4397 p_batch_party_id IN NUMBER,
4398 x_return_status OUT NOCOPY VARCHAR2
4399 ) IS
4400 l_msg_count NUMBER;
4401 l_msg_data VARCHAR2(100);
4402 l_to_vendor_id NUMBER;
4403
4404 vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
4405 l_vendor_id NUMBER;
4406 l_party_id NUMBER; /*:= p_to_fk_id*/
4407 l_ven_num_code financials_system_parameters.user_defined_vendor_num_code%TYPE;
4408 l_party_orig_system_ref VARCHAR2(500);
4409
4410 BEGIN
4411 x_return_status := fnd_api.g_ret_sts_success;
4412
4413 fnd_file.put_line(fnd_file.log,
4414 'Inside POS_MERGE_SUPPLIER_PKG.enable_party_as_supplier p_to_fk_id: ' ||
4415 p_to_fk_id || ' p_to_fk_id: ' || p_from_fk_id);
4416
4417 UPDATE ap_suppliers
4418 SET end_date_active = SYSDATE
4419 WHERE party_id = p_from_fk_id;
4420
4421 SELECT COUNT(vendor_id)
4422 INTO l_to_vendor_id
4423 FROM ap_suppliers
4424 WHERE party_id = p_to_fk_id;
4425
4426 fnd_file.put_line(fnd_file.log, 'l_to_vendor_id: ' || l_to_vendor_id);
4427
4428 IF (l_to_vendor_id = 0) THEN
4429 l_ven_num_code := pos_batch_import_pkg.chk_vendor_num_nmbering_method();
4430
4431 IF (l_ven_num_code = 'MANUAL') THEN
4432
4433 SELECT orig_system_reference
4434 INTO l_party_orig_system_ref
4435 FROM hz_orig_sys_references hr
4436 WHERE hr.owner_table_name = 'HZ_PARTIES'
4437 AND owner_table_id = p_to_fk_id
4438 AND party_id = p_to_fk_id
4439 AND hr.status = 'A'
4440 AND (hr.reason_code <> 'MERGED' OR hr.reason_code IS NULL)
4441 AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE;
4442
4443 vendor_rec.segment1 := l_party_orig_system_ref;
4444 END IF;
4445
4446 vendor_rec.party_id := p_to_fk_id;
4447
4448 ap_vendor_pub_pkg.create_vendor(1.0,
4449 fnd_api.g_false,
4450 fnd_api.g_false,
4451 fnd_api.g_valid_level_full,
4452 x_return_status,
4453 l_msg_count,
4454 l_msg_data,
4455 vendor_rec,
4456 l_vendor_id,
4457 l_party_id);
4458
4459 fnd_file.put_line(fnd_file.log,
4460 'x_return_status from ap_vendor_pub_pkg.create_vendor: ' ||
4461 x_return_status || ' l_vendor_id: ' || l_vendor_id);
4462
4463 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4464 fnd_file.put_line(fnd_file.log,
4465 'No. of Messages: ' || l_msg_count ||
4466 ', Message: ' || l_msg_data ||
4467 ' From ap_vendor_pub_pkg.create_vendor API.');
4468 RETURN;
4469 ELSE
4470 x_to_id := l_vendor_id;
4471 END IF;
4472 END IF;
4473
4474 EXCEPTION
4475 WHEN OTHERS THEN
4476 x_return_status := fnd_api.g_ret_sts_unexp_error;
4477 END enable_party_as_supplier;
4478
4479 END pos_merge_supplier_pkg;|
|
|
|