[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PO_UDA_IMPORT_PKG
Source
1 PACKAGE BODY po_uda_import_pkg AS
2 /* $Header: PO_UDA_IMPORT_PKG.plb 120.10.12020000.4 2013/02/19 10:06:50 pamandav ship $ */
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 FUNCTION validate_uda_req_attributes(p_transaction_id IN NUMBER ) RETURN VARCHAR2 ;
20
21
22 --------------------------------------------------------------------------------------------------------------------
23 --Start of Comments
24 --Name: POPULATE_STRUCTURES
25 --Procedure:
26 -- This procedure populates the structures EGO_COL_NAME_VALUE_PAIR_ARRAY, EGO_USER_ATTR_DATA_TABLE,
27 -- EGO_USER_ATTR_ROW_TABLE, EGO_COL_NAME_VALUE_PAIR_ARRAY from the data in the interface table PO_UDA_INTERFACE
28 --Parameters:
29 --IN:
30 -- 1.p_transaction_id
31 -- Unique id of the transaction
32 -- 2. p_attr_grp_type
33 -- The attribute Group Type (Entity Type)
34 -- 3. p_pk1_value, p_pk2_value, p_pk3_value, p_pk4_value, p_pk5_value
35 -- The Primary Key values for the UDA Attributes
36 --IN/OUT:
37 -- 1. x_base_object_name
38 -- This variable would contain the base object name
39 -- 2. x_pk_column_name_value_pairs
40 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
41 -- 3. x_attr_name_value_pairs
42 -- This variable would contain a table of type EGO_USER_ATTR_DATA_TABLE with the attribute and its value pairs
43 -- 4. x_attributes_row_table
44 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
45 -- 5. x_class_code_name_value_pairs
46 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the template details
47 --OUT:
48 -- 1. x_return_status - Generic Out parameter having return status
49 -- 2. x_errorcode - Generic Out parameter having error code
50 -- 3. x_msg_count - Generic Out parameter having return msg Count
51 -- 4. x_msg_data - Generic Out parameter having return msg
52 --
53 --End of Comments
54 --------------------------------------------------------------------------------------------------------------------
55
56 PROCEDURE POPULATE_STRUCTURES
57 (
58 p_transaction_id IN NUMBER DEFAULT NULL
59 ,p_attr_grp_type IN VARCHAR2
60 ,p_pk1_value IN NUMBER
61 ,p_pk2_value IN NUMBER
62 ,p_pk3_value IN NUMBER
63 ,p_pk4_value IN NUMBER
64 ,p_pk5_value IN NUMBER
65 ,x_base_object_name IN OUT NOCOPY VARCHAR2
66 ,x_pk_column_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
67 ,x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
68 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
69 ,x_class_code_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
70 ,x_return_status OUT NOCOPY VARCHAR2
71 ,x_errorcode OUT NOCOPY NUMBER
72 ,x_msg_count OUT NOCOPY NUMBER
73 ,x_msg_data OUT NOCOPY VARCHAR2
74 )
75
76 IS
77 d_progress NUMBER := 0;
78 l_interface_reference_id NUMBER;
79 l_transaction_id NUMBER;
80 l_row_identifier NUMBER;
81 l_attr_group_id NUMBER;
82 l_attr_group_app_id NUMBER;
83 l_attr_group_name VARCHAR2(30);
84 l_data_level_1 VARCHAR2(150);
85 l_data_level_2 VARCHAR2(40);
86 l_data_level_3 VARCHAR2(40);
87 l_transaction_type VARCHAR2(10);
88 l_attr_name VARCHAR2(30);
89 l_attr_value_str VARCHAR2(1000);
90 l_attr_value_num NUMBER;
91 l_attr_value_date DATE;
92 l_attr_disp_value VARCHAR2(1000);
93 l_attr_unit_of_measure VARCHAR2(3);
94 l_user_row_identifier NUMBER;
95 l_uda_template_id NUMBER;
96 l_process_status NUMBER;
97 l_process_phase VARCHAR2(50);
98 l_request_id NUMBER;
99 l_program_id NUMBER;
100 l_program_application_id NUMBER;
101 l_program_update_date DATE;
102 l_last_update_date DATE;
103 l_creation_date DATE;
104 l_last_update_login NUMBER;
105 l_created_by NUMBER;
106 l_last_updated_by NUMBER;
107 l_created_by_name VARCHAR2(100);
108 l_last_updated_by_name VARCHAR2(100);
109 l_interface_source_code VARCHAR2(100);
110 l_interface_source_line_id NUMBER;
111 l_pk1_column_name VARCHAR2(500);
112 l_pk2_column_name VARCHAR2(500);
113 l_pk3_column_name VARCHAR2(500);
114 l_pk4_column_name VARCHAR2(500);
115 l_pk5_column_name VARCHAR2(500);
116 l_pk1_column_type VARCHAR2(500);
117 l_pk2_column_type VARCHAR2(500);
118 l_pk3_column_type VARCHAR2(500);
119 l_pk4_column_type VARCHAR2(500);
120 l_pk5_column_type VARCHAR2(500);
121
122
123 l_count NUMBER := 0;
124
125 CURSOR cur_uda_interface_recs
126 (
127 p_pk1_value NUMBER
128 ,p_pk2_value NUMBER
129 ,p_pk3_value NUMBER
130 ,p_pk4_value NUMBER
131 ,p_pk5_value NUMBER
132 ,p_attr_grp_type VARCHAR2
133 )IS
134 SELECT INTERFACE_REFERENCE_ID
135 ,TRANSACTION_ID
136 ,ROW_IDENTIFIER
137 ,ATTR_GROUP_ID
138 ,ATTR_GROUP_APP_ID
139 ,ATTR_GROUP_NAME
140 ,DATA_LEVEL_1
141 ,DATA_LEVEL_2
142 ,DATA_LEVEL_3
143 ,TRANSACTION_TYPE
144 ,ATTR_NAME
145 ,ATTR_VALUE_STR
146 ,ATTR_VALUE_NUM
147 ,ATTR_VALUE_DATE
148 ,ATTR_DISP_VALUE
149 ,ATTR_UNIT_OF_MEASURE
150 ,USER_ROW_IDENTIFIER
151 ,UDA_TEMPLATE_ID
152 ,PROCESS_STATUS
153 ,PROCESS_PHASE
154 ,REQUEST_ID
155 ,PROGRAM_ID
156 ,PROGRAM_APPLICATION_ID
157 ,PROGRAM_UPDATE_DATE
158 ,LAST_UPDATE_DATE
159 ,CREATION_DATE
160 ,LAST_UPDATE_LOGIN
161 ,CREATED_BY
162 ,LAST_UPDATED_BY
163 ,CREATED_BY_NAME
164 ,LAST_UPDATED_BY_NAME
165 ,INTERFACE_SOURCE_CODE
166 ,INTERFACE_SOURCE_LINE_ID
167 FROM PO_UDA_INTERFACE
168 WHERE PROCESS_STATUS = PO_UDA_IMPORT_PKG.IN_PROGRESS
169 AND NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
170 AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
171 AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
172 AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
173 AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
174 AND Nvl(transaction_id, -999) = Nvl(p_transaction_id, -999) --PAR Project
175 AND ATTR_GROUP_TYPE = p_attr_grp_type
176 ORDER BY ATTR_GROUP_ID;
177
178 l_err_txt VARCHAR2(2000);
179
180
181 BEGIN
182 d_progress := 10;
183 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_UDA_IMPORT_PKG.POPULATE_STRUCTURES with parameters' , d_progress);
184 PO_UDA_DEFAULTING_PKG.DEBUG('p_attr_grp_type : ' || p_attr_grp_type, d_progress);
185 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk1_value : ' || p_pk1_value, d_progress);
186 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk2_value : ' || p_pk2_value, d_progress);
187 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk3_value : ' || p_pk2_value, d_progress);
188 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk4_value : ' || p_pk4_value, d_progress);
189 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk5_value : ' || p_pk5_value, d_progress);
190 PO_UDA_DEFAULTING_PKG.DEBUG('x_base_object_name : ' || x_base_object_name, d_progress);
191 PO_UDA_DEFAULTING_PKG.DEBUG('x_pk_column_name_value_pairs is as follows :', d_progress);
192 PO_UDA_DEFAULTING_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
193 PO_UDA_DEFAULTING_PKG.DEBUG('x_attr_name_value_pairs is as follows :', d_progress);
194 PO_UDA_DEFAULTING_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
195 PO_UDA_DEFAULTING_PKG.DEBUG('x_attributes_row_table is as follows :', d_progress);
196 PO_UDA_DEFAULTING_PKG.DEBUG(x_attributes_row_table, d_progress);
197 PO_UDA_DEFAULTING_PKG.DEBUG('x_class_code_name_value_pairs is as follows :', d_progress);
198 PO_UDA_DEFAULTING_PKG.DEBUG(x_class_code_name_value_pairs, d_progress);
199 d_progress := 20;
200
201 BEGIN
202 d_progress := 30;
203 SELECT INTERFACE_REFERENCE_ID
204 INTO g_interface_reference_id
205 FROM PO_UDA_INTERFACE
206 WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
207 AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
208 AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
209 AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
210 AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
211 AND ROWNUM < 2;
212 d_progress := 40;
213 PO_UDA_DEFAULTING_PKG.DEBUG('INTERFACE_REFERENCE_ID : ' || g_interface_reference_id, d_progress);
214 EXCEPTION
215 WHEN OTHERS THEN
216 d_progress := 50;
217 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the INTERFACE_REFERENCE_ID', d_progress);
218 RAISE;
219 END;
220
221 d_progress := 60;
222 x_base_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(p_attr_grp_type).l_object_name;
223 PO_UDA_DEFAULTING_PKG.DEBUG('The Base object is ' || x_base_object_name, d_progress);
224 d_progress := 70;
225
226 PO_UDA_DEFAULTING_PKG.DEBUG('Before PO_UDA_DEFAULTING_PKG.CONSTRUCT_PK_OBJECT' , d_progress);
227 PO_UDA_DEFAULTING_PKG.CONSTRUCT_PK_OBJECT(
228 x_base_object_name
229 ,p_pk1_value
230 ,p_pk2_value
231 ,p_pk3_value
232 ,p_pk4_value
233 ,p_pk5_value
234 ,x_pk_column_name_value_pairs
235 );
236 d_progress := 80;
237 PO_UDA_DEFAULTING_PKG.DEBUG('Before PO_UDA_DEFAULTING_PKG.CONSTRUCT_PK_OBJECT' , d_progress);
238 PO_UDA_DEFAULTING_PKG.DEBUG('x_pk_column_name_value_pairs is as follows :', d_progress);
239 PO_UDA_DEFAULTING_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
240
241 OPEN cur_uda_interface_recs (p_pk1_value, p_pk2_value, p_pk3_value, p_pk4_value, p_pk5_value, p_attr_grp_type);
242 d_progress := 90;
243 LOOP
244 d_progress := 100;
245 FETCH cur_uda_interface_recs
246 INTO l_interface_reference_id
247 ,l_transaction_id
248 ,l_row_identifier
249 ,l_attr_group_id
250 ,l_attr_group_app_id
251 ,l_attr_group_name
252 ,l_data_level_1
253 ,l_data_level_2
254 ,l_data_level_3
255 ,l_transaction_type
256 ,l_attr_name
257 ,l_attr_value_str
258 ,l_attr_value_num
259 ,l_attr_value_date
260 ,l_attr_disp_value
261 ,l_attr_unit_of_measure
262 ,l_user_row_identifier
263 ,l_uda_template_id
264 ,l_process_status
265 ,l_process_phase
266 ,l_request_id
267 ,l_program_id
268 ,l_program_application_id
269 ,l_program_update_date
270 ,l_last_update_date
271 ,l_creation_date
272 ,l_last_update_login
273 ,l_created_by
274 ,l_last_updated_by
275 ,l_created_by_name
276 ,l_last_updated_by_name
277 ,l_interface_source_code
278 ,l_interface_source_line_id;
279 EXIT WHEN cur_uda_interface_recs%NOTFOUND;
280
281 d_progress := 110;
282 PO_UDA_DEFAULTING_PKG.DEBUG('Details read from the interface table are as follows :', d_progress);
283 PO_UDA_DEFAULTING_PKG.DEBUG('l_interface_reference_id : ' || l_interface_reference_id, d_progress);
284 PO_UDA_DEFAULTING_PKG.DEBUG('l_transaction_id : ' || l_transaction_id, d_progress);
285 PO_UDA_DEFAULTING_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
286 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_group_id : ' || l_attr_group_id, d_progress);
287 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_group_app_id : ' || l_attr_group_app_id, d_progress);
288 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_group_name : ' || l_attr_group_name, d_progress);
289 PO_UDA_DEFAULTING_PKG.DEBUG('l_data_level_1 : ' || l_data_level_1, d_progress);
290 PO_UDA_DEFAULTING_PKG.DEBUG('l_data_level_2 : ' || l_data_level_2, d_progress);
291 PO_UDA_DEFAULTING_PKG.DEBUG('l_data_level_3 : ' || l_data_level_3, d_progress);
292 PO_UDA_DEFAULTING_PKG.DEBUG('l_transaction_type : ' || l_transaction_type, d_progress);
293 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_name : ' || l_attr_name, d_progress);
294 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_value_str : ' || l_attr_value_str, d_progress);
295 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_value_num : ' || l_attr_value_num, d_progress);
296 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_value_date : ' || l_attr_value_date, d_progress);
297 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_disp_value : ' || l_attr_disp_value, d_progress);
298 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_unit_of_measure : ' || l_attr_unit_of_measure, d_progress);
299 PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
300 PO_UDA_DEFAULTING_PKG.DEBUG('l_uda_template_id : ' || l_uda_template_id, d_progress);
301 PO_UDA_DEFAULTING_PKG.DEBUG('l_process_status : ' || l_process_status, d_progress);
302 PO_UDA_DEFAULTING_PKG.DEBUG('l_process_phase : ' || l_process_phase, d_progress);
303 PO_UDA_DEFAULTING_PKG.DEBUG('l_request_id : ' || l_request_id, d_progress);
304 PO_UDA_DEFAULTING_PKG.DEBUG('l_program_id : ' || l_program_id, d_progress);
305 PO_UDA_DEFAULTING_PKG.DEBUG('l_program_application_id : ' || l_program_application_id, d_progress);
306 PO_UDA_DEFAULTING_PKG.DEBUG('l_program_update_date : ' || l_program_update_date, d_progress);
307 PO_UDA_DEFAULTING_PKG.DEBUG('l_last_update_date : ' || l_last_update_date, d_progress);
308 PO_UDA_DEFAULTING_PKG.DEBUG('l_creation_date : ' || l_creation_date, d_progress);
309 PO_UDA_DEFAULTING_PKG.DEBUG('l_last_updated_by : ' || l_last_updated_by, d_progress);
310 PO_UDA_DEFAULTING_PKG.DEBUG('l_created_by_name : ' || l_created_by_name, d_progress);
311 PO_UDA_DEFAULTING_PKG.DEBUG('l_last_updated_by_name : ' || l_last_updated_by_name, d_progress);
312 PO_UDA_DEFAULTING_PKG.DEBUG('l_interface_source_code : ' || l_interface_source_code, d_progress);
313 PO_UDA_DEFAULTING_PKG.DEBUG('l_interface_source_line_id : ' || l_interface_source_line_id, d_progress);
314 d_progress := 120;
315
316 PO_UDA_DEFAULTING_PKG.DEBUG('Before constructing Attribute row Array', d_progress);
317 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_row_identifier ' || l_row_identifier, d_progress);
318 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_attr_group_id ' || l_attr_group_id, d_progress);
319 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_attr_group_app_id ' || l_attr_group_app_id, d_progress);
320 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: p_attr_grp_type ' || p_attr_grp_type, d_progress);
321 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_attr_group_name ' || l_attr_group_name, d_progress);
322 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_data_level_1 ' || l_data_level_1, d_progress);
323 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_data_level_2 ' || l_data_level_2, d_progress);
324 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_data_level_3 ' || l_data_level_3, d_progress);
325 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute row Array: l_transaction_type ' || l_transaction_type, d_progress);
326 d_progress := 130;
327 x_attributes_row_table.EXTEND(1);
328 x_attributes_row_table(l_count + 1) := ego_user_attr_row_obj
329 (
330 l_row_identifier
331 ,l_attr_group_id
332 ,l_attr_group_app_id
333 ,p_attr_grp_type
334 ,l_attr_group_name
335 ,l_data_level_1
336 ,l_data_level_2
337 ,l_data_level_3
338 ,NULL
339 ,NULL
340 ,NULL
341 ,l_transaction_type
342 );
343 d_progress := 140;
344 PO_UDA_DEFAULTING_PKG.DEBUG('After constructing Attribute row Array', d_progress);
345 PO_UDA_DEFAULTING_PKG.DEBUG('Before constructing Attribute Name Values Array', d_progress);
346 PO_UDA_DEFAULTING_PKG.DEBUG('l_row_identifier ' || l_row_identifier, d_progress);
347 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_name ' || l_attr_name, d_progress);
348 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_value_str ' || l_attr_value_str, d_progress);
349 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_value_num ' || l_attr_value_num, d_progress);
350 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_value_date ' || l_attr_value_date, d_progress);
351 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_disp_value ' || l_attr_disp_value, d_progress);
352 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_unit_of_measure ' || l_attr_unit_of_measure, d_progress);
353 PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier ' || l_user_row_identifier, d_progress);
354 d_progress := 150;
355 x_attr_name_value_pairs.EXTEND(1);
356 x_attr_name_value_pairs(l_count + 1) := ego_user_attr_data_obj
357 (
358 l_row_identifier
359 ,l_attr_name
360 ,l_attr_value_str
361 ,l_attr_value_num
362 ,l_attr_value_date
363 ,l_attr_disp_value
364 ,l_attr_unit_of_measure
365 ,l_user_row_identifier
366 );
367 d_progress := 160;
368 PO_UDA_DEFAULTING_PKG.DEBUG('After constructing Attribute Name Values Array', d_progress);
369 l_count := l_count + 1;
370 d_progress := 170;
371 END LOOP;
372 d_progress := 180;
373 CLOSE cur_uda_interface_recs;
374 d_progress := 190;
375
376 PO_UDA_DEFAULTING_PKG.DEBUG('Before constructing Class codes Array', d_progress);
377 x_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
378 (
379 EGO_COL_NAME_VALUE_PAIR_OBJ
380 (
381 'UDA_TEMPLATE_ID'
382 ,l_uda_template_id || ''
383 )
384 );
385 d_progress := 200;
386 PO_UDA_DEFAULTING_PKG.DEBUG('After constructing Class codes Array with template id : ' || l_uda_template_id, d_progress);
387 x_return_status := FND_API.G_RET_STS_SUCCESS;
388 EXCEPTION
389 WHEN OTHERS THEN
390 l_err_txt := 'Exception in POPULATE_STRUCTURES with ERROR : ' || SQLERRM;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 PO_UDA_IMPORT_PKG.HANDLE_ERROR
393 (
394 p_msg => l_err_txt
395 ,p_msg_code => 'UNEXPECTED_POPULATE_STRUCTURES'
396 ,p_attr_grp_id => l_attr_group_id
397 );
398 END POPULATE_STRUCTURES;
399
400
401 --------------------------------------------------------------------------------------------------------------------
402 --Start of Comments
403 --Name: POPULATE_REMAINING_ATTRIBUTES
404 --Procedure:
405 -- This procedure populates the structures EGO_USER_ATTR_DATA_TABLE, EGO_USER_ATTR_ROW_TABLE
406 -- from the table FND_DESCR_FLEX_COLUMN_USAGES. It would add elements for all the attributes present in
407 -- the attribute groups present in the variable x_attributes_row_table of type EGO_USER_ATTR_ROW_TABLE
408 -- and not present in variable x_attr_name_value_pairs of type EGO_USER_ATTR_DATA_TABLE already
409 -- with NULL values
410 --Parameters:
411 --IN/OUT:
412 -- 1. x_attr_name_value_pairs
413 -- This variable would contain a table of type EGO_USER_ATTR_DATA_TABLE with the attribute and its value pairs
414 -- 2. x_attributes_row_table
415 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
416 --OUT:
417 -- 1. x_return_status - Generic Out parameter having return status
418 -- 2. x_errorcode - Generic Out parameter having error code
419 -- 3. x_msg_count - Generic Out parameter having return msg Count
420 -- 4. x_msg_data - Generic Out parameter having return msg
421 --
422 --End of Comments
423 --------------------------------------------------------------------------------------------------------------------
424 PROCEDURE POPULATE_REMAINING_ATTRIBUTES
425 (
426 x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
427 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
428 ,x_return_status OUT NOCOPY VARCHAR2
429 ,x_errorcode OUT NOCOPY NUMBER
430 ,x_msg_count OUT NOCOPY NUMBER
431 ,x_msg_data OUT NOCOPY VARCHAR2
432 )
433 IS
434 d_progress NUMBER := 0;
435 l_attr_row_table_index NUMBER;
436 l_attr_row_obj EGO_USER_ATTR_ROW_OBJ;
437 l_attr_name_val EGO_USER_ATTR_DATA_OBJ;
438 l_row_identifier NUMBER;
439 l_attr_group_type VARCHAR2(200);
440 l_attr_group_name VARCHAR2(200);
441 l_attr_name VARCHAR2(200);
442 l_attr_name_tab VARCHAR2(200);
443 l_user_row_identifier NUMBER;
444 l_attr_name_value_index NUMBER;
445 l_attr_row_identifier NUMBER;
446 l_found BOOLEAN := FALSE;
447
448 CURSOR cur_uda_attributes
449 (
450 p_attr_grp_type VARCHAR2
451 ,p_attr_grp_name VARCHAR2
452 )IS
453 SELECT END_USER_COLUMN_NAME
454 FROM FND_DESCR_FLEX_COLUMN_USAGES
455 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
456 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
457
458 l_err_txt VARCHAR2(2000);
459
460 BEGIN
461 d_progress := 10;
462 PO_UDA_DEFAULTING_PKG.DEBUG('Starting POPULATE_REMAINING_ATTRIBUTES', d_progress);
463 PO_UDA_DEFAULTING_PKG.DEBUG('x_attr_name_value_pairs is as follows :', d_progress);
464 PO_UDA_DEFAULTING_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
465 PO_UDA_DEFAULTING_PKG.DEBUG('x_attributes_row_table is as follows :', d_progress);
466 PO_UDA_DEFAULTING_PKG.DEBUG(x_attributes_row_table, d_progress);
467 d_progress := 20;
468
469 PO_UDA_DEFAULTING_PKG.DEBUG('Looping for all the Attribute Groups', d_progress);
470 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0) THEN
471 d_progress := 30;
472 l_attr_row_table_index := x_attributes_row_table.FIRST;
473 d_progress := 40;
474 WHILE (l_attr_row_table_index IS NOT NULL AND l_attr_row_table_index <= x_attributes_row_table.LAST)
475 LOOP
476 d_progress := 50;
477 IF x_attributes_row_table(l_attr_row_table_index) IS NOT NULL THEN
478 d_progress := 60;
479 l_attr_row_obj := x_attributes_row_table(l_attr_row_table_index);
480 d_progress := 70;
481 l_row_identifier := l_attr_row_obj.ROW_IDENTIFIER;
482 l_attr_group_type := l_attr_row_obj.ATTR_GROUP_TYPE;
483 l_attr_group_name := l_attr_row_obj.ATTR_GROUP_NAME;
484
485 d_progress := 80;
486 PO_UDA_DEFAULTING_PKG.DEBUG('AG ROW_IDENTIFIER : ' || l_row_identifier, d_progress);
487 PO_UDA_DEFAULTING_PKG.DEBUG('AG ATTR_GROUP_TYPE : ' || l_attr_group_type, d_progress);
488 PO_UDA_DEFAULTING_PKG.DEBUG('AG ATTR_GROUP_NAME : ' || l_attr_group_name, d_progress);
489 d_progress := 90;
490 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
491 LOOP
492 d_progress := 100;
493 FETCH cur_uda_attributes
494 INTO l_attr_name ;
495 EXIT WHEN cur_uda_attributes%NOTFOUND;
496
497 d_progress := 110;
498 BEGIN
499 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
500 INTO l_user_row_identifier
501 FROM DUAL;
502 d_progress := 110;
503 PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
504 EXCEPTION
505 WHEN OTHERS THEN
506 d_progress := 120;
507 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_user_row_identifier', d_progress);
508 RAISE;
509 END;
510 d_progress := 130;
511 l_attr_name_value_index := x_attr_name_value_pairs.FIRST;
512 PO_UDA_DEFAULTING_PKG.DEBUG('Looping for all the Attributes for the attribute Group :' || l_attr_group_name, d_progress);
513 d_progress := 140;
514 IF x_attr_name_value_pairs(l_attr_name_value_index) IS NOT NULL THEN
515 d_progress := 150;
516 WHILE (l_attr_name_value_index IS NOT NULL AND l_attr_name_value_index <= x_attr_name_value_pairs.LAST)
517 LOOP
518 d_progress := 150;
519 l_attr_name_val := x_attr_name_value_pairs(l_attr_name_value_index);
520 l_attr_row_identifier := l_attr_name_val.ROW_IDENTIFIER;
521 l_attr_name_tab := l_attr_name_val.ATTR_NAME;
522
523 d_progress := 160;
524 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute ROW_IDENTIFIER : ' || l_attr_row_identifier, d_progress);
525 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute ATTR_NAME : ' || l_attr_name_tab, d_progress);
526 d_progress := 170;
527 IF l_row_identifier = l_attr_row_identifier AND l_attr_name = l_attr_name_tab THEN
528 l_found := TRUE;
529 d_progress := 180;
530 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute ATTR_NAME : ' || l_attr_name_tab || ' is found ', d_progress);
531 EXIT;
532 END IF;
533 d_progress := 190;
534 l_attr_name_value_index := x_attr_name_value_pairs.NEXT(l_attr_name_value_index);
535 END LOOP;
536 d_progress := 200;
537 IF l_found = FALSE THEN
538 d_progress := 210;
539 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute not found, creating the attribute', d_progress);
540 x_attr_name_value_pairs.EXTEND(1);
541 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
542 (
543 l_row_identifier
544 ,l_attr_name
545 ,NULL
546 ,NULL
547 ,NULL
548 ,NULL
549 ,NULL
550 ,l_user_row_identifier
551 );
552 d_progress := 220;
553 ELSE
554 d_progress := 230;
555 l_found := FALSE;
556 END IF;
557 d_progress := 240;
558 ELSE
559 d_progress := 250;
560 PO_UDA_DEFAULTING_PKG.DEBUG('Attribute not found, creating the attribute', d_progress);
561 x_attr_name_value_pairs.EXTEND(1);
562 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
563 (
564 l_row_identifier
565 ,l_attr_name
566 ,NULL
567 ,NULL
568 ,NULL
569 ,NULL
570 ,NULL
571 ,l_user_row_identifier
572 );
573 d_progress := 260;
574 END IF;
575 END LOOP;
576 d_progress := 270;
577 CLOSE cur_uda_attributes;
578 d_progress := 280;
579 END IF;
580 l_attr_row_table_index := x_attributes_row_table.NEXT(l_attr_row_table_index);
581 d_progress := 290;
582 END LOOP;
583 END IF;
584 d_progress := 300;
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586 EXCEPTION
587 WHEN OTHERS THEN
588 l_err_txt := 'Exception in POPULATE_REMAINING_ATTRIBUTES with ERROR : ' || SQLERRM;
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 PO_UDA_IMPORT_PKG.HANDLE_ERROR
591 (
592 p_msg => l_err_txt
593 ,p_msg_code => 'UNEXPECTED_POPULATE_REMAINING_ATTRIBUTES'
594 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
595 );
596 END POPULATE_REMAINING_ATTRIBUTES;
597
598 --------------------------------------------------------------------------------------------------------------------
599 --Start of Comments
600 --Name: POPULATE_EMPTY_AGS
601 --Procedure:
602 -- This procedure populates the structures EGO_USER_ATTR_DATA_TABLE, EGO_USER_ATTR_ROW_TABLE
603 -- with the details of the attribute groups in x_attributes_row_table and attributes in
604 -- x_attr_name_value_pairs which are not present in x_attributes_row_table and present in
605 -- x_po_uda_usage_object_array.
606 --Parameters:
607 --IN:
608 -- 1. p_pk_column_name_value_pairs
609 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
610 -- 2. p_external_attr_value_pairs
611 -- This variable wsould contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
612 --IN/OUT:
613 -- 1. x_po_uda_usage_object_array
614 -- This variable of type PO_UDA_USAGE_OBJECT_ARRAY contains data of hhe usages.
615 -- 2. x_attr_name_value_pairs
616 -- This variable would contain a table of type EGO_USER_ATTR_DATA_TABLE with the attribute and its value pairs
617 -- 3. x_attributes_row_table
618 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
619 --OUT:
620 -- 1. x_return_status - Generic Out parameter having return status
621 -- 2. x_errorcode - Generic Out parameter having error code
622 -- 3. x_msg_count - Generic Out parameter having return msg Count
623 -- 4. x_msg_data - Generic Out parameter having return msg
624 --
625 --End of Comments
626 --------------------------------------------------------------------------------------------------------------------
627 PROCEDURE POPULATE_EMPTY_AGS
628 (
629 p_template_id IN NUMBER
630 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
631 ,p_external_attr_value_pairs IN EGO_COL_NAME_VALUE_PAIR_TABLE
632 ,p_address_lookup_type IN VARCHAR2 DEFAULT NULL
633 ,x_po_uda_usage_object_array IN OUT NOCOPY PO_UDA_USAGE_OBJECT_ARRAY
634 ,x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
635 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
636 ,x_return_status OUT NOCOPY VARCHAR2
637 ,x_errorcode OUT NOCOPY NUMBER
638 ,x_msg_count OUT NOCOPY NUMBER
639 ,x_msg_data OUT NOCOPY VARCHAR2
640 )
641 IS
642 d_progress NUMBER := 0;
643 l_po_uda_usage_object PO_UDA_USAGE_OBJECT;
644 l_attr_group_table PO_TBL_NUMBER;
645 l_context_object_index NUMBER;
646 l_attr_group_id_index NUMBER;
647 l_pk_col_index NUMBER;
648 l_attr_group_id NUMBER;
649 l_base_table VARCHAR2(200);
650 l_attr_group_type VARCHAR2(200);
651 l_attr_group_name VARCHAR2(200);
652 l_data_level VARCHAR2(200);
653 l_sql_query VARCHAR2(4000);
654 l_check NUMBER;
655 v_query_hdl NUMBER;
656 l_pk_name_value_pair EGO_COL_NAME_VALUE_PAIR_OBJ;
657 l_attr_row_obj EGO_USER_ATTR_ROW_OBJ;
658 l_rows_processed NUMBER;
659 l_attr_grp_loc_index NUMBER;
660 l_row_identifier NUMBER;
661 l_user_row_identifier NUMBER;
662 l_attr_name VARCHAR2(200);
663 l_present NUMBER := 0;
664 l_mod_add_query VARCHAR2(2000);
665 l_use_mod_addr_type VARCHAR2(1) := 'N';
666
667 CURSOR cur_uda_attributes
668 (
669 p_attr_grp_type VARCHAR2
670 ,p_attr_grp_name VARCHAR2
671 )IS
672 SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
673 FROM FND_DESCR_FLEX_COLUMN_USAGES
674 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
675 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
676
677 CURSOR cur_address
678 (
679 p_address_lookup_type VARCHAR2
680 ,p_use_mod_addr_type VARCHAR2
681 )IS
682 SELECT DISTINCT LOOKUP_CODE , TAG
683 FROM FND_LOOKUP_VALUES
684 WHERE LOOKUP_TYPE = p_address_lookup_type
685 AND (p_use_mod_addr_type = 'N'
686 OR (p_use_mod_addr_type = 'Y' AND
687 LOOKUP_CODE IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')))
688 ORDER BY TAG;
689
690 l_attr_col VARCHAR2(200);
691 l_attr_val VARCHAR2(200);
692
693 l_xd_present NUMBER;
694
695 l_err_txt VARCHAR2(2000);
696
697
698 BEGIN
699 d_progress := 10;
700 PO_UDA_DEFAULTING_PKG.DEBUG('Starting POPULATE_EMPTY_AGS', d_progress);
701 PO_UDA_DEFAULTING_PKG.DEBUG('p_template_id : ' || p_template_id, d_progress);
702 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk_column_name_value_pairs is as follows', d_progress);
703 PO_UDA_DEFAULTING_PKG.DEBUG(p_pk_column_name_value_pairs, d_progress);
704 PO_UDA_DEFAULTING_PKG.DEBUG('p_external_attr_value_pairs is as follows', d_progress);
705 PO_UDA_DEFAULTING_PKG.DEBUG(p_external_attr_value_pairs, d_progress);
706 PO_UDA_DEFAULTING_PKG.DEBUG('p_address_lookup_type : ' || p_address_lookup_type, d_progress);
707 PO_UDA_DEFAULTING_PKG.DEBUG('x_po_uda_usage_object_array is as follows', d_progress);
708 PO_UDA_DEFAULTING_PKG.DEBUG(x_po_uda_usage_object_array, d_progress);
709 PO_UDA_DEFAULTING_PKG.DEBUG('x_attr_name_value_pairs is as follows', d_progress);
710 PO_UDA_DEFAULTING_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
711 PO_UDA_DEFAULTING_PKG.DEBUG('x_attributes_row_table is as follows', d_progress);
712 PO_UDA_DEFAULTING_PKG.DEBUG(x_attributes_row_table, d_progress);
713 d_progress := 20;
714
715 l_attr_group_table := PO_TBL_NUMBER();
716 d_progress := 30;
717 IF (x_po_uda_usage_object_array IS NOT NULL AND x_po_uda_usage_object_array.COUNT > 0) THEN
718 d_progress := 40;
719 PO_UDA_DEFAULTING_PKG.DEBUG('x_po_uda_usage_object_array.COUNT :' || x_po_uda_usage_object_array.COUNT, d_progress);
720 l_context_object_index := x_po_uda_usage_object_array.FIRST;
721 d_progress := 50;
722
723 WHILE (l_context_object_index IS NOT NULL AND l_context_object_index <= x_po_uda_usage_object_array.LAST)
724 LOOP
725 d_progress := 60;
726 IF x_po_uda_usage_object_array(l_context_object_index) IS NOT NULL THEN
727 d_progress := 70;
728 l_po_uda_usage_object := x_po_uda_usage_object_array(l_context_object_index);
729 l_attr_group_table := l_po_uda_usage_object.Attr_group_Id;
730 d_progress := 80;
731
732 IF (l_attr_group_table IS NOT NULL AND l_attr_group_table.COUNT > 0) THEN
733 d_progress := 100;
734 l_attr_group_id_index := l_attr_group_table.FIRST;
735 d_progress := 110;
736 WHILE (l_attr_group_id_index IS NOT NULL AND l_attr_group_id_index <= l_attr_group_table.LAST)
737 LOOP
738 d_progress := 120;
739
740 IF l_attr_group_table(l_attr_group_id_index) IS NOT NULL THEN
741 l_attr_group_id := l_attr_group_table(l_attr_group_id_index);
742 PO_UDA_DEFAULTING_PKG.DEBUG(' l_attr_group_id : ' || l_attr_group_id, d_progress);
743 d_progress := 130;
744 BEGIN
745 d_progress := 140;
746 SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
747 INTO l_attr_group_type, l_attr_group_name
748 FROM EGO_FND_DSC_FLX_CTX_EXT
749 WHERE ATTR_GROUP_ID = l_attr_group_id;
750
751 d_progress := 150;
752 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_group_type : ' || l_attr_group_type, d_progress);
753 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_group_name : ' || l_attr_group_name, d_progress);
754 EXCEPTION
755 WHEN OTHERS THEN
756 d_progress := 150;
757 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_attr_group_type, l_attr_group_name', d_progress);
758 RAISE;
759 END;
760
761 d_progress := 160;
762 l_base_table := PO_UDA_DATA_UTIL.g_object_dff_tl(l_attr_group_type).l_object_ext_b_table;
763
764 l_sql_query := 'SELECT 1 FROM ' || l_base_table;
765 l_sql_query := l_sql_query || ' WHERE ATTR_GROUP_ID = ' || l_attr_group_id;
766 d_progress := 170;
767
768 IF (p_pk_column_name_value_pairs IS NOT NULL AND p_pk_column_name_value_pairs.COUNT > 0) THEN
769 l_pk_col_index := p_pk_column_name_value_pairs.FIRST;
770 d_progress := 180;
771 WHILE (l_pk_col_index IS NOT NULL AND l_pk_col_index <= p_pk_column_name_value_pairs.LAST)
772 LOOP
773 d_progress := 190;
774 IF p_pk_column_name_value_pairs(l_pk_col_index) IS NOT NULL THEN
775 d_progress := 200;
776 l_pk_name_value_pair := p_pk_column_name_value_pairs(l_pk_col_index);
777 l_sql_query := l_sql_query || ' AND ' || l_pk_name_value_pair.NAME || ' = ' || l_pk_name_value_pair.VALUE; -- PK CHECK
778 d_progress := 210;
779 END IF;
780 d_progress := 220;
781 l_pk_col_index := p_pk_column_name_value_pairs.NEXT(l_pk_col_index);
782 d_progress := 230;
783 END LOOP;
784
785 --bug11872637 Start : Add Condition for Mod Specific Lookup Codes of Type 'PO_MOD_UDA_ADDRESS_TYPES'
786 IF l_po_uda_usage_object.USAGE_TYPE = 'ADDRESS' AND p_address_lookup_type = 'PO_MOD_UDA_ADDRESS_TYPES' THEN
787 l_use_mod_addr_type := 'Y';
788 l_mod_add_query := ' AND C_EXT_ATTR40 = '|| '''PO_MOD_UDA_ADDRESS_TYPES''' ||
789 ' AND C_EXT_ATTR39 IN (' || '''MOD_ADMIN_OFFICE''' ||',' || '''MOD_ISSUING_OFFICE'''
790 || ')';
791 l_sql_query := l_sql_query || l_mod_add_query;
792 END IF;
793 --bug11872637 End
794 END IF;
795 d_progress := 240;
796
797 -- Not using binds as it would be complicated to put all the checks
798 d_progress := 250;
799 PO_UDA_DEFAULTING_PKG.DEBUG('l_sql_query : ' || l_sql_query, d_progress);
800 PO_UDA_DEFAULTING_PKG.DEBUG('Before executing', d_progress);
801 v_query_hdl := DBMS_SQL.open_cursor;
802 DBMS_SQL.parse(v_query_hdl, l_sql_query, DBMS_SQL.native);
803 DBMS_SQL.define_column(v_query_hdl, 1, l_check);
804 l_rows_processed := DBMS_SQL.EXECUTE(v_query_hdl);
805 PO_UDA_DEFAULTING_PKG.DEBUG('After executing l_rows_processed :' || l_rows_processed, d_progress);
806
807 d_progress := 260;
808 IF DBMS_SQL.fetch_rows(v_query_hdl) <= 0 THEN
809 l_attr_grp_loc_index := x_attributes_row_table.FIRST;
810 l_present := 0;
811 WHILE (l_attr_grp_loc_index IS NOT NULL AND l_attr_grp_loc_index <= x_attributes_row_table.LAST)
812 LOOP
813 d_progress := 270;
814 IF x_attributes_row_table(l_attr_grp_loc_index) IS NOT NULL THEN
815 l_attr_row_obj := x_attributes_row_table(l_attr_grp_loc_index);
816 d_progress := 280;
817
818 IF l_attr_row_obj.ATTR_GROUP_ID = l_attr_group_id THEN
819 l_present := 1;
820 d_progress := 290;
821 EXIT;
822 END IF;
823 d_progress := 300;
824 END IF;
825 l_attr_grp_loc_index := x_attributes_row_table.NEXT(l_attr_grp_loc_index);
826 END LOOP;
827 d_progress := 310;
828
829 IF l_present = 0 THEN
830 d_progress := 320;
831 PO_UDA_DEFAULTING_PKG.DEBUG('AG is found :' || l_attr_group_id, d_progress);
832 BEGIN
833 d_progress := 330;
834 SELECT DATA_LEVEL_NAME
835 INTO l_data_level
836 FROM EGO_DATA_LEVEL_B
837 WHERE APPLICATION_ID = 201
838 AND ATTR_GROUP_TYPE = l_attr_group_type
839 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
840 AND ATTR_GROUP_TYPE NOT LIKE '%DUMMY%';
841
842 d_progress := 340;
843 PO_UDA_DEFAULTING_PKG.DEBUG('l_data_level : ' || l_data_level, d_progress);
844 EXCEPTION
845 WHEN OTHERS THEN
846 d_progress := 350;
847 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_data_level', d_progress);
848 RAISE;
849 END;
850
851 d_progress := 360;
852 SELECT Count(*)
853 INTO l_xd_present
854 FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
855 WHERE classification_code = p_template_id
856 AND attr_group_id = l_attr_group_id
857 AND execution_method = 'XD'
858 AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id;
859
860 d_progress := 370;
861 IF l_xd_present <= 0 THEN
862 -- XD Function is not present and the ag need not be added
863 d_progress := 380;
864 PO_UDA_DEFAULTING_PKG.DEBUG('XD function is not found so skipping the AG', d_progress);
865 NULL;
866 ELSIF l_po_uda_usage_object.USAGE_TYPE = 'ADDRESS' THEN -- FOR ADDRESS AGS
867 d_progress := 390;
868 PO_UDA_DEFAULTING_PKG.DEBUG('Usage type is ADDRESS', d_progress);
869 FOR cur_address_rec IN cur_address(p_address_lookup_type, l_use_mod_addr_type) LOOP
870 BEGIN
871 d_progress := 400;
872 SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
873 INTO l_row_identifier
874 FROM DUAL;
875
876 d_progress := 410;
877 PO_UDA_DEFAULTING_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
878 EXCEPTION
879 WHEN OTHERS THEN
880 d_progress := 420;
881 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_row_identifier', d_progress);
882 RAISE;
883 END;
884
885 d_progress := 430;
886 PO_UDA_DEFAULTING_PKG.DEBUG('Before adding AG to attribute row table', d_progress);
887 x_attributes_row_table.EXTEND(1);
888 x_attributes_row_table(x_attributes_row_table.COUNT) := ego_user_attr_row_obj
889 (
890 l_row_identifier
891 ,l_attr_group_id
892 ,201
893 ,l_attr_group_type
894 ,l_attr_group_name
895 ,l_data_level
896 ,NULL
897 ,NULL
898 ,NULL
899 ,NULL
900 ,NULL
901 ,'SYNC'
902 );
903 d_progress := 440;
904 PO_UDA_DEFAULTING_PKG.DEBUG('After adding AG to attribute row table', d_progress);
905
906 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
907 LOOP
908 d_progress := 450;
909 FETCH cur_uda_attributes
910 INTO l_attr_name, l_attr_col;
911 EXIT WHEN cur_uda_attributes%NOTFOUND;
912
913 BEGIN
914 d_progress := 460;
915 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
916 INTO l_user_row_identifier
917 FROM DUAL;
918
919 d_progress := 470;
920 PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
921 EXCEPTION
922 WHEN OTHERS THEN
923 d_progress := 480;
924 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_user_row_identifier', d_progress);
925 RAISE;
926 END;
927 d_progress := 490;
928 PO_UDA_DEFAULTING_PKG.DEBUG('Before adding attribute', d_progress);
929 x_attr_name_value_pairs.EXTEND(1);
930 d_progress := 500;
931 IF l_attr_col = 'C_EXT_ATTR39' THEN
932 d_progress := 510;
933 l_attr_val := cur_address_rec.LOOKUP_CODE;
934 ELSIF l_attr_col = 'C_EXT_ATTR40' THEN
935 d_progress := 520;
936 l_attr_val := p_address_lookup_type;
937 ELSE
938 d_progress := 530;
939 l_attr_val := NULL;
940 END IF;
941 d_progress := 540;
942 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
943 (
944 l_row_identifier
945 ,l_attr_name
946 ,l_attr_val
947 ,NULL
948 ,NULL
949 ,NULL
950 ,NULL
951 ,l_user_row_identifier
952 );
953 d_progress := 550;
954 PO_UDA_DEFAULTING_PKG.DEBUG('After adding attribute', d_progress);
955
956 END LOOP;
957 CLOSE cur_uda_attributes;
958 d_progress := 560;
959 END LOOP;
960 ELSE -- FOR NON ADDRESS AGS
961 d_progress := 570;
962 BEGIN
963 d_progress := 580;
964 SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
965 INTO l_row_identifier
966 FROM DUAL;
967 d_progress := 590;
968 PO_UDA_DEFAULTING_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
969 EXCEPTION
970 WHEN OTHERS THEN
971 d_progress := 600;
972 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_row_identifier', d_progress);
973 RAISE;
974 END;
975
976 d_progress := 610;
977 PO_UDA_DEFAULTING_PKG.DEBUG('Before adding AG to attribute row table', d_progress);
978 x_attributes_row_table.EXTEND(1);
979 x_attributes_row_table(x_attributes_row_table.COUNT) := ego_user_attr_row_obj
980 (
981 l_row_identifier
982 ,l_attr_group_id
983 ,201
984 ,l_attr_group_type
985 ,l_attr_group_name
986 ,l_data_level
987 ,NULL
988 ,NULL
989 ,NULL
990 ,NULL
991 ,NULL
992 ,'SYNC'
993 );
994 d_progress := 620;
995 PO_UDA_DEFAULTING_PKG.DEBUG('After adding AG to attribute row table', d_progress);
996
997 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
998 LOOP
999 d_progress := 630;
1000 FETCH cur_uda_attributes
1001 INTO l_attr_name, l_attr_col;
1002 EXIT WHEN cur_uda_attributes%NOTFOUND;
1003
1004 d_progress := 640;
1005 BEGIN
1006 d_progress := 650;
1007 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
1008 INTO l_user_row_identifier
1009 FROM DUAL;
1010 d_progress := 660;
1011 PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 d_progress := 670;
1015 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to get the l_user_row_identifier', d_progress);
1016 RAISE;
1017 END;
1018 d_progress := 680;
1019
1020 PO_UDA_DEFAULTING_PKG.DEBUG('Before adding AG to attribute row table', d_progress);
1021 x_attr_name_value_pairs.EXTEND(1);
1022 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
1023 (
1024 l_row_identifier
1025 ,l_attr_name
1026 ,NULL
1027 ,NULL
1028 ,NULL
1029 ,NULL
1030 ,NULL
1031 ,l_user_row_identifier
1032 );
1033 d_progress := 690;
1034 PO_UDA_DEFAULTING_PKG.DEBUG('After adding AG to attribute row table', d_progress);
1035 d_progress := 700;
1036
1037 END LOOP;
1038 CLOSE cur_uda_attributes;
1039 d_progress := 710;
1040 END IF; -- FOR NON ADDRESS AGS
1041 d_progress := 720;
1042 END IF;
1043 END IF;
1044
1045 IF DBMS_SQL.is_open(v_query_hdl) THEN
1046 DBMS_SQL.close_cursor(v_query_hdl);
1047 END IF ;
1048
1049 END IF;
1050 d_progress := 730;
1051 l_attr_group_id_index := l_attr_group_table.NEXT(l_attr_group_id_index);
1052 END LOOP;
1053 d_progress := 740;
1054 END IF;
1055
1056 END IF;
1057 d_progress := 750;
1058 l_context_object_index := x_po_uda_usage_object_array.NEXT(l_context_object_index);
1059 END LOOP;
1060 ELSE
1061 d_progress := 760;
1062 PO_UDA_DEFAULTING_PKG.DEBUG('USAGE object array does not have any objects', d_progress);
1063 END IF;
1064 d_progress := 770;
1065 x_return_status := FND_API.G_RET_STS_SUCCESS;
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 l_err_txt := 'Exception in POPULATE_EMPTY_AGS with ERROR : ' || SQLERRM;
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070
1071 IF DBMS_SQL.is_open(v_query_hdl) THEN
1072 DBMS_SQL.close_cursor(v_query_hdl);
1073 END IF ;
1074
1075 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1076 (
1077 p_msg => l_err_txt
1078 ,p_msg_code => 'UNEXPECTED_POPULATE_EMPTY_AGS'
1079 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
1080 );
1081 END POPULATE_EMPTY_AGS;
1082
1083 --
1084 -- Private
1085 -- To Check whether all the attributes in the AG are null or not
1086 --
1087 FUNCTION All_Attr_Values_Are_Null (
1088 p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
1089 )
1090 RETURN BOOLEAN
1091 IS
1092
1093 l_attr_count NUMBER;
1094 l_all_are_null BOOLEAN := TRUE;
1095
1096 BEGIN
1097 PO_UDA_DEFAULTING_PKG.DEBUG('Starting All_Attr_Values_Are_Null ', 0);
1098 PO_UDA_DEFAULTING_PKG.DEBUG('p_attr_name_value_pairs is as follows ', 0);
1099 PO_UDA_DEFAULTING_PKG.DEBUG(p_attr_name_value_pairs, 0);
1100
1101 IF (p_attr_name_value_pairs IS NOT NULL AND p_attr_name_value_pairs.COUNT > 0) THEN
1102 l_attr_count := p_attr_name_value_pairs.FIRST;
1103 WHILE (l_attr_count <= p_attr_name_value_pairs.LAST)
1104 LOOP
1105 EXIT WHEN (NOT l_all_are_null);
1106 IF (
1107 p_attr_name_value_pairs(l_attr_count).ATTR_VALUE_STR IS NOT NULL OR
1108 p_attr_name_value_pairs(l_attr_count).ATTR_VALUE_NUM IS NOT NULL OR
1109 p_attr_name_value_pairs(l_attr_count).ATTR_VALUE_DATE IS NOT NULL OR
1110 p_attr_name_value_pairs(l_attr_count).ATTR_DISP_VALUE IS NOT NULL
1111 ) THEN
1112 l_all_are_null := FALSE;
1113 END IF;
1114
1115 l_attr_count := p_attr_name_value_pairs.NEXT(l_attr_count);
1116 END LOOP;
1117 END IF;
1118 RETURN l_all_are_null;
1119 END All_Attr_Values_Are_Null;
1120
1121 --------------------------------------------------------------------------------------------------------------------
1122 --Start of Comments
1123 --Name: EXECUTE_IMPORT_UDA_FUNCTION
1124 --Procedure:
1125 -- This procedure would execute all the actions appropriate using the procedure
1126 -- EGO_EXT_FWK_PUB.EXECUTE_FUNCTION.
1127 -- Appropriate actions are teh actions from the view EGO_ACTION_DISPLAYS_B with
1128 -- Calssification code as the teplate id attriubute group id would be all the
1129 -- attribute groups from the variable x_attributes_row_table and execution_code
1130 -- as that of p_event
1131 -- The actions would be executed in the order of the sequence number of the
1132 -- appropriate actions.
1133 --Parameters:
1134 --IN:
1135 -- 1. p_template_id
1136 -- This variable would contain the template id for which the actions are to be executed
1137 -- 2. p_event
1138 -- This variable would contain the event type (execution code)
1139 --IN/OUT:
1140 -- 1. x_external_attr_value_pairs
1141 -- This variable wsould contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
1142 -- 2. x_pk_column_name_value_pairs
1143 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
1144 -- 3. x_attr_name_value_pairs
1145 -- This variable would contain an array of type EGO_USER_ATTR_DATA_TABLE with the template details
1146 -- 4. x_attributes_row_table
1147 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
1148 --OUT:
1149 -- 1. x_return_status - Generic Out parameter having return status
1150 -- 2. x_errorcode - Generic Out parameter having error code
1151 -- 3. x_msg_count - Generic Out parameter having return msg Count
1152 -- 4. x_msg_data - Generic Out parameter having return msg
1153 --
1154 --End of Comments
1155 --------------------------------------------------------------------------------------------------------------------
1156 PROCEDURE EXECUTE_IMPORT_UDA_FUNCTION
1157 (
1158 p_template_id IN NUMBER
1159 ,p_event IN VARCHAR2
1160 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
1161 ,x_pk_column_name_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
1162 ,x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
1163 ,x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
1164 ,x_return_status OUT NOCOPY VARCHAR2
1165 ,x_errorcode OUT NOCOPY NUMBER
1166 ,x_msg_count OUT NOCOPY NUMBER
1167 ,x_msg_data OUT NOCOPY VARCHAR2
1168 )
1169 IS
1170 d_progress NUMBER := 0;
1171 l_attr_group_id_index NUMBER;
1172 l_attr_row_obj EGO_USER_ATTR_ROW_OBJ;
1173 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1174 l_action_id NUMBER;
1175 l_data_level_name VARCHAR2(200);
1176 l_data_level_id NUMBER;
1177 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
1178 l_attr_id_index NUMBER;
1179 l_attr_id_index1 NUMBER;
1180 l_attr_obj EGO_USER_ATTR_DATA_OBJ;
1181 l_attr_obj1 EGO_USER_ATTR_DATA_OBJ;
1182 ext_attr_value_pairs_ind NUMBER;
1183 ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
1184
1185 g_uda_return_status VARCHAR2(2);
1186 uda_msg_data VARCHAR2(2000);
1187 uda_return_status VARCHAR2(2);
1188
1189
1190
1191 CURSOR c_get_action_id(p_template_id NUMBER, p_attribute_group_id NUMBER, p_event VARCHAR2) IS
1192 SELECT EGO_ACTION_DISPLAYS_B.action_id FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
1193 WHERE classification_code = p_template_id
1194 AND attr_group_id = p_attribute_group_id
1195 AND execution_method = p_event
1196 AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id
1197 ORDER BY SEQUENCE;
1198
1199
1200 l_pk_col_name_value_pairs_ind NUMBER;
1201 l_attr_name_value_pairs_ind NUMBER;
1202 l_attributes_row_table_ind NUMBER;
1203 l_ext_attr_value_pairs_ind NUMBER;
1204
1205 l_pk_column_name_value EGO_COL_NAME_VALUE_PAIR_OBJ;
1206 l_attr_name_value EGO_USER_ATTR_DATA_OBJ;
1207 l_attributes_row ego_user_attr_row_obj;
1208 l_external_attr_value EGO_COL_NAME_VALUE_PAIR_OBJ;
1209
1210 l_err_txt VARCHAR2(2000);
1211
1212 BEGIN
1213 d_progress := 10;
1214 PO_UDA_DEFAULTING_PKG.DEBUG('Starting EXECUTE_IMPORT_UDA_FUNCTION', d_progress);
1215 PO_UDA_DEFAULTING_PKG.DEBUG('p_template_id : ' || p_template_id, d_progress);
1216 PO_UDA_DEFAULTING_PKG.DEBUG('p_event : ' || p_event, d_progress);
1217 PO_UDA_DEFAULTING_PKG.DEBUG('x_external_attr_value_pairs is as follows', d_progress);
1218 PO_UDA_DEFAULTING_PKG.DEBUG(x_external_attr_value_pairs, d_progress);
1219 PO_UDA_DEFAULTING_PKG.DEBUG('x_pk_column_name_value_pairs is as follows', d_progress);
1220 PO_UDA_DEFAULTING_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
1221 PO_UDA_DEFAULTING_PKG.DEBUG('x_attr_name_value_pairs is as follows', d_progress);
1222 PO_UDA_DEFAULTING_PKG.DEBUG(x_attr_name_value_pairs, d_progress);
1223 PO_UDA_DEFAULTING_PKG.DEBUG('x_attributes_row_table is as follows', d_progress);
1224 PO_UDA_DEFAULTING_PKG.DEBUG(x_attributes_row_table, d_progress);
1225
1226 x_return_status := FND_API.G_RET_STS_SUCCESS;
1227 g_uda_return_status := FND_API.G_RET_STS_SUCCESS;
1228 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0) THEN
1229 d_progress := 20;
1230 l_attr_group_id_index := x_attributes_row_table.FIRST;
1231 d_progress := 30;
1232
1233 WHILE (l_attr_group_id_index IS NOT NULL AND l_attr_group_id_index <= x_attributes_row_table.LAST)
1234 LOOP
1235 d_progress := 40;
1236 IF x_attributes_row_table(l_attr_group_id_index) IS NOT NULL THEN
1237 d_progress := 50;
1238 l_attr_row_obj := x_attributes_row_table(l_attr_group_id_index);
1239 d_progress := 60;
1240
1241 BEGIN
1242 d_progress := 70;
1243 SELECT DATA_LEVEL_NAME, DATA_LEVEL_ID
1244 INTO l_data_level_name, l_data_level_id
1245 FROM EGO_DATA_LEVEL_B
1246 WHERE ATTR_GROUP_TYPE = l_attr_row_obj.ATTR_GROUP_TYPE
1247 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
1248
1249 d_progress := 80;
1250 PO_UDA_DEFAULTING_PKG.DEBUG('l_data_level_name : ' || l_data_level_name, d_progress);
1251 PO_UDA_DEFAULTING_PKG.DEBUG('l_data_level_id : ' || l_data_level_id, d_progress);
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 d_progress := 90;
1255 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch l_data_level_name, l_data_level_id', d_progress);
1256 END;
1257
1258 d_progress := 100;
1259 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
1260 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
1261 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE(l_attr_row_obj);
1262 PO_UDA_DEFAULTING_PKG.DEBUG('Initialized l_attr_name_value_pairs, l_attributes_row_table', d_progress);
1263
1264 d_progress := 110;
1265 PO_UDA_DEFAULTING_PKG.DEBUG('Putting all attributes with the ROW_IDENTIFIER (' || l_attr_obj.ROW_IDENTIFIER || ') into another object array', d_progress);
1266 IF (x_attr_name_value_pairs IS NOT NULL AND x_attr_name_value_pairs.COUNT > 0) THEN
1267 d_progress := 120;
1268 l_attr_id_index := x_attr_name_value_pairs.FIRST;
1269 d_progress := 130;
1270 WHILE (l_attr_id_index IS NOT NULL AND l_attr_id_index <= x_attr_name_value_pairs.LAST)
1271 LOOP
1272 d_progress := 140;
1273 IF x_attr_name_value_pairs(l_attr_id_index) IS NOT NULL THEN
1274 d_progress := 150;
1275 l_attr_obj := x_attr_name_value_pairs(l_attr_id_index);
1276 d_progress := 160;
1277 IF l_attr_obj.ROW_IDENTIFIER = l_attr_row_obj.ROW_IDENTIFIER THEN
1278 d_progress := 170;
1279 l_attr_name_value_pairs.EXTEND(1);
1280 l_attr_name_value_pairs(l_attr_name_value_pairs.COUNT) := l_attr_obj;
1281 d_progress := 180;
1282 END IF;
1283 END IF;
1284 d_progress := 190;
1285 l_attr_id_index := x_attr_name_value_pairs.NEXT(l_attr_id_index);
1286 END LOOP;
1287 END IF;
1288 d_progress := 200;
1289 PO_UDA_DEFAULTING_PKG.DEBUG('Object array is constructed', d_progress);
1290
1291 OPEN c_get_action_id(p_template_id, l_attr_row_obj.ATTR_GROUP_ID, p_event);
1292 LOOP
1293 d_progress := 210;
1294 FETCH c_get_action_id
1295 INTO l_action_id;
1296 EXIT WHEN c_get_action_id%NOTFOUND;
1297 d_progress := 220;
1298
1299 PO_UDA_DEFAULTING_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION l_action_id : ' || l_action_id, d_progress);
1300 PO_UDA_DEFAULTING_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION l_data_level_name : ' || l_data_level_name, d_progress);
1301 PO_UDA_DEFAULTING_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION l_data_level_id : ' || l_data_level_id, d_progress);
1302 PO_UDA_DEFAULTING_PKG.DEBUG('x_pk_column_name_value_pairs is as follows :', d_progress);
1303 PO_UDA_DEFAULTING_PKG.DEBUG(x_pk_column_name_value_pairs, d_progress);
1304 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_row_table is as follows :', d_progress);
1305 PO_UDA_DEFAULTING_PKG.DEBUG(l_attributes_row_table, d_progress);
1306 PO_UDA_DEFAULTING_PKG.DEBUG('l_attr_name_value_pairs is as follows :', d_progress);
1307 PO_UDA_DEFAULTING_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1308 PO_UDA_DEFAULTING_PKG.DEBUG('x_external_attr_value_pairs is as follows :', d_progress);
1309 PO_UDA_DEFAULTING_PKG.DEBUG(x_external_attr_value_pairs, d_progress);
1310 PO_UDA_DEFAULTING_PKG.DEBUG('Before EGO_EXT_FWK_PUB.EXECUTE_FUNCTION x_return_status :' || x_return_status, d_progress);
1311
1312 d_progress := 230;
1313 EGO_EXT_FWK_PUB.EXECUTE_FUNCTION
1314 (
1315 p_Action_Id => l_action_id
1316 ,p_pk_col_value_pairs => x_pk_column_name_value_pairs
1317 ,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))
1318 ,x_attributes_row_table => l_attributes_row_table
1319 ,x_attributes_data_table => l_attr_name_value_pairs
1320 ,x_external_attrs_value_pairs => x_external_attr_value_pairs
1321 ,x_return_status => x_return_status
1322 ,x_errorcode => x_errorcode
1323 ,x_msg_count => x_msg_count
1324 ,x_msg_data => x_msg_data
1325 );
1326 d_progress := 240;
1327 PO_UDA_DEFAULTING_PKG.DEBUG('After EGO_EXT_FWK_PUB.EXECUTE_FUNCTION x_return_status : ' || x_return_status, d_progress);
1328 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode : ' || x_errorcode, d_progress);
1329 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count : ' || x_msg_count, d_progress);
1330 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data : ' || x_msg_data, d_progress);
1331
1332 d_progress := 250;
1333 PO_UDA_DEFAULTING_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', x_external_attr_value_pairs, ext_name_val_obj);
1334 uda_return_status := ext_name_val_obj.VALUE ;
1335 PO_UDA_DEFAULTING_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', x_external_attr_value_pairs, ext_name_val_obj);
1336 uda_msg_data := ext_name_val_obj.VALUE ;
1337
1338 d_progress := 260;
1339 IF uda_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1340 g_uda_return_status := uda_return_status;
1341 END IF;
1342
1343 d_progress := 270;
1344 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1345 d_progress := 280;
1346 EXIT;
1347 END IF;
1348
1349 d_progress := 290;
1350 PO_UDA_DEFAULTING_PKG.DEBUG('Starting Putting back the new object to the old one', d_progress);
1351 IF (l_attr_name_value_pairs IS NOT NULL AND l_attr_name_value_pairs.COUNT > 0) THEN
1352 d_progress := 300;
1353 l_attr_id_index1 := l_attr_name_value_pairs.FIRST;
1354 d_progress := 310;
1355 WHILE (l_attr_id_index1 IS NOT NULL AND l_attr_id_index1 <= l_attr_name_value_pairs.LAST)
1356 LOOP
1357 d_progress := 320;
1358 IF l_attr_name_value_pairs(l_attr_id_index1) IS NOT NULL THEN
1359 d_progress := 330;
1360 l_attr_obj1 := l_attr_name_value_pairs(l_attr_id_index1);
1361 IF (x_attr_name_value_pairs IS NOT NULL AND x_attr_name_value_pairs.COUNT > 0) THEN
1362 d_progress := 340;
1363 l_attr_id_index := x_attr_name_value_pairs.FIRST;
1364 WHILE (l_attr_id_index IS NOT NULL AND l_attr_id_index <= x_attr_name_value_pairs.LAST)
1365 LOOP
1366 d_progress := 350;
1367 IF x_attr_name_value_pairs(l_attr_id_index) IS NOT NULL THEN
1368 d_progress := 360;
1369 l_attr_obj := x_attr_name_value_pairs(l_attr_id_index);
1370 IF l_attr_obj.USER_ROW_IDENTIFIER = l_attr_obj1.USER_ROW_IDENTIFIER THEN
1371 d_progress := 370;
1372 x_attr_name_value_pairs(l_attr_id_index) := l_attr_obj1;
1373 EXIT;
1374 ELSIF l_attr_obj.ROW_IDENTIFIER = l_attr_obj1.ROW_IDENTIFIER
1375 AND l_attr_obj.ATTR_NAME = l_attr_obj1.ATTR_NAME THEN
1376 d_progress := 375;
1377 x_attr_name_value_pairs(l_attr_id_index) := l_attr_obj1;
1378 EXIT;
1379 END IF;
1380 END IF;
1381 d_progress := 380;
1382 l_attr_id_index := x_attr_name_value_pairs.NEXT(l_attr_id_index);
1383 END LOOP;
1384 END IF;
1385 END IF;
1386 d_progress := 390;
1387 l_attr_id_index1 := l_attr_name_value_pairs.NEXT(l_attr_id_index1);
1388 END LOOP;
1389 END IF;
1390 d_progress := 400;
1391 PO_UDA_DEFAULTING_PKG.DEBUG('Done Putting back the new object to the old one', d_progress);
1392
1393 IF (p_event = 'XV') AND (x_external_attr_value_pairs IS NOT NULL AND x_external_attr_value_pairs.COUNT > 0) THEN
1394 d_progress := 410;
1395 PO_UDA_DEFAULTING_PKG.DEBUG('Checking if XV has failed', d_progress);
1396 PO_UDA_DEFAULTING_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', x_external_attr_value_pairs, ext_name_val_obj);
1397 x_return_status := ext_name_val_obj.VALUE ;
1398 PO_UDA_DEFAULTING_PKG.DEBUG('Return status for action is ' || x_return_status, d_progress);
1399
1400 d_progress := 420;
1401 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1402 d_progress := 43;
1403 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1404 (
1405 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION for Action ID :' || l_action_id
1406 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION'
1407 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
1408 );
1409 d_progress := 440;
1410 EXIT;
1411 END IF;
1412 d_progress := 450;
1413 ELSIF (p_event = 'XD') AND All_Attr_Values_Are_Null (l_attr_name_value_pairs) THEN
1414 d_progress := 460;
1415 PO_UDA_DEFAULTING_PKG.DEBUG('Removing the attributes with value is null', d_progress);
1416 IF (x_attr_name_value_pairs IS NOT NULL AND x_attr_name_value_pairs.COUNT > 0) THEN
1417 l_attr_id_index := x_attr_name_value_pairs.FIRST;
1418 d_progress := 470;
1419 WHILE (l_attr_id_index IS NOT NULL AND l_attr_id_index <= x_attr_name_value_pairs.LAST)
1420 LOOP
1421 d_progress := 480;
1422 IF x_attr_name_value_pairs(l_attr_id_index) IS NOT NULL THEN
1423 l_attr_obj := x_attr_name_value_pairs(l_attr_id_index);
1424 d_progress := 490;
1425 IF l_attr_obj.ROW_IDENTIFIER = l_attr_row_obj.ROW_IDENTIFIER THEN
1426 d_progress := 500;
1427 x_attr_name_value_pairs.DELETE(l_attr_id_index);
1428 END IF;
1429 END IF;
1430 d_progress := 510;
1431 l_attr_id_index := x_attr_name_value_pairs.NEXT(l_attr_id_index);
1432 END LOOP;
1433 END IF;
1434 d_progress := 520;
1435 x_attributes_row_table.DELETE(l_attr_group_id_index);
1436 END IF;
1437
1438 d_progress := 530;
1439 END LOOP;
1440 CLOSE c_get_action_id;
1441
1442 IF g_uda_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1443 d_progress := 540;
1444 PO_UDA_DEFAULTING_PKG.DEBUG('g_uda_return_status is a failure', d_progress);
1445 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', g_uda_return_status, x_external_attr_value_pairs);
1446 END IF;
1447 d_progress := 550;
1448 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1449 PO_UDA_DEFAULTING_PKG.DEBUG('x_return_status is a failure', d_progress);
1450 d_progress := 560;
1451 EXIT;
1452 END IF;
1453 d_progress := 570;
1454 END IF;
1455 d_progress := 580;
1456 l_attr_group_id_index := x_attributes_row_table.NEXT(l_attr_group_id_index);
1457 END LOOP;
1458 IF x_return_status IS NULL THEN
1459 d_progress := 600;
1460 x_return_status := FND_API.G_RET_STS_SUCCESS;
1461 END IF;
1462 d_progress := 610;
1463 END IF;
1464
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 l_err_txt := 'Exception in EXECUTE_IMPORT_UDA_FUNCTION with ERROR : ' || SQLERRM;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1470 (
1471 p_msg => l_err_txt
1472 ,p_msg_code => 'UNEXPECTED_EXECUTE_IMPORT_UDA_FUNCTION'
1473 ,p_attr_grp_id => l_attr_row_obj.ATTR_GROUP_ID
1474 );
1475 END EXECUTE_IMPORT_UDA_FUNCTION;
1476
1477 --------------------------------------------------------------------------------------------------------------------
1478 --Start of Comments
1479 --Name: IMPORT_UDA_ATTRS
1480 --Procedure:
1481 -- This procedure would execute all the procedure in a process to complete the import flow
1482 -- Starting with POPULATE_STRUCTURES, POPULATE_REMAINING_ATTRIBUTES, POPULATE_EMPTY_AGS
1483 -- EXECUTE_IMPORT_UDA_FUNCTION with event 'XD' followed by 'XLE'
1484 -- Then it executes EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data to execute the
1485 -- EGO validations and then EXECUTE_IMPORT_UDA_FUNCTION with event 'XV'
1486 -- Then loads the data into the appropriate EXT tables using the procedure
1487 -- EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data.
1488 --Parameters:
1489 --IN:
1490 -- 1. p_transaction_id
1491 -- Unique identifier of the transaction
1492 -- 2. p_attr_grp_type
1493 -- The attribute Group Type (Entity Type)
1494 -- 3. p_pk1_value, p_pk2_value, p_pk3_value, p_pk4_value, p_pk5_value
1495 -- The Primary Key values for the UDA Attributes
1496 --IN/OUT:
1497 -- 1. x_po_uda_usage_object_array
1498 -- This variable of type PO_UDA_USAGE_OBJECT_ARRAY contains data of hhe usages.
1499 -- 2. x_external_attr_value_pairs
1500 -- This variable would contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
1501 --OUT:
1502 -- 1. x_return_status - Generic Out parameter having return status
1503 -- 2. x_errorcode - Generic Out parameter having error code
1504 -- 3. x_msg_count - Generic Out parameter having return msg Count
1505 -- 4. x_msg_data - Generic Out parameter having return msg
1506 --
1507 --End of Comments
1508 --------------------------------------------------------------------------------------------------------------------
1509 PROCEDURE IMPORT_UDA_ATTRS
1510 (
1511 p_attr_grp_type IN VARCHAR2
1512 ,p_pk1_value IN NUMBER
1513 ,p_pk2_value IN NUMBER DEFAULT NULL
1514 ,p_pk3_value IN NUMBER DEFAULT NULL
1515 ,p_pk4_value IN NUMBER DEFAULT NULL
1516 ,p_pk5_value IN NUMBER DEFAULT NULL
1517 ,x_po_uda_usage_object_array IN OUT NOCOPY PO_UDA_USAGE_OBJECT_ARRAY
1518 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
1519 ,x_return_status OUT NOCOPY VARCHAR2
1520 ,x_errorcode OUT NOCOPY NUMBER
1521 ,x_msg_count OUT NOCOPY NUMBER
1522 ,x_msg_data OUT NOCOPY VARCHAR2
1523 ,p_commit_flag IN VARCHAR2 DEFAULT 'Y' --CLM Release4
1524 ,p_transaction_id IN NUMBER DEFAULT NULL
1525 )
1526 IS
1527 l_base_object_name VARCHAR2(200);
1528 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1529 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
1530 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1531 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1532 d_progress NUMBER := 0;
1533 l_failed_row_id_list VARCHAR2(2000);
1534
1535 l_err_txt VARCHAR2(2000);
1536
1537 BEGIN
1538 d_progress := 10;
1539 PO_UDA_DEFAULTING_PKG.DEBUG('Starting IMPORT_UDA_ATTRS', d_progress);
1540 PO_UDA_DEFAULTING_PKG.DEBUG('p_transaction_id '|| p_transaction_id, d_progress);
1541 PO_UDA_DEFAULTING_PKG.DEBUG('p_attr_grp_type : ' || p_attr_grp_type, d_progress);
1542 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk1_value : ' || p_pk1_value, d_progress);
1543 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk2_value : ' || p_pk2_value, d_progress);
1544 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk3_value : ' || p_pk3_value, d_progress);
1545 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk4_value : ' || p_pk4_value, d_progress);
1546 PO_UDA_DEFAULTING_PKG.DEBUG('p_pk5_value : ' || p_pk5_value, d_progress);
1547 PO_UDA_DEFAULTING_PKG.DEBUG('x_po_uda_usage_object_array is as follows : ', d_progress);
1548 PO_UDA_DEFAULTING_PKG.DEBUG(x_po_uda_usage_object_array, d_progress);
1549 PO_UDA_DEFAULTING_PKG.DEBUG('x_external_attr_value_pairs is as follows : ', d_progress);
1550 PO_UDA_DEFAULTING_PKG.DEBUG(x_external_attr_value_pairs, d_progress);
1551 PO_UDA_DEFAULTING_PKG.DEBUG('p_commit_flag : ' || p_commit_flag, d_progress);
1552 d_progress := 20;
1553
1554 PO_UDA_DEFAULTING_PKG.DEBUG('Before PO_UDA_IMPORT_PKG.UPDATE_STATUS to change to IN_PROGRESS from PENDING', d_progress);
1555 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1556 (
1557 p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1558 ,p_pre_process_status => PO_UDA_IMPORT_PKG.PENDING
1559 ,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
1560 ,p_pk1_value => p_pk1_value
1561 ,p_pk2_value => p_pk2_value
1562 ,p_pk3_value => p_pk3_value
1563 ,p_pk4_value => p_pk4_value
1564 ,p_pk5_value => p_pk5_value
1565 );
1566 PO_UDA_DEFAULTING_PKG.DEBUG('After PO_UDA_IMPORT_PKG.UPDATE_STATUS to change to PENDING from IN_PROGRESS', d_progress);
1567
1568 d_progress := 30;
1569 g_attr_grp_type := p_attr_grp_type;
1570 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
1571 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
1572 d_progress := 40;
1573 PO_UDA_DEFAULTING_PKG.DEBUG('Initialized teh objects l_attributes_row_table, l_attr_name_value_pairs', d_progress);
1574
1575 d_progress := 50;
1576 PO_UDA_DEFAULTING_PKG.DEBUG('Before POPULATE_STRUCTURES ' || x_return_status, d_progress);
1577 PO_UDA_IMPORT_PKG.POPULATE_STRUCTURES
1578 (
1579 p_transaction_id => p_transaction_id
1580 ,p_attr_grp_type => p_attr_grp_type
1581 ,p_pk1_value => p_pk1_value
1582 ,p_pk2_value => p_pk2_value
1583 ,p_pk3_value => p_pk3_value
1584 ,p_pk4_value => p_pk4_value
1585 ,p_pk5_value => p_pk5_value
1586 ,x_base_object_name => l_base_object_name
1587 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1588 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1589 ,x_attributes_row_table => l_attributes_row_table
1590 ,x_class_code_name_value_pairs => l_class_code_name_value_pairs
1591 ,x_return_status => x_return_status
1592 ,x_errorcode => x_errorcode
1593 ,x_msg_count => x_msg_count
1594 ,x_msg_data => x_msg_data
1595 );
1596 d_progress := 60;
1597 PO_UDA_DEFAULTING_PKG.DEBUG('After POPULATE_STRUCTURES ' || x_return_status, d_progress);
1598 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1599 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1600 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1601
1602 PO_UDA_DEFAULTING_PKG.DEBUG('Template ID is :: ' || l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE, d_progress);
1603 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1604 d_progress := 70;
1605 PO_UDA_DEFAULTING_PKG.DEBUG('Before POPULATE_REMAINING_ATTRIBUTES ' || x_return_status, d_progress);
1606 PO_UDA_IMPORT_PKG.POPULATE_REMAINING_ATTRIBUTES
1607 (
1608 x_attr_name_value_pairs => l_attr_name_value_pairs
1609 ,x_attributes_row_table => l_attributes_row_table
1610 ,x_return_status => x_return_status
1611 ,x_errorcode => x_errorcode
1612 ,x_msg_count => x_msg_count
1613 ,x_msg_data => x_msg_data
1614 );
1615 d_progress := 80;
1616 PO_UDA_DEFAULTING_PKG.DEBUG('After POPULATE_REMAINING_ATTRIBUTES ' || x_return_status, d_progress);
1617 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1618 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1619 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1620 ELSE
1621 d_progress := 90;
1622 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1623 (
1624 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
1625 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1626 ,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
1627 ,p_pk1_value => p_pk1_value
1628 ,p_pk2_value => p_pk2_value
1629 ,p_pk3_value => p_pk3_value
1630 ,p_pk4_value => p_pk4_value
1631 ,p_pk5_value => p_pk5_value
1632 );
1633 d_progress := 100;
1634
1635 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1636 (
1637 p_msg => 'There was an error in POPULATE_STRUCTURES'
1638 ,p_msg_code => 'ERROR_POPULATE_STRUCTURES'
1639 ,p_attr_grp_id => NULL
1640 );
1641 d_progress := 110;
1642 END IF;
1643 d_progress := 120;
1644 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1645 d_progress := 130;
1646 PO_UDA_DEFAULTING_PKG.DEBUG('Before POPULATE_EMPTY_AGS ' || x_return_status, d_progress);
1647 PO_UDA_IMPORT_PKG.POPULATE_EMPTY_AGS
1648 (
1649 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1650 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1651 ,p_external_attr_value_pairs => x_external_attr_value_pairs
1652 ,x_po_uda_usage_object_array => x_po_uda_usage_object_array
1653 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1654 ,x_attributes_row_table => l_attributes_row_table
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 d_progress := 140;
1661 PO_UDA_DEFAULTING_PKG.DEBUG('After POPULATE_EMPTY_AGS ' || x_return_status, d_progress);
1662 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1663 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1664 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1665 ELSE
1666 d_progress := 150;
1667 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1668 (
1669 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
1670 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1671 ,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
1672 ,p_pk1_value => p_pk1_value
1673 ,p_pk2_value => p_pk2_value
1674 ,p_pk3_value => p_pk3_value
1675 ,p_pk4_value => p_pk4_value
1676 ,p_pk5_value => p_pk5_value
1677 );
1678 d_progress := 160;
1679 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1680 (
1681 p_msg => 'There was an error in POPULATE_REMAINING_ATTRIBUTES'
1682 ,p_msg_code => 'ERROR_POPULATE_REMAINING_ATTRIBUTES'
1683 ,p_attr_grp_id => NULL
1684 );
1685 d_progress := 170;
1686 END IF;
1687
1688 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1689 d_progress := 180;
1690 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1691 (
1692 p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1693 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1694 ,p_process_phase => PO_UDA_IMPORT_PKG.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 := 190;
1702
1703 PO_UDA_DEFAULTING_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XD) ' || x_return_status, d_progress);
1704 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1705 (
1706 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1707 ,p_event => 'XD'
1708 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1709 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1710 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1711 ,x_attributes_row_table => l_attributes_row_table
1712 ,x_return_status => x_return_status
1713 ,x_errorcode => x_errorcode
1714 ,x_msg_count => x_msg_count
1715 ,x_msg_data => x_msg_data
1716 );
1717 d_progress := 200;
1718 PO_UDA_DEFAULTING_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XD) ' || x_return_status, d_progress);
1719 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1720 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1721 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1722 ELSE
1723 d_progress := 210;
1724 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1725 (
1726 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
1727 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1728 ,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
1729 ,p_pk1_value => p_pk1_value
1730 ,p_pk2_value => p_pk2_value
1731 ,p_pk3_value => p_pk3_value
1732 ,p_pk4_value => p_pk4_value
1733 ,p_pk5_value => p_pk5_value
1734 );
1735 d_progress := 220;
1736 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1737 (
1738 p_msg => 'There was an error in POPULATE_EMPTY_AGS'
1739 ,p_msg_code => 'ERROR_POPULATE_EMPTY_AGS'
1740 ,p_attr_grp_id => NULL
1741 );
1742 d_progress := 230;
1743 END IF;
1744
1745 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1746 d_progress := 240;
1747 PO_UDA_DEFAULTING_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XLE) ' || x_return_status, d_progress);
1748 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1749 (
1750 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1751 ,p_event => 'XLE'
1752 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1753 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1754 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1755 ,x_attributes_row_table => l_attributes_row_table
1756 ,x_return_status => x_return_status
1757 ,x_errorcode => x_errorcode
1758 ,x_msg_count => x_msg_count
1759 ,x_msg_data => x_msg_data
1760 );
1761 d_progress := 250;
1762 PO_UDA_DEFAULTING_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XLE) ' || x_return_status, d_progress);
1763 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1764 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1765 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1766 ELSE
1767 d_progress := 260;
1768 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1769 (
1770 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
1771 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1772 ,p_process_phase => PO_UDA_IMPORT_PKG.DEFAULTING
1773 ,p_pk1_value => p_pk1_value
1774 ,p_pk2_value => p_pk2_value
1775 ,p_pk3_value => p_pk3_value
1776 ,p_pk4_value => p_pk4_value
1777 ,p_pk5_value => p_pk5_value
1778 );
1779 d_progress := 270;
1780 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1781 (
1782 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION (XD)'
1783 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION_XD'
1784 ,p_attr_grp_id => NULL
1785 );
1786 d_progress := 280;
1787 END IF;
1788
1789 --<MOD API Project>
1790 -- Amount needs to be calculated in case of complex pricing
1791 -- Hence a call to XPC has been added
1792 IF p_attr_grp_type = 'PO_LINE_EXT_ATTRS'
1793 THEN
1794 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1795 (
1796 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1797 ,p_event => 'XPC'
1798 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1799 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1800 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1801 ,x_attributes_row_table => l_attributes_row_table
1802 ,x_return_status => x_return_status
1803 ,x_errorcode => x_errorcode
1804 ,x_msg_count => x_msg_count
1805 ,x_msg_data => x_msg_data
1806 );
1807
1808 d_progress := 285;
1809 PO_UDA_DEFAULTING_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XPC) ' || x_return_status, d_progress);
1810 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1811 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1812 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1813
1814 END IF;
1815
1816 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1817 d_progress := 290;
1818 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1819 (
1820 p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1821 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1822 ,p_process_phase => PO_UDA_IMPORT_PKG.EGO_VALIDATE
1823 ,p_pk1_value => p_pk1_value
1824 ,p_pk2_value => p_pk2_value
1825 ,p_pk3_value => p_pk3_value
1826 ,p_pk4_value => p_pk4_value
1827 ,p_pk5_value => p_pk5_value
1828 );
1829 d_progress := 300;
1830
1831 PO_UDA_DEFAULTING_PKG.DEBUG('Before VALIDATE_ROW ' || x_return_status, d_progress);
1832 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_row_table.COUNT ' || l_attributes_row_table.COUNT, d_progress);
1833 IF l_attributes_row_table.COUNT > 0 THEN
1834 PO_UDA_DEFAULTING_PKG.DEBUG('Before EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data with parameters ' || x_return_status, d_progress);
1835 PO_UDA_DEFAULTING_PKG.DEBUG('l_base_object_name : ' || l_base_object_name, d_progress);
1836 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_row_table is as follows : ', d_progress);
1837 PO_UDA_DEFAULTING_PKG.DEBUG(l_attributes_row_table, d_progress);
1838 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_data_table is as follows : ', d_progress);
1839 PO_UDA_DEFAULTING_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1840 PO_UDA_DEFAULTING_PKG.DEBUG('l_pk_column_name_value_pairs is as follows : ', d_progress);
1841 PO_UDA_DEFAULTING_PKG.DEBUG(l_pk_column_name_value_pairs, d_progress);
1842 PO_UDA_DEFAULTING_PKG.DEBUG('l_class_code_name_value_pairs is as follows : ', d_progress);
1843 PO_UDA_DEFAULTING_PKG.DEBUG(l_class_code_name_value_pairs, d_progress);
1844 d_progress := 310;
1845
1846 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
1847 (
1848 p_api_version => 1.0
1849 ,p_object_name => l_base_object_name
1850 ,p_attributes_row_table => l_attributes_row_table
1851 ,p_attributes_data_table => l_attr_name_value_pairs
1852 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1853 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
1854 ,p_validate_only => FND_API.G_TRUE
1855 ,x_failed_row_id_list => l_failed_row_id_list
1856 ,x_return_status => x_return_status
1857 ,x_errorcode => x_errorcode
1858 ,x_msg_count => x_msg_count
1859 ,x_msg_data => x_msg_data
1860 );
1861 d_progress := 320;
1862 END IF;
1863 PO_UDA_DEFAULTING_PKG.DEBUG('After VALIDATE_ROW ' || x_return_status, d_progress);
1864 PO_UDA_DEFAULTING_PKG.DEBUG('l_failed_row_id_list is : ' || l_failed_row_id_list, d_progress);
1865 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1866 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1867 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1868 d_progress := 330;
1869 ELSE
1870 d_progress := 340;
1871 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1872 (
1873 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
1874 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1875 ,p_process_phase => PO_UDA_IMPORT_PKG.DEFAULTING
1876 ,p_pk1_value => p_pk1_value
1877 ,p_pk2_value => p_pk2_value
1878 ,p_pk3_value => p_pk3_value
1879 ,p_pk4_value => p_pk4_value
1880 ,p_pk5_value => p_pk5_value
1881 );
1882 d_progress := 350;
1883 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1884 (
1885 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION (XLE)'
1886 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION_XLE'
1887 ,p_attr_grp_id => NULL
1888 );
1889 d_progress := 360;
1890 END IF;
1891 d_progress := 370;
1892 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1893 d_progress := 380;
1894
1895 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1896 (
1897 p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1898 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1899 ,p_process_phase => PO_UDA_IMPORT_PKG.CUSTOM_VALIDATE
1900 ,p_pk1_value => p_pk1_value
1901 ,p_pk2_value => p_pk2_value
1902 ,p_pk3_value => p_pk3_value
1903 ,p_pk4_value => p_pk4_value
1904 ,p_pk5_value => p_pk5_value
1905 );
1906 d_progress := 390;
1907
1908 IF x_external_attr_value_pairs IS NULL THEN
1909 d_progress := 400;
1910 x_external_attr_value_pairs := EGO_COL_NAME_VALUE_PAIR_TABLE();
1911 END IF;
1912 d_progress := 410;
1913
1914 PO_UDA_DEFAULTING_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XV) ' || x_return_status, d_progress);
1915 IF l_attributes_row_table.COUNT > 0 THEN
1916 d_progress := 420;
1917 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
1918 (
1919 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
1920 ,p_event => 'XV'
1921 ,x_external_attr_value_pairs => x_external_attr_value_pairs
1922 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1923 ,x_attr_name_value_pairs => l_attr_name_value_pairs
1924 ,x_attributes_row_table => l_attributes_row_table
1925 ,x_return_status => x_return_status
1926 ,x_errorcode => x_errorcode
1927 ,x_msg_count => x_msg_count
1928 ,x_msg_data => x_msg_data
1929 );
1930 d_progress := 430;
1931 END IF;
1932
1933 PO_UDA_DEFAULTING_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XV) ' || x_return_status, d_progress);
1934 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
1935 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
1936 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
1937 d_progress := 440;
1938
1939 ELSE
1940 d_progress := 450;
1941 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1942 (
1943 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
1944 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1945 ,p_process_phase => PO_UDA_IMPORT_PKG.EGO_VALIDATE
1946 ,p_pk1_value => p_pk1_value
1947 ,p_pk2_value => p_pk2_value
1948 ,p_pk3_value => p_pk3_value
1949 ,p_pk4_value => p_pk4_value
1950 ,p_pk5_value => p_pk5_value
1951 );
1952 d_progress := 460;
1953 PO_UDA_IMPORT_PKG.HANDLE_ERROR
1954 (
1955 p_msg => 'There was an error in EGO Validate'
1956 ,p_msg_code => 'ERROR_EGO_VALIDATE_ERROR'
1957 ,p_attr_grp_id => NULL
1958 );
1959 d_progress := 470;
1960 END IF;
1961
1962 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1963 d_progress := 480;
1964
1965 PO_UDA_IMPORT_PKG.UPDATE_STATUS
1966 (
1967 p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1968 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
1969 ,p_process_phase => PO_UDA_IMPORT_PKG.LOAD
1970 ,p_pk1_value => p_pk1_value
1971 ,p_pk2_value => p_pk2_value
1972 ,p_pk3_value => p_pk3_value
1973 ,p_pk4_value => p_pk4_value
1974 ,p_pk5_value => p_pk5_value
1975 );
1976 d_progress := 490;
1977
1978 PO_UDA_DEFAULTING_PKG.DEBUG('Before Process_User_Attrs_Data ' || x_return_status, d_progress);
1979 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_row_table.COUNT ' || l_attributes_row_table.COUNT, d_progress);
1980 IF l_attributes_row_table.COUNT > 0 THEN
1981 PO_UDA_DEFAULTING_PKG.DEBUG('Before EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data with parameters ' || x_return_status, d_progress);
1982 PO_UDA_DEFAULTING_PKG.DEBUG('l_base_object_name : ' || l_base_object_name, d_progress);
1983 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_row_table is as follows : ', d_progress);
1984 PO_UDA_DEFAULTING_PKG.DEBUG(l_attributes_row_table, d_progress);
1985 PO_UDA_DEFAULTING_PKG.DEBUG('l_attributes_data_table is as follows : ', d_progress);
1986 PO_UDA_DEFAULTING_PKG.DEBUG(l_attr_name_value_pairs, d_progress);
1987 PO_UDA_DEFAULTING_PKG.DEBUG('l_pk_column_name_value_pairs is as follows : ', d_progress);
1988 PO_UDA_DEFAULTING_PKG.DEBUG(l_pk_column_name_value_pairs, d_progress);
1989 PO_UDA_DEFAULTING_PKG.DEBUG('l_class_code_name_value_pairs is as follows : ', d_progress);
1990 PO_UDA_DEFAULTING_PKG.DEBUG(l_class_code_name_value_pairs, d_progress);
1991 d_progress := 500;
1992 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
1993 (
1994 p_api_version => 1.0
1995 ,p_object_name => l_base_object_name
1996 ,p_attributes_row_table => l_attributes_row_table
1997 ,p_attributes_data_table => l_attr_name_value_pairs
1998 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
1999 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
2000 ,x_failed_row_id_list => l_failed_row_id_list
2001 ,x_return_status => x_return_status
2002 ,x_errorcode => x_errorcode
2003 ,x_msg_count => x_msg_count
2004 ,x_msg_data => x_msg_data
2005 );
2006 d_progress := 510;
2007 END IF;
2008
2009 PO_UDA_DEFAULTING_PKG.DEBUG('After Process_User_Attrs_Data ' || x_return_status, d_progress);
2010 PO_UDA_DEFAULTING_PKG.DEBUG('l_failed_row_id_list is : ' || l_failed_row_id_list, d_progress);
2011 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
2012 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
2013 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
2014 d_progress := 520;
2015 ELSE
2016 d_progress := 530;
2017 PO_UDA_IMPORT_PKG.UPDATE_STATUS
2018 (
2019 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
2020 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
2021 ,p_process_phase => PO_UDA_IMPORT_PKG.CUSTOM_VALIDATE
2022 ,p_pk1_value => p_pk1_value
2023 ,p_pk2_value => p_pk2_value
2024 ,p_pk3_value => p_pk3_value
2025 ,p_pk4_value => p_pk4_value
2026 ,p_pk5_value => p_pk5_value
2027 );
2028 d_progress := 540;
2029 PO_UDA_IMPORT_PKG.HANDLE_ERROR
2030 (
2031 p_msg => 'There was an error in EXECUTE_IMPORT_UDA_FUNCTION (XV)'
2032 ,p_msg_code => 'ERROR_EXECUTE_IMPORT_UDA_FUNCTION_XLE'
2033 ,p_attr_grp_id => NULL
2034 );
2035 d_progress := 550;
2036 END IF;
2037
2038 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2039 d_progress := 560;
2040 PO_UDA_DEFAULTING_PKG.DEBUG('Before EXECUTE_IMPORT_UDA_FUNCTION (XPD) ' || x_return_status, d_progress);
2041 IF l_attributes_row_table.COUNT > 0 THEN
2042 d_progress := 570;
2043 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
2044 (
2045 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
2046 ,p_event => 'XPD'
2047 ,x_external_attr_value_pairs => x_external_attr_value_pairs
2048 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
2049 ,x_attr_name_value_pairs => l_attr_name_value_pairs
2050 ,x_attributes_row_table => l_attributes_row_table
2051 ,x_return_status => x_return_status
2052 ,x_errorcode => x_errorcode
2053 ,x_msg_count => x_msg_count
2054 ,x_msg_data => x_msg_data
2055 );
2056 d_progress := 580;
2057 END IF;
2058
2059 PO_UDA_DEFAULTING_PKG.DEBUG('After EXECUTE_IMPORT_UDA_FUNCTION (XPD) ' || x_return_status, d_progress);
2060 PO_UDA_DEFAULTING_PKG.DEBUG('x_errorcode ' || x_errorcode, d_progress);
2061 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_count ' || x_msg_count, d_progress);
2062 PO_UDA_DEFAULTING_PKG.DEBUG('x_msg_data ' || x_msg_data, d_progress);
2063 d_progress := 590;
2064 ELSE
2065 d_progress := 600;
2066 PO_UDA_IMPORT_PKG.UPDATE_STATUS
2067 (
2068 p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
2069 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
2070 ,p_process_phase => PO_UDA_IMPORT_PKG.LOAD
2071 ,p_pk1_value => p_pk1_value
2072 ,p_pk2_value => p_pk2_value
2073 ,p_pk3_value => p_pk3_value
2074 ,p_pk4_value => p_pk4_value
2075 ,p_pk5_value => p_pk5_value
2076 );
2077 d_progress := 610;
2078 PO_UDA_IMPORT_PKG.HANDLE_ERROR
2079 (
2080 p_msg => 'There was an error in loading UDA Attributes'
2081 ,p_msg_code => 'ERROR_EGO_LOAD_ERROR'
2082 ,p_attr_grp_id => NULL
2083 );
2084 d_progress := 620;
2085 END IF;
2086 d_progress := 630;
2087 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2088 d_progress := 640;
2089 PO_UDA_IMPORT_PKG.UPDATE_STATUS
2090 (
2091 p_process_status => PO_UDA_IMPORT_PKG.COMPLETED
2092 ,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
2093 ,p_process_phase => PO_UDA_IMPORT_PKG.LOAD
2094 ,p_pk1_value => p_pk1_value
2095 ,p_pk2_value => p_pk2_value
2096 ,p_pk3_value => p_pk3_value
2097 ,p_pk4_value => p_pk4_value
2098 ,p_pk5_value => p_pk5_value
2099 );
2100 d_progress := 650;
2101 END IF;
2102
2103 --CLM Release4: Moved commit from update_status to avoid intermediate commits
2104 --Commit will now be based upon p_commit_flag whose default value is Y
2105 IF p_commit_flag = 'Y'
2106 THEN
2107 COMMIT;
2108 END IF;
2109
2110
2111 EXCEPTION
2112 WHEN OTHERS THEN
2113 l_err_txt := 'Exception in IMPORT_UDA_ATTRS with ERROR : ' || SQLERRM;
2114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2115 PO_UDA_IMPORT_PKG.HANDLE_ERROR
2116 (
2117 p_msg => l_err_txt
2118 ,p_msg_code => 'UNEX_EXEC_IMPRT_UDA_FUNC'
2119 ,p_attr_grp_id => NULL
2120 );
2121 END IMPORT_UDA_ATTRS;
2122
2123 PROCEDURE HANDLE_ERROR
2124 (
2125 p_msg IN VARCHAR2
2126 ,p_msg_code IN VARCHAR2
2127 ,p_attr_grp_id IN NUMBER
2128 )
2129 IS
2130 BEGIN
2131
2132 if fnd_global.CONC_REQUEST_ID IS NOT NULL AND fnd_global.CONC_REQUEST_ID <> -1 THEN
2133 PO_INTERFACE_ERRORS_SV1.insert_po_interface_errors_msg
2134 (
2135 X_interface_type => g_attr_grp_type,
2136 X_Interface_Header_ID => g_interface_reference_id,
2137 X_Interface_Line_Id => NULL,
2138 X_Interface_Dist_Id => NULL,
2139 X_error_message_text => p_msg,
2140 X_error_message_name => p_msg_code,
2141 X_column_name => NULL,
2142 X_table_name => NULL,
2143 X_batch_id => p_attr_grp_id
2144 );
2145 else
2146 PO_UDA_DEFAULTING_PKG.DEBUG ('IN HANDLE ERROR : '||p_msg ||' code ' ||p_msg_code ||' for attr grp id '||p_attr_grp_id, 0);
2147 end if;
2148
2149 END HANDLE_ERROR;
2150
2151 PROCEDURE UPDATE_STATUS
2152 (
2153 p_process_status IN NUMBER
2154 ,p_pre_process_status IN NUMBER
2155 ,p_process_phase IN VARCHAR2
2156 ,p_pk1_value IN NUMBER
2157 ,p_pk2_value IN NUMBER
2158 ,p_pk3_value IN NUMBER
2159 ,p_pk4_value IN NUMBER
2160 ,p_pk5_value IN NUMBER
2161 )
2162 IS
2163 BEGIN
2164 IF p_process_status IN (PO_UDA_IMPORT_PKG.PENDING, PO_UDA_IMPORT_PKG.IN_PROGRESS, PO_UDA_IMPORT_PKG.COMPLETED) THEN
2165 UPDATE PO_UDA_INTERFACE
2166 SET PROCESS_STATUS = p_process_status
2167 ,PROCESS_PHASE = p_process_phase
2168 WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
2169 AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
2170 AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
2171 AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
2172 AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
2173 AND PROCESS_STATUS = p_pre_process_status;
2174 --CLM Release4 : Getting rid of intermediate commit.
2175 --Moved the commit to IMPORT_UDA_ATTRS
2176 --COMMIT;
2177 END IF;
2178 END;
2179
2180 --------------------------------------------------------------------------------------------------------------------
2181 --Start of Comments
2182 --Name: TRANSFER_UDA_DATA
2183 --Procedure:
2184 -- This procedure imports data from po_uda_interface table to ext table
2185 -- This method used in MOD API flow
2186 --Parameters:
2187 --IN:
2188 -- 1. p_header_id
2189 -- Po Header Id of the document
2190 -- 2. p_draft_id
2191 -- Draft Id of the document
2192 -- 3. p_attr_display_name_list
2193 --- This list contains the attributesDisplayName
2194 --4. p_transaction_id
2195 -- Unique identifier of the transaction
2196 --End of Comments
2197 --------------------------------------------------------------------------------------------------------------------
2198
2199 PROCEDURE TRANSFER_UDA_DATA
2200 (
2201 p_header_id IN NUMBER,
2202 p_draft_id IN NUMBER,
2203 p_attr_display_name_list IN po_tbl_varchar100 DEFAULT NULL ,
2204 p_transaction_id IN NUMBER)
2205 IS
2206
2207 d_api_name VARCHAR2(50) := 'TRANSFER_UDA_DATA';
2208 d_pkg_name CONSTANT VARCHAR2(30) := PO_LOG.get_package_base('PO_UDA_IMPORT_PKG');
2209 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2210 l_uda_pk1_tbl po_tbl_number ;
2211 l_uda_pk2_tbl po_tbl_number ;
2212 l_attr_group_type_tbl po_tbl_varchar40 ;
2213 l_attr_group_id_tbl po_tbl_number;
2214 l_po_lines_tbl po_tbl_number;
2215 l_template_id_tbl po_tbl_number;
2216 l_draft_id_tbl po_tbl_number;
2217 x_po_uda_usage_object_array PO_UDA_USAGE_OBJECT_ARRAY;
2218 x_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;-- := new EGO_COL_NAME_VALUE_PAIR_TABLE();
2219 x_return_status VARCHAR2(100) := null;
2220 x_msg_data VARCHAR2(100) := null;
2221 x_errorcode NUMBER := null;
2222 x_msg_count NUMBER := 0;
2223 test_ext_param ego_col_name_value_pair_obj;
2224 l_attribute_category VARCHAR2(30);
2225 l_language_code_tbl po_tbl_varchar5;
2226 l_org_id NUMBER;
2227 l_records_exist VARCHAR2(1):= 'N';
2228 d_position NUMBER;
2229 l_invalid_line_count NUMBER := 0;
2230 l_matching_basis VARCHAR2(30);
2231 l_quant NUMBER;
2232 Cursor c_uda_intf(p_transaction_id number) IS
2233
2234 SELECT pk1_value, pk2_value, attr_group_type
2235 FROM po_uda_interface pui
2236 WHERE transaction_id = p_transaction_id
2237 AND attr_group_type IN ('PO_HEADER_EXT_ATTRS', 'PO_LINE_EXT_ATTRS','PO_SHIPMENTS_EXT_ATTRS' ) ;
2238
2239 BEGIN
2240
2241 d_position := 0;
2242
2243 OPEN c_uda_intf(p_transaction_id);
2244 LOOP
2245 FETCH c_uda_intf BULK COLLECT INTO l_uda_pk1_tbl, l_uda_pk2_tbl, l_attr_group_type_tbl;
2246 EXIT WHEN c_uda_intf%NOTFOUND ;
2247 END LOOP;
2248
2249 SELECT org_id
2250 INTO l_org_id
2251 FROM po_headers_draft_all
2252 WHERE po_header_id = p_header_id
2253 AND draft_id = p_draft_id;
2254
2255 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR( p_ext_attr_name => 'ORG_ID',
2256 p_ext_attr_value => l_org_id,
2257 x_external_attr_value_pairs => x_external_attr_value_pairs
2258 );
2259
2260 -- Form ego_col_name_value_pair_obj object
2261 -- This is useful for addresses
2262 IF p_attr_display_name_list IS NOT NULL AND p_attr_display_name_list.Count <> 0
2263 THEN
2264
2265 x_external_attr_value_pairs.extend(p_attr_display_name_list.Count);
2266 FOR i IN 1..p_attr_display_name_list.Count
2267 LOOP
2268 x_external_attr_value_pairs(x_external_attr_value_pairs.Count - p_attr_display_name_list.Count+ i):= NEW ego_col_name_value_pair_obj('p_source_item',p_attr_display_name_list(i));
2269 END LOOP;
2270 END IF;
2271
2272 fnd_msg_pub.Initialize;
2273 ERROR_HANDLER.Initialize;
2274
2275 FOR i IN 1..l_uda_pk1_tbl.Count
2276 LOOP
2277
2278 IF (PO_LOG.d_stmt) THEN
2279 PO_LOG.stmt(d_module, d_position, 'inside loop');
2280 END IF;
2281
2282 --Call import function to import data into po_uda_interface table
2283 IMPORT_UDA_ATTRS( p_attr_grp_type => l_attr_group_type_tbl(i), --ATTR_GROUP_TYPE
2284 p_pk1_value => l_uda_pk1_tbl(i), -- po_header_id in case of header level attr, po_line_id in case of line level attr,
2285 p_pk2_value => l_uda_pk2_tbl(i), -- draft_id
2286 x_po_uda_usage_object_array => x_po_uda_usage_object_array,
2287 x_external_attr_value_pairs => x_external_attr_value_pairs,
2288 x_return_status => x_return_status,
2289 x_errorcode => x_errorcode,
2290 x_msg_count => x_msg_count,
2291 x_msg_data => x_msg_data,
2292 p_transaction_id => p_transaction_id,
2293 p_commit_flag => 'N');
2294
2295 d_position := 10;
2296
2297 IF (PO_LOG.d_stmt) THEN
2298 PO_LOG.stmt(d_module, d_position,'x_return_status after IMPORT_UDA_ATTRS : '|| x_return_status);
2299 END IF;
2300
2301 IF Nvl(x_return_status, FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS
2302 THEN
2303 l_invalid_line_count := l_invalid_line_count + 1;
2304 END IF;
2305 END LOOP;
2306
2307 IF (PO_LOG.d_stmt) THEN
2308 PO_LOG.stmt(d_module, d_position, 'The value of l_invalid_line_count is ' ||
2309 l_invalid_line_count);
2310 END IF;
2311
2312 --Return in case of error
2313 IF l_invalid_line_count > 0
2314 THEN
2315 x_return_status := FND_API.G_RET_STS_ERROR;
2316 RETURN;
2317 END IF;
2318
2319
2320 d_position := 20;
2321
2322 --Check if all the mandatory attributes are provided
2323 IF validate_uda_req_attributes(p_transaction_id) = 'N' THEN
2324
2325 IF (PO_LOG.d_exc) THEN
2326 PO_LOG.exc(d_module, d_position,' Validation for mandatory attributes failed ');
2327 END IF;
2328
2329 x_return_status := FND_API.G_RET_STS_ERROR;
2330 x_msg_data := 'Mandatory UDA attributes are missing';
2331 RETURN ;
2332
2333 END IF;
2334
2335
2336 d_position := 30;
2337
2338 --Fetch complex pricing related data
2339 SELECT DISTINCT attr_group_id, pk1_value , pk2_value , uda_template_id
2340 BULK collect INTO l_attr_group_id_tbl , l_po_lines_tbl, l_draft_id_tbl, l_template_id_tbl
2341 FROM po_uda_interface
2342 WHERE ATTR_GROUP_TYPE = 'PO_LINE_EXT_ATTRS'
2343 AND transaction_id = p_transaction_id;
2344
2345 FOR i IN 1..l_attr_group_id_tbl.Count()
2346 LOOP
2347
2348 -- Collect the attribute categories
2349 SELECT attribute_category
2350 into l_attribute_category
2351 from po_uda_ag_template_usages
2352 WHERE attribute_group_id = l_attr_group_id_tbl(i) and template_id = l_template_id_tbl(i) ;
2353
2354 IF l_attribute_category = 'PRICING' THEN
2355 BEGIN
2356 --need to update Qunatity/amount in po_lines_draft_all table
2357
2358 SELECT matching_basis,quantity
2359 INTO l_matching_basis,l_quant
2360 FROM po_lines_draft_all plda
2361 WHERE plda.draft_id = p_draft_id
2362 AND plda.po_line_id = l_po_lines_tbl(i);
2363
2364 IF l_matching_basis = 'AMOUNT' THEN
2365 UPDATE po_lines_draft_all plda
2366 SET (plda.CLM_UDA_PRICING_TOTAL , plda.amount) =
2367 (SELECT plaeb.N_EXT_ATTR20,(plaeb.N_EXT_ATTR20 + Nvl(plda.CLM_APPROVED_UNDEF_AMOUNT,0))-- + plu.undef_amount)
2368 FROM po_lines_all_ext_b plaeb
2369 WHERE plaeb.draft_id = Decode(l_draft_id_tbl(i),p_draft_id,p_draft_id,-1)
2370 AND plaeb.attr_group_id = l_attr_group_id_tbl(i)
2371 AND plaeb.po_line_id = l_po_lines_tbl(i) )
2372 WHERE plda.draft_id = p_draft_id
2373 AND plda.po_line_id = l_po_lines_tbl(i);
2374
2375 UPDATE po_line_locations_draft_all pllda
2376 SET pllda.amount =
2377 ( SELECT amount
2378 FROM po_lines_draft_all plda
2379 WHERE plda.draft_id = p_draft_id
2380 AND plda.po_line_id = l_po_lines_tbl(i)
2381 )
2382 WHERE pllda.draft_id = p_draft_id
2383 AND pllda.po_line_id = l_po_lines_tbl(i)
2384 AND EXISTS (SELECT 'there should be only one shipment with quantity equals to 1'
2385 FROM po_line_locations_draft_all pllda
2386 WHERE pllda.draft_id = p_draft_id
2387 AND pllda.po_line_id = l_po_lines_tbl(i)
2388 AND pllda.amount = 1
2389 HAVING Count(*) = 1);
2390
2391 UPDATE po_distributions_draft_all pdda
2392 SET pdda.amount_ordered =
2393 ( SELECT amount
2394 FROM po_lines_draft_all plda
2395 WHERE plda.draft_id = p_draft_id
2396 AND plda.po_line_id = l_po_lines_tbl(i)
2397 )
2398 WHERE pdda.draft_id = p_draft_id
2399 AND pdda.po_line_id = l_po_lines_tbl(i)
2400 AND EXISTS (SELECT 'there should be only one distribution with quantity equals to 1'
2401 FROM po_distributions_draft_all pdda
2402 WHERE pdda.draft_id = p_draft_id
2403 AND pdda.po_line_id = l_po_lines_tbl(i)
2404 AND pdda.amount_ordered = 1
2405 HAVING Count(*) = 1);
2406
2407 ELSE
2408 UPDATE po_lines_draft_all plda
2409 SET (plda.CLM_UDA_PRICING_TOTAL,plda.quantity) =
2410 (SELECT plaeb.N_EXT_ATTR20,(plaeb.N_EXT_ATTR20 + Nvl(plda.CLM_APPROVED_UNDEF_AMOUNT,0))-- + plu.undef_amount)
2411 FROM po_lines_all_ext_b plaeb
2412 WHERE plaeb.draft_id = Decode(l_draft_id_tbl(i),p_draft_id,p_draft_id,-1)
2413 AND plaeb.attr_group_id = l_attr_group_id_tbl(i)
2414 AND plaeb.po_line_id = l_po_lines_tbl(i) )
2415 WHERE plda.draft_id = p_draft_id
2416 AND plda.po_line_id = l_po_lines_tbl(i);
2417
2418 UPDATE po_line_locations_draft_all pllda
2419 SET pllda.quantity =
2420 ( SELECT quantity
2421 FROM po_lines_draft_all plda
2422 WHERE plda.draft_id = p_draft_id
2423 AND plda.po_line_id = l_po_lines_tbl(i)
2424 )
2425 WHERE pllda.draft_id = p_draft_id
2426 AND pllda.po_line_id = l_po_lines_tbl(i)
2427 AND EXISTS (SELECT 'there should be only one shipment with quantity equlas to 1'
2428 FROM po_line_locations_draft_all pllda
2429 WHERE pllda.draft_id = p_draft_id
2430 AND pllda.po_line_id = l_po_lines_tbl(i)
2431 AND pllda.quantity = 1
2432 HAVING Count(*) = 1);
2433
2434 UPDATE po_distributions_draft_all pdda
2435 SET pdda.quantity_ordered =
2436 ( SELECT quantity
2437 FROM po_lines_draft_all plda
2438 WHERE plda.draft_id = p_draft_id
2439 AND plda.po_line_id = l_po_lines_tbl(i)
2440 )
2441 WHERE pdda.draft_id = p_draft_id
2442 AND pdda.po_line_id = l_po_lines_tbl(i)
2443 AND EXISTS (SELECT 'there should be only one distribution with quantity equlas to 1'
2444 FROM po_distributions_draft_all pdda
2445 WHERE pdda.draft_id = p_draft_id
2446 AND pdda.po_line_id = l_po_lines_tbl(i)
2447 AND pdda.quantity_ordered = 1
2448 HAVING Count(*) = 1);
2449 END IF;
2450
2451 EXCEPTION
2452 WHEN No_Data_Found
2453 THEN
2454 NULL;
2455 END ;
2456
2457 END IF;
2458
2459
2460 IF l_attribute_category = 'PRICING' AND l_draft_id_tbl(i) = p_draft_id
2461 THEN
2462
2463 BEGIN
2464
2465 --Need to check if delta record already exists in po_lines_all_ext_b
2466 --If the record exists, then the record needs to be updated
2467 --If record doesnt exist, then insert a new record in po_lines_all_ext_b
2468 SELECT 'Y'
2469 INTO l_records_exist
2470 FROM dual
2471 WHERE EXISTS ( SELECT 1
2472 FROM
2473 po_lines_all_ext_b
2474 WHERE draft_id = l_draft_id_tbl(i)
2475 AND attr_group_id = l_attr_group_id_tbl(i)
2476 AND po_line_id = l_po_lines_tbl(i)
2477 AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'));
2478
2479 EXCEPTION
2480 WHEN No_Data_Found
2481 THEN NULL;
2482 END ;
2483
2484 d_position := 40;
2485
2486 IF (PO_LOG.d_stmt) THEN
2487 PO_LOG.stmt(d_module, d_position, 'The value of l_records_exist is ' ||
2488 l_records_exist);
2489 END IF;
2490
2491 --Collect installed langages
2492 SELECT language_code
2493 BULK COLLECT INTO l_language_code_tbl
2494 FROM fnd_languages
2495 WHERE INSTALLED_FLAG in ('I', 'B');
2496
2497 --The import UDA api doesnt take care of inserting delta archove and old archive record in
2498 -- po_lines_all_ext_b and po_lines_all_ext_tl.
2499 -- So these records are inserted / updated here
2500 -- Update the existing record if record exists
2501 IF l_records_exist = 'Y'
2502 THEN
2503 UPDATE po_lines_all_ext_b
2504 SET (EXTENSION_ID
2505 ,ATTR_GROUP_ID
2506 ,PO_LINE_ID
2507 ,DATA_LEVEL_ID
2508 ,PK1_VALUE
2509 ,PK2_VALUE
2510 ,PK3_VALUE
2511 ,PK4_VALUE
2512 ,PK5_VALUE
2513 ,LAST_UPDATE_DATE
2514 ,LAST_UPDATED_BY
2515 ,LAST_UPDATE_LOGIN
2516 ,CREATED_BY
2517 ,CREATION_DATE
2518 ,C_EXT_ATTR1
2519 ,C_EXT_ATTR2
2520 ,C_EXT_ATTR3
2521 ,C_EXT_ATTR4
2522 ,C_EXT_ATTR5
2523 ,C_EXT_ATTR6
2524 ,C_EXT_ATTR7
2525 ,C_EXT_ATTR8
2526 ,C_EXT_ATTR9
2527 ,C_EXT_ATTR10
2528 ,C_EXT_ATTR11
2529 ,C_EXT_ATTR12
2530 ,C_EXT_ATTR13
2531 ,C_EXT_ATTR14
2532 ,C_EXT_ATTR15
2533 ,C_EXT_ATTR16
2534 ,C_EXT_ATTR17
2535 ,C_EXT_ATTR18
2536 ,C_EXT_ATTR19
2537 ,C_EXT_ATTR20
2538 ,C_EXT_ATTR21
2539 ,C_EXT_ATTR22
2540 ,C_EXT_ATTR23
2541 ,C_EXT_ATTR24
2542 ,C_EXT_ATTR25
2543 ,C_EXT_ATTR26
2544 ,C_EXT_ATTR27
2545 ,C_EXT_ATTR28
2546 ,C_EXT_ATTR29
2547 ,C_EXT_ATTR30
2548 ,C_EXT_ATTR31
2549 ,C_EXT_ATTR32
2550 ,C_EXT_ATTR33
2551 ,C_EXT_ATTR34
2552 ,C_EXT_ATTR35
2553 ,C_EXT_ATTR36
2554 ,C_EXT_ATTR37
2555 ,C_EXT_ATTR38
2556 ,C_EXT_ATTR39
2557 ,C_EXT_ATTR40
2558 ,N_EXT_ATTR1
2559 ,N_EXT_ATTR2
2560 ,N_EXT_ATTR3
2561 ,N_EXT_ATTR4
2562 ,N_EXT_ATTR5
2563 ,N_EXT_ATTR6
2564 ,N_EXT_ATTR7
2565 ,N_EXT_ATTR8
2566 ,N_EXT_ATTR9
2567 ,N_EXT_ATTR10
2568 ,N_EXT_ATTR11
2569 ,N_EXT_ATTR12
2570 ,N_EXT_ATTR13
2571 ,N_EXT_ATTR14
2572 ,N_EXT_ATTR15
2573 ,N_EXT_ATTR16
2574 ,N_EXT_ATTR17
2575 ,N_EXT_ATTR18
2576 ,N_EXT_ATTR19
2577 ,N_EXT_ATTR20
2578 ,UOM_EXT_ATTR1
2579 ,UOM_EXT_ATTR2
2580 ,UOM_EXT_ATTR3
2581 ,UOM_EXT_ATTR4
2582 ,UOM_EXT_ATTR5
2583 ,UOM_EXT_ATTR6
2584 ,UOM_EXT_ATTR7
2585 ,UOM_EXT_ATTR8
2586 ,UOM_EXT_ATTR9
2587 ,UOM_EXT_ATTR10
2588 ,UOM_EXT_ATTR11
2589 ,UOM_EXT_ATTR12
2590 ,UOM_EXT_ATTR13
2591 ,UOM_EXT_ATTR14
2592 ,UOM_EXT_ATTR15
2593 ,UOM_EXT_ATTR16
2594 ,UOM_EXT_ATTR17
2595 ,UOM_EXT_ATTR18
2596 ,UOM_EXT_ATTR19
2597 ,UOM_EXT_ATTR20
2598 ,D_EXT_ATTR1
2599 ,D_EXT_ATTR2
2600 ,D_EXT_ATTR3
2601 ,D_EXT_ATTR4
2602 ,D_EXT_ATTR5
2603 ,D_EXT_ATTR6
2604 ,D_EXT_ATTR7
2605 ,D_EXT_ATTR8
2606 ,D_EXT_ATTR9
2607 ,D_EXT_ATTR10
2608 ,UDA_TEMPLATE_ID
2609 ,DRAFT_ID
2610 ) =
2611 (SELECT
2612 pl_new.extension_id
2613 ,pl_old.ATTR_GROUP_ID
2614 ,(pl_old.PO_LINE_ID * -1 )
2615 ,(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE')
2616 ,l_draft_id_tbl(i) * -1
2617 ,pl_new.PK2_VALUE
2618 ,pl_new.PK3_VALUE
2619 ,pl_new.PK4_VALUE
2620 ,pl_new.PK5_VALUE
2621 ,pl_new.LAST_UPDATE_DATE
2622 ,pl_new.LAST_UPDATED_BY
2623 ,pl_new.LAST_UPDATE_LOGIN
2624 ,pl_new.CREATED_BY
2625 ,pl_new.CREATION_DATE
2626 ,pl_new.C_EXT_ATTR1
2627 ,pl_new.C_EXT_ATTR2
2628 ,pl_new.C_EXT_ATTR3
2629 ,pl_new.C_EXT_ATTR4
2630 ,pl_new.C_EXT_ATTR5
2631 ,pl_new.C_EXT_ATTR6
2632 ,pl_new.C_EXT_ATTR7
2633 ,pl_new.C_EXT_ATTR8
2634 ,pl_new.C_EXT_ATTR9
2635 ,pl_new.C_EXT_ATTR10
2636 ,pl_new.C_EXT_ATTR11
2637 ,pl_new.C_EXT_ATTR12
2638 ,pl_new.C_EXT_ATTR13
2639 ,pl_new.C_EXT_ATTR14
2640 ,pl_new.C_EXT_ATTR15
2641 ,pl_new.C_EXT_ATTR16
2642 ,pl_new.C_EXT_ATTR17
2643 ,pl_new.C_EXT_ATTR18
2644 ,pl_new.C_EXT_ATTR19
2645 ,pl_new.C_EXT_ATTR20
2646 ,pl_new.C_EXT_ATTR21
2647 ,pl_new.C_EXT_ATTR22
2648 ,pl_new.C_EXT_ATTR23
2649 ,pl_new.C_EXT_ATTR24
2650 ,pl_new.C_EXT_ATTR25
2651 ,pl_new.C_EXT_ATTR26
2652 ,pl_new.C_EXT_ATTR27
2653 ,pl_new.C_EXT_ATTR28
2654 ,pl_new.C_EXT_ATTR29
2655 ,pl_new.C_EXT_ATTR30
2656 ,pl_new.C_EXT_ATTR31
2657 ,pl_new.C_EXT_ATTR32
2658 ,pl_new.C_EXT_ATTR33
2659 ,pl_new.C_EXT_ATTR34
2660 ,pl_new.C_EXT_ATTR35
2661 ,pl_new.C_EXT_ATTR36
2662 ,pl_new.C_EXT_ATTR37
2663 ,pl_new.C_EXT_ATTR38
2664 ,pl_new.C_EXT_ATTR39
2665 ,pl_new.C_EXT_ATTR40
2666 ,pl_new.N_EXT_ATTR1 - pl_old.N_EXT_ATTR1
2667 ,pl_new.N_EXT_ATTR2 - pl_old.N_EXT_ATTR2
2668 ,pl_new.N_EXT_ATTR3 - pl_old.N_EXT_ATTR3
2669 ,pl_new.N_EXT_ATTR4 - pl_old.N_EXT_ATTR4
2670 ,pl_new.N_EXT_ATTR5 - pl_old.N_EXT_ATTR5
2671 ,pl_new.N_EXT_ATTR6 - pl_old.N_EXT_ATTR6
2672 ,pl_new.N_EXT_ATTR7 - pl_old.N_EXT_ATTR7
2673 ,pl_new.N_EXT_ATTR8 - pl_old.N_EXT_ATTR8
2674 ,pl_new.N_EXT_ATTR9 - pl_old.N_EXT_ATTR9
2675 ,pl_new.N_EXT_ATTR10 - pl_old.N_EXT_ATTR10
2676 ,pl_new.N_EXT_ATTR11 - pl_old.N_EXT_ATTR11
2677 ,pl_new.N_EXT_ATTR12 - pl_old.N_EXT_ATTR12
2678 ,pl_new.N_EXT_ATTR13 - pl_old.N_EXT_ATTR13
2679 ,pl_new.N_EXT_ATTR14 - pl_old.N_EXT_ATTR14
2680 ,pl_new.N_EXT_ATTR15 - pl_old.N_EXT_ATTR15
2681 ,pl_new.N_EXT_ATTR16 - pl_old.N_EXT_ATTR16
2682 ,pl_new.N_EXT_ATTR17 - pl_old.N_EXT_ATTR17
2683 ,pl_new.N_EXT_ATTR18 - pl_old.N_EXT_ATTR18
2684 ,pl_new.N_EXT_ATTR19 - pl_old.N_EXT_ATTR19
2685 ,pl_new.N_EXT_ATTR20 - pl_old.N_EXT_ATTR20
2686 ,pl_new.UOM_EXT_ATTR1
2687 ,pl_new.UOM_EXT_ATTR2
2688 ,pl_new.UOM_EXT_ATTR3
2689 ,pl_new.UOM_EXT_ATTR4
2690 ,pl_new.UOM_EXT_ATTR5
2691 ,pl_new.UOM_EXT_ATTR6
2692 ,pl_new.UOM_EXT_ATTR7
2693 ,pl_new.UOM_EXT_ATTR8
2694 ,pl_new.UOM_EXT_ATTR9
2695 ,pl_new.UOM_EXT_ATTR10
2696 ,pl_new.UOM_EXT_ATTR11
2697 ,pl_new.UOM_EXT_ATTR12
2698 ,pl_new.UOM_EXT_ATTR13
2699 ,pl_new.UOM_EXT_ATTR14
2700 ,pl_new.UOM_EXT_ATTR15
2701 ,pl_new.UOM_EXT_ATTR16
2702 ,pl_new.UOM_EXT_ATTR17
2703 ,pl_new.UOM_EXT_ATTR18
2704 ,pl_new.UOM_EXT_ATTR19
2705 ,pl_new.UOM_EXT_ATTR20
2706 ,pl_new.D_EXT_ATTR1
2707 ,pl_new.D_EXT_ATTR2
2708 ,pl_new.D_EXT_ATTR3
2709 ,pl_new.D_EXT_ATTR4
2710 ,pl_new.D_EXT_ATTR5
2711 ,pl_new.D_EXT_ATTR6
2712 ,pl_new.D_EXT_ATTR7
2713 ,pl_new.D_EXT_ATTR8
2714 ,pl_new.D_EXT_ATTR9
2715 ,pl_new.D_EXT_ATTR10
2716 ,pl_old.UDA_TEMPLATE_ID
2717 ,l_draft_id_tbl(i) * -1
2718 FROM PO_LINES_ALL_EXT_B pl_old,PO_LINES_ALL_EXT_B pl_new
2719 WHERE pl_old.po_line_id = pl_new.po_line_id
2720 AND pl_old.ATTR_GROUP_ID = pl_new.ATTR_GROUP_ID
2721 AND pl_old.data_level_id = pl_new.data_level_id
2722 AND pl_old.ATTR_GROUP_ID = l_attr_group_id_tbl(i)
2723 AND pl_old.po_line_id = l_po_lines_tbl(i)
2724 AND pl_old.data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE')
2725 AND pl_new.draft_id = l_draft_id_tbl(i)
2726 AND pl_old.draft_id = -1);
2727
2728 ELSE
2729
2730 -- record doesnt exist in po_headers_all_ext_b table, so insert new record
2731 --inset delta archive record
2732 INSERT INTO
2733 po_lines_all_ext_b
2734 (EXTENSION_ID
2735 ,ATTR_GROUP_ID
2736 ,PO_LINE_ID
2737 ,DATA_LEVEL_ID
2738 ,PK1_VALUE
2739 ,PK2_VALUE
2740 ,PK3_VALUE
2741 ,PK4_VALUE
2742 ,PK5_VALUE
2743 ,LAST_UPDATE_DATE
2744 ,LAST_UPDATED_BY
2745 ,LAST_UPDATE_LOGIN
2746 ,CREATED_BY
2747 ,CREATION_DATE
2748 ,C_EXT_ATTR1
2749 ,C_EXT_ATTR2
2750 ,C_EXT_ATTR3
2751 ,C_EXT_ATTR4
2752 ,C_EXT_ATTR5
2753 ,C_EXT_ATTR6
2754 ,C_EXT_ATTR7
2755 ,C_EXT_ATTR8
2756 ,C_EXT_ATTR9
2757 ,C_EXT_ATTR10
2758 ,C_EXT_ATTR11
2759 ,C_EXT_ATTR12
2760 ,C_EXT_ATTR13
2761 ,C_EXT_ATTR14
2762 ,C_EXT_ATTR15
2763 ,C_EXT_ATTR16
2764 ,C_EXT_ATTR17
2765 ,C_EXT_ATTR18
2766 ,C_EXT_ATTR19
2767 ,C_EXT_ATTR20
2768 ,C_EXT_ATTR21
2769 ,C_EXT_ATTR22
2770 ,C_EXT_ATTR23
2771 ,C_EXT_ATTR24
2772 ,C_EXT_ATTR25
2773 ,C_EXT_ATTR26
2774 ,C_EXT_ATTR27
2775 ,C_EXT_ATTR28
2776 ,C_EXT_ATTR29
2777 ,C_EXT_ATTR30
2778 ,C_EXT_ATTR31
2779 ,C_EXT_ATTR32
2780 ,C_EXT_ATTR33
2781 ,C_EXT_ATTR34
2782 ,C_EXT_ATTR35
2783 ,C_EXT_ATTR36
2784 ,C_EXT_ATTR37
2785 ,C_EXT_ATTR38
2786 ,C_EXT_ATTR39
2787 ,C_EXT_ATTR40
2788 ,N_EXT_ATTR1
2789 ,N_EXT_ATTR2
2790 ,N_EXT_ATTR3
2791 ,N_EXT_ATTR4
2792 ,N_EXT_ATTR5
2793 ,N_EXT_ATTR6
2794 ,N_EXT_ATTR7
2795 ,N_EXT_ATTR8
2796 ,N_EXT_ATTR9
2797 ,N_EXT_ATTR10
2798 ,N_EXT_ATTR11
2799 ,N_EXT_ATTR12
2800 ,N_EXT_ATTR13
2801 ,N_EXT_ATTR14
2802 ,N_EXT_ATTR15
2803 ,N_EXT_ATTR16
2804 ,N_EXT_ATTR17
2805 ,N_EXT_ATTR18
2806 ,N_EXT_ATTR19
2807 ,N_EXT_ATTR20
2808 ,UOM_EXT_ATTR1
2809 ,UOM_EXT_ATTR2
2810 ,UOM_EXT_ATTR3
2811 ,UOM_EXT_ATTR4
2812 ,UOM_EXT_ATTR5
2813 ,UOM_EXT_ATTR6
2814 ,UOM_EXT_ATTR7
2815 ,UOM_EXT_ATTR8
2816 ,UOM_EXT_ATTR9
2817 ,UOM_EXT_ATTR10
2818 ,UOM_EXT_ATTR11
2819 ,UOM_EXT_ATTR12
2820 ,UOM_EXT_ATTR13
2821 ,UOM_EXT_ATTR14
2822 ,UOM_EXT_ATTR15
2823 ,UOM_EXT_ATTR16
2824 ,UOM_EXT_ATTR17
2825 ,UOM_EXT_ATTR18
2826 ,UOM_EXT_ATTR19
2827 ,UOM_EXT_ATTR20
2828 ,D_EXT_ATTR1
2829 ,D_EXT_ATTR2
2830 ,D_EXT_ATTR3
2831 ,D_EXT_ATTR4
2832 ,D_EXT_ATTR5
2833 ,D_EXT_ATTR6
2834 ,D_EXT_ATTR7
2835 ,D_EXT_ATTR8
2836 ,D_EXT_ATTR9
2837 ,D_EXT_ATTR10
2838 ,UDA_TEMPLATE_ID
2839 ,DRAFT_ID
2840 )
2841 SELECT
2842 EGO_EXTFWK_S.NEXTVAL
2843 ,pl_old.ATTR_GROUP_ID
2844 ,(pl_old.PO_LINE_ID * -1 )
2845 ,(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE')
2846 ,l_draft_id_tbl(i) * -1
2847 ,pl_new.PK2_VALUE
2848 ,pl_new.PK3_VALUE
2849 ,pl_new.PK4_VALUE
2850 ,pl_new.PK5_VALUE
2851 ,pl_new.LAST_UPDATE_DATE
2852 ,pl_new.LAST_UPDATED_BY
2853 ,pl_new.LAST_UPDATE_LOGIN
2854 ,pl_new.CREATED_BY
2855 ,pl_new.CREATION_DATE
2856 ,pl_new.C_EXT_ATTR1
2857 ,pl_new.C_EXT_ATTR2
2858 ,pl_new.C_EXT_ATTR3
2859 ,pl_new.C_EXT_ATTR4
2860 ,pl_new.C_EXT_ATTR5
2861 ,pl_new.C_EXT_ATTR6
2862 ,pl_new.C_EXT_ATTR7
2863 ,pl_new.C_EXT_ATTR8
2864 ,pl_new.C_EXT_ATTR9
2865 ,pl_new.C_EXT_ATTR10
2866 ,pl_new.C_EXT_ATTR11
2867 ,pl_new.C_EXT_ATTR12
2868 ,pl_new.C_EXT_ATTR13
2869 ,pl_new.C_EXT_ATTR14
2870 ,pl_new.C_EXT_ATTR15
2871 ,pl_new.C_EXT_ATTR16
2872 ,pl_new.C_EXT_ATTR17
2873 ,pl_new.C_EXT_ATTR18
2874 ,pl_new.C_EXT_ATTR19
2875 ,pl_new.C_EXT_ATTR20
2876 ,pl_new.C_EXT_ATTR21
2877 ,pl_new.C_EXT_ATTR22
2878 ,pl_new.C_EXT_ATTR23
2879 ,pl_new.C_EXT_ATTR24
2880 ,pl_new.C_EXT_ATTR25
2881 ,pl_new.C_EXT_ATTR26
2882 ,pl_new.C_EXT_ATTR27
2883 ,pl_new.C_EXT_ATTR28
2884 ,pl_new.C_EXT_ATTR29
2885 ,pl_new.C_EXT_ATTR30
2886 ,pl_new.C_EXT_ATTR31
2887 ,pl_new.C_EXT_ATTR32
2888 ,pl_new.C_EXT_ATTR33
2889 ,pl_new.C_EXT_ATTR34
2890 ,pl_new.C_EXT_ATTR35
2891 ,pl_new.C_EXT_ATTR36
2892 ,pl_new.C_EXT_ATTR37
2893 ,pl_new.C_EXT_ATTR38
2894 ,pl_new.C_EXT_ATTR39
2895 ,pl_new.C_EXT_ATTR40
2896 ,pl_new.N_EXT_ATTR1 - pl_old.N_EXT_ATTR1
2897 ,pl_new.N_EXT_ATTR2 - pl_old.N_EXT_ATTR2
2898 ,pl_new.N_EXT_ATTR3 - pl_old.N_EXT_ATTR3
2899 ,pl_new.N_EXT_ATTR4 - pl_old.N_EXT_ATTR4
2900 ,pl_new.N_EXT_ATTR5 - pl_old.N_EXT_ATTR5
2901 ,pl_new.N_EXT_ATTR6 - pl_old.N_EXT_ATTR6
2902 ,pl_new.N_EXT_ATTR7 - pl_old.N_EXT_ATTR7
2903 ,pl_new.N_EXT_ATTR8 - pl_old.N_EXT_ATTR8
2904 ,pl_new.N_EXT_ATTR9 - pl_old.N_EXT_ATTR9
2905 ,pl_new.N_EXT_ATTR10 - pl_old.N_EXT_ATTR10
2906 ,pl_new.N_EXT_ATTR11 - pl_old.N_EXT_ATTR11
2907 ,pl_new.N_EXT_ATTR12 - pl_old.N_EXT_ATTR12
2908 ,pl_new.N_EXT_ATTR13 - pl_old.N_EXT_ATTR13
2909 ,pl_new.N_EXT_ATTR14 - pl_old.N_EXT_ATTR14
2910 ,pl_new.N_EXT_ATTR15 - pl_old.N_EXT_ATTR15
2911 ,pl_new.N_EXT_ATTR16 - pl_old.N_EXT_ATTR16
2912 ,pl_new.N_EXT_ATTR17 - pl_old.N_EXT_ATTR17
2913 ,pl_new.N_EXT_ATTR18 - pl_old.N_EXT_ATTR18
2914 ,pl_new.N_EXT_ATTR19 - pl_old.N_EXT_ATTR19
2915 ,pl_new.N_EXT_ATTR20 - pl_old.N_EXT_ATTR20
2916 ,pl_new.UOM_EXT_ATTR1
2917 ,pl_new.UOM_EXT_ATTR2
2918 ,pl_new.UOM_EXT_ATTR3
2919 ,pl_new.UOM_EXT_ATTR4
2920 ,pl_new.UOM_EXT_ATTR5
2921 ,pl_new.UOM_EXT_ATTR6
2922 ,pl_new.UOM_EXT_ATTR7
2923 ,pl_new.UOM_EXT_ATTR8
2924 ,pl_new.UOM_EXT_ATTR9
2925 ,pl_new.UOM_EXT_ATTR10
2926 ,pl_new.UOM_EXT_ATTR11
2927 ,pl_new.UOM_EXT_ATTR12
2928 ,pl_new.UOM_EXT_ATTR13
2929 ,pl_new.UOM_EXT_ATTR14
2930 ,pl_new.UOM_EXT_ATTR15
2931 ,pl_new.UOM_EXT_ATTR16
2932 ,pl_new.UOM_EXT_ATTR17
2933 ,pl_new.UOM_EXT_ATTR18
2934 ,pl_new.UOM_EXT_ATTR19
2935 ,pl_new.UOM_EXT_ATTR20
2936 ,pl_new.D_EXT_ATTR1
2937 ,pl_new.D_EXT_ATTR2
2938 ,pl_new.D_EXT_ATTR3
2939 ,pl_new.D_EXT_ATTR4
2940 ,pl_new.D_EXT_ATTR5
2941 ,pl_new.D_EXT_ATTR6
2942 ,pl_new.D_EXT_ATTR7
2943 ,pl_new.D_EXT_ATTR8
2944 ,pl_new.D_EXT_ATTR9
2945 ,pl_new.D_EXT_ATTR10
2946 ,pl_old.UDA_TEMPLATE_ID
2947 ,l_draft_id_tbl(i) * -1
2948 FROM PO_LINES_ALL_EXT_B pl_old,PO_LINES_ALL_EXT_B pl_new
2949 WHERE pl_old.po_line_id = pl_new.po_line_id
2950 AND pl_old.ATTR_GROUP_ID = pl_new.ATTR_GROUP_ID
2951 AND pl_old.data_level_id = pl_new.data_level_id
2952 AND pl_old.ATTR_GROUP_ID = l_attr_group_id_tbl(i)
2953 AND pl_old.po_line_id = l_po_lines_tbl(i)
2954 AND pl_old.data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE')
2955 AND pl_new.draft_id = l_draft_id_tbl(i)
2956 AND pl_old.draft_id = -1;
2957
2958 --insert old archive record
2959 INSERT INTO
2960 po_lines_all_ext_b
2961 (EXTENSION_ID
2962 ,ATTR_GROUP_ID
2963 ,PO_LINE_ID
2964 ,DATA_LEVEL_ID
2965 ,PK1_VALUE
2966 ,PK2_VALUE
2967 ,PK3_VALUE
2968 ,PK4_VALUE
2969 ,PK5_VALUE
2970 ,LAST_UPDATE_DATE
2971 ,LAST_UPDATED_BY
2972 ,LAST_UPDATE_LOGIN
2973 ,CREATED_BY
2974 ,CREATION_DATE
2975 ,C_EXT_ATTR1
2976 ,C_EXT_ATTR2
2977 ,C_EXT_ATTR3
2978 ,C_EXT_ATTR4
2979 ,C_EXT_ATTR5
2980 ,C_EXT_ATTR6
2981 ,C_EXT_ATTR7
2982 ,C_EXT_ATTR8
2983 ,C_EXT_ATTR9
2984 ,C_EXT_ATTR10
2985 ,C_EXT_ATTR11
2986 ,C_EXT_ATTR12
2987 ,C_EXT_ATTR13
2988 ,C_EXT_ATTR14
2989 ,C_EXT_ATTR15
2990 ,C_EXT_ATTR16
2991 ,C_EXT_ATTR17
2992 ,C_EXT_ATTR18
2993 ,C_EXT_ATTR19
2994 ,C_EXT_ATTR20
2995 ,C_EXT_ATTR21
2996 ,C_EXT_ATTR22
2997 ,C_EXT_ATTR23
2998 ,C_EXT_ATTR24
2999 ,C_EXT_ATTR25
3000 ,C_EXT_ATTR26
3001 ,C_EXT_ATTR27
3002 ,C_EXT_ATTR28
3003 ,C_EXT_ATTR29
3004 ,C_EXT_ATTR30
3005 ,C_EXT_ATTR31
3006 ,C_EXT_ATTR32
3007 ,C_EXT_ATTR33
3008 ,C_EXT_ATTR34
3009 ,C_EXT_ATTR35
3010 ,C_EXT_ATTR36
3011 ,C_EXT_ATTR37
3012 ,C_EXT_ATTR38
3013 ,C_EXT_ATTR39
3014 ,C_EXT_ATTR40
3015 ,N_EXT_ATTR1
3016 ,N_EXT_ATTR2
3017 ,N_EXT_ATTR3
3018 ,N_EXT_ATTR4
3019 ,N_EXT_ATTR5
3020 ,N_EXT_ATTR6
3021 ,N_EXT_ATTR7
3022 ,N_EXT_ATTR8
3023 ,N_EXT_ATTR9
3024 ,N_EXT_ATTR10
3025 ,N_EXT_ATTR11
3026 ,N_EXT_ATTR12
3027 ,N_EXT_ATTR13
3028 ,N_EXT_ATTR14
3029 ,N_EXT_ATTR15
3030 ,N_EXT_ATTR16
3031 ,N_EXT_ATTR17
3032 ,N_EXT_ATTR18
3033 ,N_EXT_ATTR19
3034 ,N_EXT_ATTR20
3035 ,UOM_EXT_ATTR1
3036 ,UOM_EXT_ATTR2
3037 ,UOM_EXT_ATTR3
3038 ,UOM_EXT_ATTR4
3039 ,UOM_EXT_ATTR5
3040 ,UOM_EXT_ATTR6
3041 ,UOM_EXT_ATTR7
3042 ,UOM_EXT_ATTR8
3043 ,UOM_EXT_ATTR9
3044 ,UOM_EXT_ATTR10
3045 ,UOM_EXT_ATTR11
3046 ,UOM_EXT_ATTR12
3047 ,UOM_EXT_ATTR13
3048 ,UOM_EXT_ATTR14
3049 ,UOM_EXT_ATTR15
3050 ,UOM_EXT_ATTR16
3051 ,UOM_EXT_ATTR17
3052 ,UOM_EXT_ATTR18
3053 ,UOM_EXT_ATTR19
3054 ,UOM_EXT_ATTR20
3055 ,D_EXT_ATTR1
3056 ,D_EXT_ATTR2
3057 ,D_EXT_ATTR3
3058 ,D_EXT_ATTR4
3059 ,D_EXT_ATTR5
3060 ,D_EXT_ATTR6
3061 ,D_EXT_ATTR7
3062 ,D_EXT_ATTR8
3063 ,D_EXT_ATTR9
3064 ,D_EXT_ATTR10
3065 ,UDA_TEMPLATE_ID
3066 ,DRAFT_ID
3067 )
3068 SELECT
3069 EGO_EXTFWK_S.NEXTVAL
3070 ,pl_old.ATTR_GROUP_ID
3071 ,pl_old.PO_LINE_ID
3072 ,(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_OLD_ARCHIVE')
3073 ,l_draft_id_tbl(i)
3074 ,pl_old.PK2_VALUE
3075 ,pl_old.PK3_VALUE
3076 ,pl_old.PK4_VALUE
3077 ,pl_old.PK5_VALUE
3078 ,pl_old.LAST_UPDATE_DATE
3079 ,pl_old.LAST_UPDATED_BY
3080 ,pl_old.LAST_UPDATE_LOGIN
3081 ,pl_old.CREATED_BY
3082 ,pl_old.CREATION_DATE
3083 ,pl_old.C_EXT_ATTR1
3084 ,pl_old.C_EXT_ATTR2
3085 ,pl_old.C_EXT_ATTR3
3086 ,pl_old.C_EXT_ATTR4
3087 ,pl_old.C_EXT_ATTR5
3088 ,pl_old.C_EXT_ATTR6
3089 ,pl_old.C_EXT_ATTR7
3090 ,pl_old.C_EXT_ATTR8
3091 ,pl_old.C_EXT_ATTR9
3092 ,pl_old.C_EXT_ATTR10
3093 ,pl_old.C_EXT_ATTR11
3094 ,pl_old.C_EXT_ATTR12
3095 ,pl_old.C_EXT_ATTR13
3096 ,pl_old.C_EXT_ATTR14
3097 ,pl_old.C_EXT_ATTR15
3098 ,pl_old.C_EXT_ATTR16
3099 ,pl_old.C_EXT_ATTR17
3100 ,pl_old.C_EXT_ATTR18
3101 ,pl_old.C_EXT_ATTR19
3102 ,pl_old.C_EXT_ATTR20
3103 ,pl_old.C_EXT_ATTR21
3104 ,pl_old.C_EXT_ATTR22
3105 ,pl_old.C_EXT_ATTR23
3106 ,pl_old.C_EXT_ATTR24
3107 ,pl_old.C_EXT_ATTR25
3108 ,pl_old.C_EXT_ATTR26
3109 ,pl_old.C_EXT_ATTR27
3110 ,pl_old.C_EXT_ATTR28
3111 ,pl_old.C_EXT_ATTR29
3112 ,pl_old.C_EXT_ATTR30
3113 ,pl_old.C_EXT_ATTR31
3114 ,pl_old.C_EXT_ATTR32
3115 ,pl_old.C_EXT_ATTR33
3116 ,pl_old.C_EXT_ATTR34
3117 ,pl_old.C_EXT_ATTR35
3118 ,pl_old.C_EXT_ATTR36
3119 ,pl_old.C_EXT_ATTR37
3120 ,pl_old.C_EXT_ATTR38
3121 ,pl_old.C_EXT_ATTR39
3122 ,pl_old.C_EXT_ATTR40
3123 ,pl_old.N_EXT_ATTR1
3124 ,pl_old.N_EXT_ATTR2
3125 ,pl_old.N_EXT_ATTR3
3126 ,pl_old.N_EXT_ATTR4
3127 ,pl_old.N_EXT_ATTR5
3128 ,pl_old.N_EXT_ATTR6
3129 ,pl_old.N_EXT_ATTR7
3130 ,pl_old.N_EXT_ATTR8
3131 ,pl_old.N_EXT_ATTR9
3132 ,pl_old.N_EXT_ATTR10
3133 ,pl_old.N_EXT_ATTR11
3134 ,pl_old.N_EXT_ATTR12
3135 ,pl_old.N_EXT_ATTR13
3136 ,pl_old.N_EXT_ATTR14
3137 ,pl_old.N_EXT_ATTR15
3138 ,pl_old.N_EXT_ATTR16
3139 ,pl_old.N_EXT_ATTR17
3140 ,pl_old.N_EXT_ATTR18
3141 ,pl_old.N_EXT_ATTR19
3142 ,pl_old.N_EXT_ATTR20
3143 ,pl_old.UOM_EXT_ATTR1
3144 ,pl_old.UOM_EXT_ATTR2
3145 ,pl_old.UOM_EXT_ATTR3
3146 ,pl_old.UOM_EXT_ATTR4
3147 ,pl_old.UOM_EXT_ATTR5
3148 ,pl_old.UOM_EXT_ATTR6
3149 ,pl_old.UOM_EXT_ATTR7
3150 ,pl_old.UOM_EXT_ATTR8
3151 ,pl_old.UOM_EXT_ATTR9
3152 ,pl_old.UOM_EXT_ATTR10
3153 ,pl_old.UOM_EXT_ATTR11
3154 ,pl_old.UOM_EXT_ATTR12
3155 ,pl_old.UOM_EXT_ATTR13
3156 ,pl_old.UOM_EXT_ATTR14
3157 ,pl_old.UOM_EXT_ATTR15
3158 ,pl_old.UOM_EXT_ATTR16
3159 ,pl_old.UOM_EXT_ATTR17
3160 ,pl_old.UOM_EXT_ATTR18
3161 ,pl_old.UOM_EXT_ATTR19
3162 ,pl_old.UOM_EXT_ATTR20
3163 ,pl_old.D_EXT_ATTR1
3164 ,pl_old.D_EXT_ATTR2
3165 ,pl_old.D_EXT_ATTR3
3166 ,pl_old.D_EXT_ATTR4
3167 ,pl_old.D_EXT_ATTR5
3168 ,pl_old.D_EXT_ATTR6
3169 ,pl_old.D_EXT_ATTR7
3170 ,pl_old.D_EXT_ATTR8
3171 ,pl_old.D_EXT_ATTR9
3172 ,pl_old.D_EXT_ATTR10
3173 ,pl_old.UDA_TEMPLATE_ID
3174 , -1
3175 FROM PO_LINES_ALL_EXT_B pl_old,PO_LINES_ALL_EXT_B pl_new
3176 WHERE pl_old.po_line_id = pl_new.po_line_id
3177 AND pl_old.ATTR_GROUP_ID = pl_new.ATTR_GROUP_ID
3178 AND pl_old.data_level_id = pl_new.data_level_id
3179 AND pl_old.ATTR_GROUP_ID = l_attr_group_id_tbl(i)
3180 AND pl_old.po_line_id = l_po_lines_tbl(i)
3181 AND pl_old.data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE')
3182 AND pl_new.draft_id = l_draft_id_tbl(i)
3183 AND pl_old.draft_id = -1;
3184
3185 END IF;
3186
3187 -- Inserting/Updating records in po_lines_all_ext_b table
3188 FOR j IN 1..l_language_code_tbl.Count()
3189 LOOP
3190
3191 --Update delta archive record in po_lines_all_ext_tl
3192 IF l_records_exist = 'Y'
3193 THEN
3194 UPDATE po_lines_all_ext_tl
3195 SET ( EXTENSION_ID,
3196 ATTR_GROUP_ID,
3197 PO_LINE_ID,
3198 DATA_LEVEL_ID,
3199 pk1_value,
3200 SOURCE_LANG,
3201 LANGUAGE,
3202 LAST_UPDATE_DATE,
3203 LAST_UPDATED_BY,
3204 LAST_UPDATE_LOGIN,
3205 CREATED_BY,
3206 CREATION_DATE,
3207 UDA_TEMPLATE_ID,
3208 DRAFT_ID )
3209 =
3210 ( SELECT
3211
3212 EXTENSION_ID,
3213 attr_group_id,
3214 po_line_id,
3215 (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'),
3216 l_po_lines_tbl(i)*-1 ,
3217 USERENV('LANG'),
3218 l_language_code_tbl(j),
3219 SYSDATE,
3220 0,
3221 0,
3222 0,
3223 SYSDATE,
3224 UDA_TEMPLATE_ID,
3225 draft_id
3226 FROM po_lines_all_ext_b
3227 WHERE ATTR_GROUP_ID = l_attr_group_id_tbl(i)
3228 AND PO_LINE_ID = -1* l_po_lines_tbl(i)
3229 AND draft_id = l_draft_id_tbl(i) * -1
3230 AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'));
3231
3232 ELSE
3233
3234 --insert into tl table
3235 INSERT INTO po_lines_all_ext_tl
3236 (
3237 EXTENSION_ID,
3238 ATTR_GROUP_ID,
3239 PO_LINE_ID,
3240 DATA_LEVEL_ID,
3241 pk1_value,
3242 SOURCE_LANG,
3243 LANGUAGE,
3244 LAST_UPDATE_DATE,
3245 LAST_UPDATED_BY,
3246 LAST_UPDATE_LOGIN,
3247 CREATED_BY,
3248 CREATION_DATE,
3249 UDA_TEMPLATE_ID,
3250 DRAFT_ID
3251 )
3252 SELECT
3253 EXTENSION_ID,
3254 attr_group_id,
3255 po_line_id,
3256 (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'),
3257 l_po_lines_tbl(i)*-1 ,
3258 USERENV('LANG'),
3259 l_language_code_tbl(j),
3260 SYSDATE,
3261 0,
3262 0,
3263 0,
3264 SYSDATE,
3265 UDA_TEMPLATE_ID,
3266 draft_id
3267 FROM po_lines_all_ext_b
3268 WHERE ATTR_GROUP_ID = l_attr_group_id_tbl(i)
3269 AND PO_LINE_ID = -1* l_po_lines_tbl(i)
3270 AND draft_id = l_draft_id_tbl(i) * -1
3271 AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE');
3272
3273 --Insert old arcive record in tl table
3274 INSERT INTO po_lines_all_ext_tl
3275 (
3276 EXTENSION_ID,
3277 ATTR_GROUP_ID,
3278 PO_LINE_ID,
3279 DATA_LEVEL_ID,
3280 pk1_value,
3281 SOURCE_LANG,
3282 LANGUAGE,
3283 LAST_UPDATE_DATE,
3284 LAST_UPDATED_BY,
3285 LAST_UPDATE_LOGIN,
3286 CREATED_BY,
3287 CREATION_DATE,
3288 UDA_TEMPLATE_ID,
3289 DRAFT_ID
3290 )
3291 SELECT
3292
3293 EXTENSION_ID,
3294 attr_group_id,
3295 po_line_id,
3296 (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_OLD_ARCHIVE'),
3297 l_po_lines_tbl(i),
3298 USERENV('LANG'),
3299 l_language_code_tbl(j),
3300 SYSDATE,
3301 0,
3302 0,
3303 0,
3304 SYSDATE,
3305 UDA_TEMPLATE_ID,
3306 draft_id
3307 FROM po_lines_all_ext_b
3308 WHERE ATTR_GROUP_ID = l_attr_group_id_tbl(i)
3309 AND PO_LINE_ID = l_po_lines_tbl(i)
3310 AND draft_id = -1
3311 AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_OLD_ARCHIVE');
3312
3313 IF (PO_LOG.d_stmt) THEN
3314 PO_LOG.stmt(d_module, d_position, 'Delta and old records inserted ');
3315 END IF;
3316 END IF;
3317 END LOOP;
3318 END IF;
3319 END LOOP;
3320
3321 EXCEPTION
3322 WHEN OTHERS THEN
3323 PO_MESSAGE_S.add_exc_msg
3324 ( p_pkg_name => d_pkg_name,
3325 p_procedure_name => d_api_name || '.' || d_position
3326 );
3327 RAISE;
3328
3329 END TRANSFER_UDA_DATA;
3330
3331 -----------------------------------------------------------------------
3332 --Start of Comments
3333 --Name: validate_uda_req_attributes
3334 --Pre-reqs: None
3335 --Modifies:
3336 --Locks:
3337 --Function:
3338 -- This FUNCTION validates the manadatory UDA attributes IN a document
3339 -- This is called in MOD API flow
3340 --Parameters:
3341 --IN:p_transaction_id IN NUMBER
3342 --IN OUT:
3343 --OUT:
3344 --Notes:
3345 --Testing:
3346 --End of Comments
3347 ------------------------------------------------------------------------
3348
3349
3350 FUNCTION validate_uda_req_attributes(p_transaction_id IN NUMBER ) RETURN VARCHAR2
3351 IS
3352
3353 l_is_valid VARCHAR2(1) :='Y';
3354 l_attr_grp_name_tbl po_tbl_varchar100;
3355 l_attr_group_typ_tbl po_tbl_varchar30;
3356 l_pk1_value_tbl po_tbl_number;
3357 d_api_name CONSTANT VARCHAR2(30) := 'validate_uda_req_attributes';
3358 d_pkg_name CONSTANT VARCHAR2(30) := PO_LOG.get_package_base('PO_UDA_IMPORT_PKG');
3359 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name || '.';
3360 d_position NUMBER;
3361
3362 BEGIN
3363
3364 l_attr_group_typ_tbl := NEW po_tbl_varchar30();
3365 l_attr_group_typ_tbl.extend(3);
3366 l_attr_group_typ_tbl(1):= 'PO_HEADER_EXT_ATTRS';
3367 l_attr_group_typ_tbl(2):= 'PO_LINE_EXT_ATTRS';
3368 l_attr_group_typ_tbl(3):= 'PO_SHIPMENT_EXT_ATTRS';
3369
3370 SELECT DISTINCT pk1_value
3371 BULK COLLECT INTO l_pk1_value_tbl
3372 FROM po_uda_interface
3373 WHERE transaction_id = p_transaction_id;
3374
3375 FOR i IN 1..l_pk1_value_tbl.Count
3376 LOOP
3377 FOR j IN 1..l_attr_group_typ_tbl.Count
3378 LOOP
3379
3380 SELECT ATTR_GROUP_NAME
3381 BULK COLLECT INTO l_attr_grp_name_tbl
3382 FROM (SELECT DISTINCT ATTR_GROUP_NAME
3383 FROM po_uda_interface
3384 WHERE ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
3385 AND transaction_id = p_transaction_id
3386 AND pk1_value = l_pk1_value_tbl(i));
3387
3388 FOR k IN 1..l_attr_grp_name_tbl.Count
3389 LOOP
3390
3391 SELECT 'N'
3392 INTO l_is_valid
3393 FROM dual
3394 WHERE EXISTS ( SELECT attr_name
3395 FROM ego_attrs_v
3396 WHERE ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
3397 AND ATTR_GROUP_NAME = l_attr_grp_name_tbl(k)
3398 GROUP BY attr_group_name, attr_name, required_flag
3399 HAVING required_flag = 'Y'
3400
3401 MINUS
3402
3403 SELECT attr_name
3404 FROM po_uda_interface
3405 WHERE transaction_id = p_transaction_id
3406 AND ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
3407 AND ATTR_GROUP_NAME = l_attr_grp_name_tbl(k)
3408 AND pk1_value = l_pk1_value_tbl(i)
3409 ) ;
3410
3411 d_position := 10;
3412 IF PO_LOG.d_stmt THEN
3413 PO_LOG.stmt(d_module,d_position,'l_is_valid ',l_is_valid);
3414 PO_LOG.stmt(d_module,d_position,'l_pk1_value_tbl ',l_pk1_value_tbl(i));
3415 END IF;
3416
3417 IF l_is_valid = 'N' THEN
3418 RETURN l_is_valid;
3419 END IF ;
3420
3421 END LOOP;
3422 END LOOP;
3423 END LOOP;
3424
3425
3426 EXCEPTION
3427 WHEN No_Data_Found THEN
3428 NULL;
3429
3430 IF PO_LOG.d_stmt THEN
3431 PO_LOG.stmt(d_module,d_position,'l_is_valid ',l_is_valid);
3432 END IF;
3433
3434 RETURN l_is_valid;
3435 END validate_uda_req_attributes;
3436
3437 END PO_UDA_IMPORT_PKG;