[Home] [Help]
PACKAGE BODY: APPS.POR_UDA_PKG
Source
1 PACKAGE BODY por_uda_pkg AS
2 /* $Header: POR_UDA_PKG.plb 120.13.12020000.2 2013/04/12 17:37:35 mzhussai ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('POR_UDA_PKG');
5
6 PROCEDURE get_pr_line_attr_group_ids(
7 p_template_id IN VARCHAR2,
8 p_req_line_id IN NUMBER,
9 p_is_amd_mode VARCHAR2,
10 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2
12 ) IS
13
14 d_api_name CONSTANT VARCHAR2(30) := 'get_pr_line_attr_group_ids';
15 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
16 d_progress NUMBER := 0;
17
18 l_po_uda_usage_object po_uda_usage_object ;
19 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
20 l_attr_grp_ids_list PO_TBL_NUMBER;
21 l_contract_type VARCHAR2(250);
22 l_x_attr_ids_count NUMBER;
23 l_attr_grp_ids PO_TBL_NUMBER;
24 t_count NUMBER;
25 l_count NUMBER;
26
27 CURSOR c_get_usages(p_template_id NUMBER) IS
28 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
29 WHERE template_id = p_template_id;
30
31 CURSOR c_get_usage_context(p_req_line_id NUMBER) IS
32 SELECT CONTRACT_TYPE FROM po_requisition_lines_all
33 WHERE requisition_line_id = p_req_line_id;
34
35 BEGIN
36
37 IF (PO_LOG.d_proc) THEN
38 PO_LOG.proc_begin(d_module);
39 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
40 PO_LOG.proc_begin(d_module, 'p_req_line_id', p_req_line_id);
41 END IF;
42
43 l_attr_grp_ids := PO_TBL_NUMBER();
44 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY();
45
46 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
47
48 IF (c_get_usages_rec.attribute_category = 'PRICING') then
49 OPEN c_get_usage_context(p_req_line_id);
50 FETCH c_get_usage_context INTO l_contract_type;
51 CLOSE c_get_usage_context;
52
53 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTRACT_LINE_TYPE', l_contract_type),
54 EGO_COL_NAME_VALUE_PAIR_OBJ( 'IDC_TYPE', 'IDC_NA'));
55 END IF;
56
57 IF(p_is_amd_mode = 'true' AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
58 IF PO_LOG.d_stmt THEN
59 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
60 END IF;
61
62 ELSE
63 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
64 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
65
66 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
67 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
68 l_attr_grp_ids.extend(1);
69 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
70 END LOOP;
71 END IF;
72 END LOOP;
73
74 x_attr_grp_ids := l_attr_grp_ids;
75 x_return_status := 'S';
76
77 IF PO_LOG.d_stmt THEN
78 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
79 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
80 END IF;
81
82 EXCEPTION
83 WHEN OTHERS THEN
84 d_progress := 40;
85 x_return_status := 'U';
86 IF PO_LOG.d_stmt THEN
87 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
88 END IF;
89 END get_pr_line_attr_group_ids;
90
91 PROCEDURE SUBMIT_NON_RENDER_UDA( p_req_header_id IN NUMBER
92 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
93 ,x_return_status OUT NOCOPY VARCHAR2)
94 IS
95
96 d_api_name CONSTANT VARCHAR2(30) := 'SUBMIT_NON_RENDER_UDA';
97 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
98 d_progress NUMBER := 0;
99
100 l_po_uda_usage_object_array PO_UDA_USAGE_OBJECT_ARRAY;
101 l_template_id NUMBER;
102 l_contract_type VARCHAR2(500);
103 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
104 l_preparer_id NUMBER;
105 l_org_id NUMBER;
106 l_line_type_id NUMBER;
107 l_contract_line_type VARCHAR2(100);
108 l_item_id NUMBER;
109 l_deliver_to_location_id NUMBER;
110
111 l_return_status VARCHAR2(2);
112 l_errorcode NUMBER;
113 l_msg_count NUMBER;
114 l_msg_data VARCHAR2(4000);
115 t_count NUMBER;
116
117 l_ext_attr_value_pairs_ind NUMBER;
118 l_ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
119 l_uda_return_status VARCHAR2(10);
120 l_uda_msg_data VARCHAR2(4000);
121 l_final_return_status VARCHAR2(10);
122 l_final_msg_data VARCHAR2(4000);
123 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
124 l_msg_count_found VARCHAR2(1);
125
126 msg_count NUMBER;
127
128 CURSOR c_get_lines(p_req_header_id NUMBER) IS
129 SELECT requisition_line_id FROM po_requisition_lines_all
130 WHERE requisition_header_id = p_req_header_id;
131
132
133 BEGIN
134 IF (PO_LOG.d_proc) THEN
135 PO_LOG.proc_begin(d_module);
136 PO_LOG.proc_begin(d_module, 'p_req_header_id', p_req_header_id);
137 END IF;
138
139 SELECT uda_template_id, preparer_id, org_id
140 INTO l_template_id, l_preparer_id, l_org_id
141 FROM po_requisition_headers_all WHERE requisition_header_id = p_req_header_id;
142
143 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('PREPARER_ID', l_preparer_id || '', l_external_attr_value_pairs);
144 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('ORG_ID', l_org_id || '', l_external_attr_value_pairs);
145 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
146 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
147 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
148 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
149
150 -- To initialize the messages list.
151 fnd_msg_pub.Initialize();
152 l_final_return_status := FND_API.G_RET_STS_SUCCESS;
153
154 POR_UDA_PKG.UDA_SUBMISSION_CHECK
155 (
156 p_pk1_value => p_req_header_id
157 ,p_template_id => l_template_id
158 ,x_external_attr_value_pairs => l_external_attr_value_pairs
159 ,x_return_status => l_return_status
160 ,x_errorcode => l_errorcode
161 ,x_msg_count => l_msg_count
162 ,x_msg_data => l_msg_data
163 ,uda_return_status => l_uda_return_status
164 ,uda_msg_data => l_uda_msg_data
165 );
166
167 IF(l_uda_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
168 l_final_return_status := l_uda_return_status; -- FND_API.G_RET_STS_ERROR;
169 --l_final_msg_data := l_final_msg_data || l_uda_msg_data ;
170 END IF;
171
172 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
173
174 --Loop through all the lines for this PR to default UDA's
175 FOR c_get_lines_rec IN c_get_lines(p_req_header_id) LOOP
176
177 SELECT uda_template_id, line_type_id, contract_type, item_id, deliver_to_location_id
178 INTO l_template_id, l_line_type_id, l_contract_line_type, l_item_id, l_deliver_to_location_id
179 FROM po_requisition_lines_all
180 WHERE requisition_line_id = c_get_lines_rec.requisition_line_id;
181
182 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('ITEM_ID', l_item_id || '', l_external_attr_value_pairs);
183 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('CONTRACT_TYPE', l_contract_line_type || '', l_external_attr_value_pairs);
184 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('LINE_TYPE_ID', l_line_type_id || '', l_external_attr_value_pairs);
185 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('SHIP_TO_LOC_ID', l_deliver_to_location_id || '', l_external_attr_value_pairs);
186
187 POR_UDA_PKG.UDA_SUBMISSION_CHECK
188 (
189 p_pk1_value => c_get_lines_rec.requisition_line_id
190 ,p_template_id => l_template_id
191 ,x_external_attr_value_pairs => l_external_attr_value_pairs
192 ,x_return_status => l_return_status
193 ,x_errorcode => l_errorcode
194 ,x_msg_count => l_msg_count
195 ,x_msg_data => l_msg_data
196 ,uda_return_status => l_uda_return_status
197 ,uda_msg_data => l_uda_msg_data
198 );
199
200 IF(l_uda_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
201 l_final_return_status := l_uda_return_status; --FND_API.G_RET_STS_ERROR;
202 -- l_final_msg_data := l_final_msg_data || l_uda_msg_data ;
203 END IF;
204
205
206 END LOOP;
207 END IF;
208
209 /* l_ext_attr_value_pairs_ind := x_external_attr_value_pairs.FIRST;
210
211 WHILE (l_ext_attr_value_pairs_ind IS NOT NULL AND l_ext_attr_value_pairs_ind <= x_external_attr_value_pairs.LAST)
212 LOOP
213 l_ext_name_val_obj := x_external_attr_value_pairs(l_ext_attr_value_pairs_ind);
214 IF l_ext_name_val_obj IS NOT NULL THEN
215 IF l_ext_name_val_obj.NAME = 'x_return_status' THEN
216 x_external_attr_value_pairs(l_ext_attr_value_pairs_ind).VALUE := l_final_return_status ;
217 ELSIF l_ext_name_val_obj.NAME = 'x_msg_count' THEN
218 msg_count := fnd_msg_pub.Count_Msg();
219 x_external_attr_value_pairs(l_ext_attr_value_pairs_ind).VALUE := msg_count ;
220 END IF;
221
222 END IF;
223 l_ext_attr_value_pairs_ind := x_external_attr_value_pairs.NEXT(l_ext_attr_value_pairs_ind);
224 END LOOP;*/
225
226 msg_count := fnd_msg_pub.Count_Msg();
227 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', l_final_return_status, l_external_attr_value_pairs);
228 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', msg_count, l_external_attr_value_pairs);
229
230 x_return_status := l_return_status;
231 x_external_attr_value_pairs := l_external_attr_value_pairs;
232
233 IF (PO_LOG.d_proc) THEN
234 PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
235 END IF;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 d_progress := 40;
240 x_return_status := 'U';
241 IF PO_LOG.d_stmt THEN
242 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
243 END IF;
244
245 END SUBMIT_NON_RENDER_UDA;
246
247
248 PROCEDURE UDA_SUBMISSION_CHECK
249 (
250 p_pk1_value IN NUMBER
251 ,p_pk2_value IN NUMBER DEFAULT NULL
252 ,p_pk3_value IN NUMBER DEFAULT NULL
253 ,p_pk4_value IN NUMBER DEFAULT NULL
254 ,p_pk5_value IN NUMBER DEFAULT NULL
255 ,p_template_id IN NUMBER
256 ,p_address_lookup_type IN VARCHAR2 DEFAULT NULL
257 ,x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE
258 ,x_return_status OUT NOCOPY VARCHAR2
259 ,x_errorcode OUT NOCOPY NUMBER
260 ,x_msg_count OUT NOCOPY NUMBER
261 ,x_msg_data OUT NOCOPY VARCHAR2
262 ,uda_return_status OUT NOCOPY VARCHAR2
263 ,uda_msg_data OUT NOCOPY VARCHAR2
264
265 )
266 IS
267 d_progress NUMBER := 0;
268
269
270 CURSOR c_por_header_ext(po_rec_header_id NUMBER) IS
271 SELECT DISTINCT ATTR_GROUP_ID
272 FROM PO_REQ_HEADERS_EXT_B
273 WHERE REQUISITION_HEADER_ID = po_rec_header_id;
274
275 CURSOR c_por_line_ext(po_rec_line_id NUMBER) IS
276 SELECT DISTINCT ATTR_GROUP_ID
277 FROM PO_REQ_LINES_EXT_B
278 WHERE REQUISITION_LINE_ID = po_rec_line_id;
279
280 CURSOR c_por_attr_list(p_ag_type VARCHAR2, p_ag_name VARCHAR2) IS
281 SELECT END_USER_COLUMN_NAME
282 FROM FND_DESCR_FLEX_COLUMN_USAGES
283 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
284 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_name;
285
286
287 is_valid_entity_ag BOOLEAN := FALSE;
288
289 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
290 l_entity_code VARCHAR2(200);
291 l_ag_type VARCHAR2(200);
292 l_ag_name VARCHAR2(200);
293 l_attr_list VARCHAR2(2000);
294 l_data_level VARCHAR2(200);
295 l_pk1_column_name VARCHAR2(200);
296 l_pk2_column_name VARCHAR2(200);
297 l_pk3_column_name VARCHAR2(200);
298 l_pk4_column_name VARCHAR2(200);
299 l_pk5_column_name VARCHAR2(200);
300
301 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
302
303 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
304 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
305 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
306
307 l_ext_attr_value_pairs_ind NUMBER;
308 l_ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
309
310
311 BEGIN
312
313 d_progress := 10;
314
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
317 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
318
319 d_progress := 20;
320
321 SELECT ENTITY_CODE
322 INTO l_entity_code
323 FROM PO_UDA_AG_TEMPLATES
324 WHERE TEMPLATE_ID = p_template_id;
325
326 d_progress := 30;
327
328 IF l_entity_code = 'PO_REQ_HEADER_EXT_ATTRS' THEN
329 FOR c_por_header_ext_rec IN c_por_header_ext(p_pk1_value) LOOP
330
331 d_progress := 40;
332
333 SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
334 INTO l_ag_type, l_ag_name
335 FROM EGO_FND_DSC_FLX_CTX_EXT
336 WHERE ATTR_GROUP_ID = c_por_header_ext_rec.ATTR_GROUP_ID;
337
338 d_progress := 50;
339
340 l_attr_list := '';
341
342 FOR c_por_attr_list_rec IN c_por_attr_list(l_ag_type, l_ag_name) LOOP
343 IF l_attr_list IS NULL OR l_attr_list = '' THEN
344 l_attr_list := c_por_attr_list_rec.END_USER_COLUMN_NAME;
345 ELSE
346 l_attr_list := l_attr_list || ',' || c_por_attr_list_rec.END_USER_COLUMN_NAME;
347 END IF;
348 d_progress := 60;
349 END LOOP;
350
351 l_data_level := 'REQ_HEADER';
352
353 d_progress := 70;
354
355 IF l_attr_list IS NOT NULL THEN
356 is_valid_entity_ag := TRUE;
357 l_attr_group_request_table.EXTEND(1);
358 l_attr_group_request_table(l_attr_group_request_table.COUNT) := ego_attr_group_request_obj
359 (
360 c_por_header_ext_rec.ATTR_GROUP_ID
361 ,201
362 ,l_ag_type
363 ,l_ag_name
364 ,l_data_level
365 ,NULL
366 ,NULL
367 ,NULL
368 ,NULL
369 ,NULL
370 ,l_attr_list
371 );
372 d_progress := 80;
373 END IF;
374
375 END LOOP;
376 END IF;
377
378 d_progress := 90;
379
380 IF l_entity_code = 'PO_REQ_LINE_EXT_ATTRS' THEN
381 FOR c_por_line_ext_rec IN c_por_line_ext(p_pk1_value) LOOP
382
383 d_progress := 40;
384
385 SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
386 INTO l_ag_type, l_ag_name
387 FROM EGO_FND_DSC_FLX_CTX_EXT
388 WHERE ATTR_GROUP_ID = c_por_line_ext_rec.ATTR_GROUP_ID;
389
390 d_progress := 50;
391
392 l_attr_list := '';
393
394 FOR c_por_attr_list_rec IN c_por_attr_list(l_ag_type, l_ag_name) LOOP
395 IF l_attr_list IS NULL OR l_attr_list = '' THEN
396 l_attr_list := c_por_attr_list_rec.END_USER_COLUMN_NAME;
397 ELSE
398 l_attr_list := l_attr_list || ',' || c_por_attr_list_rec.END_USER_COLUMN_NAME;
399 END IF;
400 d_progress := 60;
401 END LOOP;
402
403 l_data_level := 'REQ_LINE';
404
405 d_progress := 70;
406
407 IF l_attr_list IS NOT NULL THEN
408 is_valid_entity_ag := TRUE;
409 l_attr_group_request_table.EXTEND(1);
410 l_attr_group_request_table(l_attr_group_request_table.COUNT) := ego_attr_group_request_obj
411 (
412 c_por_line_ext_rec.ATTR_GROUP_ID
413 ,201
414 ,l_ag_type
415 ,l_ag_name
416 ,l_data_level
417 ,NULL
418 ,NULL
419 ,NULL
420 ,NULL
421 ,NULL
422 ,l_attr_list
423 );
424 d_progress := 80;
425 END IF;
426
427 END LOOP;
428 END IF;
429
430 d_progress := 90;
431
432 IF is_valid_entity_ag THEN
433
434
435 select o.pk1_column_name,
436 o.pk2_column_name,
437 o.pk3_column_name,
438 o.pk4_column_name,
439 o.pk5_column_name
440 into l_pk1_column_name,
441 l_pk2_column_name,
442 l_pk3_column_name,
443 l_pk4_column_name,
444 l_pk5_column_name
445 from fnd_objects_vl o
446 where o.obj_name = PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_code).l_object_name;
447
448 IF p_pk1_value IS NOT NULL THEN
449 l_pk_column_name_value_pairs.EXTEND(1);
450 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
451 (
452 l_pk1_column_name, p_pk1_value
453 );
454 END IF;
455 IF p_pk2_value IS NOT NULL THEN
456 l_pk_column_name_value_pairs.EXTEND(1);
457 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
458 (
459 l_pk2_column_name, p_pk2_value
460 );
461 END IF;
462 IF p_pk3_value IS NOT NULL THEN
463 l_pk_column_name_value_pairs.EXTEND(1);
464 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
465 (
466 l_pk3_column_name, p_pk3_value
467 );
468 END IF;
469 IF p_pk4_value IS NOT NULL THEN
470 l_pk_column_name_value_pairs.EXTEND(1);
471 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
472 (
473 l_pk4_column_name, p_pk4_value
474 );
475 END IF;
476 IF p_pk5_value IS NOT NULL THEN
477 l_pk_column_name_value_pairs.EXTEND(1);
478 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.COUNT) := EGO_COL_NAME_VALUE_PAIR_OBJ
479 (
480 l_pk5_column_name, p_pk5_value
481 );
482 END IF;
483
484 d_progress := 100;
485
486
487 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
488 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
489
490 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
491 (
492 EGO_COL_NAME_VALUE_PAIR_OBJ
493 (
494 'UDA_TEMPLATE_ID'
495 ,p_template_id || ''
496 )
497 );
498
499 d_progress := 110;
500
501 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
502
503 ego_user_attrs_data_pub.Get_User_Attrs_Data
504 (
505 p_api_version => 1.0
506 ,p_object_name => PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_code).l_object_name
507 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
508 ,p_attr_group_request_table => l_attr_group_request_table
509 ,x_attributes_row_table => l_attributes_row_table
510 ,x_attributes_data_table => l_attr_name_value_pairs
511 ,x_return_status => x_return_status
512 ,x_errorcode => x_errorcode
513 ,x_msg_count => x_msg_count
514 ,x_msg_data => x_msg_data
515 );
516
517 END IF;
518
519 d_progress := 120;
520
521 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
522
523 d_progress := 130;
524
525 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
526 (
527 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).VALUE
528 ,p_event => 'XSC'
529 ,x_external_attr_value_pairs => x_external_attr_value_pairs
530 ,x_pk_column_name_value_pairs => l_pk_column_name_value_pairs
531 ,x_attr_name_value_pairs => l_attr_name_value_pairs
532 ,x_attributes_row_table => l_attributes_row_table
533 ,x_return_status => x_return_status
534 ,x_errorcode => x_errorcode
535 ,x_msg_count => x_msg_count
536 ,x_msg_data => x_msg_data
537 );
538
539
540 /* l_ext_attr_value_pairs_ind := x_external_attr_value_pairs.FIRST;
541
542 WHILE (l_ext_attr_value_pairs_ind IS NOT NULL AND l_ext_attr_value_pairs_ind <= x_external_attr_value_pairs.LAST)
543 LOOP
544 l_ext_name_val_obj := x_external_attr_value_pairs(l_ext_attr_value_pairs_ind);
545 IF l_ext_name_val_obj IS NOT NULL THEN
546 IF l_ext_name_val_obj.NAME = 'x_return_status' THEN
547 uda_return_status := l_ext_name_val_obj.VALUE ;
548 /* ELSIF l_ext_name_val_obj.NAME = 'x_msg_data' THEN
549 uda_msg_data := l_ext_name_val_obj.VALUE ;
550 END IF;
551 END IF;
552 l_ext_attr_value_pairs_ind := x_external_attr_value_pairs.NEXT(l_ext_attr_value_pairs_ind);
553 END LOOP;*/
554
555 PO_UDA_DEFAULTING_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', x_external_attr_value_pairs, l_ext_name_val_obj);
556 uda_return_status := l_ext_name_val_obj.VALUE ;
557
558 d_progress := 140;
559
560 END IF;
561
562 END IF;
563
564
565 EXCEPTION
566 WHEN OTHERS THEN
567 PO_MESSAGE_S.add_exc_msg
568 (
569 p_pkg_name => 'POR_UDA_PKG',
570 p_procedure_name => 'UDA_SUBMISSION_CHECK' || '.' || d_progress
571 );
572
573 x_return_status := 'U';
574 END UDA_SUBMISSION_CHECK;
575
576 PROCEDURE get_pr_header_attr_group_ids(
577 p_template_id IN VARCHAR2,
578 p_req_header_id IN NUMBER,
579 p_is_amd_mode VARCHAR2,
580 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
581 x_return_status OUT NOCOPY VARCHAR2
582 )IS
583
584 d_api_name CONSTANT VARCHAR2(30) := 'get_pr_header_attr_group_ids';
585 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
586 d_progress NUMBER := 0;
587
588 l_po_uda_usage_object po_uda_usage_object ;
589 l_attr_grp_ids_list PO_TBL_NUMBER;
590 l_x_attr_ids_count NUMBER;
591 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
592 l_attr_grp_ids PO_TBL_NUMBER;
593 t_count NUMBER;
594
595 CURSOR c_get_usages(p_template_id NUMBER) IS
596 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
597 WHERE template_id = p_template_id;
598
599 BEGIN
600 IF (PO_LOG.d_proc) THEN
601 PO_LOG.proc_begin(d_module);
602 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
603 END IF;
604
605 l_attr_grp_ids := PO_TBL_NUMBER();
606 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY();
607
608 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
609 IF(p_is_amd_mode = 'true' AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
610 IF PO_LOG.d_stmt THEN
611 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
612 END IF;
613
614 ELSE
615 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
616 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
617 t_count := l_attr_grp_ids_list.Count;
618
619 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
620 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
621 l_attr_grp_ids.extend(1);
622 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
623 END LOOP;
624 END IF;
625 END LOOP;
626
627 x_attr_grp_ids := l_attr_grp_ids;
628 x_return_status := 'S';
629
630 IF PO_LOG.d_stmt THEN
631 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
632 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
633 END IF;
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 d_progress := 40;
638 x_return_status := 'U';
639 IF PO_LOG.d_stmt THEN
640 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
641 END IF;
642 END get_pr_header_attr_group_ids;
643
644 PROCEDURE merge_amendment_uda_header(p_confirmed_id IN NUMBER,
645 p_revision_num IN NUMBER )
646 IS
647
648 d_api_name CONSTANT VARCHAR2(30) := 'merge_amendment_uda_header';
649 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
650 d_progress NUMBER := 0;
651
652 l_template_id NUMBER;
653 l_from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
654 l_to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
655 l_copy_attribute_groups VARCHAR2(10);
656 l_attribute_group_table PO_TBL_VARCHAR30;
657 l_attr_group_name VARCHAR2(250);
658
659 l_return_status VARCHAR2(2);
660 l_msg_count NUMBER;
661 l_msg_data VARCHAR2(2000);
662 l_req_source_hdr_id NUMBER;
663
664 CURSOR get_source_ids(p_confirmed_id NUMBER, p_revision_num NUMBER ) IS
665 SELECT requisition_header_id, uda_template_id FROM po_requisition_headers_all
666 WHERE conformed_header_id = p_confirmed_id
667 AND revision_num = p_revision_num;
668
669 CURSOR c_get_add_agids_tbl(l_template_id NUMBER) IS
670 SELECT ATTRIBUTE_GROUP_ID
671 FROM po_uda_ag_template_usages WHERE template_id = l_template_id
672 AND ATTRIBUTE_CATEGORY = 'ADDRESS';
673
674 BEGIN
675
676 IF (PO_LOG.d_proc) THEN
677 PO_LOG.proc_begin(d_module);
678 PO_LOG.proc_begin(d_module, 'p_confirmed_id', p_confirmed_id);
679 PO_LOG.proc_begin(d_module, 'p_revision_num', p_revision_num);
680 END IF;
681
682 OPEN get_source_ids(p_confirmed_id, p_revision_num);
683 FETCH get_source_ids INTO l_req_source_hdr_id, l_template_id ;
684 CLOSE get_source_ids;
685
686 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
687 EGO_COL_NAME_VALUE_PAIR_OBJ(
688 'REQUISITION_HEADER_ID', l_req_source_hdr_id ));
689
690 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
691 EGO_COL_NAME_VALUE_PAIR_OBJ(
692 'REQUISITION_HEADER_ID', p_confirmed_id ));
693
694 l_attribute_group_table := PO_TBL_VARCHAR30();
695 FOR c_get_add_agids_tbl_rec IN c_get_add_agids_tbl(l_template_id) LOOP
696 l_attribute_group_table.extend(1);
697
698 SELECT attr_group_name INTO l_attr_group_name
699 FROM EGO_ATTR_GROUPS_V
700 WHERE ATTR_GROUP_TYPE = 'PO_REQ_HEADER_EXT_ATTRS' AND ATTR_GROUP_ID = c_get_add_agids_tbl_rec.ATTRIBUTE_GROUP_ID;
701
702 l_attribute_group_table(l_attribute_group_table.Count) := l_attr_group_name;
703
704 END LOOP;
705
706 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
707 (
708 from_template_id => l_template_id
709 ,to_template_id => l_template_id
710 ,from_pk_col_value_pairs => l_from_pk_col_value_pairs
711 ,to_pk_col_value_pairs => l_to_pk_col_value_pairs
712 ,copy_attribute_groups => 'EXCLUSIVE' -- SPECIFIC / EXCLUSIVE
713 ,attribute_group_table => l_attribute_group_table
714 ,p_commit => FND_API.G_FALSE
715 ,x_return_status => l_return_status
716 ,x_msg_count => l_msg_count
717 ,x_msg_data => l_msg_data
718 );
719
720 IF PO_LOG.d_stmt THEN
721 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
722 END IF;
723
724
725 PO_UDA_DATA_UTIL.Merge_Amendment_Address_AG(x_amd_requisition_header_id => l_req_source_hdr_id
726 ,x_requisition_header_id => p_confirmed_id
727 ,x_return_status => l_return_status );
728
729 IF PO_LOG.d_stmt THEN
730 PO_LOG.stmt(d_module, d_progress, 'Merge_Amendment_Address_AG: l_return_status ', l_return_status);
731 END IF;
732
733 EXCEPTION
734 WHEN OTHERS THEN
735 d_progress := 40;
736 -- x_return_status := 'U';
737 /* IF PO_LOG.d_stmt THEN
738 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
739 END IF; */
740 END merge_amendment_uda_header ;
741
742
743 PROCEDURE merge_amendment_uda_line(p_confirmed_id IN NUMBER,
744 p_revision_num IN NUMBER )
745 IS
746
747 d_api_name CONSTANT VARCHAR2(30) := 'merge_amendment_uda_line';
748 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
749 d_progress NUMBER := 0;
750
751 l_template_id NUMBER;
752 l_from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
753 l_to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
754 l_copy_attribute_groups VARCHAR2(10);
755
756 l_return_status VARCHAR2(2);
757 l_msg_count NUMBER;
758 l_msg_data VARCHAR2(2000);
759 l_req_source_hdr_id NUMBER;
760 l_line_ids Dbms_Sql.number_table;
761 l_conformed_line_ids Dbms_Sql.number_table;
762
763 CURSOR get_req_header_id(p_confirmed_id NUMBER, p_revision_num NUMBER) IS
764 SELECT requisition_header_id FROM po_requisition_headers_all
765 WHERE conformed_header_id = p_confirmed_id
766 AND revision_num = p_revision_num;
767
768 BEGIN
769
770 IF (PO_LOG.d_proc) THEN
771 PO_LOG.proc_begin(d_module);
772 PO_LOG.proc_begin(d_module, 'p_confirmed_id', p_confirmed_id);
773 PO_LOG.proc_begin(d_module, 'p_revision_num', p_revision_num);
774 END IF;
775
776 OPEN get_req_header_id(p_confirmed_id, p_revision_num);
777 FETCH get_req_header_id INTO l_req_source_hdr_id;
778 CLOSE get_req_header_id;
779
780 SELECT requisition_line_id , conformed_line_id
781 BULK COLLECT INTO l_line_ids , l_conformed_line_ids
782 FROM po_requisition_lines_all
783 WHERE requisition_header_id = l_req_source_hdr_id;
784 -- AND group_line_id IS NULL
785 -- AND clm_base_line_num IS NULL;
786
787 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
788 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
789
790 l_from_pk_col_value_pairs.extend(l_line_ids.Count);
791 l_to_pk_col_value_pairs.extend(l_line_ids.Count);
792
793 FOR i IN 1..l_line_ids.Count LOOP
794 IF l_conformed_line_ids(i) IS NOT NULL THEN
795
796 SELECT uda_template_id INTO l_template_id
797 FROM po_requisition_lines_all WHERE REQUISITION_LINE_ID = l_line_ids(i);
798
799 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
800 'REQUISITION_LINE_ID', l_line_ids(i)));
801
802 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
803 'REQUISITION_LINE_ID', l_conformed_line_ids(i)));
804 END IF;
805
806 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
807 (
808 from_template_id => l_template_id
809 ,to_template_id => l_template_id
810 ,from_pk_col_value_pairs => l_from_pk_col_value_pairs
811 ,to_pk_col_value_pairs => l_to_pk_col_value_pairs
812 ,copy_attribute_groups => 'ALL' -- SPECIFIC / EXCLUSIVE
813 ,p_commit => FND_API.G_FALSE
814 ,x_return_status => l_return_status
815 ,x_msg_count => l_msg_count
816 ,x_msg_data => l_msg_data
817 );
818 END LOOP;
819
820 IF (PO_LOG.d_proc) THEN
821 PO_LOG.proc_begin(d_module);
822 PO_LOG.proc_begin(d_module, 'l_return_status', l_return_status);
823 END IF;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 d_progress := 40;
828 /* x_return_status := 'U';
829 IF PO_LOG.d_stmt THEN
830 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
831 END IF;
832 */
833 END merge_amendment_uda_line ;
834
835 PROCEDURE copy_uda_header(p_from_req_header_id IN number,
836 p_to_req_header_id IN number,
837 x_return_status OUT NOCOPY VARCHAR2,
838 x_return_msg OUT NOCOPY varchar2)
839 IS
840
841 d_api_name CONSTANT VARCHAR2(30) := 'copy_uda_header';
842 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
843 d_progress NUMBER := 0;
844
845 l_template_id NUMBER;
846 l_from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
847 l_to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
848 l_copy_attribute_groups VARCHAR2(10);
849
850 l_return_status VARCHAR2(2);
851 l_msg_count NUMBER;
852 l_msg_data VARCHAR2(2000);
853
854 CURSOR get_source_ids(p_from_req_header_id NUMBER) IS
855 SELECT uda_template_id FROM po_requisition_headers_all
856 WHERE requisition_header_id = p_from_req_header_id;
857
858 BEGIN
859
860 IF (PO_LOG.d_proc) THEN
861 PO_LOG.proc_begin(d_module);
862 PO_LOG.proc_begin(d_module, 'p_from_req_header_id', p_from_req_header_id);
863 PO_LOG.proc_begin(d_module, 'p_to_req_header_id', p_to_req_header_id);
864 END IF;
865
866 OPEN get_source_ids(p_from_req_header_id);
867 FETCH get_source_ids INTO l_template_id ;
868 CLOSE get_source_ids;
869
870 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
871 EGO_COL_NAME_VALUE_PAIR_OBJ(
872 'REQUISITION_HEADER_ID', p_from_req_header_id ));
873
874 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
875 EGO_COL_NAME_VALUE_PAIR_OBJ(
876 'REQUISITION_HEADER_ID', p_to_req_header_id ));
877
878 PO_UDA_DATA_UTIL.Copy_User_Attrs
879 (
880 x_template_id => l_template_id
881 ,x_from_pk_col_value_pairs => l_from_pk_col_value_pairs
882 ,x_to_pk_col_value_pairs => l_to_pk_col_value_pairs
883 ,x_copy_attribute_groups => 'ALL' -- SPECIFIC / EXCLUSIVE
884 ,x_commit => FND_API.G_FALSE
885 ,x_return_status => l_return_status
886 ,x_msg_count => l_msg_count
887 ,x_msg_data => l_msg_data
888 );
889
890 IF (PO_LOG.d_proc) THEN
891 PO_LOG.proc_begin(d_module);
892 PO_LOG.proc_begin(d_module, 'l_return_status', l_return_status);
893 END IF;
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 d_progress := 40;
898 x_return_status := 'U';
899 IF PO_LOG.d_stmt THEN
900 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
901 END IF;
902 END copy_uda_header ;
903
904
905 PROCEDURE copy_uda_line(p_from_req_header_id IN NUMBER,
906 p_to_req_header_id IN number,
907 x_return_status OUT NOCOPY VARCHAR2,
908 x_return_msg OUT NOCOPY varchar2)
909 IS
910
911 d_api_name CONSTANT VARCHAR2(30) := 'copy_uda_line';
912 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
913 d_progress NUMBER := 0;
914
915 l_template_id NUMBER;
916 l_from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
917 l_to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
918 l_copy_attribute_groups VARCHAR2(10);
919
920 l_return_status VARCHAR2(2);
921 l_msg_count NUMBER;
922 l_msg_data VARCHAR2(2000);
923 l_req_source_hdr_id NUMBER;
924 l_from_line_ids Dbms_Sql.number_table;
925 l_to_line_ids Dbms_Sql.number_table;
926
927 BEGIN
928
929 IF (PO_LOG.d_proc) THEN
930 PO_LOG.proc_begin(d_module);
931 PO_LOG.proc_begin(d_module, 'p_from_req_header_id', p_from_req_header_id);
932 PO_LOG.proc_begin(d_module, 'p_to_req_header_id', p_to_req_header_id);
933 END IF;
934
935
936 SELECT requisition_line_id
937 BULK COLLECT INTO l_from_line_ids
938 FROM po_requisition_lines_all
939 WHERE requisition_header_id = p_from_req_header_id;
940
941 SELECT requisition_line_id
942 BULK COLLECT INTO l_to_line_ids
943 FROM po_requisition_lines_all
944 WHERE requisition_header_id = p_to_req_header_id;
945
946 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
947 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
948
949 l_from_pk_col_value_pairs.extend(l_from_line_ids.Count);
950 l_to_pk_col_value_pairs.extend(l_to_line_ids.Count);
951
952 FOR i IN 1..l_from_line_ids.Count LOOP
953 IF l_to_line_ids(i) IS NOT NULL THEN
954
955 SELECT uda_template_id INTO l_template_id
956 FROM po_requisition_lines_all WHERE REQUISITION_LINE_ID = l_from_line_ids(i);
957
958 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
959 'REQUISITION_LINE_ID', l_from_line_ids(i)));
960
961 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ(
962 'REQUISITION_LINE_ID', l_to_line_ids(i)));
963 END IF;
964
965 PO_UDA_DATA_UTIL.Copy_User_Attrs
966 (
967 x_template_id => l_template_id
968 ,x_from_pk_col_value_pairs => l_from_pk_col_value_pairs
969 ,x_to_pk_col_value_pairs => l_to_pk_col_value_pairs
970 ,x_copy_attribute_groups => 'ALL' -- SPECIFIC / EXCLUSIVE
971 ,x_commit => FND_API.G_FALSE
972 ,x_return_status => l_return_status
973 ,x_msg_count => l_msg_count
974 ,x_msg_data => l_msg_data
975 );
976
977 END LOOP;
978
979 IF (PO_LOG.d_proc) THEN
980 PO_LOG.proc_begin(d_module);
981 PO_LOG.proc_begin(d_module, 'l_return_status', l_return_status);
982 END IF;
983
984 EXCEPTION
985 WHEN OTHERS THEN
986 d_progress := 40;
987 x_return_status := 'U';
988 IF PO_LOG.d_stmt THEN
989 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
990 END IF;
991 END copy_uda_line ;
992
993 PROCEDURE DEFAULT_NON_RENDER_UDA( p_req_header_id IN NUMBER,
994 p_is_amend_flow IN VARCHAR,
995 x_return_status OUT NOCOPY VARCHAR2)
996 IS
997
998 d_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_NON_RENDER_UDA';
999 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1000 d_progress NUMBER := 0;
1001
1002 l_po_uda_usage_object_array PO_UDA_USAGE_OBJECT_ARRAY;
1003 l_template_id NUMBER;
1004 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
1005 l_line_ext_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
1006 l_po_uda_usage_object PO_UDA_USAGE_OBJECT;
1007 l_contract_type VARCHAR2(500);
1008 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
1009 l_address_lookup_type VARCHAR2(200);
1010 l_preparer_id NUMBER;
1011 l_org_id NUMBER;
1012 l_line_type_id NUMBER;
1013 l_contract_line_type VARCHAR2(100);
1014 l_item_id NUMBER;
1015 l_deliver_to_location_id NUMBER;
1016 l_attr_grp_ids_list PO_TBL_NUMBER;
1017
1018 l_return_status VARCHAR2(2);
1019 l_errorcode NUMBER;
1020 l_msg_count NUMBER;
1021 l_msg_data VARCHAR2(4000);
1022 t_count NUMBER;
1023
1024 CURSOR c_get_usages(p_template_id NUMBER) IS
1025 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
1026 WHERE template_id = p_template_id
1027 AND ATTRIBUTE_CATEGORY <> 'DOCUMENT_NUMBERING';
1028
1029 CURSOR c_get_lines(p_req_header_id NUMBER) IS
1030 SELECT requisition_line_id FROM po_requisition_lines_all
1031 WHERE requisition_header_id = p_req_header_id;
1032
1033 CURSOR c_get_line_usages(p_template_id NUMBER) IS
1034 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
1035 WHERE template_id = p_template_id;
1036
1037 CURSOR c_get_usage_context(p_req_line_id NUMBER) IS
1038 SELECT CONTRACT_TYPE FROM po_requisition_lines_all
1039 WHERE requisition_line_id = p_req_line_id;
1040
1041
1042 BEGIN
1043 IF (PO_LOG.d_proc) THEN
1044 PO_LOG.proc_begin(d_module);
1045 PO_LOG.proc_begin(d_module, 'p_req_header_id', p_req_header_id);
1046 END IF;
1047
1048 SELECT uda_template_id INTO l_template_id
1049 FROM po_requisition_headers_all WHERE requisition_header_id = p_req_header_id;
1050
1051 l_po_uda_usage_object := PO_UDA_USAGE_OBJECT();
1052 l_po_uda_usage_object_array := PO_UDA_USAGE_OBJECT_ARRAY();
1053 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY();
1054
1055 IF p_is_amend_flow = 'true' THEN
1056 l_address_lookup_type := 'PR_AMD_UDA_ADDRESS_TYPES';
1057 ELSE
1058 l_address_lookup_type := 'PR_UDA_ADDRESS_TYPES';
1059 END IF;
1060
1061 FOR c_get_usages_rec IN c_get_usages(l_template_id) LOOP
1062
1063 l_po_uda_usage_object := po_uda_usage_object.new_instance(l_template_id,c_get_usages_rec.attribute_category,l_context );
1064 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
1065
1066 IF po_uda_defaulting_pkg.is_valid_ag_list (p_req_header_id, null, null, null, null, l_template_id, l_attr_grp_ids_list)
1067 THEN
1068 l_po_uda_usage_object_array.EXTEND(1);
1069 l_po_uda_usage_object_array(l_po_uda_usage_object_array.COUNT) := l_po_uda_usage_object;
1070 END IF;
1071 END LOOP;
1072
1073 SELECT preparer_id, org_id INTO l_preparer_id, l_org_id
1074 FROM po_requisition_headers_all
1075 WHERE requisition_header_id = p_req_header_id;
1076
1077 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('PREPARER_ID', l_preparer_id || '', l_external_attr_value_pairs);
1078 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('ORG_ID', l_org_id || '', l_external_attr_value_pairs);
1079
1080 PO_UDA_DEFAULTING_PKG.DEFAULT_AGS_FROM_USAGE_OBJECT
1081 (
1082 p_pk1_value => p_req_header_id
1083 ,p_template_id => l_template_id
1084 ,p_address_lookup_type => l_address_lookup_type
1085 ,x_external_attr_value_pairs => l_external_attr_value_pairs
1086 ,x_po_uda_usage_object_array => l_po_uda_usage_object_array
1087 ,x_return_status => l_return_status
1088 ,x_errorcode => l_errorcode
1089 ,x_msg_count => l_msg_count
1090 ,x_msg_data => l_msg_data
1091 );
1092
1093 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1094
1095 --Loop through all the lines for this PR to default UDA's
1096 FOR c_get_lines_rec IN c_get_lines(p_req_header_id) LOOP
1097
1098 SELECT uda_template_id INTO l_template_id
1099 FROM po_requisition_lines_all
1100 WHERE requisition_line_id = c_get_lines_rec.requisition_line_id;
1101
1102 l_po_uda_usage_object := PO_UDA_USAGE_OBJECT();
1103 l_po_uda_usage_object_array := PO_UDA_USAGE_OBJECT_ARRAY();
1104
1105 FOR c_get_line_usages_rec IN c_get_line_usages(l_template_id) LOOP
1106
1107 IF (c_get_line_usages_rec.attribute_category = 'PRICING') then
1108 OPEN c_get_usage_context(c_get_lines_rec.requisition_line_id);
1109 FETCH c_get_usage_context INTO l_contract_type;
1110 CLOSE c_get_usage_context;
1111
1112 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTRACT_LINE_TYPE', l_contract_type));
1113 END IF;
1114
1115 l_po_uda_usage_object := po_uda_usage_object.new_instance(l_template_id,c_get_line_usages_rec.attribute_category,l_context );
1116 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
1117
1118 IF po_uda_defaulting_pkg.is_valid_ag_list (p_req_header_id, null, null, null, null, l_template_id, l_attr_grp_ids_list)
1119 THEN
1120 l_po_uda_usage_object_array.EXTEND(1);
1121 l_po_uda_usage_object_array(l_po_uda_usage_object_array.COUNT) := l_po_uda_usage_object;
1122 END IF;
1123 END LOOP;
1124
1125 SELECT line_type_id, contract_type, item_id, deliver_to_location_id
1126 INTO l_line_type_id, l_contract_line_type, l_item_id, l_deliver_to_location_id
1127 FROM po_requisition_lines_all
1128 WHERE requisition_line_id = c_get_lines_rec.requisition_line_id;
1129
1130 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('ITEM_ID', l_item_id || '', l_external_attr_value_pairs);
1131 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('CONTRACT_TYPE', l_contract_line_type || '', l_external_attr_value_pairs);
1132 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('LINE_TYPE_ID', l_line_type_id || '', l_external_attr_value_pairs);
1133 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('SHIP_TO_LOC_ID', l_deliver_to_location_id || '', l_external_attr_value_pairs);
1134
1135 PO_UDA_DEFAULTING_PKG.DEFAULT_AGS_FROM_USAGE_OBJECT
1136 (
1137 p_pk1_value => c_get_lines_rec.requisition_line_id
1138 ,p_template_id => l_template_id
1139 ,x_external_attr_value_pairs => l_external_attr_value_pairs
1140 ,x_po_uda_usage_object_array => l_po_uda_usage_object_array
1141 ,x_return_status => l_return_status
1142 ,x_errorcode => l_errorcode
1143 ,x_msg_count => l_msg_count
1144 ,x_msg_data => l_msg_data
1145 );
1146
1147 END LOOP;
1148 END IF;
1149 x_return_status := l_return_status;
1150
1151 IF (PO_LOG.d_proc) THEN
1152 PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
1153 END IF;
1154
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 d_progress := 40;
1158 x_return_status := 'U';
1159 IF PO_LOG.d_stmt THEN
1160 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
1161 END IF;
1162
1163 END DEFAULT_NON_RENDER_UDA;
1164
1165
1166 procedure COPY_DOC_NUMBER_UDA(p_old_header_id IN NUMBER
1167 ,p_new_header_id IN NUMBER
1168 , p_template_id IN NUMBER
1169 , x_return_status OUT NOCOPY VARCHAR2)
1170
1171 IS
1172
1173 l_old_hdr_ext_id NUMBER;
1174 l_new_hdr_ext_id NUMBER;
1175
1176 d_api_name CONSTANT VARCHAR2(30) := 'copy_docNumber_UDA';
1177 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1178 d_progress NUMBER := 0;
1179
1180
1181 CURSOR po_req_headers_ext_cursor IS
1182 SELECT extension_id, EGO_EXTFWK_S.NEXTVAL
1183 FROM PO_REQ_HEADERS_EXT_B
1184 WHERE REQUISITION_HEADER_ID = p_old_header_id
1185 AND ATTR_GROUP_ID in ( SELECT ATTR_GROUP_ID FROM
1186 PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
1187 WHERE usages.template_id = p_template_id
1188 AND usages.ATTRIBUTE1='Base Document'
1189 AND usages.ATTRIBUTE2='NA'
1190 AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
1191 AND usages.attribute_group_id = ags.attr_group_id);
1192
1193 BEGIN
1194
1195
1196 IF (PO_LOG.d_proc) THEN
1197 PO_LOG.proc_begin(d_module);
1198 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
1199 PO_LOG.proc_begin(d_module, 'p_old_hdr_id', p_old_header_id);
1200 PO_LOG.proc_begin(d_module, 'p_new_hdr_id', p_new_header_id);
1201
1202 END IF;
1203
1204 open po_req_headers_ext_cursor ;
1205 fetch po_req_headers_ext_cursor into l_old_hdr_ext_id,l_new_hdr_ext_id;
1206 CLOSE po_req_headers_ext_cursor;
1207
1208 insert into PO_REQ_HEADERS_EXT_B(
1209 extension_id
1210 ,attr_group_id
1211 ,requisition_header_id
1212 ,data_level_id
1213 ,pk1_value
1214 ,pk2_value
1215 ,pk3_value
1216 ,pk4_value
1217 ,pk5_value
1218 ,last_update_date
1219 ,last_updated_by
1220 ,last_update_login
1221 ,created_by
1222 ,creation_date
1223 ,c_ext_attr1
1224 ,c_ext_attr2
1225 ,c_ext_attr3
1226 ,c_ext_attr4
1227 ,c_ext_attr5
1228 ,c_ext_attr6
1229 ,c_ext_attr7
1230 ,c_ext_attr8
1231 ,c_ext_attr9
1232 ,c_ext_attr10
1233 ,c_ext_attr11
1234 ,c_ext_attr12
1235 ,c_ext_attr13
1236 ,c_ext_attr14
1237 ,c_ext_attr15
1238 ,c_ext_attr16
1239 ,c_ext_attr17
1240 ,c_ext_attr18
1241 ,c_ext_attr19
1242 ,c_ext_attr20
1243 ,c_ext_attr21
1244 ,c_ext_attr22
1245 ,c_ext_attr23
1246 ,c_ext_attr24
1247 ,c_ext_attr25
1248 ,c_ext_attr26
1249 ,c_ext_attr27
1250 ,c_ext_attr28
1251 ,c_ext_attr29
1252 ,c_ext_attr30
1253 ,c_ext_attr31
1254 ,c_ext_attr32
1255 ,c_ext_attr33
1256 ,c_ext_attr34
1257 ,c_ext_attr35
1258 ,c_ext_attr36
1259 ,c_ext_attr37
1260 ,c_ext_attr38
1261 ,c_ext_attr39
1262 ,c_ext_attr40
1263 ,n_ext_attr1
1264 ,n_ext_attr2
1265 ,n_ext_attr3
1266 ,n_ext_attr4
1267 ,n_ext_attr5
1268 ,n_ext_attr6
1269 ,n_ext_attr7
1270 ,n_ext_attr8
1271 ,n_ext_attr9
1272 ,n_ext_attr10
1273 ,n_ext_attr11
1274 ,n_ext_attr12
1275 ,n_ext_attr13
1276 ,n_ext_attr14
1277 ,n_ext_attr15
1278 ,n_ext_attr16
1279 ,n_ext_attr17
1280 ,n_ext_attr18
1281 ,n_ext_attr19
1282 ,n_ext_attr20
1283 ,uom_ext_attr1
1284 ,uom_ext_attr2
1285 ,uom_ext_attr3
1286 ,uom_ext_attr4
1287 ,uom_ext_attr5
1288 ,uom_ext_attr6
1289 ,uom_ext_attr7
1290 ,uom_ext_attr8
1291 ,uom_ext_attr9
1292 ,uom_ext_attr10
1293 ,uom_ext_attr11
1294 ,uom_ext_attr12
1295 ,uom_ext_attr13
1296 ,uom_ext_attr14
1297 ,uom_ext_attr15
1298 ,uom_ext_attr16
1299 ,uom_ext_attr17
1300 ,uom_ext_attr18
1301 ,uom_ext_attr19
1302 ,uom_ext_attr20
1303 ,d_ext_attr1
1304 ,d_ext_attr2
1305 ,d_ext_attr3
1306 ,d_ext_attr4
1307 ,d_ext_attr5
1308 ,d_ext_attr6
1309 ,d_ext_attr7
1310 ,d_ext_attr8
1311 ,d_ext_attr9
1312 ,d_ext_attr10
1313 ,uda_template_id
1314 )
1315
1316 select
1317 l_new_hdr_ext_id
1318 ,attr_group_id
1319 ,p_new_header_id
1320 ,data_level_id
1321 ,pk1_value
1322 ,pk2_value
1323 ,pk3_value
1324 ,pk4_value
1325 ,pk5_value
1326 ,SYSDATE
1327 ,FND_GLOBAL.user_id
1328 ,FND_GLOBAL.login_id
1329 ,FND_GLOBAL.user_id
1330 ,SYSDATE
1331 ,c_ext_attr1
1332 ,c_ext_attr2
1333 ,c_ext_attr3
1334 ,c_ext_attr4
1335 ,c_ext_attr5
1336 ,c_ext_attr6
1337 ,c_ext_attr7
1338 ,c_ext_attr8
1339 ,c_ext_attr9
1340 ,c_ext_attr10
1341 ,c_ext_attr11
1342 ,c_ext_attr12
1343 ,c_ext_attr13
1344 ,c_ext_attr14
1345 ,c_ext_attr15
1346 ,c_ext_attr16
1347 ,c_ext_attr17
1348 ,c_ext_attr18
1349 ,c_ext_attr19
1350 ,c_ext_attr20
1351 ,c_ext_attr21
1352 ,c_ext_attr22
1353 ,c_ext_attr23
1354 ,c_ext_attr24
1355 ,c_ext_attr25
1356 ,c_ext_attr26
1357 ,c_ext_attr27
1358 ,c_ext_attr28
1359 ,c_ext_attr29
1360 ,c_ext_attr30
1361 ,c_ext_attr31
1362 ,c_ext_attr32
1363 ,c_ext_attr33
1364 ,c_ext_attr34
1365 ,c_ext_attr35
1366 ,c_ext_attr36
1367 ,c_ext_attr37
1368 ,c_ext_attr38
1369 ,c_ext_attr39
1370 ,'##'||c_ext_attr40
1371 ,n_ext_attr1
1372 ,n_ext_attr2
1373 ,n_ext_attr3
1374 ,n_ext_attr4
1375 ,n_ext_attr5
1376 ,n_ext_attr6
1377 ,n_ext_attr7
1378 ,n_ext_attr8
1379 ,n_ext_attr9
1380 ,n_ext_attr10
1381 ,n_ext_attr11
1382 ,n_ext_attr12
1383 ,n_ext_attr13
1384 ,n_ext_attr14
1385 ,n_ext_attr15
1386 ,n_ext_attr16
1387 ,n_ext_attr17
1388 ,n_ext_attr18
1389 ,n_ext_attr19
1390 ,n_ext_attr20
1391 ,uom_ext_attr1
1392 ,uom_ext_attr2
1393 ,uom_ext_attr3
1394 ,uom_ext_attr4
1395 ,uom_ext_attr5
1396 ,uom_ext_attr6
1397 ,uom_ext_attr7
1398 ,uom_ext_attr8
1399 ,uom_ext_attr9
1400 ,uom_ext_attr10
1401 ,uom_ext_attr11
1402 ,uom_ext_attr12
1403 ,uom_ext_attr13
1404 ,uom_ext_attr14
1405 ,uom_ext_attr15
1406 ,uom_ext_attr16
1407 ,uom_ext_attr17
1408 ,uom_ext_attr18
1409 ,uom_ext_attr19
1410 ,uom_ext_attr20
1411 ,d_ext_attr1
1412 ,d_ext_attr2
1413 ,d_ext_attr3
1414 ,d_ext_attr4
1415 ,d_ext_attr5
1416 ,d_ext_attr6
1417 ,d_ext_attr7
1418 ,d_ext_attr8
1419 ,d_ext_attr9
1420 ,d_ext_attr10
1421 ,p_template_id
1422 from PO_REQ_HEADERS_EXT_B where extension_id = l_old_hdr_ext_id;
1423
1424
1425 d_progress := 10 ;
1426
1427 insert into PO_req_HEADERS_EXT_TL(
1428 extension_id
1429 ,attr_group_id
1430 ,requisition_header_id
1431 ,data_level_id
1432 ,pk1_value
1433 ,pk2_value
1434 ,pk3_value
1435 ,pk4_value
1436 ,pk5_value
1437 ,source_lang
1438 ,LANGUAGE
1439 ,last_update_date
1440 ,last_updated_by
1441 ,last_update_login
1442 ,created_by
1443 ,creation_date
1444 ,tl_ext_attr1
1445 ,tl_ext_attr2
1446 ,tl_ext_attr3
1447 ,tl_ext_attr4
1448 ,tl_ext_attr5
1449 ,tl_ext_attr6
1450 ,tl_ext_attr7
1451 ,tl_ext_attr8
1452 ,tl_ext_attr9
1453 ,tl_ext_attr10
1454 ,tl_ext_attr11
1455 ,tl_ext_attr12
1456 ,tl_ext_attr13
1457 ,tl_ext_attr14
1458 ,tl_ext_attr15
1459 ,tl_ext_attr16
1460 ,tl_ext_attr17
1461 ,tl_ext_attr18
1462 ,tl_ext_attr19
1463 ,tl_ext_attr20
1464 ,tl_ext_attr21
1465 ,tl_ext_attr22
1466 ,tl_ext_attr23
1467 ,tl_ext_attr24
1468 ,tl_ext_attr25
1469 ,tl_ext_attr26
1470 ,tl_ext_attr27
1471 ,tl_ext_attr28
1472 ,tl_ext_attr29
1473 ,tl_ext_attr30
1474 ,tl_ext_attr31
1475 ,tl_ext_attr32
1476 ,tl_ext_attr33
1477 ,tl_ext_attr34
1478 ,tl_ext_attr35
1479 ,tl_ext_attr36
1480 ,tl_ext_attr37
1481 ,tl_ext_attr38
1482 ,tl_ext_attr39
1483 ,tl_ext_attr40
1484 ,uda_template_id
1485 )
1486
1487 select
1488 l_new_hdr_ext_id
1489 ,attr_group_id
1490 ,p_new_header_id
1491 ,data_level_id
1492 ,pk1_value
1493 ,pk2_value
1494 ,pk3_value
1495 ,pk4_value
1496 ,pk5_value
1497 ,source_lang
1498 ,LANGUAGE
1499 ,SYSDATE
1500 ,FND_GLOBAL.user_id
1501 ,FND_GLOBAL.login_id
1502 ,FND_GLOBAL.user_id
1503 ,SYSDATE
1504 ,tl_ext_attr1
1505 ,tl_ext_attr2
1506 ,tl_ext_attr3
1507 ,tl_ext_attr4
1508 ,tl_ext_attr5
1509 ,tl_ext_attr6
1510 ,tl_ext_attr7
1511 ,tl_ext_attr8
1512 ,tl_ext_attr9
1513 ,tl_ext_attr10
1514 ,tl_ext_attr11
1515 ,tl_ext_attr12
1516 ,tl_ext_attr13
1517 ,tl_ext_attr14
1518 ,tl_ext_attr15
1519 ,tl_ext_attr16
1520 ,tl_ext_attr17
1521 ,tl_ext_attr18
1522 ,tl_ext_attr19
1523 ,tl_ext_attr20
1524 ,tl_ext_attr21
1525 ,tl_ext_attr22
1526 ,tl_ext_attr23
1527 ,tl_ext_attr24
1528 ,tl_ext_attr25
1529 ,tl_ext_attr26
1530 ,tl_ext_attr27
1531 ,tl_ext_attr28
1532 ,tl_ext_attr29
1533 ,tl_ext_attr30
1534 ,tl_ext_attr31
1535 ,tl_ext_attr32
1536 ,tl_ext_attr33
1537 ,tl_ext_attr34
1538 ,tl_ext_attr35
1539 ,tl_ext_attr36
1540 ,tl_ext_attr37
1541 ,tl_ext_attr38
1542 ,tl_ext_attr39
1543 ,tl_ext_attr40
1544 ,p_template_id
1545
1546 from PO_req_HEADERS_EXT_TL where extension_id = l_old_hdr_ext_id;
1547
1548
1549 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1550
1551
1552 EXCEPTION
1553 WHEN others THEN
1554
1555 d_progress := 20;
1556 x_return_status := 'U';
1557 IF PO_LOG.d_stmt THEN
1558 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
1559 END IF;
1560 RAISE;
1561
1562 END COPY_DOC_NUMBER_UDA;
1563
1564 END POR_UDA_PKG;