[Home] [Help]
PACKAGE BODY: APPS.POS_SUPPLIER_UDA_BO_PKG
Source
1 PACKAGE BODY pos_supplier_uda_bo_pkg AS
2 /* $Header: POSSPUDAB.pls 120.1.12020000.4 2013/02/09 13:23:12 hvutukur ship $ */
3 l_attr_group_id NUMBER;
4 l_pk_column_values ego_col_name_value_pair_array;
5 l_request_table ego_attr_group_request_table := ego_attr_group_request_table();
6 l_attributes_row_table ego_user_attr_row_table;
7 l_attributes_data_table ego_user_attr_data_table := ego_user_attr_data_table();
8 l_return_status VARCHAR2(100);
9 l_errorcode NUMBER;
10 l_msg_count NUMBER;
11 l_msg_data VARCHAR2(4000);
12 l_attr_group_disp_name VARCHAR2(255);
13 l_attrgroup_counter NUMBER := 0;
14 pos_obj_list pos_supp_uda_obj_tbl := pos_supp_uda_obj_tbl();
15
16 PROCEDURE intialize IS
17 i NUMBER := 0;
18 BEGIN
19
20 l_attr_group_id := NULL;
21 l_pk_column_values := NULL;
22 l_request_table := ego_attr_group_request_table();
23 l_attributes_row_table := NULL;
24 l_attributes_data_table := ego_user_attr_data_table();
25 l_return_status := '';
26 l_errorcode := '';
27 l_msg_count := '';
28 l_msg_data := '';
29 l_attr_group_disp_name := '';
30 l_attrgroup_counter := 0;
31 pos_obj_list := pos_supp_uda_obj_tbl();
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 null;
36 END;
37
38 PROCEDURE get_values(p_party_id IN NUMBER,
39 p_supp_data_level IN VARCHAR2,
40 l_data_level_2 NUMBER,
41 l_data_level_3 NUMBER) IS
42
43 BEGIN
44
45 l_request_table(l_request_table.last) := ego_attr_group_request_obj(l_attr_group_id --ATTR_GROUP_ID
46 ,
47 NULL -- application id replace with fnd_application.application_id
48 ,
52 ,
49 NULL -- group type
50 ,
51 NULL -- group name
53 p_supp_data_level, -- 'SUPP_LEVEL' -- data level
54
55 'N' -- DATA_LEVEL_1 --bug 15992883
56 ,
57 l_data_level_2 -- DATA_LEVEL_2--Party_site_id
58 ,
59 l_data_level_3 -- DATA_LEVEL_3--supplier Site Id
60 ,
61 NULL -- DATA_LEVEL_4
62 ,
63 NULL -- DATA_LEVEL_5
64 ,
65 NULL -- ATTR_NAME_LIST
66 );
67
68 -- Get the user attribute data
69
70 pos_vendor_pub_pkg.get_user_attrs_data(p_api_version => 1.0,
71 p_pk_column_name_value_pairs => l_pk_column_values,
72 p_attr_group_request_table => l_request_table,
73 x_attributes_row_table => l_attributes_row_table,
74 x_attributes_data_table => l_attributes_data_table,
75 x_return_status => l_return_status,
76 x_errorcode => l_errorcode,
77 x_msg_count => l_msg_count,
78 x_msg_data => l_msg_data);
79
80 IF l_attributes_data_table IS NOT NULL THEN
81
82 pos_obj_list.extend;
83 l_attrgroup_counter := l_attrgroup_counter + 1;
84 pos_obj_list(l_attrgroup_counter) := pos_supp_uda_obj(l_data_level_2, --party_site_id,
85 l_data_level_3, --supplier_site_id,
86 l_attr_group_id,
87 l_attr_group_disp_name,
88 l_attributes_data_table);
89
90 END IF;
91 EXCEPTION
92 WHEN OTHERS THEN
93 RAISE;
94 END;
95 /*#
96 * Use this routine to get UDA data
97 * @param p_party_id The party_id
98 * @param p_party_site_id The party_site_id
99 * @param p_supplier_site_id The supplier_site_id
100 * @param p_supp_data_level The supplier data level
101 * @param x_pos_supplier_uda The supplier uda bo
102 * @param x_return_status The return status
103 * @param x_msg_count The message count
104 * @param x_msg_data The message data
105 * @rep:scope public
106 * @rep:lifecycle active
107 * @rep:displayname Get Supplier UDA BO
108 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
109 */
110 PROCEDURE get_uda_data(p_party_id IN NUMBER,
111 p_party_site_id IN NUMBER,
112 p_supplier_site_id IN NUMBER,
113 p_supp_data_level IN VARCHAR2,
114 x_pos_supplier_uda OUT NOCOPY pos_supp_uda_obj_tbl,
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2) IS
118
119 l_data_level_2 NUMBER;
120 l_data_level_3 NUMBER;
121
122 BEGIN
123 intialize;
124 l_request_table.delete;
125 FOR j IN (SELECT DISTINCT attr_group_id
126 FROM pos_supp_prof_ext_b
127 WHERE party_id = p_party_id) LOOP
128 l_attributes_data_table := ego_user_attr_data_table();
129 l_attr_group_id := j.attr_group_id;
130 BEGIN
131 SELECT attr_group_name
132 INTO l_attr_group_disp_name
133 FROM ego_attr_groups_v
134 WHERE attr_group_id = l_attr_group_id;
135 EXCEPTION
136 WHEN no_data_found THEN
137 -- Bug 14807469: Should continue publishing valid UDAs if the current one is invalid. Skip the current iteration.
138 fnd_file.put_line(fnd_file.log,'Attribute Group id: ' || l_attr_group_id || ' Not found in EGO_ATTR_GROUPS_V');
139 CONTINUE;
140 WHEN OTHERS THEN
141 EXIT;
142 END;
143
144 -- Primary key value pairs
145 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
146 p_party_id));
147 -- Attribute group object
151
148 l_request_table.extend;
149 --l_data_level_2 := NULL;
150 --l_data_level_3 := NULL;
152 IF p_supp_data_level = 'SUPP_LEVEL' THEN
153 get_values(p_party_id, p_supp_data_level, NULL, NULL);
154 ELSIF p_supp_data_level = 'SUPP_ADDR_LEVEL' THEN
155 l_data_level_2 := p_party_site_id;
156
157 get_values(p_party_id,
158 p_supp_data_level,
159 l_data_level_2,
160 NULL);
161 ELSIF p_supp_data_level = 'SUPP_ADDR_SITE_LEVEL' THEN
162 l_data_level_2 := p_party_site_id;
163 l_data_level_3 := p_supplier_site_id;
164 get_values(p_party_id,
165 p_supp_data_level,
166 l_data_level_2,
167 l_data_level_3);
168 END IF;
169 IF l_request_table IS NOT NULL THEN
170 l_request_table.delete;
171 END IF;
172 l_request_table := ego_attr_group_request_table();
173 IF l_attributes_data_table IS NOT NULL THEN
174 l_attributes_data_table.delete;
175 END IF;
176
177 END LOOP;
178 -- l_attributes_data_table.delete;
179 l_request_table.delete;
180 x_pos_supplier_uda := pos_obj_list;
181 IF pos_obj_list IS NOT NULL THEN
182 pos_obj_list.delete;
183 END IF;
184 EXCEPTION
185 WHEN fnd_api.g_exc_error THEN
186 x_return_status := fnd_api.g_ret_sts_error;
187 fnd_msg_pub.count_and_get(p_count => x_msg_count,
188 p_data => x_msg_data);
189
190 WHEN fnd_api.g_exc_unexpected_error THEN
191 x_return_status := fnd_api.g_ret_sts_unexp_error;
192 fnd_msg_pub.count_and_get(p_count => x_msg_count,
193 p_data => x_msg_data);
194 WHEN OTHERS THEN
195 x_return_status := fnd_api.g_ret_sts_unexp_error;
196 fnd_msg_pub.count_and_get(p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 END get_uda_data;
200
201 PROCEDURE get_uda_data_party_site(p_party_id IN NUMBER,
202 p_party_site_id IN NUMBER,
203 p_supplier_site_id IN NUMBER,
204 p_supp_data_level IN VARCHAR2,
205 x_pos_supplier_uda OUT NOCOPY pos_supp_uda_obj_tbl,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2) IS
209
210 BEGIN
211 NULL;
212 EXCEPTION
213 WHEN OTHERS THEN
214 RAISE;
215 END;
216 FUNCTION get_uda_for_supplier_site(p_party_id IN NUMBER,
217 p_party_site_id IN NUMBER,
218 p_supplier_site_id IN NUMBER,
219 p_supp_data_level IN VARCHAR2)
220 RETURN pos_supp_uda_obj_tbl IS
221 x_pos_supplier_uda pos_supp_uda_obj_tbl := pos_supp_uda_obj_tbl();
222 x_return_status VARCHAR2(1);
223 x_msg_count NUMBER;
224 x_msg_data VARCHAR2(1000);
225 BEGIN
226 l_attrgroup_counter := 0;
227 get_uda_data(p_party_id,
228 p_party_site_id,
229 p_supplier_site_id,
230 p_supp_data_level,
231 x_pos_supplier_uda,
232 x_return_status,
233 x_msg_count,
234 x_msg_data);
235 RETURN x_pos_supplier_uda;
236 EXCEPTION
237 WHEN OTHERS THEN
238 RAISE;
239 END;
240
241 PROCEDURE process_attribute_group(l_data_level_1 IN NUMBER,
242 l_data_level_2 IN NUMBER,
243 l_data_level_3 IN NUMBER,
244 l_data_level IN VARCHAR2,
245 l_party_id IN NUMBER,
246 p_pos_supplier_uda_obj IN pos_supp_uda_obj,
247 l_row_identifier IN NUMBER,
248 p_create_update_flag IN VARCHAR2,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2) IS
252 l_failed_row_id_buffer VARCHAR2(1000);
253
254 l_return_status VARCHAR2(2000);
255 l_msg_count NUMBER;
256 l_msg_data VARCHAR2(100);
257 l_errorcode NUMBER;
258 l_error_msg_tbl error_handler.error_tbl_type;
259
260 l_pk_column_values ego_col_name_value_pair_array;
261 l_attributes_row_table ego_user_attr_row_table := ego_user_attr_row_table();
262 l_attributes_data_table ego_user_attr_data_table := ego_user_attr_data_table();
263 l_class_code_name_value_pairs ego_col_name_value_pair_array := ego_col_name_value_pair_array();
267 attr_group_id NUMBER,
264 -- l_row_identifier NUMBER := 1020;
265 TYPE rec1 IS RECORD(
266
268 application_id NUMBER,
269 data_level_id NUMBER,
270 multi_row VARCHAR2(1),
271 attr_group_type VARCHAR2(40),
272 descriptive_flex_context_code VARCHAR2(30),
273 descriptive_flexfield_name VARCHAR2(40),
274 classification_code VARCHAR2(150));
275
276 attributes_group_tab rec1;
277
278 create_update_mode VARCHAR2(100);
279
280 BEGIN
281 x_return_status := fnd_api.g_ret_sts_success;
282
283 l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
284 to_char(l_party_id)));
285
286 IF p_create_update_flag = 'C' THEN
287 create_update_mode := ego_user_attrs_data_pvt.g_create_mode;
288 ELSIF p_create_update_flag = 'U' THEN
289 create_update_mode := ego_user_attrs_data_pvt.g_update_mode;
290 ELSE
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292 x_msg_data := 'create update flag is not set';
293 RETURN;
294 END IF;
295
296
297 BEGIN
298 SELECT ag.attr_group_id,
299 ag.application_id,
300 eas.data_level_id,
301 ag.multi_row,
302 eas.attr_group_type,
303 ag.descriptive_flex_context_code,
304 ag.descriptive_flexfield_name,
305 eas.classification_code
306 INTO attributes_group_tab
307 FROM ego_fnd_dsc_flx_ctx_ext ag,
308 ego_obj_attr_grp_assocs_v eas
309 WHERE ag.application_id = 177
310 AND ag.attr_group_id = eas.attr_group_id
311 AND eas.application_id = ag.application_id
312 AND eas.data_level_int_name = l_data_level
313 AND eas.attr_group_name =
314 p_pos_supplier_uda_obj.attribute_group_name;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 RAISE;
319 END;
320
321 l_class_code_name_value_pairs := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('CLASSIFICATION_CODE',
322 attributes_group_tab.classification_code));
323 l_attributes_row_table.extend;
324 l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
325 attributes_group_tab.attr_group_id,
326 177,
327 'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
328 p_pos_supplier_uda_obj.attribute_group_name, --p_attribute_group_name,
329 l_data_level, -- data level
330 '''N''',
331 p_pos_supplier_uda_obj.party_site_id,
332 p_pos_supplier_uda_obj.supplier_site_id,
333 NULL,
334 NULL,
335 create_update_mode --TRANSACTION_TYPE
336 );
337 /*
338 FOR datacntr IN 1 .. p_pos_supplier_uda_obj.attribute_data_list.count LOOP
339 su_debug_proc(7,
340 'Data ROW_IDENTIFIER :' || p_pos_supplier_uda_obj.attribute_data_list(datacntr)
341 .row_identifier);
342 su_debug_proc(8,
343 'Data user_row_identifier :' || p_pos_supplier_uda_obj.attribute_data_list(datacntr)
344 .user_row_identifier);
345 su_debug_proc(9,
346 'Data ATTR NAME :' || p_pos_supplier_uda_obj.attribute_data_list(datacntr)
347 .attr_name);
348 su_debug_proc(10,
349 'Data ATTR_VALUE_STR :' || p_pos_supplier_uda_obj.attribute_data_list(datacntr)
350 .attr_value_str);
351 su_debug_proc(11,
352 'Data ATTR_VALUE_NUM :' || p_pos_supplier_uda_obj.attribute_data_list(datacntr)
353 .attr_value_num);
354
355 END LOOP;
356 */
357 ego_user_attrs_data_pub.process_user_attrs_data(p_api_version => 1.0,
358 p_object_name => 'HZ_PARTIES',
359 p_attributes_row_table => l_attributes_row_table,
360 p_attributes_data_table => p_pos_supplier_uda_obj.attribute_data_list /* l_all_attributes_data_table*/,
361 p_pk_column_name_value_pairs => l_pk_column_values,
362 p_class_code_name_value_pairs => l_class_code_name_value_pairs,
366 p_debug_level => NULL, --p_debug_level,
363 p_entity_id => NULL,
364 p_entity_index => NULL,
365 p_entity_code => NULL,
367 p_commit => fnd_api.g_false,
368 p_init_error_handler => 'T',
369 p_init_fnd_msg_list => 'T',
370 x_failed_row_id_list => l_failed_row_id_buffer,
371 x_return_status => l_return_status,
372 x_errorcode => l_errorcode,
373 x_msg_count => l_msg_count,
374 x_msg_data => l_msg_data);
375
376 IF l_return_status <> fnd_api.g_ret_sts_success THEN
377 error_handler.get_message_list(l_error_msg_tbl);
378 IF l_error_msg_tbl.first IS NOT NULL THEN
379 l_msg_count := l_error_msg_tbl.first;
380 WHILE l_msg_count IS NOT NULL LOOP
381 l_msg_count := l_error_msg_tbl.next(l_msg_count);
382 END LOOP;
383 END IF;
384 END IF;
385
386 x_return_status := l_return_status;
387 x_msg_count := l_msg_count;
388 x_msg_data := l_msg_data;
389 l_attributes_row_table.trim;
390
391 EXCEPTION
392 WHEN fnd_api.g_exc_error THEN
393 x_return_status := fnd_api.g_ret_sts_error;
394 fnd_msg_pub.count_and_get(p_count => x_msg_count,
395 p_data => x_msg_data);
396
397 WHEN fnd_api.g_exc_unexpected_error THEN
398 x_return_status := fnd_api.g_ret_sts_unexp_error;
399 fnd_msg_pub.count_and_get(p_count => x_msg_count,
400 p_data => x_msg_data);
401
402 WHEN OTHERS THEN
403 x_return_status := fnd_api.g_ret_sts_unexp_error;
404 fnd_msg_pub.count_and_get(p_count => x_msg_count,
405 p_data => x_msg_data);
406 END;
407
408 PROCEDURE process_uda(p_party_id IN NUMBER,
409 p_supp_data_level IN VARCHAR2,
410 p_pos_supplier_uda IN pos_supp_uda_obj_tbl,
411 p_create_update_flag IN VARCHAR2,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2) IS
415 l_data_level_1 NUMBER;
416 l_data_level_2 NUMBER;
417 l_data_level_3 NUMBER;
418 l_data_level VARCHAR2(100);
419 v_sql VARCHAR2(2000);
420 l_row_identifier NUMBER := 0;
421
422 BEGIN
423 x_return_status := fnd_api.g_ret_sts_success;
424
425 FOR i IN p_pos_supplier_uda.first .. p_pos_supplier_uda.last LOOP
426 IF p_pos_supplier_uda(i).party_site_id IS NOT NULL AND p_pos_supplier_uda(i)
427 .supplier_site_id IS NOT NULL THEN
428 l_data_level_2 := p_pos_supplier_uda(i).party_site_id;
429 l_data_level_3 := p_pos_supplier_uda(i).supplier_site_id;
430 l_data_level := 'SUPP_ADDR_SITE_LEVEL';
431
432 ELSIF p_pos_supplier_uda(i).party_site_id IS NOT NULL THEN
433 l_data_level_2 := p_pos_supplier_uda(i).party_site_id;
434 l_data_level_3 := NULL;
435 l_data_level := 'SUPP_ADDR_LEVEL';
436
437 ELSE
438 l_data_level_2 := NULL;
439 l_data_level_3 := NULL;
440 l_data_level := 'SUPP_LEVEL';
441
442 END IF;
443 /* Build the Data Object */
444 --l_uda_data_rec := p_uda_rec_tbl(datacntr);
445 l_attributes_data_table := ego_user_attr_data_table();
446 process_attribute_group(l_data_level_1,
447 l_data_level_2,
448 l_data_level_3,
449 l_data_level,
450 p_party_id,
451 p_pos_supplier_uda(i),
452 l_row_identifier,
453 p_create_update_flag,
454 x_return_status,
455 x_msg_count,
456 x_msg_data);
457
458 l_row_identifier := l_row_identifier + 1;
459 /* Increment the row identifier */
460
461 END LOOP;
462
463 /* l_class_code_name_value_pairs := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('CLASSIFICATION_CODE',
464 p_class_code));
465 */
466 /* Call the EGO API to process the attributes based on the mode */
467
468 EXCEPTION
469 WHEN fnd_api.g_exc_error THEN
470 x_return_status := fnd_api.g_ret_sts_error;
471 fnd_msg_pub.count_and_get(p_count => x_msg_count,
472 p_data => x_msg_data);
473
474 WHEN fnd_api.g_exc_unexpected_error THEN
475 x_return_status := fnd_api.g_ret_sts_unexp_error;
476 fnd_msg_pub.count_and_get(p_count => x_msg_count,
477 p_data => x_msg_data);
478 WHEN OTHERS THEN
479 x_return_status := fnd_api.g_ret_sts_unexp_error;
480 fnd_msg_pub.count_and_get(p_count => x_msg_count,
481 p_data => x_msg_data);
482 END;
483
484
485
486 END pos_supplier_uda_bo_pkg;