[Home] [Help]
PACKAGE BODY: APPS.PON_COPY_UDAS_GRP
Source
1 PACKAGE BODY PON_COPY_UDAS_GRP AS
2 /* $Header: PONUDACPY.plb 120.10.12020000.2 2013/02/09 08:21:53 hvutukur ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(25):='PON_COPY_UDAS_GRP';
5
6 g_err_loc VARCHAR2(400);
7
8 -- Global variable for status which will be set in different sub-procedures
9 g_return_status VARCHAR2(50);
10
11
12 -- Indicate if the debug mode is on
13 g_debug_mode VARCHAR2(10) := 'Y';
14
15 -- module name for logging message
16 g_module_prefix CONSTANT VARCHAR2(40) := 'pon.plsql.pon_copy_udas_grp.';
17
18 --
19 -- Few variables redifened as done in SourcingCommonUtil and ContractCommonUtil
20 --
21
22 -- Global variable for message sufix
23 g_message_suffix PON_AUC_DOCTYPES.MESSAGE_SUFFIX%TYPE;
24
25
26 TYPE AttrGroupList IS TABLE OF VARCHAR(30);-- INDEX BY BINARY_INTEGER;
27
28 TYPE PKeyList is TABLE of NUMBER;
29 Type PKeyCol is TABLE of VARCHAR2(50);
30
31 PROCEDURE LOG_MESSAGE( p_module IN VARCHAR2, p_message IN VARCHAR2)
32 IS
33 l_debug_mode VARCHAR2(1);
34 BEGIN
35 l_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
36 IF ( l_debug_mode = 'Y' ) THEN
37 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
38 FND_LOG.string(log_level => FND_LOG.level_statement,
39 module => 'PON_UDA_COPY_GRP.' || p_module,
40 message => p_message);
41 END IF;
42 END IF;
43 -- insert into s_temp values(p_module||' , '|| p_message);
44 END LOG_MESSAGE;
45
46
47
48
49
50 PROCEDURE AUTO_CREATE_UDA_COPY(
51 from_template_id IN NUMBER
52 ,to_template_id IN NUMBER
53 ,from_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
54 ,to_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
55 ,copy_attribute_groups IN VARCHAR2 DEFAULT 'ALL' -- SPECIFIC / EXCLUSION
56 ,attribute_group_table IN PO_TBL_VARCHAR30 DEFAULT NULL
57 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
58 ,x_return_status OUT NOCOPY VARCHAR2
59 ,x_msg_count OUT NOCOPY NUMBER
60 ,x_msg_data OUT NOCOPY VARCHAR2)
61 IS
62 BEGIN
63 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
64 (
65 from_template_id => from_template_id
66 ,to_template_id => to_template_id
67 ,from_pk_col_value_pairs => from_pk_col_value_pairs
68 ,to_pk_col_value_pairs => to_pk_col_value_pairs
69 ,copy_attribute_groups => copy_attribute_groups
70 ,attribute_group_table => attribute_group_table
71 ,p_commit => FND_API.G_FALSE
72 ,x_return_status => x_return_status
73 ,x_msg_count => x_msg_count
74 ,x_msg_data => x_msg_data
75 );
76
77 END;
78
79 FUNCTION GET_ATTRIBUTE_GROUP_TABLE(attrGroups AttrGroupList) RETURN PO_TBL_VARCHAR30
80 IS
81 l_attribute_group_table PO_TBL_VARCHAR30;
82 BEGIN
83 l_attribute_group_table := PO_TBL_VARCHAR30();
84 log_message('get_attribute_group_table ','Entered method '||attrGroups.COUNT);
85 for i in attrGroups.FIRST .. attrGroups.LAST
86 LOOP
87 l_attribute_group_table.extend;
88 log_message('get_attribute_group_table','Add attr_group '||attrGroups(i));
89 l_attribute_group_table(i) := attrGroups(i);
90 END LOOP;
91 RETURN l_attribute_group_table;
92 END;
93
94 FUNCTION GET_EGO_COL_NAM_VAL_PAIR(p_key in PKeyList,
95 p_key_name in PKeyCol)
96 RETURN EGO_COL_NAME_VALUE_PAIR_ARRAY
97 IS
98 pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
99 BEGIN
100 pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
101 FOR i in p_key.FIRST .. p_key.LAST
102 LOOP
103 pk_col_value_pairs.extend;
104 pk_col_value_pairs(i) := EGO_COL_NAME_VALUE_PAIR_OBJ(p_key_name(i), p_key(i));
105
106 /*EGO_COL_NAME_VALUE_PAIR_ARRAY(
107 EGO_COL_NAME_VALUE_PAIR_OBJ(
108 p_key_name, p_key
109 )
110 );*/
111 END LOOP;
112 return pk_col_value_pairs;
113 END;
114
115 PROCEDURE COPY_UDAS(
116 p_from_template_id IN NUMBER,
117 p_from_pkey IN PKeyList,
118 p_to_template_id IN NUMBER,
119 p_to_pkey IN PKeyList,
120 p_pkey_col IN PKeyCol,
121 attrGroups IN AttrGroupList,
122 copy_attribute_groups IN VARCHAR2,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2
126 )
127 IS
128 copyfrom_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
129 copyto_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
130 l_attribute_group_table PO_TBL_VARCHAR30;
131 BEGIN
132 log_message('COPY_UDAS','Call Get_Attribute_Group_Table');
133 l_attribute_group_table := GET_ATTRIBUTE_GROUP_TABLE(attrGroups);
134 log_message('COPY_UDAS','Return Get_Attribute_Group_table');
135 copyfrom_pk_col_value_pairs := GET_EGO_COL_NAM_VAL_PAIR(p_from_pkey,p_pkey_col);
136 copyto_pk_col_value_pairs := GET_EGO_COL_NAM_VAL_PAIR(p_to_pkey,p_pkey_col);
137 log_message('COPY_UDAS','Got Ego Name Value Pairs');
138 AUTO_CREATE_UDA_COPY(
139 p_from_template_id
140 ,p_to_template_id
141 ,copyfrom_pk_col_value_pairs
142 ,copyto_pk_col_value_pairs
143 ,copy_attribute_groups
144 ,l_attribute_group_table
145 ,FND_API.G_FALSE
146 ,x_return_status
147 ,x_msg_count
148 ,x_msg_data);
149 log_message('COPY_UDAS','Return from Auto_create_uda_copy '||x_return_status);
150 END;
151
152 PROCEDURE COPY_HEADER_UDAS(
153 p_source_auction_header_id IN NUMBER,
154 p_target_auction_header_id IN NUMBER,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2
158 )
159 IS
160 l_errorcode NUMBER;
161 i Number;
162 l_template_id NUMBER;
163 l_attr_group VARCHAR2(30);
164 attrGroups AttrGroupList;
165 pSrcKey PKeyList;
166 pTarKey PKeyList;
167 pKeyCols PKeyCol;
168
169 --for addresses
170 l_amd_flag VARCHAR2(1);
171 l_prev_hdr_id1 NUMBER;
172 l_prev_hdr_id2 NUMBER;
173
174 p_src_pkey PO_TBL_NUMBER;
175 p_tar_pkey PO_TBL_NUMBER;
176 l_excl_addr_types PO_TBL_VARCHAR50;
177
178 cursor addTypCur is
179 select lookup_code from po_lookup_codes where lookup_type = 'SOL_AMD_UDA_ADDRESS_TYPES'
180 MINUS
181 select lookup_code from po_lookup_codes where lookup_type = 'SOL_UDA_ADDRESS_TYPES';
182
183
184 cursor attr_groups_cur(p_template_id Number) is
185 SELECT ATTR_GROUP_NAME FROM ego_attr_groups_v WHERE attr_group_id IN
186 (SELECT DISTINCT(ATTRIBUTE_GROUP_ID) FROM po_uda_ag_template_usages
187 WHERE template_id = p_template_id and ATTRIBUTE_CATEGORY <> 'DOCUMENT_NUMBERING');
188 BEGIN
189
190 -- get Uda Template Id
191 select uda_template_id into l_template_id from pon_auction_headers_all where
192 auction_header_id = p_source_auction_header_id;
193 log_message('COPY_Header_UDAs','Got Template Id '||l_template_id);
194 --get Attribute Groups exclude doc numbering
195 open attr_groups_cur(l_template_id);
196 log_message('COPY_Header_UDAs','Opened Cursor');
197 fetch attr_groups_cur bulk collect into attrGroups;-- l_attr_group;
198 log_message('COPY_Header_UDAs','Fetched Cursor');
199 close attr_groups_cur;
200 log_message('COPY_Header_UDAs','Got Attribute Groups '||attrGroups.COUNT);
201
202 pKeyCols := PKeyCol(g_auc_header_pkey_col);
203 pSrcKey := PKeyList(p_source_auction_header_id);
204 pTarKey := PKeyList(p_target_auction_header_id);
205
206 COPY_UDAS(
207 l_template_id,
208 pSrcKey,
209 l_template_id,
210 pTarKey,
211 pKeyCols,
212 attrGroups,
213 'SPECIFIC',
214 x_return_status,
215 x_msg_count,
216 x_msg_data);
217
218 log_message('COPY_Header_UDAs','After COPY_UDAs '||x_return_status);
219 --Handle Addresses
220 p_src_pkey := PO_TBL_NUMBER();
221 p_src_pkey.extend(1);
222 p_src_pkey(1) := p_source_auction_header_id;
223
224 p_tar_pkey := PO_TBL_NUMBER();
225 p_tar_pkey.extend(1);
226 p_tar_pkey(1) := p_target_auction_header_id;
227
228
229 select nvl(amendment_flag,'N') into l_amd_flag from pon_auction_headers_all where auction_header_id = p_source_auction_header_id;
230
231 if ( l_amd_flag = 'Y' ) THEN
232 select auction_header_id_prev_amend into l_prev_hdr_id1 from pon_auction_headers_all where auction_header_id = p_source_auction_header_id;
233 select auction_header_id_prev_amend into l_prev_hdr_id2 from pon_auction_headers_all where auction_header_id = p_target_auction_header_id;
234 --conformed amendment copy
235 if ( l_prev_hdr_id1 = l_prev_hdr_id2 ) then
236 open addTypCur;
237 fetch addTypCur bulk collect into l_excl_addr_types;
238 end if;
239 end if;
240
241
242 log_message('COPY_Header_UDAs','Call process_addresses ');
243 process_addresses(p_src_pkey=>p_src_pkey
244 ,p_tar_pkey=>p_tar_pkey
245 ,p_src_template_id=>l_template_id
246 ,p_tar_template_id=>l_template_id
247 ,p_src_doc_type=>'SOL'
248 ,p_excl_addr_types=>l_excl_addr_types
249 ,x_return_status=>x_return_status
250 ,x_msg_count=>x_msg_count
251 ,x_msg_data=>x_msg_data);
252 log_message('COPY_Header_UDAs','After process_addresses '||x_return_status);
253
254 /* PO_UDA_DATA_UTIL.copy_user_attrs
255 (
256 x_template_id => l_template_id
257 ,x_from_pk_col_value_pairs => copyfrom_pk_col_value_pairs
258 ,x_to_pk_col_value_pairs => copyto_pk_col_value_pairs
259 ,x_copy_attribute_groups => 'ALL'
260 ,x_return_status => x_return_status
261 ,x_msg_count => x_msg_count
262 ,x_msg_data => x_msg_data
263 );
264 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
265 (
266 from_template_id => l_template_id
267 ,to_template_id => l_template_id
268 ,from_pk_col_value_pairs => copyfrom_pk_col_value_pairs
269 ,to_pk_col_value_pairs => copyto_pk_col_value_pairs
270 ,copy_attribute_groups => 'SPECIFIC'
271 ,attribute_group_table => l_attribute_group_table
272 ,p_commit => FND_API.G_FALSE
273 ,x_return_status => x_return_status
274 ,x_msg_count => x_msg_count
275 ,x_msg_data => x_msg_data
276 );
277 */
278 EXCEPTION
279 WHEN OTHERS THEN
280 log_message('Copy_Header_UDAs ','Error '||SQLERRM);
281
282
283 END COPY_HEADER_UDAS;
284
285
286 PROCEDURE COPY_LINE_UDAS(
287 p_source_auction_header_id IN NUMBER,
288 p_target_auction_header_id IN NUMBER,
289 p_line_number IN NUMBER,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2
293 )
294 IS
295
296 BEGIN
297 copy_line_udas(p_source_auction_header_id,
298 p_target_auction_header_id,
299 p_line_number,
300 p_line_number,
301 x_return_status,
302 x_msg_count,
303 x_msg_data);
304 END COPY_LINE_UDAS;
305
306
307 PROCEDURE COPY_LINE_UDAS(
308 p_source_auction_header_id IN NUMBER,
309 p_target_auction_header_id IN NUMBER,
310 p_source_line_number IN NUMBER,
311 p_target_line_number IN NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2
315 )
316 IS
317 l_errorcode NUMBER;
318 l_template_id NUMBER;
319 copyfrom_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
320 copyto_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
321 BEGIN
322 log_message('COPY_LINE_UDAS ','Source '||p_source_auction_header_id||' '||p_source_line_number);
323 log_message('COPY_LINE_UDAS ','Target '||p_target_auction_header_id||' '||p_target_line_number);
324
325 copyfrom_pk_col_value_pairs :=
326 EGO_COL_NAME_VALUE_PAIR_ARRAY(
327 EGO_COL_NAME_VALUE_PAIR_OBJ(
328 g_auc_header_pkey_col, p_source_auction_header_id
329 ),
330 EGO_COL_NAME_VALUE_PAIR_OBJ(
331 g_auc_line_number, p_source_line_number
332 )
333 );
334
335 copyto_pk_col_value_pairs :=
336 EGO_COL_NAME_VALUE_PAIR_ARRAY(
337 EGO_COL_NAME_VALUE_PAIR_OBJ(
338 g_auc_header_pkey_col, p_target_auction_header_id
339 ),
340 EGO_COL_NAME_VALUE_PAIR_OBJ(
341 g_auc_line_number, p_target_line_number
342 )
343 );
344
345 select uda_template_id into l_template_id from pon_auction_item_prices_all where
346 auction_header_id = p_source_auction_header_id and line_number = p_source_line_number;
347
348 PO_UDA_DATA_UTIL.copy_user_attrs
349 (
350 x_template_id => l_template_id
351 ,x_from_pk_col_value_pairs => copyfrom_pk_col_value_pairs
352 ,x_to_pk_col_value_pairs => copyto_pk_col_value_pairs
353 ,x_copy_attribute_groups => 'ALL'
354 ,x_return_status => x_return_status
355 ,x_msg_count => x_msg_count
356 ,x_msg_data => x_msg_data
357 );
358 log_message('COPY_LINE_UDAS ','Copy Status '||x_return_status);
359 END COPY_LINE_UDAS;
360
361 FUNCTION get_complex_attr_grp_name (p_bid_number IN NUMBER,
362 p_line_number IN NUMBER,
363 p_bid_template_id IN NUMBER) RETURN VARCHAR2 IS
364
365
366 l_complex_attr_grp VARCHAR2(50);
367 BEGIN
368
369 SELECT eagv.ATTR_GROUP_NAME
370 INTO l_complex_attr_grp
371 FROM
372 po_uda_ag_templates puat
373 ,po_uda_ag_template_usages puatu
374 ,pon_bid_item_prices pbip
375 ,ego_attr_groups_v eagv
376
377 WHERE puat.template_id = p_bid_template_id
378 AND puatu.template_id = puat.template_id
379 AND pbip.bid_number = p_bid_number
380 AND pbip.line_number = p_line_number
381 AND puatu.ATTRIBUTE1= pbip.CLM_IDC_TYPE
382 AND puatu.ATTRIBUTE2= pbip.CLM_CONTRACT_TYPE
383 AND eagv.ATTR_GROUP_ID = puatu.ATTRIBUTE_GROUP_ID
384 AND eagv.ATTR_GROUP_TYPE = puat.ENTITY_CODE;
385
386 RETURN l_complex_attr_grp;
387
388 EXCEPTION WHEN No_Data_Found THEN
389 RETURN NULL;
390
391 END;
392
393 PROCEDURE COPY_BID_LINE_UDA(p_auction_header_id IN NUMBER,
394 p_bid_number IN NUMBER,
395 p_source_bid_number IN NUMBER,
396 p_line_number IN NUMBER,
397 p_auction_template_id IN NUMBER,
398 p_bid_template_id IN NUMBER,
399 p_copyfrom IN VARCHAR2,
400 x_return_status OUT NOCOPY VARCHAR2,
401 x_msg_count OUT NOCOPY NUMBER,
402 x_msg_data OUT NOCOPY VARCHAR2) IS
403
404 l_errorcode NUMBER;
405 l_source_template_id NUMBER;
406 copyfrom_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
407 copyto_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
408 l_attribute_group_table PO_TBL_VARCHAR30;
409 l_complex_attr_grp VARCHAR2(50);
410
411 BEGIN
412
413 /*insert into vhk_debug_msg values('line_number :'||p_line_number
414 ||' uda_template_id : '||p_auction_template_id
415 ||' bid_uda_template_id : '||p_bid_template_id
416 ||' copyfrom : '||p_copyfrom);*/
417
418
419 l_complex_attr_grp := get_complex_attr_grp_name (p_bid_number,p_line_number,p_bid_template_id );
420
421 l_attribute_group_table := PO_TBL_VARCHAR30 ('ADD_ITEM_INFO','FED_CUST_DESG');
422
423 IF p_copyfrom = 'A' THEN
424
425 l_source_template_id := p_auction_template_id;
426 /*insert into vhk_debug_msg values('line_number : '||p_line_number
427 ||' g_auc_header_pkey_col : '||g_auc_header_pkey_col);*/
428 copyfrom_pk_col_value_pairs :=
429 EGO_COL_NAME_VALUE_PAIR_ARRAY
430 (
431 EGO_COL_NAME_VALUE_PAIR_OBJ(g_auc_header_pkey_col, p_auction_header_id),
432 EGO_COL_NAME_VALUE_PAIR_OBJ(g_auc_line_number, p_line_number)
433 );
434
435
436 ELSE
437 l_source_template_id := p_bid_template_id;
438 copyfrom_pk_col_value_pairs :=
439 EGO_COL_NAME_VALUE_PAIR_ARRAY
440 (
441 EGO_COL_NAME_VALUE_PAIR_OBJ(g_bid_number_pkey_col, p_source_bid_number),
442 EGO_COL_NAME_VALUE_PAIR_OBJ(g_auc_line_number, p_line_number)
443 );
444
445 IF(l_complex_attr_grp IS NOT NULL) THEN
446 l_attribute_group_table := PO_TBL_VARCHAR30 ('ADD_ITEM_INFO','FED_CUST_DESG',l_complex_attr_grp);
447 END IF;
448
449 END IF;
450
451 copyto_pk_col_value_pairs :=
452 EGO_COL_NAME_VALUE_PAIR_ARRAY
453 (
454 EGO_COL_NAME_VALUE_PAIR_OBJ(g_bid_number_pkey_col, p_bid_number),
455 EGO_COL_NAME_VALUE_PAIR_OBJ(g_auc_line_number, p_line_number)
456 );
457
458
459
460 /*insert into vhk_debug_msg values('Calling Copy UDA : '||' l_source_template_id :'||l_source_template_id
461 ||' p_bid_template_id : '||p_bid_template_id);*/
462
463 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
464 (
465 from_template_id => l_source_template_id
466 ,to_template_id => p_bid_template_id
467 ,from_pk_col_value_pairs => copyfrom_pk_col_value_pairs
468 ,to_pk_col_value_pairs => copyto_pk_col_value_pairs
469 ,copy_attribute_groups => 'SPECIFIC'
470 ,attribute_group_table => l_attribute_group_table
471 ,p_commit => FND_API.G_FALSE
472 ,x_return_status => x_return_status
473 ,x_msg_count => x_msg_count
474 ,x_msg_data => x_msg_data
475 );
476
477 END COPY_BID_LINE_UDA;
478
479
480
481 -- API name : FETCH_BID_LINES_COPY_UDA
482 --
483 -- Type : Group
484 --
485 -- Pre-reqs : Negotiation with p_auction_header_id should exist in database.
486 -- Bid with p_bid_number should exist in the database.
487 --
488 -- Function : Queries all lines to be copied as below :
489 -- 1. If a line is newly created or updated in auction, then uda's should be copied from auction line
490 -- 2. If a line is not modified, then bid line should be copied from existing bid line.
491 -- Copies the UDA's from auction/bid line to new bid line.
492 --
493 -- Parameters:
494
495 -- IN : p_source_auction_header_id NUMBER
496 --
497 --
498 -- IN : p_target_auction_header_id NUMBER
499 --
500 -- IN : p_line_number NUMBER
501 --
502 --
503 PROCEDURE FETCH_BID_LINES_COPY_UDA(p_auction_header_id IN NUMBER,
504 p_bid_number IN NUMBER,
505 p_source_bid_num IN NUMBER,
506 p_bid_template_id IN NUMBER,
507 p_batch_start IN NUMBER,
508 p_batch_end IN NUMBER,
509 x_return_status OUT NOCOPY VARCHAR2,
510 x_msg_count OUT NOCOPY NUMBER,
511 x_msg_data OUT NOCOPY VARCHAR2) IS
512
513 cursor bid_lines(p_auction_header_id number, p_source_bid_number number,
514 p_batch_start number,p_batch_end number) is
515 select al.auction_header_id, al.line_number,al.uda_template_id,
516 bl.bid_number,bl.line_number bid_line_number,bl.auction_header_id bid_auc_head_id,
517 decode(al.modified_date-old_al.modified_date,0,'B','A') copyfrom
518 FROM pon_auction_item_prices_all al,
519 pon_auction_item_prices_all old_al,
520 pon_bid_item_prices bl
521 WHERE al.auction_header_id = p_auction_header_id
522 AND bl.bid_number(+) = p_source_bid_number
523 AND bl.line_number(+) = al.line_number
524 AND old_al.auction_header_id(+) = bl.auction_header_id
525 AND old_al.line_number (+) = bl.line_number
526 AND al.line_number BETWEEN p_batch_start AND p_batch_end;
527
528 BEGIN
529 /*insert into vhk_debug_msg values('p_auction_header_id : '||p_auction_header_id
530 ||' p_bid_number : '||p_bid_number
531 ||' p_source_bid_num : '||p_source_bid_num
532 ||' p_batch_start : '||p_batch_start
533 ||' p_batch_end : '||p_batch_end);*/
534
535 for line in bid_lines(p_auction_header_id,p_source_bid_num,p_batch_start,p_batch_end) loop
536
537 /*insert into vhk_debug_msg values('Calling COPY_BID_LINE_UDA');*/
538 COPY_BID_LINE_UDA(p_auction_header_id => p_auction_header_id,
539 p_bid_number => p_bid_number,
540 p_source_bid_number => p_source_bid_num,
541 p_line_number => line.line_number,
542 p_auction_template_id => line.uda_template_id,
543 p_bid_template_id => p_bid_template_id,
544 p_copyfrom => line.copyfrom,
545 x_return_status => x_return_status,
546 x_msg_count => x_msg_count,
547 x_msg_data => x_msg_data);
548
549 /*insert into vhk_debug_msg values('line_number :'||line.line_number
550 ||' uda_template_id : '||line.uda_template_id
551 ||' bid_uda_template_id : '||p_bid_template_id
552 ||' copyfrom : '||line.copyfrom
553 ||' x_return_status : '||x_return_status);*/
554 end loop;
555
556
557 END FETCH_BID_LINES_COPY_UDA;
558
559 PROCEDURE COPY_REQ_HEADER_UDA(p_auction_header_id IN NUMBER,
560 p_req_header_id IN NUMBER,
561 p_src_template_id IN NUMBER,
562 p_dest_template_id IN NUMBER,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY NUMBER,
565 x_msg_data OUT NOCOPY VARCHAR2) IS
566
567 copyfrom_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
568 copyto_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
569 l_attribute_group_table PO_TBL_VARCHAR30;
570 l_src_pkey PO_TBL_NUMBER;
571 l_tar_pkey PO_TBL_NUMBER;
572 l_excl_addr_types PO_TBL_VARCHAR50;
573
574 BEGIN
575 copyfrom_pk_col_value_pairs :=
576 EGO_COL_NAME_VALUE_PAIR_ARRAY
577 (
578 EGO_COL_NAME_VALUE_PAIR_OBJ(g_req_header_pkey_col, p_req_header_id)
579 );
580
581 copyto_pk_col_value_pairs :=
582 EGO_COL_NAME_VALUE_PAIR_ARRAY
583 (
584 EGO_COL_NAME_VALUE_PAIR_OBJ(g_auc_header_pkey_col, p_auction_header_id)
585 );
586
587 -- 'SET_ASIDE_INFO',,'SF1442_Informa' are not there in req header.
588 --l_attribute_group_table := PO_TBL_VARCHAR30 ('BU_PRI_PRJ_INF');
589 get_attribute_groups(p_uda_template_id => p_src_template_id
590 ,x_attr_group_tbl => l_attribute_group_table);
591
592 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
593 (
594 from_template_id => p_src_template_id
595 ,to_template_id => p_dest_template_id
596 ,from_pk_col_value_pairs => copyfrom_pk_col_value_pairs
597 ,to_pk_col_value_pairs => copyto_pk_col_value_pairs
598 ,copy_attribute_groups => 'SPECIFIC'
599 ,attribute_group_table => l_attribute_group_table
600 ,p_commit => FND_API.G_FALSE
601 ,x_return_status => x_return_status
602 ,x_msg_count => x_msg_count
603 ,x_msg_data => x_msg_data
604 );
605
606
607
608 IF ( x_return_status = 'S' ) Then
609
610 l_src_pkey := PO_TBL_NUMBER();
611 l_src_pkey.extend(1);
612 l_src_pkey(1) := p_req_header_id;
613
614 l_tar_pkey := PO_TBL_NUMBER();
615 l_tar_pkey.extend(1);
616 l_tar_pkey(1) := p_auction_header_id;
617
618 l_excl_addr_types := PO_TBL_VARCHAR50();
619
620 log_message('COPY_REQ_HEADER_UDA','Call process_addresses ');
621 process_addresses(p_src_pkey=>l_src_pkey
622 ,p_tar_pkey=>l_tar_pkey
623 ,p_src_template_id=>p_src_template_id
624 ,p_tar_template_id=>p_dest_template_id
625 ,p_src_doc_type=>'REQ'
626 ,p_excl_addr_types=>l_excl_addr_types
627 ,x_return_status=>x_return_status
628 ,x_msg_count=>x_msg_count
629 ,x_msg_data=>x_msg_data);
630 log_message('COPY_REQ_HEADER_UDA','After process_addresses '||x_return_status);
631
632 END IF;
633
634
635 END COPY_REQ_HEADER_UDA;
636
637 PROCEDURE get_attribute_groups(p_uda_template_id IN NUMBER,
638 x_attr_group_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
639 )
640 IS
641 l_api_name VARCHAR2(30) := 'get_attribute_groups';
642 l_progress VARCHAR2(3) := '000';
643
644
645 l_return_status VARCHAR2(1);
646 l_err_msg VARCHAR2(1000);
647 l_attr_group_tbl PO_TBL_VARCHAR30;
648
649 BEGIN
650
651 l_progress := '010';
652 log_message('get_attribute_groups','Entered PROCEDURE');
653
654 SELECT DISTINCT ag.attr_group_name
655 BULK COLLECT INTO x_attr_group_tbl
656 FROM po_uda_ag_template_usages tu, ego_attr_groups_v ag
657 WHERE tu.template_id = p_uda_template_id
658 AND ag.attr_group_id = tu.attribute_group_id
659 AND tu.attribute_category NOT IN ('PRICING','DOCUMENT_NUMBERING','ADDRESS'); --12611018 Document numbering added
660
661
662
663 log_message('get_attribute_groups','Completed PROCEDURE');
664
665
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 log_message('get_attribute_groups','Exception'
670 );
671 END get_attribute_groups;
672
673
674 PROCEDURE COPY_REQ_LINE_UDA(p_auction_header_id IN NUMBER,
675 p_auction_line_number IN NUMBER,
676 p_req_line_id IN NUMBER,
677 p_src_template_id IN NUMBER,
678 p_dest_template_id IN NUMBER,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2) IS
682
683
684
685 l_src_pkey PO_TBL_NUMBER;
686 l_tar_pkey PO_TBL_NUMBER;
687
688 l_cost_constraint pon_auction_item_prices_all.CLM_COST_CONSTRAINT%TYPE;
689 l_idc_type pon_auction_item_prices_all.CLM_IDC_TYPE%TYPE;
690 l_contract_type pon_auction_item_prices_all.CLM_CONTRACT_TYPE%TYPE;
691 BEGIN
692 select nvl(clm_cost_constraint,'X')
693 into l_cost_constraint
694 from pon_auction_item_prices_all
695 where auction_header_id=p_auction_header_id
696 and line_number=p_auction_line_number;
697
698
699 l_src_pkey := PO_TBL_NUMBER();
700 l_src_pkey.extend(1);
701 l_src_pkey(1) := p_req_line_id;
702
703 l_tar_pkey := PO_TBL_NUMBER();
704 l_tar_pkey.extend(2);
705 l_tar_pkey(1) := p_auction_header_id;
706 l_tar_pkey(2) := p_auction_line_number;
707
708
709 PON_COPY_UDAS_GRP.COPY_UDA_DATA(p_src_pkey=>l_src_pkey,
710 p_tar_pkey=>l_tar_pkey,
711 p_src_template_id=>p_src_template_id,
712 p_tar_template_id=>p_dest_template_id,
713 p_src_doc_level=>'LINE',
714 p_tar_doc_level=>'LINE',
715 p_src_doc_type=>'REQ',
716 p_tar_doc_type=>'SOL',
717 x_return_status=>x_return_status,
718 x_msg_count=>x_msg_count,
719 x_msg_data=>x_msg_data);
720
721 /* CLM QA Bug : 9835426 : When copying from req lines, need to delete complex
722 * pricing uda's for NSP/NC lines.
723 */
724 if (x_return_status = 'S') then
725
726 select nvl(clm_cost_constraint,'X'),clm_idc_type,clm_contract_type
727 into l_cost_constraint,l_idc_type,l_contract_type
728 from pon_auction_item_prices_all
729 where auction_header_id=p_auction_header_id
730 and line_number=p_auction_line_number;
731
732 if l_cost_constraint in ('NC','NSP') then
733 delete pon_auction_item_prices_ext_b
734 where auction_header_id = p_auction_header_id
735 and line_number=p_auction_line_number
736 and attr_group_id in
737 (select attribute_group_id from po_uda_ag_template_usages
738 where template_id=p_dest_template_id
739 and nvl(attribute1,'X')=nvl(l_idc_type,'X')
740 and nvl(attribute2,'X')=nvl(l_contract_type,'X'));
741 end if;
742 end if;
743
744 END COPY_REQ_LINE_UDA;
745
746
747 PROCEDURE COPY_PO_LINE_UDA(p_bid_header_id IN NUMBER,
748 p_bid_line_number IN NUMBER,
749 p_po_line_id IN NUMBER,
750 x_return_status OUT NOCOPY VARCHAR2,
751 x_msg_count OUT NOCOPY NUMBER,
752 x_msg_data OUT NOCOPY VARCHAR2) IS
753
754 copyfrom_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
755 copyto_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
756 l_attribute_group_table PO_TBL_VARCHAR30;
757 l_src_template_id NUMBER;
758 l_dest_template_id NUMBER;
759 l_module VARCHAR2(30) := 'COPY_PO_LINE_UDA';
760 attrGroups AttrGroupList;
761 l_clm_contract_type po_lines_all.CONTRACT_TYPE%TYPE;
762 l_clm_idc_type po_lines_all.CLM_IDC_TYPE%TYPE;
763
764 CURSOR attr_groups_cursor(p_bid_number NUMBER,p_bid_line_number NUMBER) is
765 select distinct(ATTR_GROUP_NAME) ATTR_GROUP_NAME
766 FROM ego_attr_groups_v ag_v, po_uda_ag_template_usages ag, pon_bid_item_prices bid_lines
767 where bid_lines.bid_number = p_bid_number
768 and bid_lines.line_number = p_bid_line_number
769 and bid_lines.uda_template_id = ag.template_id
770 and Nvl(ag.attribute1,'A') = Decode(ag.attribute1,NULL,'A',bid_lines.clm_idc_type)
771 and Nvl(ag.attribute2,'A') = Decode(ag.attribute2,NULL,'A',bid_lines.clm_contract_type)
772 and ag.attribute_group_id = ag_v.attr_group_id;
773
774 BEGIN
775 BEGIN
776 select uda_template_id,contract_type,clm_idc_type
777 into l_src_template_id,l_clm_contract_type,l_clm_idc_type
778 from
779 po_lines_all
780 where
781 po_line_id = p_po_line_id;
782 LOG_MESSAGE(l_module,'PO Line Template Id : '||l_src_template_id);
783
784 EXCEPTION
785 WHEN NO_DATA_FOUND THEN
786 LOG_MESSAGE(l_module,'Error occured while fetching po line template id.');
787 RETURN;
788 END;
789
790 BEGIN
791 select uda_template_id into l_dest_template_id
792 from
793 pon_bid_item_prices
794 where
795 bid_number = p_bid_header_id
796 and line_number = p_bid_line_number;
797 LOG_MESSAGE(l_module,'Bid Line Template Id : '||l_dest_template_id);
798
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 LOG_MESSAGE(l_module,'Error occured while fetching bid line template id.');
802 RETURN;
803 END;
804
805 OPEN attr_groups_cursor(p_bid_header_id,p_bid_line_number);
806 LOG_MESSAGE(l_module,'Opened Attribute groups cursor.');
807 FETCH attr_groups_cursor bulk collect into attrGroups;
808 log_message(l_module,'Fetched attribute groups Cursor');
809 close attr_groups_cursor;
810 log_message(l_module,'Got Attribute Groups '||attrGroups.COUNT);
811
812 l_attribute_group_table := GET_ATTRIBUTE_GROUP_TABLE(attrGroups);
813
814 copyfrom_pk_col_value_pairs :=
815 EGO_COL_NAME_VALUE_PAIR_ARRAY
816 (
817 EGO_COL_NAME_VALUE_PAIR_OBJ(g_po_line_pkey_col, p_po_line_id),
818 EGO_COL_NAME_VALUE_PAIR_OBJ(g_po_draft_pkey_col, -1)
819 );
820
821 copyto_pk_col_value_pairs :=
822 EGO_COL_NAME_VALUE_PAIR_ARRAY
823 (
824 EGO_COL_NAME_VALUE_PAIR_OBJ(g_bid_number_pkey_col, p_bid_header_id),
825 EGO_COL_NAME_VALUE_PAIR_OBJ(g_auc_line_number, p_bid_line_number)
826 );
827
828 AUTO_CREATE_UDA_COPY(l_src_template_id
829 ,l_dest_template_id
830 ,copyfrom_pk_col_value_pairs
831 ,copyto_pk_col_value_pairs
832 ,'SPECIFIC'
833 ,l_attribute_group_table
834 ,FND_API.G_FALSE
835 ,x_return_status
836 ,x_msg_count
837 ,x_msg_data);
838
839 log_message(l_module,'Return from Auto_create_uda_copy '||x_return_status);
840 IF x_return_status = 'S' THEN
841
842 update pon_bid_item_prices
843 set clm_contract_type = l_clm_contract_type,clm_idc_type =l_clm_idc_type
844 where
845 bid_number = p_bid_header_id and
846 line_number = p_bid_line_number;
847
848 END IF;
849
850 END COPY_PO_LINE_UDA;
851
852
853
854
855 FUNCTION GET_ATTR_GROUP_LIST (p_src_doc_level IN VARCHAR2,
856 p_tar_doc_level IN VARCHAR2,
857 p_src_doc_type IN VARCHAR2,
858 p_tar_doc_type IN VARCHAR2,
859 p_src_template_id IN NUMBER,
860 p_tar_template_id IN NUMBER)
861 RETURN PO_TBL_VARCHAR30 IS
862 TYPE agCurType IS REF CURSOR;
863 attrGrpCur agCurType;
864 l_attr_grp_list PO_TBL_VARCHAR30;
865 BEGIN
866 attrGrpCur := NULL;
867 if ( p_src_doc_type = 'PO' ) THEN
868 if ( p_tar_doc_level = 'HEADER' ) THEN
869 open attrGrpCur for SELECT ATTR_GROUP_NAME FROM ego_attr_groups_v WHERE (attr_group_id IN
870 (SELECT DISTINCT(ATTRIBUTE_GROUP_ID) FROM po_uda_ag_template_usages
871 WHERE template_id = p_tar_template_id and ATTRIBUTE_CATEGORY <> 'DOCUMENT_NUMBERING' and ATTRIBUTE_CATEGORY <> 'ADDRESS')
872 AND ATTR_GROUP_NAME <> 'AQ_PLAN1');
873 elsif ( p_tar_doc_level = 'LINE' and p_src_doc_level = 'LINE' ) THEN
874 open attrGrpCur for SELECT ATTR_GROUP_NAME FROM ego_attr_groups_v WHERE attr_group_id IN
875 (SELECT DISTINCT(ATTRIBUTE_GROUP_ID) FROM po_uda_ag_template_usages
876 WHERE template_id = p_tar_template_id and attribute_category <> 'FED_FIELDS');
877 elsif ( p_tar_doc_level = 'LINE' and p_src_doc_level = 'SHIP' ) THEN
878 open attrGrpCur for SELECT ATTR_GROUP_NAME FROM ego_attr_groups_v WHERE attr_group_id IN
879 (SELECT DISTINCT(ATTRIBUTE_GROUP_ID) FROM po_uda_ag_template_usages
880 WHERE template_id = p_tar_template_id and attribute_category = 'FED_FIELDS');
881 end if;
882 elsif ( p_src_doc_type = 'REQ' ) THEN
883 if ( p_tar_doc_level = 'HEADER' ) THEN
884 open attrGrpCur for SELECT ATTR_GROUP_NAME FROM ego_attr_groups_v WHERE attr_group_id IN
885 (SELECT DISTINCT(ATTRIBUTE_GROUP_ID) FROM po_uda_ag_template_usages
886 WHERE template_id = p_tar_template_id and ATTRIBUTE_CATEGORY <> 'DOCUMENT_NUMBERING' and ATTRIBUTE_CATEGORY <> 'ADDRESS');
887 elsif ( p_tar_doc_level = 'LINE' ) THEN
888 open attrGrpCur for SELECT ATTR_GROUP_NAME FROM ego_attr_groups_v WHERE attr_group_id IN
889 (SELECT DISTINCT(ATTRIBUTE_GROUP_ID) FROM po_uda_ag_template_usages
890 WHERE template_id = p_src_template_id);
891 end if;
892 end if;
893 if ( attrGrpCur is not null ) then
894 fetch attrGrpCur Bulk Collect into l_attr_grp_list;
895 close attrGrpCur;
896 end if;
897 return l_attr_grp_list;
898 END GET_ATTR_GROUP_LIST;
899
900
901
902
903 FUNCTION GET_EGO_PKEY_VALUE_PAIR (p_pkey IN PO_TBL_NUMBER,
904 p_doc_level IN VARCHAR2,
905 p_doc_type IN VARCHAR2)
906 RETURN EGO_COL_NAME_VALUE_PAIR_ARRAY IS
907 pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
908 l_col_name1 VARCHAR2(40);
909 l_col_name2 VARCHAR2(40);
910 BEGIN
911 if ( p_doc_level = 'HEADER' ) THEN
912 CASE p_doc_type
913 WHEN 'REQ' THEN
914 l_col_name1 := g_req_header_pkey_col;
915 l_col_name2 := NULL;
916 WHEN 'SOL' THEN
917 l_col_name1 := g_auc_header_pkey_col;
918 l_col_name2 := NULL;
919 WHEN 'OFR' THEN
920 l_col_name1 := g_bid_number_pkey_col;
921 l_col_name2 := NULL;
922 WHEN 'PO' THEN
923 l_col_name1 := g_po_header_pkey_col;
924 l_col_name2 := g_po_draft_pkey_col;
925 ELSE
926 l_col_name1 := 'INVALID';
927 l_col_name2 := NULL;
928 END CASE;
929
930 elsif ( p_doc_level = 'LINE' ) THEN
931 CASE p_doc_type
932 WHEN 'REQ' THEN
933 l_col_name1 := g_req_line_pkey_col;
934 l_col_name2 := NULL;
935 WHEN 'SOL' THEN
936 l_col_name1 := g_auc_header_pkey_col;
937 l_col_name2 := g_auc_line_number;
938 WHEN 'PO' THEN
939 l_col_name1 := g_po_line_pkey_col;
940 l_col_name2 := g_po_draft_pkey_col;
941 ELSE
942 l_col_name1 := 'INVALID';
943 l_col_name2 := NULL;
944 END CASE;
945 elsif ( p_doc_level = 'SHIP' ) THEN
946 CASE p_doc_type
947 WHEN 'PO' THEN
948 l_col_name1 := g_po_shipment_pkey_col;
949 l_col_name2 := g_po_draft_pkey_col;
950 ELSE
951 l_col_name1 := 'INVALID';
952 l_col_name2 := NULL;
953 END CASE;
954 END IF;
955 if ( l_col_name2 is NULL ) THEN
956
957 pk_col_value_pairs :=
958 EGO_COL_NAME_VALUE_PAIR_ARRAY (
959 EGO_COL_NAME_VALUE_PAIR_OBJ(l_col_name1,p_pkey(1))
960 );
961 else
962
963 pk_col_value_pairs :=
964 EGO_COL_NAME_VALUE_PAIR_ARRAY (
965 EGO_COL_NAME_VALUE_PAIR_OBJ(l_col_name1,p_pkey(1)),
966 EGO_COL_NAME_VALUE_PAIR_OBJ(l_col_name2,p_pkey(2))
967 );
968 end if;
969
970
971 return pk_col_value_pairs;
972 END GET_EGO_PKEY_VALUE_PAIR;
973
974
975
976
977
978
979 PROCEDURE COPY_UDA_DATA(p_src_pkey IN PO_TBL_NUMBER,
980 p_tar_pkey IN PO_TBL_NUMBER,
981 p_src_template_id IN NUMBER,
982 p_tar_template_id IN NUMBER,
983 p_src_doc_level IN VARCHAR2,
984 p_tar_doc_level IN VARCHAR2,
985 p_src_doc_type IN VARCHAR2,
986 p_tar_doc_type IN VARCHAR2,
987 x_return_status OUT NOCOPY VARCHAR2,
988 x_msg_count OUT NOCOPY NUMBER,
989 x_msg_data OUT NOCOPY VARCHAR2)
990 IS
991
992 copyfrom_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
993 copyto_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
994 l_attribute_group_table PO_TBL_VARCHAR30;
995 l_cpy_mode VARCHAR2(10);
996
997 BEGIN
998
999
1000
1001 copyfrom_pk_col_value_pairs := GET_EGO_PKEY_VALUE_PAIR (p_src_pkey,
1002 p_src_doc_level,
1003 p_src_doc_type);
1004
1005 copyto_pk_col_value_pairs := GET_EGO_PKEY_VALUE_PAIR (p_tar_pkey,
1006 p_tar_doc_level,
1007 p_tar_doc_type);
1008
1009
1010 l_cpy_mode := 'SPECIFIC';
1011 l_attribute_group_table := GET_ATTR_GROUP_LIST (p_src_doc_level,
1012 p_tar_doc_level,
1013 p_src_doc_type,
1014 p_tar_doc_type,
1015 p_src_template_id,
1016 p_tar_template_id);
1017
1018
1019
1020
1021 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
1022 (
1023 from_template_id => p_src_template_id
1024 ,to_template_id => p_tar_template_id
1025 ,from_pk_col_value_pairs => copyfrom_pk_col_value_pairs
1026 ,to_pk_col_value_pairs => copyto_pk_col_value_pairs
1027 ,copy_attribute_groups => l_cpy_mode
1028 ,attribute_group_table => l_attribute_group_table
1029 ,p_commit => FND_API.G_FALSE
1030 ,x_return_status => x_return_status
1031 ,x_msg_count => x_msg_count
1032 ,x_msg_data => x_msg_data
1033 );
1034
1035
1036 END COPY_UDA_DATA;
1037
1038
1039 -- Method to copy Address Attribute Group Data
1040 -- Addresses are not handled by UDA API
1041 -- Invoke EGO API directly to get the data
1042 -- And insert into the extension table with new extension Id and primary keys
1043 PROCEDURE PROCESS_ADDRESSES(p_src_pkey IN PO_TBL_NUMBER,
1044 p_tar_pkey IN PO_TBL_NUMBER,
1045 p_src_template_id IN NUMBER,
1046 p_tar_template_id IN NUMBER,
1047 p_src_doc_type IN VARCHAR2,
1048 p_excl_addr_types IN PO_TBL_VARCHAR50,
1049 x_return_status OUT NOCOPY VARCHAR2,
1050 x_msg_count OUT NOCOPY NUMBER,
1051 x_msg_data OUT NOCOPY VARCHAR2)
1052 IS
1053
1054 l_attr_grp_id NUMBER;
1055 l_tar_attr_grp_id NUMBER;
1056 l_src_attr_grp_type VARCHAR2(40);
1057 l_tar_attr_grp_type VARCHAR2(40);
1058
1059 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
1060
1061 l_process_attr_row_tbl EGO_USER_ATTR_ROW_TABLE;
1062 l_process_attr_data_tbl EGO_USER_ATTR_DATA_TABLE;
1063 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1064
1065
1066 l_tar_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1067 l_attr_list VARCHAR2(2000);
1068 l_src_data_level VARCHAR2(100);
1069 l_tar_data_level VARCHAR2(100);
1070 l_src_object_name VARCHAR2(200);
1071 l_tar_object_name VARCHAR2(200);
1072 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1073 l_tar_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1074
1075
1076 x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1077 x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
1078 x_errorcode NUMBER;
1079 x_failed_row_id_list VARCHAR2(1000);
1080 x_extension_id NUMBER;
1081 x_mode VARCHAR2(1000);
1082
1083 l_new_extension_id NUMBER;
1084
1085 CURSOR c_attr_list(p_ag_type VARCHAR2) IS
1086 SELECT END_USER_COLUMN_NAME
1087 FROM FND_DESCR_FLEX_COLUMN_USAGES
1088 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
1089 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'addresses';
1090
1091 Type addr_extn_type IS REF Cursor;
1092 addrExtnCur addr_extn_type;
1093
1094 l_extn_id_list PO_TBL_NUMBER;
1095
1096 Begin
1097
1098 IF ( p_src_doc_type = 'SOL' ) THEN
1099 l_src_attr_grp_type := 'PON_AUC_HDRS_EXT_ATTRS';
1100 ELSIF ( p_src_doc_type = 'REQ' ) THEN
1101 l_src_attr_grp_type := 'PO_REQ_HEADER_EXT_ATTRS';
1102 END IF;
1103
1104 l_tar_attr_grp_type := 'PON_AUC_HDRS_EXT_ATTRS';
1105
1106 select attribute_group_id into l_attr_grp_id from po_uda_ag_template_usages where template_id = p_src_template_id
1107 and attribute_category = 'ADDRESS';
1108
1109
1110 select attribute_group_id into l_tar_attr_grp_id from po_uda_ag_template_usages where template_id = p_tar_template_id
1111 and attribute_category = 'ADDRESS';
1112
1113
1114 for l_attr_list_rec in c_attr_list(l_src_attr_grp_type)
1115 loop
1116 IF l_attr_list IS NULL OR l_attr_list = '' THEN
1117 l_attr_list := l_attr_list_rec.END_USER_COLUMN_NAME;
1118 else
1119 l_attr_list := l_attr_list || ',' || l_attr_list_rec.END_USER_COLUMN_NAME;
1120 end if;
1121 end loop;
1122
1123
1124 SELECT data_level_name into l_src_data_level FROM ego_data_level_b
1125 WHERE attr_group_type = l_src_attr_grp_type
1126 AND data_level_name NOT LIKE '%ARCHIVE%';
1127
1128
1129 SELECT data_level_name into l_tar_data_level FROM ego_data_level_b
1130 WHERE attr_group_type = l_tar_attr_grp_type
1131 AND data_level_name NOT LIKE '%ARCHIVE%';
1132
1133 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
1134 l_attr_group_request_table.extend(1);
1135 l_attr_group_request_table(1) := ego_attr_group_request_obj(
1136 l_attr_grp_id
1137 ,201 -- application id is always 201
1138 ,l_src_attr_grp_type -- p_ag_type
1139 ,'addresses'
1140 ,l_src_data_level
1141 ,NULL
1142 ,NULL
1143 ,NULL
1144 ,NULL
1145 ,NULL
1146 ,l_attr_list
1147 );
1148
1149
1150 l_src_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(l_src_attr_grp_type).l_object_name;
1151 l_tar_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(l_tar_attr_grp_type).l_object_name;
1152
1153 l_src_pk_col_name_val_pairs := GET_EGO_PKEY_VALUE_PAIR (p_pkey=>p_src_pkey,
1154 p_doc_level=>'HEADER',
1155 p_doc_type=>p_src_doc_type);
1156 l_tar_pk_col_name_val_pairs := GET_EGO_PKEY_VALUE_PAIR (p_pkey=>p_tar_pkey,
1157 p_doc_level=>'HEADER',
1158 p_doc_type=>'SOL');
1159 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(ego_col_name_value_pair_obj('UDA_TEMPLATE_ID',p_tar_template_id));
1160
1161
1162
1163
1164
1165
1166 if ( p_src_doc_type = 'REQ' ) then
1167 open addrExtnCur for select extension_id from po_req_headers_ext_b where
1168 requisition_header_id = p_src_pkey(1) and attr_group_id = l_attr_grp_id order by extension_id;
1169 else
1170 open addrExtnCur for select extension_id from pon_auction_headers_ext_b where
1171 auction_header_id = p_src_pkey(1) and attr_group_id = l_attr_grp_id order by extension_id;
1172 end if;
1173 fetch addrExtnCur bulk Collect into l_extn_id_list;
1174 close addrExtnCur;
1175
1176 for i in 1..l_extn_id_list.Count
1177 loop
1178
1179 --some records are there copy them
1180 ego_user_attrs_data_pvt.get_user_attrs_data(
1181 p_api_version => 1.0
1182 ,p_object_name => l_src_object_name --Get it from PO_UDA_AG_TEMPLATES
1183 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs--Source Primary Keys
1184 ,p_attr_group_request_table => l_attr_group_request_table --Construct the Attribute Group Table
1185 ,x_attributes_row_table => x_attributes_row_table
1186 ,x_attributes_data_table => x_attributes_data_table
1187 ,x_return_status => x_return_status
1188 ,x_errorcode => x_errorcode
1189 ,x_msg_count => x_msg_count
1190 ,x_msg_data => x_msg_data
1191 );
1192
1193
1194
1195 IF x_return_status = 'S' AND x_attributes_row_table.Count > 0 THEN
1196 SELECT Value(a)
1197 BULK COLLECT INTO l_process_attr_row_tbl
1198 FROM TABLE(x_attributes_row_table) a
1199 WHERE a.row_identifier = l_extn_id_list(i);
1200
1201 SELECT Value(b)
1202 BULK COLLECT INTO l_process_attr_data_tbl
1203 FROM TABLE(x_attributes_data_table) b
1204 WHERE b.ROW_IDENTIFIER = l_extn_id_list(i);
1205
1206 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
1207 INTO l_new_extension_id
1208 FROM dual;
1209
1210
1211
1212
1213 for i in 1..l_process_attr_row_tbl.Count
1214 loop
1215 l_process_attr_row_tbl(i).Row_identifier := l_new_extension_id;
1216 l_process_attr_row_tbl(i).Attr_Group_Id := l_tar_attr_grp_id;
1217
1218 l_process_attr_row_tbl(i).data_level := l_tar_data_level;
1219 l_process_attr_row_tbl(i).attr_group_type := l_tar_attr_grp_type;
1220 end loop;
1221
1222 FOR i IN 1..l_process_attr_data_tbl.Count
1223 LOOP
1224 l_process_attr_data_tbl(i).ROW_IDENTIFIER := l_new_extension_id;
1225
1226
1227 END LOOP;
1228
1229 ego_user_attrs_data_pvt.process_user_attrs_data
1230 (
1231 p_api_version => 1.0
1232 ,p_object_name => l_tar_object_name
1233 ,p_attributes_row_table => l_process_attr_row_tbl--from Previous STEP but change the ATTR_GROUP_ID here as for the target
1234 ,p_attributes_data_table => l_process_attr_data_tbl --can be same object got from previous step
1235 ,p_pk_column_name_value_pairs => l_tar_pk_col_name_val_pairs
1236 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs -- pass { "UDA_TEMPLATE_ID", 343 }
1237 ,x_extension_id => x_extension_id
1238 ,x_mode => x_mode
1239 ,x_failed_row_id_list => x_failed_row_id_list
1240 ,x_return_status => x_return_status
1241 ,x_errorcode => x_errorcode
1242 ,x_msg_count => x_msg_count
1243 ,x_msg_data => x_msg_data
1244 );
1245
1246 END IF;
1247 end loop;
1248
1249 --delete irrelevant addresses
1250 if ( p_excl_addr_types.count > 0 ) Then
1251 for i in p_excl_addr_types.FIRST..p_excl_addr_types.LAST
1252 loop
1253 delete from pon_auction_headers_ext_b
1254 where auction_header_id = p_tar_pkey(1)
1255 and c_ext_attr39 = p_excl_addr_types(i);
1256 end loop;
1257 end if;
1258
1259 End PROCESS_ADDRESSES;
1260
1261 END PON_COPY_UDAS_GRP;
1262