[Home] [Help]
PACKAGE BODY: APPS.PO_UDA_AUTOCREATE_PKG
Source
1 PACKAGE BODY po_uda_autocreate_pkg AS
2 /* $Header: PO_UDA_AUTOCREATE_PKG.plb 120.22.12020000.4 2013/04/10 14:52:48 amalick ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(240) := 'PO_UDA_AUTOCREATE_PKG';
5 g_log_head CONSTANT VARCHAR2(240) := 'po.plsql.PO_UDA_AUTOCREATE_PKG.';
6
7
8 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
9 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
10
11 g_tar_head_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
12
13 g_src_func_area VARCHAR2(100);
14 g_src_doc_type VARCHAR2(100);
15 g_src_doc_style NUMBER;
16
17 g_target_func_area VARCHAR2(100);
18 g_target_doc_type VARCHAR2(100);
19 g_target_doc_style NUMBER;
20
21 g_interface_header_id NUMBER;
22
23 g_tar_header_uda_template_id NUMBER;
24 g_target_uda_template_id NUMBER;
25 g_draft_id NUMBER;
26
27 PROCEDURE autocreate_uda_postprocess (p_template_id IN NUMBER,p_level IN VARCHAR2);
28
29
30 PROCEDURE exec_uda_function(
31 p_template_id IN NUMBER,
32 p_pk1_col_name IN VARCHAR2,
33 p_pk1_col_value IN VARCHAR2,
34 p_pk2_col_name IN VARCHAR2,
35 p_pk2_col_value IN VARCHAR2,
36 p_attr_group_id_tbl IN po_tbl_number
37 );
38
39 PROCEDURE auto_complexpricing_ag;
40
41 PROCEDURE autocreate_uda_header_attr; /* bug 9879573*/
42
43 PROCEDURE auto_default_sys_addresses (p_address_type IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2);
44
45 PROCEDURE process_addresses(p_ag_type IN VARCHAR2
46 ,p_src_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
47 ,p_src_uda_template_id IN NUMBER
48 ,p_attr_group_id IN NUMBER
49 ,p_address_type IN VARCHAR2
50 ,p_extension_id IN NUMBER
51 );
52
53
54 /*
55 -------------------------
56 --PRIVATE PROCEDURES--
57 -------------------------
58 */
59
60 PROCEDURE get_document_levels( p_func_area IN VARCHAR2
61 ,p_doc_type IN VARCHAR2
62 ,p_doc_style IN NUMBER
63 ,x_doc_level_tbl IN OUT NOCOPY PO_TBL_VARCHAR15
64 )
65 IS
66
67 l_api_name VARCHAR2(30) := 'get_document_levels';
68 l_progress VARCHAR2(3) := '000';
69
70 BEGIN
71
72 IF g_debug_stmt THEN
73 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
74 END IF;
75
76 IF g_debug_stmt THEN
77 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
78 , p_token => l_progress
79 , p_message => ' p_func_area : ' || p_func_area
80 ||' p_doc_type : ' || p_doc_type
81 ||' p_doc_style : ' || p_doc_style
82 );
83
84 END IF;
85
86 l_progress := '010';
87
88 /*
89 -- Get the Document Levels avaiable for the Document Type
90 -- Always target document's functional area is either in Purchasing, Sourcing
91 -- For Functional Area 'Purchasing' Document Type can be
92 -- Blanket Purchase Agreement, Purchase Order and Contract Purchase Agreement
93 --
94 */
95
96 SELECT DISTINCT document_level
97 BULK COLLECT INTO x_doc_level_tbl
98 FROM po_uda_ag_templates
99 WHERE functional_area = p_func_area
100 AND ( p_doc_type IS NULL
101 OR document_type = p_doc_type
102 )
103 AND ( p_doc_style IS NULL
104 OR document_style_id = p_doc_style
105 )
106 AND document_level <> 'DISTRIBUTION'; -- bug 12611018
107
108 IF g_debug_stmt THEN
109 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name
110 ,p_token => l_progress
111 ,p_message => 'No of available document Levels : ' || x_doc_level_tbl.Count
112 );
113
114 END IF;
115
116 IF g_debug_stmt THEN
117 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
118 END IF;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF g_debug_unexp THEN
123 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
124 END IF;
125 RAISE;
126 END get_document_levels;
127
128 FUNCTION get_uda_template_id
129 ( p_functional_area IN VARCHAR2
130 ,p_doc_type IN VARCHAR2
131 ,p_doc_level IN VARCHAR2
132 ,p_doc_style_id IN NUMBER DEFAULT NULL
133 )
134 RETURN NUMBER
135 IS
136 l_api_name VARCHAR2(30) := 'get_uda_template_id';
137 l_progress VARCHAR2(3) := '000';
138
139
140 l_return_status VARCHAR2(1);
141 l_err_msg VARCHAR2(1000);
142 l_template_id NUMBER;
143
144 BEGIN
145
146 IF g_debug_stmt THEN
147 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
148 END IF;
149 l_progress := '010';
150
151 l_template_id := po_uda_data_util.get_template_id(
152 p_functional_area => p_functional_area,
153 p_document_type => p_doc_type,
154 p_document_style_id => p_doc_style_id,
155 p_document_level => p_doc_level,
156 p_input_date => SYSDATE,
157 x_return_status => l_return_status,
158 x_err_msg => l_err_msg
159 );
160
161 IF g_debug_stmt THEN
162 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name,
163 p_token => l_progress,
164 p_message => ' Return Status : ' ||l_return_status || ' Template ID ' || l_template_id
165 );
166 END IF;
167
168 IF l_template_id IS NOT NULL AND l_return_status =FND_API.G_RET_STS_SUCCESS THEN
169 RETURN l_template_id;
170 ELSE
171 RETURN NULL;
172 END IF;
173
174 IF g_debug_stmt THEN
175 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
176 END IF;
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF g_debug_unexp THEN
180 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
181 END IF;
182 RAISE;
183 END get_uda_template_id;
184
185
186 PROCEDURE get_attribute_groups(p_uda_template_id IN NUMBER,
187 x_attr_group_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
188 )
189 IS
190 l_api_name VARCHAR2(30) := 'get_attribute_groups';
191 l_progress VARCHAR2(3) := '000';
192
193
194 l_return_status VARCHAR2(1);
195 l_err_msg VARCHAR2(1000);
196 l_attr_group_tbl PO_TBL_VARCHAR30;
197
198 BEGIN
199 IF g_debug_stmt THEN
200 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
201 END IF;
202 l_progress := '010';
203
204 SELECT DISTINCT ag.attr_group_name
205 BULK COLLECT INTO x_attr_group_tbl
206 FROM po_uda_ag_template_usages tu, ego_attr_groups_v ag
207 WHERE ag.attr_group_id = tu.attribute_group_id
208 AND tu.template_id = p_uda_template_id
209 AND tu.attribute_category NOT IN ('PRICING','DOCUMENT_NUMBERING'); --12611018 Document numbering added
210
211
212 IF g_debug_stmt THEN
213 PO_DEBUG.debug_var(p_log_head => g_log_head||l_api_name,
214 p_progress => l_progress,
215 p_name => 'l_attr_group_tbl',
216 p_value => x_attr_group_tbl
217 );
218 END IF;
219
220
221 IF g_debug_stmt THEN
222 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
223 END IF;
224 EXCEPTION
225 WHEN OTHERS THEN
226 IF g_debug_unexp THEN
227 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
228 END IF;
229 END get_attribute_groups;
230
231
232 -- Get Priorites for copy by Auto Create
233 -- What to do when no rule exists.
234 PROCEDURE get_autocreate_priorities(p_doc_level IN VARCHAR2,
235 p_attr_group IN VARCHAR2,
236 p_src_doc_type IN VARCHAR2,
237 p_tar_doc_type IN VARCHAR2,
238 p_group_property IN VARCHAR2,
239 x_source_doc_level OUT NOCOPY VARCHAR2,
240 x_priorities_tbl OUT NOCOPY PO_TBL_VARCHAR3,
241 x_group_type OUT NOCOPY VARCHAR2
242 )
243 /* Query our newly created Rules Table */
244 IS
245 l_api_name VARCHAR2(30) := 'get_autocreate_priorities';
246 l_progress VARCHAR2(3) := '000';
247
248
249 l_return_status VARCHAR2(1);
250 l_err_msg VARCHAR2(1000);
251 l_priorities_tbl PO_TBL_VARCHAR3;
252
253 l_priority VARCHAR2(4);
254
255 BEGIN
256 IF g_debug_stmt THEN
257 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
258 END IF;
259 l_progress := '010';
260
261 IF g_debug_stmt THEN
262 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
263 p_token => l_progress,
264 p_message => 'p_doc_level : '|| p_doc_level
265 || 'p_attr_group : ' || p_attr_group
266 || 'p_src_doc_type : ' ||p_src_doc_type
267 || 'p_tar_doc_type : ' ||p_tar_doc_type
268 );
269 END IF;
270
271
272 x_priorities_tbl := PO_TBL_VARCHAR3();
273 l_priorities_tbl := PO_TBL_VARCHAR3(); --bug 12611018
274 l_priorities_tbl.extend(5); --bug 12611018
275
276 BEGIN
277
278 /*bug 12611018 starts : Changed the dynamic SQL to a static SQL with binds*/
279 SELECT priority1,
280 priority2,
281 priority3,
282 priority4,
283 priority5
284 INTO l_priorities_tbl(1),
285 l_priorities_tbl(2),
286 l_priorities_tbl(3),
287 l_priorities_tbl(4),
288 l_priorities_tbl(5)
289 FROM po_autocreate_uda_rules
290 WHERE source = p_src_doc_type
291 AND target = p_tar_doc_type
292 AND target_doc_level = p_doc_level
293 AND group_name = p_attr_group
294 AND nvl ( group_property,'a') = nvl(p_group_property,'a');
295
296 FOR i IN 1..5
297 loop
298
299 IF l_priorities_tbl(i) IS NOT NULL
300 THEN
301 x_priorities_tbl.extend();
302 x_priorities_tbl(i) := l_priorities_tbl(i);
303 ELSE
304 EXIT;
305 END IF;
306 /*bug 12611018 ends*/
307
308 IF g_debug_stmt THEN
309 PO_DEBUG.debug_var(p_log_head => g_log_head||l_api_name,
310 p_progress => l_progress,
311 p_name => 'l_priorities_tbl',
312 p_value => l_priorities_tbl(i)
313 );
314 END IF;
315
316 END LOOP;
317 EXCEPTION
318 WHEN No_Data_Found THEN
319 /*x_priorities_tbl.extend();
320 SELECT Decode (p_src_doc_type,'REQ', 'PR'
321 ,'OFFER','OFR')
322 INTO x_priorities_tbl(1)
323 FROM dual;*/
324 NULL;
325 END;
326
327
328 IF g_debug_stmt THEN
329 PO_DEBUG.debug_var(p_log_head => g_log_head||l_api_name,
330 p_progress => l_progress,
331 p_name => 'x_priorities_tbl Count',
332 p_value => x_priorities_tbl.count
333 );
334 END IF;
335
336 l_progress := '020';
337
338 IF x_priorities_tbl.Count > 0 THEN
339 BEGIN
340 SELECT source_doc_level,group_type
341 INTO x_source_doc_level,x_group_type
342 FROM po_autocreate_uda_rules
343 WHERE source = p_src_doc_type
344 AND target = p_tar_doc_type
345 AND target_doc_level = p_doc_level
346 AND group_name = p_attr_group
347 AND (p_group_property IS NULL
348 OR group_property = p_group_property);
349 EXCEPTION
350 WHEN No_Data_Found THEN
351 x_source_doc_level := p_doc_level;
352 x_group_type := 'ATTR_GROUP';
353 END;
354 END IF;
355
356
357 IF g_debug_stmt THEN
358 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
359 p_token => l_progress,
360 p_message => 'source_doc_level : '|| x_source_doc_level
361 || 'group_type : ' || x_group_type
362 );
363 END IF;
364
365
366 IF g_debug_stmt THEN
367 po_debug.debug_end(p_log_head => g_log_head||l_api_name);
368 END IF;
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF g_debug_unexp THEN
372 po_debug.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
373 END IF;
374 --??Return l_priorities_tbl;
375 END get_autocreate_priorities;
376
377
378 PROCEDURE get_autocreate_properties(p_doc_level IN VARCHAR2,
379 p_attr_group IN VARCHAR2,
380 p_src_doc_type IN VARCHAR2,
381 p_tar_doc_type IN VARCHAR2,
382 x_group_property_tbl OUT NOCOPY PO_TBL_VARCHAR30
383 )
384 /* Query our newly created Rules Table */
385 IS
386 l_api_name VARCHAR2(30) := 'get_autocreate_properties';
387 l_progress VARCHAR2(3) := '000';
388
389
390 l_return_status VARCHAR2(1);
391 l_err_msg VARCHAR2(1000);
392 l_priorities_tbl PO_TBL_VARCHAR3;
393
394 l_priority VARCHAR2(4);
395
396 BEGIN
397 IF g_debug_stmt THEN
398 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
399 END IF;
400
401 IF g_debug_stmt THEN
402 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
403 p_token => l_progress,
404 p_message => 'p_doc_level : '|| p_doc_level
405 || 'p_attr_group : ' || p_attr_group
406 || 'p_src_doc_type : ' ||p_src_doc_type
407 || 'p_tar_doc_type : ' ||p_tar_doc_type
408 );
409 END IF;
410
411 l_progress := '010';
412
413 SELECT group_property
414 BULK COLLECT INTO x_group_property_tbl
415 FROM po_autocreate_uda_rules
416 WHERE source = p_src_doc_type
417 AND target = p_tar_doc_type
418 AND target_doc_level = p_doc_level
419 AND group_name = p_attr_group;
420
421
422
423 IF g_debug_stmt THEN
424 PO_DEBUG.debug_var(p_log_head => g_log_head||l_api_name,
425 p_progress => l_progress,
426 p_name => 'x_group_property_tbl',
427 p_value => x_group_property_tbl
428 );
429 END IF;
430
431
432 IF x_group_property_tbl.Count = 0 THEN
433 SELECT NULL
434 BULK COLLECT INTO x_group_property_tbl
435 FROM dual;
436 END IF;
437
438
439 IF g_debug_stmt THEN
440 po_debug.debug_end(p_log_head => g_log_head||l_api_name);
441 END IF;
442 EXCEPTION
443 WHEN OTHERS THEN
444 IF g_debug_unexp THEN
445 po_debug.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
446 END IF;
447 --??Return l_priorities_tbl;
448 END get_autocreate_properties;
449
450 --Check data exists in Extension table for a
451 --given Attribute Group, Level, Doc Type and Pkey
452 /*
453 * Based on the doc level and doc type determine the extension table
454 *
455 */
456
457 FUNCTION get_attribute_group_type(p_doc_level IN VARCHAR2,
458 p_doc_type IN VARCHAR2,
459 p_doc_style IN NUMBER,
460 p_func_area IN VARCHAR2
461 )
462 RETURN VARCHAR2
463 IS
464 l_api_name VARCHAR2(50) := 'get_attribute_group_type';
465 l_progress VARCHAR2(3) := '000';
466
467
468 l_ag_type VARCHAR2(300);
469 BEGIN
470 IF g_debug_stmt THEN
471 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
472 END IF;
473
474 IF g_debug_stmt THEN
475 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
476 , p_token => l_progress
477 , p_message => ' p_func_area : ' || p_func_area
478 ||' p_doc_type : ' || p_doc_type
479 ||' p_doc_style : ' || p_doc_style
480 ||' p_doc_level : ' || p_doc_level
481 );
482
483 END IF;
484
485 l_progress := '010';
486
487 -- Determine the Attribute group type
488 SELECT entity_code
489 INTO l_ag_type
490 FROM po_uda_ag_templates
491 WHERE 1=1
492 AND functional_area = p_func_area
493 AND Nvl(document_type,'@@@') = Nvl(p_doc_type,'@@@')
494 AND Nvl(document_style_id,-99) = Nvl(p_doc_style,-99)
495 AND document_level = p_doc_level
496 AND SYSDATE BETWEEN effective_from AND Nvl(effective_to,SYSDATE+1)
497 AND compiled_flag = 'Y';
498
499
500 IF g_debug_stmt THEN
501 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
502 , p_token => l_progress
503 , p_message => ' Attribute Group Type (Entity Code) ' || l_ag_type
504 );
505
506 END IF;
507
508 IF g_debug_stmt THEN
509 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
510 END IF;
511
512 RETURN l_ag_type;
513 EXCEPTION
514 WHEN OTHERS THEN
515 IF g_debug_unexp THEN
516 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
517 END IF;
518 RETURN NULL;
519 END get_attribute_group_type;
520
521 FUNCTION get_tbl_for_ag_type(p_ag_type IN VARCHAR2)
522 RETURN VARCHAR2
523 IS
524
525 l_api_name VARCHAR2(30) := 'get_tbl_for_ag_type';
526 l_progress VARCHAR2(3) := '000';
527 l_ext_table_name VARCHAR2(100);
528 BEGIN
529
530 IF g_debug_stmt THEN
531 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
532 END IF;
533
534 IF g_debug_stmt THEN
535 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
536 , p_token => l_progress
537 , p_message => ' Attribute Group Type (Entity Code) ' || p_ag_type
538 );
539
540 END IF;
541 l_progress := '010';
542
543 -- Get the table Name from attribute Group
544 SELECT ext_table_name
545 INTO l_ext_table_name
546 FROM ego_attr_group_types_v
547 WHERE 1=1
548 AND attr_group_type = p_ag_type;
549
550 IF g_debug_stmt THEN
551 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
552 ,p_token => l_progress
553 ,p_message => 'Ext table for Attribute Group ' || l_ext_table_name
554 );
555
556 END IF;
557
558 IF g_debug_stmt THEN
559 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
560 END IF;
561
562 RETURN l_ext_table_name;
563 EXCEPTION
564 WHEN OTHERS THEN
565 IF g_debug_unexp THEN
566 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
567 END IF;
568 RETURN NULL;
569 END get_tbl_for_ag_type;
570
571 FUNCTION get_template_id(p_table_name IN VARCHAR2,
572 p_pkey1_col_name IN VARCHAR2,
573 p_pkey1_val IN NUMBER,
574 p_pkey2_col_name IN VARCHAR2,
575 p_pkey2_val IN NUMBER
576 )
577 RETURN NUMBER
578 IS
579 l_template_id NUMBER;
580
581 l_api_name VARCHAR2(30) := 'get_template_id';
582 l_progress VARCHAR2(3) := '000';
583
584 l_query VARCHAR2(1500);
585 BEGIN
586
587 IF g_debug_stmt THEN
588 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
589 END IF;
590
591 IF g_debug_stmt THEN
592 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
593 , p_token => l_progress
594 , p_message => ' p_table_name : ' || p_table_name
595 ||' p_pkey1_col_name : ' || p_pkey1_col_name
596 ||' p_pkey1_val : ' || p_pkey1_val
597 ||' p_pkey2_col_name : ' || p_pkey2_col_name
598 ||' p_pkey2_val : ' || p_pkey2_val
599 );
600
601 END IF;
602
603 l_progress := '010';
604
605 l_query := 'SELECT uda_template_id FROM ' || p_table_name
606 ||' WHERE '|| p_pkey1_col_name || ' = ' || p_pkey1_val;
607
608 IF p_pkey2_col_name IS NOT NULL AND p_pkey2_val IS NOT NULL THEN
609 l_query := l_query|| ' AND '|| p_pkey2_col_name || ' = ' || p_pkey2_val;
610 END IF;
611
612
613 IF g_debug_stmt THEN
614 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
615 , p_token => l_progress
616 , p_message => ' Query to fetch the UDA template ID : ' || l_query
617 );
618
619 END IF;
620
621 l_progress := '020';
622 EXECUTE IMMEDIATE l_query INTO l_template_id;
623
624 IF g_debug_stmt THEN
625 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
626 , p_token => l_progress
627 , p_message => ' Template ID ' || l_template_id
628 );
629
630 END IF;
631
632 IF g_debug_stmt THEN
633 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
634 END IF;
635
636 RETURN l_template_id;
637 EXCEPTION
638 WHEN OTHERS THEN
639 IF g_debug_unexp THEN
640 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
641 END IF;
642 RETURN NULL;
643 END get_template_id;
644
645 PROCEDURE get_src_primary_key
646 (
647 p_doc_level IN VARCHAR2,
648 p_doc_type IN VARCHAR2,
649 p_intf_line_id IN NUMBER,
650 x_ag_type OUT NOCOPY VARCHAR2,
651 x_src_template_id OUT NOCOPY NUMBER,
652 x_ext_table_name OUT NOCOPY VARCHAR2,
653 x_pk1_col_name OUT NOCOPY VARCHAR2,
654 x_pk1_value OUT NOCOPY NUMBER,
655 x_pk2_col_name OUT NOCOPY VARCHAR2,
656 x_pk2_value OUT NOCOPY NUMBER
657 )
658 IS
659
660 l_api_name VARCHAR2(30) := 'get_src_primary_key';
661 l_progress VARCHAR2(3) := '000';
662
663 l_doc_style NUMBER;
664 l_doc_type VARCHAR2(100);
665 l_functional_area VARCHAR2(100);
666
667 l_ag_type VARCHAR2(400);
668 l_tbl_name VARCHAR2(200);
669
670 l_doc_primary_key PO_TBL_NUMBER;
671
672 l_pkey_col_name VARCHAR2(200);
673 l_src_table_name VARCHAR2(200);
674 BEGIN
675
676 IF g_debug_stmt THEN
677 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
678 END IF;
679
680 IF g_debug_stmt THEN
681 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
682 , p_token => l_progress
683 , p_message => ' p_doc_level : ' || p_doc_level
684 ||' p_doc_type : ' || p_doc_type
685 ||' p_intf_line_id : '|| p_intf_line_id
686 );
687
688 END IF;
689
690 l_progress := '010';
691
692 IF p_doc_type = 'SOL' THEN
693
694 l_functional_area := 'SOURCING';
695 l_doc_type := 'SOLICITATION';
696 l_doc_style := NULL;
697
698 IF p_doc_level = 'HEADER' THEN
699 x_pk1_col_name := 'AUCTION_HEADER_ID';
700 l_src_table_name := 'PON_AUCTION_HEADERS_ALL';
701 ELSIF p_doc_level = 'LINE' THEN
702 x_pk1_col_name :='AUCTION_HEADER_ID';
703 x_pk2_col_name :='LINE_NUMBER';
704 l_src_table_name :='PON_AUCTION_ITEM_PRICES_ALL';
705 END IF;
706
707 ELSIF p_doc_type = 'OFR' THEN
708
709 l_functional_area := 'SOURCING';
710 l_doc_type := 'OFFER';
711 l_doc_style := NULL;
712
713 IF p_doc_level = 'HEADER' THEN
714 x_pk1_col_name := 'BID_NUMBER';
715 l_src_table_name := 'PON_BID_HEADERS';
716 ELSIF p_doc_level = 'LINE' THEN
717 x_pk1_col_name := 'BID_NUMBER';
718 x_pk2_col_name := 'LINE_NUMBER';
719 l_src_table_name := 'PON_BID_ITEM_PRICES';
720 END IF;
721
722 ELSIF p_doc_type = 'PR' THEN
723
724 l_functional_area := 'REQUISITIONS';
725 l_doc_type := NULL;
726 l_doc_style := NULL;
727
728 IF p_doc_level = 'HEADER' THEN
729 x_pk1_col_name := 'REQUISITION_HEADER_ID';
730 l_src_table_name := 'PO_REQUISITION_HEADERS_ALL';
731 ELSIF p_doc_level = 'LINE' THEN
732 x_pk1_col_name := 'REQUISITION_LINE_ID';
733 l_src_table_name := 'PO_REQUISITION_LINES_ALL';
734 END IF;
735
736 ELSIF p_doc_type = 'IDV' THEN
737
738 l_functional_area := 'PURCHASING';
739 --<Bug 16310381> : Set the l_doc_type correctly
740 IF (po_autocreate_params.g_clm_source_document_id IS NOT NULL) THEN
741 SELECT type_lookup_code
742 INTO l_doc_type
743 FROM po_headers_all
744 WHERE po_header_id = po_autocreate_params.g_clm_source_document_id;
745 ELSE
746 l_doc_type := 'BLANKET';
747 END IF;
748
749 l_doc_style := NULL; -- Need to modify this
750
751 IF p_doc_level = 'HEADER' THEN
752 x_pk1_col_name := 'PO_HEADER_ID';
753 x_pk2_col_name := 'DRAFT_ID';
754 l_src_table_name := 'PO_HEADERS_ALL';
755
756 ELSIF p_doc_level = 'LINE' THEN
757 x_pk1_col_name := 'PO_LINE_ID';
758 x_pk2_col_name := 'DRAFT_ID';
759 l_src_table_name := 'PO_LINES_ALL';
760
761 ELSIF p_doc_level = 'SHIPMENT' THEN
762 x_pk1_col_name := 'LINE_LOCATION_ID';
763 x_pk2_col_name := 'DRAFT_ID';
764 l_src_table_name := 'PO_LINE_LOCATIONS_ALL';
765
766 END IF;
767
768 ELSIF p_doc_type = 'PO' THEN
769 l_functional_area := 'PURCHASING';
770 l_doc_type := 'STANDARD';
771 l_doc_style := NULL;
772
773 IF p_doc_level = 'HEADER' THEN
774 x_pk1_col_name := 'PO_HEADER_ID';
775 x_pk2_col_name := 'DRAFT_ID';
776 l_src_table_name := 'PO_HEADERS_ALL';
777 ELSIF p_doc_level = 'LINE' THEN
778 x_pk1_col_name := 'PO_LINE_ID';
779 x_pk2_col_name := 'DRAFT_ID';
780 l_src_table_name := 'PO_LINES_ALL';
781 ELSIF p_doc_level = 'SHIPMENT' THEN
782 x_pk1_col_name := 'LINE_LOCATION_ID';
783 x_pk2_col_name := 'DRAFT_ID';
784 l_src_table_name := 'PO_LINE_LOCATIONS_ALL';
785 END IF;
786
787 END IF;
788
789 IF g_debug_stmt THEN
790 po_debug.debug_stmt( p_log_head => g_log_head||l_api_name
791 , p_token => l_progress
792 , p_message => ' Completed Initialization'
793 );
794
795 END IF;
796
797
798 l_progress := '020';
799
800 IF po_autocreate_params.g_interface_source_code='SOURCING'
801 THEN
802
803 l_progress := '030';
804
805 IF p_doc_type = 'OFR' THEN
806
807 l_progress := '040';
808
809 SELECT bid_number
810 INTO x_pk1_value
811 FROM po_lines_interface
812 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
813 AND ROWNUM=1;
814
815 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
816 THEN
817
818 l_progress := '050';
819
820 SELECT bid_line_number INTO x_pk2_value
821 FROM po_lines_interface
822 WHERE interface_header_id = po_autocreate_params.g_interface_header_id
823 AND interface_line_id = p_intf_line_id;
824 END IF;
825
826 ELSIF p_doc_type = 'SOL' THEN
827
828 l_progress := '060';
829
830 SELECT AUCTION_HEADER_ID
831 INTO x_pk1_value
832 FROM pon_bid_headers
833 WHERE BID_NUMBER=
834 (SELECT bid_number
835 FROM po_lines_interface
836 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
837 AND ROWNUM=1);
838
839 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
840 THEN
841
842 l_progress := '070';
843
844 SELECT bid.auction_line_number INTO x_pk2_value
845 FROM po_lines_interface PLI, pon_bid_item_prices bid
846 WHERE 1=1
847 AND PLI.interface_line_id = p_intf_line_id
848 AND bid.bid_number = PLI.bid_number
849 AND bid.line_number = PLI.bid_line_number;
850 END IF;
851
852 ELSIF p_doc_type = 'PR' THEN
853
854 l_progress := '080';
855 SELECT requisition_header_id
856 INTO x_pk1_value
857 FROM po_requisition_lines_all
858 WHERE requisition_line_id =(
859 SELECT requisition_line_id
860 FROM po_lines_interface
861 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
862 AND requisition_line_id IS NOT NULL
863 AND ROWNUM=1);
864
865 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
866 THEN
867 l_progress := '090';
868
869 SELECT requisition_line_id
870 INTO x_pk1_value
871 FROM PO_LINES_INTERFACE
872 WHERE interface_line_id= p_intf_line_id;
873 END IF;
874
875 ELSIF p_doc_type = 'IDV' THEN
876
877 l_progress := '100';
878
879 -- We can get it from po_lines_interface.from_header_id
880 SELECT IDV_HEADER_ID , -1
881 INTO x_pk1_value , x_pk2_value
882 FROM pon_bid_headers
883 WHERE BID_NUMBER=
884 (SELECT bid_number
885 FROM po_lines_interface
886 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
887 AND ROWNUM=1);
888
889 IF x_pk1_value IS NOT NULL THEN
890 l_progress := '105';
891 SELECT style_id INTO l_doc_style
892 FROM po_headers_all
893 WHERE po_header_id = x_pk1_value;
894 END IF;
895
896
897 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
898 THEN
899
900 l_progress := '110';
901 -- We can get it from po_lines_interface.from_line_id
902 SELECT bid.idv_line_id, -1 INTO x_pk1_value, x_pk2_value
903 FROM po_lines_interface PLI, pon_bid_item_prices bid
904 WHERE 1=1
905 AND PLI.interface_line_id = p_intf_line_id
906 AND bid.bid_number = PLI.bid_number
907 AND bid.line_number = PLI.bid_line_number;
908 END IF;
909
910 END IF;
911 END IF; -- po_autocreate_params.g_interface_source_code='SOURCING'
912
913 IF po_autocreate_params.g_interface_source_code='PO' THEN
914 l_progress := '120';
915
916 IF p_doc_type = 'PR' THEN
917
918 l_progress := '130';
919
920 IF p_doc_level='HEADER' THEN
921 l_progress := '135';
922 SELECT requisition_header_id
923 INTO x_pk1_value
924 FROM po_requisition_lines_all
925 WHERE requisition_line_id =( SELECT requisition_line_id
926 FROM PO_LINES_INTERFACE
927 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
928 AND ROWNUM=1);
929 END IF;
930
931 IF p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
932 THEN
933 l_progress := '140';
934 SELECT requisition_line_id INTO x_pk1_value
935 FROM PO_LINES_INTERFACE
936 WHERE interface_line_id= p_intf_line_id;
937 END IF;
938
939 -- If the requisition referencing a Agreement (IDV)
940 ELSIF p_doc_type = 'IDV' THEN
941
942 l_progress := '150';
943
944 --<Bug 16310381> : from_header_id in case of Blanket & contract_id in case of Contract
945 SELECT Nvl(from_header_id,contract_id), -1 INTO x_pk1_value, x_pk2_value
946 FROM po_lines_interface
947 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
948 AND (from_header_id is not NULL OR contract_id IS NOT NULL)
949 AND ROWNUM=1;
950
951 IF x_pk1_value IS NOT NULL THEN
952 l_progress := '160';
953 SELECT style_id INTO l_doc_style
954 FROM po_headers_all
955 WHERE po_header_id = x_pk1_value;
956 END IF;
957
958 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
959 THEN
960 l_progress := '170';
961
962 SELECT from_line_id,-1 INTO x_pk1_value, x_pk2_value
963 FROM po_lines_interface
964 WHERE interface_line_id= p_intf_line_id;
965 END IF;
966
967 /* Commenting the following code as these cases doesn't arise.
968
969 -- If the requisition referencing a Agreement (IDV) and that IDV is created from a offer
970 ELSIF p_doc_type = 'OFR' THEN
971
972 l_progress := '180';
973
974 SELECT from_header_id INTO x_pk1_value
975 FROM po_lines_interface
976 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
977 AND from_header_id is not null
978 AND ROWNUM=1;
979
980 l_progress := '190';
981
982 SELECT bid_number
983 INTO x_pk1_value
984 FROM pon_bid_headers
985 WHERE po_header_id = x_pk1_value;
986
987
988 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
989 THEN
990 l_progress := '200';
991 SELECT from_line_id INTO x_pk2_value
992 FROM po_lines_interface
993 WHERE interface_line_id= p_intf_line_id;
994
995 l_progress := '210';
996 SELECT line_number
997 INTO x_pk2_value
998 FROM pon_bid_item_prices
999 WHERE line_number = x_pk2_value
1000 AND bid_number = x_pk1_value;
1001 END IF;
1002
1003 -- If the requisition referencing a Agreement (IDV) and that IDV is created from a offer
1004 ELSIF p_doc_type = 'SOL' THEN
1005
1006 l_progress := '220';
1007
1008 SELECT from_header_id INTO x_pk1_value
1009 FROM po_lines_interface
1010 WHERE interface_header_id=po_autocreate_params.g_interface_header_id
1011 AND ROWNUM=1;
1012
1013 l_progress := '230';
1014 SELECT auction_header_id
1015 INTO x_pk1_value
1016 FROM pon_bid_headers
1017 WHERE po_header_id = x_pk1_value;
1018
1019
1020 IF x_pk1_value IS NOT NULL AND p_doc_level='LINE' AND p_intf_line_id IS NOT NULL
1021 THEN
1022 l_progress := '240';
1023 SELECT from_line_id INTO x_pk2_value
1024 FROM po_lines_interface
1025 WHERE interface_line_id= p_intf_line_id;
1026
1027 l_progress := '250';
1028 SELECT auction_line_number
1029 INTO x_pk2_value
1030 FROM pon_bid_item_prices
1031 WHERE line_number = x_pk2_value
1032 AND bid_number = x_pk1_value;
1033 END IF;
1034 */
1035 END IF;
1036 END IF;
1037
1038 IF g_debug_stmt THEN
1039 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1040 , p_token => l_progress
1041 , p_message => ' Completed Getting Primary Key Info'
1042 || x_pk1_col_name ||' : '|| x_pk1_value
1043 || x_pk2_col_name ||' : '|| x_pk2_value
1044 );
1045
1046 END IF;
1047
1048
1049 l_progress := '280';
1050 x_ag_type := get_attribute_group_type(p_doc_level => p_doc_level,
1051 p_doc_type => l_doc_type,
1052 p_doc_style => l_doc_style,
1053 p_func_area => l_functional_area
1054 );
1055
1056 l_progress := '290';
1057 x_ext_table_name := get_tbl_for_ag_type(x_ag_type);
1058
1059 l_progress := '300';
1060
1061 IF x_pk1_col_name IS NOT NULL AND x_pk1_value IS NOT NULL THEN
1062 IF p_doc_type = 'IDV' THEN
1063 x_src_template_id := get_template_id
1064 (p_table_name => l_src_table_name
1065 ,p_pkey1_col_name => x_pk1_col_name
1066 ,p_pkey1_val => x_pk1_value
1067 ,p_pkey2_col_name => NULL
1068 ,p_pkey2_val => NULL
1069 );
1070 ELSE
1071 x_src_template_id := get_template_id
1072 (p_table_name => l_src_table_name
1073 ,p_pkey1_col_name => x_pk1_col_name
1074 ,p_pkey1_val => x_pk1_value
1075 ,p_pkey2_col_name => x_pk2_col_name
1076 ,p_pkey2_val => x_pk2_value
1077 );
1078 END IF;
1079 END IF;
1080
1081 IF g_debug_stmt THEN
1082 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 IF g_debug_unexp THEN
1088 PO_DEBUG.debug_exc (p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1089 END IF;
1090 END get_src_primary_key;
1091
1092 FUNCTION get_ag_id( p_ag_type IN VARCHAR2
1093 ,p_attr_grp IN VARCHAR2)
1094 RETURN NUMBER
1095 IS
1096
1097 l_api_name VARCHAR2(30) := 'get_ag_id';
1098 l_progress VARCHAR2(3) := '000';
1099
1100
1101 l_attr_group_id NUMBER;
1102
1103 BEGIN
1104
1105 IF g_debug_stmt THEN
1106 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1107 END IF;
1108
1109 IF g_debug_stmt THEN
1110 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1111 , p_token => l_progress
1112 , p_message => ' p_ag_type : ' || p_ag_type
1113 || ' p_attr_grp : '|| p_attr_grp );
1114 END IF;
1115
1116 l_progress := '010';
1117
1118 SELECT attr_group_id
1119 INTO l_attr_group_id
1120 FROM EGO_ATTR_GROUPS_V
1121 WHERE ATTR_GROUP_TYPE = p_ag_type
1122 AND ATTR_GROUP_NAME = p_attr_grp;
1123
1124 IF g_debug_stmt THEN
1125 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1126 , p_token => l_progress
1127 , p_message => ' Attribute ID : ' || l_attr_group_id);
1128 END IF;
1129
1130
1131 RETURN l_attr_group_id;
1132
1133
1134 EXCEPTION WHEN OTHERS THEN
1135 IF g_debug_unexp THEN
1136 PO_DEBUG.debug_exc (p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1137 END IF;
1138 RETURN NULL;
1139 END get_ag_id;
1140
1141 FUNCTION get_extension_id
1142 (p_ag_id IN NUMBER,
1143 p_ext_table_name IN VARCHAR2,
1144 p_pk1_col_name IN VARCHAR2,
1145 p_pk1_value IN NUMBER,
1146 p_pk2_col_name IN VARCHAR2,
1147 p_pk2_value IN NUMBER,
1148 p_where_col_name IN VARCHAR2,
1149 p_address_type IN VARCHAR2
1150 )
1151 RETURN NUMBER
1152 IS
1153 l_api_name VARCHAR2(30) := 'get_extension_id';
1154 l_progress VARCHAR2(3) := '000';
1155
1156 l_query VARCHAR2(1500);
1157 l_extension_id NUMBER;
1158
1159 BEGIN
1160 IF g_debug_stmt THEN
1161 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1162 END IF;
1163
1164 IF g_debug_stmt THEN
1165 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1166 , p_token => l_progress
1167 , p_message => ' p_ag_id : ' || p_ag_id
1168 ||' p_ext_table_name : '|| p_ext_table_name
1169 ||' p_pk1_col_name : ' || p_pk1_col_name
1170 ||' p_pk1_value : ' || p_pk1_value
1171 ||' p_pk2_col_name : ' || p_pk2_col_name
1172 ||' p_pk2_value : ' || p_pk2_value
1173 ||' p_where_col_name : '|| p_where_col_name
1174 ||' p_address_type : ' || p_address_type);
1175 END IF;
1176
1177
1178 l_progress := '010';
1179
1180 l_query := 'SELECT extension_id FROM '|| p_ext_table_name
1181 ||' WHERE ATTR_GROUP_ID = ' ||p_ag_id
1182 ||' AND '|| p_pk1_col_name || ' = ' || p_pk1_value
1183 ||' AND '|| p_where_col_name ||' = '|| ''''||p_address_type||''''
1184 --<Bug 16310381> : Pickup only records which have not null location id/code & contact
1185 ||' AND (n_ext_attr1 IS NOT NULL '
1186 ||' OR n_ext_attr2 IS NOT NULL '
1187 ||' OR n_ext_attr3 IS NOT NULL ) ';
1188
1189 IF p_pk2_col_name IS not NULL AND p_pk2_value IS NOT NULL
1190 THEN
1191 l_progress := '020';
1192 l_query := l_query || ' AND '||p_pk2_col_name || ' = ' || p_pk2_value;
1193 END IF;
1194
1195 IF g_debug_stmt THEN
1196 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1197 p_token => l_progress,
1198 p_message => ' Sql to get the extension_id : '||l_query
1199 );
1200
1201 END IF;
1202
1203
1204 l_progress := '030';
1205
1206 EXECUTE IMMEDIATE l_query INTO l_extension_id;
1207
1208
1209 IF g_debug_stmt THEN
1210 PO_DEBUG.debug_stmt (p_log_head => g_log_head||l_api_name,
1211 p_token => l_progress,
1212 p_message => ' Source Extension ID : '||l_extension_id
1213 );
1214
1215 END IF;
1216
1217 RETURN l_extension_id;
1218
1219
1220 IF g_debug_stmt THEN
1221 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1222 END IF;
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 IF g_debug_unexp THEN
1226 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1227 END IF;
1228 RETURN NULL;
1229 END get_extension_id;
1230
1231
1232 FUNCTION check_data_exists
1233 (p_ag_id IN NUMBER,
1234 p_ext_table_name IN VARCHAR2,
1235 p_pk1_col_name IN VARCHAR2,
1236 p_pk1_value IN NUMBER,
1237 p_pk2_col_name IN VARCHAR2,
1238 p_pk2_value IN NUMBER
1239 )
1240 RETURN BOOLEAN
1241 IS
1242 l_api_name VARCHAR2(30) := 'check_data_exists';
1243 l_progress VARCHAR2(3) := '000';
1244
1245 l_query VARCHAR2(1500);
1246 l_count NUMBER;
1247
1248 BEGIN
1249 IF g_debug_stmt THEN
1250 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1251 END IF;
1252
1253 IF g_debug_stmt THEN
1254 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1255 , p_token => l_progress
1256 , p_message => ' p_ag_id : ' || p_ag_id
1257 ||' p_ext_table_name : '|| p_ext_table_name
1258 ||' p_pk1_col_name : ' || p_pk1_col_name
1259 ||' p_pk1_value : ' || p_pk1_value
1260 ||' p_pk2_col_name : ' || p_pk2_col_name
1261 ||' p_pk2_value : ' || p_pk2_value);
1262 END IF;
1263
1264
1265 l_progress := '010';
1266
1267
1268 --12611018 set the global values.
1269 g_pk1_value := p_pk1_value;
1270 g_pk2_value := p_pk2_value;
1271
1272 l_query := 'select count(1) from '|| p_ext_table_name
1273 ||' WHERE ATTR_GROUP_ID = ' || p_ag_id
1274 ||' AND '|| p_pk1_col_name || ' = PO_UDA_AUTOCREATE_PKG.get_pk1_value()';
1275 --12611018 added get_pk1_value
1276 --instead OF USING p_pk1_value
1277 -- the call to function get_pk1_value should be as a part of the string
1278 -- so that the value gets picked up as bind variables.
1279
1280 IF p_pk2_col_name IS not NULL AND p_pk2_value IS NOT NULL
1281 THEN
1282 l_progress := '020';
1283 l_query := l_query || ' AND '||p_pk2_col_name || ' = PO_UDA_AUTOCREATE_PKG.get_pk2_value()'; --12611018 added get_pk2_value
1284 --instead OF USING p_pk2_value
1285 -- the call to function get_pk1_value should be as a part of the string
1286 -- so that the value gets picked up as bind variables.
1287 END IF;
1288
1289 IF g_debug_stmt THEN
1290 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1291 p_token => l_progress,
1292 p_message => ' Sql to get Data Exists or not : '||l_query
1293 );
1294
1295 END IF;
1296
1297
1298 l_progress := '030';
1299 EXECUTE IMMEDIATE l_query INTO l_count;
1300
1301
1302 IF g_debug_stmt THEN
1303 PO_DEBUG.debug_stmt (p_log_head => g_log_head||l_api_name,
1304 p_token => l_progress,
1305 p_message => ' No of Rows : '||l_count
1306 );
1307
1308 END IF;
1309 IF l_count > 0 THEN
1310 RETURN TRUE;
1311 ELSE
1312 RETURN FALSE;
1313 END IF;
1314
1315
1316 IF g_debug_stmt THEN
1317 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1318 END IF;
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 IF g_debug_unexp THEN
1322 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1323 END IF;
1324 RETURN FALSE;
1325 END check_data_exists;
1326
1327 PROCEDURE autocreate_attr_group (p_group_type IN VARCHAR2
1328 ,p_group_property IN VARCHAR2
1329 ,p_src_doc_level IN VARCHAR2
1330 ,p_intf_line_id IN NUMBER
1331 ,p_attr_group IN VARCHAR2
1332 ,p_to_pk1_col_name IN VARCHAR2
1333 ,p_to_pk1_value IN NUMBER
1334 ,p_priorities_tbl IN PO_TBL_VARCHAR3
1335 )
1336
1337 IS
1338 l_api_name VARCHAR2(30) := 'autocreate_attr_group';
1339 l_progress VARCHAR2(3) := '000';
1340
1341 x_source_pkey_tbl po_tbl_number;
1342 x_target_pkey_tbl po_tbl_number;
1343 x_pkey_tbl po_tbl_number;
1344
1345 l_attr_group_tbl po_tbl_varchar30;
1346 x_return_status VARCHAR2(1);
1347 x_msg_count NUMBER;
1348 x_msg_data VARCHAR2(2000);
1349
1350 from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1351 to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1352
1353 from_template_id NUMBER;
1354 to_template_id NUMBER;
1355
1356 x_ag_type VARCHAR2(400);
1357 x_ext_table_name VARCHAR2(200);
1358 x_pk1_col_name VARCHAR2(100);
1359 x_pk1_value NUMBER;
1360 x_pk2_col_name VARCHAR2(100);
1361 x_pk2_value NUMBER;
1362 l_ag_id NUMBER;
1363 x_src_template_id NUMBER;
1364
1365 l_extension_id NUMBER;
1366
1367 BEGIN
1368
1369 IF g_debug_stmt THEN
1370 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1371 END IF;
1372
1373
1374 l_progress := '010';
1375
1376 SELECT p_attr_group
1377 BULK COLLECT INTO l_attr_group_tbl
1378 FROM dual;
1379
1380 FOR i IN 1.. p_priorities_tbl.Count
1381 LOOP
1382 IF g_debug_stmt THEN
1383 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1384 , p_token => l_progress
1385 , p_message => ' p_priorities_tbl(i) : ' || p_priorities_tbl(i)
1386 );
1387 END IF;
1388
1389 x_ag_type := NULL;
1390 x_ext_table_name := NULL;
1391 x_pk1_col_name := NULL;
1392 x_pk1_value := NULL;
1393 x_pk2_col_name := NULL;
1394 x_pk2_value := NULL;
1395 x_src_template_id := NULL;
1396 l_extension_id := NULL;
1397 x_return_status :=NULL;
1398
1399
1400 IF p_priorities_tbl(i) = 'SYS' AND
1401 p_attr_group = 'addresses' AND
1402 p_group_type = 'USAGE'
1403 THEN
1404 l_progress := '020';
1405 auto_default_sys_addresses (p_address_type => p_group_property,x_return_status=>x_return_status);
1406
1407 IF x_return_status = 'S' THEN
1408 EXIT;
1409 END IF;
1410 ELSE
1411
1412 l_progress := '030';
1413 get_src_primary_key(
1414 p_doc_level => p_src_doc_level,
1415 p_doc_type => p_priorities_tbl(i),
1416 p_intf_line_id => p_intf_line_id,
1417 x_ag_type => x_ag_type,
1418 x_src_template_id => x_src_template_id,
1419 x_ext_table_name => x_ext_table_name,
1420 x_pk1_col_name => x_pk1_col_name,
1421 x_pk1_value => x_pk1_value,
1422 x_pk2_col_name => x_pk2_col_name,
1423 x_pk2_value => x_pk2_value
1424 );
1425 l_progress := '040';
1426 l_ag_id := get_ag_id(p_ag_type => x_ag_type, p_attr_grp=> p_attr_group);
1427
1428
1429 IF x_pk1_col_name IS NOT NULL
1430 AND x_pk1_value IS NOT NULL
1431 AND x_ext_table_name IS NOT NULL
1432 AND x_src_template_id IS NOT NULL
1433 AND l_ag_id IS NOT NULL
1434 THEN
1435 l_progress := '050';
1436
1437 IF x_pk2_col_name IS NOT NULL AND x_pk2_value IS NOT NULL
1438 THEN
1439
1440 from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1441 EGO_COL_NAME_VALUE_PAIR_OBJ(
1442 x_pk1_col_name, x_pk1_value
1443 ),
1444 EGO_COL_NAME_VALUE_PAIR_OBJ(
1445 x_pk2_col_name, x_pk2_value
1446 ));
1447
1448 ELSE
1449 from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1450 EGO_COL_NAME_VALUE_PAIR_OBJ(
1451 x_pk1_col_name, x_pk1_value
1452 ));
1453 END IF;
1454
1455 to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1456 EGO_COL_NAME_VALUE_PAIR_OBJ(
1457 p_to_pk1_col_name,p_to_pk1_value
1458 ),
1459 EGO_COL_NAME_VALUE_PAIR_OBJ(
1460 'DRAFT_ID',g_draft_id));
1461
1462 l_progress := '060';
1463
1464 IF p_attr_group = 'addresses'
1465 THEN
1466 l_extension_id := get_extension_id
1467 (p_ag_id => l_ag_id,
1468 p_ext_table_name =>x_ext_table_name,
1469 p_pk1_col_name => x_pk1_col_name,
1470 p_pk1_value =>x_pk1_value,
1471 p_pk2_col_name =>x_pk2_col_name,
1472 p_pk2_value =>x_pk2_value,
1473 p_where_col_name => 'C_EXT_ATTR39', --<Bug 16310381>
1474 p_address_type => p_group_property
1475 );
1476 IF l_extension_id IS NOT NULL THEN
1477 process_addresses(p_ag_type => x_ag_type
1478 ,p_src_pk_col_name_val_pairs => from_pk_col_value_pairs
1479 ,p_src_uda_template_id => x_src_template_id
1480 ,p_attr_group_id => l_ag_id
1481 ,p_address_type => p_group_property
1482 ,p_extension_id => l_extension_id
1483 );
1484 EXIT;
1485 END IF;
1486 ELSE
1487
1488 IF check_data_exists
1489 (p_ag_id => l_ag_id,
1490 p_ext_table_name => x_ext_table_name,
1491 p_pk1_col_name => x_pk1_col_name,
1492 p_pk1_value => x_pk1_value,
1493 p_pk2_col_name => x_pk2_col_name,
1494 p_pk2_value => x_pk2_value
1495 )
1496 THEN
1497 -- Not usre whether we need to update po_headers_all with the new template id
1498 -- In case of add mode/new mode
1499 l_progress := '070';
1500
1501 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
1502 (
1503 from_template_id => x_src_template_id
1504 ,to_template_id => g_target_uda_template_id
1505 ,from_pk_col_value_pairs => from_pk_col_value_pairs
1506 ,to_pk_col_value_pairs => to_pk_col_value_pairs
1507 ,copy_attribute_groups => 'SPECIFIC'
1508 ,attribute_group_table => l_attr_group_tbl
1509 ,p_commit => fnd_api.g_false
1510 ,x_return_status => x_return_status
1511 ,x_msg_count => x_msg_count
1512 ,x_msg_data => x_msg_data
1513 );
1514
1515 IF g_debug_stmt THEN
1516 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1517 , p_token => l_progress
1518 , p_message => ' After Calling PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
1519 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
1520 ||' Message Data: '|| x_msg_data
1521 );
1522 END IF;
1523
1524 EXIT;
1525 END IF;
1526 END IF;
1527 END IF;
1528 END IF;
1529 END LOOP;
1530
1531 IF g_debug_stmt THEN
1532 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1533 END IF;
1534
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 IF g_debug_unexp THEN
1538 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1539 END IF;
1540 END autocreate_attr_group;
1541
1542
1543 PROCEDURE autocreate_ag_header (p_src_doc_level IN VARCHAR2
1544 ,p_attr_group IN VARCHAR2
1545 ,p_priorities_tbl IN PO_TBL_VARCHAR3
1546 ,p_group_type IN VARCHAR2
1547 ,p_group_property IN VARCHAR2
1548 )
1549 IS
1550 l_api_name VARCHAR2(30) := 'autocreate_ag_header';
1551 l_progress VARCHAR2(3) := '000';
1552
1553 BEGIN
1554
1555 IF g_debug_stmt THEN
1556 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1557 END IF;
1558
1559 autocreate_attr_group (p_group_type => p_group_type
1560 ,p_group_property => p_group_property
1561 ,p_src_doc_level => p_src_doc_level
1562 ,p_intf_line_id => NULL
1563 ,p_attr_group => p_attr_group
1564 ,p_to_pk1_col_name => 'PO_HEADER_ID'
1565 ,p_to_pk1_value => po_autocreate_params.g_po_header_id
1566 ,p_priorities_tbl => p_priorities_tbl
1567 );
1568
1569
1570
1571 IF g_debug_stmt THEN
1572 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1573 END IF;
1574
1575
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 IF g_debug_unexp THEN
1579 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1580 END IF;
1581 END autocreate_ag_header;
1582
1583
1584 PROCEDURE autocreate_ag_lines(p_src_doc_level IN VARCHAR2
1585 ,p_attr_group IN VARCHAR2
1586 ,p_priorities_tbl IN PO_TBL_VARCHAR3
1587 ,p_group_type IN VARCHAR2
1588 ,p_group_property IN VARCHAR2
1589 )
1590 IS
1591 l_api_name VARCHAR2(30) := 'autocreate_ag_lines';
1592 l_progress VARCHAR2(3) := '000';
1593
1594 CURSOR cur_lines
1595 IS
1596 SELECT interface_line_id, po_line_id
1597 FROM (SELECT interface_line_id , po_line_id,
1598 Rank() over(partition BY PLI.line_num order by PLI.line_num,
1599 pli.shipment_num,
1600 pli.unit_price,
1601 PLI.interface_line_id) rn
1602 FROM po_lines_interface pli
1603 WHERE pli.interface_header_id = po_autocreate_params.g_interface_header_id
1604 /*Bug : 13561015
1605 Introducing an NVL condition which take NULL value(coming from SOURCING) in action
1606 as NEW action.
1607 */
1608 AND Nvl(pli.action,'NEW') = 'NEW'
1609 )
1610 WHERE rn =1;
1611 --When lines are grouped we have to pick the first line for defaulting UDA.
1612 --Ordering should be done on the bases of line_num, shipment_num, unit_price,
1613 --interface_line_id
1614 --If Lines are being added to existing PO lines no processing is required.
1615
1616 BEGIN
1617
1618 IF g_debug_stmt THEN
1619 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1620 END IF;
1621
1622 FOR l_rec IN cur_lines
1623 LOOP
1624 autocreate_attr_group (p_group_type => p_group_type
1625 ,p_group_property => p_group_property
1626 ,p_src_doc_level => p_src_doc_level
1627 ,p_intf_line_id => l_rec.interface_line_id
1628 ,p_attr_group => p_attr_group
1629 ,p_to_pk1_col_name => 'PO_LINE_ID'
1630 ,p_to_pk1_value => l_rec.po_line_id
1631 ,p_priorities_tbl => p_priorities_tbl
1632 );
1633 END LOOP;
1634
1635 IF g_debug_stmt THEN
1636 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1637 END IF;
1638
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 IF g_debug_unexp THEN
1642 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1643 END IF;
1644 END autocreate_ag_lines;
1645
1646
1647 PROCEDURE autocreate_ag_shipments(p_src_doc_level IN VARCHAR2
1648 ,p_attr_group IN VARCHAR2
1649 ,p_priorities_tbl IN PO_TBL_VARCHAR3
1650 ,p_group_type IN VARCHAR2
1651 ,p_group_property IN VARCHAR2
1652 )
1653 IS
1654 l_api_name VARCHAR2(30) := 'autocreate_ag_shipments';
1655 l_progress VARCHAR2(3) := '000';
1656
1657
1658 CURSOR cur_ship_lines
1659 IS
1660 SELECT interface_line_id,
1661 line_location_id
1662 FROM po_line_locations_draft_all poll,
1663 (SELECT interface_line_id interface_line_id,
1664 po_line_id po_line_id,
1665 shipment_num shipment_num
1666 FROM (SELECT interface_line_id,
1667 PLI.po_line_id,
1668 PLI.shipment_num,
1669 Rank() over(PARTITION BY pli.line_num, pli.shipment_num
1670 ORDER BY pli.line_num,
1671 pli.shipment_num,
1672 pli.unit_price,
1673 pli.interface_line_id) rn
1674 FROM po_lines_interface PLI
1675 WHERE pli.interface_header_id = po_autocreate_params.g_interface_header_id
1676 /*Bug : 13561015
1677 Introducing an NVL condition which take NULL value(coming from SOURCING) in action
1678 as NEW action.
1679 */
1680 AND Nvl(pli.action,'NEW') = 'NEW')
1681 WHERE rn = 1) intf_lines
1682 WHERE poll.po_line_id = intf_lines.po_line_id
1683 AND poll.shipment_num = intf_lines.shipment_num
1684 AND poll.po_header_id = po_autocreate_params.g_po_header_id
1685 AND draft_id = po_autocreate_params.g_draft_id;
1686 --When shipments are grouped we have to pick the first shipment for defaulting UDA.
1687 --Ordering should be done on the bases of line_num, shipment_num, unit_price,
1688 --interface_line_id
1689 --If shipments are being added to existing PO shipment no processing is required.
1690
1691 BEGIN
1692
1693 IF g_debug_stmt THEN
1694 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1695 END IF;
1696
1697 FOR l_rec IN cur_ship_lines
1698 LOOP
1699 autocreate_attr_group (p_group_type => p_group_type
1700 ,p_group_property => p_group_property
1701 ,p_src_doc_level => p_src_doc_level
1702 ,p_intf_line_id => l_rec.interface_line_id
1703 ,p_attr_group => p_attr_group
1704 ,p_to_pk1_col_name => 'LINE_LOCATION_ID'
1705 ,p_to_pk1_value => l_rec.line_location_id
1706 ,p_priorities_tbl => p_priorities_tbl
1707 );
1708 END LOOP;
1709
1710 IF g_debug_stmt THEN
1711 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 IF g_debug_unexp THEN
1717 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1718 END IF;
1719 END autocreate_ag_shipments;
1720
1721
1722
1723 PROCEDURE autocreate_ag_level ( p_doc_level IN VARCHAR2
1724 ,p_src_doc_level IN VARCHAR2
1725 ,p_attr_group IN VARCHAR2
1726 ,p_priorities_tbl IN PO_TBL_VARCHAR3
1727 ,p_group_type IN VARCHAR2
1728 ,p_group_property IN VARCHAR2
1729 )
1730 IS
1731 l_api_name VARCHAR2(30) := 'autocreate_ag_level';
1732 l_progress VARCHAR2(3) := '000';
1733 BEGIN
1734
1735 IF g_debug_stmt THEN
1736 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1737 END IF;
1738
1739
1740 IF p_doc_level = 'HEADER'
1741 AND ( po_autocreate_params.g_mode = 'NEW'
1742 /*bug 12611018 starts
1743 OR (po_autocreate_params.g_mode='ADD'
1744 AND NOT po_autocreate_params.g_is_mod_exists
1745 ) bug 12611018 ends */
1746 )
1747 THEN
1748 l_progress := '010';
1749 autocreate_ag_header(p_src_doc_level => p_src_doc_level
1750 ,p_attr_group => p_attr_group
1751 ,p_priorities_tbl=> p_priorities_tbl
1752 ,p_group_type => p_group_type
1753 ,p_group_property => p_group_property
1754 );
1755
1756 ELSIF p_doc_level = 'LINE' THEN
1757 l_progress := '020';
1758 autocreate_ag_lines(p_src_doc_level => p_src_doc_level
1759 ,p_attr_group => p_attr_group
1760 ,p_priorities_tbl => p_priorities_tbl
1761 ,p_group_type => p_group_type
1762 ,p_group_property => p_group_property
1763 );
1764
1765 ELSIF p_doc_level = 'SHIPMENT' THEN
1766 l_progress := '030';
1767 autocreate_ag_shipments(p_src_doc_level => p_src_doc_level
1768 ,p_attr_group => p_attr_group
1769 ,p_priorities_tbl => p_priorities_tbl
1770 ,p_group_type => p_group_type
1771 ,p_group_property => p_group_property
1772 );
1773 END IF;
1774
1775 IF g_debug_stmt THEN
1776 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1777 END IF;
1778
1779
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 IF g_debug_unexp THEN
1783 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1784 END IF;
1785 END autocreate_ag_level;
1786
1787 PROCEDURE auto_complexpricing_ag
1788 IS
1789
1790 l_api_name VARCHAR2(30) := 'auto_complexpricing_ag';
1791 l_progress VARCHAR2(3) := '000';
1792
1793 /* Bug 9945827 :While Autocreating an amount based line from requisition or req-sol-award
1794 process, the pricing attributes were not getting copied to the
1795 outcome document.This was because the where condition was checking only
1796 for ORDER_TYPE_LOOKUP_CODE = amount , added condition for Fixed price.*/
1797 CURSOR cur_src_lines
1798 IS
1799 SELECT PLI.interface_line_id,PLI.requisition_line_id,PLI.bid_number,PLI.bid_line_number,PLI.line_num
1800 FROM po_line_types plt, po_lines_interface pli
1801 WHERE plt.line_type_id = pli.line_type_id
1802 AND plt.ORDER_TYPE_LOOKUP_CODE IN ('AMOUNT','FIXED PRICE')--Bug 9945827
1803 AND PLI.interface_header_id = po_autocreate_params.g_interface_header_id
1804 --CLM Phase 2 Autocreate Changes start
1805 --Bug 13612198 : Comlpex pricing attributes were not copied.
1806 /*Bug : 13561015
1807 Introducing an NVL condition which take NULL value(coming from SOURCING) in action
1808 as NEW action.
1809 */
1810 and Nvl(pli.action,'NEW') = 'NEW'
1811 AND line_num = (SELECT line_num
1812 FROM po_lines_interface
1813 WHERE interface_line_id = PLI.interface_line_id
1814 GROUP BY line_num
1815 HAVING Count(line_num) = 1 ) ;
1816 --CLM Phase 2 Autocreate Changes start
1817 --For Complex Pricing Attributes, If lines are grouped then the PO line should have
1818 --no UDA defaulted.
1819
1820 CURSOR cur_target_lines(p_intf_line_id NUMBER)
1821 IS
1822 SELECT pla.po_line_id
1823 FROM po_lines_interface PLI,po_lines_draft_all pla
1824 WHERE PLI.interface_header_id = po_autocreate_params.g_interface_header_id
1825 AND PLI.interface_line_id = p_intf_line_id
1826 AND PLI.line_num = pla.line_num
1827 AND pla.draft_id = po_autocreate_params.g_draft_id;
1828
1829 l_src_template_id NUMBER;
1830 l_target_template_id NUMBER;
1831 from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1832 to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1833
1834 l_to_pk1_value NUMBER;
1835 l_attr_group_tbl PO_TBL_VARCHAR30;
1836
1837 x_return_status VARCHAR2(1);
1838 x_msg_count NUMBER;
1839 x_msg_data VARCHAR2(1000);
1840
1841
1842 BEGIN
1843
1844 IF g_debug_stmt THEN
1845 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1846 END IF;
1847
1848 l_progress := '020';
1849 l_target_template_id := get_uda_template_id
1850 (p_functional_area => g_target_func_area
1851 ,p_doc_type => g_target_doc_type
1852 ,p_doc_level => 'LINE'
1853 ,p_doc_style_id => g_target_doc_style
1854 );
1855
1856 IF g_debug_stmt THEN
1857 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1858 ,p_token => l_progress
1859 ,p_message => 'Target Tempalte ID :' || l_target_template_id
1860 );
1861 END IF;
1862
1863 FOR l_src_rec IN cur_src_lines
1864 LOOP
1865
1866 IF po_autocreate_params.g_interface_source_code='SOURCING' THEN
1867 l_progress := '040';
1868 l_src_template_id := get_template_id ('PON_BID_ITEM_PRICES'
1869 ,'BID_NUMBER'
1870 ,l_src_rec.bid_number
1871 ,'LINE_NUMBER'
1872 ,l_src_rec.bid_line_number);
1873
1874 IF g_debug_stmt THEN
1875 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1876 ,p_token => l_progress
1877 ,p_message => 'l_src_template_id' || l_src_template_id
1878 );
1879 END IF;
1880
1881 from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
1882 'BID_NUMBER', l_src_rec.bid_number
1883 ),
1884 EGO_COL_NAME_VALUE_PAIR_OBJ(
1885 'LINE_NUMBER', l_src_rec.bid_line_number
1886 )
1887 );
1888 IF g_debug_stmt THEN
1889 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1890 ,p_token => l_progress
1891 ,p_message => 'BID_NUMBER : ' || l_src_rec.bid_number || 'LINE NUMBER : ' || l_src_rec.bid_line_number
1892 );
1893 END IF;
1894
1895 l_progress := '060';
1896 SELECT ag.attr_group_name
1897 BULK COLLECT INTO l_attr_group_tbl
1898 FROM pon_bid_item_prices_ext_b bid
1899 ,ego_attr_groups_v ag
1900 ,po_uda_ag_template_usages tu
1901 WHERE bid.bid_number = l_src_rec.bid_number
1902 AND bid.line_number = l_src_rec.bid_line_number
1903 AND bid.attr_group_id = ag.attr_group_id
1904 AND ag.attr_group_id = tu.attribute_group_id
1905 AND tu.template_id = l_src_template_id
1906 AND tu.attribute_category = 'PRICING';
1907
1908 IF g_debug_stmt THEN
1909 po_debug.debug_var(p_log_head => g_log_head||l_api_name
1910 ,p_progress => l_progress
1911 ,p_name => 'l_attr_group_tbl'
1912 ,p_value => l_attr_group_tbl
1913 );
1914 END IF;
1915
1916 ELSE
1917 l_progress := '080';
1918 l_src_template_id := get_template_id ('PO_REQUISITION_LINES_ALL'
1919 ,'REQUISITION_LINE_ID'
1920 ,l_src_rec.requisition_line_id
1921 ,NULL,
1922 NULL);
1923
1924 IF g_debug_stmt THEN
1925 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1926 ,p_token => l_progress
1927 ,p_message => 'l_src_template_id' || l_src_template_id
1928 );
1929 END IF;
1930
1931 from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1932 EGO_COL_NAME_VALUE_PAIR_OBJ(
1933 'REQUISITION_LINE_ID',
1934 l_src_rec.requisition_line_id
1935 )
1936 );
1937 IF g_debug_stmt THEN
1938 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
1939 ,p_token => l_progress
1940 ,p_message => 'REQUISITION_LINE_ID : ' || l_src_rec.requisition_line_id
1941 );
1942 END IF;
1943
1944 l_progress := '100';
1945 SELECT ag.attr_group_name
1946 BULK COLLECT INTO l_attr_group_tbl
1947 FROM po_req_lines_ext_b req
1948 ,ego_attr_groups_v ag
1949 ,po_uda_ag_template_usages tu
1950 WHERE req.requisition_line_id = l_src_rec.requisition_line_id
1951 AND req.attr_group_id = ag.attr_group_id
1952 AND ag.attr_group_id = tu.attribute_group_id
1953 AND tu.template_id = l_src_template_id
1954 AND tu.attribute_category = 'PRICING';
1955
1956 IF g_debug_stmt THEN
1957 po_debug.debug_var(p_log_head => g_log_head||l_api_name
1958 ,p_progress => l_progress
1959 ,p_name => 'l_attr_group_tbl'
1960 ,p_value => l_attr_group_tbl
1961 );
1962 END IF;
1963
1964 END IF;
1965
1966 l_progress := '120';
1967 OPEN cur_target_lines(l_src_rec.interface_line_id);
1968 FETCH cur_target_lines INTO l_to_pk1_value;
1969 CLOSE cur_target_lines;
1970
1971 IF g_debug_stmt THEN
1972 po_debug.debug_var(p_log_head => g_log_head||l_api_name
1973 ,p_progress => l_progress
1974 ,p_name => 'l_to_pk1_value'
1975 ,p_value => l_to_pk1_value
1976 );
1977 END IF;
1978
1979
1980
1981 to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1982 EGO_COL_NAME_VALUE_PAIR_OBJ(
1983 'PO_LINE_ID',l_to_pk1_value
1984 ),
1985 EGO_COL_NAME_VALUE_PAIR_OBJ(
1986 'DRAFT_ID',g_draft_id));
1987
1988
1989 IF l_attr_group_tbl.Count > 0 THEN
1990
1991 l_progress := '140';
1992 po_uda_data_util.autocreate_user_attrs
1993 (
1994 from_template_id => l_src_template_id
1995 ,to_template_id => l_target_template_id
1996 ,from_pk_col_value_pairs => from_pk_col_value_pairs
1997 ,to_pk_col_value_pairs => to_pk_col_value_pairs
1998 ,copy_attribute_groups => 'SPECIFIC'
1999 ,attribute_group_table => l_attr_group_tbl
2000 ,p_commit => fnd_api.g_false
2001 ,x_return_status => x_return_status
2002 ,x_msg_count => x_msg_count
2003 ,x_msg_data => x_msg_data
2004 );
2005 IF g_debug_stmt THEN
2006 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2007 , p_token => l_progress
2008 , p_message => ' After Calling PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
2009 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
2010 ||' Message Data: '|| x_msg_data
2011 );
2012 END IF;
2013
2014 -- CLM UCA Project: Update clm_uda_pricing_total with the CP Total
2015 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2016 UPDATE po_lines_draft_all
2017 SET clm_uda_pricing_total = Decode(matching_basis, 'QUANTITY', quantity, amount)
2018 WHERE po_line_id = l_to_pk1_value
2019 AND draft_id = po_autocreate_params.g_draft_id
2020 AND order_type_lookup_code IN ('AMOUNT','FIXED PRICE');
2021 END IF;
2022
2023 END IF;
2024 END LOOP;
2025
2026 IF g_debug_stmt THEN
2027 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2028 END IF;
2029
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 IF g_debug_unexp THEN
2033 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2034 END IF;
2035 RAISE;
2036 END auto_complexpricing_ag;
2037
2038 /*
2039 Addresses are always defined at header level
2040 */
2041
2042 PROCEDURE auto_default_sys_addresses (p_address_type IN VARCHAR2,x_return_status OUT NOCOPY VARCHAR2)
2043 IS
2044
2045 l_api_name VARCHAR2(30) := 'auto_default_sys_addresses';
2046 l_progress VARCHAR2(3) := '000';
2047
2048 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2049
2050
2051
2052 x_errorcode NUMBER;
2053 x_msg_count NUMBER;
2054 x_msg_data VARCHAR2(1000);
2055
2056 x_mode VARCHAR2(1000);
2057 x_failed_row_id_list VARCHAR2(1000);
2058
2059
2060 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
2061 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
2062 x_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
2063
2064 l_po_uda_usage_object_array PO_UDA_USAGE_OBJECT_ARRAY;
2065 l_po_uda_usage_object PO_UDA_USAGE_OBJECT;
2066 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
2067
2068 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2069
2070 l_address_lookup_type varchar2(100);
2071
2072 l_attr_grp_ids_list po_tbl_NUMBER;
2073
2074 l_row_identifier NUMBER;
2075
2076 l_attr_group_type VARCHAR2(100) := 'PO_HEADER_EXT_ATTRS';
2077 l_attr_group_name VARCHAR2(100) := 'addresses';
2078
2079 l_attr_group_id NUMBER;
2080
2081 l_attr_name varchar2(100);
2082 l_attr_col varchar2(100);
2083 l_attr_val varchar2(100);
2084 l_location_value NUMBER; --<Bug 15928376>
2085
2086 CURSOR cur_uda_attributes
2087 (
2088 p_attr_grp_type VARCHAR2
2089 ,p_attr_grp_name VARCHAR2
2090 )IS
2091 SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
2092 FROM FND_DESCR_FLEX_COLUMN_USAGES
2093 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
2094 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
2095
2096 CURSOR cur_address
2097 (
2098 p_address_lookup_type VARCHAR2
2099 )IS
2100 SELECT DISTINCT LOOKUP_CODE LOOKUP_CODE
2101 FROM FND_LOOKUP_VALUES
2102 WHERE LOOKUP_TYPE = l_address_lookup_type
2103 AND LOOKUP_CODE = p_address_type;
2104
2105
2106 l_user_row_identifier NUMBER;
2107 l_address VARCHAR2(100);
2108
2109 x_extension_id NUMBER;
2110 BEGIN
2111
2112
2113 IF g_debug_stmt THEN
2114 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2115 END IF;
2116
2117 IF g_debug_stmt THEN
2118 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name
2119 ,p_token => l_progress
2120 , p_message => ' p_address_type : ' || p_address_type
2121 );
2122 END IF;
2123
2124
2125 l_progress := '010';
2126
2127 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
2128 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
2129
2130 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
2131 (
2132 EGO_COL_NAME_VALUE_PAIR_OBJ
2133 (
2134 'UDA_TEMPLATE_ID'
2135 ,g_tar_header_uda_template_id || ''
2136 )
2137 );
2138
2139 x_external_attr_value_pairs := EGO_COL_NAME_VALUE_PAIR_TABLE();
2140 x_external_attr_value_pairs.extend(4);
2141 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, x_external_attr_value_pairs);
2142 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', x_external_attr_value_pairs);
2143 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', x_external_attr_value_pairs);
2144 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', x_external_attr_value_pairs);
2145
2146
2147 l_po_uda_usage_object := PO_UDA_USAGE_OBJECT();
2148 l_po_uda_usage_object_array := PO_UDA_USAGE_OBJECT_ARRAY();
2149 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2150
2151 l_po_uda_usage_object := po_uda_usage_object.new_instance(g_tar_header_uda_template_id,'ADDRESS',l_context );
2152 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
2153 l_attr_group_id := l_attr_grp_ids_list(1);
2154
2155 IF g_debug_stmt THEN
2156 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name
2157 ,p_token => l_progress
2158 ,p_message => ' l_attr_group_id : ' || l_attr_group_id
2159 );
2160 END IF;
2161
2162
2163 IF ( g_draft_id = -1 ) THEN
2164 l_address_lookup_type := 'PO_UDA_ADDRESS_TYPES';
2165 ELSE
2166 l_address_lookup_type := 'PO_MOD_UDA_ADDRESS_TYPES' ;
2167 END IF;
2168
2169
2170 l_progress := '030';
2171 OPEN cur_address(l_address_lookup_type);
2172 FETCH cur_address INTO l_address;
2173 CLOSE cur_address;
2174
2175 IF g_debug_stmt THEN
2176 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name
2177 ,p_token => l_progress
2178 ,p_message => ' l_address : ' || l_address
2179 );
2180 END IF;
2181
2182 l_progress := '050';
2183 SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
2184 INTO l_row_identifier
2185 FROM DUAL;
2186
2187 l_attributes_row_table.EXTEND(1);
2188 l_attributes_row_table(1) := ego_user_attr_row_obj
2189 (
2190 l_row_identifier
2191 ,l_attr_group_id
2192 ,201
2193 ,l_attr_group_type
2194 ,l_attr_group_name
2195 ,'PO_HEADER'
2196 ,NULL
2197 ,NULL
2198 ,NULL
2199 ,NULL
2200 ,NULL
2201 ,'SYNC'
2202 );
2203
2204 l_progress := '070';
2205
2206 OPEN cur_uda_attributes (l_attr_group_type, l_attr_group_name);
2207 LOOP
2208
2209 l_progress := '090';
2210 FETCH cur_uda_attributes
2211 INTO l_attr_name, l_attr_col;
2212 EXIT WHEN cur_uda_attributes%NOTFOUND;
2213
2214 l_progress := '110';
2215 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
2216 INTO l_user_row_identifier
2217 FROM DUAL;
2218
2219 l_attr_name_value_pairs.EXTEND(1);
2220
2221 IF l_attr_col = 'C_EXT_ATTR39' THEN
2222 l_attr_val := l_address;
2223 ELSIF l_attr_col = 'C_EXT_ATTR40' THEN
2224 l_attr_val := l_address_lookup_type;
2225 ELSE
2226 l_attr_val := NULL;
2227 END IF;
2228
2229 l_attr_name_value_pairs(l_attr_name_value_pairs.COUNT) := ego_user_attr_data_obj
2230 (
2231 l_row_identifier
2232 ,l_attr_name
2233 ,l_attr_val
2234 ,NULL
2235 ,NULL
2236 ,NULL
2237 ,NULL
2238 ,l_user_row_identifier
2239 );
2240
2241
2242
2243 END LOOP;
2244 CLOSE cur_uda_attributes;
2245
2246 l_progress := '130';
2247 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION(
2248 p_template_id => g_tar_header_uda_template_id
2249 ,p_event => 'XD'
2250 ,x_external_attr_value_pairs => x_external_attr_value_pairs
2251 ,x_pk_column_name_value_pairs => g_tar_head_col_name_val_pairs
2252 ,x_attr_name_value_pairs => l_attr_name_value_pairs
2253 ,x_attributes_row_table => l_attributes_row_table
2254 ,x_return_status => x_return_status
2255 ,x_errorcode => x_errorcode
2256 ,x_msg_count => x_msg_count
2257 ,x_msg_data => x_msg_data
2258 );
2259
2260 IF g_debug_stmt THEN
2261 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name
2262 ,p_token => l_progress
2263 ,p_message => ' Call to EXECUTE_IMPORT_UDA_FUNCTION : x_return_status '
2264 || x_return_status
2265 );
2266 END IF;
2267
2268 IF g_debug_stmt THEN
2269 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name
2270 ,p_token => l_progress
2271 ,p_message => ' l_attr_name_value_pairs.count '
2272 || l_attr_name_value_pairs. Count
2273 ||' l_attributes_row_table '
2274 || l_attributes_row_table.Count
2275 );
2276 END IF;
2277
2278 IF x_return_status = 'S' AND l_attributes_row_table.Count > 0 AND l_attr_name_value_pairs.Count >0
2279 THEN
2280 l_progress := '150';
2281 ego_user_attrs_data_pvt.process_user_attrs_data (
2282 p_api_version => 1.0
2283 ,p_object_name => PO_UDA_DATA_UTIL.g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_object_name
2284 ,p_attributes_row_table => l_attributes_row_table--from Previous STEP but change the ATTR_GROUP_ID here as for the target
2285 ,p_attributes_data_table => l_attr_name_value_pairs --can be same object got from previous step
2286 ,p_pk_column_name_value_pairs => g_tar_head_col_name_val_pairs
2287 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs -- pass { "UDA_TEMPLATE_ID", 343 }
2288 ,x_extension_id => x_extension_id
2289 ,x_mode => x_mode
2290 ,x_failed_row_id_list => x_failed_row_id_list
2291 ,x_return_status => x_return_status
2292 ,x_errorcode => x_errorcode
2293 ,x_msg_count => x_msg_count
2294 ,x_msg_data => x_msg_data
2295 );
2296 END IF;
2297
2298
2299 IF g_debug_stmt THEN
2300 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2301 , p_token => l_progress
2302 , p_message => ' After Calling ego_user_attrs_data_pvt.process_user_attrs_data
2303 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
2304 ||' Message Data: '|| x_msg_data || ' Error Code : '|| x_errorcode
2305 );
2306 END IF;
2307
2308 --<Bug 15928376 Starts> : Get the value for location attribute
2309 FOR i IN 1..l_attr_name_value_pairs.Count
2310 LOOP
2311 IF l_attr_name_value_pairs(i).attr_name = 'location'
2312 THEN
2313 l_location_value := l_attr_name_value_pairs(i).ATTR_VALUE_NUM;
2314 EXIT;
2315 END IF;
2316 END LOOP;
2317 --<Bug 15928376 Ends>
2318
2319 IF l_attributes_row_table.Count > 0 AND x_return_status = 'S'
2320 --<Bug 15928376>: Return 'F' if there is no value set in preferences for this address
2321 AND l_location_value IS NOT NULL
2322 THEN
2323 x_return_status := 'S' ;
2324 ELSE
2325 x_return_status := 'F' ;
2326 END IF;
2327
2328 IF g_debug_stmt THEN
2329 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2330 END IF;
2331
2332 EXCEPTION
2333 WHEN OTHERS THEN
2334 IF g_debug_unexp THEN
2335 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2336 RAISE;
2337 END IF;
2338 END auto_default_sys_addresses;
2339
2340
2341
2342
2343
2344 /* For address the level is alaways the Header */
2345 PROCEDURE process_addresses(p_ag_type IN VARCHAR2
2346 ,p_src_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2347 ,p_src_uda_template_id IN NUMBER
2348 ,p_attr_group_id IN NUMBER
2349 ,p_address_type IN VARCHAR2
2350 ,p_extension_id IN NUMBER
2351 )
2352 IS
2353
2354
2355 l_api_name VARCHAR2(30) := 'process_addresses';
2356 l_progress VARCHAR2(3) := '000';
2357
2358 x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
2359 l_process_attr_row_tbl EGO_USER_ATTR_ROW_TABLE;
2360
2361 x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
2362 l_process_attr_data_tbl EGO_USER_ATTR_DATA_TABLE;
2363
2364 x_return_status VARCHAR2(1);
2365 x_errorcode NUMBER;
2366 x_msg_count NUMBER;
2367 x_msg_data VARCHAR2(100);
2368 x_extension_id NUMBER;
2369 x_mode VARCHAR2(1000);
2370 x_failed_row_id_list VARCHAR2(1000);
2371
2372 l_attr_list VARCHAR2(2000);
2373
2374 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
2375
2376 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2377
2378 l_tar_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
2379
2380 l_src_data_level VARCHAR2(100);
2381
2382 CURSOR c_attr_list(p_ag_type VARCHAR2) IS
2383 SELECT END_USER_COLUMN_NAME
2384 FROM FND_DESCR_FLEX_COLUMN_USAGES
2385 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
2386 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'addresses';
2387
2388 l_new_extension_id NUMBER;
2389 l_target_object_name VARCHAR2(200);
2390 l_src_object_name VARCHAR2(200);
2391
2392 BEGIN
2393
2394 IF g_debug_stmt THEN
2395 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2396 END IF;
2397
2398 IF g_debug_stmt THEN
2399 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2400 ,p_token => l_progress
2401 ,p_message => ' p_ag_type ' || p_ag_type
2402 ||' p_attr_group_id ' || p_attr_group_id
2403 ||' p_extension_id ' || p_extension_id
2404 );
2405 END IF;
2406
2407 l_progress := '020';
2408 SELECT data_level_name
2409 INTO l_src_data_level
2410 FROM ego_data_level_b
2411 WHERE attr_group_type = p_ag_type
2412 AND data_level_name NOT LIKE '%ARCHIVE%';
2413
2414 IF g_debug_stmt THEN
2415 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2416 ,p_token => l_progress
2417 ,p_message => ' l_src_data_level ' || l_src_data_level
2418 );
2419 END IF;
2420
2421 l_attr_list := '';
2422
2423 FOR l_attr_list_rec IN c_attr_list(p_ag_type)
2424 LOOP
2425 IF l_attr_list IS NULL OR l_attr_list = '' THEN
2426 l_attr_list := l_attr_list_rec.END_USER_COLUMN_NAME;
2427 ELSE
2428 l_attr_list := l_attr_list || ',' || l_attr_list_rec.END_USER_COLUMN_NAME;
2429 END IF;
2430 END LOOP;
2431
2432 IF g_debug_stmt THEN
2433 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2434 ,p_token => l_progress
2435 ,p_message => ' l_attr_list ' || l_attr_list
2436 );
2437 END IF;
2438
2439 l_progress := '040';
2440 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
2441 l_attr_group_request_table.EXTEND(1);
2442 l_attr_group_request_table(1) := ego_attr_group_request_obj
2443 (
2444 p_attr_group_id
2445 ,201 -- application id is always 201
2446 ,p_ag_type -- p_ag_type
2447 ,'addresses'
2448 ,l_src_data_level
2449 ,NULL
2450 ,NULL
2451 ,NULL
2452 ,NULL
2453 ,NULL
2454 ,l_attr_list
2455 );
2456
2457 l_progress := '060';
2458 l_src_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(p_ag_type).l_object_name;
2459
2460 l_progress := '080';
2461 ego_user_attrs_data_pvt.get_user_attrs_data(
2462 p_api_version => 1.0
2463 ,p_object_name => l_src_object_name --Get it from PO_UDA_AG_TEMPLATES
2464 ,p_pk_column_name_value_pairs => p_src_pk_col_name_val_pairs--Source Primary Keys
2465 ,p_attr_group_request_table => l_attr_group_request_table --Construct the Attribute Group Table
2466 ,x_attributes_row_table => x_attributes_row_table
2467 ,x_attributes_data_table => x_attributes_data_table
2468 ,x_return_status => x_return_status
2469 ,x_errorcode => x_errorcode
2470 ,x_msg_count => x_msg_count
2471 ,x_msg_data => x_msg_data
2472 );
2473
2474 IF g_debug_stmt
2475 THEN
2476 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2477 , p_token => l_progress
2478 , p_message => ' After Calling ego_user_attrs_data_pvt.get_user_attrs_data
2479 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
2480 ||' Message Data: '|| x_msg_data || ' Error Code : '|| x_errorcode
2481 );
2482 END IF;
2483
2484 IF x_return_status = 'S' AND x_attributes_row_table.Count > 0
2485 THEN
2486
2487 l_progress := '100';
2488 SELECT Value(a)
2489 BULK COLLECT INTO l_process_attr_row_tbl
2490 FROM TABLE(x_attributes_row_table) a
2491 WHERE a.row_identifier = p_extension_id;
2492
2493 l_progress := '120';
2494 SELECT Value(b)
2495 BULK COLLECT INTO l_process_attr_data_tbl
2496 FROM TABLE(x_attributes_data_table) b
2497 WHERE b.ROW_IDENTIFIER =p_extension_id;
2498
2499 SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
2500 INTO l_new_extension_id
2501 FROM dual;
2502
2503 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(ego_col_name_value_pair_obj('UDA_TEMPLATE_ID',g_tar_header_uda_template_id ||'' ));
2504
2505 l_process_attr_row_tbl(1).ATTR_GROUP_ID := get_ag_id('PO_HEADER_EXT_ATTRS','addresses');
2506 l_process_attr_row_tbl(1).ATTR_GROUP_APP_ID := 201;
2507 l_process_attr_row_tbl(1).DATA_LEVEL :='PO_HEADER';
2508 l_process_attr_row_tbl(1).Row_identifier := l_new_extension_id;
2509 l_process_attr_row_tbl(1).ATTR_GROUP_TYPE := 'PO_HEADER_EXT_ATTRS';
2510
2511 l_progress := '140';
2512 FOR i IN 1..l_process_attr_data_tbl.Count
2513 LOOP
2514 l_process_attr_data_tbl(i).ROW_IDENTIFIER := l_new_extension_id;
2515 END LOOP;
2516
2517 l_target_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl('PO_HEADER_EXT_ATTRS').l_object_name;
2518
2519 l_progress := '160';
2520 ego_user_attrs_data_pvt.process_user_attrs_data (
2521 p_api_version => 1.0
2522 ,p_object_name => l_target_object_name
2523 ,p_attributes_row_table => l_process_attr_row_tbl--from Previous STEP but change the ATTR_GROUP_ID here as for the target
2524 ,p_attributes_data_table => l_process_attr_data_tbl --can be same object got from previous step
2525 ,p_pk_column_name_value_pairs => g_tar_head_col_name_val_pairs
2526 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs -- pass { "UDA_TEMPLATE_ID", 343 }
2527 ,x_extension_id => x_extension_id
2528 ,x_mode => x_mode
2529 ,x_failed_row_id_list => x_failed_row_id_list
2530 ,x_return_status => x_return_status
2531 ,x_errorcode => x_errorcode
2532 ,x_msg_count => x_msg_count
2533 ,x_msg_data => x_msg_data
2534 );
2535 IF g_debug_stmt
2536 THEN
2537 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2538 , p_token => l_progress
2539 , p_message => ' After Calling ego_user_attrs_data_pvt.get_user_attrs_data
2540 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
2541 ||' Message Data: '|| x_msg_data || ' Error Code : '|| x_errorcode
2542 );
2543 END IF;
2544
2545 END IF;
2546
2547 IF g_debug_stmt THEN
2548 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2549 END IF;
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552 IF g_debug_unexp THEN
2553 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2554 RAISE;
2555 END IF;
2556
2557 END process_addresses;
2558
2559 /* Bug 12611018 : New proceduree written to get the group_id_tbl*/
2560 PROCEDURE get_group_id_tbl(p_level IN VARCHAR2,
2561 x_attr_group_id_tbl OUT NOCOPY po_tbl_number
2562 )
2563 IS
2564
2565 l_api_name VARCHAR2(30) := 'get_group_id_tbl';
2566 l_progress VARCHAR2(3) := '000';
2567
2568 BEGIN
2569
2570 IF g_debug_stmt THEN
2571 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2572 END IF;
2573
2574 IF g_debug_stmt THEN
2575 PO_DEBUG.debug_stmt (p_log_head => g_log_head||l_api_name,
2576 p_token => l_progress,
2577 p_message => ' p_level : '|| p_level
2578 );
2579 END IF;
2580
2581 --bug 13640946 start, for new documents the data is still in the draft tables. Hence modified the logic to select attribute_group_id from draft tables
2582 IF po_autocreate_params.g_mode = 'NEW' THEN
2583 IF p_level = 'HEADER' THEN
2584
2585 SELECT distinct pudat.attribute_group_id
2586 BULK COLLECT INTO x_attr_group_id_tbl
2587 FROM po_headers_draft_all poh,
2588 po_uda_ag_template_usages pudat,
2589 EGO_ACTION_DISPLAYS_B ead,
2590 EGO_ACTIONS_B ea
2591 WHERE ea.action_id = ead.action_id
2592 AND ea.attr_group_id = pudat.attribute_group_id
2593 AND ea.classification_code = To_Char(pudat.template_id )
2594 AND ead.EXECUTION_METHOD = 'XPD'
2595 AND poh.po_header_id = po_autocreate_params.g_po_header_id
2596 AND poh.uda_template_id = pudat.template_id;
2597
2598
2599 ELSIF p_level = 'LINE' THEN
2600
2601 SELECT distinct pudat.attribute_group_id
2602 BULK COLLECT INTO x_attr_group_id_tbl
2603 FROM po_headers_draft_all poh,
2604 po_uda_ag_template_usages pudat,
2605 EGO_ACTION_DISPLAYS_B ead,
2606 EGO_ACTIONS_B ea
2607 WHERE ea.action_id = ead.action_id
2608 AND ea.attr_group_id = pudat.attribute_group_id
2609 AND ea.classification_code = To_Char(pudat.template_id )
2610 AND ead.EXECUTION_METHOD = 'XPD'
2611 AND poh.po_header_id = po_autocreate_params.g_po_header_id
2612 AND pudat.template_id = (SELECT uda_template_id
2613 FROM po_lines_draft_all
2614 WHERE po_header_id = poh.po_header_id
2615 AND ROWNUM < 2) ;
2616
2617
2618 ELSIF p_level = 'SHIPMENT' THEN
2619
2620 SELECT distinct pudat.attribute_group_id
2621 BULK COLLECT INTO x_attr_group_id_tbl
2622 FROM po_headers_draft_all poh,
2623 po_uda_ag_template_usages pudat,
2624 EGO_ACTION_DISPLAYS_B ead,
2625 EGO_ACTIONS_B ea
2626 WHERE ea.action_id = ead.action_id
2627 AND ea.attr_group_id = pudat.attribute_group_id
2628 AND ea.classification_code = To_Char(pudat.template_id )
2629 AND ead.EXECUTION_METHOD = 'XPD'
2630 AND poh.po_header_id = po_autocreate_params.g_po_header_id
2631 AND pudat.template_id = (SELECT uda_template_id
2632 FROM po_line_locations_draft_all
2633 WHERE po_header_id = poh.po_header_id
2634 AND ROWNUM < 2) ;
2635 END IF;
2636
2637 ELSE
2638 IF p_level = 'HEADER' THEN
2639
2640 SELECT distinct pudat.attribute_group_id
2641 BULK COLLECT INTO x_attr_group_id_tbl
2642 FROM po_headers_all poh,
2643 po_uda_ag_template_usages pudat,
2644 EGO_ACTION_DISPLAYS_B ead,
2645 EGO_ACTIONS_B ea
2646 WHERE ea.action_id = ead.action_id
2647 AND ea.attr_group_id = pudat.attribute_group_id
2648 AND ea.classification_code = To_Char(pudat.template_id )
2649 AND ead.EXECUTION_METHOD = 'XPD'
2650 AND poh.po_header_id = po_autocreate_params.g_po_header_id
2651 AND poh.uda_template_id = pudat.template_id;
2652
2653
2654 ELSIF p_level = 'LINE' THEN
2655
2656 SELECT distinct pudat.attribute_group_id
2657 BULK COLLECT INTO x_attr_group_id_tbl
2658 FROM po_headers_all poh,
2659 po_uda_ag_template_usages pudat,
2660 EGO_ACTION_DISPLAYS_B ead,
2661 EGO_ACTIONS_B ea
2662 WHERE ea.action_id = ead.action_id
2663 AND ea.attr_group_id = pudat.attribute_group_id
2664 AND ea.classification_code = To_Char(pudat.template_id )
2665 AND ead.EXECUTION_METHOD = 'XPD'
2666 AND poh.po_header_id = po_autocreate_params.g_po_header_id
2667 AND pudat.template_id = (SELECT uda_template_id
2668 FROM po_lines_all
2669 WHERE po_header_id = poh.po_header_id
2670 AND ROWNUM < 2) ;
2671
2672
2673 ELSIF p_level = 'SHIPMENT' THEN
2674
2675 SELECT distinct pudat.attribute_group_id
2676 BULK COLLECT INTO x_attr_group_id_tbl
2677 FROM po_headers_all poh,
2678 po_uda_ag_template_usages pudat,
2679 EGO_ACTION_DISPLAYS_B ead,
2680 EGO_ACTIONS_B ea
2681 WHERE ea.action_id = ead.action_id
2682 AND ea.attr_group_id = pudat.attribute_group_id
2683 AND ea.classification_code = To_Char(pudat.template_id )
2684 AND ead.EXECUTION_METHOD = 'XPD'
2685 AND poh.po_header_id = po_autocreate_params.g_po_header_id
2686 AND pudat.template_id = (SELECT uda_template_id
2687 FROM po_line_locations_all
2688 WHERE po_header_id = poh.po_header_id
2689 AND ROWNUM < 2) ;
2690 END IF;
2691 END IF;
2692 --bug 13640946 end
2693
2694 IF g_debug_stmt THEN
2695 PO_DEBUG.debug_stmt (p_log_head => g_log_head||l_api_name,
2696 p_token => l_progress,
2697 p_message => ' x_attr_group_id_tbl.count '|| x_attr_group_id_tbl.count
2698 );
2699 END IF;
2700
2701
2702 IF g_debug_stmt THEN
2703 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2704 END IF;
2705
2706 EXCEPTION
2707 WHEN OTHERS THEN
2708 IF g_debug_unexp THEN
2709 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2710 END IF;
2711 END get_group_id_tbl;
2712 /*bug 12611018 ends*/
2713
2714
2715
2716 PROCEDURE autocreate_uda_postprocess (p_template_id IN NUMBER,p_level IN VARCHAR2)
2717 IS
2718 l_api_name VARCHAR2(30) := 'autocreate_uda_postprocess';
2719 l_progress VARCHAR2(3) := '000';
2720
2721 x_attr_group_id_tbl po_tbl_number;
2722
2723 CURSOR c_lines
2724 IS
2725 SELECT pld.po_line_id
2726 FROM po_lines_draft_all pld, po_lines_interface PLI
2727 WHERE
2728 --bug 13640946 commenting out the condition as template_id has been stamped in the drafts table before making thisa uda call.
2729 --pld.uda_template_id IS NULL AND
2730 PLI.interface_header_id = po_autocreate_params.g_interface_header_id
2731 AND PLD.po_header_id = po_autocreate_params.g_po_header_id
2732 AND pld. line_num =PLI.line_num
2733 AND pld. draft_id = po_autocreate_params.g_draft_id
2734 ;
2735
2736 CURSOR c_shipments
2737 IS
2738 SELECT pll.line_location_id
2739 FROM po_lines_draft_all pld, po_lines_interface PLI, po_line_locations_draft_all pll
2740 WHERE
2741 --bug 13640946 commenting out the condition as template_id has been stamped in the drafts table before making thisa uda call.
2742 --pll.uda_template_id IS NULL AND
2743 PLI.interface_header_id = po_autocreate_params.g_interface_header_id
2744 AND PLD.po_header_id = po_autocreate_params.g_po_header_id
2745 AND pld. line_num =PLI.line_num
2746 AND pld.po_line_id = pll.po_line_id
2747 AND pll.shipment_num=PLI.shipment_num
2748 AND pld. draft_id = po_autocreate_params.g_draft_id
2749 AND pll.draft_id = po_autocreate_params.g_draft_id
2750 ;
2751
2752 BEGIN
2753
2754 IF g_debug_stmt THEN
2755 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2756 END IF;
2757
2758 l_progress := '010';
2759
2760
2761 IF p_level='HEADER'
2762 THEN
2763
2764 l_progress := '020';
2765 get_group_id_tbl(p_level,x_attr_group_id_tbl) ; --bug 12611018
2766 IF x_attr_group_id_tbl.Count > 0
2767 THEN
2768 l_progress := '030';
2769 exec_uda_function(
2770 p_template_id => p_template_id ,
2771 p_pk1_col_name => 'PO_HEADER_ID',
2772 p_pk1_col_value => po_autocreate_params.g_po_header_id,
2773 p_pk2_col_name => 'DRAFT_ID',
2774 p_pk2_col_value => g_draft_id,
2775 p_attr_group_id_tbl => x_attr_group_id_tbl
2776 );
2777 END IF;
2778 ELSIF p_level ='LINE'
2779 THEN
2780 l_progress := '040';
2781
2782 FOR l_rec IN c_lines
2783 LOOP
2784 l_progress := '050';
2785 get_group_id_tbl(p_level,x_attr_group_id_tbl) ; --bug 12611018
2786 IF x_attr_group_id_tbl.Count > 0
2787 THEN
2788 l_progress := '060';
2789 exec_uda_function(
2790 p_template_id => p_template_id ,
2791 p_pk1_col_name => 'PO_LINE_ID',
2792 p_pk1_col_value => l_rec.po_line_id,
2793 p_pk2_col_name => 'DRAFT_ID',
2794 p_pk2_col_value => g_draft_id,
2795 p_attr_group_id_tbl => x_attr_group_id_tbl );
2796 END IF;
2797 END LOOP;
2798
2799 ELSIF p_level='SHIPMENT'
2800 THEN
2801 l_progress := '070';
2802 FOR l_rec IN c_shipments
2803 LOOP
2804 l_progress := '080';
2805 get_group_id_tbl(p_level,x_attr_group_id_tbl) ; --bug 12611018
2806 IF x_attr_group_id_tbl.Count > 0
2807 THEN
2808 l_progress := '090';
2809 exec_uda_function(
2810 p_template_id => p_template_id ,
2811 p_pk1_col_name => 'LINE_LOCATION_ID',
2812 p_pk1_col_value => l_rec.line_location_id,
2813 p_pk2_col_name => 'DRAFT_ID',
2814 p_pk2_col_value => g_draft_id,
2815 p_attr_group_id_tbl => x_attr_group_id_tbl);
2816
2817 END IF;
2818 END LOOP;
2819 END IF;
2820
2821 IF g_debug_stmt THEN
2822 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2823 END IF;
2824
2825 EXCEPTION
2826 WHEN OTHERS THEN
2827 IF g_debug_unexp THEN
2828 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2829 END IF;
2830 END autocreate_uda_postprocess;
2831
2832
2833 PROCEDURE exec_uda_function(
2834 p_template_id IN NUMBER,
2835 p_pk1_col_name IN VARCHAR2,
2836 p_pk1_col_value IN VARCHAR2,
2837 p_pk2_col_name IN VARCHAR2,
2838 p_pk2_col_value IN VARCHAR2,
2839 p_attr_group_id_tbl IN po_tbl_number
2840 )
2841 IS
2842
2843 l_api_name VARCHAR2(30) := 'exec_uda_function';
2844 l_progress VARCHAR2(3) := '000';
2845
2846 l_entity_code VARCHAR2(200);
2847 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2848 l_data_level VARCHAR2(200);
2849
2850 l_attr_list VARCHAR2(2000);
2851
2852 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
2853
2854 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
2855 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
2856 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2857
2858 l_ext_attr_value_pairs_ind NUMBER;
2859 l_ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
2860
2861 x_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE; -- not initializing this one
2862
2863 x_return_status VARCHAR2(1);
2864 x_errorcode NUMBER;
2865 x_msg_count NUMBER;
2866 x_msg_data VARCHAR2(2000);
2867
2868 l_attr_group_name VARCHAR2(200);
2869
2870
2871
2872 CURSOR c_attr_list(p_ag_type VARCHAR2, p_ag_name IN VARCHAR2) IS
2873 SELECT END_USER_COLUMN_NAME
2874 FROM FND_DESCR_FLEX_COLUMN_USAGES
2875 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
2876 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_name;
2877
2878 BEGIN
2879 /*
2880 IF po_autocreate_params.g_mode <> 'NEW' AND p_doc_level = 'HEADER' THEN
2881 RETURN;
2882 END IF; */
2883
2884 IF g_debug_stmt THEN
2885 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2886 END IF;
2887
2888 IF g_debug_stmt THEN
2889 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2890 ,p_token => l_progress
2891 ,p_message =>' p_template_id ' || p_template_id
2892 ||' p_pk1_col_name ' || p_pk1_col_name
2893 ||' p_pk1_col_value ' || p_pk1_col_value
2894 ||' p_pk1_col2_name ' || p_pk2_col_name
2895 ||' p_pk2_col2_value ' || p_pk2_col_value);
2896
2897 po_debug.debug_var(p_log_head => g_log_head||l_api_name
2898 ,p_progress => l_progress
2899 ,p_name => 'p_attr_group_id_tbl'
2900 ,p_value => p_attr_group_id_tbl
2901 );
2902 END IF;
2903
2904 l_progress := '020';
2905
2906 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2907 EGO_COL_NAME_VALUE_PAIR_OBJ(
2908 p_pk1_col_name, p_pk1_col_value
2909 ),
2910 EGO_COL_NAME_VALUE_PAIR_OBJ(
2911 p_pk2_col_name,p_pk2_col_value)
2912 );
2913 l_progress := '040';
2914 SELECT entity_code
2915 INTO l_entity_code
2916 FROM po_uda_ag_templates
2917 WHERE template_id = p_template_id;
2918
2919 IF g_debug_stmt THEN
2920 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2921 ,p_token => l_progress
2922 ,p_message => ' l_entity_code : ' || l_entity_code
2923 );
2924 END IF;
2925
2926 l_progress := '060';
2927 SELECT data_level_name
2928 INTO l_data_level
2929 FROM ego_data_level_b
2930 WHERE attr_group_type = l_entity_code
2931 AND data_level_name NOT LIKE '%ARCHIVE%';
2932
2933 IF g_debug_stmt THEN
2934 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2935 ,p_token => l_progress
2936 ,p_message => ' l_data_level : ' || l_data_level
2937 ||' l_entity_code : ' || l_entity_code
2938
2939 );
2940 END IF;
2941
2942 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
2943 l_attr_group_request_table.EXTEND(1);
2944
2945
2946 FOR i IN 1..p_attr_group_id_tbl.Count
2947 LOOP
2948
2949 l_progress := '080';
2950 SELECT ATTR_GROUP_NAME
2951 INTO l_attr_group_name
2952 FROM EGO_ATTR_GROUPS_V
2953 WHERE ATTR_GROUP_TYPE = l_entity_code
2954 AND attr_group_id = p_attr_group_id_tbl(i);
2955
2956 IF g_debug_stmt THEN
2957 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2958 ,p_token => l_progress
2959 ,p_message => ' l_attr_group_name : '|| l_attr_group_name
2960 );
2961 END IF;
2962
2963 l_attr_list := '';
2964
2965 FOR l_attr_list_rec IN c_attr_list(l_entity_code,l_attr_group_name)
2966 LOOP
2967 IF l_attr_list IS NULL OR l_attr_list = '' THEN
2968 l_attr_list := l_attr_list_rec.END_USER_COLUMN_NAME;
2969 ELSE
2970 l_attr_list := l_attr_list || ',' || l_attr_list_rec.END_USER_COLUMN_NAME;
2971 END IF;
2972 END LOOP;
2973
2974 IF g_debug_stmt THEN
2975 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2976 ,p_token => l_progress
2977 ,p_message => ' l_attr_list : ' || l_attr_list
2978 );
2979 END IF;
2980
2981 l_progress := '100';
2982 l_attr_group_request_table(1) := ego_attr_group_request_obj
2983 (
2984 p_attr_group_id_tbl(i)
2985 ,201 -- application id is always 201
2986 ,l_entity_code -- p_ag_type
2987 ,l_attr_group_name
2988 ,l_data_level
2989 ,NULL
2990 ,NULL
2991 ,NULL
2992 ,NULL
2993 ,NULL
2994 ,l_attr_list
2995 );
2996 l_progress := '120';
2997
2998 END LOOP;
2999
3000 --bug 12611018 moved the calls to the below procedures outside the for loop.
3001 -- These procedures need not be called for everytime. Since they take Table variables as inputs
3002
3003 ego_user_attrs_data_pub.Get_User_Attrs_Data
3004 (
3005 p_api_version => 1.0
3006 ,p_object_name => PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_code).l_object_name
3007 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
3008 ,p_attr_group_request_table => l_attr_group_request_table
3009 ,x_attributes_row_table => l_attributes_row_table
3010 ,x_attributes_data_table => l_attr_name_value_pairs
3011 ,x_return_status => x_return_status
3012 ,x_errorcode => x_errorcode
3013 ,x_msg_count => x_msg_count
3014 ,x_msg_data => x_msg_data
3015 );
3016
3017 IF g_debug_stmt
3018 THEN
3019 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
3020 , p_token => l_progress
3021 , p_message => ' After Calling ego_user_attrs_data_pvt.get_user_attrs_data
3022 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
3023 ||' Message Data: '|| x_msg_data || ' Error Code : '|| x_errorcode
3024 );
3025 END IF;
3026
3027 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3028
3029 x_external_attr_value_pairs := EGO_COL_NAME_VALUE_PAIR_TABLE(
3030 EGO_COL_NAME_VALUE_PAIR_OBJ('x_return_status', FND_API.G_RET_STS_SUCCESS)
3031 ,EGO_COL_NAME_VALUE_PAIR_OBJ('x_errorcode', '0')
3032 ,EGO_COL_NAME_VALUE_PAIR_OBJ('x_msg_count', '0')
3033 ,EGO_COL_NAME_VALUE_PAIR_OBJ('x_msg_data', 'SUCCESS')
3034 );
3035
3036 l_progress := '140';
3037 po_uda_import_pkg.execute_import_uda_function
3038 ( p_template_id => p_template_id
3039 ,p_event => 'XPD'
3040 ,x_external_attr_value_pairs => x_external_attr_value_pairs
3041 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
3042 ,x_attr_name_value_pairs => l_attr_name_value_pairs
3043 ,x_attributes_row_table => l_attributes_row_table
3044 ,x_return_status => x_return_status
3045 ,x_errorcode => x_errorcode
3046 ,x_msg_count => x_msg_count
3047 ,x_msg_data => x_msg_data
3048 );
3049 IF g_debug_stmt
3050 THEN
3051 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
3052 , p_token => l_progress
3053 , p_message => ' After Calling po_uda_import_pkg.execute_import_uda_function
3054 Return Status ' || x_return_status || ' Message Count :' || x_msg_count
3055 ||' Message Data: '|| x_msg_data || ' Error Code : '|| x_errorcode
3056 );
3057 END IF;
3058 END IF;
3059
3060
3061 IF g_debug_stmt THEN
3062 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
3063 END IF;
3064
3065 EXCEPTION
3066 WHEN OTHERS THEN
3067 IF g_debug_unexp THEN
3068 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
3069 END IF;
3070 END exec_uda_function;
3071
3072 PROCEDURE init_info
3073 IS
3074
3075 l_api_name VARCHAR2(30) := 'init_info';
3076 l_progress VARCHAR2(3) := '000';
3077
3078
3079
3080 l_interface_source_code VARCHAR2(100);
3081 l_document_type VARCHAR2(100); -- PO,PA
3082 l_document_style_id NUMBER;
3083 l_document_subtype VARCHAR(100);
3084
3085 BEGIN
3086
3087 -- PO_AUTOCREATE_PARAMS.g_document_type = 'PO'
3088 -- PO_AUTOCREATE_PARAMS.g_document_subtype in ('STANDARD', 'PLANNED', 'RELEASE'))
3089
3090
3091 IF g_debug_stmt THEN
3092 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
3093 END IF;
3094
3095 l_progress := '010';
3096 SELECT interface_source_code, document_type_code, document_subtype, style_id
3097 INTO l_interface_source_code, l_document_type, l_document_subtype, l_document_style_id
3098 FROM po_headers_interface
3099 WHERE interface_header_id = po_autocreate_params.g_interface_header_id;
3100
3101 l_progress := '020';
3102
3103 IF l_interface_source_code ='PO'
3104 THEN
3105 g_src_func_area := 'REQUISITIONS';
3106
3107 ELSIF l_interface_source_code ='SOURCING'
3108 THEN
3109 g_src_func_area := 'SOURCING' ;
3110 g_src_doc_type := 'OFFER'; --Always source doc type will be bid
3111 END IF;
3112
3113 -- Determine target Document type.
3114 IF l_document_type = 'PO'
3115 THEN
3116 g_target_doc_type := 'STANDARD';
3117 ELSIF l_document_type ='PA'
3118 THEN
3119 IF l_document_subtype = 'BLANKET'
3120 THEN
3121 g_target_doc_type := 'BLANKET';
3122 ELSIF l_document_subtype ='CONTRACT'
3123 THEN
3124 g_target_doc_type :='CONTRACT';
3125 END IF;
3126 END IF;
3127
3128 g_target_func_area := 'PURCHASING';
3129 g_target_doc_style := l_document_style_id;
3130
3131 g_tar_head_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3132 EGO_COL_NAME_VALUE_PAIR_OBJ(
3133 'PO_HEADER_ID', po_autocreate_params.g_po_header_id
3134 ),
3135 EGO_COL_NAME_VALUE_PAIR_OBJ(
3136 'DRAFT_ID',g_draft_id)
3137 );
3138
3139 g_tar_header_uda_template_id := get_uda_template_id
3140 (p_functional_area => g_target_func_area
3141 ,p_doc_type => g_target_doc_type
3142 ,p_doc_level => 'HEADER'
3143 ,p_doc_style_id => g_target_doc_style
3144 );
3145
3146
3147 IF g_debug_stmt THEN
3148 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
3149 END IF;
3150
3151 EXCEPTION
3152 WHEN OTHERS THEN
3153 IF g_debug_unexp THEN
3154 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
3155 RAISE;
3156 END IF;
3157 END init_info;
3158
3159
3160
3161 /* -------------------------
3162 --PUBLIC PROCEDURES--
3163 -------------------------
3164 */
3165
3166 /*
3167 For Functional Area = Purchasing: Document Type and Document Style are mandatory.
3168 Document Type can be Blanket Purchase Agreement, Purchase Order
3169 and Contract Purchase Agreement (i.e., the seeded types only,
3170 same as those available within the document style).
3171 Document Style can be any CLM enabled active document style.
3172
3173 For Functional Area = Requisitions: Document Type and Document Style are not applicable.
3174
3175 For Functional Area = Sourcing: Document Style is not applicable,
3176 Document Type shall be enabled and mandatory.
3177 */
3178
3179 PROCEDURE autocreate_uda_data(
3180 p_source_document IN VARCHAR2, -- PR,SOL
3181 p_target_document IN VARCHAR2, -- SOL,PO,IDV
3182 p_src_pk1_value IN NUMBER,
3183 p_src_pk2_value IN NUMBER,
3184 p_tar_pk1_value IN NUMBER, -- PO_HEADER_ID
3185 p_tar_pk2_value IN NUMBER, -- DRAFT_ID
3186 x_return_status OUT NOCOPY VARCHAR2,
3187 x_errorcode OUT NOCOPY NUMBER,
3188 x_msg_count OUT NOCOPY NUMBER,
3189 x_msg_data OUT NOCOPY VARCHAR2)
3190 IS
3191
3192 l_api_name VARCHAR2(30) := 'autocreate_uda_data';
3193 l_progress VARCHAR2(3) := '000';
3194
3195 x_doc_level_tbl po_tbl_varchar15;
3196
3197 l_uda_template_id NUMBER;
3198 x_attr_group_tbl po_tbl_varchar30;
3199 x_priorities_tbl PO_TBL_VARCHAR3;
3200 l_ag_type VARCHAR2(300);
3201 l_tbl_name varchar2(100);
3202 x_source_pkey_tbl po_tbl_number;
3203 x_target_pkey_tbl po_tbl_number;
3204 l_data_copied VARCHAR2(1);
3205 x_source_doc_level VARCHAR2(10);
3206 x_group_type VARCHAR2(120);
3207 x_group_property VARCHAR2(120);
3208 x_group_property_tbl PO_TBL_VARCHAR30;
3209 l_authorization_status po_headers_all.AUTHORIZATION_STATUS%TYPE; /*bug 12415651*/
3210
3211
3212 BEGIN
3213
3214 IF g_debug_stmt THEN
3215 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
3216 END IF;
3217
3218 -- If we know the interface header id we can find out whether the call is
3219 -- from sourcing or PO BWC and also we can find out the target document style
3220 -- and document type, subtype etc..
3221 -- PO_HEADERS_INTERFACE.interface_source_code,document_type_code,document_subtype,style_id
3222 -- We can get the source document details from po_lines_interface
3223
3224 -- Initialize source and target functional,document type, document style details.
3225
3226 l_progress := '010';
3227
3228 g_draft_id := Nvl(p_tar_pk2_value,-1);
3229 init_info;
3230
3231
3232 -- Autocreate Complex Pricing Attributes.
3233 auto_complexpricing_ag;
3234
3235
3236 -- Get the target document levels
3237 l_progress := '020';
3238 get_document_levels( p_func_area => g_target_func_area
3239 ,p_doc_type => g_target_doc_type
3240 ,p_doc_style => g_target_doc_style
3241 ,x_doc_level_tbl => x_doc_level_tbl
3242 );
3243
3244 FOR i IN 1..x_doc_level_tbl.Count
3245 LOOP
3246
3247 /*bug 12611018
3248 Below code is used for copying header level uda attributes
3249 from the Base Header in ADD to flow.The Modifiaction related
3250 address also need to defaulted explicitly
3251 */
3252 l_progress := '021';
3253
3254 IF (x_doc_level_tbl(i) = 'HEADER' AND PO_AUTOCREATE_PARAMS.g_mode = 'ADD') then
3255
3256 BEGIN
3257
3258 select authorization_status
3259 INTO l_authorization_status
3260 FROM po_headers_all
3261 WHERE po_header_id = PO_AUTOCREATE_PARAMS.g_po_header_id;
3262
3263 EXCEPTION
3264 WHEN No_Data_Found THEN
3265 l_authorization_status:='';
3266 END;
3267
3268 /* Header level UDA attributes should be copied from base award to MOD
3269 only while creating a new modification in the ADD to mode.*/
3270 IF ( NOT po_autocreate_params.g_is_mod_exists
3271 AND Nvl(l_authorization_status,'INCOMPLETE') = 'APPROVED') THEN
3272
3273 l_progress := '022';
3274
3275 autocreate_uda_header_attr();
3276
3277 /* Need to default the MOD_ISSUING_OFFICE and MOD_ADMIN_OFFICE*/
3278 auto_default_sys_addresses('MOD_ISSUING_OFFICE',x_return_status);
3279
3280 auto_default_sys_addresses('MOD_ADMIN_OFFICE',x_return_status);
3281 ELSE
3282 Null;
3283 /*No need to copy UDA attributes when modification exists
3284 Or the award we are trying to is an INCOMPLETE award.In this case
3285 Header UDa attr are already present*/
3286 end if;
3287
3288
3289 ELSE
3290 --bug 12611018 ends
3291
3292 l_progress := '030';
3293 l_uda_template_id:= get_uda_template_id
3294 (p_functional_area => g_target_func_area
3295 ,p_doc_type => g_target_doc_type
3296 ,p_doc_level => x_doc_level_tbl(i)
3297 ,p_doc_style_id => g_target_doc_style
3298 );
3299
3300 g_target_uda_template_id := l_uda_template_id;
3301
3302 l_progress := '040';
3303 get_attribute_groups(p_uda_template_id => l_uda_template_id
3304 ,x_attr_group_tbl => x_attr_group_tbl);
3305
3306
3307
3308 FOR j IN 1..x_attr_group_tbl.Count
3309 LOOP
3310 l_progress := '050';
3311
3312 get_autocreate_properties(p_doc_level => x_doc_level_tbl(i),
3313 p_attr_group => x_attr_group_tbl(j),
3314 p_src_doc_type => Nvl(g_src_doc_type,'REQ'),
3315 p_tar_doc_type => g_target_doc_type,
3316 x_group_property_tbl => x_group_property_tbl
3317 );
3318
3319 FOR k IN 1..x_group_property_tbl.Count
3320 LOOP
3321
3322
3323 /* If no rule exists in the rules table copy from the immediate source doc to target doc*/
3324 l_progress := '060';
3325 get_autocreate_priorities(p_doc_level => x_doc_level_tbl(i)
3326 ,p_attr_group => x_attr_group_tbl(j)
3327 ,p_src_doc_type => Nvl(g_src_doc_type,'REQ')
3328 ,p_tar_doc_type => g_target_doc_type
3329 ,p_group_property => x_group_property_tbl(k)
3330 ,x_source_doc_level => x_source_doc_level
3331 ,x_priorities_tbl => x_priorities_tbl
3332 ,x_group_type => x_group_type
3333 );
3334 l_progress := '070';
3335
3336 IF x_priorities_tbl.Count > 0 THEN
3337 autocreate_ag_level(p_doc_level => x_doc_level_tbl(i)
3338 ,p_src_doc_level => x_source_doc_level
3339 ,p_attr_group => x_attr_group_tbl(j)
3340 ,p_priorities_tbl => x_priorities_tbl
3341 ,p_group_type => x_group_type
3342 ,p_group_property => x_group_property_tbl(k)
3343 );
3344 END IF;
3345 END LOOP; -- End Attribute Properties
3346 END LOOP; -- End Attribute Groups
3347
3348 autocreate_uda_postprocess(l_uda_template_id,x_doc_level_tbl(i));
3349
3350 END IF;
3351 END LOOP; -- End Doc Levels
3352
3353 l_progress :='070';
3354
3355
3356 IF g_debug_stmt THEN
3357 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
3358 END IF;
3359
3360 EXCEPTION
3361 WHEN OTHERS THEN
3362 IF g_debug_unexp THEN
3363 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
3364 END IF;
3365 END autocreate_uda_data;
3366
3367 /* Bug 9879573 and 12611018 Start
3368 While autocreating in add to add to award flow.
3369 The header level UDA attributes were not getting copied
3370 from the base award.In this case we have to separately
3371 copy the UDA attributes from the base header.
3372 Hence source becomes the base award and target is the modification. */
3373
3374 PROCEDURE autocreate_uda_header_attr
3375 IS
3376
3377 l_api_name VARCHAR2(30) := 'autocreate_uda_header_attr';
3378 l_progress VARCHAR2(3) := '000';
3379
3380 l_object_id FND_OBJECTS.object_id%TYPE;
3381 l_attr_group_Type VARCHAR2(300);
3382 l_object_dff_cr PO_UDA_DATA_UTIL.g_object_dff_cr;
3383 l_dtlevel_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3384 l_main_data_level_id EGO_DATA_LEVEL_B.data_level_id%TYPE;
3385 x_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
3386 x_errorcode NUMBER;
3387
3388
3389 from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3390 to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3391
3392 x_pk1_col_name VARCHAR2(100);
3393 x_pk1_value NUMBER;
3394 x_pk2_col_name VARCHAR2(100);
3395 x_pk2_value NUMBER;
3396 x_src_template_id NUMBER;
3397
3398 x_return_status VARCHAR2(1);
3399 x_msg_count NUMBER;
3400 x_msg_data VARCHAR2(2000);
3401
3402 BEGIN
3403 x_pk1_col_name := 'PO_HEADER_ID';
3404 x_pk2_col_name := 'DRAFT_ID';
3405 x_pk1_value := po_autocreate_params.g_po_header_id;
3406 x_pk2_value := -1;
3407
3408 x_src_template_id := get_template_id (p_table_name => 'PO_HEADERS_ALL'
3409 ,p_pkey1_col_name => 'PO_HEADER_ID'
3410 ,p_pkey1_val => po_autocreate_params.g_po_header_id
3411 ,p_pkey2_col_name => NULL
3412 ,p_pkey2_val => NULL
3413 );
3414
3415
3416 IF g_debug_stmt THEN
3417 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
3418 , p_token => l_progress
3419 , p_message => 'x_src_template_id :'||x_src_template_id
3420 );
3421 END IF;
3422
3423 from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3424 EGO_COL_NAME_VALUE_PAIR_OBJ(
3425 'PO_HEADER_ID', po_autocreate_params.g_po_header_id
3426 ),
3427 EGO_COL_NAME_VALUE_PAIR_OBJ(
3428 'DRAFT_ID', -1
3429 ));
3430 to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3431 EGO_COL_NAME_VALUE_PAIR_OBJ(
3432 'PO_HEADER_ID', po_autocreate_params.g_po_header_id
3433 ),
3434 EGO_COL_NAME_VALUE_PAIR_OBJ(
3435 'DRAFT_ID',g_draft_id));
3436
3437
3438 BEGIN
3439
3440 SELECT ENTITY_CODE
3441 INTO l_attr_group_Type
3442 FROM PO_UDA_AG_TEMPLATES
3443 WHERE TEMPLATE_ID = x_src_template_id;
3444
3445 IF g_debug_stmt THEN
3446 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
3447 , p_token => l_progress
3448 , p_message => 'l_attr_group_Type :'||l_attr_group_Type
3449 );
3450 END IF;
3451
3452
3453 l_object_dff_cr := PO_UDA_DATA_UTIL.g_object_dff_tl(l_attr_group_Type);
3454
3455 SELECT object_id
3456 INTO l_object_id
3457 FROM FND_OBJECTS
3458 WHERE OBJ_NAME = l_object_dff_cr.l_object_name;
3459
3460 SELECT data_level_id
3461 INTO l_main_data_level_id
3462 FROM EGO_DATA_LEVEL_B
3463 WHERE ATTR_GROUP_TYPE = l_attr_group_Type
3464 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
3465
3466 EXCEPTION
3467 WHEN OTHERS THEN
3468 IF g_debug_unexp THEN
3469 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
3470 END IF;
3471
3472 END;
3473
3474 l_dtlevel_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3475 EGO_COL_NAME_VALUE_PAIR_OBJ(
3476 'PK1_VALUE', null
3477 )
3478 );
3479
3480 IF g_debug_stmt THEN
3481 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
3482 , p_token => l_progress
3483 , p_message => 'l_object.object_id : '||l_object_id ||
3484 'l_object_dff_cr.l_object_name' ||l_object_dff_cr.l_object_name ||
3485 'l_main_data_level.data_level_id'||l_main_data_level_id
3486 );
3487 END IF;
3488
3489
3490 ego_user_attrs_data_pvt.copy_user_attrs_data ( p_api_version => 1.0
3491 ,p_application_id => 201
3492 ,p_object_id => l_object_id
3493 ,p_object_name => l_object_dff_cr.l_object_name
3494 ,p_old_pk_col_value_pairs => from_pk_col_value_pairs
3495 ,p_old_data_level_id => l_main_data_level_id
3496 ,p_old_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs
3497 ,p_new_pk_col_value_pairs => to_pk_col_value_pairs
3498 ,p_new_data_level_id => l_main_data_level_id
3499 ,p_new_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs
3500 ,p_commit => FND_API.G_FALSE
3501 ,x_return_status => x_return_status
3502 ,x_errorcode => x_errorcode
3503 ,x_msg_count => x_msg_count
3504 ,x_msg_data => x_msg_data
3505 );
3506
3507
3508 END autocreate_uda_header_attr;
3509 /* Bug 9879573 ends*/
3510
3511 /* bug 12611018 starts*/
3512 FUNCTION get_pk1_value
3513 RETURN NUMBER IS
3514 BEGIN
3515 return g_pk1_value;
3516 END get_pk1_value;
3517
3518 FUNCTION get_pk2_value
3519 RETURN NUMBER IS
3520 BEGIN
3521 return g_pk2_value;
3522 END get_pk2_value;
3523 /* bug 12611018 ends*/
3524 END PO_UDA_AUTOCREATE_PKG;