[Home] [Help]
PACKAGE BODY: APPS.PON_UDA_IMPORT_PKG
Source
1 PACKAGE BODY pon_uda_import_pkg AS
2 /* $Header: PON_UDA_IMPORT_PKG.plb 120.0.12020000.2 2013/02/09 08:13:48 hvutukur noship $ */
3
4 PENDING CONSTANT NUMBER := 1;
5 IN_PROGRESS CONSTANT NUMBER := 2;
6 UDA_ERROR CONSTANT NUMBER := 3;
7 COMPLETED CONSTANT NUMBER := 4;
8 WARNING CONSTANT NUMBER := 5;
9 UDA_EXCEPTION CONSTANT NUMBER := 6;
10
11 PRE_DEFAULTING CONSTANT VARCHAR2(50) := 'PRE_DEFAULTING';
12 DEFAULTING CONSTANT VARCHAR2(50) := 'DEFAULTING';
13 EGO_VALIDATE CONSTANT VARCHAR2(50) := 'EGO_VALIDATE';
14 CUSTOM_VALIDATE CONSTANT VARCHAR2(50) := 'CUSTOM_VALIDATE';
15 LOAD CONSTANT VARCHAR2(50) := 'LOAD';
16
17 g_interface_reference_id NUMBER;
18 g_attr_grp_type VARCHAR2(50);
19
20 --------------------------------------------------------------------------------------------------------------------
21 --Start of Comments
22 --Name: POPULATE_STRUCTURES
23 --Procedure:
24 -- This procedure populates the structures EGO_COL_NAME_VALUE_PAIR_ARRAY, EGO_USER_ATTR_DATA_TABLE,
25 -- EGO_USER_ATTR_ROW_TABLE, EGO_COL_NAME_VALUE_PAIR_ARRAY from the data in the interface table PO_UDA_INTERFACE
26 --Parameters:
27 --IN:
28 -- 1. p_attr_grp_type
29 -- The attribute Group Type (Entity Type)
30 -- 2. p_pk1_value, p_pk2_value, p_pk3_value, p_pk4_value, p_pk5_value
31 -- The Primary Key values for the UDA Attributes
32 --IN/OUT:
33 -- 1. x_base_object_name
34 -- This variable would contain the base object name
35 -- 2. x_pk_column_name_value_pairs
36 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
37 -- 3. x_attr_name_value_pairs
38 -- This variable would contain a table of type EGO_USER_ATTR_DATA_TABLE with the attribute and its value pairs
39 -- 4. x_attributes_row_table
40 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
41 -- 5. x_class_code_name_value_pairs
42 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the template details
43 --OUT:
44 -- 1. x_return_status - Generic Out parameter having return status
45 -- 2. x_errorcode - Generic Out parameter having error code
46 -- 3. x_msg_count - Generic Out parameter having return msg Count
47 -- 4. x_msg_data - Generic Out parameter having return msg
48 --
49 --End of Comments
50 --------------------------------------------------------------------------------------------------------------------
51
52 PROCEDURE POPULATE_STRUCTURES
53 (
54 p_attr_grp_type IN VARCHAR2
55 ,p_pk1_value IN NUMBER
56 ,p_pk2_value IN NUMBER
57 ,p_pk3_value IN NUMBER
58 ,p_pk4_value IN NUMBER
59 ,p_pk5_value IN NUMBER
60 ,x_base_object_name IN OUT NOCOPY VARCHAR2
61 ,x_pk_column_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
62 ,x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
63 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
64 ,x_class_code_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
65 ,x_return_status OUT NOCOPY VARCHAR2
66 ,x_errorcode OUT NOCOPY NUMBER
67 ,x_msg_count OUT NOCOPY NUMBER
68 ,x_msg_data OUT NOCOPY VARCHAR2
69 )
70
71 IS
72 d_progress NUMBER := 0;
73 l_interface_reference_id NUMBER;
74 l_transaction_id NUMBER;
75 l_row_identifier NUMBER;
76 l_attr_group_id NUMBER;
77 l_attr_group_app_id NUMBER;
78 l_attr_group_name VARCHAR2(30);
79 l_data_level_1 VARCHAR2(150);
80 l_data_level_2 VARCHAR2(40);
81 l_data_level_3 VARCHAR2(40);
82 l_transaction_type VARCHAR2(10);
83 l_attr_name VARCHAR2(30);
84 l_attr_value_str VARCHAR2(1000);
85 l_attr_value_num NUMBER;
86 l_attr_value_date DATE;
87 l_attr_disp_value VARCHAR2(1000);
88 l_attr_unit_of_measure VARCHAR2(3);
89 l_user_row_identifier NUMBER;
90 l_uda_template_id NUMBER;
91 l_process_status NUMBER;
92 l_process_phase VARCHAR2(50);
93 l_request_id NUMBER;
94 l_program_id NUMBER;
95 l_program_application_id NUMBER;
96 l_program_update_date DATE;
97 l_last_update_date DATE;
98 l_creation_date DATE;
99 l_last_update_login NUMBER;
100 l_created_by NUMBER;
101 l_last_updated_by NUMBER;
102 l_created_by_name VARCHAR2(100);
103 l_last_updated_by_name VARCHAR2(100);
104 l_interface_source_code VARCHAR2(100);
105 l_interface_source_line_id NUMBER;
106 l_pk1_column_name VARCHAR2(500);
107 l_pk2_column_name VARCHAR2(500);
108 l_pk3_column_name VARCHAR2(500);
109 l_pk4_column_name VARCHAR2(500);
110 l_pk5_column_name VARCHAR2(500);
111 l_pk1_column_type VARCHAR2(500);
112 l_pk2_column_type VARCHAR2(500);
113 l_pk3_column_type VARCHAR2(500);
114 l_pk4_column_type VARCHAR2(500);
115 l_pk5_column_type VARCHAR2(500);
116
117
118 l_count NUMBER := 0;
119 l_attr_category VARCHAR2(500);
120 l_desc_flex_cntxt_name VARCHAR2(500);
121 l_database_col VARCHAR2(500);
122 l_compare_string VARCHAR2(500);
123
124 CURSOR cur_uda_interface_recs
125 (
126 p_pk1_value NUMBER
127 ,p_pk2_value NUMBER
128 ,p_pk3_value NUMBER
129 ,p_pk4_value NUMBER
130 ,p_pk5_value NUMBER
131 ,p_attr_grp_type VARCHAR2
132 )IS
133 SELECT INTERFACE_REFERENCE_ID
134 ,TRANSACTION_ID
135 ,ROW_IDENTIFIER
136 ,ATTR_GROUP_ID
137 ,ATTR_GROUP_APP_ID
138 ,ATTR_GROUP_NAME
139 ,DATA_LEVEL_1
140 ,DATA_LEVEL_2
141 ,DATA_LEVEL_3
142 ,TRANSACTION_TYPE
143 ,ATTR_NAME
144 ,ATTR_VALUE_STR
145 ,ATTR_VALUE_NUM
146 ,ATTR_VALUE_DATE
147 ,ATTR_DISP_VALUE
148 ,ATTR_UNIT_OF_MEASURE
149 ,USER_ROW_IDENTIFIER
150 ,UDA_TEMPLATE_ID
151 ,PROCESS_STATUS
152 ,PROCESS_PHASE
153 ,REQUEST_ID
154 ,PROGRAM_ID
155 ,PROGRAM_APPLICATION_ID
156 ,PROGRAM_UPDATE_DATE
157 ,LAST_UPDATE_DATE
158 ,CREATION_DATE
159 ,LAST_UPDATE_LOGIN
160 ,CREATED_BY
161 ,LAST_UPDATED_BY
162 ,CREATED_BY_NAME
163 ,LAST_UPDATED_BY_NAME
164 ,INTERFACE_SOURCE_CODE
165 ,INTERFACE_SOURCE_LINE_ID
166 FROM PO_UDA_INTERFACE
167 WHERE PROCESS_STATUS = PON_UDA_IMPORT_PKG.IN_PROGRESS
168 AND NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
169 AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
170 AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
171 AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
172 AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
173 AND ATTR_GROUP_TYPE = p_attr_grp_type
174 ORDER BY ATTR_GROUP_ID;
175
176 l_err_txt VARCHAR2(2000);
177
178
179 BEGIN
180 d_progress := 10;
181 PON_UDA_IMPORT_PKG.DEBUG('Starting PON_UDA_IMPORT_PKG.POPULATE_STRUCTURES with parameters' , d_progress);
182 PON_UDA_IMPORT_PKG.DEBUG('p_attr_grp_type : ' || p_attr_grp_type, d_progress);
183 PON_UDA_IMPORT_PKG.DEBUG('p_pk1_value : ' || p_pk1_value, d_progress);
184 PON_UDA_IMPORT_PKG.DEBUG('p_pk2_value : ' || p_pk2_value, d_progress);
185 PON_UDA_IMPORT_PKG.DEBUG('p_pk3_value : ' || p_pk2_value, d_progress);
186 PON_UDA_IMPORT_PKG.DEBUG('p_pk4_value : ' || p_pk4_value, d_progress);
187 PON_UDA_IMPORT_PKG.DEBUG('p_pk5_value : ' || p_pk5_value, d_progress);
188 PON_UDA_IMPORT_PKG.DEBUG('x_base_object_name : ' || x_base_object_name, d_progress);
189 PON_UDA_IMPORT_PKG.DEBUG('x_pk_column_name_value_pairs is as follows :', d_progress);
190 PON_UDA_IMPORT_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
191 PON_UDA_IMPORT_PKG.DEBUG('x_attr_name_value_pairs is as follows :', d_progress);
192 PON_UDA_IMPORT_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
193 PON_UDA_IMPORT_PKG.DEBUG('x_attributes_row_table is as follows :', d_progress);
194 PON_UDA_IMPORT_PKG.DEBUG(x_attributes_row_table, d_progress);
195 PON_UDA_IMPORT_PKG.DEBUG('x_class_code_name_value_pairs is as follows :', d_progress);
196 PON_UDA_IMPORT_PKG.DEBUG(x_class_code_name_value_pairs, d_progress);
197 d_progress := 20;
198
199 BEGIN
200 d_progress := 30;
201 SELECT INTERFACE_REFERENCE_ID
202 INTO g_interface_reference_id
203 FROM PO_UDA_INTERFACE
204 WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
205 AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
206 AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
207 AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
208 AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
209 AND ROWNUM < 2;
210 d_progress := 40;
211 PON_UDA_IMPORT_PKG.DEBUG('INTERFACE_REFERENCE_ID : ' || g_interface_reference_id, d_progress);
212 EXCEPTION
213 WHEN OTHERS THEN
214 d_progress := 50;
215 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the INTERFACE_REFERENCE_ID', d_progress);
216 RAISE;
217 END;
218
219 d_progress := 60;
220 x_base_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(p_attr_grp_type).l_object_name;
221 PON_UDA_IMPORT_PKG.DEBUG('The Base object is ' || x_base_object_name, d_progress);
222 d_progress := 70;
223
224 PON_UDA_IMPORT_PKG.DEBUG('Before PON_UDA_IMPORT_PKG.CONSTRUCT_PK_OBJECT' , d_progress);
225 PON_UDA_IMPORT_PKG.CONSTRUCT_PK_OBJECT(
226 x_base_object_name
227 ,p_pk1_value
228 ,p_pk2_value
229 ,p_pk3_value
230 ,p_pk4_value
231 ,p_pk5_value
232 ,x_pk_column_name_value_pairs
233 );
234 d_progress := 80;
235 PON_UDA_IMPORT_PKG.DEBUG('Before PON_UDA_IMPORT_PKG.CONSTRUCT_PK_OBJECT' , d_progress);
236 PON_UDA_IMPORT_PKG.DEBUG('x_pk_column_name_value_pairs is as follows :', d_progress);
237 PON_UDA_IMPORT_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
238
239 OPEN cur_uda_interface_recs (p_pk1_value, p_pk2_value, p_pk3_value, p_pk4_value, p_pk5_value, p_attr_grp_type);
240 d_progress := 90;
241 LOOP
242 d_progress := 100;
243 FETCH cur_uda_interface_recs
244 INTO l_interface_reference_id
245 ,l_transaction_id
246 ,l_row_identifier
247 ,l_attr_group_id
248 ,l_attr_group_app_id
249 ,l_attr_group_name
250 ,l_data_level_1
251 ,l_data_level_2
252 ,l_data_level_3
253 ,l_transaction_type
254 ,l_attr_name
255 ,l_attr_value_str
256 ,l_attr_value_num
257 ,l_attr_value_date
258 ,l_attr_disp_value
259 ,l_attr_unit_of_measure
260 ,l_user_row_identifier
261 ,l_uda_template_id
262 ,l_process_status
263 ,l_process_phase
264 ,l_request_id
265 ,l_program_id
266 ,l_program_application_id
267 ,l_program_update_date
268 ,l_last_update_date
269 ,l_creation_date
270 ,l_last_update_login
271 ,l_created_by
272 ,l_last_updated_by
273 ,l_created_by_name
274 ,l_last_updated_by_name
275 ,l_interface_source_code
276 ,l_interface_source_line_id;
277 EXIT WHEN cur_uda_interface_recs%NOTFOUND;
278
279 d_progress := 110;
280 PON_UDA_IMPORT_PKG.DEBUG('Details read from the interface table are as follows :', d_progress);
281 PON_UDA_IMPORT_PKG.DEBUG('l_interface_reference_id : ' || l_interface_reference_id, d_progress);
282 PON_UDA_IMPORT_PKG.DEBUG('l_transaction_id : ' || l_transaction_id, d_progress);
283 PON_UDA_IMPORT_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
284 PON_UDA_IMPORT_PKG.DEBUG('l_attr_group_id : ' || l_attr_group_id, d_progress);
285 PON_UDA_IMPORT_PKG.DEBUG('l_attr_group_app_id : ' || l_attr_group_app_id, d_progress);
286 PON_UDA_IMPORT_PKG.DEBUG('l_attr_group_name : ' || l_attr_group_name, d_progress);
287 PON_UDA_IMPORT_PKG.DEBUG('l_data_level_1 : ' || l_data_level_1, d_progress);
288 PON_UDA_IMPORT_PKG.DEBUG('l_data_level_2 : ' || l_data_level_2, d_progress);
289 PON_UDA_IMPORT_PKG.DEBUG('l_data_level_3 : ' || l_data_level_3, d_progress);
290 PON_UDA_IMPORT_PKG.DEBUG('l_transaction_type : ' || l_transaction_type, d_progress);
291 PON_UDA_IMPORT_PKG.DEBUG('l_attr_name : ' || l_attr_name, d_progress);
292 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_str : ' || l_attr_value_str, d_progress);
293 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_num : ' || l_attr_value_num, d_progress);
294 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_date : ' || l_attr_value_date, d_progress);
295 PON_UDA_IMPORT_PKG.DEBUG('l_attr_disp_value : ' || l_attr_disp_value, d_progress);
296 PON_UDA_IMPORT_PKG.DEBUG('l_attr_unit_of_measure : ' || l_attr_unit_of_measure, d_progress);
297 PON_UDA_IMPORT_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
298 PON_UDA_IMPORT_PKG.DEBUG('l_uda_template_id : ' || l_uda_template_id, d_progress);
299 PON_UDA_IMPORT_PKG.DEBUG('l_process_status : ' || l_process_status, d_progress);
300 PON_UDA_IMPORT_PKG.DEBUG('l_process_phase : ' || l_process_phase, d_progress);
301 PON_UDA_IMPORT_PKG.DEBUG('l_request_id : ' || l_request_id, d_progress);
302 PON_UDA_IMPORT_PKG.DEBUG('l_program_id : ' || l_program_id, d_progress);
303 PON_UDA_IMPORT_PKG.DEBUG('l_program_application_id : ' || l_program_application_id, d_progress);
304 PON_UDA_IMPORT_PKG.DEBUG('l_program_update_date : ' || l_program_update_date, d_progress);
305 PON_UDA_IMPORT_PKG.DEBUG('l_last_update_date : ' || l_last_update_date, d_progress);
306 PON_UDA_IMPORT_PKG.DEBUG('l_creation_date : ' || l_creation_date, d_progress);
307 PON_UDA_IMPORT_PKG.DEBUG('l_last_updated_by : ' || l_last_updated_by, d_progress);
308 PON_UDA_IMPORT_PKG.DEBUG('l_created_by_name : ' || l_created_by_name, d_progress);
309 PON_UDA_IMPORT_PKG.DEBUG('l_last_updated_by_name : ' || l_last_updated_by_name, d_progress);
310 PON_UDA_IMPORT_PKG.DEBUG('l_interface_source_code : ' || l_interface_source_code, d_progress);
311 PON_UDA_IMPORT_PKG.DEBUG('l_interface_source_line_id : ' || l_interface_source_line_id, d_progress);
312 d_progress := 120;
313
314 PON_UDA_IMPORT_PKG.DEBUG('Before constructing Attribute row Array', d_progress);
315 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_row_identifier ' || l_row_identifier, d_progress);
316 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_attr_group_id ' || l_attr_group_id, d_progress);
317 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_attr_group_app_id ' || l_attr_group_app_id, d_progress);
318 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: p_attr_grp_type ' || p_attr_grp_type, d_progress);
319 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_attr_group_name ' || l_attr_group_name, d_progress);
320 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_data_level_1 ' || l_data_level_1, d_progress);
321 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_data_level_2 ' || l_data_level_2, d_progress);
322 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_data_level_3 ' || l_data_level_3, d_progress);
323 PON_UDA_IMPORT_PKG.DEBUG('Attribute row Array: l_transaction_type ' || l_transaction_type, d_progress);
324 d_progress := 130;
325 x_attributes_row_table.EXTEND(1);
326 x_attributes_row_table(l_count + 1) := ego_user_attr_row_obj
327 (
328 l_row_identifier
329 ,l_attr_group_id
330 ,l_attr_group_app_id
331 ,p_attr_grp_type
332 ,l_attr_group_name
333 ,l_data_level_1
334 ,l_data_level_2
335 ,l_data_level_3
336 ,NULL
337 ,NULL
338 ,NULL
339 ,l_transaction_type
340 );
341 d_progress := 140;
342 PON_UDA_IMPORT_PKG.DEBUG('After constructing Attribute row Array', d_progress);
343 PON_UDA_IMPORT_PKG.DEBUG('Before constructing Attribute Name Values Array', d_progress);
344 PON_UDA_IMPORT_PKG.DEBUG('l_row_identifier ' || l_row_identifier, d_progress);
345 PON_UDA_IMPORT_PKG.DEBUG('l_attr_name ' || l_attr_name, d_progress);
346 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_str ' || l_attr_value_str, d_progress);
347 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_num ' || l_attr_value_num, d_progress);
348 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_date ' || l_attr_value_date, d_progress);
349 PON_UDA_IMPORT_PKG.DEBUG('l_attr_disp_value ' || l_attr_disp_value, d_progress);
350 PON_UDA_IMPORT_PKG.DEBUG('l_attr_unit_of_measure ' || l_attr_unit_of_measure, d_progress);
351 PON_UDA_IMPORT_PKG.DEBUG('l_user_row_identifier ' || l_user_row_identifier, d_progress);
352 d_progress := 150;
353
354 SELECT ATTRIBUTE_CATEGORY INTO l_attr_category
355 FROM po_uda_ag_template_usages
356 WHERE TEMPLATE_ID=l_uda_template_id AND
357 ATTRIBUTE_GROUP_ID = l_attr_group_id;
358
359 IF l_attr_category = 'PRICING' THEN
360 SELECT distinct agtl.DESCRIPTIVE_FLEX_CONTEXT_NAME, attr.DATABASE_COLUMN
361 INTO l_desc_flex_cntxt_name, l_database_col
362 FROM FND_DESCR_FLEX_CONTEXTS_tl agtl,
363 FND_DESCR_FLEX_CONTEXTS ag,
364 EGO_ATTRS_V attr,
365 po_uda_ag_templates t,
366 po_uda_ag_template_usages u,
367 ego_fnd_dsc_flx_ctx_ext egoag
368 WHERE attr.ATTR_GROUP_TYPE = ag.DESCRIPTIVE_FLEXFIELD_NAME
369 AND attr.ATTR_GROUP_NAME = ag.DESCRIPTIVE_FLEX_CONTEXT_CODE
370 AND ag.DESCRIPTIVE_FLEXFIELD_NAME = agtl.DESCRIPTIVE_FLEXFIELD_NAME
371 AND ag.DESCRIPTIVE_FLEX_CONTEXT_CODE = agtl.DESCRIPTIVE_FLEX_CONTEXT_CODE
372 AND agtl.LANGUAGE = UserEnv('LANG')
373 AND t.template_id = u.template_id
374 AND egoag.attr_group_id = u.attribute_group_id
375 AND egoag.DESCRIPTIVE_FLEXFIELD_NAME = ag.DESCRIPTIVE_FLEXFIELD_NAME
376 AND egoag.DESCRIPTIVE_FLEX_CONTEXT_CODE = ag.DESCRIPTIVE_FLEX_CONTEXT_CODE
377 AND t.template_id = l_uda_template_id
378 AND u.ATTRIBUTE_CATEGORY = l_attr_category
379 AND egoag.attr_group_id = l_attr_group_id
380 AND attr.ATTR_NAME = l_attr_name;
381
382 SELECT REGEXP_SUBSTR(l_database_col,'N[^N]+R') INTO l_compare_string FROM dual;
383
384 IF (l_compare_string = 'N_EXT_ATTR' AND l_attr_value_num IS NULL ) THEN
385 l_attr_value_num := 0;
386 END IF;
387 END IF;
388
389 PON_UDA_IMPORT_PKG.DEBUG('l_row_identifier ' || l_row_identifier, d_progress);
390 PON_UDA_IMPORT_PKG.DEBUG('l_attr_name ' || l_attr_name, d_progress);
391 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_str ' || l_attr_value_str, d_progress);
392 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_num ' || l_attr_value_num, d_progress);
393 PON_UDA_IMPORT_PKG.DEBUG('l_attr_value_date ' || l_attr_value_date, d_progress);
394 PON_UDA_IMPORT_PKG.DEBUG('l_attr_disp_value ' || l_attr_disp_value, d_progress);
395 PON_UDA_IMPORT_PKG.DEBUG('l_attr_unit_of_measure ' || l_attr_unit_of_measure, d_progress);
396 PON_UDA_IMPORT_PKG.DEBUG('l_user_row_identifier ' || l_user_row_identifier, d_progress);
397
398 x_attr_name_value_pairs.EXTEND(1);
399 x_attr_name_value_pairs(l_count + 1) := ego_user_attr_data_obj
400 (
401 l_row_identifier
402 ,l_attr_name
403 ,l_attr_value_str
404 ,l_attr_value_num
405 ,l_attr_value_date
406 ,l_attr_disp_value
407 ,l_attr_unit_of_measure
408 ,l_user_row_identifier
409 );
410 d_progress := 160;
411 PON_UDA_IMPORT_PKG.DEBUG('After constructing Attribute Name Values Array', d_progress);
412 l_count := l_count + 1;
413 d_progress := 170;
414 END LOOP;
415 d_progress := 180;
416 CLOSE cur_uda_interface_recs;
417 d_progress := 190;
418
419 PON_UDA_IMPORT_PKG.DEBUG('Before constructing Class codes Array', d_progress);
420 x_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
421 (
422 EGO_COL_NAME_VALUE_PAIR_OBJ
423 (
424 'UDA_TEMPLATE_ID'
425 ,l_uda_template_id || ''
426 )
427 );
428 d_progress := 200;
429 PON_UDA_IMPORT_PKG.DEBUG('After constructing Class codes Array with template id : ' || l_uda_template_id, d_progress);
430 x_return_status := FND_API.G_RET_STS_SUCCESS;
431 EXCEPTION
432 WHEN OTHERS THEN
433 l_err_txt := 'Exception in POPULATE_STRUCTURES with ERROR : ' || SQLERRM;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 PON_UDA_IMPORT_PKG.HANDLE_ERROR
436 (
437 p_msg => l_err_txt
438 ,p_msg_code => 'UNEXPECTED_POPULATE_STRUCTURES'
439 ,p_attr_grp_id => l_attr_group_id
440 );
441 END POPULATE_STRUCTURES;
442
443
444 --------------------------------------------------------------------------------------------------------------------
445 --Start of Comments
446 --Name: POPULATE_REMAINING_ATTRIBUTES
447 --Procedure:
448 -- This procedure populates the structures EGO_USER_ATTR_DATA_TABLE, EGO_USER_ATTR_ROW_TABLE
449 -- from the table FND_DESCR_FLEX_COLUMN_USAGES. It would add elements for all the attributes present in
450 -- the attribute groups present in the variable x_attributes_row_table of type EGO_USER_ATTR_ROW_TABLE
451 -- and not present in variable x_attr_name_value_pairs of type EGO_USER_ATTR_DATA_TABLE already
452 -- with NULL values
453 --Parameters:
454 --IN/OUT:
455 -- 1. x_attr_name_value_pairs
456 -- This variable would contain a table of type EGO_USER_ATTR_DATA_TABLE with the attribute and its value pairs
457 -- 2. x_attributes_row_table
458 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
459 --OUT:
460 -- 1. x_return_status - Generic Out parameter having return status
461 -- 2. x_errorcode - Generic Out parameter having error code
462 -- 3. x_msg_count - Generic Out parameter having return msg Count
463 -- 4. x_msg_data - Generic Out parameter having return msg
464 --
465 --End of Comments
466 --------------------------------------------------------------------------------------------------------------------
467 PROCEDURE POPULATE_REMAINING_ATTRIBUTES
468 (
469 x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
470 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
471 ,x_return_status OUT NOCOPY VARCHAR2
472 ,x_errorcode OUT NOCOPY NUMBER
473 ,x_msg_count OUT NOCOPY NUMBER
474 ,x_msg_data OUT NOCOPY VARCHAR2
475 )
476 IS
477 d_progress NUMBER := 0;
478 l_attr_row_table_index NUMBER;
479 l_attr_row_obj EGO_USER_ATTR_ROW_OBJ;
480 l_attr_name_val EGO_USER_ATTR_DATA_OBJ;
481 l_row_identifier NUMBER;
482 l_attr_group_type VARCHAR2(200);
483 l_attr_group_name VARCHAR2(200);
484 l_attr_name VARCHAR2(200);
485 l_attr_name_tab VARCHAR2(200);
486 l_user_row_identifier NUMBER;
487 l_attr_name_value_index NUMBER;
488 l_attr_row_identifier NUMBER;
489 l_found BOOLEAN := FALSE;
490
491 l_attr_group_id NUMBER;
492 l_uda_template_id NUMBER;
493 l_attr_col_name VARCHAR2(500);
494 l_attr_category VARCHAR2(500);
495 l_attr_value_num VARCHAR2(500);
496 l_compare_string VARCHAR2(500);
497
498 CURSOR cur_uda_attributes
499 (
500 p_attr_grp_type VARCHAR2
501 ,p_attr_grp_name VARCHAR2
502 )IS
503 SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
504 FROM FND_DESCR_FLEX_COLUMN_USAGES
505 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
506 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
507
508 l_err_txt VARCHAR2(2000);
509
510 BEGIN
511 d_progress := 10;
512 PON_UDA_IMPORT_PKG.DEBUG('Starting POPULATE_REMAINING_ATTRIBUTES', d_progress);
513 PON_UDA_IMPORT_PKG.DEBUG('x_attr_name_value_pairs is as follows :', d_progress);
514 PON_UDA_IMPORT_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
515 PON_UDA_IMPORT_PKG.DEBUG('x_attributes_row_table is as follows :', d_progress);
516 PON_UDA_IMPORT_PKG.DEBUG(x_attributes_row_table, d_progress);
517 d_progress := 20;
518
519 PON_UDA_IMPORT_PKG.DEBUG('Looping for all the Attribute Groups', d_progress);
520 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0) THEN
521 d_progress := 30;
522 l_attr_row_table_index := x_attributes_row_table.FIRST;
523 d_progress := 40;
524 WHILE (l_attr_row_table_index IS NOT NULL AND l_attr_row_table_index <= x_attributes_row_table.LAST)
525 LOOP
526 d_progress := 50;
527 IF x_attributes_row_table(l_attr_row_table_index) IS NOT NULL THEN
528 d_progress := 60;
529 l_attr_row_obj := x_attributes_row_table(l_attr_row_table_index);
530 d_progress := 70;
531 l_row_identifier := l_attr_row_obj.ROW_IDENTIFIER;
532 l_attr_group_type := l_attr_row_obj.ATTR_GROUP_TYPE;
533 l_attr_group_name := l_attr_row_obj.ATTR_GROUP_NAME;
534
535 SELECT ATTR_GROUP_ID INTO l_attr_group_id
536 FROM ego_fnd_dsc_flx_ctx_ext
537 WHERE DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type AND
538 DESCRIPTIVE_FLEX_CONTEXT_CODE = l_attr_group_name;
539
540 SELECT TEMPLATE_ID INTO l_uda_template_id
541 FROM po_uda_ag_templates
542 WHERE FUNCTIONAL_AREA='SOURCING' AND
543 ENTITY_CODE=l_attr_group_type;
544
545 SELECT ATTRIBUTE_CATEGORY INTO l_attr_category
546 FROM po_uda_ag_template_usages
547 WHERE TEMPLATE_ID=l_uda_template_id AND
548 ATTRIBUTE_GROUP_ID = l_attr_group_id;
549
550 d_progress := 80;
551 PON_UDA_IMPORT_PKG.DEBUG('AG ROW_IDENTIFIER : ' || l_row_identifier, d_progress);
552 PON_UDA_IMPORT_PKG.DEBUG('AG ATTR_GROUP_TYPE : ' || l_attr_group_type, d_progress);
553 PON_UDA_IMPORT_PKG.DEBUG('AG ATTR_GROUP_NAME : ' || l_attr_group_name, d_progress);
554 PON_UDA_IMPORT_PKG.DEBUG('AG l_attr_group_id : ' || l_attr_group_id, d_progress);
555 PON_UDA_IMPORT_PKG.DEBUG('AG l_uda_template_id : ' || l_uda_template_id, d_progress);
556 PON_UDA_IMPORT_PKG.DEBUG('AG l_attr_category : ' || l_attr_category, d_progress);
557 d_progress := 90;
558 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
559 LOOP
560 d_progress := 100;
561 FETCH cur_uda_attributes
562 INTO l_attr_name, l_attr_col_name ;
563 EXIT WHEN cur_uda_attributes%NOTFOUND;
564
565 d_progress := 110;
566 BEGIN
567 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
568 INTO l_user_row_identifier
569 FROM DUAL;
570 d_progress := 110;
571 PON_UDA_IMPORT_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
572 EXCEPTION
573 WHEN OTHERS THEN
574 d_progress := 120;
575 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_user_row_identifier', d_progress);
576 RAISE;
577 END;
578 d_progress := 130;
579 l_attr_name_value_index := x_attr_name_value_pairs.FIRST;
580 PON_UDA_IMPORT_PKG.DEBUG('Looping for all the Attributes for the attribute Group :' || l_attr_group_name, d_progress);
581 d_progress := 140;
582 IF x_attr_name_value_pairs(l_attr_name_value_index) IS NOT NULL THEN
583 d_progress := 150;
584 WHILE (l_attr_name_value_index IS NOT NULL AND l_attr_name_value_index <= x_attr_name_value_pairs.LAST)
585 LOOP
586 d_progress := 150;
587 l_attr_name_val := x_attr_name_value_pairs(l_attr_name_value_index);
588 l_attr_row_identifier := l_attr_name_val.ROW_IDENTIFIER;
589 l_attr_name_tab := l_attr_name_val.ATTR_NAME;
590
591 d_progress := 160;
592 PON_UDA_IMPORT_PKG.DEBUG('Attribute ROW_IDENTIFIER : ' || l_attr_row_identifier, d_progress);
593 PON_UDA_IMPORT_PKG.DEBUG('Attribute ATTR_NAME : ' || l_attr_name_tab, d_progress);
594 d_progress := 170;
595 IF l_row_identifier = l_attr_row_identifier AND l_attr_name = l_attr_name_tab THEN
596 l_found := TRUE;
597 d_progress := 180;
598 PON_UDA_IMPORT_PKG.DEBUG('Attribute ATTR_NAME : ' || l_attr_name_tab || ' is found ', d_progress);
599 EXIT;
600 END IF;
601 d_progress := 190;
602 l_attr_name_value_index := x_attr_name_value_pairs.NEXT(l_attr_name_value_index);
603 END LOOP;
604 d_progress := 200;
605 IF l_found = FALSE THEN
606 d_progress := 210;
607 PON_UDA_IMPORT_PKG.DEBUG('Attribute not found, creating the attribute', d_progress);
608 PON_UDA_IMPORT_PKG.DEBUG('Attr l_attr_col_name : ' || l_attr_col_name, d_progress);
609 IF l_attr_category = 'PRICING' THEN
610 SELECT REGEXP_SUBSTR(l_attr_col_name,'N[^N]+R') INTO l_compare_string FROM dual;
611 PON_UDA_IMPORT_PKG.DEBUG('Attr l_compare_string : ' || l_compare_string, d_progress);
612 IF (l_compare_string = 'N_EXT_ATTR') THEN
613 l_attr_value_num := 0;
614 ELSE
615 l_attr_value_num := NULL;
616 END IF;
617 END IF;
618 PON_UDA_IMPORT_PKG.DEBUG('Attr l_attr_value_num : ' || l_attr_value_num, d_progress);
619
620
621
622 x_attr_name_value_pairs.EXTEND(1);
623 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
624 (
625 l_row_identifier
626 ,l_attr_name
627 ,NULL
628 ,l_attr_value_num
629 ,NULL
630 ,NULL
631 ,NULL
632 ,l_user_row_identifier
633 );
634 d_progress := 220;
635 ELSE
636 d_progress := 230;
637 l_found := FALSE;
638 l_attr_value_num := NULL;
639 END IF;
640 d_progress := 240;
641 ELSE
642 d_progress := 250;
643 PON_UDA_IMPORT_PKG.DEBUG('Attr l_attr_col_name 1 : ' || l_attr_col_name, d_progress);
644 IF l_attr_category = 'PRICING' THEN
645 SELECT REGEXP_SUBSTR(l_attr_col_name,'N[^N]+R') INTO l_compare_string FROM dual;
646 PON_UDA_IMPORT_PKG.DEBUG('Attr l_compare_string 1 : ' || l_compare_string, d_progress);
647 IF (l_compare_string = 'N_EXT_ATTR') THEN
648 l_attr_value_num := 0;
649 ELSE
650 l_attr_value_num := NULL;
651 END IF;
652 END IF;
653 PON_UDA_IMPORT_PKG.DEBUG('Attr l_attr_value_num 1 : ' || l_attr_value_num, d_progress);
654 PON_UDA_IMPORT_PKG.DEBUG('Attribute not found, creating the attribute', d_progress);
655 x_attr_name_value_pairs.EXTEND(1);
656 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
657 (
658 l_row_identifier
659 ,l_attr_name
660 ,NULL
661 ,l_attr_value_num
662 ,NULL
663 ,NULL
664 ,NULL
665 ,l_user_row_identifier
666 );
667 d_progress := 260;
668 END IF;
669 END LOOP;
670 d_progress := 270;
671 CLOSE cur_uda_attributes;
672 d_progress := 280;
673 END IF;
674 l_attr_row_table_index := x_attributes_row_table.NEXT(l_attr_row_table_index);
675 d_progress := 290;
676 END LOOP;
677 END IF;
678 d_progress := 300;
679 x_return_status := FND_API.G_RET_STS_SUCCESS;
680 EXCEPTION
681 WHEN OTHERS THEN
682 l_err_txt := 'Exception in POPULATE_REMAINING_ATTRIBUTES with ERROR : ' || SQLERRM;
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 PON_UDA_IMPORT_PKG.HANDLE_ERROR
685 (
686 p_msg => l_err_txt
687 ,p_msg_code => 'UNEXPECTED_POPULATE_REMAINING_ATTRIBUTES'
688 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
689 );
690 END POPULATE_REMAINING_ATTRIBUTES;
691
692 --------------------------------------------------------------------------------------------------------------------
693 --Start of Comments
694 --Name: POPULATE_EMPTY_AGS
695 --Procedure:
696 -- This procedure populates the structures EGO_USER_ATTR_DATA_TABLE, EGO_USER_ATTR_ROW_TABLE
697 -- with the details of the attribute groups in x_attributes_row_table and attributes in
698 -- x_attr_name_value_pairs which are not present in x_attributes_row_table and present in
699 -- x_po_uda_usage_object_array.
700 --Parameters:
701 --IN:
702 -- 1. p_pk_column_name_value_pairs
703 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
704 -- 2. p_external_attr_value_pairs
705 -- This variable wsould contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
706 --IN/OUT:
707 -- 1. x_po_uda_usage_object_array
708 -- This variable of type PO_UDA_USAGE_OBJECT_ARRAY contains data of hhe usages.
709 -- 2. x_attr_name_value_pairs
710 -- This variable would contain a table of type EGO_USER_ATTR_DATA_TABLE with the attribute and its value pairs
711 -- 3. x_attributes_row_table
712 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
713 --OUT:
714 -- 1. x_return_status - Generic Out parameter having return status
715 -- 2. x_errorcode - Generic Out parameter having error code
716 -- 3. x_msg_count - Generic Out parameter having return msg Count
717 -- 4. x_msg_data - Generic Out parameter having return msg
718 --
719 --End of Comments
720 --------------------------------------------------------------------------------------------------------------------
721 PROCEDURE POPULATE_EMPTY_AGS
722 (
723 p_template_id IN NUMBER
724 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
725 ,p_external_attr_value_pairs IN EGO_COL_NAME_VALUE_PAIR_TABLE
726 ,p_address_lookup_type IN VARCHAR2 DEFAULT NULL
727 ,x_po_uda_usage_object_array IN OUT NOCOPY PO_UDA_USAGE_OBJECT_ARRAY
728 ,x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
729 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
730 ,x_return_status OUT NOCOPY VARCHAR2
731 ,x_errorcode OUT NOCOPY NUMBER
732 ,x_msg_count OUT NOCOPY NUMBER
733 ,x_msg_data OUT NOCOPY VARCHAR2
734 )
735 IS
736 d_progress NUMBER := 0;
737 l_po_uda_usage_object PO_UDA_USAGE_OBJECT;
738 l_attr_group_table PO_TBL_NUMBER;
739 l_context_object_index NUMBER;
740 l_attr_group_id_index NUMBER;
741 l_pk_col_index NUMBER;
742 l_attr_group_id NUMBER;
743 l_base_table VARCHAR2(200);
744 l_attr_group_type VARCHAR2(200);
745 l_attr_group_name VARCHAR2(200);
746 l_data_level VARCHAR2(200);
747 l_sql_query VARCHAR2(4000);
748 l_check NUMBER;
749 v_query_hdl NUMBER;
750 l_pk_name_value_pair EGO_COL_NAME_VALUE_PAIR_OBJ;
751 l_attr_row_obj EGO_USER_ATTR_ROW_OBJ;
752 l_rows_processed NUMBER;
753 l_attr_grp_loc_index NUMBER;
754 l_row_identifier NUMBER;
755 l_user_row_identifier NUMBER;
756 l_attr_name VARCHAR2(200);
757 l_present NUMBER := 0;
758
759 CURSOR cur_uda_attributes
760 (
761 p_attr_grp_type VARCHAR2
762 ,p_attr_grp_name VARCHAR2
763 )IS
764 SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
765 FROM FND_DESCR_FLEX_COLUMN_USAGES
766 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
767 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
768
769 CURSOR cur_address
770 (
771 p_address_lookup_type VARCHAR2
772 )IS
773 SELECT DISTINCT LOOKUP_CODE , TAG
774 FROM FND_LOOKUP_VALUES
775 WHERE LOOKUP_TYPE = p_address_lookup_type
776 ORDER BY TAG;
777
778 l_attr_col VARCHAR2(200);
779 l_attr_val VARCHAR2(200);
780
781 l_xd_present NUMBER;
782
783 l_err_txt VARCHAR2(2000);
784
785
786 BEGIN
787 d_progress := 10;
788 PON_UDA_IMPORT_PKG.DEBUG('Starting POPULATE_EMPTY_AGS', d_progress);
789 PON_UDA_IMPORT_PKG.DEBUG('p_template_id : ' || p_template_id, d_progress);
790 PON_UDA_IMPORT_PKG.DEBUG('p_pk_column_name_value_pairs is as follows', d_progress);
791 PON_UDA_IMPORT_PKG.DEBUG(p_pk_column_name_value_pairs, d_progress);
792 PON_UDA_IMPORT_PKG.DEBUG('p_external_attr_value_pairs is as follows', d_progress);
793 PON_UDA_IMPORT_PKG.DEBUG(p_external_attr_value_pairs, d_progress);
794 PON_UDA_IMPORT_PKG.DEBUG('p_address_lookup_type : ' || p_address_lookup_type, d_progress);
795 PON_UDA_IMPORT_PKG.DEBUG('x_po_uda_usage_object_array is as follows', d_progress);
796 PON_UDA_IMPORT_PKG.DEBUG(x_po_uda_usage_object_array, d_progress);
797 PON_UDA_IMPORT_PKG.DEBUG('x_attr_name_value_pairs is as follows', d_progress);
798 PON_UDA_IMPORT_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
799 PON_UDA_IMPORT_PKG.DEBUG('x_attributes_row_table is as follows', d_progress);
800 PON_UDA_IMPORT_PKG.DEBUG(x_attributes_row_table, d_progress);
801 d_progress := 20;
802
803 l_attr_group_table := PO_TBL_NUMBER();
804 d_progress := 30;
805 IF (x_po_uda_usage_object_array IS NOT NULL AND x_po_uda_usage_object_array.COUNT > 0) THEN
806 d_progress := 40;
807 PON_UDA_IMPORT_PKG.DEBUG('x_po_uda_usage_object_array.COUNT :' || x_po_uda_usage_object_array.COUNT, d_progress);
808 l_context_object_index := x_po_uda_usage_object_array.FIRST;
809 d_progress := 50;
810
811 WHILE (l_context_object_index IS NOT NULL AND l_context_object_index <= x_po_uda_usage_object_array.LAST)
812 LOOP
813 d_progress := 60;
814 IF x_po_uda_usage_object_array(l_context_object_index) IS NOT NULL THEN
815 d_progress := 70;
816 l_po_uda_usage_object := x_po_uda_usage_object_array(l_context_object_index);
817 l_attr_group_table := l_po_uda_usage_object.Attr_group_Id;
818 d_progress := 80;
819
820 IF (l_attr_group_table IS NOT NULL AND l_attr_group_table.COUNT > 0) THEN
821 d_progress := 100;
822 l_attr_group_id_index := l_attr_group_table.FIRST;
823 d_progress := 110;
824 WHILE (l_attr_group_id_index IS NOT NULL AND l_attr_group_id_index <= l_attr_group_table.LAST)
825 LOOP
826 d_progress := 120;
827
828 IF l_attr_group_table(l_attr_group_id_index) IS NOT NULL THEN
829 l_attr_group_id := l_attr_group_table(l_attr_group_id_index);
830 PON_UDA_IMPORT_PKG.DEBUG(' l_attr_group_id : ' || l_attr_group_id, d_progress);
831 d_progress := 130;
832 BEGIN
833 d_progress := 140;
834 SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
835 INTO l_attr_group_type, l_attr_group_name
836 FROM EGO_FND_DSC_FLX_CTX_EXT
837 WHERE ATTR_GROUP_ID = l_attr_group_id;
838
839 d_progress := 150;
840 PON_UDA_IMPORT_PKG.DEBUG('l_attr_group_type : ' || l_attr_group_type, d_progress);
841 PON_UDA_IMPORT_PKG.DEBUG('l_attr_group_name : ' || l_attr_group_name, d_progress);
842 EXCEPTION
843 WHEN OTHERS THEN
844 d_progress := 150;
845 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_attr_group_type, l_attr_group_name', d_progress);
846 RAISE;
847 END;
848
849 d_progress := 160;
850 l_base_table := PO_UDA_DATA_UTIL.g_object_dff_tl(l_attr_group_type).l_object_ext_b_table;
851
852 l_sql_query := 'SELECT 1 FROM ' || l_base_table;
853 l_sql_query := l_sql_query || ' WHERE ATTR_GROUP_ID = ' || l_attr_group_id;
854 d_progress := 170;
855
856 IF (p_pk_column_name_value_pairs IS NOT NULL AND p_pk_column_name_value_pairs.COUNT > 0) THEN
857 l_pk_col_index := p_pk_column_name_value_pairs.FIRST;
858 d_progress := 180;
859 WHILE (l_pk_col_index IS NOT NULL AND l_pk_col_index <= p_pk_column_name_value_pairs.LAST)
860 LOOP
861 d_progress := 190;
862 IF p_pk_column_name_value_pairs(l_pk_col_index) IS NOT NULL THEN
863 d_progress := 200;
864 l_pk_name_value_pair := p_pk_column_name_value_pairs(l_pk_col_index);
865 l_sql_query := l_sql_query || ' AND ' || l_pk_name_value_pair.NAME || ' = ' || l_pk_name_value_pair.VALUE; -- PK CHECK
866 d_progress := 210;
867 END IF;
868 d_progress := 220;
869 l_pk_col_index := p_pk_column_name_value_pairs.NEXT(l_pk_col_index);
870 d_progress := 230;
871 END LOOP;
872 END IF;
873 d_progress := 240;
874
875 -- Not using binds as it would be complicated to put all the checks
876 d_progress := 250;
877 PON_UDA_IMPORT_PKG.DEBUG('l_sql_query : ' || l_sql_query, d_progress);
878 PON_UDA_IMPORT_PKG.DEBUG('Before executing', d_progress);
879 v_query_hdl := DBMS_SQL.open_cursor;
880 DBMS_SQL.parse(v_query_hdl, l_sql_query, DBMS_SQL.native);
881 DBMS_SQL.define_column(v_query_hdl, 1, l_check);
882 l_rows_processed := DBMS_SQL.EXECUTE(v_query_hdl);
883 PON_UDA_IMPORT_PKG.DEBUG('After executing l_rows_processed :' || l_rows_processed, d_progress);
884
885 d_progress := 260;
886 IF DBMS_SQL.fetch_rows(v_query_hdl) <= 0 THEN
887 l_attr_grp_loc_index := x_attributes_row_table.FIRST;
888 l_present := 0;
889 WHILE (l_attr_grp_loc_index IS NOT NULL AND l_attr_grp_loc_index <= x_attributes_row_table.LAST)
890 LOOP
891 d_progress := 270;
892 IF x_attributes_row_table(l_attr_grp_loc_index) IS NOT NULL THEN
893 l_attr_row_obj := x_attributes_row_table(l_attr_grp_loc_index);
894 d_progress := 280;
895
896 IF l_attr_row_obj.ATTR_GROUP_ID = l_attr_group_id THEN
897 l_present := 1;
898 d_progress := 290;
899 EXIT;
900 END IF;
901 d_progress := 300;
902 END IF;
903 l_attr_grp_loc_index := x_attributes_row_table.NEXT(l_attr_grp_loc_index);
904 END LOOP;
905 d_progress := 310;
906
907 IF l_present = 0 THEN
908 d_progress := 320;
909 PON_UDA_IMPORT_PKG.DEBUG('AG is found :' || l_attr_group_id, d_progress);
910 BEGIN
911 d_progress := 330;
912 SELECT DATA_LEVEL_NAME
913 INTO l_data_level
914 FROM EGO_DATA_LEVEL_B
915 WHERE APPLICATION_ID = 201
916 AND ATTR_GROUP_TYPE = l_attr_group_type
917 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
918 AND ATTR_GROUP_TYPE NOT LIKE '%DUMMY%';
919
920 d_progress := 340;
921 PON_UDA_IMPORT_PKG.DEBUG('l_data_level : ' || l_data_level, d_progress);
922 EXCEPTION
923 WHEN OTHERS THEN
924 d_progress := 350;
925 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_data_level', d_progress);
926 RAISE;
927 END;
928
929 d_progress := 360;
930 SELECT Count(*)
931 INTO l_xd_present
932 FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
933 WHERE classification_code = p_template_id
934 AND attr_group_id = l_attr_group_id
935 AND execution_method = 'XD'
936 AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id;
937
938 d_progress := 370;
939 IF l_xd_present <= 0 THEN
940 -- XD Function is not present and the ag need not be added
941 d_progress := 380;
942 PON_UDA_IMPORT_PKG.DEBUG('XD function is not found so skipping the AG', d_progress);
943 NULL;
944 ELSIF l_po_uda_usage_object.USAGE_TYPE = 'ADDRESS' THEN -- FOR ADDRESS AGS
945 d_progress := 390;
946 PON_UDA_IMPORT_PKG.DEBUG('Usage type is ADDRESS', d_progress);
947 FOR cur_address_rec IN cur_address(p_address_lookup_type) LOOP
948 BEGIN
949 d_progress := 400;
950 SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
951 INTO l_row_identifier
952 FROM DUAL;
953
954 d_progress := 410;
955 PON_UDA_IMPORT_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
956 EXCEPTION
957 WHEN OTHERS THEN
958 d_progress := 420;
959 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_row_identifier', d_progress);
960 RAISE;
961 END;
962
963 d_progress := 430;
964 PON_UDA_IMPORT_PKG.DEBUG('Before adding AG to attribute row table', d_progress);
965 x_attributes_row_table.EXTEND(1);
966 x_attributes_row_table(x_attributes_row_table.COUNT) := ego_user_attr_row_obj
967 (
968 l_row_identifier
969 ,l_attr_group_id
970 ,201
971 ,l_attr_group_type
972 ,l_attr_group_name
973 ,l_data_level
974 ,NULL
975 ,NULL
976 ,NULL
977 ,NULL
978 ,NULL
979 ,'SYNC'
980 );
981 d_progress := 440;
982 PON_UDA_IMPORT_PKG.DEBUG('After adding AG to attribute row table', d_progress);
983
984 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
985 LOOP
986 d_progress := 450;
987 FETCH cur_uda_attributes
988 INTO l_attr_name, l_attr_col;
989 EXIT WHEN cur_uda_attributes%NOTFOUND;
990
991 BEGIN
992 d_progress := 460;
993 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
994 INTO l_user_row_identifier
995 FROM DUAL;
996
997 d_progress := 470;
998 PON_UDA_IMPORT_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 d_progress := 480;
1002 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_user_row_identifier', d_progress);
1003 RAISE;
1004 END;
1005 d_progress := 490;
1006 PON_UDA_IMPORT_PKG.DEBUG('Before adding attribute', d_progress);
1007 x_attr_name_value_pairs.EXTEND(1);
1008 d_progress := 500;
1009 IF l_attr_col = 'C_EXT_ATTR39' THEN
1010 d_progress := 510;
1011 l_attr_val := cur_address_rec.LOOKUP_CODE;
1012 ELSIF l_attr_col = 'C_EXT_ATTR40' THEN
1013 d_progress := 520;
1014 l_attr_val := p_address_lookup_type;
1015 ELSE
1016 d_progress := 530;
1017 l_attr_val := NULL;
1018 END IF;
1019 d_progress := 540;
1020 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
1021 (
1022 l_row_identifier
1023 ,l_attr_name
1024 ,l_attr_val
1025 ,NULL
1026 ,NULL
1027 ,NULL
1028 ,NULL
1029 ,l_user_row_identifier
1030 );
1031 d_progress := 550;
1032 PON_UDA_IMPORT_PKG.DEBUG('After adding attribute', d_progress);
1033
1034 END LOOP;
1035 CLOSE cur_uda_attributes;
1036 d_progress := 560;
1037 END LOOP;
1038 ELSE -- FOR NON ADDRESS AGS
1039 d_progress := 570;
1040 BEGIN
1041 d_progress := 580;
1042 SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
1043 INTO l_row_identifier
1044 FROM DUAL;
1045 d_progress := 590;
1046 PON_UDA_IMPORT_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 d_progress := 600;
1050 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_row_identifier', d_progress);
1051 RAISE;
1052 END;
1053
1054 d_progress := 610;
1055 PON_UDA_IMPORT_PKG.DEBUG('Before adding AG to attribute row table', d_progress);
1056 x_attributes_row_table.EXTEND(1);
1057 x_attributes_row_table(x_attributes_row_table.COUNT) := ego_user_attr_row_obj
1058 (
1059 l_row_identifier
1060 ,l_attr_group_id
1061 ,201
1062 ,l_attr_group_type
1063 ,l_attr_group_name
1064 ,l_data_level
1065 ,NULL
1066 ,NULL
1067 ,NULL
1068 ,NULL
1069 ,NULL
1070 ,'SYNC'
1071 );
1072 d_progress := 620;
1073 PON_UDA_IMPORT_PKG.DEBUG('After adding AG to attribute row table', d_progress);
1074
1075 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
1076 LOOP
1077 d_progress := 630;
1078 FETCH cur_uda_attributes
1079 INTO l_attr_name, l_attr_col;
1080 EXIT WHEN cur_uda_attributes%NOTFOUND;
1081
1082 d_progress := 640;
1083 BEGIN
1084 d_progress := 650;
1085 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
1086 INTO l_user_row_identifier
1087 FROM DUAL;
1088 d_progress := 660;
1089 PON_UDA_IMPORT_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 d_progress := 670;
1093 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the l_user_row_identifier', d_progress);
1094 RAISE;
1095 END;
1096 d_progress := 680;
1097
1098 PON_UDA_IMPORT_PKG.DEBUG('Before adding AG to attribute row table', d_progress);
1099 x_attr_name_value_pairs.EXTEND(1);
1100 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
1101 (
1102 l_row_identifier
1103 ,l_attr_name
1104 ,NULL
1105 ,NULL
1106 ,NULL
1107 ,NULL
1108 ,NULL
1109 ,l_user_row_identifier
1110 );
1111 d_progress := 690;
1112 PON_UDA_IMPORT_PKG.DEBUG('After adding AG to attribute row table', d_progress);
1113 d_progress := 700;
1114
1115 END LOOP;
1116 CLOSE cur_uda_attributes;
1117 d_progress := 710;
1118 END IF; -- FOR NON ADDRESS AGS
1119 d_progress := 720;
1120 END IF;
1121 END IF;
1122
1123 END IF;
1124 d_progress := 730;
1125 l_attr_group_id_index := l_attr_group_table.NEXT(l_attr_group_id_index);
1126 END LOOP;
1127 d_progress := 740;
1128 END IF;
1129
1130 END IF;
1131 d_progress := 750;
1132 l_context_object_index := x_po_uda_usage_object_array.NEXT(l_context_object_index);
1133 END LOOP;
1134 ELSE
1135 d_progress := 760;
1136 PON_UDA_IMPORT_PKG.DEBUG('USAGE object array does not have any objects', d_progress);
1137 END IF;
1138 d_progress := 770;
1139 x_return_status := FND_API.G_RET_STS_SUCCESS;
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 l_err_txt := 'Exception in POPULATE_EMPTY_AGS with ERROR : ' || SQLERRM;
1143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1144 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1145 (
1146 p_msg => l_err_txt
1147 ,p_msg_code => 'UNEXPECTED_POPULATE_EMPTY_AGS'
1148 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
1149 );
1150 END POPULATE_EMPTY_AGS;
1151
1152 --
1153 -- Private
1154 -- To Check whether all the attributes in the AG are null or not
1155 --
1156 FUNCTION All_Attr_Values_Are_Null (
1157 p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1158 )
1159 RETURN BOOLEAN
1160 IS
1161
1162 l_attr_count NUMBER;
1163 l_all_are_null BOOLEAN := TRUE;
1164
1165 BEGIN
1166 PON_UDA_IMPORT_PKG.DEBUG('Starting All_Attr_Values_Are_Null ', 0);
1167 PON_UDA_IMPORT_PKG.DEBUG('p_attr_name_value_pairs is as follows ', 0);
1168 PON_UDA_IMPORT_PKG.DEBUG(p_attr_name_value_pairs, 0);
1169
1170 IF (p_attr_name_value_pairs IS NOT NULL AND p_attr_name_value_pairs.COUNT > 0) THEN
1171 l_attr_count := p_attr_name_value_pairs.FIRST;
1172 WHILE (l_attr_count <= p_attr_name_value_pairs.LAST)
1173 LOOP
1174 EXIT WHEN (NOT l_all_are_null);
1175 IF (
1176 p_attr_name_value_pairs(l_attr_count).ATTR_VALUE_STR IS NOT NULL OR
1177 p_attr_name_value_pairs(l_attr_count).ATTR_VALUE_NUM IS NOT NULL OR
1178 p_attr_name_value_pairs(l_attr_count).ATTR_VALUE_DATE IS NOT NULL OR
1179 p_attr_name_value_pairs(l_attr_count).ATTR_DISP_VALUE IS NOT NULL
1180 ) THEN
1181 l_all_are_null := FALSE;
1182 END IF;
1183
1184 l_attr_count := p_attr_name_value_pairs.NEXT(l_attr_count);
1185 END LOOP;
1186 END IF;
1187 RETURN l_all_are_null;
1188 END All_Attr_Values_Are_Null;
1189
1190 --------------------------------------------------------------------------------------------------------------------
1191 --Start of Comments
1192 --Name: EXECUTE_IMPORT_UDA_FUNCTION
1193 --Procedure:
1194 -- This procedure would execute all the actions appropriate using the procedure
1195 -- EGO_EXT_FWK_PUB.EXECUTE_FUNCTION.
1196 -- Appropriate actions are teh actions from the view EGO_ACTION_DISPLAYS_B with
1197 -- Calssification code as the teplate id attriubute group id would be all the
1198 -- attribute groups from the variable x_attributes_row_table and execution_code
1199 -- as that of p_event
1200 -- The actions would be executed in the order of the sequence number of the
1201 -- appropriate actions.
1202 --Parameters:
1203 --IN:
1204 -- 1. p_template_id
1205 -- This variable would contain the template id for which the actions are to be executed
1206 -- 2. p_event
1207 -- This variable would contain the event type (execution code)
1208 --IN/OUT:
1209 -- 1. x_external_attr_value_pairs
1210 -- This variable wsould contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
1211 -- 2. x_pk_column_name_value_pairs
1212 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
1213 -- 3. x_attr_name_value_pairs
1214 -- This variable would contain an array of type EGO_USER_ATTR_DATA_TABLE with the template details
1215 -- 4. x_attributes_row_table
1216 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
1217 --OUT:
1218 -- 1. x_return_status - Generic Out parameter having return status
1219 -- 2. x_errorcode - Generic Out parameter having error code
1220 -- 3. x_msg_count - Generic Out parameter having return msg Count
1221 -- 4. x_msg_data - Generic Out parameter having return msg
1222 --
1223 --End of Comments
1224 --------------------------------------------------------------------------------------------------------------------
1225 PROCEDURE EXECUTE_IMPORT_UDA_FUNCTION
1226 (
1227 p_template_id IN NUMBER
1228 ,p_event IN VARCHAR2
1229 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
1230 ,x_pk_column_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
1231 ,x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
1232 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
1233 ,x_return_status OUT NOCOPY VARCHAR2
1234 ,x_errorcode OUT NOCOPY NUMBER
1235 ,x_msg_count OUT NOCOPY NUMBER
1236 ,x_msg_data OUT NOCOPY VARCHAR2
1237 )
1238 IS
1239 d_progress NUMBER := 0;
1240 l_attr_group_id_index NUMBER;
1241 l_attr_row_obj EGO_USER_ATTR_ROW_OBJ;
1242 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1243 l_action_id NUMBER;
1244 l_data_level_name VARCHAR2(200);
1245 l_data_level_id NUMBER;
1246 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
1247 l_attr_id_index NUMBER;
1248 l_attr_id_index1 NUMBER;
1249 l_attr_obj EGO_USER_ATTR_DATA_OBJ;
1250 l_attr_obj1 EGO_USER_ATTR_DATA_OBJ;
1251 ext_attr_value_pairs_ind NUMBER;
1252 ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
1253
1254 g_uda_return_status VARCHAR2(2);
1255 uda_msg_data VARCHAR2(2000);
1256 uda_return_status VARCHAR2(2);
1257
1258
1259
1260 CURSOR c_get_action_id(p_template_id NUMBER, p_attribute_group_id NUMBER, p_event VARCHAR2) IS
1261 SELECT EGO_ACTION_DISPLAYS_B.action_id FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
1262 WHERE classification_code = p_template_id
1263 AND attr_group_id = p_attribute_group_id
1264 AND execution_method = p_event
1265 AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id
1266 ORDER BY SEQUENCE;
1267
1268
1269 l_pk_col_name_value_pairs_ind NUMBER;
1270 l_attr_name_value_pairs_ind NUMBER;
1271 l_attributes_row_table_ind NUMBER;
1272 l_ext_attr_value_pairs_ind NUMBER;
1273
1274 l_pk_column_name_value EGO_COL_NAME_VALUE_PAIR_OBJ;
1275 l_attr_name_value EGO_USER_ATTR_DATA_OBJ;
1276 l_attributes_row ego_user_attr_row_obj;
1277 l_external_attr_value EGO_COL_NAME_VALUE_PAIR_OBJ;
1278
1279 l_err_txt VARCHAR2(2000);
1280
1281 BEGIN
1282 d_progress := 10;
1283 PON_UDA_IMPORT_PKG.DEBUG('Starting EXECUTE_IMPORT_UDA_FUNCTION', d_progress);
1284 PON_UDA_IMPORT_PKG.DEBUG('p_template_id : ' || p_template_id, d_progress);
1285 PON_UDA_IMPORT_PKG.DEBUG('p_event : ' || p_event, d_progress);
1286 PON_UDA_IMPORT_PKG.DEBUG('x_external_attr_value_pairs is as follows', d_progress);
1287 PON_UDA_IMPORT_PKG.DEBUG(x_external_attr_value_pairs, d_progress);
1288 PON_UDA_IMPORT_PKG.DEBUG('x_pk_column_name_value_pairs is as follows', d_progress);
1289 PON_UDA_IMPORT_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
1290 PON_UDA_IMPORT_PKG.DEBUG('x_attr_name_value_pairs is as follows', d_progress);
1291 PON_UDA_IMPORT_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
1292 PON_UDA_IMPORT_PKG.DEBUG('x_attributes_row_table is as follows', d_progress);
1293 PON_UDA_IMPORT_PKG.DEBUG(x_attributes_row_table, d_progress);
1294
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;
1296 g_uda_return_status := FND_API.G_RET_STS_SUCCESS;
1297 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0) THEN
1298 d_progress := 20;
1299 l_attr_group_id_index := x_attributes_row_table.FIRST;
1300 d_progress := 30;
1301
1302 WHILE (l_attr_group_id_index IS NOT NULL AND l_attr_group_id_index <= x_attributes_row_table.LAST)
1303 LOOP
1304 d_progress := 40;
1305 IF x_attributes_row_table(l_attr_group_id_index) IS NOT NULL THEN
1306 d_progress := 50;
1307 l_attr_row_obj := x_attributes_row_table(l_attr_group_id_index);
1308 d_progress := 60;
1309
1310 BEGIN
1311 d_progress := 70;
1312 SELECT DATA_LEVEL_NAME, DATA_LEVEL_ID
1313 INTO l_data_level_name, l_data_level_id
1314 FROM EGO_DATA_LEVEL_B
1315 WHERE ATTR_GROUP_TYPE = l_attr_row_obj.ATTR_GROUP_TYPE
1316 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
1317
1318 d_progress := 80;
1319 PON_UDA_IMPORT_PKG.DEBUG('l_data_level_name : ' || l_data_level_name, d_progress);
1320 PON_UDA_IMPORT_PKG.DEBUG('l_data_level_id : ' || l_data_level_id, d_progress);
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 d_progress := 90;
1324 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to fetch l_data_level_name, l_data_level_id', d_progress);
1325 END;
1326
1327 d_progress := 100;
1328 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
1329 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
1330 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE(l_attr_row_obj);
1331 PON_UDA_IMPORT_PKG.DEBUG('Initialized l_attr_name_value_pairs, l_attributes_row_table', d_progress);
1332
1333 d_progress := 110;
1334 PON_UDA_IMPORT_PKG.DEBUG('Putting all attributes with the ROW_IDENTIFIER (' || l_attr_obj.ROW_IDENTIFIER || ') into another object array', d_progress);
1335 IF (x_attr_name_value_pairs IS NOT NULL AND x_attr_name_value_pairs.COUNT > 0) THEN
1336 d_progress := 120;
1337 l_attr_id_index := x_attr_name_value_pairs.FIRST;
1338 d_progress := 130;
1339 WHILE (l_attr_id_index IS NOT NULL AND l_attr_id_index <= x_attr_name_value_pairs.LAST)
1340 LOOP
1341 d_progress := 140;
1342 IF x_attr_name_value_pairs(l_attr_id_index) IS NOT NULL THEN
1343 d_progress := 150;
1344 l_attr_obj := x_attr_name_value_pairs(l_attr_id_index);
1345 d_progress := 160;
1346 IF l_attr_obj.ROW_IDENTIFIER = l_attr_row_obj.ROW_IDENTIFIER THEN
1347 d_progress := 170;
1348 l_attr_name_value_pairs.EXTEND(1);
1349 l_attr_name_value_pairs(l_attr_name_value_pairs.COUNT) := l_attr_obj;
1350 d_progress := 180;
1351 END IF;
1352 END IF;
1353 d_progress := 190;
1354 l_attr_id_index := x_attr_name_value_pairs.NEXT(l_attr_id_index);
1355 END LOOP;
1356 END IF;
1357 d_progress := 200;
1358 PON_UDA_IMPORT_PKG.DEBUG('Object array is constructed', d_progress);
1359
1360 OPEN c_get_action_id(p_template_id, l_attr_row_obj.ATTR_GROUP_ID, p_event);
1361 LOOP
1362 d_progress := 210;
1363 FETCH c_get_action_id
1364 INTO l_action_id;
1365 EXIT WHEN c_get_action_id%NOTFOUND;
1366 d_progress := 220;
1367
1368 PON_UDA_IMPORT_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION l_action_id : ' || l_action_id, d_progress);
1369 PON_UDA_IMPORT_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION l_data_level_name : ' || l_data_level_name, d_progress);
1370 PON_UDA_IMPORT_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION l_data_level_id : ' || l_data_level_id, d_progress);
1371 PON_UDA_IMPORT_PKG.DEBUG('x_pk_column_name_value_pairs is as follows :', d_progress);
1372 PON_UDA_IMPORT_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
1373 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_row_table is as follows :', d_progress);
1374 PON_UDA_IMPORT_PKG.DEBUG(l_attributes_row_table, d_progress);
1375 PON_UDA_IMPORT_PKG.DEBUG('l_attr_name_value_pairs is as follows :', d_progress);
1376 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1377 PON_UDA_IMPORT_PKG.DEBUG('x_external_attr_value_pairs is as follows :', d_progress);
1378 PON_UDA_IMPORT_PKG.DEBUG(x_external_attr_value_pairs, d_progress);
1379 PON_UDA_IMPORT_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION x_return_status :' || x_return_status, d_progress);
1380 d_progress := 230;
1381 EGO_EXT_FWK_PUB.EXECUTE_FUNCTION
1382 (
1383 p_Action_Id => l_action_id
1384 ,p_pk_col_value_pairs => x_pk_column_name_value_pairs
1385 ,p_dtlevel_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(l_data_level_name, l_data_level_id))
1386 ,x_attributes_row_table => l_attributes_row_table
1387 ,x_attributes_data_table => l_attr_name_value_pairs
1388 ,x_external_attrs_value_pairs => x_external_attr_value_pairs
1389 ,x_return_status => x_return_status
1390 ,x_errorcode => x_errorcode
1391 ,x_msg_count => x_msg_count
1392 ,x_msg_data => x_msg_data
1393 );
1394 d_progress := 240;
1395 PON_UDA_IMPORT_PKG.DEBUG('After EGO_EXT_FWK_PUB.EXECUTE_FUNCTION x_return_status : ' || x_return_status, d_progress);
1396 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode : ' || x_errorcode, d_progress);
1397 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count : ' || x_msg_count, d_progress);
1398 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data : ' || x_msg_data, d_progress);
1399
1400 d_progress := 250;
1401 PON_UDA_IMPORT_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', x_external_attr_value_pairs, ext_name_val_obj);
1402 uda_return_status := ext_name_val_obj.VALUE ;
1403 PON_UDA_IMPORT_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', x_external_attr_value_pairs, ext_name_val_obj);
1404 uda_msg_data := ext_name_val_obj.VALUE ;
1405
1406 d_progress := 260;
1407 IF uda_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1408 g_uda_return_status := uda_return_status;
1409 END IF;
1410
1411 d_progress := 270;
1412 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1413 d_progress := 280;
1414 EXIT;
1415 END IF;
1416
1417 d_progress := 290;
1418 PON_UDA_IMPORT_PKG.DEBUG('Starting Putting back the new object to the old one', d_progress);
1419 IF (l_attr_name_value_pairs IS NOT NULL AND l_attr_name_value_pairs.COUNT > 0) THEN
1420 d_progress := 300;
1421 l_attr_id_index1 := l_attr_name_value_pairs.FIRST;
1422 d_progress := 310;
1423 WHILE (l_attr_id_index1 IS NOT NULL AND l_attr_id_index1 <= l_attr_name_value_pairs.LAST)
1424 LOOP
1425 d_progress := 320;
1426 IF l_attr_name_value_pairs(l_attr_id_index1) IS NOT NULL THEN
1427 d_progress := 330;
1428 l_attr_obj1 := l_attr_name_value_pairs(l_attr_id_index1);
1429 IF (x_attr_name_value_pairs IS NOT NULL AND x_attr_name_value_pairs.COUNT > 0) THEN
1430 d_progress := 340;
1431 l_attr_id_index := x_attr_name_value_pairs.FIRST;
1432 WHILE (l_attr_id_index IS NOT NULL AND l_attr_id_index <= x_attr_name_value_pairs.LAST)
1433 LOOP
1434 d_progress := 350;
1435 IF x_attr_name_value_pairs(l_attr_id_index) IS NOT NULL THEN
1436 d_progress := 360;
1437 l_attr_obj := x_attr_name_value_pairs(l_attr_id_index);
1438 IF l_attr_obj.USER_ROW_IDENTIFIER = l_attr_obj1.USER_ROW_IDENTIFIER THEN
1439 d_progress := 370;
1440 x_attr_name_value_pairs(l_attr_id_index) := l_attr_obj1;
1441 EXIT;
1442 ELSIF l_attr_obj.ROW_IDENTIFIER = l_attr_obj1.ROW_IDENTIFIER
1443 AND l_attr_obj.ATTR_NAME = l_attr_obj1.ATTR_NAME THEN
1444 d_progress := 375;
1445 x_attr_name_value_pairs(l_attr_id_index) := l_attr_obj1;
1446 EXIT;
1447 END IF;
1448 END IF;
1449 d_progress := 380;
1450 l_attr_id_index := x_attr_name_value_pairs.NEXT(l_attr_id_index);
1451 END LOOP;
1452 END IF;
1453 END IF;
1454 d_progress := 390;
1455 l_attr_id_index1 := l_attr_name_value_pairs.NEXT(l_attr_id_index1);
1456 END LOOP;
1457 END IF;
1458 d_progress := 400;
1459 PON_UDA_IMPORT_PKG.DEBUG('Done Putting back the new object to the old one', d_progress);
1460
1461 IF (p_event = 'XV') AND (x_external_attr_value_pairs IS NOT NULL AND x_external_attr_value_pairs.COUNT > 0) THEN
1462 d_progress := 410;
1463 PON_UDA_IMPORT_PKG.DEBUG('Checking if XV has failed', d_progress);
1464 PON_UDA_IMPORT_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', x_external_attr_value_pairs, ext_name_val_obj);
1465 x_return_status := ext_name_val_obj.VALUE ;
1466 PON_UDA_IMPORT_PKG.DEBUG('Return status for action is ' || x_return_status, d_progress);
1467
1468 d_progress := 420;
1469 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1470 d_progress := 43;
1471 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1472 (
1473 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION for Action ID :' || l_action_id
1474 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION'
1475 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
1476 );
1477 d_progress := 440;
1478 EXIT;
1479 END IF;
1480 d_progress := 450;
1481 ELSIF (p_event = 'XD') AND All_Attr_Values_Are_Null (l_attr_name_value_pairs) THEN
1482 d_progress := 460;
1483 PON_UDA_IMPORT_PKG.DEBUG('Removing the attributes with value is null', d_progress);
1484 IF (x_attr_name_value_pairs IS NOT NULL AND x_attr_name_value_pairs.COUNT > 0) THEN
1485 l_attr_id_index := x_attr_name_value_pairs.FIRST;
1486 d_progress := 470;
1487 WHILE (l_attr_id_index IS NOT NULL AND l_attr_id_index <= x_attr_name_value_pairs.LAST)
1488 LOOP
1489 d_progress := 480;
1490 IF x_attr_name_value_pairs(l_attr_id_index) IS NOT NULL THEN
1491 l_attr_obj := x_attr_name_value_pairs(l_attr_id_index);
1492 d_progress := 490;
1493 IF l_attr_obj.ROW_IDENTIFIER = l_attr_row_obj.ROW_IDENTIFIER THEN
1494 d_progress := 500;
1495 x_attr_name_value_pairs.DELETE(l_attr_id_index);
1496 END IF;
1497 END IF;
1498 d_progress := 510;
1499 l_attr_id_index := x_attr_name_value_pairs.NEXT(l_attr_id_index);
1500 END LOOP;
1501 END IF;
1502 d_progress := 520;
1503 x_attributes_row_table.DELETE(l_attr_group_id_index);
1504 END IF;
1505
1506 d_progress := 530;
1507 END LOOP;
1508 CLOSE c_get_action_id;
1509
1510 IF g_uda_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1511 d_progress := 540;
1512 PON_UDA_IMPORT_PKG.DEBUG('g_uda_return_status is a failure', d_progress);
1513 PON_UDA_IMPORT_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', g_uda_return_status, x_external_attr_value_pairs);
1514 END IF;
1515 d_progress := 550;
1516 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1517 PON_UDA_IMPORT_PKG.DEBUG('x_return_status is a failure', d_progress);
1518 d_progress := 560;
1519 EXIT;
1520 END IF;
1521 d_progress := 570;
1522 END IF;
1523 d_progress := 580;
1524 l_attr_group_id_index := x_attributes_row_table.NEXT(l_attr_group_id_index);
1525 END LOOP;
1526 IF x_return_status IS NULL THEN
1527 d_progress := 600;
1528 x_return_status := FND_API.G_RET_STS_SUCCESS;
1529 END IF;
1530 d_progress := 610;
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 l_err_txt := 'Exception in EXECUTE_IMPORT_UDA_FUNCTION with ERROR : ' || SQLERRM;
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1538 (
1539 p_msg => l_err_txt
1540 ,p_msg_code => 'UNEXPECTED_EXECUTE_IMPORT_UDA_FUNCTION'
1541 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
1542 );
1543 END EXECUTE_IMPORT_UDA_FUNCTION;
1544
1545
1546 --------------------------------------------------------------------------------------------------------------------
1547 --Start of Comments
1548 --Name: IMPORT_UDA_ATTRS
1549 --Procedure:
1550 -- This procedure would execute all the procedure in a process to complete the import flow
1551 -- Starting with POPULATE_STRUCTURES, POPULATE_REMAINING_ATTRIBUTES, POPULATE_EMPTY_AGS
1552 -- EXECUTE_IMPORT_UDA_FUNCTION with event 'XD' followed by 'XLE'
1553 -- Then it executes EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data to execute the
1554 -- EGO validations and then EXECUTE_IMPORT_UDA_FUNCTION with event 'XV'
1555 -- Then loads the data into the appropriate EXT tables using the procedure
1556 -- EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data.
1557 --Parameters:
1558 --IN:
1559 -- 1. p_attr_grp_type
1560 -- The attribute Group Type (Entity Type)
1561 -- 2. p_pk1_value, p_pk2_value, p_pk3_value, p_pk4_value, p_pk5_value
1562 -- The Primary Key values for the UDA Attributes
1563 --IN/OUT:
1564 -- 1. x_po_uda_usage_object_array
1565 -- This variable of type PO_UDA_USAGE_OBJECT_ARRAY contains data of hhe usages.
1566 -- 2. x_external_attr_value_pairs
1567 -- This variable would contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
1568 --OUT:
1569 -- 1. x_return_status - Generic Out parameter having return status
1570 -- 2. x_errorcode - Generic Out parameter having error code
1571 -- 3. x_msg_count - Generic Out parameter having return msg Count
1572 -- 4. x_msg_data - Generic Out parameter having return msg
1573 --
1574 --End of Comments
1575 --------------------------------------------------------------------------------------------------------------------
1576 PROCEDURE IMPORT_UDA_ATTRS
1577 (
1578 p_attr_grp_type IN VARCHAR2
1579 ,p_pk1_value IN NUMBER
1580 ,p_pk2_value IN NUMBER DEFAULT NULL
1581 ,p_pk3_value IN NUMBER DEFAULT NULL
1582 ,p_pk4_value IN NUMBER DEFAULT NULL
1583 ,p_pk5_value IN NUMBER DEFAULT NULL
1584 ,x_po_uda_usage_object_array IN OUT NOCOPY PO_UDA_USAGE_OBJECT_ARRAY
1585 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
1586 ,x_return_status OUT NOCOPY VARCHAR2
1587 ,x_errorcode OUT NOCOPY NUMBER
1588 ,x_msg_count OUT NOCOPY NUMBER
1589 ,x_msg_data OUT NOCOPY VARCHAR2
1590
1591 )
1592 IS
1593 l_base_object_name VARCHAR2(200);
1594 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1595 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
1596 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1597 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1598 d_progress NUMBER := 0;
1599 l_failed_row_id_list VARCHAR2(2000);
1600
1601 l_err_txt VARCHAR2(2000);
1602
1603 BEGIN
1604 d_progress := 10;
1605 PON_UDA_IMPORT_PKG.DEBUG('Starting IMPORT_UDA_ATTRS', d_progress);
1606 PON_UDA_IMPORT_PKG.DEBUG('p_attr_grp_type : ' || p_attr_grp_type, d_progress);
1607 PON_UDA_IMPORT_PKG.DEBUG('p_pk1_value : ' || p_pk1_value, d_progress);
1608 PON_UDA_IMPORT_PKG.DEBUG('p_pk2_value : ' || p_pk2_value, d_progress);
1609 PON_UDA_IMPORT_PKG.DEBUG('p_pk3_value : ' || p_pk3_value, d_progress);
1610 PON_UDA_IMPORT_PKG.DEBUG('p_pk4_value : ' || p_pk4_value, d_progress);
1611 PON_UDA_IMPORT_PKG.DEBUG('p_pk5_value : ' || p_pk5_value, d_progress);
1612 PON_UDA_IMPORT_PKG.DEBUG('x_po_uda_usage_object_array is as follows : ', d_progress);
1613 PON_UDA_IMPORT_PKG.DEBUG(x_po_uda_usage_object_array, d_progress);
1614 PON_UDA_IMPORT_PKG.DEBUG('x_external_attr_value_pairs is as follows : ', d_progress);
1615 PON_UDA_IMPORT_PKG.DEBUG(x_external_attr_value_pairs, d_progress);
1616 d_progress := 20;
1617
1618 PON_UDA_IMPORT_PKG.DEBUG('Before PON_UDA_IMPORT_PKG.UPDATE_STATUS to change to IN_PROGRESS from PENDING', d_progress);
1619 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1620 (
1621 p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1622 ,p_pre_process_status => PON_UDA_IMPORT_PKG.PENDING
1623 ,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
1624 ,p_pk1_value => p_pk1_value
1625 ,p_pk2_value => p_pk2_value
1626 ,p_pk3_value => p_pk3_value
1627 ,p_pk4_value => p_pk4_value
1628 ,p_pk5_value => p_pk5_value
1629 );
1630 PON_UDA_IMPORT_PKG.DEBUG('After PON_UDA_IMPORT_PKG.UPDATE_STATUS to change to PENDING from IN_PROGRESS', d_progress);
1631
1632 d_progress := 30;
1633 g_attr_grp_type := p_attr_grp_type;
1634 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
1635 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
1636 d_progress := 40;
1637 PON_UDA_IMPORT_PKG.DEBUG('Initialized teh objects l_attributes_row_table, l_attr_name_value_pairs', d_progress);
1638
1639 d_progress := 50;
1640 PON_UDA_IMPORT_PKG.DEBUG('Before POPULATE_STRUCTURES ' || x_return_status, d_progress);
1641 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1642 PON_UDA_IMPORT_PKG.POPULATE_STRUCTURES
1643 (
1644 p_attr_grp_type => p_attr_grp_type
1645 ,p_pk1_value => p_pk1_value
1646 ,p_pk2_value => p_pk2_value
1647 ,p_pk3_value => p_pk3_value
1648 ,p_pk4_value => p_pk4_value
1649 ,p_pk5_value => p_pk5_value
1650 ,x_base_object_name => l_base_object_name
1651 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1652 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1653 ,x_attributes_row_table => l_attributes_row_table
1654 ,x_class_code_name_value_pairs => l_class_code_name_value_pairs
1655 ,x_return_status => x_return_status
1656 ,x_errorcode => x_errorcode
1657 ,x_msg_count => x_msg_count
1658 ,x_msg_data => x_msg_data
1659 );
1660 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1661 d_progress := 60;
1662 PON_UDA_IMPORT_PKG.DEBUG('After POPULATE_STRUCTURES ' || x_return_status, d_progress);
1663 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1664 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1665 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1666
1667 PON_UDA_IMPORT_PKG.DEBUG('Template ID is :: ' || l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE, d_progress);
1668 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1669 d_progress := 70;
1670 PON_UDA_IMPORT_PKG.DEBUG('Before POPULATE_REMAINING_ATTRIBUTES ' || x_return_status, d_progress);
1671 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1672
1673 PON_UDA_IMPORT_PKG.POPULATE_REMAINING_ATTRIBUTES
1674 (
1675 x_attr_name_value_pairs => l_attr_name_value_pairs
1676 ,x_attributes_row_table => l_attributes_row_table
1677 ,x_return_status => x_return_status
1678 ,x_errorcode => x_errorcode
1679 ,x_msg_count => x_msg_count
1680 ,x_msg_data => x_msg_data
1681 );
1682 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1683 d_progress := 80;
1684 PON_UDA_IMPORT_PKG.DEBUG('After POPULATE_REMAINING_ATTRIBUTES ' || x_return_status, d_progress);
1685 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1686 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1687 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1688 ELSE
1689 d_progress := 90;
1690 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1691 (
1692 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
1693 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1694 ,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
1695 ,p_pk1_value => p_pk1_value
1696 ,p_pk2_value => p_pk2_value
1697 ,p_pk3_value => p_pk3_value
1698 ,p_pk4_value => p_pk4_value
1699 ,p_pk5_value => p_pk5_value
1700 );
1701 d_progress := 100;
1702
1703 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1704 (
1705 p_msg => 'There was an error in POPULATE_STRUCTURES'
1706 ,p_msg_code => 'ERROR_POPULATE_STRUCTURES'
1707 ,p_attr_grp_id => NULL
1708 );
1709 d_progress := 110;
1710 END IF;
1711 d_progress := 120;
1712 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1713 d_progress := 130;
1714 PON_UDA_IMPORT_PKG.DEBUG('Before POPULATE_EMPTY_AGS ' || x_return_status, d_progress);
1715 PON_UDA_IMPORT_PKG.POPULATE_EMPTY_AGS
1716 (
1717 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1718 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1719 ,p_external_attr_value_pairs => x_external_attr_value_pairs
1720 ,x_po_uda_usage_object_array => x_po_uda_usage_object_array
1721 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1722 ,x_attributes_row_table => l_attributes_row_table
1723 ,x_return_status => x_return_status
1724 ,x_errorcode => x_errorcode
1725 ,x_msg_count => x_msg_count
1726 ,x_msg_data => x_msg_data
1727 );
1728 d_progress := 140;
1729 PON_UDA_IMPORT_PKG.DEBUG('After POPULATE_EMPTY_AGS ' || x_return_status, d_progress);
1730 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1731 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1732 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1733 ELSE
1734 d_progress := 150;
1735 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1736 (
1737 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
1738 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1739 ,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
1740 ,p_pk1_value => p_pk1_value
1741 ,p_pk2_value => p_pk2_value
1742 ,p_pk3_value => p_pk3_value
1743 ,p_pk4_value => p_pk4_value
1744 ,p_pk5_value => p_pk5_value
1745 );
1746 d_progress := 160;
1747 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1748 (
1749 p_msg => 'There was an error in POPULATE_REMAINING_ATTRIBUTES'
1750 ,p_msg_code => 'ERROR_POPULATE_REMAINING_ATTRIBUTES'
1751 ,p_attr_grp_id => NULL
1752 );
1753 d_progress := 170;
1754 END IF;
1755
1756 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1757 d_progress := 180;
1758 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1759 (
1760 p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1761 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1762 ,p_process_phase => PON_UDA_IMPORT_PKG.DEFAULTING
1763 ,p_pk1_value => p_pk1_value
1764 ,p_pk2_value => p_pk2_value
1765 ,p_pk3_value => p_pk3_value
1766 ,p_pk4_value => p_pk4_value
1767 ,p_pk5_value => p_pk5_value
1768 );
1769 d_progress := 190;
1770
1771 PON_UDA_IMPORT_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XD) ' || x_return_status, d_progress);
1772 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1773 PON_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1774 (
1775 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1776 ,p_event => 'XD'
1777 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1778 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1779 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1780 ,x_attributes_row_table => l_attributes_row_table
1781 ,x_return_status => x_return_status
1782 ,x_errorcode => x_errorcode
1783 ,x_msg_count => x_msg_count
1784 ,x_msg_data => x_msg_data
1785 );
1786
1787 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1788
1789 d_progress := 200;
1790 PON_UDA_IMPORT_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XD) ' || x_return_status, d_progress);
1791 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1792 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1793 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1794 ELSE
1795 d_progress := 210;
1796 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1797 (
1798 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
1799 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1800 ,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
1801 ,p_pk1_value => p_pk1_value
1802 ,p_pk2_value => p_pk2_value
1803 ,p_pk3_value => p_pk3_value
1804 ,p_pk4_value => p_pk4_value
1805 ,p_pk5_value => p_pk5_value
1806 );
1807 d_progress := 220;
1808 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1809 (
1810 p_msg => 'There was an error in POPULATE_EMPTY_AGS'
1811 ,p_msg_code => 'ERROR_POPULATE_EMPTY_AGS'
1812 ,p_attr_grp_id => NULL
1813 );
1814 d_progress := 230;
1815 END IF;
1816
1817 /*IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1818 d_progress := 240;
1819 PON_UDA_IMPORT_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XLE) ' || x_return_status, d_progress);
1820 PON_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1821 (
1822 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1823 ,p_event => 'XLE'
1824 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1825 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1826 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1827 ,x_attributes_row_table => l_attributes_row_table
1828 ,x_return_status => x_return_status
1829 ,x_errorcode => x_errorcode
1830 ,x_msg_count => x_msg_count
1831 ,x_msg_data => x_msg_data
1832 );
1833 d_progress := 250;
1834 PON_UDA_IMPORT_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XLE) ' || x_return_status, d_progress);
1835 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1836 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1837 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1838 ELSE
1839 d_progress := 260;
1840 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1841 (
1842 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
1843 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1844 ,p_process_phase => PON_UDA_IMPORT_PKG.DEFAULTING
1845 ,p_pk1_value => p_pk1_value
1846 ,p_pk2_value => p_pk2_value
1847 ,p_pk3_value => p_pk3_value
1848 ,p_pk4_value => p_pk4_value
1849 ,p_pk5_value => p_pk5_value
1850 );
1851 d_progress := 270;
1852 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1853 (
1854 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION (XD)'
1855 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION_XD'
1856 ,p_attr_grp_id => NULL
1857 );
1858 d_progress := 280;
1859 END IF;*/
1860
1861 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1862 d_progress := 290;
1863 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1864 (
1865 p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1866 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1867 ,p_process_phase => PON_UDA_IMPORT_PKG.EGO_VALIDATE
1868 ,p_pk1_value => p_pk1_value
1869 ,p_pk2_value => p_pk2_value
1870 ,p_pk3_value => p_pk3_value
1871 ,p_pk4_value => p_pk4_value
1872 ,p_pk5_value => p_pk5_value
1873 );
1874 d_progress := 300;
1875
1876 PON_UDA_IMPORT_PKG.DEBUG('Before VALIDATE_ROW ' || x_return_status, d_progress);
1877 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_row_table.COUNT ' || l_attributes_row_table.COUNT, d_progress);
1878 IF l_attributes_row_table.COUNT > 0 THEN
1879 PON_UDA_IMPORT_PKG.DEBUG('Before EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data with parameters ' || x_return_status, d_progress);
1880 PON_UDA_IMPORT_PKG.DEBUG('l_base_object_name : ' || l_base_object_name, d_progress);
1881 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_row_table is as follows : ', d_progress);
1882 PON_UDA_IMPORT_PKG.DEBUG(l_attributes_row_table, d_progress);
1883 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_data_table is as follows : ', d_progress);
1884 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1885 PON_UDA_IMPORT_PKG.DEBUG('l_pk_column_name_value_pairs is as follows : ', d_progress);
1886 PON_UDA_IMPORT_PKG.DEBUG(l_pk_column_name_value_pairs, d_progress);
1887 PON_UDA_IMPORT_PKG.DEBUG('l_class_code_name_value_pairs is as follows : ', d_progress);
1888 PON_UDA_IMPORT_PKG.DEBUG(l_class_code_name_value_pairs, d_progress);
1889 d_progress := 310;
1890 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
1891 (
1892 p_api_version => 1.0
1893 ,p_object_name => l_base_object_name
1894 ,p_attributes_row_table => l_attributes_row_table
1895 ,p_attributes_data_table => l_attr_name_value_pairs
1896 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1897 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
1898 ,p_validate_only => FND_API.G_TRUE
1899 ,x_failed_row_id_list => l_failed_row_id_list
1900 ,x_return_status => x_return_status
1901 ,x_errorcode => x_errorcode
1902 ,x_msg_count => x_msg_count
1903 ,x_msg_data => x_msg_data
1904 );
1905 d_progress := 320;
1906 END IF;
1907 PON_UDA_IMPORT_PKG.DEBUG('After VALIDATE_ROW ' || x_return_status, d_progress);
1908 PON_UDA_IMPORT_PKG.DEBUG('l_failed_row_id_list is : ' || l_failed_row_id_list, d_progress);
1909 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1910 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1911 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1912 d_progress := 330;
1913 ELSE
1914 d_progress := 340;
1915 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1916 (
1917 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
1918 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1919 ,p_process_phase => PON_UDA_IMPORT_PKG.DEFAULTING
1920 ,p_pk1_value => p_pk1_value
1921 ,p_pk2_value => p_pk2_value
1922 ,p_pk3_value => p_pk3_value
1923 ,p_pk4_value => p_pk4_value
1924 ,p_pk5_value => p_pk5_value
1925 );
1926 d_progress := 350;
1927 PON_UDA_IMPORT_PKG.HANDLE_ERROR
1928 (
1929 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION (XLE)'
1930 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION_XLE'
1931 ,p_attr_grp_id => NULL
1932 );
1933 d_progress := 360;
1934 END IF;
1935 d_progress := 370;
1936 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1937 d_progress := 380;
1938
1939 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1940 (
1941 p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1942 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1943 ,p_process_phase => PON_UDA_IMPORT_PKG.CUSTOM_VALIDATE
1944 ,p_pk1_value => p_pk1_value
1945 ,p_pk2_value => p_pk2_value
1946 ,p_pk3_value => p_pk3_value
1947 ,p_pk4_value => p_pk4_value
1948 ,p_pk5_value => p_pk5_value
1949 );
1950 d_progress := 390;
1951
1952 IF x_external_attr_value_pairs IS NULL THEN
1953 d_progress := 400;
1954 x_external_attr_value_pairs := EGO_COL_NAME_VALUE_PAIR_TABLE();
1955 END IF;
1956 d_progress := 410;
1957
1958 PON_UDA_IMPORT_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XV) ' || x_return_status, d_progress);
1959 IF l_attributes_row_table.COUNT > 0 THEN
1960 d_progress := 420;
1961 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1962 PON_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1963 (
1964 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1965 ,p_event => 'XV'
1966 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1967 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1968 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1969 ,x_attributes_row_table => l_attributes_row_table
1970 ,x_return_status => x_return_status
1971 ,x_errorcode => x_errorcode
1972 ,x_msg_count => x_msg_count
1973 ,x_msg_data => x_msg_data
1974 );
1975 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1976
1977 d_progress := 430;
1978 END IF;
1979 PON_UDA_IMPORT_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XV) ' || x_return_status, d_progress);
1980 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1981 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1982 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1983 d_progress := 440;
1984
1985 ELSE
1986 d_progress := 450;
1987 PON_UDA_IMPORT_PKG.UPDATE_STATUS
1988 (
1989 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
1990 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
1991 ,p_process_phase => PON_UDA_IMPORT_PKG.EGO_VALIDATE
1992 ,p_pk1_value => p_pk1_value
1993 ,p_pk2_value => p_pk2_value
1994 ,p_pk3_value => p_pk3_value
1995 ,p_pk4_value => p_pk4_value
1996 ,p_pk5_value => p_pk5_value
1997 );
1998 d_progress := 460;
1999 PON_UDA_IMPORT_PKG.HANDLE_ERROR
2000 (
2001 p_msg => 'There was an error in EGO Validate'
2002 ,p_msg_code => 'ERROR_EGO_VALIDATE_ERROR'
2003 ,p_attr_grp_id => NULL
2004 );
2005 d_progress := 470;
2006 END IF;
2007
2008 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2009 d_progress := 560;
2010 PON_UDA_IMPORT_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XPD) ' || x_return_status, d_progress);
2011 IF l_attributes_row_table.COUNT > 0 THEN
2012 d_progress := 570;
2013 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
2014 PON_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
2015 (
2016 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
2017 ,p_event => 'XPC'
2018 ,x_external_attr_value_pairs => x_external_attr_value_pairs
2019 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
2020 ,x_attr_name_value_pairs => l_attr_name_value_pairs
2021 ,x_attributes_row_table => l_attributes_row_table
2022 ,x_return_status => x_return_status
2023 ,x_errorcode => x_errorcode
2024 ,x_msg_count => x_msg_count
2025 ,x_msg_data => x_msg_data
2026 );
2027 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
2028
2029 d_progress := 580;
2030 END IF;
2031 PON_UDA_IMPORT_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XPD) ' || x_return_status, d_progress);
2032 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
2033 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
2034 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
2035 d_progress := 590;
2036 ELSE
2037 d_progress := 600;
2038 PON_UDA_IMPORT_PKG.UPDATE_STATUS
2039 (
2040 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
2041 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
2042 ,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
2043 ,p_pk1_value => p_pk1_value
2044 ,p_pk2_value => p_pk2_value
2045 ,p_pk3_value => p_pk3_value
2046 ,p_pk4_value => p_pk4_value
2047 ,p_pk5_value => p_pk5_value
2048 );
2049 d_progress := 610;
2050 PON_UDA_IMPORT_PKG.HANDLE_ERROR
2051 (
2052 p_msg => 'There was an error in loading UDA Attributes'
2053 ,p_msg_code => 'ERROR_EGO_LOAD_ERROR'
2054 ,p_attr_grp_id => NULL
2055 );
2056 d_progress := 620;
2057 END IF;
2058
2059 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2060 d_progress := 480;
2061
2062 PON_UDA_IMPORT_PKG.UPDATE_STATUS
2063 (
2064 p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
2065 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
2066 ,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
2067 ,p_pk1_value => p_pk1_value
2068 ,p_pk2_value => p_pk2_value
2069 ,p_pk3_value => p_pk3_value
2070 ,p_pk4_value => p_pk4_value
2071 ,p_pk5_value => p_pk5_value
2072 );
2073 d_progress := 490;
2074
2075 PON_UDA_IMPORT_PKG.DEBUG('Before Process_User_Attrs_Data ' || x_return_status, d_progress);
2076 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_row_table.COUNT ' || l_attributes_row_table.COUNT, d_progress);
2077 IF l_attributes_row_table.COUNT > 0 THEN
2078 PON_UDA_IMPORT_PKG.DEBUG('Before EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data with parameters ' || x_return_status, d_progress);
2079 PON_UDA_IMPORT_PKG.DEBUG('l_base_object_name : ' || l_base_object_name, d_progress);
2080 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_row_table is as follows : ', d_progress);
2081 PON_UDA_IMPORT_PKG.DEBUG(l_attributes_row_table, d_progress);
2082 PON_UDA_IMPORT_PKG.DEBUG('l_attributes_data_table is as follows : ', d_progress);
2083 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
2084 PON_UDA_IMPORT_PKG.DEBUG('l_pk_column_name_value_pairs is as follows : ', d_progress);
2085 PON_UDA_IMPORT_PKG.DEBUG(l_pk_column_name_value_pairs, d_progress);
2086 PON_UDA_IMPORT_PKG.DEBUG('l_class_code_name_value_pairs is as follows : ', d_progress);
2087 PON_UDA_IMPORT_PKG.DEBUG(l_class_code_name_value_pairs, d_progress);
2088 d_progress := 500;
2089 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
2090 (
2091 p_api_version => 1.0
2092 ,p_object_name => l_base_object_name
2093 ,p_attributes_row_table => l_attributes_row_table
2094 ,p_attributes_data_table => l_attr_name_value_pairs
2095 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
2096 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
2097 ,x_failed_row_id_list => l_failed_row_id_list
2098 ,x_return_status => x_return_status
2099 ,x_errorcode => x_errorcode
2100 ,x_msg_count => x_msg_count
2101 ,x_msg_data => x_msg_data
2102 );
2103 d_progress := 510;
2104 END IF;
2105 PON_UDA_IMPORT_PKG.DEBUG('After Process_User_Attrs_Data ' || x_return_status, d_progress);
2106 PON_UDA_IMPORT_PKG.DEBUG('l_failed_row_id_list is : ' || l_failed_row_id_list, d_progress);
2107 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
2108 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
2109 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
2110 d_progress := 520;
2111 ELSE
2112 d_progress := 530;
2113 PON_UDA_IMPORT_PKG.UPDATE_STATUS
2114 (
2115 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
2116 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
2117 ,p_process_phase => PON_UDA_IMPORT_PKG.CUSTOM_VALIDATE
2118 ,p_pk1_value => p_pk1_value
2119 ,p_pk2_value => p_pk2_value
2120 ,p_pk3_value => p_pk3_value
2121 ,p_pk4_value => p_pk4_value
2122 ,p_pk5_value => p_pk5_value
2123 );
2124 d_progress := 540;
2125 PON_UDA_IMPORT_PKG.HANDLE_ERROR
2126 (
2127 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION (XV)'
2128 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION_XLE'
2129 ,p_attr_grp_id => NULL
2130 );
2131 d_progress := 550;
2132 END IF;
2133
2134 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2135 d_progress := 560;
2136 PON_UDA_IMPORT_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XPD) ' || x_return_status, d_progress);
2137 IF l_attributes_row_table.COUNT > 0 THEN
2138 d_progress := 570;
2139 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
2140 PON_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
2141 (
2142 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
2143 ,p_event => 'XPD'
2144 ,x_external_attr_value_pairs => x_external_attr_value_pairs
2145 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
2146 ,x_attr_name_value_pairs => l_attr_name_value_pairs
2147 ,x_attributes_row_table => l_attributes_row_table
2148 ,x_return_status => x_return_status
2149 ,x_errorcode => x_errorcode
2150 ,x_msg_count => x_msg_count
2151 ,x_msg_data => x_msg_data
2152 );
2153 PON_UDA_IMPORT_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
2154
2155 d_progress := 580;
2156 END IF;
2157 PON_UDA_IMPORT_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XPD) ' || x_return_status, d_progress);
2158 PON_UDA_IMPORT_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
2159 PON_UDA_IMPORT_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
2160 PON_UDA_IMPORT_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
2161 d_progress := 590;
2162 ELSE
2163 d_progress := 600;
2164 PON_UDA_IMPORT_PKG.UPDATE_STATUS
2165 (
2166 p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
2167 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
2168 ,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
2169 ,p_pk1_value => p_pk1_value
2170 ,p_pk2_value => p_pk2_value
2171 ,p_pk3_value => p_pk3_value
2172 ,p_pk4_value => p_pk4_value
2173 ,p_pk5_value => p_pk5_value
2174 );
2175 d_progress := 610;
2176 PON_UDA_IMPORT_PKG.HANDLE_ERROR
2177 (
2178 p_msg => 'There was an error in loading UDA Attributes'
2179 ,p_msg_code => 'ERROR_EGO_LOAD_ERROR'
2180 ,p_attr_grp_id => NULL
2181 );
2182 d_progress := 620;
2183 END IF;
2184 d_progress := 630;
2185 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2186 d_progress := 640;
2187 PON_UDA_IMPORT_PKG.UPDATE_STATUS
2188 (
2189 p_process_status => PON_UDA_IMPORT_PKG.COMPLETED
2190 ,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
2191 ,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
2192 ,p_pk1_value => p_pk1_value
2193 ,p_pk2_value => p_pk2_value
2194 ,p_pk3_value => p_pk3_value
2195 ,p_pk4_value => p_pk4_value
2196 ,p_pk5_value => p_pk5_value
2197 );
2198 d_progress := 650;
2199 END IF;
2200
2201 EXCEPTION
2202 WHEN OTHERS THEN
2203 l_err_txt := 'Exception in IMPORT_UDA_ATTRS with ERROR : ' || SQLERRM;
2204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205
2206 UPDATE PO_UDA_INTERFACE
2207 SET PK1_VALUE = NULL, PK2_VALUE=NULL, PROCESS_STATUS = 1, PROCESS_PHASE = NULL
2208 WHERE PK1_VALUE = p_pk1_value;
2209
2210 PON_UDA_IMPORT_PKG.HANDLE_ERROR
2211 (
2212 p_msg => l_err_txt
2213 ,p_msg_code => 'UNEX_EXEC_IMPRT_UDA_FUNC'
2214 ,p_attr_grp_id => NULL
2215 );
2216 END IMPORT_UDA_ATTRS;
2217
2218 PROCEDURE HANDLE_ERROR
2219 (
2220 p_msg IN VARCHAR2
2221 ,p_msg_code IN VARCHAR2
2222 ,p_attr_grp_id IN NUMBER
2223 )
2224 IS
2225 BEGIN
2226
2227 if fnd_global.CONC_REQUEST_ID IS NOT NULL AND fnd_global.CONC_REQUEST_ID <> -1 THEN
2228 PO_INTERFACE_ERRORS_SV1.insert_po_interface_errors_msg
2229 (
2230 X_interface_type => g_attr_grp_type,
2231 X_Interface_Header_ID => g_interface_reference_id,
2232 X_Interface_Line_Id => NULL,
2233 X_Interface_Dist_Id => NULL,
2234 X_error_message_text => p_msg,
2235 X_error_message_name => p_msg_code,
2236 X_column_name => NULL,
2237 X_table_name => NULL,
2238 X_batch_id => p_attr_grp_id
2239 );
2240 else
2241 PON_UDA_IMPORT_PKG.DEBUG ('IN HANDLE ERROR : '||p_msg ||' code ' ||p_msg_code ||' for attr grp id '||p_attr_grp_id, 0);
2242 end if;
2243
2244 END HANDLE_ERROR;
2245
2246 PROCEDURE UPDATE_STATUS
2247 (
2248 p_process_status IN NUMBER
2249 ,p_pre_process_status IN NUMBER
2250 ,p_process_phase IN VARCHAR2
2251 ,p_pk1_value IN NUMBER
2252 ,p_pk2_value IN NUMBER
2253 ,p_pk3_value IN NUMBER
2254 ,p_pk4_value IN NUMBER
2255 ,p_pk5_value IN NUMBER
2256 )
2257 IS
2258 BEGIN
2259 IF p_process_status IN (PON_UDA_IMPORT_PKG.PENDING, PON_UDA_IMPORT_PKG.IN_PROGRESS, PON_UDA_IMPORT_PKG.COMPLETED) THEN
2260 UPDATE PO_UDA_INTERFACE
2261 SET PROCESS_STATUS = p_process_status
2262 ,PROCESS_PHASE = p_process_phase
2263 WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
2264 AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
2265 AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
2266 AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
2267 AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
2268 AND PROCESS_STATUS = p_pre_process_status;
2269 --COMMIT;
2270 END IF;
2271 END;
2272
2273 PROCEDURE CONSTRUCT_PK_OBJECT
2274 (
2275 p_obj_name IN VARCHAR2
2276 ,p_pk1_value IN NUMBER
2277 ,p_pk2_value IN NUMBER DEFAULT NULL
2278 ,p_pk3_value IN NUMBER DEFAULT NULL
2279 ,p_pk4_value IN NUMBER DEFAULT NULL
2280 ,p_pk5_value IN NUMBER DEFAULT NULL
2281 ,x_pk_column_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
2282 )
2283 IS
2284 l_pk1_column_name VARCHAR2(200);
2285 l_pk2_column_name VARCHAR2(200);
2286 l_pk3_column_name VARCHAR2(200);
2287 l_pk4_column_name VARCHAR2(200);
2288 l_pk5_column_name VARCHAR2(200);
2289 BEGIN
2290
2291 x_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2292 BEGIN
2293 select o.pk1_column_name,
2294 o.pk2_column_name,
2295 o.pk3_column_name,
2296 o.pk4_column_name,
2297 o.pk5_column_name
2298 into l_pk1_column_name,
2299 l_pk2_column_name,
2300 l_pk3_column_name,
2301 l_pk4_column_name,
2302 l_pk5_column_name
2303 from fnd_objects_vl o
2304 where o.obj_name = p_obj_name;
2305
2306 PON_UDA_IMPORT_PKG.DEBUG('l_pk1_column_name :' || l_pk1_column_name, 0);
2307 PON_UDA_IMPORT_PKG.DEBUG('l_pk2_column_name :' || l_pk2_column_name, 0);
2308 PON_UDA_IMPORT_PKG.DEBUG('l_pk3_column_name :' || l_pk3_column_name, 0);
2309 PON_UDA_IMPORT_PKG.DEBUG('l_pk4_column_name :' || l_pk4_column_name, 0);
2310 PON_UDA_IMPORT_PKG.DEBUG('l_pk5_column_name :' || l_pk5_column_name, 0);
2311 EXCEPTION
2312 WHEN OTHERS THEN
2313 PON_UDA_IMPORT_PKG.DEBUG('Exception while trying to get the PK values', 0);
2314 RAISE;
2315 END;
2316
2317 IF p_pk1_value IS NOT NULL THEN
2318 x_pk_column_name_value_pairs.EXTEND(1);
2319 x_pk_column_name_value_pairs(x_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
2320 (
2321 l_pk1_column_name, p_pk1_value
2322 );
2323 END IF;
2324 IF p_pk2_value IS NOT NULL THEN
2325 x_pk_column_name_value_pairs.EXTEND(1);
2326 x_pk_column_name_value_pairs(x_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
2327 (
2328 l_pk2_column_name, p_pk2_value
2329 );
2330 END IF;
2331 IF p_pk3_value IS NOT NULL THEN
2332 x_pk_column_name_value_pairs.EXTEND(1);
2333 x_pk_column_name_value_pairs(x_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
2334 (
2335 l_pk3_column_name, p_pk3_value
2336 );
2337 END IF;
2338 IF p_pk4_value IS NOT NULL THEN
2339 x_pk_column_name_value_pairs.EXTEND(1);
2340 x_pk_column_name_value_pairs(x_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
2341 (
2342 l_pk4_column_name, p_pk4_value
2343 );
2344 END IF;
2345 IF p_pk5_value IS NOT NULL THEN
2346 x_pk_column_name_value_pairs.EXTEND(1);
2347 x_pk_column_name_value_pairs(x_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
2348 (
2349 l_pk5_column_name, p_pk5_value
2350 );
2351 END IF;
2352
2353 EXCEPTION
2354 WHEN OTHERS THEN
2355 --PON_UDA_IMPORT_PKG.DEBUG(SQLERRM, 0);
2356 RAISE;
2357 END;
2358
2359 PROCEDURE SET_EXT_ATTR_NAME_VALUE_PAIR
2360 (
2361 p_ext_attr_name IN VARCHAR2
2362 ,p_ext_attr_value IN VARCHAR2
2363 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
2364 )
2365 IS
2366 MSG_DATA_IND NUMBER := 1;
2367 MSG_COUNT_IND NUMBER := 2;
2368 ERRORCODE_IND NUMBER := 3;
2369 RETURN_STATUS_IND NUMBER := 4;
2370 VENDOR_CONTACT_ID_IND NUMBER := 5;
2371 VENDOR_SITE_ID_IND NUMBER := 6;
2372 VENDOR_ID_IND NUMBER := 7;
2373 ITEM_ID_IND NUMBER := 8;
2374 LINE_TYPE_ID_IND NUMBER := 9;
2375 SHIP_TO_LOC_ID_IND NUMBER := 10;
2376 ORG_ID_IND NUMBER := 11;
2377 AGENT_ID_IND NUMBER := 12;
2378 CONTRACT_TYPE_IND NUMBER := 13;
2379 CATEGORY_ID_IND NUMBER := 14;
2380 PREPARER_ID_IND NUMBER := 15;
2381 BASE_AUCTION_HEADER_ID_IND NUMBER := 16;
2382 DELIMITER_IND NUMBER := 17;
2383 SOURCE_DOC_NUMBER_IND NUMBER := 18;
2384
2385 l_index NUMBER;
2386
2387 BEGIN
2388 PON_UDA_IMPORT_PKG.DEBUG(' Starting SET_EXT_ATTR_NAME_VALUE_PAIR with values ', 0);
2389 PON_UDA_IMPORT_PKG.DEBUG(' p_ext_attr_name ' || p_ext_attr_name, 0);
2390 PON_UDA_IMPORT_PKG.DEBUG(' p_ext_attr_value ' || p_ext_attr_value, 0);
2391
2392 IF x_external_attr_value_pairs IS NULL THEN
2393 x_external_attr_value_pairs := EGO_COL_NAME_VALUE_PAIR_TABLE(
2394 EGO_COL_NAME_VALUE_PAIR_OBJ
2395 (
2396 'x_msg_data'
2397 ,NULL
2398 ),
2399 EGO_COL_NAME_VALUE_PAIR_OBJ
2400 (
2401 'x_msg_count'
2402 ,NULL
2403 ),
2404 EGO_COL_NAME_VALUE_PAIR_OBJ
2405 (
2406 'x_errorcode'
2407 ,NULL
2408 ),
2409 EGO_COL_NAME_VALUE_PAIR_OBJ
2410 (
2411 'x_return_status'
2412 ,NULL
2413 ),
2414 EGO_COL_NAME_VALUE_PAIR_OBJ
2415 (
2416 'VENDOR_CONTACT_ID'
2417 ,NULL
2418 ),
2419 EGO_COL_NAME_VALUE_PAIR_OBJ
2420 (
2421 'VENDOR_SITE_ID'
2422 ,NULL
2423 ),
2424 EGO_COL_NAME_VALUE_PAIR_OBJ
2425 (
2426 'VENDOR_ID'
2427 ,NULL
2428 ),
2429 EGO_COL_NAME_VALUE_PAIR_OBJ
2430 (
2431 'ITEM_ID'
2432 ,NULL
2433 ),
2434 EGO_COL_NAME_VALUE_PAIR_OBJ
2435 (
2436 'LINE_TYPE_ID'
2437 ,NULL
2438 ),
2439 EGO_COL_NAME_VALUE_PAIR_OBJ
2440 (
2441 'SHIP_TO_LOC_ID'
2442 ,NULL
2443 ),
2444 EGO_COL_NAME_VALUE_PAIR_OBJ
2445 (
2446 'ORG_ID'
2447 ,NULL
2448 ),
2449 EGO_COL_NAME_VALUE_PAIR_OBJ
2450 (
2451 'AGENT_ID'
2452 ,NULL
2453 ),
2454 EGO_COL_NAME_VALUE_PAIR_OBJ
2455 (
2456 'CONTRACT_TYPE'
2457 ,NULL
2458 ),
2459 EGO_COL_NAME_VALUE_PAIR_OBJ
2460 (
2461 'CATEGORY_ID'
2462 ,NULL
2463 ),
2464 EGO_COL_NAME_VALUE_PAIR_OBJ
2465 (
2466 'PREPARER_ID'
2467 ,NULL
2468 ),
2469 EGO_COL_NAME_VALUE_PAIR_OBJ
2470 (
2471 'BASE_AUCTION_HEADER_ID'
2472 ,NULL
2473 ),
2474 EGO_COL_NAME_VALUE_PAIR_OBJ
2475 (
2476 'DELIMITER'
2477 ,NULL
2478 ),
2479 EGO_COL_NAME_VALUE_PAIR_OBJ
2480 (
2481 'p_source_doc_number'
2482 ,NULL
2483 )
2484 );
2485 END IF;
2486
2487 IF p_ext_attr_name = 'x_msg_data' THEN
2488 l_index := MSG_DATA_IND;
2489 ELSIF p_ext_attr_name = 'x_msg_count' THEN
2490 l_index := MSG_COUNT_IND;
2491 ELSIF p_ext_attr_name = 'x_errorcode' THEN
2492 l_index := ERRORCODE_IND;
2493 ELSIF p_ext_attr_name = 'x_return_status' THEN
2494 l_index := RETURN_STATUS_IND;
2495 ELSIF p_ext_attr_name = 'VENDOR_CONTACT_ID' THEN
2496 l_index := VENDOR_CONTACT_ID_IND;
2497 ELSIF p_ext_attr_name = 'VENDOR_SITE_ID' THEN
2498 l_index := VENDOR_SITE_ID_IND;
2499 ELSIF p_ext_attr_name = 'VENDOR_ID' THEN
2500 l_index := VENDOR_ID_IND;
2501 ELSIF p_ext_attr_name = 'ITEM_ID' THEN
2502 l_index := ITEM_ID_IND;
2503 ELSIF p_ext_attr_name = 'LINE_TYPE_ID' THEN
2504 l_index := LINE_TYPE_ID_IND;
2505 ELSIF p_ext_attr_name = 'SHIP_TO_LOC_ID' THEN
2506 l_index := SHIP_TO_LOC_ID_IND;
2507 ELSIF p_ext_attr_name = 'ORG_ID' THEN
2508 l_index := ORG_ID_IND;
2509 ELSIF p_ext_attr_name = 'AGENT_ID' THEN
2510 l_index := AGENT_ID_IND;
2511 ELSIF p_ext_attr_name = 'CONTRACT_TYPE' THEN
2512 l_index := CONTRACT_TYPE_IND;
2513 ELSIF p_ext_attr_name = 'CATEGORY_ID' THEN
2514 l_index := CATEGORY_ID_IND;
2515 ELSIF p_ext_attr_name = 'PREPARER_ID' THEN
2516 l_index := PREPARER_ID_IND;
2517 ELSIF p_ext_attr_name = 'BASE_AUCTION_HEADER_ID' THEN
2518 l_index := BASE_AUCTION_HEADER_ID_IND;
2519 ELSIF p_ext_attr_name = 'DELIMITER' THEN
2520 l_index := DELIMITER_IND;
2521 ELSIF p_ext_attr_name = 'p_source_doc_number' THEN
2522 l_index := SOURCE_DOC_NUMBER_IND;
2523 ELSE
2524 PON_UDA_IMPORT_PKG.DEBUG('The Name Value Pair does not have an index defined (Check PON_UDA_IMPORT_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR)', 0);
2525 RETURN;
2526 END IF;
2527
2528 x_external_attr_value_pairs(l_index) := EGO_COL_NAME_VALUE_PAIR_OBJ(p_ext_attr_name, p_ext_attr_value);
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 PON_UDA_IMPORT_PKG.DEBUG(SQLERRM, 0);
2532 RAISE;
2533 END;
2534
2535 PROCEDURE GET_EXT_ATTR_NAME_VALUE_PAIR
2536 (
2537 p_ext_attr_name IN VARCHAR2
2538 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
2539 ,x_ext_name_val_obj OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_OBJ
2540 )
2541 IS
2542 MSG_DATA_IND NUMBER := 1;
2543 MSG_COUNT_IND NUMBER := 2;
2544 ERRORCODE_IND NUMBER := 3;
2545 RETURN_STATUS_IND NUMBER := 4;
2546 VENDOR_CONTACT_ID_IND NUMBER := 5;
2547 VENDOR_SITE_ID_IND NUMBER := 6;
2548 VENDOR_ID_IND NUMBER := 7;
2549 ITEM_ID_IND NUMBER := 8;
2550 LINE_TYPE_ID_IND NUMBER := 9;
2551 SHIP_TO_LOC_ID_IND NUMBER := 10;
2552 ORG_ID_IND NUMBER := 11;
2553 AGENT_ID_IND NUMBER := 12;
2554 CONTRACT_TYPE_IND NUMBER := 13;
2555 CATEGORY_ID_IND NUMBER := 14;
2556 PREPARER_ID_IND NUMBER := 15;
2557 BASE_AUCTION_HEADER_ID_IND NUMBER := 16;
2558 DELIMITER_IND NUMBER := 17;
2559 SOURCE_DOC_NUMBER_IND NUMBER := 18;
2560
2561 l_index NUMBER;
2562
2563 BEGIN
2564
2565 IF p_ext_attr_name = 'x_msg_data' THEN
2566 x_ext_name_val_obj := x_external_attr_value_pairs(MSG_DATA_IND);
2567 ELSIF p_ext_attr_name = 'x_msg_count' THEN
2568 x_ext_name_val_obj := x_external_attr_value_pairs(MSG_COUNT_IND);
2569 ELSIF p_ext_attr_name = 'x_errorcode' THEN
2570 x_ext_name_val_obj := x_external_attr_value_pairs(ERRORCODE_IND);
2571 ELSIF p_ext_attr_name = 'x_return_status' THEN
2572 x_ext_name_val_obj := x_external_attr_value_pairs(RETURN_STATUS_IND);
2573 ELSIF p_ext_attr_name = 'VENDOR_CONTACT_ID' THEN
2574 x_ext_name_val_obj := x_external_attr_value_pairs(VENDOR_CONTACT_ID_IND);
2575 ELSIF p_ext_attr_name = 'VENDOR_SITE_ID' THEN
2576 x_ext_name_val_obj := x_external_attr_value_pairs(VENDOR_SITE_ID_IND);
2577 ELSIF p_ext_attr_name = 'VENDOR_ID' THEN
2578 x_ext_name_val_obj := x_external_attr_value_pairs(VENDOR_ID_IND);
2579 ELSIF p_ext_attr_name = 'ITEM_ID' THEN
2580 x_ext_name_val_obj := x_external_attr_value_pairs(ITEM_ID_IND);
2581 ELSIF p_ext_attr_name = 'LINE_TYPE_ID' THEN
2582 x_ext_name_val_obj := x_external_attr_value_pairs(LINE_TYPE_ID_IND);
2583 ELSIF p_ext_attr_name = 'SHIP_TO_LOC_ID' THEN
2584 x_ext_name_val_obj := x_external_attr_value_pairs(SHIP_TO_LOC_ID_IND);
2585 ELSIF p_ext_attr_name = 'ORG_ID' THEN
2586 x_ext_name_val_obj := x_external_attr_value_pairs(ORG_ID_IND);
2587 ELSIF p_ext_attr_name = 'AGENT_ID' THEN
2588 x_ext_name_val_obj := x_external_attr_value_pairs(AGENT_ID_IND);
2589 ELSIF p_ext_attr_name = 'CONTRACT_TYPE' THEN
2590 x_ext_name_val_obj := x_external_attr_value_pairs(CONTRACT_TYPE_IND);
2591 ELSIF p_ext_attr_name = 'CATEGORY_ID' THEN
2592 x_ext_name_val_obj := x_external_attr_value_pairs(CATEGORY_ID_IND);
2593 ELSIF p_ext_attr_name = 'PREPARER_ID' THEN
2594 x_ext_name_val_obj := x_external_attr_value_pairs(PREPARER_ID_IND);
2595 ELSIF p_ext_attr_name = 'BASE_AUCTION_HEADER_ID' THEN
2596 x_ext_name_val_obj := x_external_attr_value_pairs(BASE_AUCTION_HEADER_ID_IND);
2597 ELSIF p_ext_attr_name = 'DELIMITER' THEN
2598 x_ext_name_val_obj := x_external_attr_value_pairs(DELIMITER_IND);
2599 ELSIF p_ext_attr_name = 'p_source_doc_number' THEN
2600 x_ext_name_val_obj := x_external_attr_value_pairs(SOURCE_DOC_NUMBER_IND);
2601 ELSE
2602 x_ext_name_val_obj := NULL;
2603 PON_UDA_IMPORT_PKG.DEBUG('The Name Value Pair does not have an index defined (Check PON_UDA_IMPORT_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR)', 0);
2604 END IF;
2605
2606 EXCEPTION
2607 WHEN OTHERS THEN
2608 PON_UDA_IMPORT_PKG.DEBUG(SQLERRM, 0);
2609 RAISE;
2610 END;
2611
2612 PROCEDURE DEBUG
2613 (
2614 p_structure_array IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2615 ,p_progress IN NUMBER
2616 )
2617 IS
2618 l_structure_ind NUMBER;
2619 l_structure EGO_COL_NAME_VALUE_PAIR_OBJ;
2620 BEGIN
2621 IF p_structure_array IS NOT NULL THEN
2622 l_structure_ind := p_structure_array.FIRST;
2623 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2624 LOOP
2625 l_structure := p_structure_array(l_structure_ind);
2626 IF l_structure IS NOT NULL THEN
2627 PON_UDA_IMPORT_PKG.DEBUG('NAME at index : ' || l_structure_ind || ' is : ' || l_structure.NAME, 0);
2628 PON_UDA_IMPORT_PKG.DEBUG('VALUE at index : ' || l_structure_ind || ' is : ' || l_structure.VALUE, 0);
2629 ELSE
2630 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2631 END IF;
2632 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2633 END LOOP;
2634 ELSE
2635 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2636 END IF;
2637 END DEBUG;
2638
2639 PROCEDURE DEBUG
2640 (
2641 p_structure_array IN EGO_COL_NAME_VALUE_PAIR_TABLE
2642 ,p_progress IN NUMBER
2643 )
2644 IS
2645 l_structure_ind NUMBER;
2646 l_structure EGO_COL_NAME_VALUE_PAIR_OBJ;
2647 BEGIN
2648 IF p_structure_array IS NOT NULL THEN
2649 l_structure_ind := p_structure_array.FIRST;
2650 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2651 LOOP
2652 l_structure := p_structure_array(l_structure_ind);
2653 IF l_structure IS NOT NULL THEN
2654 PON_UDA_IMPORT_PKG.DEBUG('NAME at index : ' || l_structure_ind || ' is : ' || l_structure.NAME, 0);
2655 PON_UDA_IMPORT_PKG.DEBUG('VALUE at index : ' || l_structure_ind || ' is : ' || l_structure.VALUE, 0);
2656 ELSE
2657 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2658 END IF;
2659 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2660 END LOOP;
2661 ELSE
2662 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2663 END IF;
2664 END DEBUG;
2665
2666 PROCEDURE DEBUG
2667 (
2668 p_structure_array IN EGO_USER_ATTR_DATA_TABLE
2669 ,p_progress IN NUMBER
2670 )
2671 IS
2672 l_structure_ind NUMBER;
2673 l_structure EGO_USER_ATTR_DATA_OBJ;
2674 BEGIN
2675 IF p_structure_array IS NOT NULL THEN
2676 l_structure_ind := p_structure_array.FIRST;
2677 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2678 LOOP
2679 l_structure := p_structure_array(l_structure_ind);
2680 IF l_structure IS NOT NULL THEN
2681 PON_UDA_IMPORT_PKG.DEBUG('ROW_IDENTIFIER at index : ' || l_structure_ind || ' is : ' || l_structure.ROW_IDENTIFIER, 0);
2682 PON_UDA_IMPORT_PKG.DEBUG('ATTR_NAME at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_NAME, 0);
2683 PON_UDA_IMPORT_PKG.DEBUG('ATTR_VALUE_STR at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_VALUE_STR, 0);
2684 PON_UDA_IMPORT_PKG.DEBUG('ATTR_VALUE_NUM at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_VALUE_NUM, 0);
2685 PON_UDA_IMPORT_PKG.DEBUG('ATTR_VALUE_DATE at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_VALUE_DATE, 0);
2686 PON_UDA_IMPORT_PKG.DEBUG('ATTR_DISP_VALUE at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_DISP_VALUE, 0);
2687 PON_UDA_IMPORT_PKG.DEBUG('ATTR_UNIT_OF_MEASURE at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_UNIT_OF_MEASURE, 0);
2688 PON_UDA_IMPORT_PKG.DEBUG('USER_ROW_IDENTIFIER at index : ' || l_structure_ind || ' is : ' || l_structure.USER_ROW_IDENTIFIER, 0);
2689 ELSE
2690 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2691 END IF;
2692 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2693 END LOOP;
2694 ELSE
2695 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2696 END IF;
2697
2698 END DEBUG;
2699
2700 PROCEDURE DEBUG
2701 (
2702 p_structure_array IN EGO_USER_ATTR_ROW_TABLE
2703 ,p_progress IN NUMBER
2704 )
2705 IS
2706 l_structure_ind NUMBER;
2707 l_structure EGO_USER_ATTR_ROW_OBJ;
2708 BEGIN
2709 IF p_structure_array IS NOT NULL THEN
2710 l_structure_ind := p_structure_array.FIRST;
2711 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2712 LOOP
2713 l_structure := p_structure_array(l_structure_ind);
2714 IF l_structure IS NOT NULL THEN
2715 PON_UDA_IMPORT_PKG.DEBUG('ROW_IDENTIFIER at index : ' || l_structure_ind || ' is : ' || l_structure.ROW_IDENTIFIER, 0);
2716 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_ID at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_ID, 0);
2717 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_APP_ID at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_APP_ID, 0);
2718 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_TYPE at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_TYPE, 0);
2719 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_NAME at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_NAME, 0);
2720 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL, 0);
2721 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL1 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_1, 0);
2722 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL2 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_2, 0);
2723 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL3 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_3, 0);
2724 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL4 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_4, 0);
2725 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL5 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_5, 0);
2726 PON_UDA_IMPORT_PKG.DEBUG('TRANSACTION_TYPE at index : ' || l_structure_ind || ' is : ' || l_structure.TRANSACTION_TYPE, 0);
2727 ELSE
2728 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2729 END IF;
2730 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2731 END LOOP;
2732 ELSE
2733 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2734 END IF;
2735 END DEBUG;
2736
2737 PROCEDURE DEBUG
2738 (
2739 p_structure_array IN PO_UDA_USAGE_OBJECT_ARRAY
2740 ,p_progress IN NUMBER
2741 )
2742 IS
2743 l_structure_ind NUMBER;
2744 l_structure PO_UDA_USAGE_OBJECT;
2745 BEGIN
2746 IF p_structure_array IS NOT NULL THEN
2747 l_structure_ind := p_structure_array.FIRST;
2748 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2749 LOOP
2750 l_structure := p_structure_array(l_structure_ind);
2751 IF l_structure IS NOT NULL THEN
2752 PON_UDA_IMPORT_PKG.DEBUG('TEMPLATE_ID at index : ' || l_structure_ind || ' is : ' || l_structure.TEMPLATE_ID, 0);
2753 PON_UDA_IMPORT_PKG.DEBUG('USAGE_TYPE at index : ' || l_structure_ind || ' is : ' || l_structure.USAGE_TYPE, 0);
2754 PON_UDA_IMPORT_PKG.DEBUG('Context Object in Usage Object is as follows', 0);
2755 PON_UDA_IMPORT_PKG.DEBUG(l_structure.Context, 0);
2756 PON_UDA_IMPORT_PKG.DEBUG('End of Context Object in the Usage Object', 0);
2757 PON_UDA_IMPORT_PKG.DEBUG('Attr Group IDs in Usage Object is as follows', 0);
2758 PON_UDA_IMPORT_PKG.DEBUG(l_structure.Attr_group_Id, 0);
2759 PON_UDA_IMPORT_PKG.DEBUG('End of Attr Group IDs in the Usage Object', 0);
2760 ELSE
2761 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2762 END IF;
2763 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2764 END LOOP;
2765 ELSE
2766 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2767 END IF;
2768 END DEBUG;
2769
2770 PROCEDURE DEBUG
2771 (
2772 p_structure_array IN PO_TBL_NUMBER
2773 ,p_progress IN NUMBER
2774 )
2775 IS
2776 l_structure_ind NUMBER;
2777 l_structure NUMBER;
2778 BEGIN
2779 IF p_structure_array IS NOT NULL THEN
2780 l_structure_ind := p_structure_array.FIRST;
2781 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2782 LOOP
2783 l_structure := p_structure_array(l_structure_ind);
2784 IF l_structure IS NOT NULL THEN
2785 PON_UDA_IMPORT_PKG.DEBUG('PO_TBL_NUMBER Table at index : ' || l_structure_ind || ' is : ' || l_structure, 0);
2786 ELSE
2787 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2788 END IF;
2789 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2790 END LOOP;
2791 ELSE
2792 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2793 END IF;
2794 END DEBUG;
2795
2796 PROCEDURE DEBUG
2797 (
2798 p_structure_array IN EGO_ATTR_GROUP_REQUEST_TABLE
2799 ,p_progress IN NUMBER
2800 )
2801 IS
2802 l_structure_ind NUMBER;
2803 l_structure EGO_ATTR_GROUP_REQUEST_OBJ;
2804 BEGIN
2805 IF p_structure_array IS NOT NULL THEN
2806 l_structure_ind := p_structure_array.FIRST;
2807 WHILE (l_structure_ind IS NOT NULL AND l_structure_ind <= p_structure_array.LAST)
2808 LOOP
2809 l_structure := p_structure_array(l_structure_ind);
2810
2811 IF l_structure IS NOT NULL THEN
2812 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_ID at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_ID, 0);
2813 PON_UDA_IMPORT_PKG.DEBUG('APPLICATION_ID at index : ' || l_structure_ind || ' is : ' || l_structure.APPLICATION_ID, 0);
2814 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_TYPE at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_TYPE, 0);
2815 PON_UDA_IMPORT_PKG.DEBUG('ATTR_GROUP_NAME at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_GROUP_NAME, 0);
2816 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL, 0);
2817 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL1 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_1, 0);
2818 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL2 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_2, 0);
2819 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL3 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_3, 0);
2820 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL4 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_4, 0);
2821 PON_UDA_IMPORT_PKG.DEBUG('DATA_LEVEL5 at index : ' || l_structure_ind || ' is : ' || l_structure.DATA_LEVEL_5, 0);
2822 PON_UDA_IMPORT_PKG.DEBUG('ATTR_NAME_LIST at index : ' || l_structure_ind || ' is : ' || l_structure.ATTR_NAME_LIST, 0);
2823 ELSE
2824 PON_UDA_IMPORT_PKG.DEBUG('NO VALUE AT THIS INDEX', 0);
2825 END IF;
2826 l_structure_ind := p_structure_array.NEXT(l_structure_ind);
2827 END LOOP;
2828 ELSE
2829 PON_UDA_IMPORT_PKG.DEBUG('Structure is NULL', 0);
2830 END IF;
2831 END DEBUG;
2832
2833 PROCEDURE DEBUG
2834 (
2835 p_msg IN VARCHAR2
2836 ,p_progress IN NUMBER
2837 )
2838 IS
2839 BEGIN
2840 IF PO_LOG.d_stmt THEN
2841 PO_LOG.stmt('PON_UDA_IMPORT_PKG', p_progress, 'Message: ', p_msg);
2842 END IF;
2843 END DEBUG;
2844
2845 END PON_UDA_IMPORT_PKG;