[Home] [Help]
PACKAGE BODY: APPS.PON_UDA_PKG
Source
1 PACKAGE BODY pon_uda_pkg AS
2 /* $Header: PON_UDA_PKG.plb 120.16.12020000.2 2013/05/30 05:24:25 hvutukur ship $*/
3 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_UDA_PKG.';
4 --d_appln_short_name CONSTANT VARCHAR2(20) := 'PO';
5
6 PROCEDURE competitive_info_XDE(x_set_aside_status OUT NOCOPY VARCHAR2,
7 x_set_aside_per OUT NOCOPY VARCHAR2) IS
8 BEGIN
9 x_set_aside_status := 'SET_ASIDE'; -- Define as constant
10 x_set_aside_per := '100'; -- Define as constant
11
12 END competitive_info_XDE;
13
14 PROCEDURE pon_add_item_info_XLE(p_item_id IN NUMBER,
15 p_org_id IN NUMBER,
16 x_ext_description OUT NOCOPY VARCHAR,
17 x_return_status OUT NOCOPY VARCHAR,
18 x_errorcode OUT NOCOPY VARCHAR,
19 x_msg_count OUT NOCOPY VARCHAR,
20 x_msg_data OUT NOCOPY VARCHAR) IS
21
22 BEGIN
23 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
24 FND_LOG.string(log_level => FND_LOG.level_procedure,
25 module => g_module_prefix || 'pon_add_item_info_XLE',
26 message => 'Entering PON_UDA_PKG.pon_add_item_info_XLE'
27 || ', p_item_id = ' || p_item_id);
28 END IF;
29 if ( p_item_id is not null ) Then
30 SELECT LONG_DESCRIPTION
31 INTO x_ext_description
32 FROM MTL_SYSTEM_ITEMS_TL
33 WHERE INVENTORY_ITEM_ID = p_item_id
34 AND ORGANIZATION_ID = p_org_id
35 AND language = userenv('LANG');
36 end if;
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38 x_errorcode := '0';
39 x_msg_count := '1';
40 x_msg_data := 'SUCCESS';
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
45 x_errorcode := '-2';
46 x_msg_count := '1';
47 x_msg_data := 'Exception in add_item_info_XLE';
48 fnd_msg_pub.Add_Exc_Msg ( 'PON_UDA_PKG', 'PON_ADD_ITEM_INFO_XLE', x_msg_data );
49
50 END pon_add_item_info_XLE;
51
52
53 PROCEDURE pon_addressdef (addresstype IN OUT nocopy VARCHAR2
54 ,location IN OUT nocopy NUMBER
55 ,addresscode IN OUT nocopy NUMBER
56 ,contact IN OUT nocopy NUMBER
57 ,addressdetails IN OUT nocopy VARCHAR2
58 ,contactdetails IN OUT nocopy VARCHAR2
59 ,addressdetailsxml IN OUT nocopy VARCHAR2
60 ,contactdetailsxml IN OUT nocopy VARCHAR2
61 , hiddenCountry IN OUT nocopy VARCHAR2
62 , hiddenZipCode IN OUT nocopy VARCHAR2
63 ,HiddenAddType IN VARCHAR2
64 ,HiddenLKPType IN VARCHAR2)
65 IS
66
67
68 l_position varchar2(200);
69 l_HiddenCountry VARCHAR2(20);
70 l_HiddenZipCode VARCHAR(20);
71
72 BEGIN
73 addresstype := HiddenAddType;
74 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN
75 FND_LOG.string(log_level => FND_LOG.level_procedure,
76 module => g_module_prefix || 'pon_addressdef',
77 message => 'Entering PON_UDA_PKG.pon_addressdef'
78 || ', HiddenAddType = ' || HiddenAddType
79 || ' , location =' || location);
80 END IF ;
81
82
83
84 po_fed_field_functions.address_XD(
85 addresstype
86 ,location
87 ,addresscode
88 ,contact
89 ,addressdetails
90 ,contactdetails
91 ,addressdetailsxml
92 ,contactdetailsxml
93 ,hiddenCountry
94 ,hiddenZipCode
95 ,HiddenAddType
96 ,HiddenLKPType
97 ,'SOURCING'
98 );
99
100
101
102 EXCEPTION
103 /* WHEN No_Data_Found THEN
104 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
105 FND_LOG.string(log_level => FND_LOG.level_procedure,
106 module => g_module_prefix || 'pon_addressdef',
107 message => g_module_prefix || ' pon_addressdef: NO DATA FOUND');
108 END IF; */
109 WHEN OTHERS THEN
110 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
111 FND_LOG.string(log_level => FND_LOG.level_procedure,
112 module => g_module_prefix || 'pon_addressdef',
113 message => g_module_prefix || ' pon_addressdef: ' || SQLERRM );
114 END IF ;
115
116
117 END pon_addressdef;
118
119 PROCEDURE pon_addresses_XPD(p_header_id IN NUMBER,
120 p_HiddenAddType IN VARCHAR2,
121 p_HiddenLKPType IN VARCHAR2,
122 x_location IN OUT NOCOPY NUMBER,
123 x_addresscode IN OUT NOCOPY NUMBER,
124 x_contact_id IN OUT NOCOPY NUMBER,
125 x_addressdetails IN OUT NOCOPY VARCHAR2,
126 x_contactdetails IN OUT NOCOPY VARCHAR2,
127 x_addressdtlsxml IN OUT NOCOPY VARCHAR2,
128 x_contactdtlsxml IN OUT NOCOPY VARCHAR2,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_errorcode OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY VARCHAR2,
132 x_msg_data OUT NOCOPY VARCHAR2) IS
133 BEGIN
134 null;
135 END pon_addresses_XPD;
136
137 PROCEDURE delete_uda(p_auction_header_id IN NUMBER,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_error_msg OUT NOCOPY VARCHAR2) IS
140 BEGIN
141 x_return_status := 'S';
142 delete from pon_auction_headers_ext_b where auction_header_id = p_auction_header_id;
143 EXCEPTION
144 WHEN OTHERS THEN
145 x_return_status := 'E';
146 x_error_msg := 'An error occured while deleting udas.';
147 END delete_uda;
148
149
150 PROCEDURE DEFAULT_NON_RENDER_UDA( p_auction_header_id IN NUMBER,
151 x_return_status OUT NOCOPY VARCHAR2) IS
152
153 l_po_uda_usage_object_array PO_UDA_USAGE_OBJECT_ARRAY;
154 l_po_uda_usage_object PO_UDA_USAGE_OBJECT;
155 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
156 l_template_id NUMBER;
157 l_amd_flag Varchar2(1);
158 l_address_lookup_type varchar2(100);
159 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
160
161 l_return_status Varchar2(1);
162 l_errorcode Number;
163 l_msg_count Number;
164 l_msg_data Varchar2(2000);
165
166
167 BEGIN
168 l_po_uda_usage_object := PO_UDA_USAGE_OBJECT();
169 l_po_uda_usage_object_array := PO_UDA_USAGE_OBJECT_ARRAY();
170 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY();
171
172 select uda_template_id,nvl(amendment_flag,'N') into l_template_id,l_amd_flag from pon_auction_headers_all where auction_header_id = p_auction_header_id;
173
174 if ( l_amd_flag = 'Y' ) then
175 l_address_lookup_type := 'SOL_AMD_UDA_ADDRESS_TYPES';
176 else
177 l_address_lookup_type := 'SOL_UDA_ADDRESS_TYPES';
178 end if;
179
180 l_po_uda_usage_object := po_uda_usage_object.new_instance(l_template_id,'ADDRESS',l_context );
181 l_po_uda_usage_object_array.extend(1);
182 l_po_uda_usage_object_array(1) := l_po_uda_usage_object;
183
184
185 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
186 FND_LOG.string(log_level => FND_LOG.level_procedure,
187 module => g_module_prefix || 'default_non_render_uda',
188 message => 'Call to Default_Ags_From_Usage_Object');
189 END IF ;
190
191 PO_UDA_DEFAULTING_PKG.DEFAULT_AGS_FROM_USAGE_OBJECT
192 (
193 p_pk1_value => p_auction_header_id
194 ,p_template_id => l_template_id
195 ,p_address_lookup_type => l_address_lookup_type
196 ,x_external_attr_value_pairs => l_external_attr_value_pairs
197 ,x_po_uda_usage_object_array => l_po_uda_usage_object_array
198 ,x_return_status => l_return_status
199 ,x_errorcode => l_errorcode
200 ,x_msg_count => l_msg_count
201 ,x_msg_data => l_msg_data
202 );
203
204 x_return_status := l_return_status;
205
206 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
207 FND_LOG.string(log_level => FND_LOG.level_procedure,
208 module => g_module_prefix || 'default_non_render_uda',
209 message => 'Return from Default_Ags_From_Usage_Object '||x_return_status);
210 END IF ;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 x_return_status := 'U';
215 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
216 FND_LOG.string(log_level => FND_LOG.level_procedure,
217 module => g_module_prefix || 'default_non_render_uda',
218 message => 'Exception from Default_Ags_From_Usage_Object '||SQLERRM);
219 END IF ;
220
221 END DEFAULT_NON_RENDER_UDA;
222
223 /*
224 PROCEDURE pon_addresses_XSC
225 (
226 p_HiddenAddType IN VARCHAR2
227 ,p_location IN NUMBER
228 ,p_contact_id IN NUMBER
229 ,x_return_status OUT NOCOPY VARCHAR2
230 ,x_errorcode OUT NOCOPY VARCHAR2
231 ,x_msg_count OUT NOCOPY VARCHAR2
232 ,x_msg_data OUT NOCOPY VARCHAR2
233 )IS
234
235 l_submissionValid BOOLEAN;
236 l_AddTypeDispField VARCHAR2(100);
237
238 BEGIN
239 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN
240 FND_LOG.string(log_level => FND_LOG.level_procedure,
241 module => g_module_prefix || 'pon_addresses_XSC',
242 message => 'Entering PON_UDA_PKG.pon_addresses_XSC'
243 || ', p_HiddenAddType = ' || p_HiddenAddType
244 || ' , p_location =' || p_location);
245 END IF ;
246
247
248 IF p_location IS NULL THEN
249 CASE p_HiddenAddType
250
251 WHEN 'COTR_OFFICE' THEN l_submissionValid := FALSE;
252 WHEN 'ISSUING_OFFICE' THEN l_submissionValid := FALSE;
253 WHEN 'REQ_OFFICE' THEN l_submissionValid := FALSE;
254 WHEN 'PRO_ADMIN_OFFICE' THEN l_submissionValid := FALSE;
255
256 ELSE
257 l_submissionValid := TRUE;
258 END CASE;
259 ELSE
260 l_submissionValid := TRUE;
261 END IF;
262
263 IF l_submissionValid THEN
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265 x_errorcode := '0';
266 x_msg_count := '1';
267 x_msg_data := 'SUCCESS';
268 ELSE
269
270 SELECT displayed_field
271 INTO l_AddTypeDispField
272 FROM po_lookup_codes
273 WHERE lookup_type = 'SOL_UDA_ADDRESS_TYPES'
274 AND lookup_code = p_HiddenAddType;
275
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 x_errorcode := '-1';
278 x_msg_count := '1';
279 x_msg_data := 'Location cannot be null for ' || l_AddTypeDispField;
280 fnd_message.set_name(d_appln_short_name,'PO_LOCATION_NOT_NULL') ;
281 fnd_message.set_token('ADDTYPE',l_AddTypeDispField);
282 fnd_msg_pub.ADD;
283 END IF ;
284
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN
289 FND_LOG.string(log_level => FND_LOG.level_procedure,
290 module => g_module_prefix || 'pon_addresses_XSC',
291 message => g_module_prefix || ' pon_addresses_XSC: ' || SQLERRM );
292 END IF ;
293 END pon_addresses_XSC; */
294
295 /*procedure print_debug_messages(p_msg in varchar2) is
296 pragma autonomous_transaction;
297 BEGIN
298 insert into vhk_dummy values (p_msg);
299 commit;
300 END print_debug_messages;*/
301
302 PROCEDURE IMPORT_EXCEL_UDA(p_bid_number IN NUMBER,
303 p_line_number IN NUMBER,
304 p_template_id IN NUMBER,
305 x_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE,
306 x_attributes_row_table IN OUT NOCOPY EGO_USER_ATTR_ROW_TABLE,
307 x_external_attr_value_pairs IN OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_TABLE,
308 x_total_amount OUT NOCOPY NUMBER,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_errorcode OUT NOCOPY NUMBER,
311 x_msg_count OUT NOCOPY NUMBER,
312 x_msg_data OUT NOCOPY VARCHAR2) IS
313
314 pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
315 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
316 l_failed_row_id_list VARCHAR2(100);
317 l_count number;
318
319 --Bug : 16838468
320 l_attr_grp_id NUMBER;
321
322 BEGIN
323 --print_debug_messages('Start of procedure IMPORT_EXCEL_UDA, p_bid_number : '||p_bid_number||' p_line_number '||p_line_number||' p_template_id '||p_template_id);
324 pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
325 (
326 EGO_COL_NAME_VALUE_PAIR_OBJ('BID_NUMBER', p_bid_number),
327 EGO_COL_NAME_VALUE_PAIR_OBJ('LINE_NUMBER', p_line_number)
328 );
329
330 --Bug : 16838468 : Get complex pricing attr group id to fetch total_amount value.
331 l_attr_grp_id := x_attributes_row_table(1).ATTR_GROUP_ID;
332
333 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION(
334 p_template_id => p_template_id
335 ,p_event => 'XPC'
336 ,x_external_attr_value_pairs => x_external_attr_value_pairs
337 ,x_pk_column_name_value_pairs => pk_col_value_pairs
338 ,x_attr_name_value_pairs => x_attr_name_value_pairs
339 ,x_attributes_row_table => x_attributes_row_table
340 ,x_return_status => x_return_status
341 ,x_errorcode => x_errorcode
342 ,x_msg_count => x_msg_count
343 ,x_msg_data => x_msg_data);
344
345 --print_debug_messages('After EXECUTE_IMPORT_UDA_FUNCTION : x_return_status :'||x_return_status||' x_msg_count '||x_msg_count);
346 if x_return_status = 'S' then
347
348 /* Commented for qa bug : 9957084
349 FOR i IN x_attr_name_value_pairs.first..x_attr_name_value_pairs.last LOOP
350 If x_attr_name_value_pairs(i).ATTR_NAME = 'TOTAL_AMOUNT' THEN
351 x_total_amount := x_attr_name_value_pairs(i).ATTR_VALUE_NUM;
352 exit;
353 END IF;
354 END LOOP;*/
355
356 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
357 (
358 EGO_COL_NAME_VALUE_PAIR_OBJ
359 (
360 'UDA_TEMPLATE_ID',p_template_id || ''
361 )
362 );
363 --print_debug_messages('Before calling process_user_attrs_data.');
364 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
365 (
366 p_api_version => 1.0
367 ,p_object_name => 'PON_BID_ITEM_PRICES'
368 ,p_attributes_row_table => x_attributes_row_table
369 ,p_attributes_data_table => x_attr_name_value_pairs
370 ,p_pk_column_name_value_pairs => pk_col_value_pairs
371 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
372 --,p_validate_only => FND_API.G_TRUE
373 ,x_failed_row_id_list => l_failed_row_id_list
374 ,p_init_fnd_msg_list => FND_API.G_TRUE
375 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
376 ,x_return_status => x_return_status
377 ,x_errorcode => x_errorcode
378 ,x_msg_count => x_msg_count
379 ,x_msg_data => x_msg_data
380 );
381 /* For qa bug : 9957084 : Offer pice should be populated from
382 * column N_EXT_ATTR20.
383 */
384 IF x_return_status = 'S' THEN
385 SELECT N_EXT_ATTR20 INTO x_total_amount
386 FROM pon_bid_item_prices_ext_b
387 WHERE bid_number=p_bid_number
388 AND line_number = p_line_number
389 AND ATTR_GROUP_ID = l_attr_grp_id; --Bug : 16838468 :Fetch for complex pricing attribute group
390 END IF;
391 End if;
392
393 END IMPORT_EXCEL_UDA;
394
395 PROCEDURE VALIDATE_UDA(p_bid_number IN NUMBER,
396 p_line_number IN NUMBER,
397 p_template_id IN NUMBER,
398 p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE,
399 p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE,
400 x_return_status OUT NOCOPY VARCHAR2,
401 x_errorcode OUT NOCOPY NUMBER,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2) IS
404
405 l_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
406 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
407 l_failed_row_id_list VARCHAR2(100);
408
409 BEGIN
410 --print_debug_messages('Start of procedure validate_uda.');
411
412 l_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
413 (
414 EGO_COL_NAME_VALUE_PAIR_OBJ('BID_NUMBER', p_bid_number),
415 EGO_COL_NAME_VALUE_PAIR_OBJ('LINE_NUMBER', p_line_number)
416 );
417
418 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
419 EGO_COL_NAME_VALUE_PAIR_OBJ
420 (
421 'UDA_TEMPLATE_ID',p_template_id || ''
422 ));
423
424 --print_debug_messages('Before calling procedure EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data');
425
426 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
427 (
428 p_api_version => 1.0
429 ,p_object_name => 'PON_BID_ITEM_PRICES'
430 ,p_attributes_row_table => p_attributes_row_table
431 ,p_attributes_data_table => p_attr_name_value_pairs
432 ,p_pk_column_name_value_pairs => l_pk_col_value_pairs
433 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
434 ,p_validate_only => FND_API.G_TRUE
435 ,x_failed_row_id_list => l_failed_row_id_list
436 ,p_init_fnd_msg_list => FND_API.G_TRUE
437 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
438 ,x_return_status => x_return_status
439 ,x_errorcode => x_errorcode
440 ,x_msg_count => x_msg_count
441 ,x_msg_data => x_msg_data
442 );
443 --print_debug_messages('After procedure EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data');
444 --print_debug_messages('x_return_status : '||x_return_status||' x_errorcode :'||x_errorcode||' x_msg_count :'||x_msg_count||' x_msg_data :'||x_msg_data);
445
446 END VALIDATE_UDA;
447
448
449 end PON_UDA_PKG;
450