[Home] [Help]
PACKAGE BODY: APPS.PO_DOC_UDA_UTIL
Source
1 package body po_doc_uda_util AS
2 /* $Header: PO_DOC_UDA_UTIL.plb 120.10.12020000.2 2013/02/11 01:54:11 vegajula ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_DOC_UDA_UTIL');
5
6 PROCEDURE DEBUG
7 (
8 p_msg IN VARCHAR2
9 ,p_progress IN NUMBER
10 )
11 IS
12 BEGIN
13 IF PO_LOG.d_stmt THEN
14 PO_LOG.stmt('po_doc_uda_util', p_progress, 'Message: ', p_msg);
15 END IF;
16
17
18 END DEBUG;
19
20 PROCEDURE get_po_header_attr_group_ids(
21 p_template_id IN VARCHAR2,
22 p_po_header_id IN NUMBER,
23 p_is_doc_mod VARCHAR2,
24 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2
26 ) IS
27 d_api_name CONSTANT VARCHAR2(30) := 'get_po_header_attr_group_ids';
28 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
29 d_progress NUMBER := 0;
30 l_po_uda_usage_object po_uda_usage_object ;
31 l_attr_grp_ids_list PO_TBL_NUMBER;
32 l_x_attr_ids_count NUMBER;
33 l_clm_standard_form PO_HEADERS_ALL.CLM_STANDARD_FORM%TYPE;
34 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
35 l_attr_grp_ids PO_TBL_NUMBER;
36 t_count NUMBER;
37
38 cursor c_get_usages(p_template_id number) is
39 select distinct(attribute_category) from po_uda_ag_template_usages
40 where template_id = p_template_id;
41
42 BEGIN
43 IF (PO_LOG.d_proc) THEN
44 PO_LOG.proc_begin(d_module);
45 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
46 END IF;
47
48 l_attr_grp_ids := PO_TBL_NUMBER();
49
50 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
51 l_context := NULL;
52
53 IF (c_get_usages_rec.attribute_category = 'FORMS') then
54 SELECT clm_standard_form into l_clm_standard_form FROM po_headers_all
55 WHERE po_header_id = p_po_header_id;
56 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'FORM_TYPE', l_clm_standard_form));
57 END IF;
58
59 IF((p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN')
60 OR (c_get_usages_rec.attribute_category = 'DOCUMENT_NUMBERING')) THEN
61 -- OR (c_get_usages_rec.attribute_category = 'BASE_DOC_INFO') ) THEN --Bug 11797563
62 IF PO_LOG.d_stmt THEN
63 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain and Doc Numbering AGs');
64 END IF;
65 ELSE
66 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
67 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
68 t_count := l_attr_grp_ids_list.Count;
69
70 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
71 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
72 l_attr_grp_ids.extend(1);
73 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
74 END LOOP;
75 END IF;
76 END LOOP;
77
78 x_attr_grp_ids := l_attr_grp_ids;
79 x_return_status := 'S';
80
81 IF PO_LOG.d_stmt THEN
82 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
83 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
84 END IF;
85
86 EXCEPTION
87 WHEN No_Data_Found THEN
88 NULL;
89
90 WHEN OTHERS THEN
91 d_progress := 40;
92 x_return_status := 'U';
93 IF PO_LOG.d_stmt THEN
94 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
95 END IF;
96 END get_po_header_attr_group_ids;
97
98 PROCEDURE get_po_line_attr_group_ids(
99 p_template_id IN VARCHAR2,
100 p_po_line_id IN NUMBER,
101 p_draft_id IN NUMBER DEFAULT -1,
102 p_is_doc_mod VARCHAR2,
103 p_contract_type IN VARCHAR2,
104 p_clm_idc_type IN VARCHAR2,
105 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2
107 ) IS
108 d_api_name CONSTANT VARCHAR2(30) := 'get_po_line_attr_group_ids';
109 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
110 d_progress NUMBER := 0;
111
112 l_po_uda_usage_object po_uda_usage_object ;
113 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
114 l_attr_grp_ids_list PO_TBL_NUMBER;
115 l_contract_type PO_LINES_ALL.CONTRACT_TYPE%TYPE;
116 l_clm_idc_type PO_LINES_ALL.CLM_IDC_TYPE%TYPE;
117 l_x_attr_ids_count NUMBER;
118 l_attr_grp_ids PO_TBL_NUMBER;
119 t_count NUMBER;
120 l_count NUMBER;
121
122 CURSOR c_get_usages(p_template_id NUMBER) IS
123 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
124 WHERE template_id = p_template_id;
125
126 BEGIN
127
128 IF (PO_LOG.d_proc) THEN
129 PO_LOG.proc_begin(d_module);
130 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
131 END IF;
132
133 l_attr_grp_ids := PO_TBL_NUMBER();
134
135 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
136
137 l_context := NULL;
138
139 IF (c_get_usages_rec.attribute_category = 'PRICING') then
140 IF(p_contract_type IS NULL AND p_clm_idc_type IS NULL) THEN
141 IF(p_draft_id = -1) THEN
142 BEGIN
143 SELECT CONTRACT_TYPE, CLM_IDC_TYPE
144 INTO l_contract_type ,l_clm_idc_type
145 FROM po_lines_all
146 WHERE po_line_id = p_po_line_id;
147 EXCEPTION
148 WHEN No_Data_Found THEN
149 l_contract_type := NULL;
150 l_clm_idc_type := NULL;
151 END;
152
153 ELSE
154 BEGIN
155 SELECT CONTRACT_TYPE, CLM_IDC_TYPE
156 INTO l_contract_type ,l_clm_idc_type
157 FROM po_lines_draft_all
158 WHERE po_line_id = p_po_line_id
159 AND draft_id = p_draft_id;
160 EXCEPTION
161 WHEN No_Data_Found THEN
162 l_contract_type := NULL;
163 l_clm_idc_type := NULL;
164 END;
165
166 END IF;
167 ELSE
168 l_contract_type := p_contract_type;
169 l_clm_idc_type := p_clm_idc_type;
170 END IF;
171
172 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'CONTRACT_LINE_TYPE', l_contract_type),
173 EGO_COL_NAME_VALUE_PAIR_OBJ( 'IDC_TYPE' , l_clm_idc_type ));
174 END IF;
175
176 IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
177 IF PO_LOG.d_stmt THEN
178 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
179 END IF;
180
181 ELSE
182 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
183 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
184
185 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
186 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
187 l_attr_grp_ids.extend(1);
188 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
189 END LOOP;
193 x_attr_grp_ids := l_attr_grp_ids;
190 END IF;
191 END LOOP;
192
194 x_return_status := 'S';
195
196 IF PO_LOG.d_stmt THEN
197 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
198 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
199 END IF;
200
201 EXCEPTION
202 WHEN No_Data_Found THEN
203 NULL;
204
205 WHEN OTHERS THEN
206 d_progress := 40;
207 x_return_status := 'U';
208 IF PO_LOG.d_stmt THEN
209 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
210 END IF;
211 END get_po_line_attr_group_ids;
212
213
214 PROCEDURE get_po_line_loc_attr_group_ids(
215 p_template_id IN VARCHAR2,
216 p_po_line_location_id IN NUMBER,
217 p_is_doc_mod VARCHAR2,
218 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2
220 ) IS
221
222 d_api_name CONSTANT VARCHAR2(30) := 'get_po_line_loc_attr_group_ids';
223 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
224 d_progress NUMBER := 0;
225 l_po_uda_usage_object po_uda_usage_object ;
226 l_attr_grp_ids_list PO_TBL_NUMBER;
227 l_x_attr_ids_count NUMBER;
228 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
229 l_attr_grp_ids PO_TBL_NUMBER;
230 t_count NUMBER;
231
232 CURSOR c_get_usages(p_template_id NUMBER) IS
233 SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
234 WHERE template_id = p_template_id;
235
236 BEGIN
237 IF (PO_LOG.d_proc) THEN
238 PO_LOG.proc_begin(d_module);
239 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
240 END IF;
241
242 l_attr_grp_ids := PO_TBL_NUMBER();
243
244 FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
245 IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
246 IF PO_LOG.d_stmt THEN
247 PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
248 END IF;
249
250 ELSE
251 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
252 l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
253 t_count := l_attr_grp_ids_list.Count;
254
255 FOR i IN 1..l_attr_grp_ids_list.Count LOOP
256 l_x_attr_ids_count := l_attr_grp_ids.Count + 1;
257 l_attr_grp_ids.extend(1);
258 l_attr_grp_ids(l_x_attr_ids_count) := l_attr_grp_ids_list(i);
259 END LOOP;
260 END IF;
261 END LOOP;
262
263 x_attr_grp_ids := l_attr_grp_ids;
264 x_return_status := 'S';
265
266 IF PO_LOG.d_stmt THEN
267 PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
268 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
269 END IF;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 d_progress := 40;
274 x_return_status := 'U';
275 IF PO_LOG.d_stmt THEN
276 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
277 END IF;
278 END get_po_line_loc_attr_group_ids;
279
280 PROCEDURE merge_mod_uda
281 (
282 p_po_header_id IN NUMBER,
283 p_draft_id IN NUMBER,
284 x_return_status OUT NOCOPY VARCHAR2)
285 IS
286 d_api_name CONSTANT VARCHAR2(30) := 'merge_mod_uda';
287 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
288 d_progress NUMBER := 0;
289 l_template_id NUMBER;
290 l_from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
291 l_to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
292 l_copy_attribute_groups VARCHAR2(10);
293 l_attribute_group_table PO_TBL_VARCHAR30;
294 l_attr_group_name VARCHAR2(250);
295 l_return_status VARCHAR2(2);
296 l_msg_count NUMBER;
297 l_msg_data VARCHAR2(2000);
298 l_req_source_hdr_id NUMBER;
299 l_po_line_ids Dbms_Sql.number_table;
300 l_po_line_location_ids Dbms_Sql.number_table;
301
302 l_drft_contract_type VARCHAR2(200) ;
303 l_drft_idc_type VARCHAR2(200);
304 l_base_contract_type VARCHAR2(200);
305 l_base_idc_type VARCHAR2(200);
306
307 CURSOR c_get_add_agids_tbl(l_template_id NUMBER)
308 IS
309 SELECT ATTRIBUTE_GROUP_ID
310 FROM po_uda_ag_template_usages
311 WHERE template_id = l_template_id
312 AND ATTRIBUTE_CATEGORY IN ('ADDRESS', 'DOCUMENT_NUMBERING','MODIFICATIONS');
313 BEGIN
314 IF (PO_LOG.d_proc) THEN
315 PO_LOG.proc_begin(d_module);
316 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
317 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
318 END IF;
319 -- START WITH HEADER LEVEL MERGE
320 SELECT uda_template_id
321 INTO l_template_id
322 FROM po_headers_all
323 WHERE po_header_id = p_po_header_id;
324
325 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PO_HEADER_ID', p_po_header_id),
326 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID', p_draft_id));
327 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PO_HEADER_ID', p_po_header_id),
328 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID', -1));
332 l_attribute_group_table.extend(1);
329 l_attribute_group_table := PO_TBL_VARCHAR30();
330 FOR c_get_add_agids_tbl_rec IN c_get_add_agids_tbl(l_template_id)
331 LOOP
333 SELECT attr_group_name
334 INTO l_attr_group_name
335 FROM EGO_ATTR_GROUPS_V
336 WHERE ATTR_GROUP_TYPE = 'PO_HEADER_EXT_ATTRS'
337 AND ATTR_GROUP_ID = c_get_add_agids_tbl_rec.ATTRIBUTE_GROUP_ID;
338
339 l_attribute_group_table(l_attribute_group_table.Count) := l_attr_group_name;
340 END LOOP;
341
342 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
343 (
344 from_template_id => l_template_id
345 ,to_template_id => l_template_id
346 ,from_pk_col_value_pairs => l_from_pk_col_value_pairs
347 ,to_pk_col_value_pairs => l_to_pk_col_value_pairs
348 ,copy_attribute_groups => 'EXCLUSIVE' -- SPECIFIC / EXCLUSIVE
349 ,attribute_group_table => l_attribute_group_table
350 ,p_commit => FND_API.G_FALSE
351 ,x_return_status => l_return_status
352 ,x_msg_count => l_msg_count
353 ,x_msg_data => l_msg_data
354 );
355
356 IF PO_LOG.d_stmt THEN
357 PO_LOG.stmt(d_module, d_progress, 'l_return_status for Headers', l_return_status);
358 END IF;
359
360 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 PO_UDA_DATA_UTIL.Merge_Mod_Address_AG(p_po_header_id => p_po_header_id
365 ,p_draft_id => p_draft_id
366 ,x_return_status => l_return_status );
367 IF PO_LOG.d_stmt THEN
368 PO_LOG.stmt(d_module, d_progress, 'Merge_Mod_Address_AG: l_return_status ', l_return_status);
369 END IF;
370
371 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374
375 -- STARTING WITH LINE MERGE
376 SELECT po_line_id BULK COLLECT
377 INTO l_po_line_ids
378 FROM po_lines_draft_all
379 WHERE po_header_id = p_po_header_id
380 AND draft_id = p_draft_id;
381
382 FOR i IN 1..l_po_line_ids.Count
383 LOOP
384 SELECT uda_template_id
385 INTO l_template_id
386 FROM po_lines_all
387 WHERE po_line_id = l_po_line_ids(i);
388
389 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PO_LINE_ID', l_po_line_ids(i)),
390 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID', p_draft_id));
391 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PO_LINE_ID', l_po_line_ids(i)),
392 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID', -1));
393
394 SELECT CONTRACT_TYPE, CLM_IDC_TYPE
395 INTO l_drft_contract_type, l_drft_idc_type
396 FROM po_lines_draft_all
397 WHERE PO_LINE_ID = l_po_line_ids(i)
398 AND DRAFT_ID = p_draft_id;
399
400 SELECT CONTRACT_TYPE, CLM_IDC_TYPE
401 INTO l_base_contract_type, l_base_idc_type
402 FROM po_lines_all
403 WHERE PO_LINE_ID = l_po_line_ids(i);
404
405 IF (l_drft_contract_type <> l_base_contract_type) OR (l_drft_idc_type <> l_base_idc_type) THEN
406 DELETE FROM PO_LINES_ALL_EXT_TL WHERE extension_id IN (SELECT extension_id FROM PO_LINES_ALL_EXT_B WHERE PO_LINE_ID = l_po_line_ids(i) AND DRAFT_ID = -1);
407 DELETE FROM PO_LINES_ALL_EXT_B WHERE PO_LINE_ID = l_po_line_ids(i) AND DRAFT_ID = -1;
408 END IF;
409
410 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
411 (
412 from_template_id => l_template_id
413 ,to_template_id => l_template_id
414 ,from_pk_col_value_pairs => l_from_pk_col_value_pairs
415 ,to_pk_col_value_pairs => l_to_pk_col_value_pairs
416 ,copy_attribute_groups => 'ALL' -- SPECIFIC / EXCLUSIVE
417 ,p_commit => FND_API.G_FALSE
418 ,x_return_status => l_return_status
419 ,x_msg_count => l_msg_count
420 ,x_msg_data => l_msg_data
421 );
422
423 IF PO_LOG.d_stmt THEN
424 PO_LOG.stmt(d_module, d_progress, 'Merging UDA for Line ID ', l_po_line_ids(i));
425 PO_LOG.stmt(d_module, d_progress, 'l_return_status ', l_return_status);
426 END IF;
427
428 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431
432 SELECT line_location_id BULK COLLECT
433 INTO l_po_line_location_ids
434 FROM po_line_locations_draft_all
435 WHERE po_line_id = l_po_line_ids(i)
436 AND draft_id = p_draft_id;
437
438 FOR j IN 1..l_po_line_location_ids.Count
439 LOOP
440 SELECT uda_template_id
441 INTO l_template_id
442 FROM po_line_locations_all
443 WHERE line_location_id = l_po_line_location_ids(j);
444
445 l_from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('LINE_LOCATION_ID', l_po_line_location_ids(j)),
446 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID', p_draft_id));
447 l_to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('LINE_LOCATION_ID', l_po_line_location_ids(j)),
448 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID', -1));
449
450 PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
451 (
455 ,to_pk_col_value_pairs => l_to_pk_col_value_pairs
452 from_template_id => l_template_id
453 ,to_template_id => l_template_id
454 ,from_pk_col_value_pairs => l_from_pk_col_value_pairs
456 ,copy_attribute_groups => 'ALL' -- SPECIFIC / EXCLUSIVE
457 ,p_commit => FND_API.G_FALSE
458 ,x_return_status => l_return_status
459 ,x_msg_count => l_msg_count
460 ,x_msg_data => l_msg_data
461 );
462
463 IF PO_LOG.d_stmt THEN
464 PO_LOG.stmt(d_module, d_progress, 'Merging UDA for Shipment ID ', l_po_line_location_ids(j));
465 PO_LOG.stmt(d_module, d_progress, 'l_return_status ', l_return_status);
466 END IF;
467
468 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471
472
473 END LOOP;
474 END LOOP;
475 x_return_status := FND_API.G_RET_STS_SUCCESS; -- BUG 13580463
476 EXCEPTION
477 WHEN FND_API.G_EXC_ERROR THEN
478 d_progress := 090;
479 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
480 IF (PO_LOG.d_exc) THEN
481 PO_LOG.exc(d_module, d_progress, l_msg_data );
482 PO_LOG.proc_end(d_module);
483 END IF;
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 WHEN OTHERS THEN
486 d_progress := 100;
487 PO_MESSAGE_S.add_exc_msg
488 ( p_pkg_name => d_pkg_name,
489 p_procedure_name => d_api_name || '.' || d_progress
490 );
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492 END merge_mod_uda ;
493
494 PROCEDURE get_ext_param_xle_actions_ags(
495 p_template_id IN VARCHAR2,
496 p_external_param IN VARCHAR2,
497 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
498 x_return_status OUT NOCOPY VARCHAR2
499 ) IS
500
501
502 d_api_name CONSTANT VARCHAR2(30) := 'get_ext_param_xle_actions_ags';
503 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
504 d_progress NUMBER := 0;
505 l_attr_grp_ids PO_TBL_NUMBER;
506
507 CURSOR c_get_attr_ids(p_template_id VARCHAR2) IS
508 SELECT ACT.ATTR_GROUP_ID
509 FROM EGO_ACTION_DISPLAYS_B EAD, EGO_ACTIONS_b ACT ,EGO_MAPPINGS_B B
510 WHERE ACT.action_id = ead.action_id
511 AND EAD.EXECUTION_METHOD = 'XLE'
512 AND B.MAPPED_OBJ_PK1_VAL = ACT.ACTION_ID
513 AND B.MAPPED_TO_GROUP_TYPE = 'E'
514 AND B.MAPPED_ATTRIBUTE = p_external_param
515 AND ACT.CLASSIFICATION_CODE = p_template_id;
516
517 BEGIN
518 IF (PO_LOG.d_proc) THEN
519 PO_LOG.proc_begin(d_module);
520 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
521 END IF;
522
523 l_attr_grp_ids := PO_TBL_NUMBER();
524
525 FOR c_get_attr_ids_rec IN c_get_attr_ids(p_template_id) LOOP
526 l_attr_grp_ids.extend(1);
527 l_attr_grp_ids(l_attr_grp_ids.Count) := c_get_attr_ids_rec.ATTR_GROUP_ID;
528 END LOOP;
529
530 x_attr_grp_ids := l_attr_grp_ids;
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532
533 IF PO_LOG.d_stmt THEN
534 PO_LOG.stmt(d_module, d_progress, 'x_return_status ', x_return_status);
535 END IF;
536
537 EXCEPTION
538 WHEN OTHERS THEN
539 d_progress := 40;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
541 IF PO_LOG.d_stmt THEN
542 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
543 END IF;
544 END get_ext_param_xle_actions_ags;
545
546 PROCEDURE get_ext_param_xd_actions_ags(
547 p_template_id IN VARCHAR2,
548 p_external_param IN VARCHAR2,
549 x_attr_grp_ids OUT NOCOPY PO_TBL_NUMBER,
550 x_return_status OUT NOCOPY VARCHAR2
551 ) IS
552
553
554 d_api_name CONSTANT VARCHAR2(30) := 'get_ext_param_xd_actions_ags';
555 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
556 d_progress NUMBER := 0;
557 l_attr_grp_ids PO_TBL_NUMBER;
558
559 CURSOR c_get_attr_ids(p_template_id VARCHAR2) IS
560 SELECT ACT.ATTR_GROUP_ID
561 FROM EGO_ACTION_DISPLAYS_B EAD, EGO_ACTIONS_b ACT ,EGO_MAPPINGS_B B
562 WHERE ACT.action_id = ead.action_id
563 AND EAD.EXECUTION_METHOD = 'XD'
564 AND B.MAPPED_OBJ_PK1_VAL = ACT.ACTION_ID
565 AND B.MAPPED_TO_GROUP_TYPE = 'E'
566 AND B.MAPPED_ATTRIBUTE = p_external_param
567 AND ACT.CLASSIFICATION_CODE = p_template_id;
568
569 BEGIN
570 IF (PO_LOG.d_proc) THEN
571 PO_LOG.proc_begin(d_module);
572 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
573 END IF;
574
575 l_attr_grp_ids := PO_TBL_NUMBER();
576
577 FOR c_get_attr_ids_rec IN c_get_attr_ids(p_template_id) LOOP
578 l_attr_grp_ids.extend(1);
579 l_attr_grp_ids(l_attr_grp_ids.Count) := c_get_attr_ids_rec.ATTR_GROUP_ID;
580 END LOOP;
581
582 x_attr_grp_ids := l_attr_grp_ids;
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 IF PO_LOG.d_stmt THEN
586 PO_LOG.stmt(d_module, d_progress, 'x_return_status ', x_return_status);
587 END IF;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 d_progress := 40;
592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 IF PO_LOG.d_stmt THEN
594 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
595 END IF;
596 END get_ext_param_xd_actions_ags;
597
598 END po_doc_uda_util;