[Home] [Help]
PACKAGE BODY: APPS.PO_UDA_DATA_UTIL
Source
1 PACKAGE BODY PO_UDA_DATA_UTIL AS
2 /* $Header: PO_UDA_DATA_UTIL.plb 120.25.12020000.9 2013/04/18 11:48:43 inagdeo ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_UDA_DATA_UTIL');
6
7 TYPE varchar_tbl is table of varchar2(100) index by binary_integer;
8 user_col_name_tbl varchar_tbl;
9 appl_col_name_tbl varchar_tbl;
10
11
12 PROCEDURE CHECK_ATTR_OR_PAGE_ID_VALID
13 (
14 p_template_id IN NUMBER
15 ,p_attr_grp_or_page_id IN NUMBER
16 ,p_attr_grp_or_page_type IN VARCHAR2
17 ,x_usage_code OUT NOCOPY VARCHAR2
18 ,x_found_status OUT NOCOPY VARCHAR2
19 )
20 IS
21 l_count NUMBER := 0;
22
23 d_api_name CONSTANT VARCHAR2(30) := 'ATTR_GRP OR PAGE_ID is Valid';
24 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
25 d_progress NUMBER;
26
27 BEGIN
28
29 d_progress := 10;
30
31 IF (PO_LOG.d_proc) THEN
32 PO_LOG.proc_begin(d_module);
33 PO_LOG.proc_begin(d_module, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
34 END IF;
35
36 -- Contition for Attribute Group Id
37 IF p_attr_grp_or_page_type = 'ATTRIBUTE_GROUP_ID' THEN
38
39 d_progress := 20;
40
41 BEGIN
42
43 -- Checking if there exists a template usage for the specific template and attribute group id
44 SELECT 1
45 INTO l_count
46 FROM po_uda_ag_template_usages
47 WHERE TEMPLATE_ID = p_template_id
48 AND ATTRIBUTE_GROUP_ID = p_attr_grp_or_page_id
49 AND ROWNUM < 2;
50
51 d_progress := 30;
52
53 IF PO_LOG.d_stmt THEN
54 PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
55 END IF;
56
57 IF l_count >= 1 THEN
58
59 -- Collecting the usage code if there exists a template usage for the specific template and attribute group id
60 SELECT DISTINCT ATTRIBUTE_CATEGORY
61 INTO x_usage_code
62 FROM po_uda_ag_template_usages
63 WHERE TEMPLATE_ID = p_template_id
64 AND ATTRIBUTE_GROUP_ID = p_attr_grp_or_page_id;
65 x_found_status := 'true';
66
67 IF PO_LOG.d_stmt THEN
68 PO_LOG.stmt(d_module, d_progress, 'x_usage_code', x_usage_code);
69 END IF;
70
71
72 END IF;
73
74 d_progress := 40;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 d_progress := 50;
79 x_found_status := 'false';
80 IF PO_LOG.d_stmt THEN
81 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
82 END IF;
83 END;
84
85 -- Contition for Page Id
86 ELSIF p_attr_grp_or_page_type = 'PAGE_ID' THEN
87
88 d_progress := 60;
89
90 IF PO_LOG.d_stmt THEN
91 PO_LOG.stmt(d_module, d_progress, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
92 END IF;
93
94 BEGIN
95 d_progress := 70;
96
97 -- Checking if there exists a page with specific template
98 SELECT 1
99 INTO l_count
100 FROM EGO_PAGE_ENTRIES_B P,
101 po_uda_AG_templates t
102 WHERE P.PAGE_ID = p_attr_grp_or_page_id
103 AND t.TEMPLATE_ID = p_template_id
104 AND p.CLASSIFICATION_CODE = p_template_id || ''
105 AND ROWNUM < 2;
106
107 IF PO_LOG.d_stmt THEN
108 PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
109 END IF;
110
111 d_progress := 80;
112
113 IF l_count >= 1 THEN
114 x_found_status := 'true';
115 END IF;
116
117 IF PO_LOG.d_stmt THEN
118 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
119 END IF;
120 d_progress := 90;
121 EXCEPTION
122 WHEN OTHERS THEN
123 d_progress := 100;
124 x_found_status := 'false';
125 IF PO_LOG.d_stmt THEN
126 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
127 END IF;
128 END;
129
130 END IF;
131
132 d_progress := 110;
133
134 IF (PO_LOG.d_exc) THEN
135 PO_LOG.exc(d_module, d_progress, x_found_status );
136 PO_LOG.proc_end(d_module);
137 END IF;
138
139 END CHECK_ATTR_OR_PAGE_ID_VALID;
140
141
142 --------------------------------------------------------------------------------------------------------------------
143 --Start of Comments
144 --Name: UDA_DEFAULT_ATTRIBUTES
145 --Procedure:
146 -- This procedure accepts a template id and attribute group id and then cretaes EGO_USER_ATTR_ROW_TABLE
147 -- and PO_UDA_USAGE_OBJECT_ARRAY to contain the attribute group and attribute information with null values for
148 -- attributes and then run the defaulting actions (Actions with executiond code as XD and XLE)
149 --Parameters:
150 --IN:
151 -- 1. p_template_id
152 -- This variable would contain the template id for which the actions are to be defaulted
153 -- 2. p_attr_grp_id
154 -- This variable would contain the attribute group id for which the actions are to be defaulted
155 -- 3. p_pk_column_name_value_pairs
156 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
157 --IN/OUT:
158 -- 1. x_attributes_row_table
159 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
160 -- 2. x_attr_name_value_pairs
161 -- This variable of type PO_UDA_USAGE_OBJECT_ARRAY contains data of hhe usages.
162 -- 3. x_external_attr_value_pairs
163 -- This variable would contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
164 --OUT:
165 -- 1. x_return_status - Generic Out parameter having return status
166 -- 2. x_errorcode - Generic Out parameter having error code
167 -- 3. x_msg_count - Generic Out parameter having return msg Count
168 -- 4. x_msg_data - Generic Out parameter having return msg
169 --
170 --End of Comments
171 --------------------------------------------------------------------------------------------------------------------
172 PROCEDURE UDA_DEFAULT_ATTRIBUTES
173 (
174 p_template_id IN NUMBER
175 ,p_attr_grp_id IN NUMBER
176 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
177 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
178 ,x_attr_name_value_pairs OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
179 ,x_attributes_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
180 ,x_return_status OUT NOCOPY VARCHAR2
181 ,x_errorcode OUT NOCOPY NUMBER
182 ,x_msg_count OUT NOCOPY NUMBER
183 ,x_msg_data OUT NOCOPY VARCHAR2
184 )
185 IS
186 d_progress NUMBER := 0;
187 l_row_identifier NUMBER;
188 l_user_row_identifier NUMBER;
189 l_attr_group_type VARCHAR2(200);
190 l_attr_group_name VARCHAR2(200);
191 l_data_level VARCHAR2(200);
192 l_attr_name VARCHAR2(200);
193
194 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
195
196 CURSOR cur_uda_attributes
197 (
198 p_attr_grp_type VARCHAR2
199 ,p_attr_grp_name VARCHAR2
200 )IS
201 SELECT END_USER_COLUMN_NAME
202 FROM FND_DESCR_FLEX_COLUMN_USAGES
203 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
204 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
205
206 BEGIN
207 d_progress := 10;
208 SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
209 INTO l_row_identifier
210 FROM DUAL;
211
212 d_progress := 20;
213 SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
214 INTO l_attr_group_type, l_attr_group_name
215 FROM EGO_FND_DSC_FLX_CTX_EXT
216 WHERE ATTR_GROUP_ID = p_attr_grp_id;
217
218 d_progress := 30;
219 SELECT DATA_LEVEL_NAME
220 INTO l_data_level
221 FROM EGO_DATA_LEVEL_B
222 WHERE ATTR_GROUP_TYPE = l_attr_group_type
223 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
224
225 d_progress := 40;
226 x_attributes_row_table := EGO_USER_ATTR_ROW_TABLE
227 (
228 EGO_USER_ATTR_ROW_OBJ
229 (
230 l_row_identifier
231 ,p_attr_grp_id
232 ,201
233 ,l_attr_group_type
234 ,l_attr_group_name
235 ,l_data_level
236 ,NULL
237 ,NULL
238 ,NULL
239 ,NULL
240 ,NULL
241 ,'SYNC'
242 )
243 );
244
245 d_progress := 50;
246 x_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
247
248 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
249 LOOP
250 d_progress := 60;
251 FETCH cur_uda_attributes
252 INTO l_attr_name;
253 EXIT WHEN cur_uda_attributes%NOTFOUND;
254
255 d_progress := 70;
256 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
257 INTO l_user_row_identifier
258 FROM DUAL;
259
260 d_progress := 80;
261 x_attr_name_value_pairs.EXTEND(1);
262 x_attr_name_value_pairs(x_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
263 (
264 l_row_identifier
265 ,l_attr_name
266 ,NULL
267 ,NULL
268 ,NULL
269 ,NULL
270 ,NULL
271 ,l_user_row_identifier
272 );
273 d_progress := 90;
274 END LOOP;
275
276 l_pk_column_name_value_pairs := p_pk_column_name_value_pairs;
277 d_progress := 100;
278
279 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
280 (
281 p_template_id => p_template_id
282 ,p_event => 'XD'
283 ,x_external_attr_value_pairs => x_external_attr_value_pairs
284 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
285 ,x_attr_name_value_pairs => x_attr_name_value_pairs
286 ,x_attributes_row_table => x_attributes_row_table
287 ,x_return_status => x_return_status
288 ,x_errorcode => x_errorcode
289 ,x_msg_count => x_msg_count
290 ,x_msg_data => x_msg_data
291 );
292
293 d_progress := 110;
294
295 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
296 d_progress := 120;
297 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
298 (
299 p_template_id => p_template_id
300 ,p_event => 'XLE'
301 ,x_external_attr_value_pairs => x_external_attr_value_pairs
302 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
303 ,x_attr_name_value_pairs => x_attr_name_value_pairs
304 ,x_attributes_row_table => x_attributes_row_table
305 ,x_return_status => x_return_status
306 ,x_errorcode => x_errorcode
307 ,x_msg_count => x_msg_count
308 ,x_msg_data => x_msg_data
309 );
310 d_progress := 130;
311 END IF;
312 d_progress := 140;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 END UDA_DEFAULT_ATTRIBUTES;
318
319 --------------------------------------------------------------------------------------------------------------------
320 --Start of Comments
321 --Name: UDA_VALIDATE_ATTRIBUTES
322 --Procedure:
323 -- This procedure accepts a template id and structures EGO_USER_ATTR_DATA_TABLE, EGO_USER_ATTR_ROW_TABLE
324 -- and validates the attributes present in the structures it also accepts a array of external attribute value pairs
325 -- It first runs the EGO validations ( EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data) and then executes the UDA
326 -- Actiond with execution code 'XV'.
327 --Parameters:
328 --IN:
329 -- 1. p_template_id
330 -- This variable would contain the template id for which the actions are to be defaulted
331 -- 2. p_pk_column_name_value_pairs
332 -- This variable would contain an array of type EGO_COL_NAME_VALUE_PAIR_ARRAY with the PK values
333 --IN/OUT:
334 -- 1. x_attributes_row_table
335 -- This variable would contain a table of type EGO_USER_ATTR_ROW_TABLE with the attribute group details
336 -- 2. x_attr_name_value_pairs
337 -- This variable of type PO_UDA_USAGE_OBJECT_ARRAY contains data of hhe usages.
338 -- 3. x_external_attr_value_pairs
339 -- This variable would contain an array of attribute value pairs (EGO_COL_NAME_VALUE_PAIR_TABLE)
340 --OUT:
341 -- 1. x_return_status - Generic Out parameter having return status
342 -- 2. x_errorcode - Generic Out parameter having error code
343 -- 3. x_msg_count - Generic Out parameter having return msg Count
344 -- 4. x_msg_data - Generic Out parameter having return msg
345 --
346 --End of Comments
347 --------------------------------------------------------------------------------------------------------------------
348 PROCEDURE UDA_VALIDATE_ATTRIBUTES
349 (
350 p_template_id IN NUMBER
351 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
352 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
353 ,x_attr_name_value_pairs OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
354 ,x_attributes_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
355 ,x_return_status OUT NOCOPY VARCHAR2
356 ,x_errorcode OUT NOCOPY NUMBER
357 ,x_msg_count OUT NOCOPY NUMBER
358 ,x_msg_data OUT NOCOPY VARCHAR2
359 )
360 IS
361 d_progress NUMBER := 0;
362 l_failed_row_id_list VARCHAR2(200);
363 l_base_object_name VARCHAR2(200);
364 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
365
366 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
367
368 BEGIN
369 d_progress := 10;
370 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
371 (
372 EGO_COL_NAME_VALUE_PAIR_OBJ
373 (
374 'UDA_TEMPLATE_ID'
375 ,p_template_id || ''
376 )
377 );
378
379 l_pk_column_name_value_pairs := p_pk_column_name_value_pairs;
380 d_progress := 20;
381
382 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
383 (
384 p_api_version => 1.0
385 ,p_object_name => l_base_object_name
386 ,p_attributes_row_table => x_attributes_row_table
387 ,p_attributes_data_table => x_attr_name_value_pairs
388 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
389 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
390 ,p_validate_only => FND_API.G_TRUE
391 ,x_failed_row_id_list => l_failed_row_id_list
392 ,x_return_status => x_return_status
393 ,x_errorcode => x_errorcode
394 ,x_msg_count => x_msg_count
395 ,x_msg_data => x_msg_data
396 );
397 d_progress := 30;
398
399
400 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
401 d_progress := 40;
402 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
403 (
404 p_template_id => p_template_id
405 ,p_event => 'XV'
406 ,x_external_attr_value_pairs => x_external_attr_value_pairs
407 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
408 ,x_attr_name_value_pairs => x_attr_name_value_pairs
409 ,x_attributes_row_table => x_attributes_row_table
410 ,x_return_status => x_return_status
411 ,x_errorcode => x_errorcode
412 ,x_msg_count => x_msg_count
413 ,x_msg_data => x_msg_data
414 );
415 d_progress := 50;
416 END IF;
417 d_progress := 60;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 END UDA_VALIDATE_ATTRIBUTES;
423
424 PROCEDURE CREATE_ASSOCIATION_ID
425 (
426 p_template_id IN NUMBER,
427 p_attribute_grp_id IN NUMBER,
428 p_association_id OUT NOCOPY NUMBER
429 )
430 IS
431 v_entity_type VARCHAR2(100);
432 v_object_id NUMBER;
433 v_base_table VARCHAR2(100);
434
435 d_api_name CONSTANT VARCHAR2(30) := 'CREATE_ASSOCIATION_ID';
436 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
437 d_progress NUMBER;
438 v_data_level VARCHAR2(300);
439
440 x_return_status VARCHAR2(300);
441 x_errorcode NUMBER;
442 x_msg_count NUMBER;
443 x_msg_data VARCHAR2 (300);
444
445 --Bug9441838: Added variable to hold the sequence value
446 x_association_id NUMBER;
447 BEGIN
448
449 IF (PO_LOG.d_proc) THEN
450 PO_LOG.proc_begin(d_module);
451 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
452 PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
453 END IF;
454
455 d_progress := 10;
456 -- Fetching the internal name and entity code for the template
457 SELECT ENTITY_CODE
458 INTO v_entity_type
459 FROM PO_UDA_AG_TEMPLATES
460 WHERE TEMPLATE_ID = p_template_id
461 AND ROWNUM = 1;
462
463 IF PO_LOG.d_stmt THEN
464 PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
465 PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
466 END IF;
467
468 d_progress := 20;
469 SELECT OBJECT_ID
470 INTO v_object_id
471 FROM FND_OBJECTS
472 WHERE OBJ_NAME = g_object_dff_tl(v_entity_type).l_object_name;
473
474 SELECT DATA_LEVEL_NAME
475 INTO v_data_level
476 FROM EGO_DATA_LEVEL_B
477 WHERE DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
478 AND ATTR_GROUP_TYPE = v_entity_type;
479
480 IF PO_LOG.d_stmt THEN
481 PO_LOG.stmt(d_module, d_progress, 'v_object_id', v_object_id);
482 END IF;
483
484 d_progress := 30;
485
486 SELECT EGO_ASSOCS_S.NEXTVAL
487 INTO x_association_id
488 FROM sys.dual;
489
490
491 PO_UDA_TEMPLATES_UTIL.Create_Association (
492 p_api_version => 1.0
493 ,p_association_id => x_association_id
494 ,p_object_id => v_object_id
495 ,p_classification_code => p_template_id || ''
496 ,p_data_level => v_data_level
497 ,p_attr_group_id => p_attribute_grp_id
498 ,p_enabled_flag => 'Y'
499 ,p_view_privilege_id => NULL
500 ,p_edit_privilege_id => NULL
501 ,p_init_msg_list => fnd_api.g_FALSE
502 ,p_commit => fnd_api.g_FALSE
503 ,x_association_id => p_association_id
504 ,x_return_status => x_return_status
505 ,x_errorcode => x_errorcode
506 ,x_msg_count => x_msg_count
507 ,x_msg_data => x_msg_data
508 );
509
510 d_progress := 40;
511
512 IF PO_LOG.d_stmt THEN
513 PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
514 END IF;
515
516
517 IF (PO_LOG.d_exc) THEN
518 PO_LOG.exc(d_module, d_progress, p_association_id );
519 PO_LOG.proc_end(d_module);
520 END IF;
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 d_progress := 50;
525 IF PO_LOG.d_stmt THEN
526 PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
527 END IF;
528 END CREATE_ASSOCIATION_ID;
529
530 PROCEDURE GET_ASSOCIATION_ID
531 (
532 p_template_id IN NUMBER,
533 p_attribute_grp_id IN NUMBER,
534 p_association_id OUT NOCOPY NUMBER
535 )
536 IS
537 v_entity_type VARCHAR2(100);
538 v_object_id NUMBER;
539 v_base_table VARCHAR2(100);
540
541 d_api_name CONSTANT VARCHAR2(30) := 'GET_ASSOCIATION_ID';
542 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
543 d_progress NUMBER;
544 BEGIN
545
546 IF (PO_LOG.d_proc) THEN
547 PO_LOG.proc_begin(d_module);
548 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
549 PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
550 END IF;
551
552 d_progress := 10;
553 -- Fetching the internal name and entity code for the template
554 SELECT ENTITY_CODE
555 INTO v_entity_type
556 FROM PO_UDA_AG_TEMPLATES
557 WHERE TEMPLATE_ID = p_template_id
558 AND ROWNUM = 1;
559
560 IF PO_LOG.d_stmt THEN
561 PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
562 PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
563 END IF;
564
565 d_progress := 20;
566 SELECT OBJECT_ID
567 INTO v_object_id
568 FROM FND_OBJECTS
569 WHERE OBJ_NAME = g_object_dff_tl(v_entity_type).l_object_name;
570
571 IF PO_LOG.d_stmt THEN
572 PO_LOG.stmt(d_module, d_progress, 'v_object_id', v_object_id);
573 END IF;
574
575 d_progress := 30;
576 p_association_id := ego_ext_fwk_pub.Get_Association_Id_From_PKs(v_object_id , p_template_id || '', p_attribute_grp_id);
577
578 d_progress := 40;
579
580 IF PO_LOG.d_stmt THEN
581 PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
582 END IF;
583
584
585 IF (PO_LOG.d_exc) THEN
586 PO_LOG.exc(d_module, d_progress, p_association_id );
587 PO_LOG.proc_end(d_module);
588 END IF;
589
590 EXCEPTION
591 WHEN OTHERS THEN
592 d_progress := 50;
593 IF PO_LOG.d_stmt THEN
594 PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
595 END IF;
596 END GET_ASSOCIATION_ID;
597
598
599 PROCEDURE CHECK_CONTEXT_VALID
600 (
601 p_template_id IN NUMBER
602 ,p_usage_code IN VARCHAR2
603 ,x_valid_status OUT NOCOPY VARCHAR2
604 )
605 IS
606 l_count NUMBER := 0;
607
608 d_api_name CONSTANT VARCHAR2(30) := 'CHECK_CONTEXT_VALID';
609 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
610 d_progress NUMBER;
611 BEGIN
612
613 IF (PO_LOG.d_proc) THEN
614 PO_LOG.proc_begin(d_module);
615 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
616 PO_LOG.proc_begin(d_module, 'p_usage_code', p_usage_code);
617 END IF;
618
619 d_progress := 10;
620
621 SELECT 1
622 INTO l_count
623 FROM po_uda_AG_templates b, po_uda_ag_template_usages u
624 WHERE u.template_id = b.template_id
625 AND b.template_id = p_template_id
626 AND u.ATTRIBUTE_CATEGORY = p_usage_code
627 AND ROWNUM < 2;
628
629 IF PO_LOG.d_stmt THEN
630 PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
631 END IF;
632
633 d_progress := 20;
634
635 IF l_count >= 1 THEN
636 x_valid_status := 'true';
637 END IF;
638
639 IF PO_LOG.d_stmt THEN
640 PO_LOG.stmt(d_module, d_progress, 'x_valid_status', x_valid_status);
641 END IF;
642
643
644 d_progress := 30;
645
646 IF (PO_LOG.d_exc) THEN
647 PO_LOG.exc(d_module, d_progress, x_valid_status );
648 PO_LOG.proc_end(d_module);
649 END IF;
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 d_progress := 40;
654 x_valid_status := 'false';
655
656 IF (PO_LOG.d_exc) THEN
657 PO_LOG.exc(d_module, d_progress, x_valid_status );
658 PO_LOG.proc_end(d_module);
659 END IF;
660
661
662
663 END CHECK_CONTEXT_VALID;
664
665 PROCEDURE GET_ATTR_OR_PAGE_ID
666 (
667 p_sql_str IN VARCHAR2
668 ,p_template_id IN VARCHAR2
669 ,x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER
670 ,x_type OUT NOCOPY VARCHAR2
671 ,x_id OUT NOCOPY NUMBER
672 ,x_found_status OUT NOCOPY VARCHAR2
673 )
674 IS
675 v_query_hdl2 NUMBER;
676 v_query_hdl1 NUMBER;
677 v_attr_group_id NUMBER;
678 v_rows_processed NUMBER;
679 v_count_attr_grp_ids NUMBER := 0;
680 v_sql_pg VARCHAR2(30000) := '';
681 v_page_id NUMBER;
682 v_page_count NUMBER;
683 v_final_page_id NUMBER;
684 v_count_attr_pg_ids NUMBER := 0;
685
686 d_api_name CONSTANT VARCHAR2(30) := 'GET_ATTR_OR_PAGE_ID';
687 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
688 d_progress NUMBER;
689
690
691 BEGIN
692
693 IF (PO_LOG.d_proc) THEN
694 PO_LOG.proc_begin(d_module);
695 PO_LOG.proc_begin(d_module, 'p_sql_str', p_sql_str);
696 END IF;
697
698 d_progress := 10;
699
700 v_query_hdl2 := DBMS_SQL.open_cursor;
701 DBMS_SQL.parse(v_query_hdl2, p_sql_str, DBMS_SQL.native);
702 DBMS_SQL.define_column(v_query_hdl2, 1, v_attr_group_id);
703 v_rows_processed := DBMS_SQL.EXECUTE(v_query_hdl2);
704 x_attr_grp_ids := PO_TBL_NUMBER();
705
706 d_progress := 20;
707
708 IF PO_LOG.d_stmt THEN
709 PO_LOG.stmt(d_module, d_progress, 'v_attr_group_id', v_attr_group_id);
710 END IF;
711
712 -- Loop to fetch the PL SQL Table of attribute groups
713 LOOP
714
715 IF DBMS_SQL.fetch_rows(v_query_hdl2) > 0 THEN
716 DBMS_SQL.column_value(v_query_hdl2, 1, v_attr_group_id);
717 v_count_attr_grp_ids := v_count_attr_grp_ids + 1;
718 x_attr_grp_ids.extend(1);
719 x_attr_grp_ids(v_count_attr_grp_ids) := v_attr_group_id;
720
721 IF PO_LOG.d_stmt THEN
722 PO_LOG.stmt(d_module, d_progress, 'v_attr_group_id', v_attr_group_id);
723 END IF;
724
725 ELSE
726 EXIT;
727 END IF;
728
729 END LOOP;
730
731 DBMS_SQL.CLOSE_CURSOR(v_query_hdl2);
732
733 d_progress := 30;
734
735 IF PO_LOG.d_stmt THEN
736 PO_LOG.stmt(d_module, d_progress, 'v_count_attr_grp_ids', v_count_attr_grp_ids);
737 END IF;
738
739 IF v_count_attr_grp_ids = 0 THEN
740
741 x_found_status := 'false';
742 d_progress := 40;
743
744 IF PO_LOG.d_stmt THEN
745 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
746 END IF;
747
748 ELSIF v_count_attr_grp_ids = 1 THEN
749
750 x_type := 'ATTRIBUTE_GROUP_ID';
751 x_id := v_attr_group_id;
752 x_found_status := 'true';
753
754 d_progress := 50;
755
756 IF PO_LOG.d_stmt THEN
757 PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
758 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
759 END IF;
760
761 ELSE
762
763 v_sql_pg := 'SELECT PAGE_ID, count(*) FROM EGO_PAGE_ENTRIES_B PG, EGO_OBJ_AG_ASSOCS_B ATTR WHERE ';
764 v_sql_pg := v_sql_pg || ' PG.ASSOCIATION_ID = ATTR.ASSOCIATION_ID AND ATTR_GROUP_ID IN ( ' || p_sql_str;
765 v_sql_pg := v_sql_pg || ' ) AND PG.CLASSIFICATION_CODE = ''' || p_template_id || ''' GROUP BY PAGE_ID HAVING PAGE_ID NOT IN (';
766 v_sql_pg := v_sql_pg || ' SELECT DISTINCT PAGE_ID FROM EGO_PAGE_ENTRIES_B PG1, EGO_OBJ_AG_ASSOCS_B ATTR1';
767 v_sql_pg := v_sql_pg || ' WHERE PG1.ASSOCIATION_ID = ATTR1.ASSOCIATION_ID AND ATTR_GROUP_ID NOT IN (' || p_sql_str || '))';
768
769 d_progress := 60;
770
771 IF PO_LOG.d_stmt THEN
772 PO_LOG.stmt(d_module, d_progress, 'v_sql_pg', v_sql_pg);
773 END IF;
774
775 v_query_hdl1 := DBMS_SQL.open_cursor;
776 DBMS_SQL.parse(v_query_hdl1, v_sql_pg, DBMS_SQL.native);
777 DBMS_SQL.define_column(v_query_hdl1, 1, v_page_id);
778 DBMS_SQL.define_column(v_query_hdl1, 2, v_page_count);
779 v_rows_processed := DBMS_SQL.EXECUTE(v_query_hdl1);
780
781 d_progress := 70;
782
783 IF PO_LOG.d_stmt THEN
784 PO_LOG.stmt(d_module, d_progress, 'v_rows_processed', v_rows_processed);
785 END IF;
786
787 -- Loop to fetch the actual page id
788 LOOP
789
790 IF DBMS_SQL.fetch_rows(v_query_hdl1) > 0 THEN
791 DBMS_SQL.column_value(v_query_hdl1, 1, v_page_id);
792 DBMS_SQL.column_value(v_query_hdl1, 2, v_page_count);
793 IF v_page_count = v_count_attr_grp_ids THEN
794 IF v_count_attr_grp_ids = v_page_count THEN
795 v_final_page_id := v_page_id;
796 v_count_attr_pg_ids := v_count_attr_pg_ids + 1;
797 END IF;
798 END IF;
799 ELSE
800 EXIT;
801 END IF;
802
803 END LOOP;
804 DBMS_SQL.CLOSE_CURSOR(v_query_hdl1);
805
806 d_progress := 80;
807
808 IF PO_LOG.d_stmt THEN
809 PO_LOG.stmt(d_module, d_progress, 'v_count_attr_pg_ids', v_count_attr_pg_ids);
810 END IF;
811
812 IF v_count_attr_pg_ids = 1 THEN
813
814 x_type := 'PAGE_ID';
815 x_id := v_final_page_id;
816 x_found_status := 'true';
817
818 d_progress := 90;
819
820 IF PO_LOG.d_stmt THEN
821 PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
822 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
823 END IF;
824
825 ELSE
826 x_found_status := 'false';
827 d_progress := 100;
828
829 IF PO_LOG.d_stmt THEN
830 PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
831 END IF;
832
833 END IF;
834
835 END IF;
836 EXCEPTION
837 WHEN OTHERS THEN
838 IF (PO_LOG.d_exc) THEN
839 d_progress := 110;
840 PO_LOG.exc(d_module, d_progress, x_found_status );
841 PO_LOG.proc_end(d_module);
842 END IF;
843 END GET_ATTR_OR_PAGE_ID;
844
845 -------------------------------------------------------------------------------
846 --Start of Comments
847 --Name: AUTOCREATE_USER_ATTRS
848 --Pre-reqs:
849 -- None.
850 --Modifies:
851 -- Extension Tables for table associated with to_tempalate_id
852 --Locks:
853 -- None.
854 --Function:
855 -- This procedure Copy User Defined Attributes across various Entities like
856 -- Requisiton to Purchasing Documents or from Solication to Requisition to
857 -- Purchase Order
858 --Parameters:
859 --IN:
860 -- 1. from_template_id - Source Document's UDA Template Id needs to passed.
861 -- 2. to_template_id - Destination Document's UDA Template Id needs to passed.
862 -- 3. from_pk_col_value_pairs
863 -- Is the Name Value Pair Arry of type EGO_COL_NAME_VALUE_PAIR_ARRAY which need to created for all the primary Keys of Source Document.
864 -- 4. to_pk_col_value_pairs
865 -- Is the Name Value Pair Arry of type EGO_COL_NAME_VALUE_PAIR_ARRAY which need to created for all the primary Keys of Source Document.
866 -- 5. copy_attribute_groups - Possible Values are :
867 -- 1. 'ALL' [Default] - Identify that all the Attribute Groups which are associated to Source Document and having a Attribute Group Mapping to a Destination Document Attribute Group and have a usage in Destination template.
868 -- 2. SPECIFIC - Only Specific Attribute Groups which internal names are mentioned in attribute_group_table parameter.
869 -- 3. EXCLUSIVE - All the Attribute group mentioned in attribute_group_table will be ignored.
870 -- 6. attribute_group_table - PL SQL table of Varchar30 to store Attribute Group Internal Names.
871 -- 7. p_commit - Flag to indentify whether the commit has to be done by the API or calling program will perform a commit.
872 -- Possible Values are :
873 -- 1. FND_API.G_FALSE [Default]
874 -- 2. FND_API.G_TRUE
875 --OUT:
876 -- 1. x_return_status - Generic Out parameter having return status
877 -- 2. x_msg_count - Generic Out parameter having return msg Count
878 -- 3. x_msg_data - Generic Out parameter having return msg. --Testing:
879 --Testing:
880 --
881 --End of Comments
882 -------------------------------------------------------------------------------
883
884 PROCEDURE AutoCreate_User_Attrs
885 (
886 from_template_id IN NUMBER
887 ,to_template_id IN NUMBER
888 ,from_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
889 ,to_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
890 ,copy_attribute_groups IN VARCHAR2 DEFAULT 'ALL' -- SPECIFIC / EXCLUSIVE
891 ,attribute_group_table IN PO_TBL_VARCHAR30 DEFAULT NULL
892 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
893 ,x_return_status OUT NOCOPY VARCHAR2
894 ,x_msg_count OUT NOCOPY NUMBER
895 ,x_msg_data OUT NOCOPY VARCHAR2
896 )IS
897
898 d_api_name CONSTANT VARCHAR2(30) := 'AutoCreate_User_Attrs';
899 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
900 d_progress NUMBER;
901
902
903 l_from_object_cr g_object_dff_cr;
904 l_to_object_cr g_object_dff_cr;
905
906 l_from_object FND_OBJECTS%ROWTYPE;
907 l_to_object FND_OBJECTS%ROWTYPE;
908 l_to_data_level_id NUMBER;
909 l_from_data_level_id NUMBER;
910
911
912 l_index NUMBER;
913
914 l_insert_pk_sql VARCHAR2(500);
915 l_select_pk_sql VARCHAR2(2000);
916 l_from_where_pk_sql VARCHAR2(2000);
917 l_to_where_pk_sql VARCHAR2(2000);
918 l_where_ag_sql VARCHAR2(2000);
919 l_attr_group_id NUMBER;
920 from_attr_group_Type VARCHAR2(300);
921 to_attr_group_Type VARCHAR2(300);
922
923 l_where_not_in_sql VARCHAR2(1000);
924 l_column_name_to_copy VARCHAR2(30);
925 l_language VARCHAR2(30);
926 l_base_table_copy_dml VARCHAR2(20000) := '';
927 l_tl_table_copy_dml VARCHAR2(20000) := '';
928 l_base_table_copy_dml_table PO_TBL_VARCHAR4000;
929 l_tl_table_copy_dml_table PO_TBL_VARCHAR4000;
930
931 l_copy_from_ext_id NUMBER;
932 l_copy_to_ext_id NUMBER;
933 l_dynamic_sql VARCHAR2(1000);
934 l_dynamic_Lang_sql VARCHAR2(500);
935 l_b_table_col_names_list VARCHAR2(3000);
936 l_d_b_table_col_names_list VARCHAR2(4000);
937 l_s_b_table_col_names_list VARCHAR2(4000);
938
939 l_tl_table_col_names_list VARCHAR2(3000);
940 l_d_tl_table_col_names_list VARCHAR2(4000);
941 l_s_tl_table_col_names_list VARCHAR2(4000);
942
943 l_b_table_upt_list VARCHAR2(6000);
944 l_tl_table_upt_list VARCHAR2(6000);
945
946 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
947 l_current_login_id NUMBER := FND_GLOBAL.Login_Id;
948
949 TYPE DYNAMIC_CUR IS REF CURSOR;
950 l_dynamic_cursor DYNAMIC_CUR;
951 l_dynamic_lang_cursor DYNAMIC_CUR;
952
953 BEGIN
954 d_progress := 001;
955 IF (PO_LOG.d_proc) THEN
956 PO_LOG.proc_begin(d_module);
957 PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958 PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959 PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960 PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
961 PO_LOG.proc_begin(d_module, 'p_commit', p_commit);
962 END IF;
963
964 IF FND_API.To_Boolean(p_commit) THEN
965 SAVEPOINT Copy_Attrs_Data_SP;
966 END IF;
967
968 SELECT ENTITY_CODE
969 INTO from_attr_group_Type
970 FROM PO_UDA_AG_TEMPLATES
971 WHERE TEMPLATE_ID = from_template_id;
972
973 SELECT ENTITY_CODE
974 INTO to_attr_group_type
975 FROM PO_UDA_AG_TEMPLATES
976 WHERE TEMPLATE_ID = to_template_id;
977
978 IF PO_LOG.d_stmt THEN
979 PO_LOG.stmt(d_module,d_progress,'from_attr_group_Type',from_attr_group_Type);
980 PO_LOG.stmt(d_module,d_progress,'to_attr_group_Type',to_attr_group_Type);
981 END IF;
982
983 l_from_object_cr := g_object_dff_tl(from_attr_group_Type);
984 l_to_object_cr := g_object_dff_tl(to_attr_group_Type);
985
986 SELECT *
987 INTO l_from_object
988 FROM FND_OBJECTS
989 WHERE OBJ_NAME = l_from_object_cr.l_object_name;
990
991 SELECT *
992 INTO l_to_object
993 FROM FND_OBJECTS
994 WHERE OBJ_NAME = l_to_object_cr.l_object_name;
995
996 d_progress := 010;
997
998 -----------------------------------------------------------------------------
999 -- loop through all from_pk_col_value_pairs values for :
1000 -- l_from_where_pk_sql : Used to Fetch the extension records to be copied.
1001 -- l_where_not_in_sql : PK columns of Ext. Table which should not be copied.
1002 -----------------------------------------------------------------------------
1003
1004 l_index := 0;
1005 l_index := from_pk_col_value_pairs.FIRST;
1006 WHILE (l_index <= from_pk_col_value_pairs.LAST)
1007 LOOP
1008 IF (((l_from_object.PK1_COLUMN_NAME IS NOT NULL) AND (from_pk_col_value_pairs(l_index).NAME = Upper(l_from_object.PK1_COLUMN_NAME))) OR
1009 ((l_from_object.PK2_COLUMN_NAME IS NOT NULL) AND (from_pk_col_value_pairs(l_index).NAME = Upper(l_from_object.PK2_COLUMN_NAME))) OR
1010 ((l_from_object.PK3_COLUMN_NAME IS NOT NULL) AND (from_pk_col_value_pairs(l_index).NAME = Upper(l_from_object.PK3_COLUMN_NAME))) OR
1011 ((l_from_object.PK4_COLUMN_NAME IS NOT NULL) AND (from_pk_col_value_pairs(l_index).NAME = Upper(l_from_object.PK4_COLUMN_NAME))) OR
1012 ((l_from_object.PK5_COLUMN_NAME IS NOT NULL) AND (from_pk_col_value_pairs(l_index).NAME = Upper(l_from_object.PK5_COLUMN_NAME))) )
1013 THEN
1014 IF (from_pk_col_value_pairs(l_index).VALUE IS NOT NULL) THEN
1015 l_from_where_pk_sql := l_from_where_pk_sql ||from_pk_col_value_pairs(l_index).NAME ||' = ''' ||from_pk_col_value_pairs(l_index).VALUE ||''' AND ';
1016 l_where_not_in_sql := l_where_not_in_sql ||'''' ||from_pk_col_value_pairs(l_index).NAME ||''', ' ;
1017 END IF;
1018 ELSE
1019 FND_MESSAGE.set_name('PO', 'PO_UDA_PK_COL_NAME_ERR');
1020 FND_MSG_PUB.add;
1021 RAISE FND_API.G_EXC_ERROR;
1022 END IF;
1023 l_index := from_pk_col_value_pairs.NEXT(l_index);
1024 END LOOP;
1025
1026 IF PO_LOG.d_stmt THEN
1027 PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1028 PO_LOG.stmt(d_module,d_progress,'l_where_not_in_sql',l_where_not_in_sql);
1029 END IF;
1030
1031 d_progress := 020;
1032
1033 -----------------------------------------------------------------------------
1034 -- loop through all to_pk_col_value_pairs values for Create clause :
1035 -- l_insert_pk_sql : Used to insert Primary Key of Target Extension record.
1036 -- l_select_pk_sql : Values of the Primary Key for l_insert_pk_sql
1037 -- l_to_where_pk_sql : Merge Condition to check for update / Create
1038 -----------------------------------------------------------------------------
1039
1040 l_index := 0;
1041 l_index := to_pk_col_value_pairs.FIRST;
1042 WHILE (l_index <= to_pk_col_value_pairs.LAST)
1043 LOOP
1044 IF PO_LOG.d_stmt THEN
1045 PO_LOG.stmt(d_module,d_progress,'to_pk_col_value_pairs(l_index).NAME' , to_pk_col_value_pairs(l_index).NAME);
1046 PO_LOG.stmt(d_module,d_progress,'l_to_object.PK1_COLUMN_NAME ' , l_to_object.PK1_COLUMN_NAME);
1047 PO_LOG.stmt(d_module,d_progress,'l_to_object.PK2_COLUMN_NAME ' , l_to_object.PK2_COLUMN_NAME);
1048 END IF ;
1049 IF (((l_to_object.PK1_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK1_COLUMN_NAME))) OR
1050 ((l_to_object.PK2_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK2_COLUMN_NAME))) OR
1051 ((l_to_object.PK3_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK3_COLUMN_NAME))) OR
1052 ((l_to_object.PK4_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK4_COLUMN_NAME))) OR
1053 ((l_to_object.PK5_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK5_COLUMN_NAME))) )
1054 THEN
1055 IF (to_pk_col_value_pairs(l_index).VALUE IS NOT NULL) THEN
1056 l_insert_pk_sql := l_insert_pk_sql || 'destination.'|| to_pk_col_value_pairs(l_index).NAME ||', ';
1057 l_select_pk_sql := l_select_pk_sql ||'''' ||to_pk_col_value_pairs(l_index).VALUE ||''', ';
1058 l_to_where_pk_sql := l_to_where_pk_sql || 'destination.'|| to_pk_col_value_pairs(l_index).NAME ||' = ''' ||to_pk_col_value_pairs(l_index).VALUE ||''' AND ';
1059 END IF;
1060 ELSE
1061 FND_MESSAGE.set_name('PO', 'PO_UDA_PK_COL_NAME_ERR');
1062 FND_MSG_PUB.add;
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065 l_index := to_pk_col_value_pairs.NEXT(l_index);
1066 END LOOP;
1067
1068 d_progress := 020;
1069 IF PO_LOG.d_stmt THEN
1070 PO_LOG.stmt(d_module,d_progress,'l_insert_pk_sql',l_insert_pk_sql);
1071 PO_LOG.stmt(d_module,d_progress,'l_select_pk_sql',l_select_pk_sql);
1072 PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1073 END IF;
1074 -----------------------------------------------------------------------------
1075 -- Columns which are not to be copied from Source to Destination.
1076 -----------------------------------------------------------------------------
1077 /*Bug 13892657 : the UDA_TEMPLTE_ID for the target doc was stamped with
1078 the value from source. Now added UDA_TEMPLTE_ID to the exclusion list and
1079 in the merge statement below updated it with value to_template_id*/
1080 l_where_not_in_sql := l_where_not_in_sql ||
1081 '''EXTENSION_ID'', '||
1082 '''ATTR_GROUP_ID'', '||
1083 '''DATA_LEVEL_ID'', '||
1084 '''UDA_TEMPLATE_ID'', '||
1085 '''PK1_VALUE'', '||
1086 '''PK2_VALUE'', '||
1087 '''PK3_VALUE'', '||
1088 '''PK4_VALUE'', '||
1089 '''PK5_VALUE'', '||
1090 '''CREATED_BY'', '||
1091 '''CREATION_DATE'', '||
1092 '''LAST_UPDATED_BY'', '||
1093 '''LAST_UPDATE_DATE'', '||
1094 '''LAST_UPDATE_LOGIN''';
1095
1096 -----------------------------------------------------------------------------
1097 -- Columns List of the Columns to be selected from the Source Table
1098 -----------------------------------------------------------------------------
1099 l_b_table_col_names_list := Get_Table_Columns_List(
1100 p_from_table_name => l_from_object_cr.l_object_ext_b_table
1101 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1102 );
1103 -----------------------------------------------------------------------------
1104 -- Columns List of the Columns to be used as Values in Insert Section .
1105 -----------------------------------------------------------------------------
1106 l_s_b_table_col_names_list := Get_Table_Columns_List(
1107 p_from_table_name => l_from_object_cr.l_object_ext_b_table
1108 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1109 ,p_from_table_alias_prefix => 'source'
1110 );
1111 -----------------------------------------------------------------------------
1112 -- Columns List of the Columns to be used in Insert Section .
1113 -----------------------------------------------------------------------------
1114 l_d_b_table_col_names_list := Get_Table_Columns_List(
1115 p_from_table_name => l_from_object_cr.l_object_ext_b_table
1116 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1117 ,p_from_table_alias_prefix => 'destination'
1118 );
1119
1120 -----------------------------------------------------------------------------
1121 -- Columns List of the Columns to be selected from the Source Table
1122 -----------------------------------------------------------------------------
1123 l_tl_table_col_names_list := Get_Table_Columns_List(
1124 p_from_table_name => l_from_object_cr.l_object_ext_tl_table
1125 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1126 );
1127 -----------------------------------------------------------------------------
1128 -- Columns List of the Columns to be used as Values in Insert Section .
1129 -----------------------------------------------------------------------------
1130 l_s_tl_table_col_names_list := Get_Table_Columns_List(
1131 p_from_table_name => l_from_object_cr.l_object_ext_tl_table
1132 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1133 ,p_from_table_alias_prefix => 'source'
1134 );
1135 -----------------------------------------------------------------------------
1136 -- Columns List of the Columns to be used in Insert Section .
1137 -----------------------------------------------------------------------------
1138 l_d_tl_table_col_names_list := Get_Table_Columns_List(
1139 p_from_table_name => l_from_object_cr.l_object_ext_tl_table
1140 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1141 ,p_from_table_alias_prefix => 'destination'
1142 );
1143
1144 -----------------------------------------------------------------------------
1145 -- Source To Destination Mapping for Update Sectio of Merge for Base Table.
1146 -----------------------------------------------------------------------------
1147 l_b_table_upt_list := Get_Table_Columns_List(
1148 p_from_table_name => l_from_object_cr.l_object_ext_b_table
1149 ,p_from_cols_to_exclude_list => l_where_not_in_sql
1150 ,p_from_table_alias_prefix => 'source'
1151 ,p_to_table_name => l_to_object_cr.l_object_ext_b_table
1152 ,p_to_table_alias_prefix => 'destination'
1153 );
1154
1155 -----------------------------------------------------------------------------
1156 -- Source To Destination Mapping for Update Sectio of Merge for TL Table.
1157 -----------------------------------------------------------------------------
1158 l_tl_table_upt_list := Get_Table_Columns_List(
1159 p_from_table_name => l_from_object_cr.l_object_ext_tl_table
1160 ,p_from_cols_to_exclude_list => l_where_not_in_sql || ',' || '''LANGUAGE''' || ',' || '''SOURCE_LANG'''
1161 ,p_from_table_alias_prefix => 'source'
1162 ,p_to_table_name => l_to_object_cr.l_object_ext_tl_table
1163 ,p_to_table_alias_prefix => 'destination'
1164 );
1165
1166 -----------------------------------------------------------------------------
1167 -- Trim the last , which is left from Get_Table_Columns_List Procedure
1168 -----------------------------------------------------------------------------
1169 l_b_table_upt_list := RTRIM(l_b_table_upt_list, ',');
1170 l_tl_table_upt_list := RTRIM(l_tl_table_upt_list, ',');
1171
1172 d_progress := 030;
1173 IF PO_LOG.d_stmt THEN
1174 PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175 PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176 PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177 PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178 PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179 PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180 PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1181 PO_LOG.stmt(d_module,d_progress,'l_tl_table_upt_list',l_tl_table_upt_list);
1182 END IF;
1183
1184 -----------------------------------------------------------------------------
1185 -- Creating the Condition to Filter Records Based On the Attribute Group List.
1186 -- l_where_ag_sql : SQL for Filtering the records to be Copied.
1187 -----------------------------------------------------------------------------
1188
1189 IF ( (copy_attribute_groups = 'SPECIFIC') OR ( copy_attribute_groups = 'EXCLUSIVE') ) THEN
1190 d_progress := 040;
1191 FOR i IN 1..attribute_group_table.Count LOOP
1192 BEGIN
1193 SELECT attr_group_id
1194 INTO l_attr_group_id
1195 FROM EGO_ATTR_GROUPS_V
1196 WHERE ATTR_GROUP_TYPE = from_attr_group_Type
1197 AND ATTR_GROUP_NAME = attribute_group_table(i);
1198 EXCEPTION
1199 WHEN No_Data_Found THEN
1200 FND_MESSAGE.set_name('PO', 'PO_WRONG_ATTR_GROUP');
1201 FND_MSG_PUB.add;
1202 RAISE FND_API.G_EXC_ERROR;
1203 END ;
1204
1205 l_where_ag_sql := l_where_ag_sql || l_attr_group_id || ',' ;
1206 END LOOP;
1207
1208 -- Remove the extra ',' after the loop is finished.
1209 IF (LENGTH(l_where_ag_sql) > 0) THEN
1210 l_where_ag_sql := RTRIM(l_where_ag_sql, ',');
1211 END IF;
1212 -----------------------------------------------------------------------------
1213 -- Create Not in Clause for EXCLUSIVE and IN Clause for SPECIFIC
1214 -----------------------------------------------------------------------------
1215 IF ( copy_attribute_groups = 'SPECIFIC') THEN
1216 l_where_ag_sql := ' AND ATTR_GROUP_ID IN ( '|| l_where_ag_sql || ' )';
1217 ELSE IF ( copy_attribute_groups = 'EXCLUSIVE') THEN
1218 l_where_ag_sql := ' AND ATTR_GROUP_ID NOT IN ( '|| l_where_ag_sql || ' )';
1219 END IF ;
1220 END IF;
1221
1222 IF PO_LOG.d_stmt THEN
1223 PO_LOG.stmt(d_module,d_progress,'l_where_ag_sql',l_where_ag_sql);
1224 END IF;
1225 END IF ;
1226
1227 d_progress := 050;
1228 -----------------------------------------------------------------------------
1229 -- Fetch The TO Data Level to be used for copied records.
1230 -----------------------------------------------------------------------------
1231 SELECT DISTINCT(DATA_LEVEL_ID)
1232 INTO l_to_data_level_id
1233 FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = to_attr_group_Type;
1234
1235 -----------------------------------------------------------------------------
1236 -- Fetch The From Data Level to be used filter Correct reow to be copied.
1237 -----------------------------------------------------------------------------
1238 SELECT DISTINCT(DATA_LEVEL_ID)
1239 INTO l_from_data_level_id
1240 FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = from_attr_group_Type;
1241
1242 IF PO_LOG.d_stmt THEN
1243 PO_LOG.stmt(d_module,d_progress,'From Data Level' || l_from_data_level_id);
1244 PO_LOG.stmt(d_module,d_progress,'To Data Level' || l_to_data_level_id);
1245 END IF;
1246
1247 /*Bug 13892657 : Added UDA_TEMPALTE_ID in the insert statement*/
1248 l_base_table_copy_dml := 'MERGE INTO ' || l_to_object_cr.l_object_ext_b_table || ' destination' ||
1249 ' USING ' ||
1250 '( SELECT '||
1251 ':1 TO_EXTENSION_ID, '||
1252 '(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID) TO_ATTR_GROUP_ID, '|| -- ATTR_GROUP_ID
1253 'ATTR_GROUP_ID, ' ||
1254 l_b_table_col_names_list||' '||
1255 'last_updated_by,last_update_date '||
1256 ' FROM '||l_from_object_cr.l_object_ext_b_table||
1257 ' WHERE EXTENSION_ID = :2 ' ||
1258 l_where_ag_sql||
1259 ' AND EXISTS ( SELECT 1 FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID AND ' || l_to_object_cr.l_ag_mapping_column || ' IS NOT null )' ||
1260 ') source ' ||
1261 'ON ( '||
1262 l_to_where_pk_sql ||
1263 ' destination.attr_group_id = source.TO_ATTR_GROUP_ID ' ||
1264 ' and destination.data_level_id = ' || l_to_data_level_id || ' )' ||
1265 ' WHEN MATCHED THEN ' ||
1266 ' UPDATE ' ||
1267 ' SET ' ||
1268 'destination.last_updated_by = source.last_updated_by ,' ||
1269 'destination.last_update_date = source.last_update_date ,' ||
1270 l_b_table_upt_list ||
1271 ' WHEN NOT MATCHED THEN '||
1272 ' INSERT ' ||
1273 ' (destination.EXTENSION_ID, '||
1274 'destination.ATTR_GROUP_ID, '||
1275 l_insert_pk_sql ||' '||
1276 'destination.DATA_LEVEL_ID, '||
1277 'destination.UDA_TEMPLATE_ID, '||
1278 l_d_b_table_col_names_list||' '||
1279 'destination.CREATED_BY, '||
1280 'destination.CREATION_DATE, '||
1281 'destination.LAST_UPDATED_BY, '||
1282 'destination.LAST_UPDATE_DATE, '||
1283 'destination.LAST_UPDATE_LOGIN)' ||
1284 ' VALUES ' ||
1285 '(source.TO_EXTENSION_ID, '||
1286 ' source.TO_ATTR_GROUP_ID, '||
1287 l_select_pk_sql ||' '||
1288 l_to_data_level_id||', '||
1289 to_template_id||', '||
1290 l_s_b_table_col_names_list||' '||
1291 l_current_user_id||', '||
1292 'SYSDATE, '||
1293 l_current_user_id||', '||
1294 'SYSDATE, '||
1295 l_current_login_id ||
1296 ') ';
1297
1298 /*Bug 13892657 : Added UDA_TEMPALTE_ID in the insert statement*/
1299 l_tl_table_copy_dml := 'MERGE INTO ' || l_to_object_cr.l_object_ext_tl_table || ' destination' ||
1300 ' USING ' ||
1301 '( SELECT '||
1302 ':1 TO_EXTENSION_ID, '||
1303 '(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID) TO_ATTR_GROUP_ID, '|| -- ATTR_GROUP_ID
1304 'ATTR_GROUP_ID, ' ||
1305 l_tl_table_col_names_list||' '||
1306 'last_updated_by,last_update_date '||
1307 ' FROM '||l_from_object_cr.l_object_ext_tl_table||
1308 ' WHERE EXTENSION_ID = :2 ' ||
1309 ' AND language = :3 ' ||
1310 l_where_ag_sql||
1311 ' AND EXISTS ( SELECT 1 FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID AND ' || l_to_object_cr.l_ag_mapping_column || ' IS NOT null )' ||
1312 ') source ' ||
1313 'ON ( '||
1314 l_to_where_pk_sql ||
1315 ' destination.LANGUAGE = source.LANGUAGE '||
1316 ' AND destination.attr_group_id = source.TO_ATTR_GROUP_ID ' ||
1317 ' and destination.data_level_id = ' || l_to_data_level_id || ' )' ||
1318 ' WHEN MATCHED THEN ' ||
1319 ' UPDATE ' ||
1320 ' SET ' ||
1321 'destination.last_updated_by = source.last_updated_by ,' ||
1322 'destination.last_update_date = source.last_update_date ,' ||
1323 l_tl_table_upt_list ||
1324 ' WHEN NOT MATCHED THEN '||
1325 ' INSERT ' ||
1326 ' (destination.EXTENSION_ID, '||
1327 'destination.ATTR_GROUP_ID, '||
1328 l_insert_pk_sql ||' '||
1329 'destination.DATA_LEVEL_ID, '||
1330 'destination.UDA_TEMPLATE_ID, '||
1331 l_d_tl_table_col_names_list||' '||
1332 'destination.CREATED_BY, '||
1333 'destination.CREATION_DATE, '||
1334 'destination.LAST_UPDATED_BY, '||
1335 'destination.LAST_UPDATE_DATE, '||
1336 'destination.LAST_UPDATE_LOGIN)' ||
1337 ' VALUES ' ||
1338 '(source.TO_EXTENSION_ID, '||
1339 ' source.TO_ATTR_GROUP_ID, '||
1340 l_select_pk_sql ||' '||
1341 l_to_data_level_id||', '||
1342 to_template_id||', '||
1343 l_s_tl_table_col_names_list||' '||
1344 l_current_user_id||', '||
1345 'SYSDATE, '||
1346 l_current_user_id||', '||
1347 'SYSDATE, '||
1348 l_current_login_id ||
1349 ') ';
1350 d_progress := 060;
1351
1352 IF PO_LOG.d_stmt THEN
1353 l_base_table_copy_dml_table := PO_TBL_VARCHAR4000 (SUBSTR(l_base_table_copy_dml,1 , 4000),
1354 SUBSTR(l_base_table_copy_dml,4001 , 4000),
1355 SUBSTR(l_base_table_copy_dml,8001 , 4000),
1356 SUBSTR(l_base_table_copy_dml,12001 , 4000));
1357 l_tl_table_copy_dml_table := PO_TBL_VARCHAR4000 (SUBSTR(l_tl_table_copy_dml,1 , 4000),
1358 SUBSTR(l_tl_table_copy_dml,4001 , 4000),
1359 SUBSTR(l_tl_table_copy_dml,8001 , 4000),
1360 SUBSTR(l_tl_table_copy_dml,12001 , 4000));
1361
1362 PO_LOG.stmt(d_module,d_progress,'l_base_table_copy_dml_table', l_base_table_copy_dml_table);
1363 PO_LOG.stmt(d_module,d_progress,'l_tl_table_copy_dml_table', l_tl_table_copy_dml_table);
1364 END IF;
1365
1366 d_progress := 070;
1367 -----------------------------------------------------------------------------
1368 -- Creating a Dynamic SQL to select which Extension Records need to Copied
1369 -- for the giving Source Document.
1370 -- Only those Extension records will be picked pertaing to Attribute Groups
1371 -- which has a entry in mapping table.
1372 -- Extension record pertaing to Destination Attribute groups which are not
1373 -- there in target documents Template will be skipped.
1374 -----------------------------------------------------------------------------
1375
1376 l_dynamic_sql := ' SELECT EXTENSION_ID, EGO_EXTFWK_S.NEXTVAL '||
1377 ' FROM '||l_from_object_cr.l_object_ext_b_table||
1378 ' WHERE '||l_from_where_pk_sql ||
1379 ' DATA_LEVEL_ID = ' || l_from_data_level_id ||
1380 ' AND EXISTS (SELECT 1 from PO_UDA_AG_TEMPLATE_USAGES where template_id = ' || to_template_id ||
1381 ' AND attribute_group_id = ' ||
1382 '(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID)) ';
1383
1384 IF PO_LOG.d_stmt THEN
1385 PO_LOG.stmt(d_module,d_progress,'l_dynamic_sql', l_dynamic_sql);
1386 END IF;
1387
1388 OPEN l_dynamic_cursor FOR l_dynamic_sql;
1389 LOOP
1390 FETCH l_dynamic_cursor INTO l_copy_from_ext_id, l_copy_to_ext_id;
1391 EXIT WHEN l_dynamic_cursor%NOTFOUND;
1392 EXECUTE IMMEDIATE l_base_table_copy_dml USING l_copy_to_ext_id, l_copy_from_ext_id;
1393
1394 IF (SQL%ROWCOUNT > 0) THEN
1395 -----------------------------------------------------------------------------
1396 -- Copy the TL Table Records only if Records Where Copied from Base Table.
1397 -----------------------------------------------------------------------------
1398 l_dynamic_Lang_sql := 'SELECT language from '|| l_from_object_cr.l_object_ext_tl_table || ' where extension_id = ' || l_copy_from_ext_id;
1399 IF PO_LOG.d_stmt THEN
1400 PO_LOG.stmt(d_module,d_progress,'l_dynamic_Lang_sql', l_dynamic_Lang_sql);
1401 END IF;
1402 -----------------------------------------------------------------------------
1403 -- Copy the all TL Table Records based on Langauge.
1404 -----------------------------------------------------------------------------
1405 OPEN l_dynamic_lang_cursor FOR l_dynamic_Lang_sql ;
1406 LOOP
1407 FETCH l_dynamic_lang_cursor INTO l_language;
1408 EXIT WHEN l_dynamic_lang_cursor%NOTFOUND;
1409 EXECUTE IMMEDIATE l_tl_table_copy_dml USING l_copy_to_ext_id, l_copy_from_ext_id , l_language;
1410
1411 END LOOP;
1412 CLOSE l_dynamic_lang_cursor;
1413 END IF;
1414
1415 END LOOP;
1416 CLOSE l_dynamic_cursor;
1417
1418 d_progress := 090;
1419 IF FND_API.To_Boolean(p_commit) THEN
1420 COMMIT WORK;
1421 END IF;
1422
1423 x_return_status := FND_API.G_RET_STS_SUCCESS;
1424 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1425
1426 IF (PO_LOG.d_proc) THEN
1427 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1428 PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1429 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1430 PO_LOG.proc_end(d_module);
1431 END IF;
1432
1433 EXCEPTION
1434 WHEN FND_API.G_EXC_ERROR THEN
1435 d_progress := 100;
1436 IF FND_API.To_Boolean(p_commit) THEN
1437 ROLLBACK TO Copy_Attrs_Data_SP;
1438 END IF;
1439 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1440 IF (PO_LOG.d_exc) THEN
1441 PO_LOG.exc(d_module, d_progress, x_msg_data );
1442 PO_LOG.proc_end(d_module);
1443 END IF;
1444 x_return_status := FND_API.G_RET_STS_ERROR;
1445 WHEN OTHERS THEN
1446 PO_MESSAGE_S.add_exc_msg
1447 ( p_pkg_name => d_pkg_name,
1448 p_procedure_name => d_api_name || '.' || d_progress
1449 );
1450 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1452 END AutoCreate_User_Attrs;
1453
1454
1455
1456 FUNCTION Get_Table_Columns_List (
1457 p_from_table_name IN VARCHAR2
1458 ,p_from_cols_to_exclude_list IN VARCHAR2 DEFAULT NULL
1459 ,p_from_table_alias_prefix IN VARCHAR2 DEFAULT NULL
1460 ,p_to_table_name IN VARCHAR2 DEFAULT NULL
1461 ,p_to_table_alias_prefix IN VARCHAR2 DEFAULT NULL
1462 )
1463 RETURN VARCHAR2
1464 IS
1465 d_api_name CONSTANT VARCHAR2(30) := 'Get_Table_Columns_List';
1466 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1467 d_progress NUMBER;
1468
1469
1470 l_dynamic_sql VARCHAR2(20000);
1471 l_table_column_names_list VARCHAR2(32767);
1472 l_column_name VARCHAR2(30);
1473 l_column_type VARCHAR2(1);
1474 l_in_update_mode BOOLEAN;
1475
1476
1477 TYPE DYNAMIC_CUR IS REF CURSOR;
1478 l_dynamic_cursor DYNAMIC_CUR;
1479
1480 l_app_id NUMBER;
1481
1482 BEGIN
1483 IF (PO_LOG.d_proc) THEN
1484 PO_LOG.proc_begin(d_module);
1485 PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486 PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487 PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488 PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1489 PO_LOG.proc_begin(d_module, 'p_to_table_alias_prefix', p_to_table_alias_prefix);
1490 END IF;
1491 d_progress := 001;
1492 -----------------------------------------------------------------------------
1493 -- SQL to Fetch the Column List from SYS.ALL_TAB_COLUMNS Table.
1494 -----------------------------------------------------------------------------
1495
1496 -- <Bug 16468041 Start>
1497 -- Rewriting the below sql due to performance issue.
1498
1499 --<Bug 15927193 Start>
1500 --BugFix:5503749 (FND_COLUMNS has the TL columns registered for the B table also hence cannot FND_COLUMNS now)
1501 /*l_dynamic_sql := ' SELECT tab.COLUMN_NAME COLUMN_NAME,' ||
1502 ' Decode(tab.DATA_TYPE,''NUMBER'',''N'', ''DATE'',''D'',''VARCHAR2'',''V'',NULL) COLUMN_TYPE '||
1503 ' FROM SYS.ALL_TAB_COLUMNS tab, ' ||
1504 'ALL_SYNONYMS syn ' ||
1505 ' WHERE tab.table_name = syn.table_name '||
1506 ' AND tab.owner = syn.table_owner '||
1507 ' AND syn.synonym_name = :1 ';*/
1508 --<Bug 15927193 End>
1509
1510 select application_id
1511 into l_app_id
1512 from fnd_application
1513 where application_short_name = 'PO' ;
1514
1515 -- Removing the edition_name join.
1516 -- Edition may not be same in fnd_tables and fnd_columns.
1517 l_dynamic_sql := ' SELECT DISTINCT col.column_name COLUMN_NAME, col.column_type COLUMN_TYPE' ||
1518 ' FROM fnd_tables tab, fnd_columns col ' ||
1519 ' WHERE col.table_id = tab.table_id ' ||
1520 ' AND tab.table_name = :1 ' ||
1521 ' AND tab.application_id = ' || l_app_id ||
1522 ' AND col.application_id = tab.application_id ';
1523
1524
1525 -- <Bug 16468041 End>
1526
1527 -----------------------------------------------------------------------------
1528 -- Appending the Condition to ignore columns in p_from_cols_to_exclude_list.
1529 -----------------------------------------------------------------------------
1530 IF (p_from_cols_to_exclude_list IS NOT NULL) THEN
1531 l_dynamic_sql := l_dynamic_sql||' AND COLUMN_NAME NOT IN ( '||p_from_cols_to_exclude_list||' )';
1532 END IF;
1533 -----------------------------------------------------------------------------
1534 -- Ordering by column_name is required as we need to map columns.
1535 -----------------------------------------------------------------------------
1536 l_dynamic_sql := l_dynamic_sql||' ORDER BY COLUMN_NAME';
1537
1538 l_in_update_mode := (p_to_table_name IS NOT NULL);
1539
1540 IF PO_LOG.d_stmt THEN
1541 PO_LOG.stmt(d_module,d_progress,'l_dynamic_sql', l_dynamic_sql);
1542 END IF;
1543
1544 OPEN l_dynamic_cursor FOR l_dynamic_sql
1545 USING p_from_table_name;
1546
1547 d_progress := 010;
1548 LOOP
1549 FETCH l_dynamic_cursor INTO l_column_name, l_column_type;
1550 EXIT WHEN l_dynamic_cursor%NOTFOUND;
1551
1552 -----------------------------------------------------------------------------
1553 -- If Its update mode then Update Section has to prepared.
1554 -- Source.Column_Name = Destination.Column_name
1555 -----------------------------------------------------------------------------
1556 IF (l_in_update_mode) THEN
1557 l_table_column_names_list := l_table_column_names_list || p_to_table_alias_prefix || '.' ||
1558 l_column_name || '= ' ;
1559 END IF;
1560
1561 IF (p_from_table_alias_prefix IS NOT NULL) THEN
1562 l_table_column_names_list := l_table_column_names_list||p_from_table_alias_prefix||'.';
1563 END IF;
1564
1565 l_table_column_names_list := l_table_column_names_list || l_column_name || ' ';
1566 l_table_column_names_list := l_table_column_names_list || ',';
1567 END LOOP;
1568 CLOSE l_dynamic_cursor;
1569
1570 d_progress := 020;
1571 IF (PO_LOG.d_proc) THEN
1572 PO_LOG.proc_end(d_module, 'l_table_column_names_list', l_table_column_names_list);
1573 PO_LOG.proc_end(d_module);
1574 END IF;
1575
1576 RETURN l_table_column_names_list;
1577
1578 END Get_Table_Columns_List;
1579
1580 -------------------------------------------------------------------------------
1581 --Start of Comments
1582 --Name: ARCHIVE_ATTRS_DATA
1583 --Pre-reqs:
1584 -- None.
1585 --Modifies:
1586 -- Extension Tables for table associated with x_template_id
1587 --Locks:
1588 -- None.
1589 --Function:
1590 -- This procedure Create Archive for User Defined Attributes within a given Entity
1591 --Parameters:
1592 --IN:
1593 -- 1. x_template_id - Document's UDA Template Id needs to passed.
1594 -- 2. x_pk_col_value_pairs
1595 -- Is the Name Value Pair Arry of type EGO_COL_NAME_VALUE_PAIR_ARRAY which need to created for all the primary Keys of Source Document.
1596 -- 3. x_revision_id
1597 -- Is the Revision Id which needs to be created.
1598 -- 4. p_commit - Flag to indentify whether the commit has to be done by the API or calling program will perform a commit.
1599 -- Possible Values are :
1600 -- 1. FND_API.G_FALSE [Default]
1601 -- 2. FND_API.G_TRUE
1602 --OUT:
1603 -- 1. x_return_status - Generic Out parameter having return status
1604 -- 2. x_msg_count - Generic Out parameter having return msg Count
1605 -- 3. x_msg_data - Generic Out parameter having return msg.
1606 --Testing:
1607 --
1608 --End of Comments
1609 -------------------------------------------------------------------------------
1610
1611 PROCEDURE Archive_Attrs_Data
1612 (
1613 x_template_id IN NUMBER
1614 ,x_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1615 ,x_revision_id IN NUMBER
1616 ,x_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1617 ,x_return_status OUT NOCOPY VARCHAR2
1618 ,x_msg_count OUT NOCOPY NUMBER
1619 ,x_msg_data OUT NOCOPY VARCHAR2
1620 )IS
1621
1622 d_api_name CONSTANT VARCHAR2(30) := 'Archive_Attrs_Data';
1623 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1624 d_progress NUMBER;
1625
1626 l_attr_group_Type VARCHAR2(300);
1627 l_object_dff_cr g_object_dff_cr;
1628 l_object FND_OBJECTS%ROWTYPE;
1629 l_main_data_level_id NUMBER;
1630 l_archive_data_level EGO_DATA_LEVEL_B%ROWTYPE;
1631 l_to_data_level_id NUMBER;
1632 l_new_dtlevel_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1633 l_old_dtlevel_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1634 l_return_status VARCHAR2(1);
1635 l_errorcode NUMBER;
1636 l_msg_count NUMBER;
1637 l_msg_data VARCHAR2(1000);
1638 l_index NUMBER;
1639 l_count NUMBER := 0;
1640 l_Rev_num_dml VARCHAR2(1000);
1641 l_where_pk_sql VARCHAR2(2000);
1642
1643 BEGIN
1644 IF (PO_LOG.d_proc) THEN
1645 PO_LOG.proc_begin(d_module);
1646 PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1647 PO_LOG.proc_begin(d_module, 'x_revision_id',x_revision_id);
1648 PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1649 END IF;
1650 d_progress := 010;
1651
1652 SELECT ENTITY_CODE
1653 INTO l_attr_group_Type
1654 FROM PO_UDA_AG_TEMPLATES
1655 WHERE TEMPLATE_ID = x_template_id;
1656
1657 IF PO_LOG.d_stmt THEN
1658 PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
1659 END IF;
1660
1661 l_object_dff_cr := g_object_dff_tl(l_attr_group_Type);
1662
1663 SELECT *
1664 INTO l_object
1665 FROM FND_OBJECTS
1666 WHERE OBJ_NAME = l_object_dff_cr.l_object_name;
1667
1668 SELECT data_level_id
1669 INTO l_main_data_level_id
1670 FROM EGO_DATA_LEVEL_B
1671 WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1672 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
1673
1674 SELECT *
1675 INTO l_archive_data_level
1676 FROM EGO_DATA_LEVEL_B
1677 WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1678 AND DATA_LEVEL_NAME LIKE '%ARCHIVE';
1679
1680 IF PO_LOG.d_stmt THEN
1681 PO_LOG.stmt(d_module,d_progress,'Main_data_level_id',l_main_data_level_id);
1682 PO_LOG.stmt(d_module,d_progress,'Archive_data_level_id',l_archive_data_level.data_level_id);
1683 END IF;
1684
1685 d_progress := 020;
1686 l_index := 0;
1687 l_index := x_pk_col_value_pairs.FIRST;
1688 WHILE (l_index <= x_pk_col_value_pairs.LAST)
1689 LOOP
1690 IF (((l_object.PK1_COLUMN_NAME IS NOT NULL) AND (x_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK1_COLUMN_NAME))) OR
1691 ((l_object.PK2_COLUMN_NAME IS NOT NULL) AND (x_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK2_COLUMN_NAME))) OR
1692 ((l_object.PK3_COLUMN_NAME IS NOT NULL) AND (x_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK3_COLUMN_NAME))) OR
1693 ((l_object.PK4_COLUMN_NAME IS NOT NULL) AND (x_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK4_COLUMN_NAME))) OR
1694 ((l_object.PK5_COLUMN_NAME IS NOT NULL) AND (x_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK5_COLUMN_NAME))) )
1695 THEN
1696 IF (x_pk_col_value_pairs(l_index).VALUE IS NOT NULL) THEN
1697 l_where_pk_sql := l_where_pk_sql ||x_pk_col_value_pairs(l_index).NAME ||' = ''' ||x_pk_col_value_pairs(l_index).VALUE ||''' AND ';
1698 END IF;
1699 END IF;
1700 l_index := x_pk_col_value_pairs.NEXT(l_index);
1701 END LOOP;
1702
1703 IF PO_LOG.d_stmt THEN
1704 PO_LOG.stmt(d_module,d_progress,'l_where_pk_sql',l_where_pk_sql);
1705 END IF;
1706
1707 d_progress := 030;
1708 l_Rev_num_dml := ' SELECT count(1) from ( '||
1709 ' SELECT * '||
1710 ' FROM '|| l_object_dff_cr.l_object_ext_b_table||
1711 ' WHERE '|| l_where_pk_sql ||
1712 ' PK1_VALUE IS NOT NULL AND PK1_VALUE = ' || x_revision_id ||
1713 ' AND ROWNUM < 2 )';
1714
1715 IF PO_LOG.d_stmt THEN
1716 PO_LOG.stmt(d_module,d_progress,'l_Rev_num_dml', l_Rev_num_dml);
1717 END IF;
1718
1719 EXECUTE IMMEDIATE l_Rev_num_dml INTO l_count;
1720
1721 IF (l_count > 0) THEN
1722 d_progress := 040;
1723 FND_MESSAGE.set_name('PO', 'PO_UDA_REV_ALLREADY_EXISTS');
1724 FND_MSG_PUB.add;
1725 RAISE FND_API.G_EXC_ERROR;
1726 END IF;
1727
1728 d_progress := 050;
1729 l_new_dtlevel_col_value_pairs :=
1730 EGO_COL_NAME_VALUE_PAIR_ARRAY(
1731 EGO_COL_NAME_VALUE_PAIR_OBJ(
1732 l_archive_data_level.PK1_COLUMN_NAME, x_revision_id
1733 )
1734 );
1735 l_old_dtlevel_col_value_pairs :=
1736 EGO_COL_NAME_VALUE_PAIR_ARRAY(
1737 EGO_COL_NAME_VALUE_PAIR_OBJ(
1738 l_archive_data_level.PK1_COLUMN_NAME, null
1739 )
1740 );
1741
1742 ----------------------------------------------------------------------------
1743 -- We are using EGO's Copy API to Copy the attribute Group Data amoung
1744 -- Data Levels for a given entity as in our case Archive is just a data-level
1745 -- along main Data level. We are Copied attribute from main data level to
1746 -- archival Data Level.
1747 ----------------------------------------------------------------------------
1748
1749 ego_user_attrs_data_pvt.copy_user_attrs_data (
1750 p_api_version => 1.0
1751 ,p_application_id => 201
1752 ,p_object_id => l_object.object_id
1753 ,p_object_name => l_object_dff_cr.l_object_name
1754 ,p_old_pk_col_value_pairs => x_pk_col_value_pairs
1755 ,p_old_data_level_id => l_main_data_level_id
1756 ,p_old_dtlevel_col_value_pairs => l_old_dtlevel_col_value_pairs
1757 ,p_new_pk_col_value_pairs => x_pk_col_value_pairs
1758 ,p_new_data_level_id => l_archive_data_level.data_level_id
1759 ,p_new_dtlevel_col_value_pairs => l_new_dtlevel_col_value_pairs
1760 ,p_commit => x_commit
1761 ,x_return_status => l_return_status
1762 ,x_errorcode => l_errorcode
1763 ,x_msg_count => l_msg_count
1764 ,x_msg_data => l_msg_data
1765 );
1766
1767 d_progress := 060;
1768
1769 IF PO_LOG.d_stmt THEN
1770 PO_LOG.stmt(d_module,d_progress,'ego_user_attrs_data_pvt.copy_user_attrs_data : Return_status : ', l_return_status);
1771 END IF;
1772
1773 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1774 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1775 x_return_status := FND_API.G_RET_STS_SUCCESS;
1776 ELSE
1777 RAISE FND_API.G_EXC_ERROR;
1778 END IF;
1779
1780 IF (PO_LOG.d_proc) THEN
1781 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782 PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1783 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1784 PO_LOG.proc_end(d_module);
1785 END IF;
1786 EXCEPTION
1787 WHEN FND_API.G_EXC_ERROR THEN
1788 d_progress := 070;
1789 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1790 IF (PO_LOG.d_exc) THEN
1791 PO_LOG.exc(d_module, d_progress, x_msg_data );
1792 PO_LOG.proc_end(d_module);
1793 END IF;
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795 WHEN OTHERS THEN
1796 d_progress := 080;
1797 PO_MESSAGE_S.add_exc_msg
1798 ( p_pkg_name => d_pkg_name,
1799 p_procedure_name => d_api_name || '.' || d_progress
1800 );
1801 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1803 END Archive_Attrs_Data;
1804
1805 -------------------------------------------------------------------------------
1806 --Start of Comments
1807 --Name: COPY_USER_ATTRS
1808 --Pre-reqs:
1809 -- None.
1810 --Modifies:
1811 -- Extension Tables for table associated with x_template_id
1812 --Locks:
1813 -- None.
1814 --Function:
1815 -- This procedure Copy User Defined Attributes within a given Entity
1816 --Parameters:
1817 --IN:
1818 -- 1. x_template_id - Document's UDA Template Id needs to pass.
1819 -- 2. x_from_pk_col_value_pairs
1820 -- Is the Name Value Pair Arry of type EGO_COL_NAME_VALUE_PAIR_ARRAY which need to created for all the primary Keys of Source Document.
1821 -- 3. x_to_pk_col_value_pairs
1822 -- Is the Name Value Pair Arry of type EGO_COL_NAME_VALUE_PAIR_ARRAY which need to created for all the primary Keys of Destination Document.
1823 -- 4. x_copy_attribute_groups - Possible Values are :
1824 -- 1. 'ALL' [Default] - Identify that all the Attribute Groups which are associated to Source Document and having a Attribute Group Mapping to a Destination Document Attribute Group and have a usage in Destination template.
1825 -- 2. SPECIFIC - Only Specific Attribute Groups which internal names are mentioned in attribute_group_table parameter.
1826 -- 3. EXCLUSIVE - All the Attribute group mentioned in attribute_group_table will be ignored.
1827 --
1828 -- 5. x_attribute_group_table - PL SQL table of Varchar30 to store Attribute Group Internal Names.
1829 -- 6. x_commit - Flag to indentify whether the commit has to be done by the API or calling program will perform a commit.
1830 -- Possible Values are :
1831 -- 1. FND_API.G_FALSE [Default]
1832 -- 2. FND_API.G_TRUE3.
1833 --OUT:
1834 -- 1. x_return_status - Generic Out parameter having return status--Testing:
1835 -- 2. x_msg_count - Generic Out parameter having return msg Count
1836 -- 3. x_msg_data - Generic Out parameter having return msg.--End of Comments
1837 -------------------------------------------------------------------------------
1838
1839 PROCEDURE Copy_User_Attrs
1840 (
1841 x_template_id IN NUMBER
1842 ,x_from_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1843 ,x_to_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1844 ,x_copy_attribute_groups IN VARCHAR2 DEFAULT 'ALL' -- SPECIFIC / EXCLUSIVE
1845 ,x_attribute_group_table IN PO_TBL_VARCHAR30 DEFAULT NULL
1846 ,x_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1847 ,x_return_status OUT NOCOPY VARCHAR2
1848 ,x_msg_count OUT NOCOPY NUMBER
1849 ,x_msg_data OUT NOCOPY VARCHAR2
1850 ) IS
1851
1852 d_api_name CONSTANT VARCHAR2(30) := 'Copy_User_Attrs';
1853 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1854 d_progress NUMBER;
1855
1856 l_attr_group_Type VARCHAR2(300);
1857 l_object_dff_cr g_object_dff_cr;
1858 l_object FND_OBJECTS%ROWTYPE;
1859 l_main_data_level EGO_DATA_LEVEL_B%ROWTYPE;
1860 l_to_data_level_id NUMBER;
1861 l_dtlevel_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1862 l_return_status VARCHAR2(1);
1863 l_errorcode NUMBER;
1864 l_msg_count NUMBER;
1865 l_msg_data VARCHAR2(1000);
1866 l_index NUMBER;
1867 l_count NUMBER := 0;
1868 l_Rev_num_dml VARCHAR2(1000);
1869 l_where_pk_sql VARCHAR2(2000);
1870 l_where_ag_sql VARCHAR2(2000) := '';
1871 l_from_where_pk_sql VARCHAR2(2000);
1872 l_to_where_pk_sql VARCHAR2(2000);
1873 l_attr_group_id NUMBER;
1874 l_attr_grp_ids_tbl po_tbl_NUMBER;
1875 l_attr_grp_ids_list VARCHAR2(1000) := '';
1876 l_get_attr_list_sql VARCHAR2(4000);
1877
1878 BEGIN
1879 d_progress := 010;
1880 IF (PO_LOG.d_proc) THEN
1881 PO_LOG.proc_begin(d_module);
1882 PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1883 PO_LOG.proc_begin(d_module, 'x_copy_attribute_groups',x_copy_attribute_groups);
1884 PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1885 END IF;
1886
1887 SELECT ENTITY_CODE
1888 INTO l_attr_group_Type
1889 FROM PO_UDA_AG_TEMPLATES
1890 WHERE TEMPLATE_ID = x_template_id;
1891
1892 IF PO_LOG.d_stmt THEN
1893 PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
1894 END IF;
1895
1896 d_progress := 020;
1897 l_object_dff_cr := g_object_dff_tl(l_attr_group_Type);
1898
1899 SELECT *
1900 INTO l_object
1901 FROM FND_OBJECTS
1902 WHERE OBJ_NAME = l_object_dff_cr.l_object_name;
1903
1904 SELECT *
1905 INTO l_main_data_level
1906 FROM EGO_DATA_LEVEL_B
1907 WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1908 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
1909
1910 IF PO_LOG.d_stmt THEN
1911 PO_LOG.stmt(d_module,d_progress,'Main_data_level',l_main_data_level.data_level_id);
1912 END IF;
1913
1914 d_progress := 030;
1915
1916 l_index := 0;
1917 l_index := x_from_pk_col_value_pairs.FIRST;
1918 WHILE (l_index <= x_from_pk_col_value_pairs.LAST)
1919 LOOP
1920 IF (((l_object.PK1_COLUMN_NAME IS NOT NULL) AND (x_from_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK1_COLUMN_NAME))) OR
1921 ((l_object.PK2_COLUMN_NAME IS NOT NULL) AND (x_from_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK2_COLUMN_NAME))) OR
1922 ((l_object.PK3_COLUMN_NAME IS NOT NULL) AND (x_from_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK3_COLUMN_NAME))) OR
1923 ((l_object.PK4_COLUMN_NAME IS NOT NULL) AND (x_from_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK4_COLUMN_NAME))) OR
1924 ((l_object.PK5_COLUMN_NAME IS NOT NULL) AND (x_from_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK5_COLUMN_NAME))) )
1925 THEN
1926 IF (x_from_pk_col_value_pairs(l_index).VALUE IS NOT NULL) THEN
1927 l_from_where_pk_sql := l_from_where_pk_sql ||x_from_pk_col_value_pairs(l_index).NAME ||' = ''' ||x_from_pk_col_value_pairs(l_index).VALUE ||''' AND ';
1928 END IF;
1929 ELSE
1930 FND_MESSAGE.set_name('PO', 'PO_UDA_PK_COL_NAME_ERR');
1931 FND_MSG_PUB.add;
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 l_index := x_from_pk_col_value_pairs.NEXT(l_index);
1935 END LOOP;
1936
1937 l_from_where_pk_sql := SUBSTR(l_from_where_pk_sql, 1, LENGTH(l_from_where_pk_sql) - LENGTH(' AND'));
1938
1939
1940 IF PO_LOG.d_stmt THEN
1941 PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1942 END IF;
1943
1944 l_index := 0;
1945 l_index := x_to_pk_col_value_pairs.FIRST;
1946 WHILE (l_index <= x_to_pk_col_value_pairs.LAST)
1947 LOOP
1948 IF (((l_object.PK1_COLUMN_NAME IS NOT NULL) AND (x_to_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK1_COLUMN_NAME))) OR
1949 ((l_object.PK2_COLUMN_NAME IS NOT NULL) AND (x_to_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK2_COLUMN_NAME))) OR
1950 ((l_object.PK3_COLUMN_NAME IS NOT NULL) AND (x_to_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK3_COLUMN_NAME))) OR
1951 ((l_object.PK4_COLUMN_NAME IS NOT NULL) AND (x_to_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK4_COLUMN_NAME))) OR
1952 ((l_object.PK5_COLUMN_NAME IS NOT NULL) AND (x_to_pk_col_value_pairs(l_index).NAME = Upper(l_object.PK5_COLUMN_NAME))) )
1953 THEN
1954 IF (x_to_pk_col_value_pairs(l_index).VALUE IS NOT NULL) THEN
1955 l_to_where_pk_sql := l_to_where_pk_sql ||x_to_pk_col_value_pairs(l_index).NAME ||' = ''' ||x_to_pk_col_value_pairs(l_index).VALUE ||''' AND ';
1956 END IF;
1957 END IF;
1958 l_index := x_to_pk_col_value_pairs.NEXT(l_index);
1959 END LOOP;
1960
1961 l_to_where_pk_sql := SUBSTR(l_to_where_pk_sql, 1, LENGTH(l_to_where_pk_sql) - LENGTH(' AND'));
1962
1963 IF PO_LOG.d_stmt THEN
1964 PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1965 END IF;
1966
1967 d_progress := 040;
1968 IF ( (x_copy_attribute_groups = 'SPECIFIC') OR ( x_copy_attribute_groups = 'EXCLUSIVE') ) THEN
1969 FOR i IN 1..x_attribute_group_table.Count LOOP
1970
1971 BEGIN
1972 SELECT attr_group_id
1973 INTO l_attr_group_id
1974 FROM EGO_ATTR_GROUPS_V
1975 WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1976 AND ATTR_GROUP_NAME = x_attribute_group_table(i);
1977 EXCEPTION
1978 WHEN No_Data_Found THEN
1979 FND_MESSAGE.set_name('PO', 'PO_UDA_WRONG_ATTRIBUTE_GROUP');
1980 FND_MSG_PUB.add;
1981 RAISE FND_API.G_EXC_ERROR;
1982 END ;
1983
1984 l_where_ag_sql := l_where_ag_sql || l_attr_group_id || ',' ;
1985
1986 END LOOP;
1987
1988 IF (LENGTH(l_where_ag_sql) > 0) THEN
1989 l_where_ag_sql := RTRIM(l_where_ag_sql, ',');
1990 END IF;
1991
1992 IF ( x_copy_attribute_groups = 'SPECIFIC') THEN
1993 l_where_ag_sql := ' AND ATTR_GROUP_ID IN ( '|| l_where_ag_sql || ' )';
1994 ELSE IF ( x_copy_attribute_groups = 'EXCLUSIVE') THEN
1995 l_where_ag_sql := ' AND ATTR_GROUP_ID NOT IN ( '|| l_where_ag_sql || ' )';
1996 END IF ;
1997
1998 END IF;
1999 IF PO_LOG.d_stmt THEN
2000 PO_LOG.stmt(d_module,d_progress,'l_where_ag_sql',l_where_ag_sql);
2001 END IF;
2002 END IF ;
2003
2004 d_progress := 050;
2005
2006 /*
2007 l_Rev_num_dml := ' SELECT count(1) from ( '||
2008 ' SELECT * '||
2009 ' FROM '|| l_object_dff_cr.l_object_ext_b_table||
2010 ' WHERE '|| l_to_where_pk_sql ||
2011 l_where_ag_sql ||
2012 ' AND ROWNUM < 2 )';
2013
2014 IF PO_LOG.d_stmt THEN
2015 PO_LOG.stmt(d_module,d_progress,'l_Rev_num_dml', l_Rev_num_dml);
2016 END IF;
2017
2018 EXECUTE IMMEDIATE l_Rev_num_dml INTO l_count;
2019
2020 IF (l_count > 0) THEN
2021 d_progress := 060;
2022 FND_MESSAGE.set_name('PO', 'PO_UDA_RECORD_ALLREADY_EXISTS');
2023 FND_MSG_PUB.add;
2024 RAISE FND_API.G_EXC_ERROR;
2025 END IF; */
2026
2027 l_get_attr_list_sql := 'SELECT UNIQUE ATTR_GROUP_ID
2028 FROM ' || l_object_dff_cr.l_object_ext_b_table || ' SRC
2029 WHERE '|| l_from_where_pk_sql || l_where_ag_sql ||
2030 ' AND NOT EXISTS
2031 (SELECT 1 FROM '|| l_object_dff_cr.l_object_ext_b_table ||' DEST WHERE ' || l_to_where_pk_sql||' AND DEST.ATTR_GROUP_ID = SRC.ATTR_GROUP_ID )';
2032
2033
2034 EXECUTE IMMEDIATE l_get_attr_list_sql BULK COLLECT INTO l_attr_grp_ids_tbl;
2035
2036 FOR i IN 1..l_attr_grp_ids_tbl.Count LOOP
2037
2038 l_attr_grp_ids_list := l_attr_grp_ids_list || l_attr_grp_ids_tbl(i) || ',';
2039
2040 END LOOP ;
2041
2042 IF (LENGTH(l_attr_grp_ids_list) > 0) THEN
2043 l_attr_grp_ids_list := RTRIM(l_attr_grp_ids_list, ',');
2044 ELSE -- bug16361247: No UDA data to copy
2045 x_return_status := FND_API.G_RET_STS_SUCCESS;
2046
2047 d_progress := 060;
2048 IF PO_LOG.d_stmt THEN
2049 PO_LOG.stmt(d_module,d_progress,'No UDA Data to copy. Returning with Return_status : ', x_return_status);
2050 END IF;
2051
2052 RETURN;
2053 END IF;
2054
2055
2056 l_dtlevel_col_value_pairs :=
2057 EGO_COL_NAME_VALUE_PAIR_ARRAY(
2058 EGO_COL_NAME_VALUE_PAIR_OBJ(
2059 'PK1_VALUE', null
2060 )
2061 );
2062 d_progress := 070;
2063 ego_user_attrs_data_pvt.copy_user_attrs_data (
2064 p_api_version => 1.0
2065 ,p_application_id => 201
2066 ,p_object_id => l_object.object_id
2067 ,p_object_name => l_object_dff_cr.l_object_name
2068 ,p_old_pk_col_value_pairs => x_from_pk_col_value_pairs
2069 ,p_old_data_level_id => l_main_data_level.data_level_id
2070 ,p_old_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs
2071 ,p_new_pk_col_value_pairs => x_to_pk_col_value_pairs
2072 ,p_new_data_level_id => l_main_data_level.data_level_id
2073 ,p_new_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs
2074 ,p_attr_group_list => l_attr_grp_ids_list
2075 ,p_commit => x_commit
2076 ,x_return_status => l_return_status
2077 ,x_errorcode => l_errorcode
2078 ,x_msg_count => l_msg_count
2079 ,x_msg_data => l_msg_data
2080 );
2081 IF PO_LOG.d_stmt THEN
2082 PO_LOG.stmt(d_module,d_progress,'ego_user_attrs_data_pvt.copy_user_attrs_data : Return_status : ', l_return_status);
2083 END IF;
2084 d_progress := 080;
2085 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2086 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2087 x_return_status := FND_API.G_RET_STS_SUCCESS;
2088 ELSE
2089 RAISE FND_API.G_EXC_ERROR;
2090 END IF;
2091
2092 IF (PO_LOG.d_proc) THEN
2093 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2094 PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
2095 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2096 PO_LOG.proc_end(d_module);
2097 END IF;
2098
2099 EXCEPTION
2100 WHEN FND_API.G_EXC_ERROR THEN
2101 d_progress := 090;
2102 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2103 IF (PO_LOG.d_exc) THEN
2104 PO_LOG.exc(d_module, d_progress, x_msg_data );
2105 PO_LOG.proc_end(d_module);
2106 END IF;
2107 x_return_status := FND_API.G_RET_STS_ERROR;
2108 WHEN OTHERS THEN
2109 d_progress := 100;
2110 PO_MESSAGE_S.add_exc_msg
2111 ( p_pkg_name => d_pkg_name,
2112 p_procedure_name => d_api_name || '.' || d_progress
2113 );
2114 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2116 END Copy_User_Attrs;
2117
2118 PROCEDURE Merge_Amendment_Address_AG
2119 (
2120 x_amd_requisition_header_id IN NUMBER
2121 ,x_requisition_header_id IN NUMBER
2122 ,x_return_status OUT NOCOPY VARCHAR2
2123 ) IS
2124
2125 d_api_name CONSTANT VARCHAR2(30) := 'Merge_Amendment_Address_AG';
2126 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2127 d_progress NUMBER;
2128
2129 amd_address_row PO_REQ_HEADERS_EXT_B%ROWTYPE ;
2130 TYPE amd_address_row_table IS TABLE OF PO_REQ_HEADERS_EXT_B%ROWTYPE INDEX BY BINARY_INTEGER;
2131 amd_address_rows amd_address_row_table;
2132
2133 amd_tl_address_row PO_REQ_HEADERS_EXT_TL%ROWTYPE ;
2134 TYPE amd_tl_address_row_table IS TABLE OF PO_REQ_HEADERS_EXT_TL%ROWTYPE INDEX BY BINARY_INTEGER;
2135 amd_tl_address_rows amd_tl_address_row_table;
2136 l_attr_group_id NUMBER;
2137
2138 --Bug9441838: Added variable to hold the sequence value
2139 x_association_id NUMBER;
2140 BEGIN
2141
2142 d_progress := 010;
2143 IF (PO_LOG.d_proc) THEN
2144 PO_LOG.proc_begin(d_module);
2145 PO_LOG.proc_begin(d_module, 'x_mod_requisition_header_id', x_amd_requisition_header_id);
2146 PO_LOG.proc_begin(d_module, 'x_requisition_header_id',x_requisition_header_id);
2147 END IF;
2148
2149 SELECT ATTR_GROUP_ID
2150 INTO l_attr_group_id
2151 FROM EGO_ATTR_GROUPS_V
2152 WHERE ATTR_GROUP_TYPE = 'PO_REQ_HEADER_EXT_ATTRS'
2153 AND ATTR_GROUP_NAME = 'addresses';
2154
2155
2156 SELECT * BULK COLLECT INTO amd_address_rows FROM po_req_headers_ext_b
2157 WHERE requisition_header_id = x_amd_requisition_header_id
2158 AND ATTR_GROUP_ID = l_attr_group_id;
2159
2160 FOR row_b IN 1 .. amd_address_rows.Count LOOP
2161 -- First Fetch the TL records for the current Extension ID
2162 SELECT * BULK COLLECT INTO amd_tl_address_rows FROM po_req_headers_ext_tl
2163 WHERE requisition_header_id = x_amd_requisition_header_id
2164 AND ATTR_GROUP_ID = l_attr_group_id
2165 AND extension_id = amd_address_rows(row_b).extension_id;
2166
2167
2168 -- Updating the required information.
2169 SELECT EGO_EXTFWK_S.NEXTVAL
2170 INTO amd_address_rows(row_b).extension_id
2171 FROM sys.dual;
2172
2173 amd_address_rows(row_b).requisition_header_id := x_requisition_header_id;
2174 amd_address_rows(row_b).c_ext_attr40 := 'PR_UDA_ADDRESS_TYPES';
2175
2176 FOR row_tl IN 1 .. amd_tl_address_rows.Count LOOP
2177
2178 amd_tl_address_rows(row_tl).extension_id := amd_address_rows(row_b).extension_id;
2179 amd_tl_address_rows(row_tl).requisition_header_id := x_requisition_header_id;
2180
2181 UPDATE po_req_headers_ext_tl SET ROW = amd_tl_address_rows(row_tl)
2182 WHERE extension_id IN ( SELECT extension_id FROM po_req_headers_ext_b
2183 WHERE C_EXT_ATTR39 = amd_address_rows(row_b).C_EXT_ATTR39
2184 AND attr_group_id = l_attr_group_id
2185 AND requisition_header_id = x_requisition_header_id)
2186 AND po_req_headers_ext_tl.LANGUAGE = amd_tl_address_rows(row_tl).LANGUAGE;
2187
2188 END LOOP;
2189
2190 UPDATE po_req_headers_ext_b SET ROW = amd_address_rows(row_b)
2191 WHERE C_EXT_ATTR39 = amd_address_rows(row_b).C_EXT_ATTR39
2192 AND attr_group_id = l_attr_group_id
2193 AND requisition_header_id = x_requisition_header_id;
2194
2195 END LOOP;
2196
2197 x_return_status := FND_API.G_RET_STS_SUCCESS;
2198 IF (PO_LOG.d_proc) THEN
2199 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2200 PO_LOG.proc_end(d_module);
2201 END IF;
2202 EXCEPTION
2203 WHEN OTHERS THEN
2204 d_progress := 100;
2205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2206 IF (PO_LOG.d_exc) THEN
2207 PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2208 PO_LOG.proc_end(d_module);
2209 END IF;
2210 END Merge_Amendment_Address_AG ;
2211
2212 PROCEDURE Merge_Mod_Address_AG
2213 (
2214 p_po_header_id IN NUMBER
2215 ,p_draft_id IN NUMBER
2216 ,x_return_status OUT NOCOPY VARCHAR2
2217 ) IS
2218
2219 d_api_name CONSTANT VARCHAR2(30) := 'Merge_Mod_Address_AG';
2220 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2221 d_progress NUMBER;
2222
2223 po_address_row PO_HEADERS_ALL_EXT_B%ROWTYPE ;
2224 TYPE po_address_row_table IS TABLE OF PO_HEADERS_ALL_EXT_B%ROWTYPE INDEX BY BINARY_INTEGER;
2225 po_address_rows po_address_row_table;
2226
2227 po_tl_address_row PO_HEADERS_ALL_EXT_TL%ROWTYPE ;
2228 TYPE po_tl_address_row_table IS TABLE OF PO_HEADERS_ALL_EXT_TL%ROWTYPE INDEX BY BINARY_INTEGER;
2229 po_tl_address_rows po_tl_address_row_table;
2230 l_attr_group_id NUMBER;
2231 BEGIN
2232
2233 d_progress := 010;
2234 IF (PO_LOG.d_proc) THEN
2235 PO_LOG.proc_begin(d_module);
2236 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2237 PO_LOG.proc_begin(d_module, 'p_draft_id',p_draft_id);
2238 END IF;
2239
2240 SELECT ATTR_GROUP_ID
2241 INTO l_attr_group_id
2242 FROM EGO_ATTR_GROUPS_V
2243 WHERE ATTR_GROUP_TYPE = 'PO_HEADER_EXT_ATTRS'
2244 AND ATTR_GROUP_NAME = 'addresses';
2245
2246 SELECT * BULK COLLECT INTO po_address_rows FROM po_headers_all_ext_b
2247 WHERE po_header_id = p_po_header_id
2248 AND ATTR_GROUP_ID = l_attr_group_id
2249 AND draft_id = p_draft_id;
2250
2251 FOR row_b IN 1 .. po_address_rows.Count LOOP
2252 -- First Fetch the TL records for the current Extension ID
2253 SELECT * BULK COLLECT INTO po_tl_address_rows FROM po_headers_all_ext_tl
2254 WHERE po_header_id = p_po_header_id
2255 AND draft_id = p_draft_id
2256 AND ATTR_GROUP_ID = l_attr_group_id
2257 AND extension_id = po_address_rows(row_b).extension_id;
2258
2259
2260 -- Updating the required information.
2261 SELECT EGO_EXTFWK_S.NEXTVAL
2262 INTO po_address_rows(row_b).extension_id
2263 FROM sys.dual;
2264
2265 po_address_rows(row_b).po_header_id := p_po_header_id;
2266 po_address_rows(row_b).draft_id := -1;
2267 po_address_rows(row_b).c_ext_attr40 := 'PO_UDA_ADDRESS_TYPES';
2268
2269 FOR row_tl IN 1 .. po_tl_address_rows.Count LOOP
2270
2271 po_tl_address_rows(row_tl).extension_id := po_address_rows(row_b).extension_id;
2272 po_tl_address_rows(row_tl).po_header_id := p_po_header_id;
2273 po_tl_address_rows(row_tl).draft_id := -1;
2274
2275 UPDATE po_headers_all_ext_tl SET ROW = po_tl_address_rows(row_tl)
2276 WHERE extension_id IN ( SELECT extension_id FROM po_headers_all_ext_b
2277 WHERE C_EXT_ATTR39 = po_address_rows(row_b).C_EXT_ATTR39
2278 AND attr_group_id = l_attr_group_id
2279 AND po_header_id = p_po_header_id
2280 AND draft_id = -1)
2281 AND po_headers_all_ext_tl.LANGUAGE = po_tl_address_rows(row_tl).LANGUAGE;
2282
2283 END LOOP;
2284
2285 UPDATE po_headers_all_ext_b SET ROW = po_address_rows(row_b)
2286 WHERE C_EXT_ATTR39 = po_address_rows(row_b).C_EXT_ATTR39
2287 AND attr_group_id = l_attr_group_id
2288 AND po_header_id = p_po_header_id
2289 AND draft_id = -1 ;
2290
2291 END LOOP;
2292
2293 x_return_status := FND_API.G_RET_STS_SUCCESS;
2294 IF (PO_LOG.d_proc) THEN
2295 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2296 PO_LOG.proc_end(d_module);
2297 END IF;
2298 EXCEPTION
2299 WHEN OTHERS THEN
2300 d_progress := 100;
2301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2302 IF (PO_LOG.d_exc) THEN
2303 PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2304 PO_LOG.proc_end(d_module);
2305 END IF;
2306 END Merge_Mod_Address_AG ;
2307
2308
2309 PROCEDURE get_attr_group_ids(
2310 p_object_name IN VARCHAR2,
2311 p_where_clause IN VARCHAR2,
2312 p_attr_grp_type IN VARCHAR2,
2313 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER
2314 ) IS
2315
2316 TYPE ref_cursor IS REF CURSOR;
2317 c_attr_grp_id ref_cursor;
2318 c_count_num ref_cursor;
2319
2320 query VARCHAR2(8000);
2321 count_query VARCHAR2(10000);
2322 l_count NUMBER;
2323 j NUMBER ;
2324 i NUMBER := 1;
2325
2326 l_attr_grp_id PO_HEADERS_ALL_EXT_B.ATTR_GRouP_ID%TYPE;
2327 BEGIN
2328
2329 query := 'SELECT attr_group_id FROM '|| g_object_dff_tl(p_attr_grp_type).l_object_ext_b_table || ' WHERE ' || p_where_clause || ' AND pk1_value IS NULL';
2330 count_query := 'SELECT count(*) FROM '|| g_object_dff_tl(p_attr_grp_type).l_object_ext_b_table || ' WHERE ' || p_where_clause || ' AND pk1_value IS NULL';
2331
2332 OPEN c_count_num FOR count_query;
2333 FETCH c_count_num into l_count;
2334 CLOSE c_count_num;
2335
2336 x_attr_grp_ids := PO_TBL_NUMBER();
2337 x_attr_grp_ids.extend(l_count);
2338
2339 OPEN c_attr_grp_id FOR (query);
2340
2341 FOR i IN 1.. l_count
2342 LOOP
2343 FETCH c_attr_grp_id INTO l_attr_grp_id;
2344 x_attr_grp_ids(i) := l_attr_grp_id;
2345 exit when c_attr_grp_id%notfound;
2346 END LOOP;
2347 END get_attr_group_ids;
2348
2349 Function get_template_id (
2350 p_functional_area IN VARCHAR2,
2351 p_document_type IN VARCHAR2,
2352 p_document_style_id IN NUMBER,
2353 p_document_level IN VARCHAR2,
2354 p_input_date IN DATE,
2355 x_return_status OUT NOCOPY VARCHAR2,
2356 x_err_msg OUT NOCOPY VARCHAR2
2357 ) RETURN NUMBER IS
2358
2359 l_template_id NUMBER;
2360
2361 d_api_name CONSTANT VARCHAR2(30) := 'get_template_id';
2362 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2363 d_progress NUMBER := 0;
2364
2365 BEGIN
2366 IF (PO_LOG.d_proc) THEN
2367 PO_LOG.proc_begin(d_module);
2368 PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370 PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371 PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2372 PO_LOG.proc_begin(d_module, 'p_input_date', To_Char(p_input_date, 'mm/dd/yyyy hh24:mi:ss'));
2373 END IF;
2374 -- the standard style (style_id = 1)is used for seeding. However UDAs are not enabled for the standard style.
2375 IF p_document_style_id = 1 THEN
2376
2377 IF (PO_LOG.d_proc) THEN
2378 PO_LOG.proc_end(d_module);
2379 END IF;
2380
2381 x_return_status := FND_API.G_RET_STS_ERROR;
2382 x_err_msg := 'PO_UDA_TEMPLATE_NOT_FOUND';
2383 RETURN NULL;
2384 END IF;
2385
2386 SELECT template_id
2387 INTO l_template_id
2388 FROM po_uda_ag_templates
2389 WHERE functional_area = p_functional_area
2390 AND document_level = p_document_level
2391 AND Nvl(document_type, '@@@') = Nvl(p_document_type, '@@@')
2392 AND Nvl(document_style_id , -1) = Nvl(p_document_style_id, -1)
2393 AND p_input_date >= effective_from
2394 AND ( effective_to IS NULL
2395 OR
2396 p_input_date < effective_to
2397 )
2398 AND compiled_flag = 'Y';
2399
2400 IF (PO_LOG.d_proc) THEN
2401 PO_LOG.proc_end(d_module);
2402 END IF;
2403
2404 x_return_status := FND_API.G_RET_STS_SUCCESS;
2405 RETURN l_template_id;
2406
2407
2408 EXCEPTION
2409 WHEN OTHERS THEN
2410 x_err_msg := 'PO_UDA_TEMPLATE_NOT_FOUND';
2411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2412
2413 IF PO_LOG.d_stmt THEN
2414 PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2415 END IF;
2416
2417 IF (PO_LOG.d_proc) THEN
2418 PO_LOG.proc_end(d_module);
2419 END IF;
2420
2421 RETURN NULL;
2422
2423 END get_template_id;
2424
2425 PROCEDURE Execute_uda_Function(
2426 p_Action_Id IN NUMBER
2427 ,p_template_id IN NUMBER
2428 ,p_attribute_group_id IN NUMBER
2429 ,p_event IN VARCHAR2
2430 ,x_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2431 ,x_attribute_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
2432 ,x_attribute_data_table IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
2433 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
2434 ,x_return_status OUT NOCOPY VARCHAR2
2435 ,x_errorcode OUT NOCOPY NUMBER
2436 ,x_msg_count OUT NOCOPY NUMBER
2437 ,x_msg_data OUT NOCOPY VARCHAR2
2438 )
2439 IS
2440
2441 d_api_name CONSTANT VARCHAR2(30) := 'Execute_uda_Function';
2442 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2443 d_progress NUMBER := 0;
2444
2445 l_action_id NUMBER;
2446 l_dtlevel_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2447 l_return_status VARCHAR2(10);
2448 l_errorcode NUMBER;
2449 l_msg_count NUMBER;
2450 l_msg_data VARCHAR2(2000);
2451 p_attribute_row_table EGO_USER_ATTR_ROW_TABLE ;
2452 p_attribute_data_table EGO_USER_ATTR_DATA_TABLE;
2453
2454
2455 CURSOR c_get_action_id(p_template_id NUMBER, p_attribute_group_id NUMBER, p_event VARCHAR2) IS
2456 SELECT ATTRACT.ACTION_ID
2457 FROM EGO_ACTION_DISPLAYS_B ATTRACTDISP,
2458 EGO_ACTIONS_B ATTRACT
2459 WHERE ATTRACT.ACTION_ID = ATTRACTDISP.ACTION_ID
2460 AND ATTRACT.ATTR_GROUP_ID = p_attribute_group_id
2461 AND ATTRACTDISP.EXECUTION_METHOD = p_event
2462 AND ATTRACT.CLASSIFICATION_CODE = p_template_id ;
2463
2464 BEGIN
2465 IF (PO_LOG.d_proc) THEN
2466 PO_LOG.proc_begin(d_module);
2467 PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2470 PO_LOG.proc_begin(d_module, 'p_event', p_event);
2471 END IF;
2472
2473 IF(p_action_id IS NOT NULL) THEN
2474 l_action_id := p_action_id;
2475 ELSE
2476 OPEN c_get_action_id(p_template_id, p_attribute_group_id, p_event);
2477 FETCH c_get_action_id INTO l_action_id;
2478 CLOSE c_get_action_id;
2479 END IF;
2480
2481 IF ( l_action_id IS NOT NULL ) THEN
2482
2483 IF PO_LOG.d_stmt THEN
2484 PO_LOG.stmt(d_module,d_progress,'Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2485 PO_LOG.stmt(d_module,d_progress,'Executing UDA Function for Action Id : '|| l_action_id );
2486 END IF;
2487
2488 ego_ext_fwk_pub.Execute_Function(
2489 p_Action_Id => l_action_id
2490 ,p_pk_col_value_pairs => x_pk_col_value_pairs
2491 ,p_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs
2492 ,x_attributes_row_table => x_attribute_row_table
2493 ,x_attributes_data_table => x_attribute_data_table
2494 ,x_external_attrs_value_pairs => x_external_attr_value_pairs
2495 ,x_return_status => l_return_status
2496 ,x_errorcode => l_errorcode
2497 ,x_msg_count => l_msg_count
2498 ,x_msg_data => l_msg_data
2499 );
2500 x_return_status := l_return_status;
2501 x_errorcode := l_errorcode;
2502 x_msg_count := l_msg_count;
2503 x_msg_data := l_msg_data;
2504 ELSE
2505 x_return_status := FND_API.G_RET_STS_SUCCESS;
2506 IF PO_LOG.d_stmt THEN
2507 PO_LOG.stmt(d_module,d_progress,'No Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2508 END IF;
2509 END IF;
2510
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513
2514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2515
2516 IF PO_LOG.d_stmt THEN
2517 PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2518 END IF;
2519
2520 IF (PO_LOG.d_proc) THEN
2521 PO_LOG.proc_end(d_module);
2522 END IF;
2523
2524 END Execute_uda_Function;
2525
2526 /*
2527 mode take two possible values - 'INTERNAL_VALUE', 'DISPLAY_VALUE'
2528
2529 FUNCTION GET_SINGLE_ATTR_VALUE (
2530 p_template_id IN NUMBER,
2531 pk1_value IN NUMBER,
2532 pk2_value IN NUMBER,
2533 pk3_value IN NUMBER,
2534 pk4_value IN NUMBER,
2535 pk5_value IN NUMBER,
2536 p_attr_grp_id IN NUMBER,
2537 p_attr_grp_int_name IN VARCHAR2,
2538 p_attr_id IN NUMBER,
2539 p_attr_int_name IN VARCHAR2,
2540 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE',
2541 x_return_status OUT NOCOPY VARCHAR2,
2542 x_msg_data OUT NOCOPY VARCHAR2
2543 ) RETURN VARCHAR2
2544 is
2545
2546 ATTR_GRP_REQD EXCEPTION;
2547 ATTR_REQD EXCEPTION;
2548 d_api_name CONSTANT VARCHAR2(30) := 'GET_SINGLE_ATTR_VALUE';
2549 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2550 d_progress NUMBER;
2551 l_attr_group_Type VARCHAR2(50);
2552 l_ext_table_name VARCHAR2(100);
2553 l_db_object_name VARCHAR2(100);
2554 l_attr_grp_id NUMBER;
2555 l_attr_grp_name varchar2(100);
2556 l_db_col_name varchar2(100);
2557 l_sql_stmt varchar2(1000);
2558 l_attr_value varchar2(4000);
2559 l_attr_disp_value varchar2(4000);
2560 l_application_id number;
2561
2562
2563 BEGIN
2564 x_return_status := FND_API.G_RET_STS_SUCCESS;
2565 d_progress := 010;
2566 IF (PO_LOG.d_proc) THEN
2567 PO_LOG.proc_begin(d_module);
2568 PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569 PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570 PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571 PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572 PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573 PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574 PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575 PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576 PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577 PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2578 PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
2579 END IF;
2580
2581 SELECT ENTITY_CODE
2582 INTO l_attr_group_Type
2583 FROM PO_UDA_AG_TEMPLATES
2584 WHERE TEMPLATE_ID = p_template_id;
2585
2586 IF PO_LOG.d_stmt THEN
2587 PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
2588 END IF;
2589
2590 d_progress := 020;
2591 l_ext_table_name := g_object_dff_tl(l_attr_group_Type).l_object_ext_b_table;
2592 l_db_object_name := g_object_dff_tl(l_attr_group_Type).l_object_name;
2593
2594 -- if attr grp id is supplied, get the name. If the attr grp name is supplied, get the id
2595 d_progress := 030;
2596 begin
2597 if p_attr_grp_id is not null then
2598 l_attr_grp_id := p_attr_grp_id;
2599
2600 select attr_group_name
2601 into l_attr_grp_name
2602 from ego_attr_groups_v
2603 where ATTR_GROUP_TYPE = l_attr_group_Type
2604 and attr_group_id = l_attr_grp_id
2605 AND MULTI_ROW_CODE = 'N';
2606
2607 elsif p_attr_grp_int_name is not null then
2608 l_attr_grp_name := p_attr_grp_int_name;
2609
2610 select attr_group_id
2611 into l_attr_grp_id
2612 from ego_attr_groups_v
2613 where ATTR_GROUP_TYPE = l_attr_group_Type
2614 and attr_group_name = l_attr_grp_name
2615 AND MULTI_ROW_CODE = 'N';
2616
2617 else
2618 raise ATTR_GRP_REQD;
2619 end if;
2620 exception
2621 when no_data_found then
2622 po_log.exc('At '|| d_progress ||': invalid attribute group');
2623 raise ATTR_GRP_REQD;
2624 end;
2625
2626 IF PO_LOG.d_stmt THEN
2627 PO_LOG.stmt(d_module,d_progress,'l_attr_grp_id',l_attr_grp_id);
2628 PO_LOG.stmt(d_module,d_progress,'l_attr_grp_name',l_attr_grp_name);
2629 END IF;
2630
2631 -- next hit ego_attrs_v to get the db column name
2632 d_progress := 040;
2633 begin
2634 if p_attr_id is not null then
2635
2636 select database_column, application_id, data_type_code
2637 into l_db_col_name, l_application_id, l_data_type
2638 from ego_attrs_v
2639 where attr_id = p_attr_id
2640 and ATTR_GROUP_NAME = l_attr_grp_name
2641 and ATTR_GROUP_TYPE = l_attr_group_Type;
2642
2643 elsif p_attr_int_name is not null then
2644 select database_column
2645 into l_db_col_name
2646 from ego_attrs_v
2647 where ATTR_NAME = p_attr_int_name
2648 and ATTR_GROUP_NAME = l_attr_grp_name
2649 and ATTR_GROUP_TYPE = l_attr_group_Type;
2650
2651 else
2652 raise ATTR_REQD;
2653 end if;
2654 exception
2655 when no_data_found then
2656 po_log.exc('At '|| d_progress ||': invalid attribute');
2657 raise ATTR_REQD;
2658 end;
2659 IF PO_LOG.d_stmt THEN
2660 PO_LOG.stmt(d_module,d_progress,'l_db_col_name',l_db_col_name);
2661 END IF;
2662
2663 -- construct the sql query on the ext table
2664 d_progress := 050;
2665 l_sql_stmt := 'select '|| l_db_col_name ||' from '||l_ext_table_name
2666 ||' where attribute_group_id = '|| l_attr_grp_id ;
2667 -- construct the primary key part
2668 select pk1_column_name, pk2_column_name, pk3_column_name, pk4_column_name, pk5_column_name
2669 into l_pk1_col_name, l_pk2_col_name, l_pk3_col_name, l_pk4_col_name, l_pk5_col_name
2670 from fnd_objects
2671 where obj_name = l_db_object_name
2672 and rownum < 2;
2673
2674 if l_pk1_col_name is not null then
2675 l_sql_stmt := l_sql_stmt ||' and '||l_pk1_col_name ||' = '|| pk1_value;
2676 end if;
2677 if l_pk2_col_name is not null then
2678 l_sql_stmt := l_sql_stmt ||' and '||l_pk2_col_name ||' = '|| pk2_value;
2679 end if;
2680 if l_pk3_col_name is not null then
2681 l_sql_stmt := l_sql_stmt ||' and '||l_pk3_col_name ||' = '|| pk3_value;
2682 end if;
2683 if l_pk4_col_name is not null then
2684 l_sql_stmt := l_sql_stmt ||' and '||l_pk4_col_name ||' = '|| pk4_value;
2685 end if;
2686 if l_pk5_col_name is not null then
2687 l_sql_stmt := l_sql_stmt ||' and '||l_pk5_col_name ||' = '|| pk5_value;
2688 end if;
2689 IF PO_LOG.d_stmt THEN
2690 PO_LOG.stmt(d_module,d_progress,'l_sql_stmt',l_sql_stmt);
2691 END IF;
2692
2693 -- execute the query to get the attribute value
2694 d_progress := 060;
2695 execute immediate l_sql_stmt into l_attr_value;
2696 IF PO_LOG.d_stmt THEN
2697 PO_LOG.stmt(d_module,d_progress,'l_attr_value',substr(l_attr_value, 1, 2000));
2698 END IF;
2699
2700 if p_mode = 'INTERNAL_VALUE' then
2701 return l_attr_value;
2702 end if;
2703
2704 -- call ego api to get the display value
2705 d_progress := 070;
2706 EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
2707 p_application_id => 431
2708 ,p_attr_internal_str_value => 'HOLDS'
2709 ,p_attr_internal_name => 'charAttrIndVS'
2710 ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP'
2711 ,p_attr_group_int_name => 'gtestAG'
2712 ,p_attr_id => 8901
2713 ,p_object_name => 'EGO_ITEM'
2714 ,p_pk1_column_name => 'INVENTORY_ITEM_ID'
2715 ,p_pk1_value => 138385
2716 ,p_pk2_column_name => 'ORGANIZATION_ID'
2717 ,p_pk2_value => 204
2718 ,p_data_level1_column_name => 'REVISION_ID'
2719 ,p_data_level1_value => null
2720 );
2721
2722 EXCEPTION
2723 WHEN ATTR_GRP_REQD then
2724 PO_LOG.exc(d_module,d_progress,'Either attribute group id or attribute group internal name must be specified');
2725 x_return_status := FND_API.G_RET_STS_ERROR;
2726 x_msg_data := 'Either attribute group id or attribute group internal name must be specified' ;
2727 return null;
2728 WHEN ATTR_REQD then
2729 PO_LOG.exc(d_module,d_progress,'Either attribute id or attribute internal name must be specified');
2730 x_return_status := FND_API.G_RET_STS_ERROR;
2731 x_msg_data := 'Either attribute id or attribute internal name must be specified' ;
2732 return null;
2733 when others then
2734 PO_LOG.exc(d_module,d_progress,'Unexpected error: '|| SQLERRM || 'at '||d_progress);
2735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2736 x_msg_data := 'Unexpected error: '|| SQLERRM || 'at '||d_progress ;
2737 return null;
2738 END GET_SINGLE_ATTR_VALUE;
2739
2740 FUNCTION GET_ADDRESS_ATTR_VALUE (
2741 p_template_id IN NUMBER,
2742 pk1_value IN NUMBER,
2743 pk2_value IN NUMBER,
2744 pk3_value IN NUMBER,
2745 pk4_value IN NUMBER,
2746 pk5_value IN NUMBER,
2747 p_attr_grp_id IN NUMBER,
2748 p_attr_grp_int_name IN VARCHAR2,
2749 p_attr_id IN NUMBER,
2750 p_attr_int_name IN VARCHAR2,
2751 p_address_type IN VARCHAR2,
2752 x_return_status OUT NOCOPY VARCHAR2,
2753 x_msg_data OUT NOCOPY VARCHAR2
2754 ) RETURN VARCHAR2
2755 IS
2756 BEGIN
2757 null;
2758 EXCEPTION
2759 when others then
2760 null;
2761 END GET_ADDRESS_ATTR_VALUE;
2762
2763 */
2764
2765 PROCEDURE get_po_header_attr_group_ids(
2766 p_template_id IN VARCHAR2,
2767 p_po_header_id IN NUMBER,
2768 p_is_doc_mod VARCHAR2,
2769 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
2770 x_return_status OUT NOCOPY VARCHAR2
2771 ) IS
2772 d_api_name CONSTANT VARCHAR2(30) := 'get_po_header_attr_group_ids';
2773 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2774 d_progress NUMBER := 0;
2775 l_po_uda_usage_object po_uda_usage_object ;
2776 l_attr_grp_ids_list PO_TBL_NUMBER;
2777 l_x_attr_ids_count NUMBER;
2778 l_clm_standard_form PO_HEADERS_ALL.CLM_STANDARD_FORM%TYPE;
2779 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
2780 l_attr_grp_ids PO_TBL_NUMBER;
2781 t_count NUMBER;
2782
2783 cursor c_get_usages(p_template_id number) is
2784 select distinct(attribute_category) from po_uda_ag_template_usages
2785 where template_id = p_template_id;
2786
2787 BEGIN
2788 IF (PO_LOG.d_proc) THEN
2789 PO_LOG.proc_begin(d_module);
2790 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2791 END IF;
2792
2793 l_attr_grp_ids := PO_TBL_NUMBER();
2794
2795 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
2796 l_context := NULL;
2797
2798 /* IF (c_get_usages_rec.attribute_category = 'FORMS') then
2799 SELECT clm_standard_form into l_clm_standard_form FROM po_headers_all
2800 WHERE po_header_id = p_po_header_id;
2801 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'FORM_NAME', l_clm_standard_form));
2802 END IF; */
2803
2804 IF((p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN')
2805 OR (c_get_usages_rec.attribute_category = 'DOCUMENT_NUMBERING') ) THEN
2806 IF PO_LOG.d_stmt THEN
2807 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain and Doc Numbering AGs');
2808 END IF;
2809 ELSE
2810 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2811 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
2812 t_count := l_attr_grp_ids_list.Count;
2813
2814 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
2815 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
2816 l_attr_grp_ids.extend(1);
2817 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
2818 END LOOP;
2819 END IF;
2820 END LOOP;
2821
2822 x_attr_grp_ids := l_attr_grp_ids;
2823 x_return_status := 'S';
2824
2825 IF PO_LOG.d_stmt THEN
2826 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2827 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2828 END IF;
2829
2830 EXCEPTION
2831 WHEN OTHERS THEN
2832 d_progress := 40;
2833 x_return_status := 'U';
2834 IF PO_LOG.d_stmt THEN
2835 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2836 END IF;
2837 END get_po_header_attr_group_ids;
2838
2839 PROCEDURE get_po_line_attr_group_ids(
2840 p_template_id IN VARCHAR2,
2841 p_po_line_id IN NUMBER,
2842 p_draft_id IN NUMBER DEFAULT -1,
2843 p_is_doc_mod VARCHAR2,
2844 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
2845 x_return_status OUT NOCOPY VARCHAR2
2846 ) IS
2847 d_api_name CONSTANT VARCHAR2(30) := 'get_po_line_attr_group_ids';
2848 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2849 d_progress NUMBER := 0;
2850
2851 l_po_uda_usage_object po_uda_usage_object ;
2852 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
2853 l_attr_grp_ids_list PO_TBL_NUMBER;
2854 l_contract_type PO_LINES_ALL.CONTRACT_TYPE%TYPE;
2855 l_clm_idc_type PO_LINES_ALL.CLM_IDC_TYPE%TYPE;
2856 l_x_attr_ids_count NUMBER;
2857 l_attr_grp_ids PO_TBL_NUMBER;
2858 t_count NUMBER;
2859 l_count NUMBER;
2860
2861 CURSOR c_get_usages(p_template_id NUMBER) IS
2862 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
2863 WHERE template_id = p_template_id;
2864
2865 BEGIN
2866
2867 IF (PO_LOG.d_proc) THEN
2868 PO_LOG.proc_begin(d_module);
2869 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2870 END IF;
2871
2872 l_attr_grp_ids := PO_TBL_NUMBER();
2873
2874 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
2875
2876 l_context := NULL;
2877
2878 IF (c_get_usages_rec.attribute_category = 'PRICING') then
2879 IF(p_draft_id = -1) THEN
2880 SELECT CONTRACT_TYPE, CLM_IDC_TYPE
2881 INTO l_contract_type ,l_clm_idc_type
2882 FROM po_lines_all
2883 WHERE po_line_id = p_po_line_id;
2884 ELSE
2885 SELECT CONTRACT_TYPE, CLM_IDC_TYPE
2886 INTO l_contract_type ,l_clm_idc_type
2887 FROM po_lines_draft_all
2888 WHERE po_line_id = p_po_line_id
2889 AND draft_id = p_draft_id;
2890 END IF;
2891 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTRACT_LINE_TYPE', l_contract_type),
2892 EGO_COL_NAME_VALUE_PAIR_OBJ( 'IDC_TYPE' , l_clm_idc_type ));
2893 END IF;
2894
2895 IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
2896 IF PO_LOG.d_stmt THEN
2897 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
2898 END IF;
2899
2900 ELSE
2901 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2902 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
2903
2904 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
2905 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
2906 l_attr_grp_ids.extend(1);
2907 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
2908 END LOOP;
2909 END IF;
2910 END LOOP;
2911
2912 x_attr_grp_ids := l_attr_grp_ids;
2913 x_return_status := 'S';
2914
2915 IF PO_LOG.d_stmt THEN
2916 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2917 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2918 END IF;
2919
2920 EXCEPTION
2921 WHEN OTHERS THEN
2922 d_progress := 40;
2923 x_return_status := 'U';
2924 IF PO_LOG.d_stmt THEN
2925 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2926 END IF;
2927 END get_po_line_attr_group_ids;
2928
2929
2930 PROCEDURE get_po_line_loc_attr_group_ids(
2931 p_template_id IN VARCHAR2,
2932 p_po_line_location_id IN NUMBER,
2933 p_is_doc_mod VARCHAR2,
2934 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
2935 x_return_status OUT NOCOPY VARCHAR2
2936 ) IS
2937
2938 d_api_name CONSTANT VARCHAR2(30) := 'get_po_line_loc_attr_group_ids';
2939 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2940 d_progress NUMBER := 0;
2941 l_po_uda_usage_object po_uda_usage_object ;
2942 l_attr_grp_ids_list PO_TBL_NUMBER;
2943 l_x_attr_ids_count NUMBER;
2944 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
2945 l_attr_grp_ids PO_TBL_NUMBER;
2946 t_count NUMBER;
2947
2948 CURSOR c_get_usages(p_template_id NUMBER) IS
2949 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
2950 WHERE template_id = p_template_id;
2951
2952 BEGIN
2953 IF (PO_LOG.d_proc) THEN
2954 PO_LOG.proc_begin(d_module);
2955 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2956 END IF;
2957
2958 l_attr_grp_ids := PO_TBL_NUMBER();
2959
2960 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
2961 IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
2962 IF PO_LOG.d_stmt THEN
2963 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
2964 END IF;
2965
2966 ELSE
2967 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2968 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
2969 t_count := l_attr_grp_ids_list.Count;
2970
2971 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
2972 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
2973 l_attr_grp_ids.extend(1);
2974 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
2975 END LOOP;
2976 END IF;
2977 END LOOP;
2978
2979 x_attr_grp_ids := l_attr_grp_ids;
2980 x_return_status := 'S';
2981
2982 IF PO_LOG.d_stmt THEN
2983 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2984 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2985 END IF;
2986
2987 EXCEPTION
2988 WHEN OTHERS THEN
2989 d_progress := 40;
2990 x_return_status := 'U';
2991 IF PO_LOG.d_stmt THEN
2992 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2993 END IF;
2994 END get_po_line_loc_attr_group_ids;
2995
2996 PROCEDURE global_update_line_uda
2997 (
2998 po_src_line_id IN NUMBER ,
2999 po_dest_line_ids_tbl IN PO_TBL_NUMBER,
3000 p_draft_id IN NUMBER
3001 )
3002
3003 IS
3004
3005 d_api_name CONSTANT VARCHAR2(30) := 'global_update_line_uda';
3006 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3007
3008 po_dest_line_ids VARCHAR2(32000) := '';
3009 extension_id_tbl PO_TBL_NUMBER;
3010 attr_group_id_tbl PO_TBL_NUMBER;
3011 po_line_id_tbl PO_TBL_NUMBER;
3012 data_level_id_tbl PO_TBL_NUMBER;
3013 uda_template_id_tbl PO_TBL_NUMBER;
3014 mergeStmt VARCHAR(20000) := '';
3015 selectStmt VARCHAR(2000) := '';
3016 d_progress NUMBER := 0;
3017
3018
3019 BEGIN
3020
3021 IF (PO_LOG.d_proc) THEN
3022 PO_LOG.proc_begin(d_module);
3023 PO_LOG.proc_begin(d_module, 'po_src_line_id', po_src_line_id);
3024 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3025 END IF;
3026
3027 FOR i IN 1..po_dest_line_ids_tbl.Count LOOP
3028 po_dest_line_ids := po_dest_line_ids || po_dest_line_ids_tbl(i) || ',' ;
3029 END LOOP ;
3030
3031 po_dest_line_ids := RTrim(po_dest_line_ids,',');
3032 d_progress := 10;
3033
3034 --Copy from base if it is mod and no row exists in mod
3035
3036 IF(p_draft_id > 0) THEN
3037
3038 FORALL i IN 1..po_dest_line_ids_tbl.Count
3039 INSERT INTO po_lines_all_ext_b DESTlINE
3040 (DESTLINE.extension_id,
3041 DESTLINE.attr_group_id,
3042 DESTLINE.po_line_id,
3043 DESTLINE.data_level_id,
3044 DESTLINE.pk1_value,
3045 DESTLINE.pk2_value,
3046 DESTLINE.pk3_value,
3047 DESTLINE.pk4_value,
3048 DESTLINE.pk5_value,
3049 DESTLINE.last_update_date,
3050 DESTLINE.last_updated_by,
3051 DESTLINE.last_update_login,
3052 DESTLINE.created_by,
3053 DESTLINE.creation_date,
3054 DESTLINE.c_ext_attr1,
3055 DESTLINE.c_ext_attr2,
3056 DESTLINE.c_ext_attr3,
3057 DESTLINE.c_ext_attr4,
3058 DESTLINE.c_ext_attr5,
3059 DESTLINE.c_ext_attr6,
3060 DESTLINE.c_ext_attr7,
3061 DESTLINE.c_ext_attr8,
3062 DESTLINE.c_ext_attr9,
3063 DESTLINE.c_ext_attr10,
3064 DESTLINE.c_ext_attr11,
3065 DESTLINE.c_ext_attr12,
3066 DESTLINE.c_ext_attr13,
3067 DESTLINE.c_ext_attr14,
3068 DESTLINE.c_ext_attr15,
3069 DESTLINE.c_ext_attr16,
3070 DESTLINE.c_ext_attr17,
3071 DESTLINE.c_ext_attr18,
3072 DESTLINE.c_ext_attr19,
3073 DESTLINE.c_ext_attr20,
3074 DESTLINE.c_ext_attr21,
3075 DESTLINE.c_ext_attr22,
3076 DESTLINE.c_ext_attr23,
3077 DESTLINE.c_ext_attr24,
3078 DESTLINE.c_ext_attr25,
3079 DESTLINE.c_ext_attr26,
3080 DESTLINE.c_ext_attr27,
3081 DESTLINE.c_ext_attr28,
3082 DESTLINE.c_ext_attr29,
3083 DESTLINE.c_ext_attr30,
3084 DESTLINE.c_ext_attr31,
3085 DESTLINE.c_ext_attr32,
3086 DESTLINE.c_ext_attr33,
3087 DESTLINE.c_ext_attr34,
3088 DESTLINE.c_ext_attr35,
3089 DESTLINE.c_ext_attr36,
3090 DESTLINE.c_ext_attr37,
3091 DESTLINE.c_ext_attr38,
3092 DESTLINE.c_ext_attr39,
3093 DESTLINE.c_ext_attr40,
3094 DESTLINE.n_ext_attr1,
3095 DESTLINE.n_ext_attr2,
3096 DESTLINE.n_ext_attr3,
3097 DESTLINE.n_ext_attr4,
3098 DESTLINE.n_ext_attr5,
3099 DESTLINE.n_ext_attr6,
3100 DESTLINE.n_ext_attr7,
3101 DESTLINE.n_ext_attr8,
3102 DESTLINE.n_ext_attr9,
3103 DESTLINE.n_ext_attr10,
3104 DESTLINE.n_ext_attr11,
3105 DESTLINE.n_ext_attr12,
3106 DESTLINE.n_ext_attr13,
3107 DESTLINE.n_ext_attr14,
3108 DESTLINE.n_ext_attr15,
3109 DESTLINE.n_ext_attr16,
3110 DESTLINE.n_ext_attr17,
3111 DESTLINE.n_ext_attr18,
3112 DESTLINE.n_ext_attr19,
3113 DESTLINE.n_ext_attr20,
3114 DESTLINE.uom_ext_attr1,
3115 DESTLINE.uom_ext_attr2,
3116 DESTLINE.uom_ext_attr3,
3117 DESTLINE.uom_ext_attr4,
3118 DESTLINE.uom_ext_attr5,
3119 DESTLINE.uom_ext_attr6,
3120 DESTLINE.uom_ext_attr7,
3121 DESTLINE.uom_ext_attr8,
3122 DESTLINE.uom_ext_attr9,
3123 DESTLINE.uom_ext_attr10,
3124 DESTLINE.uom_ext_attr11,
3125 DESTLINE.uom_ext_attr12,
3126 DESTLINE.uom_ext_attr13,
3127 DESTLINE.uom_ext_attr14,
3128 DESTLINE.uom_ext_attr15,
3129 DESTLINE.uom_ext_attr16,
3130 DESTLINE.uom_ext_attr17,
3131 DESTLINE.uom_ext_attr18,
3132 DESTLINE.uom_ext_attr19,
3133 DESTLINE.uom_ext_attr20,
3134 DESTLINE.d_ext_attr1,
3135 DESTLINE.d_ext_attr2,
3136 DESTLINE.d_ext_attr3,
3137 DESTLINE.d_ext_attr4,
3138 DESTLINE.d_ext_attr5,
3139 DESTLINE.d_ext_attr6,
3140 DESTLINE.d_ext_attr7,
3141 DESTLINE.d_ext_attr8,
3142 DESTLINE.d_ext_attr9,
3143 DESTLINE.d_ext_attr10,
3144 DESTLINE.uda_template_id,
3145 DESTLINE.draft_id)
3146 SELECT
3147 EGO_EXTFWK_S.NEXTVAL,
3148 SRCLINE.attr_group_id,
3149 po_dest_line_ids_tbl(i),
3150 SRCLINE.data_level_id,
3151 SRCLINE.pk1_value,
3152 SRCLINE.pk2_value,
3153 SRCLINE.pk3_value,
3154 SRCLINE.pk4_value,
3155 SRCLINE.pk5_value,
3156 SRCLINE.last_update_date,
3157 SRCLINE.last_updated_by,
3158 SRCLINE.last_update_login,
3159 SRCLINE.created_by,
3160 SRCLINE.creation_date,
3161 SRCLINE.c_ext_attr1,
3162 SRCLINE.c_ext_attr2,
3163 SRCLINE.c_ext_attr3,
3164 SRCLINE.c_ext_attr4,
3165 SRCLINE.c_ext_attr5,
3166 SRCLINE.c_ext_attr6,
3167 SRCLINE.c_ext_attr7,
3168 SRCLINE.c_ext_attr8,
3169 SRCLINE.c_ext_attr9,
3170 SRCLINE.c_ext_attr10,
3171 SRCLINE.c_ext_attr11,
3172 SRCLINE.c_ext_attr12,
3173 SRCLINE.c_ext_attr13,
3174 SRCLINE.c_ext_attr14,
3175 SRCLINE.c_ext_attr15,
3176 SRCLINE.c_ext_attr16,
3177 SRCLINE.c_ext_attr17,
3178 SRCLINE.c_ext_attr18,
3179 SRCLINE.c_ext_attr19,
3180 SRCLINE.c_ext_attr20,
3181 SRCLINE.c_ext_attr21,
3182 SRCLINE.c_ext_attr22,
3183 SRCLINE.c_ext_attr23,
3184 SRCLINE.c_ext_attr24,
3185 SRCLINE.c_ext_attr25,
3186 SRCLINE.c_ext_attr26,
3187 SRCLINE.c_ext_attr27,
3188 SRCLINE.c_ext_attr28,
3189 SRCLINE.c_ext_attr29,
3190 SRCLINE.c_ext_attr30,
3191 SRCLINE.c_ext_attr31,
3192 SRCLINE.c_ext_attr32,
3193 SRCLINE.c_ext_attr33,
3194 SRCLINE.c_ext_attr34,
3195 SRCLINE.c_ext_attr35,
3196 SRCLINE.c_ext_attr36,
3197 SRCLINE.c_ext_attr37,
3198 SRCLINE.c_ext_attr38,
3199 SRCLINE.c_ext_attr39,
3200 SRCLINE.c_ext_attr40,
3201 SRCLINE.n_ext_attr1,
3202 SRCLINE.n_ext_attr2,
3203 SRCLINE.n_ext_attr3,
3204 SRCLINE.n_ext_attr4,
3205 SRCLINE.n_ext_attr5,
3206 SRCLINE.n_ext_attr6,
3207 SRCLINE.n_ext_attr7,
3208 SRCLINE.n_ext_attr8,
3209 SRCLINE.n_ext_attr9,
3210 SRCLINE.n_ext_attr10,
3211 SRCLINE.n_ext_attr11,
3212 SRCLINE.n_ext_attr12,
3213 SRCLINE.n_ext_attr13,
3214 SRCLINE.n_ext_attr14,
3215 SRCLINE.n_ext_attr15,
3216 SRCLINE.n_ext_attr16,
3217 SRCLINE.n_ext_attr17,
3218 SRCLINE.n_ext_attr18,
3219 SRCLINE.n_ext_attr19,
3220 SRCLINE.n_ext_attr20,
3221 SRCLINE.uom_ext_attr1,
3222 SRCLINE.uom_ext_attr2,
3223 SRCLINE.uom_ext_attr3,
3224 SRCLINE.uom_ext_attr4,
3225 SRCLINE.uom_ext_attr5,
3226 SRCLINE.uom_ext_attr6,
3227 SRCLINE.uom_ext_attr7,
3228 SRCLINE.uom_ext_attr8,
3229 SRCLINE.uom_ext_attr9,
3230 SRCLINE.uom_ext_attr10,
3231 SRCLINE.uom_ext_attr11,
3232 SRCLINE.uom_ext_attr12,
3233 SRCLINE.uom_ext_attr13,
3234 SRCLINE.uom_ext_attr14,
3235 SRCLINE.uom_ext_attr15,
3236 SRCLINE.uom_ext_attr16,
3237 SRCLINE.uom_ext_attr17,
3238 SRCLINE.uom_ext_attr18,
3239 SRCLINE.uom_ext_attr19,
3240 SRCLINE.uom_ext_attr20,
3241 SRCLINE.d_ext_attr1,
3242 SRCLINE.d_ext_attr2,
3243 SRCLINE.d_ext_attr3,
3244 SRCLINE.d_ext_attr4,
3245 SRCLINE.d_ext_attr5,
3246 SRCLINE.d_ext_attr6,
3247 SRCLINE.d_ext_attr7,
3248 SRCLINE.d_ext_attr8,
3249 SRCLINE.d_ext_attr9,
3250 SRCLINE.d_ext_attr10,
3251 SRCLINE.uda_template_id,
3252 SRCLINE.draft_id
3253 FROM po_lines_all_ext_b SRCLINE WHERE SRCLINE.po_line_id = po_src_line_id AND draft_id = -1
3254 AND NOT EXISTS (SELECT 1 FROM po_lines_all_ext_b pleb WHERE
3255 pleb.po_line_id =po_dest_line_ids_tbl(i) AND
3256 pleb.attr_group_id = SRCLINE.attr_group_id AND
3257 pleb.draft_id = p_draft_id);
3258 END IF ;
3259
3260 mergeStmt := mergeStmt ||
3261
3262 'MERGE INTO po_lines_all_ext_b DESTLINE
3263 USING (
3264 SELECT
3265 attr_group_id,
3266 po_line_id,
3267 data_level_id,
3268 last_update_date,
3269 last_updated_by,
3270 last_update_login,
3271 creation_date,
3272 created_by,
3273 pk1_value,
3274 pk2_value,
3275 pk3_value,
3276 pk4_value,
3277 pk5_value,
3278 c_ext_attr1,
3279 c_ext_attr2,
3280 c_ext_attr3,
3281 c_ext_attr4,
3282 c_ext_attr5,
3283 c_ext_attr6,
3284 c_ext_attr7,
3285 c_ext_attr8,
3286 c_ext_attr9,
3287 c_ext_attr10,
3288 c_ext_attr11,
3289 c_ext_attr12,
3290 c_ext_attr13,
3291 c_ext_attr14,
3292 c_ext_attr15,
3293 c_ext_attr16,
3294 c_ext_attr17,
3295 c_ext_attr18,
3296 c_ext_attr19,
3297 c_ext_attr20,
3298 c_ext_attr21,
3299 c_ext_attr22,
3300 c_ext_attr23,
3301 c_ext_attr24,
3302 c_ext_attr25,
3303 c_ext_attr26,
3304 c_ext_attr27,
3305 c_ext_attr28,
3306 c_ext_attr29,
3307 c_ext_attr30,
3308 c_ext_attr31,
3309 c_ext_attr32,
3310 c_ext_attr33,
3311 c_ext_attr34,
3312 c_ext_attr35,
3313 c_ext_attr36,
3314 c_ext_attr37,
3315 c_ext_attr38,
3316 c_ext_attr39,
3317 c_ext_attr40,
3318 n_ext_attr1,
3319 n_ext_attr2,
3320 n_ext_attr3,
3321 n_ext_attr4,
3322 n_ext_attr5,
3323 n_ext_attr6,
3324 n_ext_attr7,
3325 n_ext_attr8,
3326 n_ext_attr9,
3327 n_ext_attr10,
3328 n_ext_attr11,
3329 n_ext_attr12,
3330 n_ext_attr13,
3331 n_ext_attr14,
3332 n_ext_attr15,
3333 n_ext_attr16,
3334 n_ext_attr17,
3335 n_ext_attr18,
3336 n_ext_attr19,
3337 n_ext_attr20,
3338 uom_ext_attr1,
3339 uom_ext_attr2,
3340 uom_ext_attr3,
3341 uom_ext_attr4,
3342 uom_ext_attr5,
3343 uom_ext_attr6,
3344 uom_ext_attr7,
3345 uom_ext_attr8,
3346 uom_ext_attr9,
3347 uom_ext_attr10,
3348 uom_ext_attr11,
3349 uom_ext_attr12,
3350 uom_ext_attr13,
3351 uom_ext_attr14,
3352 uom_ext_attr15,
3353 uom_ext_attr16,
3354 uom_ext_attr17,
3355 uom_ext_attr18,
3356 uom_ext_attr19,
3357 uom_ext_attr20,
3358 d_ext_attr1,
3359 d_ext_attr2,
3360 d_ext_attr3,
3361 d_ext_attr4,
3362 d_ext_attr5,
3363 d_ext_attr6,
3364 d_ext_attr7,
3365 d_ext_attr8,
3366 d_ext_attr9,
3367 d_ext_attr10,
3368 uda_template_id,
3369 draft_id
3370 FROM po_lines_all_ext_b WHERE po_line_id = ' || po_src_line_id || ' and draft_id = ' || p_draft_id || ' ) SRCLINE
3371 ON
3372 (DESTLINE.po_line_id IN (' || po_dest_line_ids || ') AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND DESTLINE.draft_id = '|| p_draft_id ||')
3373 WHEN MATCHED THEN
3374 UPDATE
3375 SET
3376 DESTLINE.last_update_date = SYSDATE ,
3377 DESTLINE.last_updated_by = SRCLINE.last_updated_by,
3378 DESTLINE.last_update_login = SRCLINE.last_update_login,
3379 DESTLINE.created_by = SRCLINE.created_by,
3380 DESTLINE.creation_date = SYSDATE,
3381 DESTLINE.c_ext_attr1 = nvl(SRCLINE.c_ext_attr1 , DESTLINE.c_ext_attr1),
3382 DESTLINE.c_ext_attr2 = nvl(SRCLINE.c_ext_attr2 , DESTLINE.c_ext_attr2),
3383 DESTLINE.c_ext_attr3 = nvl(SRCLINE.c_ext_attr3 , DESTLINE.c_ext_attr3),
3384 DESTLINE.c_ext_attr4 = nvl(SRCLINE.c_ext_attr4 , DESTLINE.c_ext_attr4),
3385 DESTLINE.c_ext_attr5 = nvl(SRCLINE.c_ext_attr5 , DESTLINE.c_ext_attr5),
3386 DESTLINE.c_ext_attr6 = nvl(SRCLINE.c_ext_attr6 , DESTLINE.c_ext_attr6),
3387 DESTLINE.c_ext_attr7 = nvl(SRCLINE.c_ext_attr7 , DESTLINE.c_ext_attr7),
3388 DESTLINE.c_ext_attr8 = nvl(SRCLINE.c_ext_attr8 , DESTLINE.c_ext_attr8),
3389 DESTLINE.c_ext_attr9 = nvl(SRCLINE.c_ext_attr9 , DESTLINE.c_ext_attr9),
3390 DESTLINE.c_ext_attr10 = nvl(SRCLINE.c_ext_attr10 , DESTLINE.c_ext_attr10),
3391 DESTLINE.c_ext_attr11 = nvl(SRCLINE.c_ext_attr11 , DESTLINE.c_ext_attr11),
3392 DESTLINE.c_ext_attr12 = nvl(SRCLINE.c_ext_attr12 , DESTLINE.c_ext_attr12),
3393 DESTLINE.c_ext_attr13 = nvl(SRCLINE.c_ext_attr13 , DESTLINE.c_ext_attr13),
3394 DESTLINE.c_ext_attr14 = nvl(SRCLINE.c_ext_attr14 , DESTLINE.c_ext_attr14),
3395 DESTLINE.c_ext_attr15 = nvl(SRCLINE.c_ext_attr15 , DESTLINE.c_ext_attr15),
3396 DESTLINE.c_ext_attr16 = nvl(SRCLINE.c_ext_attr16 , DESTLINE.c_ext_attr16),
3397 DESTLINE.c_ext_attr17 = nvl(SRCLINE.c_ext_attr17 , DESTLINE.c_ext_attr17),
3398 DESTLINE.c_ext_attr18 = nvl(SRCLINE.c_ext_attr18 , DESTLINE.c_ext_attr18),
3399 DESTLINE.c_ext_attr19 = nvl(SRCLINE.c_ext_attr19 , DESTLINE.c_ext_attr19),
3400 DESTLINE.c_ext_attr20 = nvl(SRCLINE.c_ext_attr20 , DESTLINE.c_ext_attr20),
3401 DESTLINE.c_ext_attr21 = nvl(SRCLINE.c_ext_attr21 , DESTLINE.c_ext_attr21),
3402 DESTLINE.c_ext_attr22 = nvl(SRCLINE.c_ext_attr22 , DESTLINE.c_ext_attr22),
3403 DESTLINE.c_ext_attr23 = nvl(SRCLINE.c_ext_attr23 , DESTLINE.c_ext_attr23),
3404 DESTLINE.c_ext_attr24 = nvl(SRCLINE.c_ext_attr24 , DESTLINE.c_ext_attr24),
3405 DESTLINE.c_ext_attr25 = nvl(SRCLINE.c_ext_attr25 , DESTLINE.c_ext_attr25),
3406 DESTLINE.c_ext_attr26 = nvl(SRCLINE.c_ext_attr26 , DESTLINE.c_ext_attr26),
3407 DESTLINE.c_ext_attr27 = nvl(SRCLINE.c_ext_attr27 , DESTLINE.c_ext_attr27),
3408 DESTLINE.c_ext_attr28 = nvl(SRCLINE.c_ext_attr28 , DESTLINE.c_ext_attr28),
3409 DESTLINE.c_ext_attr29 = nvl(SRCLINE.c_ext_attr29 , DESTLINE.c_ext_attr29),
3410 DESTLINE.c_ext_attr30 = nvl(SRCLINE.c_ext_attr30 , DESTLINE.c_ext_attr30),
3411 DESTLINE.c_ext_attr31 = nvl(SRCLINE.c_ext_attr31 , DESTLINE.c_ext_attr31),
3412 DESTLINE.c_ext_attr32 = nvl(SRCLINE.c_ext_attr32 , DESTLINE.c_ext_attr32),
3413 DESTLINE.c_ext_attr33 = nvl(SRCLINE.c_ext_attr33 , DESTLINE.c_ext_attr33),
3414 DESTLINE.c_ext_attr34 = nvl(SRCLINE.c_ext_attr34 , DESTLINE.c_ext_attr34),
3415 DESTLINE.c_ext_attr35 = nvl(SRCLINE.c_ext_attr35 , DESTLINE.c_ext_attr35),
3416 DESTLINE.c_ext_attr36 = nvl(SRCLINE.c_ext_attr36 , DESTLINE.c_ext_attr36),
3417 DESTLINE.c_ext_attr37 = nvl(SRCLINE.c_ext_attr37 , DESTLINE.c_ext_attr37),
3418 DESTLINE.c_ext_attr38 = nvl(SRCLINE.c_ext_attr38 , DESTLINE.c_ext_attr38),
3419 DESTLINE.c_ext_attr39 = nvl(SRCLINE.c_ext_attr39 , DESTLINE.c_ext_attr39),
3420 DESTLINE.c_ext_attr40 = nvl(SRCLINE.c_ext_attr40 , DESTLINE.c_ext_attr40),
3421 DESTLINE.n_ext_attr1 = nvl(SRCLINE.n_ext_attr1 , DESTLINE.n_ext_attr1),
3422 DESTLINE.n_ext_attr2 = nvl(SRCLINE.n_ext_attr2 , DESTLINE.n_ext_attr2),
3423 DESTLINE.n_ext_attr3 = nvl(SRCLINE.n_ext_attr3 , DESTLINE.n_ext_attr3),
3424 DESTLINE.n_ext_attr4 = nvl(SRCLINE.n_ext_attr4 , DESTLINE.n_ext_attr4),
3425 DESTLINE.n_ext_attr5 = nvl(SRCLINE.n_ext_attr5 , DESTLINE.n_ext_attr5),
3426 DESTLINE.n_ext_attr6 = nvl(SRCLINE.n_ext_attr6 , DESTLINE.n_ext_attr6),
3427 DESTLINE.n_ext_attr7 = nvl(SRCLINE.n_ext_attr7 , DESTLINE.n_ext_attr7),
3428 DESTLINE.n_ext_attr8 = nvl(SRCLINE.n_ext_attr8 , DESTLINE.n_ext_attr8),
3429 DESTLINE.n_ext_attr9 = nvl(SRCLINE.n_ext_attr9 , DESTLINE.n_ext_attr9),
3430 DESTLINE.n_ext_attr10 = nvl(SRCLINE.n_ext_attr10 , DESTLINE.n_ext_attr10),
3431 DESTLINE.n_ext_attr11 = nvl(SRCLINE.n_ext_attr11 , DESTLINE.n_ext_attr11),
3432 DESTLINE.n_ext_attr12 = nvl(SRCLINE.n_ext_attr12 , DESTLINE.n_ext_attr12),
3433 DESTLINE.n_ext_attr13 = nvl(SRCLINE.n_ext_attr13 , DESTLINE.n_ext_attr13),
3434 DESTLINE.n_ext_attr14 = nvl(SRCLINE.n_ext_attr14 , DESTLINE.n_ext_attr14),
3435 DESTLINE.n_ext_attr15 = nvl(SRCLINE.n_ext_attr15 , DESTLINE.n_ext_attr15),
3436 DESTLINE.n_ext_attr16 = nvl(SRCLINE.n_ext_attr16 , DESTLINE.n_ext_attr16),
3437 DESTLINE.n_ext_attr17 = nvl(SRCLINE.n_ext_attr17 , DESTLINE.n_ext_attr17),
3438 DESTLINE.n_ext_attr18 = nvl(SRCLINE.n_ext_attr18 , DESTLINE.n_ext_attr18),
3439 DESTLINE.n_ext_attr19 = nvl(SRCLINE.n_ext_attr19 , DESTLINE.n_ext_attr19),
3440 DESTLINE.n_ext_attr20 = nvl(SRCLINE.n_ext_attr20 , DESTLINE.n_ext_attr20),
3441 DESTLINE.uom_ext_attr1 = nvl(SRCLINE.uom_ext_attr1 , DESTLINE.uom_ext_attr1),
3442 DESTLINE.uom_ext_attr2 = nvl(SRCLINE.uom_ext_attr2 , DESTLINE.uom_ext_attr2),
3443 DESTLINE.uom_ext_attr3 = nvl(SRCLINE.uom_ext_attr3 , DESTLINE.uom_ext_attr3),
3444 DESTLINE.uom_ext_attr4 = nvl(SRCLINE.uom_ext_attr4 , DESTLINE.uom_ext_attr4),
3445 DESTLINE.uom_ext_attr5 = nvl(SRCLINE.uom_ext_attr5 , DESTLINE.uom_ext_attr5),
3446 DESTLINE.uom_ext_attr6 = nvl(SRCLINE.uom_ext_attr6 , DESTLINE.uom_ext_attr6),
3447 DESTLINE.uom_ext_attr7 = nvl(SRCLINE.uom_ext_attr7 , DESTLINE.uom_ext_attr7),
3448 DESTLINE.uom_ext_attr8 = nvl(SRCLINE.uom_ext_attr8 , DESTLINE.uom_ext_attr8),
3449 DESTLINE.uom_ext_attr9 = nvl(SRCLINE.uom_ext_attr9 , DESTLINE.uom_ext_attr9),
3450 DESTLINE.uom_ext_attr10 = nvl(SRCLINE.uom_ext_attr10 , DESTLINE.uom_ext_attr10),
3451 DESTLINE.uom_ext_attr11 = nvl(SRCLINE.uom_ext_attr11 , DESTLINE.uom_ext_attr11),
3452 DESTLINE.uom_ext_attr12 = nvl(SRCLINE.uom_ext_attr12 , DESTLINE.uom_ext_attr12),
3453 DESTLINE.uom_ext_attr13 = nvl(SRCLINE.uom_ext_attr13 , DESTLINE.uom_ext_attr13),
3454 DESTLINE.uom_ext_attr14 = nvl(SRCLINE.uom_ext_attr14 , DESTLINE.uom_ext_attr14),
3455 DESTLINE.uom_ext_attr15 = nvl(SRCLINE.uom_ext_attr15 , DESTLINE.uom_ext_attr15),
3456 DESTLINE.uom_ext_attr16 = nvl(SRCLINE.uom_ext_attr16 , DESTLINE.uom_ext_attr16),
3457 DESTLINE.uom_ext_attr17 = nvl(SRCLINE.uom_ext_attr17 , DESTLINE.uom_ext_attr17),
3458 DESTLINE.uom_ext_attr18 = nvl(SRCLINE.uom_ext_attr18 , DESTLINE.uom_ext_attr18),
3459 DESTLINE.uom_ext_attr19 = nvl(SRCLINE.uom_ext_attr19 , DESTLINE.uom_ext_attr19),
3460 DESTLINE.uom_ext_attr20 = nvl(SRCLINE.uom_ext_attr20 , DESTLINE.uom_ext_attr20),
3461 DESTLINE.d_ext_attr1 = nvl(SRCLINE.d_ext_attr1 , DESTLINE.d_ext_attr1),
3462 DESTLINE.d_ext_attr2 = nvl(SRCLINE.d_ext_attr2 , DESTLINE.d_ext_attr2),
3463 DESTLINE.d_ext_attr3 = nvl(SRCLINE.d_ext_attr3 , DESTLINE.d_ext_attr3),
3464 DESTLINE.d_ext_attr4 = nvl(SRCLINE.d_ext_attr4 , DESTLINE.d_ext_attr4),
3465 DESTLINE.d_ext_attr5 = nvl(SRCLINE.d_ext_attr5 , DESTLINE.d_ext_attr5),
3466 DESTLINE.d_ext_attr6 = nvl(SRCLINE.d_ext_attr6 , DESTLINE.d_ext_attr6),
3467 DESTLINE.d_ext_attr7 = nvl(SRCLINE.d_ext_attr7 , DESTLINE.d_ext_attr7),
3468 DESTLINE.d_ext_attr8 = nvl(SRCLINE.d_ext_attr8 , DESTLINE.d_ext_attr8),
3469 DESTLINE.d_ext_attr9 = nvl(SRCLINE.d_ext_attr9 , DESTLINE.d_ext_attr9),
3470 DESTLINE.d_ext_attr10 = nvl(SRCLINE.d_ext_attr10 , DESTLINE.d_ext_attr10)' ;
3471
3472 d_progress := '20';
3473
3474 EXECUTE IMMEDIATE mergeStmt ;
3475
3476 d_progress := '30';
3477
3478 FORALL i IN 1..po_dest_line_ids_tbl.Count
3479
3480 INSERT INTO po_lines_all_ext_b DESTlINE
3481 (DESTLINE.extension_id,
3482 DESTLINE.attr_group_id,
3483 DESTLINE.po_line_id,
3484 DESTLINE.data_level_id,
3485 DESTLINE.pk1_value,
3486 DESTLINE.pk2_value,
3487 DESTLINE.pk3_value,
3488 DESTLINE.pk4_value,
3489 DESTLINE.pk5_value,
3490 DESTLINE.last_update_date,
3491 DESTLINE.last_updated_by,
3492 DESTLINE.last_update_login,
3493 DESTLINE.created_by,
3494 DESTLINE.creation_date,
3495 DESTLINE.c_ext_attr1,
3496 DESTLINE.c_ext_attr2,
3497 DESTLINE.c_ext_attr3,
3498 DESTLINE.c_ext_attr4,
3499 DESTLINE.c_ext_attr5,
3500 DESTLINE.c_ext_attr6,
3501 DESTLINE.c_ext_attr7,
3502 DESTLINE.c_ext_attr8,
3503 DESTLINE.c_ext_attr9,
3504 DESTLINE.c_ext_attr10,
3505 DESTLINE.c_ext_attr11,
3506 DESTLINE.c_ext_attr12,
3507 DESTLINE.c_ext_attr13,
3508 DESTLINE.c_ext_attr14,
3509 DESTLINE.c_ext_attr15,
3510 DESTLINE.c_ext_attr16,
3511 DESTLINE.c_ext_attr17,
3512 DESTLINE.c_ext_attr18,
3513 DESTLINE.c_ext_attr19,
3514 DESTLINE.c_ext_attr20,
3515 DESTLINE.c_ext_attr21,
3516 DESTLINE.c_ext_attr22,
3517 DESTLINE.c_ext_attr23,
3518 DESTLINE.c_ext_attr24,
3519 DESTLINE.c_ext_attr25,
3520 DESTLINE.c_ext_attr26,
3521 DESTLINE.c_ext_attr27,
3522 DESTLINE.c_ext_attr28,
3523 DESTLINE.c_ext_attr29,
3524 DESTLINE.c_ext_attr30,
3525 DESTLINE.c_ext_attr31,
3526 DESTLINE.c_ext_attr32,
3527 DESTLINE.c_ext_attr33,
3528 DESTLINE.c_ext_attr34,
3529 DESTLINE.c_ext_attr35,
3530 DESTLINE.c_ext_attr36,
3531 DESTLINE.c_ext_attr37,
3532 DESTLINE.c_ext_attr38,
3533 DESTLINE.c_ext_attr39,
3534 DESTLINE.c_ext_attr40,
3535 DESTLINE.n_ext_attr1,
3536 DESTLINE.n_ext_attr2,
3537 DESTLINE.n_ext_attr3,
3538 DESTLINE.n_ext_attr4,
3539 DESTLINE.n_ext_attr5,
3540 DESTLINE.n_ext_attr6,
3541 DESTLINE.n_ext_attr7,
3542 DESTLINE.n_ext_attr8,
3543 DESTLINE.n_ext_attr9,
3544 DESTLINE.n_ext_attr10,
3545 DESTLINE.n_ext_attr11,
3546 DESTLINE.n_ext_attr12,
3547 DESTLINE.n_ext_attr13,
3548 DESTLINE.n_ext_attr14,
3549 DESTLINE.n_ext_attr15,
3550 DESTLINE.n_ext_attr16,
3551 DESTLINE.n_ext_attr17,
3552 DESTLINE.n_ext_attr18,
3553 DESTLINE.n_ext_attr19,
3554 DESTLINE.n_ext_attr20,
3555 DESTLINE.uom_ext_attr1,
3556 DESTLINE.uom_ext_attr2,
3557 DESTLINE.uom_ext_attr3,
3558 DESTLINE.uom_ext_attr4,
3559 DESTLINE.uom_ext_attr5,
3560 DESTLINE.uom_ext_attr6,
3561 DESTLINE.uom_ext_attr7,
3562 DESTLINE.uom_ext_attr8,
3563 DESTLINE.uom_ext_attr9,
3564 DESTLINE.uom_ext_attr10,
3565 DESTLINE.uom_ext_attr11,
3566 DESTLINE.uom_ext_attr12,
3567 DESTLINE.uom_ext_attr13,
3568 DESTLINE.uom_ext_attr14,
3569 DESTLINE.uom_ext_attr15,
3570 DESTLINE.uom_ext_attr16,
3571 DESTLINE.uom_ext_attr17,
3572 DESTLINE.uom_ext_attr18,
3573 DESTLINE.uom_ext_attr19,
3574 DESTLINE.uom_ext_attr20,
3575 DESTLINE.d_ext_attr1,
3576 DESTLINE.d_ext_attr2,
3577 DESTLINE.d_ext_attr3,
3578 DESTLINE.d_ext_attr4,
3579 DESTLINE.d_ext_attr5,
3580 DESTLINE.d_ext_attr6,
3581 DESTLINE.d_ext_attr7,
3582 DESTLINE.d_ext_attr8,
3583 DESTLINE.d_ext_attr9,
3584 DESTLINE.d_ext_attr10,
3585 DESTLINE.uda_template_id,
3586 DESTLINE.draft_id)
3587 SELECT
3588 EGO_EXTFWK_S.NEXTVAL,
3589 SRCLINE.attr_group_id,
3590 po_dest_line_ids_tbl(i),
3591 SRCLINE.data_level_id,
3592 SRCLINE.pk1_value,
3593 SRCLINE.pk2_value,
3594 SRCLINE.pk3_value,
3595 SRCLINE.pk4_value,
3596 SRCLINE.pk5_value,
3597 SRCLINE.last_update_date,
3598 SRCLINE.last_updated_by,
3599 SRCLINE.last_update_login,
3600 SRCLINE.created_by,
3601 SRCLINE.creation_date,
3602 SRCLINE.c_ext_attr1,
3603 SRCLINE.c_ext_attr2,
3604 SRCLINE.c_ext_attr3,
3605 SRCLINE.c_ext_attr4,
3606 SRCLINE.c_ext_attr5,
3607 SRCLINE.c_ext_attr6,
3608 SRCLINE.c_ext_attr7,
3609 SRCLINE.c_ext_attr8,
3610 SRCLINE.c_ext_attr9,
3611 SRCLINE.c_ext_attr10,
3612 SRCLINE.c_ext_attr11,
3613 SRCLINE.c_ext_attr12,
3614 SRCLINE.c_ext_attr13,
3615 SRCLINE.c_ext_attr14,
3616 SRCLINE.c_ext_attr15,
3617 SRCLINE.c_ext_attr16,
3618 SRCLINE.c_ext_attr17,
3619 SRCLINE.c_ext_attr18,
3620 SRCLINE.c_ext_attr19,
3621 SRCLINE.c_ext_attr20,
3622 SRCLINE.c_ext_attr21,
3623 SRCLINE.c_ext_attr22,
3624 SRCLINE.c_ext_attr23,
3625 SRCLINE.c_ext_attr24,
3626 SRCLINE.c_ext_attr25,
3627 SRCLINE.c_ext_attr26,
3628 SRCLINE.c_ext_attr27,
3629 SRCLINE.c_ext_attr28,
3630 SRCLINE.c_ext_attr29,
3631 SRCLINE.c_ext_attr30,
3632 SRCLINE.c_ext_attr31,
3633 SRCLINE.c_ext_attr32,
3634 SRCLINE.c_ext_attr33,
3635 SRCLINE.c_ext_attr34,
3636 SRCLINE.c_ext_attr35,
3637 SRCLINE.c_ext_attr36,
3638 SRCLINE.c_ext_attr37,
3639 SRCLINE.c_ext_attr38,
3640 SRCLINE.c_ext_attr39,
3641 SRCLINE.c_ext_attr40,
3642 SRCLINE.n_ext_attr1,
3643 SRCLINE.n_ext_attr2,
3644 SRCLINE.n_ext_attr3,
3645 SRCLINE.n_ext_attr4,
3646 SRCLINE.n_ext_attr5,
3647 SRCLINE.n_ext_attr6,
3648 SRCLINE.n_ext_attr7,
3649 SRCLINE.n_ext_attr8,
3650 SRCLINE.n_ext_attr9,
3651 SRCLINE.n_ext_attr10,
3652 SRCLINE.n_ext_attr11,
3653 SRCLINE.n_ext_attr12,
3654 SRCLINE.n_ext_attr13,
3655 SRCLINE.n_ext_attr14,
3656 SRCLINE.n_ext_attr15,
3657 SRCLINE.n_ext_attr16,
3658 SRCLINE.n_ext_attr17,
3659 SRCLINE.n_ext_attr18,
3660 SRCLINE.n_ext_attr19,
3661 SRCLINE.n_ext_attr20,
3662 SRCLINE.uom_ext_attr1,
3663 SRCLINE.uom_ext_attr2,
3664 SRCLINE.uom_ext_attr3,
3665 SRCLINE.uom_ext_attr4,
3666 SRCLINE.uom_ext_attr5,
3667 SRCLINE.uom_ext_attr6,
3668 SRCLINE.uom_ext_attr7,
3669 SRCLINE.uom_ext_attr8,
3670 SRCLINE.uom_ext_attr9,
3671 SRCLINE.uom_ext_attr10,
3672 SRCLINE.uom_ext_attr11,
3673 SRCLINE.uom_ext_attr12,
3674 SRCLINE.uom_ext_attr13,
3675 SRCLINE.uom_ext_attr14,
3676 SRCLINE.uom_ext_attr15,
3677 SRCLINE.uom_ext_attr16,
3678 SRCLINE.uom_ext_attr17,
3679 SRCLINE.uom_ext_attr18,
3680 SRCLINE.uom_ext_attr19,
3681 SRCLINE.uom_ext_attr20,
3682 SRCLINE.d_ext_attr1,
3683 SRCLINE.d_ext_attr2,
3684 SRCLINE.d_ext_attr3,
3685 SRCLINE.d_ext_attr4,
3686 SRCLINE.d_ext_attr5,
3687 SRCLINE.d_ext_attr6,
3688 SRCLINE.d_ext_attr7,
3689 SRCLINE.d_ext_attr8,
3690 SRCLINE.d_ext_attr9,
3691 SRCLINE.d_ext_attr10,
3692 SRCLINE.uda_template_id,
3693 SRCLINE.draft_id
3694 FROM po_lines_all_ext_b SRCLINE WHERE SRCLINE.po_line_id = po_src_line_id AND draft_id = p_draft_id
3695 AND NOT EXISTS (SELECT 1 FROM po_lines_all_ext_b pleb WHERE
3696 pleb.po_line_id =po_dest_line_ids_tbl(i) AND
3697 pleb.attr_group_id = SRCLINE.attr_group_id AND
3698 pleb.draft_id = SRCLINE.draft_id);
3699
3700
3701 d_progress := '40';
3702 selectStmt := selectStmt ||
3703 ' SELECT extension_id,attr_group_id,po_line_id,data_level_id,uda_template_id
3704 FROM po_lines_all_ext_b
3705 WHERE po_line_id IN ( ' || po_dest_line_ids || ') AND draft_id = '|| p_draft_id;
3706
3707 d_progress := '50';
3708
3709 EXECUTE IMMEDIATE selectStmt BULK COLLECT INTO extension_id_tbl,attr_group_id_tbl,po_line_id_tbl,data_level_id_tbl,uda_template_id_tbl;
3710
3711 d_progress := '60';
3712
3713
3714 FORALL i IN 1..extension_id_tbl.Count
3715 INSERT INTO po_lines_all_ext_tl
3716 (
3717 EXTENSION_ID,
3718 ATTR_GROUP_ID,
3719 PO_LINE_ID,
3720 DATA_LEVEL_ID,
3721 SOURCE_LANG,
3722 LANGUAGE,
3723 LAST_UPDATE_DATE,
3724 LAST_UPDATED_BY,
3725 LAST_UPDATE_LOGIN,
3726 CREATED_BY,
3727 CREATION_DATE,
3728 UDA_TEMPLATE_ID,
3729 DRAFT_ID
3730 )
3731 SELECT
3732
3733 extension_id_tbl(i),
3734 attr_group_id_tbl(i),
3735 po_line_id_tbl(i),
3736 data_level_id_tbl(i),
3737 USERENV('LANG'),
3738 L.LANGUAGE_CODE,
3739 SYSDATE,
3740 0,
3741 0,
3742 0,
3743 SYSDATE,
3744 uda_template_id_tbl(i),
3745 p_draft_id
3746
3747 FROM FND_LANGUAGES L
3748 WHERE L.INSTALLED_FLAG in ('I', 'B')
3749 AND NOT EXISTS
3750 (SELECT 1 FROM po_lines_all_ext_tl pltl WHERE
3751 pltl.extension_id = extension_id_tbl(i)
3752 AND pltl.LANGUAGE = L.LANGUAGE_CODE);
3753
3754 d_progress := '70';
3755
3756 DELETE FROM po_lines_all_ext_b WHERE po_line_id = po_src_line_id AND draft_id = p_draft_id;
3757
3758 d_progress := '80';
3759
3760 EXCEPTION
3761 WHEN OTHERS THEN
3762 IF PO_LOG.d_stmt THEN
3763 PO_LOG.stmt(d_module, d_progress, 'Exception : ', SQLERRM );
3764 END IF;
3765 END GLOBAL_UPDATE_LINE_UDA;
3766
3767 PROCEDURE global_update_shipment_uda
3768 (
3769 po_src_line_location_id IN NUMBER ,
3770 po_dest_line_location_ids_tbl IN PO_TBL_NUMBER,
3771 p_draft_id IN NUMBER
3772 )
3773
3774 IS
3775
3776 d_api_name CONSTANT VARCHAR2(30) := 'global_update_shipment_uda';
3777 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3778
3779 po_dest_line_location_ids VARCHAR2(32000) := '';
3780 extension_id_tbl PO_TBL_NUMBER;
3781 attr_group_id_tbl PO_TBL_NUMBER;
3782 line_location_id_tbl PO_TBL_NUMBER;
3783 data_level_id_tbl PO_TBL_NUMBER;
3784 uda_template_id_tbl PO_TBL_NUMBER;
3785 l_ship_to_loc_id NUMBER ;
3786 get_valid_line_loc_stmt VARCHAR2(32000) := '';
3787 get_valid_line_loc_tbl PO_TBL_NUMBER ;
3788 mergeStmt VARCHAR(20000) := '';
3789 selectStmt VARCHAR(2000) := '';
3790 d_progress NUMBER := 0 ;
3791
3792
3793 BEGIN
3794
3795 IF (PO_LOG.d_proc) THEN
3796 PO_LOG.proc_begin(d_module);
3797 PO_LOG.proc_begin(d_module, 'po_src_line_location_id', po_src_line_location_id);
3798 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3799 END IF;
3800
3801 FOR i IN 1..po_dest_line_location_ids_tbl.Count LOOP
3802 po_dest_line_location_ids := po_dest_line_location_ids || po_dest_line_location_ids_tbl(i) || ',' ;
3803 END LOOP ;
3804
3805 po_dest_line_location_ids := RTrim(po_dest_line_location_ids,',');
3806
3807 d_progress := 10;
3808
3809 --Copy the uda from base if it is mod and no row exists in mod
3810
3811 IF p_draft_id > 0 THEN
3812
3813 FORALL i IN 1..po_dest_line_location_ids_tbl.Count
3814 INSERT INTO po_line_locations_all_ext_b DESTlINE
3815 (DESTLINE.extension_id,
3816 DESTLINE.attr_group_id,
3817 DESTLINE.line_location_id,
3818 DESTLINE.data_level_id,
3819 DESTLINE.pk1_value,
3820 DESTLINE.pk2_value,
3821 DESTLINE.pk3_value,
3822 DESTLINE.pk4_value,
3823 DESTLINE.pk5_value,
3824 DESTLINE.last_update_date,
3825 DESTLINE.last_updated_by,
3826 DESTLINE.last_update_login,
3827 DESTLINE.created_by,
3828 DESTLINE.creation_date,
3829 DESTLINE.c_ext_attr1,
3830 DESTLINE.c_ext_attr2,
3831 DESTLINE.c_ext_attr3,
3832 DESTLINE.c_ext_attr4,
3833 DESTLINE.c_ext_attr5,
3834 DESTLINE.c_ext_attr6,
3835 DESTLINE.c_ext_attr7,
3836 DESTLINE.c_ext_attr8,
3837 DESTLINE.c_ext_attr9,
3838 DESTLINE.c_ext_attr10,
3839 DESTLINE.c_ext_attr11,
3840 DESTLINE.c_ext_attr12,
3841 DESTLINE.c_ext_attr13,
3842 DESTLINE.c_ext_attr14,
3843 DESTLINE.c_ext_attr15,
3844 DESTLINE.c_ext_attr16,
3845 DESTLINE.c_ext_attr17,
3846 DESTLINE.c_ext_attr18,
3847 DESTLINE.c_ext_attr19,
3848 DESTLINE.c_ext_attr20,
3849 DESTLINE.c_ext_attr21,
3850 DESTLINE.c_ext_attr22,
3851 DESTLINE.c_ext_attr23,
3852 DESTLINE.c_ext_attr24,
3853 DESTLINE.c_ext_attr25,
3854 DESTLINE.c_ext_attr26,
3855 DESTLINE.c_ext_attr27,
3856 DESTLINE.c_ext_attr28,
3857 DESTLINE.c_ext_attr29,
3858 DESTLINE.c_ext_attr30,
3859 DESTLINE.c_ext_attr31,
3860 DESTLINE.c_ext_attr32,
3861 DESTLINE.c_ext_attr33,
3862 DESTLINE.c_ext_attr34,
3863 DESTLINE.c_ext_attr35,
3864 DESTLINE.c_ext_attr36,
3865 DESTLINE.c_ext_attr37,
3866 DESTLINE.c_ext_attr38,
3867 DESTLINE.c_ext_attr39,
3868 DESTLINE.c_ext_attr40,
3869 DESTLINE.n_ext_attr1,
3870 DESTLINE.n_ext_attr2,
3871 DESTLINE.n_ext_attr3,
3872 DESTLINE.n_ext_attr4,
3873 DESTLINE.n_ext_attr5,
3874 DESTLINE.n_ext_attr6,
3875 DESTLINE.n_ext_attr7,
3876 DESTLINE.n_ext_attr8,
3877 DESTLINE.n_ext_attr9,
3878 DESTLINE.n_ext_attr10,
3879 DESTLINE.n_ext_attr11,
3880 DESTLINE.n_ext_attr12,
3881 DESTLINE.n_ext_attr13,
3882 DESTLINE.n_ext_attr14,
3883 DESTLINE.n_ext_attr15,
3884 DESTLINE.n_ext_attr16,
3885 DESTLINE.n_ext_attr17,
3886 DESTLINE.n_ext_attr18,
3887 DESTLINE.n_ext_attr19,
3888 DESTLINE.n_ext_attr20,
3889 DESTLINE.uom_ext_attr1,
3890 DESTLINE.uom_ext_attr2,
3891 DESTLINE.uom_ext_attr3,
3892 DESTLINE.uom_ext_attr4,
3893 DESTLINE.uom_ext_attr5,
3894 DESTLINE.uom_ext_attr6,
3895 DESTLINE.uom_ext_attr7,
3896 DESTLINE.uom_ext_attr8,
3897 DESTLINE.uom_ext_attr9,
3898 DESTLINE.uom_ext_attr10,
3899 DESTLINE.uom_ext_attr11,
3900 DESTLINE.uom_ext_attr12,
3901 DESTLINE.uom_ext_attr13,
3902 DESTLINE.uom_ext_attr14,
3903 DESTLINE.uom_ext_attr15,
3904 DESTLINE.uom_ext_attr16,
3905 DESTLINE.uom_ext_attr17,
3906 DESTLINE.uom_ext_attr18,
3907 DESTLINE.uom_ext_attr19,
3908 DESTLINE.uom_ext_attr20,
3909 DESTLINE.d_ext_attr1,
3910 DESTLINE.d_ext_attr2,
3911 DESTLINE.d_ext_attr3,
3912 DESTLINE.d_ext_attr4,
3913 DESTLINE.d_ext_attr5,
3914 DESTLINE.d_ext_attr6,
3915 DESTLINE.d_ext_attr7,
3916 DESTLINE.d_ext_attr8,
3917 DESTLINE.d_ext_attr9,
3918 DESTLINE.d_ext_attr10,
3919 DESTLINE.uda_template_id,
3920 DESTLINE.draft_id)
3921 SELECT
3922 EGO_EXTFWK_S.NEXTVAL,
3923 SRCLINE.attr_group_id,
3924 po_dest_line_location_ids_tbl(i),
3925 SRCLINE.data_level_id,
3926 SRCLINE.pk1_value,
3927 SRCLINE.pk2_value,
3928 SRCLINE.pk3_value,
3929 SRCLINE.pk4_value,
3930 SRCLINE.pk5_value,
3931 SRCLINE.last_update_date,
3932 SRCLINE.last_updated_by,
3933 SRCLINE.last_update_login,
3934 SRCLINE.created_by,
3935 SRCLINE.creation_date,
3936 SRCLINE.c_ext_attr1,
3937 SRCLINE.c_ext_attr2,
3938 SRCLINE.c_ext_attr3,
3939 SRCLINE.c_ext_attr4,
3940 SRCLINE.c_ext_attr5,
3941 SRCLINE.c_ext_attr6,
3942 SRCLINE.c_ext_attr7,
3943 SRCLINE.c_ext_attr8,
3944 SRCLINE.c_ext_attr9,
3945 SRCLINE.c_ext_attr10,
3946 SRCLINE.c_ext_attr11,
3947 SRCLINE.c_ext_attr12,
3948 SRCLINE.c_ext_attr13,
3949 SRCLINE.c_ext_attr14,
3950 SRCLINE.c_ext_attr15,
3951 SRCLINE.c_ext_attr16,
3952 SRCLINE.c_ext_attr17,
3953 SRCLINE.c_ext_attr18,
3954 SRCLINE.c_ext_attr19,
3955 SRCLINE.c_ext_attr20,
3956 SRCLINE.c_ext_attr21,
3957 SRCLINE.c_ext_attr22,
3958 SRCLINE.c_ext_attr23,
3959 SRCLINE.c_ext_attr24,
3960 SRCLINE.c_ext_attr25,
3961 SRCLINE.c_ext_attr26,
3962 SRCLINE.c_ext_attr27,
3963 SRCLINE.c_ext_attr28,
3964 SRCLINE.c_ext_attr29,
3965 SRCLINE.c_ext_attr30,
3966 SRCLINE.c_ext_attr31,
3967 SRCLINE.c_ext_attr32,
3968 SRCLINE.c_ext_attr33,
3969 SRCLINE.c_ext_attr34,
3970 SRCLINE.c_ext_attr35,
3971 SRCLINE.c_ext_attr36,
3972 SRCLINE.c_ext_attr37,
3973 SRCLINE.c_ext_attr38,
3974 SRCLINE.c_ext_attr39,
3975 SRCLINE.c_ext_attr40,
3976 SRCLINE.n_ext_attr1,
3977 SRCLINE.n_ext_attr2,
3978 SRCLINE.n_ext_attr3,
3979 SRCLINE.n_ext_attr4,
3980 SRCLINE.n_ext_attr5,
3981 SRCLINE.n_ext_attr6,
3982 SRCLINE.n_ext_attr7,
3983 SRCLINE.n_ext_attr8,
3984 SRCLINE.n_ext_attr9,
3985 SRCLINE.n_ext_attr10,
3986 SRCLINE.n_ext_attr11,
3987 SRCLINE.n_ext_attr12,
3988 SRCLINE.n_ext_attr13,
3989 SRCLINE.n_ext_attr14,
3990 SRCLINE.n_ext_attr15,
3991 SRCLINE.n_ext_attr16,
3992 SRCLINE.n_ext_attr17,
3993 SRCLINE.n_ext_attr18,
3994 SRCLINE.n_ext_attr19,
3995 SRCLINE.n_ext_attr20,
3996 SRCLINE.uom_ext_attr1,
3997 SRCLINE.uom_ext_attr2,
3998 SRCLINE.uom_ext_attr3,
3999 SRCLINE.uom_ext_attr4,
4000 SRCLINE.uom_ext_attr5,
4001 SRCLINE.uom_ext_attr6,
4002 SRCLINE.uom_ext_attr7,
4003 SRCLINE.uom_ext_attr8,
4004 SRCLINE.uom_ext_attr9,
4005 SRCLINE.uom_ext_attr10,
4006 SRCLINE.uom_ext_attr11,
4007 SRCLINE.uom_ext_attr12,
4008 SRCLINE.uom_ext_attr13,
4009 SRCLINE.uom_ext_attr14,
4010 SRCLINE.uom_ext_attr15,
4011 SRCLINE.uom_ext_attr16,
4012 SRCLINE.uom_ext_attr17,
4013 SRCLINE.uom_ext_attr18,
4014 SRCLINE.uom_ext_attr19,
4015 SRCLINE.uom_ext_attr20,
4016 SRCLINE.d_ext_attr1,
4017 SRCLINE.d_ext_attr2,
4018 SRCLINE.d_ext_attr3,
4019 SRCLINE.d_ext_attr4,
4020 SRCLINE.d_ext_attr5,
4021 SRCLINE.d_ext_attr6,
4022 SRCLINE.d_ext_attr7,
4023 SRCLINE.d_ext_attr8,
4024 SRCLINE.d_ext_attr9,
4025 SRCLINE.d_ext_attr10,
4026 SRCLINE.uda_template_id,
4027 SRCLINE.draft_id
4028 FROM po_line_locations_all_ext_b SRCLINE WHERE SRCLINE.line_location_id = po_src_line_location_id AND draft_id = -1
4029 AND NOT EXISTS (SELECT 1 FROM po_line_locations_all_ext_b pleb WHERE
4030 pleb.line_location_id =po_dest_line_location_ids_tbl(i) AND
4031 pleb.attr_group_id = SRCLINE.attr_group_id AND
4032 pleb.draft_id = SRCLINE.draft_id);
4033
4034 END IF ;
4035
4036 mergeStmt := mergeStmt ||
4037
4038 'MERGE INTO po_line_locations_all_ext_b DESTLINE
4039 USING (
4040 SELECT
4041 attr_group_id,
4042 line_location_id,
4043 data_level_id,
4044 last_update_date,
4045 last_updated_by,
4046 last_update_login,
4047 creation_date,
4048 created_by,
4049 pk1_value,
4050 pk2_value,
4051 pk3_value,
4052 pk4_value,
4053 pk5_value,
4054 c_ext_attr1,
4055 c_ext_attr2,
4056 c_ext_attr3,
4057 c_ext_attr4,
4058 c_ext_attr5,
4059 c_ext_attr6,
4060 c_ext_attr7,
4061 c_ext_attr8,
4062 c_ext_attr9,
4063 c_ext_attr14,
4064 c_ext_attr15,
4065 c_ext_attr16,
4066 c_ext_attr17,
4067 c_ext_attr18,
4068 c_ext_attr19,
4069 c_ext_attr20,
4070 c_ext_attr21,
4071 c_ext_attr22,
4072 c_ext_attr23,
4073 c_ext_attr24,
4074 c_ext_attr25,
4075 c_ext_attr26,
4076 c_ext_attr27,
4077 c_ext_attr28,
4078 c_ext_attr29,
4079 c_ext_attr30,
4080 c_ext_attr31,
4081 c_ext_attr32,
4082 c_ext_attr33,
4083 c_ext_attr34,
4084 c_ext_attr35,
4085 c_ext_attr36,
4086 c_ext_attr37,
4087 c_ext_attr38,
4088 c_ext_attr39,
4089 c_ext_attr40,
4090 n_ext_attr1,
4091 n_ext_attr2,
4092 n_ext_attr3,
4093 n_ext_attr4,
4094 n_ext_attr5,
4095 n_ext_attr6,
4096 n_ext_attr7,
4097 n_ext_attr8,
4098 n_ext_attr9,
4099 n_ext_attr10,
4100 n_ext_attr11,
4101 n_ext_attr12,
4102 n_ext_attr13,
4103 n_ext_attr14,
4104 n_ext_attr15,
4105 n_ext_attr16,
4106 n_ext_attr17,
4107 n_ext_attr18,
4108 n_ext_attr19,
4109 n_ext_attr20,
4110 uom_ext_attr1,
4111 uom_ext_attr2,
4112 uom_ext_attr3,
4113 uom_ext_attr4,
4114 uom_ext_attr5,
4115 uom_ext_attr6,
4116 uom_ext_attr7,
4117 uom_ext_attr8,
4118 uom_ext_attr9,
4119 uom_ext_attr10,
4120 uom_ext_attr11,
4121 uom_ext_attr12,
4122 uom_ext_attr13,
4123 uom_ext_attr14,
4124 uom_ext_attr15,
4125 uom_ext_attr16,
4126 uom_ext_attr17,
4127 uom_ext_attr18,
4128 uom_ext_attr19,
4129 uom_ext_attr20,
4130 d_ext_attr1,
4131 d_ext_attr2,
4132 d_ext_attr3,
4133 d_ext_attr4,
4134 d_ext_attr5,
4135 d_ext_attr6,
4136 d_ext_attr7,
4137 d_ext_attr8,
4138 d_ext_attr9,
4139 d_ext_attr10,
4140 uda_template_id,
4141 draft_id
4142 FROM po_line_locations_all_ext_b WHERE line_location_id = ' || po_src_line_location_id || ' and draft_id = ' || p_draft_id || ' ) SRCLINE
4143 ON
4144 (DESTLINE.line_location_id IN (' || po_dest_line_location_ids || ') AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND DESTLINE.draft_id = '|| p_draft_id ||')
4145 WHEN MATCHED THEN
4146 UPDATE
4147 SET
4148 DESTLINE.last_update_date = SYSDATE ,
4149 DESTLINE.last_updated_by = SRCLINE.last_updated_by,
4150 DESTLINE.last_update_login = SRCLINE.last_update_login,
4151 DESTLINE.created_by = SRCLINE.created_by,
4152 DESTLINE.creation_date = SYSDATE,
4153 DESTLINE.c_ext_attr1 = nvl(SRCLINE.c_ext_attr1 , DESTLINE.c_ext_attr1),
4154 DESTLINE.c_ext_attr2 = nvl(SRCLINE.c_ext_attr2 , DESTLINE.c_ext_attr2),
4155 DESTLINE.c_ext_attr3 = nvl(SRCLINE.c_ext_attr3 , DESTLINE.c_ext_attr3),
4156 DESTLINE.c_ext_attr4 = nvl(SRCLINE.c_ext_attr4 , DESTLINE.c_ext_attr4),
4157 DESTLINE.c_ext_attr5 = nvl(SRCLINE.c_ext_attr5 , DESTLINE.c_ext_attr5),
4158 DESTLINE.c_ext_attr6 = nvl(SRCLINE.c_ext_attr6 , DESTLINE.c_ext_attr6),
4159 DESTLINE.c_ext_attr7 = nvl(SRCLINE.c_ext_attr7 , DESTLINE.c_ext_attr7),
4160 DESTLINE.c_ext_attr8 = nvl(SRCLINE.c_ext_attr8 , DESTLINE.c_ext_attr8),
4161 DESTLINE.c_ext_attr9 = nvl(SRCLINE.c_ext_attr9 , DESTLINE.c_ext_attr9),
4162 DESTLINE.c_ext_attr14 = nvl(SRCLINE.c_ext_attr14 , DESTLINE.c_ext_attr14),
4163 DESTLINE.c_ext_attr15 = nvl(SRCLINE.c_ext_attr15 , DESTLINE.c_ext_attr15),
4164 DESTLINE.c_ext_attr16 = nvl(SRCLINE.c_ext_attr16 , DESTLINE.c_ext_attr16),
4165 DESTLINE.c_ext_attr17 = nvl(SRCLINE.c_ext_attr17 , DESTLINE.c_ext_attr17),
4166 DESTLINE.c_ext_attr18 = nvl(SRCLINE.c_ext_attr18 , DESTLINE.c_ext_attr18),
4167 DESTLINE.c_ext_attr19 = nvl(SRCLINE.c_ext_attr19 , DESTLINE.c_ext_attr19),
4168 DESTLINE.c_ext_attr20 = nvl(SRCLINE.c_ext_attr20 , DESTLINE.c_ext_attr20),
4169 DESTLINE.c_ext_attr21 = nvl(SRCLINE.c_ext_attr21 , DESTLINE.c_ext_attr21),
4170 DESTLINE.c_ext_attr22 = nvl(SRCLINE.c_ext_attr22 , DESTLINE.c_ext_attr22),
4171 DESTLINE.c_ext_attr23 = nvl(SRCLINE.c_ext_attr23 , DESTLINE.c_ext_attr23),
4172 DESTLINE.c_ext_attr24 = nvl(SRCLINE.c_ext_attr24 , DESTLINE.c_ext_attr24),
4173 DESTLINE.c_ext_attr25 = nvl(SRCLINE.c_ext_attr25 , DESTLINE.c_ext_attr25),
4174 DESTLINE.c_ext_attr26 = nvl(SRCLINE.c_ext_attr26 , DESTLINE.c_ext_attr26),
4175 DESTLINE.c_ext_attr27 = nvl(SRCLINE.c_ext_attr27 , DESTLINE.c_ext_attr27),
4176 DESTLINE.c_ext_attr28 = nvl(SRCLINE.c_ext_attr28 , DESTLINE.c_ext_attr28),
4177 DESTLINE.c_ext_attr29 = nvl(SRCLINE.c_ext_attr29 , DESTLINE.c_ext_attr29),
4178 DESTLINE.c_ext_attr30 = nvl(SRCLINE.c_ext_attr30 , DESTLINE.c_ext_attr30),
4179 DESTLINE.c_ext_attr31 = nvl(SRCLINE.c_ext_attr31 , DESTLINE.c_ext_attr31),
4180 DESTLINE.c_ext_attr32 = nvl(SRCLINE.c_ext_attr32 , DESTLINE.c_ext_attr32),
4181 DESTLINE.c_ext_attr33 = nvl(SRCLINE.c_ext_attr33 , DESTLINE.c_ext_attr33),
4182 DESTLINE.c_ext_attr34 = nvl(SRCLINE.c_ext_attr34 , DESTLINE.c_ext_attr34),
4183 DESTLINE.c_ext_attr35 = nvl(SRCLINE.c_ext_attr35 , DESTLINE.c_ext_attr35),
4184 DESTLINE.c_ext_attr36 = nvl(SRCLINE.c_ext_attr36 , DESTLINE.c_ext_attr36),
4185 DESTLINE.c_ext_attr37 = nvl(SRCLINE.c_ext_attr37 , DESTLINE.c_ext_attr37),
4186 DESTLINE.c_ext_attr38 = nvl(SRCLINE.c_ext_attr38 , DESTLINE.c_ext_attr38),
4187 DESTLINE.c_ext_attr39 = nvl(SRCLINE.c_ext_attr39 , DESTLINE.c_ext_attr39),
4188 DESTLINE.c_ext_attr40 = nvl(SRCLINE.c_ext_attr40 , DESTLINE.c_ext_attr40),
4189 DESTLINE.n_ext_attr1 = nvl(SRCLINE.n_ext_attr1 , DESTLINE.n_ext_attr1),
4190 DESTLINE.n_ext_attr2 = nvl(SRCLINE.n_ext_attr2 , DESTLINE.n_ext_attr2),
4191 DESTLINE.n_ext_attr3 = nvl(SRCLINE.n_ext_attr3 , DESTLINE.n_ext_attr3),
4192 DESTLINE.n_ext_attr4 = nvl(SRCLINE.n_ext_attr4 , DESTLINE.n_ext_attr4),
4193 DESTLINE.n_ext_attr5 = nvl(SRCLINE.n_ext_attr5 , DESTLINE.n_ext_attr5),
4194 DESTLINE.n_ext_attr6 = nvl(SRCLINE.n_ext_attr6 , DESTLINE.n_ext_attr6),
4195 DESTLINE.n_ext_attr7 = nvl(SRCLINE.n_ext_attr7 , DESTLINE.n_ext_attr7),
4196 DESTLINE.n_ext_attr8 = nvl(SRCLINE.n_ext_attr8 , DESTLINE.n_ext_attr8),
4197 DESTLINE.n_ext_attr9 = nvl(SRCLINE.n_ext_attr9 , DESTLINE.n_ext_attr9),
4198 DESTLINE.n_ext_attr10 = nvl(SRCLINE.n_ext_attr10 , DESTLINE.n_ext_attr10),
4199 DESTLINE.n_ext_attr11 = nvl(SRCLINE.n_ext_attr11 , DESTLINE.n_ext_attr11),
4200 DESTLINE.n_ext_attr12 = nvl(SRCLINE.n_ext_attr12 , DESTLINE.n_ext_attr12),
4201 DESTLINE.n_ext_attr13 = nvl(SRCLINE.n_ext_attr13 , DESTLINE.n_ext_attr13),
4202 DESTLINE.n_ext_attr14 = nvl(SRCLINE.n_ext_attr14 , DESTLINE.n_ext_attr14),
4203 DESTLINE.n_ext_attr15 = nvl(SRCLINE.n_ext_attr15 , DESTLINE.n_ext_attr15),
4204 DESTLINE.n_ext_attr16 = nvl(SRCLINE.n_ext_attr16 , DESTLINE.n_ext_attr16),
4205 DESTLINE.n_ext_attr17 = nvl(SRCLINE.n_ext_attr17 , DESTLINE.n_ext_attr17),
4206 DESTLINE.n_ext_attr18 = nvl(SRCLINE.n_ext_attr18 , DESTLINE.n_ext_attr18),
4207 DESTLINE.n_ext_attr19 = nvl(SRCLINE.n_ext_attr19 , DESTLINE.n_ext_attr19),
4208 DESTLINE.n_ext_attr20 = nvl(SRCLINE.n_ext_attr20 , DESTLINE.n_ext_attr20),
4209 DESTLINE.uom_ext_attr1 = nvl(SRCLINE.uom_ext_attr1 , DESTLINE.uom_ext_attr1),
4210 DESTLINE.uom_ext_attr2 = nvl(SRCLINE.uom_ext_attr2 , DESTLINE.uom_ext_attr2),
4211 DESTLINE.uom_ext_attr3 = nvl(SRCLINE.uom_ext_attr3 , DESTLINE.uom_ext_attr3),
4212 DESTLINE.uom_ext_attr4 = nvl(SRCLINE.uom_ext_attr4 , DESTLINE.uom_ext_attr4),
4213 DESTLINE.uom_ext_attr5 = nvl(SRCLINE.uom_ext_attr5 , DESTLINE.uom_ext_attr5),
4214 DESTLINE.uom_ext_attr6 = nvl(SRCLINE.uom_ext_attr6 , DESTLINE.uom_ext_attr6),
4215 DESTLINE.uom_ext_attr7 = nvl(SRCLINE.uom_ext_attr7 , DESTLINE.uom_ext_attr7),
4216 DESTLINE.uom_ext_attr8 = nvl(SRCLINE.uom_ext_attr8 , DESTLINE.uom_ext_attr8),
4217 DESTLINE.uom_ext_attr9 = nvl(SRCLINE.uom_ext_attr9 , DESTLINE.uom_ext_attr9),
4218 DESTLINE.uom_ext_attr10 = nvl(SRCLINE.uom_ext_attr10 , DESTLINE.uom_ext_attr10),
4219 DESTLINE.uom_ext_attr11 = nvl(SRCLINE.uom_ext_attr11 , DESTLINE.uom_ext_attr11),
4220 DESTLINE.uom_ext_attr12 = nvl(SRCLINE.uom_ext_attr12 , DESTLINE.uom_ext_attr12),
4221 DESTLINE.uom_ext_attr13 = nvl(SRCLINE.uom_ext_attr13 , DESTLINE.uom_ext_attr13),
4222 DESTLINE.uom_ext_attr14 = nvl(SRCLINE.uom_ext_attr14 , DESTLINE.uom_ext_attr14),
4223 DESTLINE.uom_ext_attr15 = nvl(SRCLINE.uom_ext_attr15 , DESTLINE.uom_ext_attr15),
4224 DESTLINE.uom_ext_attr16 = nvl(SRCLINE.uom_ext_attr16 , DESTLINE.uom_ext_attr16),
4225 DESTLINE.uom_ext_attr17 = nvl(SRCLINE.uom_ext_attr17 , DESTLINE.uom_ext_attr17),
4226 DESTLINE.uom_ext_attr18 = nvl(SRCLINE.uom_ext_attr18 , DESTLINE.uom_ext_attr18),
4227 DESTLINE.uom_ext_attr19 = nvl(SRCLINE.uom_ext_attr19 , DESTLINE.uom_ext_attr19),
4228 DESTLINE.uom_ext_attr20 = nvl(SRCLINE.uom_ext_attr20 , DESTLINE.uom_ext_attr20),
4229 DESTLINE.d_ext_attr1 = nvl(SRCLINE.d_ext_attr1 , DESTLINE.d_ext_attr1),
4230 DESTLINE.d_ext_attr2 = nvl(SRCLINE.d_ext_attr2 , DESTLINE.d_ext_attr2),
4231 DESTLINE.d_ext_attr3 = nvl(SRCLINE.d_ext_attr3 , DESTLINE.d_ext_attr3),
4232 DESTLINE.d_ext_attr4 = nvl(SRCLINE.d_ext_attr4 , DESTLINE.d_ext_attr4),
4233 DESTLINE.d_ext_attr5 = nvl(SRCLINE.d_ext_attr5 , DESTLINE.d_ext_attr5),
4234 DESTLINE.d_ext_attr6 = nvl(SRCLINE.d_ext_attr6 , DESTLINE.d_ext_attr6),
4235 DESTLINE.d_ext_attr7 = nvl(SRCLINE.d_ext_attr7 , DESTLINE.d_ext_attr7),
4236 DESTLINE.d_ext_attr8 = nvl(SRCLINE.d_ext_attr8 , DESTLINE.d_ext_attr8),
4237 DESTLINE.d_ext_attr9 = nvl(SRCLINE.d_ext_attr9 , DESTLINE.d_ext_attr9),
4238 DESTLINE.d_ext_attr10 = nvl(SRCLINE.d_ext_attr10 , DESTLINE.d_ext_attr10)' ;
4239
4240 d_progress := '20';
4241
4242 EXECUTE IMMEDIATE mergeStmt ;
4243
4244 d_progress := '30';
4245
4246 FORALL i IN 1..po_dest_line_location_ids_tbl.Count
4247
4248 INSERT INTO po_line_locations_all_ext_b DESTlINE
4249 (DESTLINE.extension_id,
4250 DESTLINE.attr_group_id,
4251 DESTLINE.line_location_id,
4252 DESTLINE.data_level_id,
4253 DESTLINE.pk1_value,
4254 DESTLINE.pk2_value,
4255 DESTLINE.pk3_value,
4256 DESTLINE.pk4_value,
4257 DESTLINE.pk5_value,
4258 DESTLINE.last_update_date,
4259 DESTLINE.last_updated_by,
4260 DESTLINE.last_update_login,
4261 DESTLINE.created_by,
4262 DESTLINE.creation_date,
4263 DESTLINE.c_ext_attr1,
4264 DESTLINE.c_ext_attr2,
4265 DESTLINE.c_ext_attr3,
4266 DESTLINE.c_ext_attr4,
4267 DESTLINE.c_ext_attr5,
4268 DESTLINE.c_ext_attr6,
4269 DESTLINE.c_ext_attr7,
4270 DESTLINE.c_ext_attr8,
4271 DESTLINE.c_ext_attr9,
4272 DESTLINE.c_ext_attr14,
4273 DESTLINE.c_ext_attr15,
4274 DESTLINE.c_ext_attr16,
4275 DESTLINE.c_ext_attr17,
4276 DESTLINE.c_ext_attr18,
4277 DESTLINE.c_ext_attr19,
4278 DESTLINE.c_ext_attr20,
4279 DESTLINE.c_ext_attr21,
4280 DESTLINE.c_ext_attr22,
4281 DESTLINE.c_ext_attr23,
4282 DESTLINE.c_ext_attr24,
4283 DESTLINE.c_ext_attr25,
4284 DESTLINE.c_ext_attr26,
4285 DESTLINE.c_ext_attr27,
4286 DESTLINE.c_ext_attr28,
4287 DESTLINE.c_ext_attr29,
4288 DESTLINE.c_ext_attr30,
4289 DESTLINE.c_ext_attr31,
4290 DESTLINE.c_ext_attr32,
4291 DESTLINE.c_ext_attr33,
4292 DESTLINE.c_ext_attr34,
4293 DESTLINE.c_ext_attr35,
4294 DESTLINE.c_ext_attr36,
4295 DESTLINE.c_ext_attr37,
4296 DESTLINE.c_ext_attr38,
4297 DESTLINE.c_ext_attr39,
4298 DESTLINE.c_ext_attr40,
4299 DESTLINE.n_ext_attr1,
4300 DESTLINE.n_ext_attr2,
4301 DESTLINE.n_ext_attr3,
4302 DESTLINE.n_ext_attr4,
4303 DESTLINE.n_ext_attr5,
4304 DESTLINE.n_ext_attr6,
4305 DESTLINE.n_ext_attr7,
4306 DESTLINE.n_ext_attr8,
4307 DESTLINE.n_ext_attr9,
4308 DESTLINE.n_ext_attr10,
4309 DESTLINE.n_ext_attr11,
4310 DESTLINE.n_ext_attr12,
4311 DESTLINE.n_ext_attr13,
4312 DESTLINE.n_ext_attr14,
4313 DESTLINE.n_ext_attr15,
4314 DESTLINE.n_ext_attr16,
4315 DESTLINE.n_ext_attr17,
4316 DESTLINE.n_ext_attr18,
4317 DESTLINE.n_ext_attr19,
4318 DESTLINE.n_ext_attr20,
4319 DESTLINE.uom_ext_attr1,
4320 DESTLINE.uom_ext_attr2,
4321 DESTLINE.uom_ext_attr3,
4322 DESTLINE.uom_ext_attr4,
4323 DESTLINE.uom_ext_attr5,
4324 DESTLINE.uom_ext_attr6,
4325 DESTLINE.uom_ext_attr7,
4326 DESTLINE.uom_ext_attr8,
4327 DESTLINE.uom_ext_attr9,
4328 DESTLINE.uom_ext_attr10,
4329 DESTLINE.uom_ext_attr11,
4330 DESTLINE.uom_ext_attr12,
4331 DESTLINE.uom_ext_attr13,
4332 DESTLINE.uom_ext_attr14,
4333 DESTLINE.uom_ext_attr15,
4334 DESTLINE.uom_ext_attr16,
4335 DESTLINE.uom_ext_attr17,
4336 DESTLINE.uom_ext_attr18,
4337 DESTLINE.uom_ext_attr19,
4338 DESTLINE.uom_ext_attr20,
4339 DESTLINE.d_ext_attr1,
4340 DESTLINE.d_ext_attr2,
4341 DESTLINE.d_ext_attr3,
4342 DESTLINE.d_ext_attr4,
4343 DESTLINE.d_ext_attr5,
4344 DESTLINE.d_ext_attr6,
4345 DESTLINE.d_ext_attr7,
4346 DESTLINE.d_ext_attr8,
4347 DESTLINE.d_ext_attr9,
4348 DESTLINE.d_ext_attr10,
4349 DESTLINE.uda_template_id,
4350 DESTLINE.draft_id)
4351 SELECT
4352 EGO_EXTFWK_S.NEXTVAL,
4353 SRCLINE.attr_group_id,
4354 po_dest_line_location_ids_tbl(i),
4355 SRCLINE.data_level_id,
4356 SRCLINE.pk1_value,
4357 SRCLINE.pk2_value,
4358 SRCLINE.pk3_value,
4359 SRCLINE.pk4_value,
4360 SRCLINE.pk5_value,
4361 SRCLINE.last_update_date,
4362 SRCLINE.last_updated_by,
4363 SRCLINE.last_update_login,
4364 SRCLINE.created_by,
4365 SRCLINE.creation_date,
4366 SRCLINE.c_ext_attr1,
4367 SRCLINE.c_ext_attr2,
4368 SRCLINE.c_ext_attr3,
4369 SRCLINE.c_ext_attr4,
4370 SRCLINE.c_ext_attr5,
4371 SRCLINE.c_ext_attr6,
4372 SRCLINE.c_ext_attr7,
4373 SRCLINE.c_ext_attr8,
4374 SRCLINE.c_ext_attr9,
4375 SRCLINE.c_ext_attr14,
4376 SRCLINE.c_ext_attr15,
4377 SRCLINE.c_ext_attr16,
4378 SRCLINE.c_ext_attr17,
4379 SRCLINE.c_ext_attr18,
4380 SRCLINE.c_ext_attr19,
4381 SRCLINE.c_ext_attr20,
4382 SRCLINE.c_ext_attr21,
4383 SRCLINE.c_ext_attr22,
4384 SRCLINE.c_ext_attr23,
4385 SRCLINE.c_ext_attr24,
4386 SRCLINE.c_ext_attr25,
4387 SRCLINE.c_ext_attr26,
4388 SRCLINE.c_ext_attr27,
4389 SRCLINE.c_ext_attr28,
4390 SRCLINE.c_ext_attr29,
4391 SRCLINE.c_ext_attr30,
4392 SRCLINE.c_ext_attr31,
4393 SRCLINE.c_ext_attr32,
4394 SRCLINE.c_ext_attr33,
4395 SRCLINE.c_ext_attr34,
4396 SRCLINE.c_ext_attr35,
4397 SRCLINE.c_ext_attr36,
4398 SRCLINE.c_ext_attr37,
4399 SRCLINE.c_ext_attr38,
4400 SRCLINE.c_ext_attr39,
4401 SRCLINE.c_ext_attr40,
4402 SRCLINE.n_ext_attr1,
4403 SRCLINE.n_ext_attr2,
4404 SRCLINE.n_ext_attr3,
4405 SRCLINE.n_ext_attr4,
4406 SRCLINE.n_ext_attr5,
4407 SRCLINE.n_ext_attr6,
4408 SRCLINE.n_ext_attr7,
4409 SRCLINE.n_ext_attr8,
4410 SRCLINE.n_ext_attr9,
4411 SRCLINE.n_ext_attr10,
4412 SRCLINE.n_ext_attr11,
4413 SRCLINE.n_ext_attr12,
4414 SRCLINE.n_ext_attr13,
4415 SRCLINE.n_ext_attr14,
4416 SRCLINE.n_ext_attr15,
4417 SRCLINE.n_ext_attr16,
4418 SRCLINE.n_ext_attr17,
4419 SRCLINE.n_ext_attr18,
4420 SRCLINE.n_ext_attr19,
4421 SRCLINE.n_ext_attr20,
4422 SRCLINE.uom_ext_attr1,
4423 SRCLINE.uom_ext_attr2,
4424 SRCLINE.uom_ext_attr3,
4425 SRCLINE.uom_ext_attr4,
4426 SRCLINE.uom_ext_attr5,
4427 SRCLINE.uom_ext_attr6,
4428 SRCLINE.uom_ext_attr7,
4429 SRCLINE.uom_ext_attr8,
4430 SRCLINE.uom_ext_attr9,
4431 SRCLINE.uom_ext_attr10,
4432 SRCLINE.uom_ext_attr11,
4433 SRCLINE.uom_ext_attr12,
4434 SRCLINE.uom_ext_attr13,
4435 SRCLINE.uom_ext_attr14,
4436 SRCLINE.uom_ext_attr15,
4437 SRCLINE.uom_ext_attr16,
4438 SRCLINE.uom_ext_attr17,
4439 SRCLINE.uom_ext_attr18,
4440 SRCLINE.uom_ext_attr19,
4441 SRCLINE.uom_ext_attr20,
4442 SRCLINE.d_ext_attr1,
4443 SRCLINE.d_ext_attr2,
4444 SRCLINE.d_ext_attr3,
4445 SRCLINE.d_ext_attr4,
4446 SRCLINE.d_ext_attr5,
4447 SRCLINE.d_ext_attr6,
4448 SRCLINE.d_ext_attr7,
4449 SRCLINE.d_ext_attr8,
4450 SRCLINE.d_ext_attr9,
4451 SRCLINE.d_ext_attr10,
4452 SRCLINE.uda_template_id,
4453 SRCLINE.draft_id
4454 FROM po_line_locations_all_ext_b SRCLINE WHERE SRCLINE.line_location_id = po_src_line_location_id AND draft_id = p_draft_id
4455 AND NOT EXISTS (SELECT 1 FROM po_line_locations_all_ext_b pleb WHERE
4456 pleb.line_location_id =po_dest_line_location_ids_tbl(i) AND
4457 pleb.attr_group_id = SRCLINE.attr_group_id AND
4458 pleb.draft_id = SRCLINE.draft_id);
4459
4460
4461 --Get the ship to location to conditioonally update the ship to location
4462 SELECT SubStr(c_ext_attr13,Length('<SHIP_TO_LOCATION_ID>')+1,instr(c_ext_attr13,'</SHIP_TO_LOCATION_ID>')-(Length('<SHIP_TO_LOCATION_ID>')+1 )) ship_to_location_id
4463 INTO l_ship_to_loc_id
4464 FROM po_line_locations_all_ext_b
4465 WHERE line_location_id = po_src_line_location_id
4466 AND draft_id = p_draft_id;
4467
4468 --Get all the line location ids for which ship to location is valid.
4469 get_valid_line_loc_stmt := 'SELECT line_location_id
4470 FROM po_line_locations_merge_v
4471 WHERE line_location_id IN ( ' || po_dest_line_location_ids || ')
4472 AND nvl(draft_id,-1) = '|| p_draft_id ||
4473 ' AND 0 = PO_LOCATIONS_S.val_location('|| l_ship_to_loc_id||',null,org_id )';
4474
4475 --Find all the line location ids for which global update ship to location is to be updated.
4476 EXECUTE IMMEDIATE get_valid_line_loc_stmt BULK COLLECT INTO get_valid_line_loc_tbl;
4477
4478 --Update the ext table for all the valid line location ids.
4479
4480 MERGE INTO po_line_locations_all_ext_b DESTLINE
4481 USING (
4482 SELECT
4483 line_location_id,
4484 attr_group_id,
4485 draft_id,
4486 last_update_date,
4487 last_updated_by,
4488 last_update_login,
4489 creation_date,
4490 created_by,
4491 c_ext_attr10,
4492 c_ext_attr11,
4493 c_ext_attr12,
4494 c_ext_attr13
4495 FROM po_line_locations_all_ext_b WHERE line_location_id = po_src_line_location_id and draft_id = p_draft_id ) SRCLINE
4496 ON
4497 (DESTLINE.line_location_id IN (SELECT column_value FROM TABLE(get_valid_line_loc_tbl) ) AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND DESTLINE.draft_id = p_draft_id )
4498 WHEN MATCHED THEN
4499 UPDATE
4500 SET
4501 DESTLINE.last_update_date = SYSDATE ,
4502 DESTLINE.last_updated_by = SRCLINE.last_updated_by,
4503 DESTLINE.last_update_login = SRCLINE.last_update_login,
4504 DESTLINE.created_by = SRCLINE.created_by,
4505 DESTLINE.creation_date = SYSDATE,
4506 DESTLINE.c_ext_attr10 = nvl(SRCLINE.c_ext_attr10 , DESTLINE.c_ext_attr10),
4507 DESTLINE.c_ext_attr11 = nvl(SRCLINE.c_ext_attr11 , DESTLINE.c_ext_attr11),
4508 DESTLINE.c_ext_attr12 = nvl(SRCLINE.c_ext_attr12 , DESTLINE.c_ext_attr12),
4509 DESTLINE.c_ext_attr13 = nvl(SRCLINE.c_ext_attr13 , DESTLINE.c_ext_attr13);
4510
4511
4512 d_progress := '40';
4513 selectStmt := selectStmt ||
4514 ' SELECT extension_id,attr_group_id,line_location_id,data_level_id,uda_template_id
4515 FROM po_line_locations_all_ext_b
4516 WHERE line_location_id IN ( ' || po_dest_line_location_ids || ') AND draft_id = '|| p_draft_id;
4517
4518 d_progress := '50';
4519
4520 EXECUTE IMMEDIATE selectStmt BULK COLLECT INTO extension_id_tbl,attr_group_id_tbl,line_location_id_tbl,data_level_id_tbl,uda_template_id_tbl;
4521
4522 d_progress := '60';
4523
4524
4525 FORALL i IN 1..extension_id_tbl.Count
4526 INSERT INTO po_line_locations_all_ext_tl
4527 (
4528 EXTENSION_ID,
4529 ATTR_GROUP_ID,
4530 LINE_LOCATION_ID,
4531 DATA_LEVEL_ID,
4532 SOURCE_LANG,
4533 LANGUAGE,
4534 LAST_UPDATE_DATE,
4535 LAST_UPDATED_BY,
4536 LAST_UPDATE_LOGIN,
4537 CREATED_BY,
4538 CREATION_DATE,
4539 UDA_TEMPLATE_ID,
4540 DRAFT_ID
4541 )
4542 SELECT
4543
4544 extension_id_tbl(i),
4545 attr_group_id_tbl(i),
4546 line_location_id_tbl(i),
4547 data_level_id_tbl(i),
4548 USERENV('LANG'),
4549 L.LANGUAGE_CODE,
4550 SYSDATE,
4551 0,
4552 0,
4553 0,
4554 SYSDATE,
4555 uda_template_id_tbl(i),
4556 p_draft_id
4557
4558 FROM FND_LANGUAGES L
4559 WHERE L.INSTALLED_FLAG in ('I', 'B')
4560 AND NOT EXISTS
4561 (SELECT 1 FROM po_line_locations_all_ext_tl pltl WHERE
4562 pltl.extension_id = extension_id_tbl(i)
4563 AND pltl.LANGUAGE = L.LANGUAGE_CODE);
4564
4565 d_progress := '70';
4566
4567 DELETE FROM po_line_locations_all_ext_b WHERE line_location_id = po_src_line_location_id AND draft_id = p_draft_id;
4568
4569 d_progress := '80';
4570
4571 EXCEPTION
4572 WHEN OTHERS THEN
4573 IF PO_LOG.d_stmt THEN
4574 PO_LOG.stmt(d_module, d_progress, 'Exception : ', SQLERRM );
4575 END IF;
4576
4577 END GLOBAL_UPDATE_SHIPMENT_UDA;
4578
4579 function is_location_valid (
4580 p_location_id in number,
4581 p_address_type in varchar2
4582 ) return varchar2 is
4583
4584 d_api_name CONSTANT VARCHAR2(30) := 'is_location_valid';
4585 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4586 d_progress NUMBER := 0;
4587
4588 l_sql_stmt varchar2(2000);
4589 l_count number := -1 ;
4590
4591 begin
4592 -- using statement level logging to log the entry because this proc will be
4593 -- executed thousands of times
4594 IF PO_LOG.d_stmt THEN
4595 PO_LOG.stmt(d_module, d_progress, 'Entering');
4596 PO_LOG.stmt(d_module, d_progress, 'p_location_id', p_location_id);
4597 PO_LOG.stmt(d_module, d_progress, 'p_address_type', p_address_type);
4598 END IF;
4599
4600 d_progress := 10;
4601
4602
4603 IF p_address_type LIKE 'INV_OFFICE' THEN
4604
4605 l_sql_stmt := 'SELECT COUNT(*)
4606 FROM HR_LOCATIONS_ALL
4607 WHERE LOCATION_ID = :1
4608 AND BILL_TO_SITE_FLAG = ''Y''
4609 AND SYSDATE < NVL(INACTIVE_DATE, SYSDATE + 1)
4610 AND (NVL(BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99)) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99))';
4611
4612 ELSE
4613 l_sql_stmt := 'SELECT Count(*) FROM HR_LOCATION_EXTRA_INFO '
4614 ||' WHERE INFORMATION_TYPE = ''CLM_OFFICE_CODE'' '
4615 ||' AND location_id = :1 AND ';
4616
4617 IF p_address_type IN ('MOD_ADMIN_OFFICE', 'AMD_ADMIN_OFFICE') THEN
4618 l_sql_stmt := l_sql_stmt || g_address_eit_tbl ('ADMIN_OFFICE');
4619 ELSIF p_address_type IN ('MOD_ISSUING_OFFICE', 'AMD_ISSUING_OFFICE') THEN
4620 l_sql_stmt := l_sql_stmt || g_address_eit_tbl ('ISSUING_OFFICE');
4621 ELSE
4622 l_sql_stmt := l_sql_stmt || g_address_eit_tbl (p_address_type);
4623 END IF;
4624 l_sql_stmt := l_sql_stmt || ' = ''Y'' ';
4625 END IF;
4626
4627
4628 IF PO_LOG.d_stmt THEN
4629 PO_LOG.stmt(d_module, d_progress, 'l_sql_stmt', l_sql_stmt);
4630 END IF;
4631
4632 d_progress := 20;
4633 execute immediate l_sql_stmt into l_count using p_location_id;
4634
4635 IF PO_LOG.d_stmt THEN
4636 PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
4637 PO_LOG.stmt(d_module, d_progress, 'Exiting');
4638 END IF;
4639
4640 if l_count > 0 then
4641 return 'Y';
4642 else
4643 return 'N';
4644 end if;
4645
4646 exception
4647 when others then
4648 IF PO_LOG.d_stmt THEN
4649 PO_LOG.stmt(d_module, d_progress, 'Encountered exception', substr(SQLERRM, 1, 200));
4650 END IF;
4651
4652 return 'N';
4653 end is_location_valid;
4654
4655 function get_award_type (
4656 p_po_header_id in number,
4657 p_draft_id in number,
4658 x_return_status out nocopy varchar2,
4659 x_msg_data out nocopy varchar2
4660 ) return varchar2
4661 IS
4662 BEGIN
4663 return null;
4664 END get_award_type;
4665
4666
4667 --<Bug 13915026>
4668 ---------------------------------------------------------------------------
4669 --Start of Comments
4670 --Name: get_attr_group_id
4671 --Function:
4672 -- Gets attribute grp id for specific attribute category,
4673 -- idc type & contract type
4674 --Parameters:
4675 --IN:
4676 -- p_contract_type: contract type
4677 -- p_idc_type: idc type
4678 -- p_template_id: template id
4679 -- p_attr_category : attribute category
4680 --IN OUT:
4681 --OUT:
4682 -- x_attr_group_id : attribute group id
4683 -- x_return_status : return status
4684 --End of Comments
4685 ---------------------------------------------------------------------------
4686 PROCEDURE get_attr_group_id (
4687 p_contract_type IN VARCHAR2,
4688 p_idc_type IN VARCHAR2,
4689 p_template_id IN NUMBER,
4690 p_attr_category IN VARCHAR2,
4691 x_attr_group_id OUT NOCOPY NUMBER,
4692 x_return_status OUT NOCOPY VARCHAR2
4693 )
4694 IS
4695
4696 d_api_name CONSTANT VARCHAR2(30) := 'get_attr_group_id';
4697 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4698 d_progress NUMBER := 0;
4699 l_attr_group_id NUMBER := 0;
4700 l_template_id NUMBER;
4701
4702 BEGIN
4703 IF (PO_LOG.d_proc)
4704 THEN
4705 PO_LOG.proc_begin(d_module);
4706 PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707 PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4708 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4709 PO_LOG.proc_begin(d_module, 'p_attr_category', p_attr_category);
4710 END IF;
4711
4712 -- SQL What: Querying for attribute_group_id for a specific attribute category
4713 -- SQL Why: Need to attribute_group_id to copy specific attr grp uda data
4714 -- from base doc to mod
4715 -- SQL Join: po_uda_ag_template_usages
4716 SELECT attribute_group_id
4717 INTO l_attr_group_id
4718 FROM po_uda_ag_template_usages
4719 WHERE attribute2 = p_contract_type
4720 AND attribute1 = p_idc_type
4721 AND template_id = p_template_id
4722 AND attribute_category = p_attr_category;
4723
4724 x_return_status := FND_API.G_RET_STS_SUCCESS;
4725 x_attr_group_id := l_attr_group_id;
4726
4727 IF (PO_LOG.d_proc)
4728 THEN
4729 PO_LOG.proc_begin(d_module, 'x_attr_group_id', x_attr_group_id);
4730 PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
4731 PO_LOG.proc_end(d_module);
4732 END IF;
4733
4734 EXCEPTION
4735 WHEN OTHERS THEN
4736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4737
4738 IF PO_LOG.d_stmt THEN
4739 PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
4740 END IF;
4741
4742 IF (PO_LOG.d_proc) THEN
4743 PO_LOG.proc_end(d_module);
4744 END IF;
4745
4746 END get_attr_group_id;
4747
4748 BEGIN
4749
4750 g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_object_name := 'PO_HEADERS_ALL' ;
4751 g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_dff_name := 'PO_HEADER_EXT_ATTRS';
4752 g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_object_ext_b_table := 'PO_HEADERS_ALL_EXT_B';
4753 g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_object_ext_tl_table := 'PO_HEADERS_ALL_EXT_TL';
4754 g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_ag_mapping_column := 'PO_HEADER_GROUP_ID';
4755 g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_arch_object_name := 'PO_HEADERS_ARCHIVE_ALL' ;
4756
4757 g_object_dff_tl('PO_LINE_EXT_ATTRS').l_object_name := 'PO_LINES_ALL' ;
4758 g_object_dff_tl('PO_LINE_EXT_ATTRS').l_dff_name := 'PO_LINE_EXT_ATTRS';
4759 g_object_dff_tl('PO_LINE_EXT_ATTRS').l_object_ext_b_table := 'PO_LINES_ALL_EXT_B';
4760 g_object_dff_tl('PO_LINE_EXT_ATTRS').l_object_ext_tl_table := 'PO_LINES_ALL_EXT_TL';
4761 g_object_dff_tl('PO_LINE_EXT_ATTRS').l_ag_mapping_column := 'PO_LINE_GROUP_ID';
4762 g_object_dff_tl('PO_LINE_EXT_ATTRS').l_arch_object_name := 'PO_LINES_ARCHIVE_ALL' ;
4763
4764 g_object_dff_tl('PO_SHIPMENTS_EXT_ATTRS').l_object_name := 'PO_LINE_LOCATIONS_ALL' ;
4765 g_object_dff_tl('PO_SHIPMENTS_EXT_ATTRS').l_dff_name := 'PO_SHIPMENTS_EXT_ATTRS';
4766 g_object_dff_tl('PO_SHIPMENTS_EXT_ATTRS').l_object_ext_b_table := 'PO_LINE_LOCATIONS_ALL_EXT_B';
4767 g_object_dff_tl('PO_SHIPMENTS_EXT_ATTRS').l_object_ext_tl_table := 'PO_LINE_LOCATIONS_ALL_EXT_TL';
4768 g_object_dff_tl('PO_SHIPMENTS_EXT_ATTRS').l_ag_mapping_column := 'PO_SHIPMENTS_GROUP_ID';
4769 g_object_dff_tl('PO_SHIPMENTS_EXT_ATTRS').l_arch_object_name := 'PO_LINE_LOCATIONS_ARCHIVE_ALL' ;
4770
4771 g_object_dff_tl('PO_DISTRIBUTIONS_EXT_ATTRS').l_object_name := 'PO_DISTRIBUTIONS_ALL' ;
4772 g_object_dff_tl('PO_DISTRIBUTIONS_EXT_ATTRS').l_dff_name := 'PO_DISTRIBUTIONS_EXT_ATTRS';
4773 g_object_dff_tl('PO_DISTRIBUTIONS_EXT_ATTRS').l_object_ext_b_table := 'PO_DISTRIBUTIONS_ALL_EXT_B';
4774 g_object_dff_tl('PO_DISTRIBUTIONS_EXT_ATTRS').l_object_ext_tl_table := 'PO_DISTRIBUTIONS_ALL_EXT_TL';
4775 g_object_dff_tl('PO_DISTRIBUTIONS_EXT_ATTRS').l_ag_mapping_column := 'PO_DISTRIBUTIONS_GROUP_ID' ;
4776 g_object_dff_tl('PO_DISTRIBUTIONS_EXT_ATTRS').l_arch_object_name := 'PO_DISTRIBUTIONS_ARCHIVE_ALL' ;
4777
4778 g_object_dff_tl('PO_REQ_HEADER_EXT_ATTRS').l_object_name := 'PO_REQUISITION_HEADERS_ALL' ;
4779 g_object_dff_tl('PO_REQ_HEADER_EXT_ATTRS').l_dff_name := 'PO_REQ_HEADER_EXT_ATTRS';
4780 g_object_dff_tl('PO_REQ_HEADER_EXT_ATTRS').l_object_ext_b_table := 'PO_REQ_HEADERS_EXT_B';
4781 g_object_dff_tl('PO_REQ_HEADER_EXT_ATTRS').l_object_ext_tl_table := 'PO_REQ_HEADERS_EXT_TL';
4782 g_object_dff_tl('PO_REQ_HEADER_EXT_ATTRS').l_ag_mapping_column := 'REQ_HEADER_GROUP_ID';
4783
4784 g_object_dff_tl('PO_REQ_LINE_EXT_ATTRS').l_object_name := 'PO_REQUISITION_LINES_ALL' ;
4785 g_object_dff_tl('PO_REQ_LINE_EXT_ATTRS').l_dff_name := 'PO_REQ_LINE_EXT_ATTRS';
4786 g_object_dff_tl('PO_REQ_LINE_EXT_ATTRS').l_object_ext_b_table := 'PO_REQ_LINES_EXT_B';
4787 g_object_dff_tl('PO_REQ_LINE_EXT_ATTRS').l_object_ext_tl_table := 'PO_REQ_LINES_EXT_TL';
4788 g_object_dff_tl('PO_REQ_LINE_EXT_ATTRS').l_ag_mapping_column := 'REQ_LINE_GROUP_ID';
4789
4790 g_object_dff_tl('PO_REQ_DIST_EXT_ATTRS').l_object_name := 'PO_REQ_DISTRIBUTIONS_ALL' ;
4791 g_object_dff_tl('PO_REQ_DIST_EXT_ATTRS').l_dff_name := 'PO_REQ_DIST_EXT_ATTRS';
4792 g_object_dff_tl('PO_REQ_DIST_EXT_ATTRS').l_object_ext_b_table := 'PO_REQ_DISTRIBUTIONS_EXT_B';
4793 g_object_dff_tl('PO_REQ_DIST_EXT_ATTRS').l_object_ext_tl_table := 'PO_REQ_DISTRIBUTIONS_EXT_TL';
4794 g_object_dff_tl('PO_REQ_DIST_EXT_ATTRS').l_ag_mapping_column := 'REQ_DISTRIBUTION_GROUP_ID';
4795
4796 g_object_dff_tl('PON_AUC_HDRS_EXT_ATTRS').l_object_name := 'PON_AUCTION_HEADERS_ALL' ;
4797 g_object_dff_tl('PON_AUC_HDRS_EXT_ATTRS').l_dff_name := 'PON_AUC_HDRS_EXT_ATTRS';
4798 g_object_dff_tl('PON_AUC_HDRS_EXT_ATTRS').l_object_ext_b_table := 'PON_AUCTION_HEADERS_EXT_B';
4799 g_object_dff_tl('PON_AUC_HDRS_EXT_ATTRS').l_object_ext_tl_table := 'PON_AUCTION_HEADERS_EXT_TL';
4800 g_object_dff_tl('PON_AUC_HDRS_EXT_ATTRS').l_ag_mapping_column := 'PON_AUC_HEADER_GROUP_ID';
4801
4802 g_object_dff_tl('PON_AUC_PRICES_EXT_ATTRS').l_object_name := 'PON_AUCTION_ITEM_PRICES_ALL' ;
4803 g_object_dff_tl('PON_AUC_PRICES_EXT_ATTRS').l_dff_name := 'PON_AUC_PRICES_EXT_ATTRS';
4804 g_object_dff_tl('PON_AUC_PRICES_EXT_ATTRS').l_object_ext_b_table := 'PON_AUCTION_ITEM_PRICES_EXT_B';
4805 g_object_dff_tl('PON_AUC_PRICES_EXT_ATTRS').l_object_ext_tl_table := 'PON_AUCTION_ITEM_PRICES_EXT_TL';
4806 g_object_dff_tl('PON_AUC_PRICES_EXT_ATTRS').l_ag_mapping_column := 'PON_AUC_ITEM_PRICE_GROUP_ID';
4807
4808 g_object_dff_tl('PON_BID_HDRS_EXT_ATTRS').l_object_name := 'PON_BID_HEADERS' ;
4809 g_object_dff_tl('PON_BID_HDRS_EXT_ATTRS').l_dff_name := 'PON_BID_HDRS_EXT_ATTRS';
4810 g_object_dff_tl('PON_BID_HDRS_EXT_ATTRS').l_object_ext_b_table := 'PON_BID_HEADERS_EXT_B';
4811 g_object_dff_tl('PON_BID_HDRS_EXT_ATTRS').l_object_ext_tl_table := 'PON_BID_HEADERS_EXT_TL';
4812 g_object_dff_tl('PON_BID_HDRS_EXT_ATTRS').l_ag_mapping_column := 'BID_HEADER_GROUP_ID';
4813
4814 g_object_dff_tl('PON_BID_PRICES_EXT_ATTRS').l_object_name := 'PON_BID_ITEM_PRICES' ;
4815 g_object_dff_tl('PON_BID_PRICES_EXT_ATTRS').l_dff_name := 'PON_BID_PRICES_EXT_ATTRS';
4816 g_object_dff_tl('PON_BID_PRICES_EXT_ATTRS').l_object_ext_b_table := 'PON_BID_ITEM_PRICES_EXT_B';
4817 g_object_dff_tl('PON_BID_PRICES_EXT_ATTRS').l_object_ext_tl_table := 'PON_BID_ITEM_PRICES_EXT_TL';
4818 g_object_dff_tl('PON_BID_PRICES_EXT_ATTRS').l_ag_mapping_column := 'BID_ITEM_PRICE_GROUP_ID';
4819
4820 -- populate the mapping table for address EIT
4821
4822 SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
4823 BULK COLLECT INTO user_col_name_tbl, appl_col_name_tbl
4824 FROM fnd_descr_flex_column_usages
4825 WHERE APPLICATION_ID=800
4826 and DESCRIPTIVE_FLEXFIELD_NAME='Extra Location Info DDF'
4827 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'CLM_OFFICE_CODE' ;
4828
4829 if user_col_name_tbl.count > 0 then
4830 for i in user_col_name_tbl.first..user_col_name_tbl.last loop
4831 g_address_eit_tbl(user_col_name_tbl(i)) := appl_col_name_tbl (i);
4832 end loop;
4833 end if;
4834
4835
4836 END PO_UDA_DATA_UTIL;