[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_ERR_UTL
Source
1 PACKAGE BODY PO_PDOI_ERR_UTL AS
2 /* $Header: PO_PDOI_ERR_UTL.plb 120.11 2011/09/08 06:21:30 dtoshniw ship $ */
3
4
5 d_pkg_name CONSTANT VARCHAR2(50) :=
6 PO_LOG.get_package_base('PO_PDOI_ERR_UTL');
7
8 g_APP_PO CONSTANT VARCHAR2(30) := 'PO';
9
10 --------------------------------------------------------------------------
11 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
12 --------------------------------------------------------------------------
13
14 PROCEDURE map_error_message
15 (
16 p_validation_id IN NUMBER,
17 p_headers IN PO_PDOI_TYPES.headers_rec_type := NULL,
18 p_lines IN PO_PDOI_TYPES.lines_rec_type := NULL,
19 p_line_locs IN PO_PDOI_TYPES.line_locs_rec_type := NULL,
20 p_price_diffs IN PO_PDOI_TYPES.price_diffs_rec_type := NULL,
21 p_table_name IN VARCHAR2 := NULL,
22 p_interface_header_id IN NUMBER,
23 p_interface_line_id IN NUMBER := NULL,
24 p_interface_line_location_id IN NUMBER := NULL,
25 p_price_diff_interface_id IN NUMBER := NULL,
26 x_mapping_exists OUT NOCOPY VARCHAR2,
27 x_mapped_err_msg OUT NOCOPY PO_MSG_MAPPING_UTL.msg_rec_type
28 );
29
30 FUNCTION get_value_from_key
31 (
32 p_key IN VARCHAR2,
33 p_headers IN PO_PDOI_TYPES.headers_rec_type := NULL,
34 p_lines IN PO_PDOI_TYPES.lines_rec_type := NULL,
35 p_line_locs IN PO_PDOI_TYPES.line_locs_rec_type := NULL,
36 p_price_diffs IN PO_PDOI_TYPES.price_diffs_rec_type := NULL,
37 p_table_name IN VARCHAR2 := NULL,
38 p_interface_header_id IN NUMBER,
39 p_interface_line_id IN NUMBER := NULL,
40 p_interface_line_location_id IN NUMBER := NULL,
41 p_price_diff_interface_id IN NUMBER := NULL
42 ) RETURN VARCHAR2;
43
44 -------------------------------------------------------
45 -------------- PUBLIC PROCEDURES ----------------------
46 -------------------------------------------------------
47
48 -----------------------------------------------------------------------
49 --Start of Comments
50 --Name: add_error
51 --Function:
52 -- Add an error to the strucutre. The structure will eventually gets
53 -- flushed to PO_INTERFACE_ERRORS table.
54 --Parameters:
55 --IN:
56 --p_app_name
57 -- Application where the error message is defined in. If none is passed,
58 -- 'PO' will be assumed
59 --IN OUT:
60 --OUT:
61 --End of Comments
62 ------------------------------------------------------------------------
63 PROCEDURE add_error
64 ( p_interface_header_id IN NUMBER,
65 p_interface_line_id IN NUMBER := NULL,
66 p_interface_line_location_id IN NUMBER := NULL,
67 p_interface_distribution_id IN NUMBER := NULL,
68 p_price_diff_interface_id IN NUMBER := NULL,
69 p_interface_attr_values_id IN NUMBER := NULL,
70 p_interface_attr_values_tlp_id IN NUMBER := NULL,
71 p_app_name IN VARCHAR2 := NULL,
72 p_error_message_name IN VARCHAR2 := NULL,
73 p_table_name IN VARCHAR2 := NULL,
74 p_column_name IN VARCHAR2 , -- TODO: Default to Null later
75 p_column_value IN VARCHAR2 , -- TODO: Default to Null later
76 p_token1_name IN VARCHAR2 := NULL,
77 p_token1_value IN VARCHAR2 := NULL,
78 p_token2_name IN VARCHAR2 := NULL,
79 p_token2_value IN VARCHAR2 := NULL,
80 p_token3_name IN VARCHAR2 := NULL,
81 p_token3_value IN VARCHAR2 := NULL,
82 p_token4_name IN VARCHAR2 := NULL,
83 p_token4_value IN VARCHAR2 := NULL,
84 p_token5_name IN VARCHAR2 := NULL,
85 p_token5_value IN VARCHAR2 := NULL,
86 p_token6_name IN VARCHAR2 := NULL,
87 p_token6_value IN VARCHAR2 := NULL,
88 p_error_message IN VARCHAR2 := NULL
89 ) IS
90
91 d_api_name CONSTANT VARCHAR2(30) := 'add_error';
92 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
93 d_position NUMBER;
94
95 l_rec PO_INTERFACE_ERRORS%ROWTYPE;
96
97 BEGIN
98 d_position := 0;
99
100 IF (PO_LOG.d_proc) THEN
101 PO_LOG.proc_begin(d_module);
102 END IF;
103
104 l_rec.interface_type := 'PO_DOCS_OPEN_INTERFACE';
105 l_rec.interface_header_id := p_interface_header_id;
106 l_rec.interface_line_id := p_interface_line_id;
107 l_rec.interface_line_location_id := p_interface_line_location_id;
108 l_rec.interface_distribution_id := p_interface_distribution_id;
109 l_rec.interface_attr_values_id := p_interface_attr_values_id;
110 l_rec.interface_attr_values_tlp_id := p_interface_attr_values_tlp_id;
111 l_rec.price_diff_interface_id := p_price_diff_interface_id; -- bug 5215781
112 l_rec.app_name := NVL(p_app_name, g_APP_PO);
113 l_rec.error_message_name := p_error_message_name;
114 l_rec.table_name := p_table_name;
115 l_rec.column_name := p_column_name;
116 l_rec.column_value := p_column_value;
117 --bug 12812134: truncating the values assigned to token values to 200,
118 -- the column size of token values in po_interface_errors.
119 l_rec.token1_name := p_token1_name;
120 l_rec.token1_value := SubStr(p_token1_value,1,200);
121 l_rec.token2_name := p_token2_name;
122 l_rec.token2_value := SubStr(p_token2_value,1,200);
123 l_rec.token3_name := p_token3_name;
124 l_rec.token3_value := SubStr(p_token3_value,1,200);
125 l_rec.token4_name := p_token4_name;
126 l_rec.token4_value := SubStr(p_token4_value,1,200);
127 l_rec.token5_name := p_token5_name;
128 l_rec.token5_value := SubStr(p_token5_value,1,200);
129 l_rec.token6_name := p_token6_name;
130 l_rec.token6_value := SubStr(p_token6_value,1,200);
131 l_rec.error_message := p_error_message; -- bug5385342
132
133 /* Bug 9918507 start
134 Batch_id was not getting populated in the po_interface_errors
135 even though it was entered in po_headers_interface.
136 Query for batch_id using interface_header_id and populate l_rec.batch_id
137 with that value*/
138 SELECT batch_id
139 INTO l_rec.batch_id
140 FROM po_headers_interface
141 WHERE interface_header_id = p_interface_header_id;
142 /* Bug 9918507 end*/
143
144 PO_INTERFACE_ERRORS_UTL.add_to_errors_tbl
145 ( p_err_type => 'FATAL',
146 p_err_rec => l_rec
147 );
148
149 IF (PO_LOG.d_proc) THEN
150 PO_LOG.proc_end(d_module);
151 END IF;
152 EXCEPTION
153 WHEN OTHERS THEN
154 PO_MESSAGE_S.add_exc_msg
155 ( p_pkg_name => d_pkg_name,
156 p_procedure_name => d_api_name || '.' || d_position
157 );
158 RAISE;
159 END add_error;
160
161 -----------------------------------------------------------------------
162 --Start of Comments
163 --Name: add_warning
164 --Function:
165 -- Add a warning to the strucutre. The structure will eventually gets
166 -- flushed to PO_INTERFACE_ERRORS table.
167 --Parameters:
168 --IN:
169 --p_app_name
170 -- Application where the error message is defined in. If none is passed,
171 -- 'PO' will be assumed
172 --IN OUT:
173 --OUT:
174 --End of Comments
175 ------------------------------------------------------------------------
176 PROCEDURE add_warning
177 ( p_interface_header_id IN NUMBER,
178 p_interface_line_id IN NUMBER := NULL,
179 p_interface_line_location_id IN NUMBER := NULL,
180 p_interface_distribution_id IN NUMBER := NULL,
181 p_price_diff_interface_id IN NUMBER := NULL,
182 p_interface_attr_values_id IN NUMBER := NULL,
183 p_interface_attr_values_tlp_id IN NUMBER := NULL,
184 p_app_name IN VARCHAR2 := NULL,
185 p_error_message_name IN VARCHAR2 := NULL,
186 p_table_name IN VARCHAR2 := NULL,
187 p_column_name IN VARCHAR2 := NULL,
188 p_column_value IN VARCHAR2 := NULL,
189 p_token1_name IN VARCHAR2 := NULL,
190 p_token1_value IN VARCHAR2 := NULL,
191 p_token2_name IN VARCHAR2 := NULL,
192 p_token2_value IN VARCHAR2 := NULL,
193 p_token3_name IN VARCHAR2 := NULL,
194 p_token3_value IN VARCHAR2 := NULL,
195 p_token4_name IN VARCHAR2 := NULL,
196 p_token4_value IN VARCHAR2 := NULL,
197 p_token5_name IN VARCHAR2 := NULL,
198 p_token5_value IN VARCHAR2 := NULL,
199 p_token6_name IN VARCHAR2 := NULL,
200 p_token6_value IN VARCHAR2 := NULL,
201 p_error_message IN VARCHAR2 := NULL
202 ) IS
203 d_api_name CONSTANT VARCHAR2(30) := 'add_warning';
204 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
205 d_position NUMBER;
206
207 l_rec PO_INTERFACE_ERRORS%ROWTYPE;
208
209 BEGIN
210 d_position := 0;
211
212 IF (PO_LOG.d_proc) THEN
213 PO_LOG.proc_begin(d_module);
214 END IF;
215
216 l_rec.interface_type := 'PO_DOCS_OPEN_INTERFACE';
217 l_rec.interface_header_id := p_interface_header_id;
218 l_rec.interface_line_id := p_interface_line_id;
219 l_rec.interface_line_location_id := p_interface_line_location_id;
220 l_rec.interface_distribution_id := p_interface_distribution_id;
221 l_rec.interface_attr_values_id := p_interface_attr_values_id;
222 l_rec.interface_attr_values_tlp_id := p_interface_attr_values_tlp_id;
223 l_rec.price_diff_interface_id := p_price_diff_interface_id; -- bug 5215781
224 l_rec.app_name := NVL(p_app_name, g_APP_PO);
225 l_rec.error_message_name := p_error_message_name;
226 l_rec.table_name := p_table_name;
227 l_rec.column_name := p_column_name;
228 l_rec.column_value := p_column_value;
229 --bug 12812134: truncating the values assigned to token values to 200,
230 -- the column size of token values in po_interface_errors.
231 l_rec.token1_name := p_token1_name;
232 l_rec.token1_value := SubStr(p_token1_value,1,200);
233 l_rec.token2_name := p_token2_name;
234 l_rec.token2_value := SubStr(p_token2_value,1,200);
235 l_rec.token3_name := p_token3_name;
236 l_rec.token3_value := SubStr(p_token3_value,1,200);
237 l_rec.token4_name := p_token4_name;
238 l_rec.token4_value := SubStr(p_token4_value,1,200);
239 l_rec.token5_name := p_token5_name;
240 l_rec.token5_value := SubStr(p_token5_value,1,200);
241 l_rec.token6_name := p_token6_name;
242 l_rec.token6_value := SubStr(p_token6_value,1,200);
243 l_rec.error_message := p_error_message; -- bug5385342
244
245 d_position := 10;
246
247 PO_INTERFACE_ERRORS_UTL.add_to_errors_tbl
248 ( p_err_type => 'WARNING',
249 p_err_rec => l_rec
250 );
251
252 d_position := 20;
253
254 IF (PO_LOG.d_proc) THEN
255 PO_LOG.proc_end(d_module);
256 END IF;
257 EXCEPTION
258 WHEN OTHERS THEN
259 PO_MESSAGE_S.add_exc_msg
260 ( p_pkg_name => d_pkg_name,
261 p_procedure_name => d_api_name || '.' || d_position
262 );
263 RAISE;
264 END add_warning;
265
266 -----------------------------------------------------------------------
267 --Start of Comments
268 --Name: add_fatal_error
269 --Function:
270 -- insert an error to the strucutre. The structure will eventually gets
271 -- flushed to PO_INTERFACE_ERRORS table. But the error may be mapped
272 -- to a new message before the actual insert happens accoring to its
273 -- context and validation content.
274 --Parameters:
275 --IN:
276 --p_app_name
277 -- Application where the error message is defined in. If none is passed,
278 -- 'PO' will be assumed
279 --IN OUT:
280 --OUT:
281 --End of Comments
282 ------------------------------------------------------------------------
283 PROCEDURE add_fatal_error
284 ( p_interface_header_id IN NUMBER,
285 p_interface_line_id IN NUMBER := NULL,
286 p_interface_line_location_id IN NUMBER := NULL,
287 p_interface_distribution_id IN NUMBER := NULL,
288 p_price_diff_interface_id IN NUMBER := NULL,
289 p_interface_attr_values_id IN NUMBER := NULL,
290 p_interface_attr_values_tlp_id IN NUMBER := NULL,
291 p_app_name IN VARCHAR2 := NULL,
292 p_error_message_name IN VARCHAR2 := NULL,
293 p_table_name IN VARCHAR2 := NULL,
294 p_column_name IN VARCHAR2 := NULL,
295 p_column_value IN VARCHAR2 := NULL,
296 p_token1_name IN VARCHAR2 := NULL,
297 p_token1_value IN VARCHAR2 := NULL,
298 p_token2_name IN VARCHAR2 := NULL,
299 p_token2_value IN VARCHAR2 := NULL,
300 p_token3_name IN VARCHAR2 := NULL,
301 p_token3_value IN VARCHAR2 := NULL,
302 p_token4_name IN VARCHAR2 := NULL,
303 p_token4_value IN VARCHAR2 := NULL,
304 p_token5_name IN VARCHAR2 := NULL,
305 p_token5_value IN VARCHAR2 := NULL,
306 p_token6_name IN VARCHAR2 := NULL,
307 p_token6_value IN VARCHAR2 := NULL,
308 p_error_message IN VARCHAR2 := NULL,
309 p_validation_id IN NUMBER := NULL,
310 p_headers IN PO_PDOI_TYPES.headers_rec_type := NULL,
311 p_lines IN PO_PDOI_TYPES.lines_rec_type := NULL,
312 p_line_locs IN PO_PDOI_TYPES.line_locs_rec_type := NULL,
313 p_distributions IN PO_PDOI_TYPES.distributions_rec_type := NULL,
314 p_price_diffs IN PO_PDOI_TYPES.price_diffs_rec_type := NULL
315 ) IS
316
317 d_api_name CONSTANT VARCHAR2(30) := 'add_fatal_error';
318 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
319 d_position NUMBER;
320
321 l_mapping_exists VARCHAR2(1) := FND_API.G_FALSE;
322 l_mapped_err_msg PO_MSG_MAPPING_UTL.msg_rec_type;
323
324 BEGIN
325 d_position := 0;
326
327 IF (PO_LOG.d_proc) THEN
328 PO_LOG.proc_begin(d_module);
329 END IF;
330
331 IF (p_validation_id IS NOT NULL) THEN
332 -- check whether the message needs to be mapped
333 map_error_message(
334 p_validation_id => p_validation_id,
335 p_headers => p_headers,
336 p_lines => p_lines,
337 p_line_locs => p_line_locs,
338 p_price_diffs => p_price_diffs,
339 p_table_name => p_table_name,
340 p_interface_header_id => p_interface_header_id,
341 p_interface_line_id => p_interface_line_id,
342 p_interface_line_location_id => p_interface_line_location_id,
343 p_price_diff_interface_id => p_price_diff_interface_id,
344 x_mapping_exists => l_mapping_exists,
345 x_mapped_err_msg => l_mapped_err_msg
346 );
347 END IF;
348
349 -- after checking, we know that if message needs to be transformed,
350 -- l_mapping_err_msg will contain the new messages; Otherwise,
351 -- l_mapping_err_msg will be empty
352 -- In the following code, we copy the original message values to
353 -- l_mapping_err_msg if mapping does not happen; so that the message
354 -- contained in l_mapping_err_msg can be added to the error table
355 IF (l_mapping_exists = FND_API.G_FALSE) THEN
356 l_mapped_err_msg.app_name := p_app_name;
357 l_mapped_err_msg.message_name := p_error_message_name;
358 l_mapped_err_msg.column_name := p_column_name;
359 l_mapped_err_msg.column_value := p_column_value;
360 l_mapped_err_msg.token1_name := p_token1_name;
361 l_mapped_err_msg.token1_value := p_token1_value;
362 l_mapped_err_msg.token2_name := p_token2_name;
363 l_mapped_err_msg.token2_value := p_token2_value;
364 l_mapped_err_msg.token3_name := p_token3_name;
365 l_mapped_err_msg.token3_value := p_token3_value;
366 l_mapped_err_msg.token4_name := p_token4_name;
367 l_mapped_err_msg.token4_value := p_token4_value;
368 l_mapped_err_msg.token5_name := p_token5_name;
369 l_mapped_err_msg.token5_value := p_token5_value;
370 l_mapped_err_msg.token6_name := p_token6_name;
371 l_mapped_err_msg.token6_value := p_token6_value;
372 END IF;
373
374 add_error(
375 p_interface_header_id => p_interface_header_id,
376 p_interface_line_id => p_interface_line_id,
377 p_interface_line_location_id => p_interface_line_location_id,
378 p_interface_distribution_id => p_interface_distribution_id,
379 p_price_diff_interface_id => p_price_diff_interface_id,
380 p_app_name => l_mapped_err_msg.app_name,
381 p_error_message_name => l_mapped_err_msg.message_name,
382 p_table_name => p_table_name,
383 p_column_name => l_mapped_err_msg.column_name,
384 p_column_value => l_mapped_err_msg.column_value,
385 p_token1_name => l_mapped_err_msg.token1_name,
386 p_token1_value => l_mapped_err_msg.token1_value,
387 p_token2_name => l_mapped_err_msg.token2_name,
388 p_token2_value => l_mapped_err_msg.token2_value,
389 p_token3_name => l_mapped_err_msg.token3_name,
390 p_token3_value => l_mapped_err_msg.token3_value,
391 p_token4_name => l_mapped_err_msg.token4_name,
392 p_token4_value => l_mapped_err_msg.token4_value,
393 p_token5_name => l_mapped_err_msg.token5_name,
394 p_token5_value => l_mapped_err_msg.token5_value,
395 p_token6_name => l_mapped_err_msg.token6_name,
396 p_token6_value => l_mapped_err_msg.token6_value,
397 p_error_message => p_error_message -- bug5385342
398 );
399
400 IF (PO_LOG.d_proc) THEN
401 PO_LOG.proc_end(d_module);
402 END IF;
403 EXCEPTION
404 WHEN OTHERS THEN
405 PO_MESSAGE_S.add_exc_msg
406 ( p_pkg_name => d_pkg_name,
407 p_procedure_name => d_api_name || '.' || d_position
408 );
409 RAISE;
410 END add_fatal_error;
411
412 -----------------------------------------------------------------------
413 --Start of Comments
414 --Name: process_val_type_errors
415 --Function:
416 -- Procedure to process the PDOI Validation results.
417 --Parameters:
418 --IN:
419 --x_results - po_validation_results_type
420 --p_table_name - values include: PO_PDOI_CONSTANTS.g_PO_HEADERS_INTERFACE
421 -- PO_PDOI_CONSTANTS.g_PO_LINES_INTERFACE
422 -- PO_PDOI_CONSTANTS.g_PO_LINE_LOCATIONS_INTERFACE
423 -- PO_PDOI_CONSTANTS.g_PO_DISTRIBUTIONS_INTERFACE
424 -- PO_PDOI_CONSTANTS.g_PO_PRICE_DIFF_INTERFACE
425 --
426 --IN OUT:
427 --OUT:
428 --End of Comments
429 ------------------------------------------------------------------------
430 PROCEDURE process_val_type_errors
431 ( x_results IN OUT NOCOPY po_validation_results_type,
432 p_table_name IN VARCHAR2,
433 p_headers IN PO_PDOI_TYPES.headers_rec_type := NULL,
434 p_lines IN PO_PDOI_TYPES.lines_rec_type := NULL,
435 p_line_locs IN PO_PDOI_TYPES.line_locs_rec_type := NULL,
436 p_distributions IN PO_PDOI_TYPES.distributions_rec_type := NULL,
437 p_price_diffs IN PO_PDOI_TYPES.price_diffs_rec_type := NULL
438 )
439 IS
440
441 l_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
442 l_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
443 l_intf_line_loc_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
444 l_intf_dist_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
445 l_intf_price_diff_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
446
447 d_api_name CONSTANT VARCHAR2(30) := 'process_val_type_errors';
448 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
449 d_position NUMBER;
450
451 l_mapping_exists VARCHAR2(1);
452 l_mapped_err_msg PO_MSG_MAPPING_UTL.msg_rec_type;
453 l_app_name VARCHAR2(30) := NULL;
454 BEGIN
455 d_position := 0;
456
457 IF (PO_LOG.d_proc) THEN
458 PO_LOG.proc_begin(d_module, 'table_name', p_table_name);
459 END IF;
460
461 l_intf_header_id_tbl.extend(x_results.entity_id.COUNT);
462 l_intf_line_id_tbl.extend(x_results.entity_id.COUNT);
463 l_intf_line_loc_id_tbl.extend(x_results.entity_id.COUNT);
464 l_intf_dist_id_tbl.extend(x_results.entity_id.COUNT);
465 l_intf_price_diff_id_tbl.extend(x_results.entity_id.COUNT);
466
467 d_position := 10;
468
469 derive_parent_interface_ids
470 ( p_table_name => p_table_name,
471 p_table_id_tbl => x_results.entity_id,
472 x_intf_header_id_tbl => l_intf_header_id_tbl,
473 x_intf_line_id_tbl => l_intf_line_id_tbl,
474 x_intf_line_loc_id_tbl => l_intf_line_loc_id_tbl,
475 x_intf_dist_id_tbl => l_intf_dist_id_tbl,
476 x_intf_price_diff_id_tbl => l_intf_price_diff_id_tbl
477 );
478
479 d_position := 20;
480
481
482 FOR i IN 1 .. x_results.entity_id.COUNT LOOP
483
484 map_error_message
485 ( p_validation_id => x_results.validation_id(i),
486 p_headers => p_headers,
487 p_lines => p_lines,
488 p_line_locs => p_line_locs,
489 p_price_diffs => p_price_diffs,
490 p_table_name => p_table_name,
491 p_interface_header_id => l_intf_header_id_tbl(i),
492 p_interface_line_id => l_intf_line_id_tbl(i),
493 p_interface_line_location_id => l_intf_line_loc_id_tbl(i),
494 p_price_diff_interface_id => l_intf_price_diff_id_tbl(i),
495 x_mapping_exists => l_mapping_exists,
496 x_mapped_err_msg => l_mapped_err_msg
497 );
498
499 IF (l_mapping_exists = FND_API.G_TRUE) THEN
500
501 -- If we need to transform the message, then copy the values from
502 -- the returned record structure to x_results
503 l_app_name := l_mapped_err_msg.app_name;
504 x_results.message_name(i) := l_mapped_err_msg.message_name;
505 x_results.column_name(i) := l_mapped_err_msg.column_name;
506 x_results.column_val(i) := l_mapped_err_msg.column_value;
507 x_results.token1_name(i) := l_mapped_err_msg.token1_name;
508 x_results.token1_value(i) := l_mapped_err_msg.token1_value;
509 x_results.token2_name(i) := l_mapped_err_msg.token2_name;
510 x_results.token2_value(i) := l_mapped_err_msg.token2_value;
511 x_results.token3_name(i) := l_mapped_err_msg.token3_name;
512 x_results.token3_value(i) := l_mapped_err_msg.token3_value;
513 x_results.token4_name(i) := l_mapped_err_msg.token4_name;
514 x_results.token4_value(i) := l_mapped_err_msg.token4_value;
515 x_results.token5_name(i) := l_mapped_err_msg.token5_name;
516 x_results.token5_value(i) := l_mapped_err_msg.token5_value;
517 x_results.token6_name(i) := l_mapped_err_msg.token6_name;
518 x_results.token6_value(i) := l_mapped_err_msg.token6_value;
519
520 END IF;
521
522 IF x_results.result_type(i) = po_validations.c_result_type_failure THEN
523 add_error(p_interface_header_id => l_intf_header_id_tbl(i),
524 p_interface_line_id => l_intf_line_id_tbl(i),
525 p_interface_line_location_id => l_intf_line_loc_id_tbl(i),
526 p_interface_distribution_id => l_intf_dist_id_tbl(i),
527 p_price_diff_interface_id => l_intf_price_diff_id_tbl(i),
528 p_app_name => l_app_name,
529 p_error_message_name => x_results.message_name(i),
530 p_table_name => p_table_name,
531 p_column_name => x_results.column_name(i),
532 p_column_value => x_results.column_val(i),
533 p_token1_name => x_results.token1_name(i),
534 p_token1_value => x_results.token1_value(i),
535 p_token2_name => x_results.token2_name(i),
536 p_token2_value => x_results.token2_value(i),
537 p_token3_name => x_results.token3_name(i),
538 p_token3_value => x_results.token3_value(i),
539 p_token4_name => x_results.token4_name(i),
540 p_token4_value => x_results.token4_value(i),
541 p_token5_name => x_results.token5_name(i),
542 p_token5_value => x_results.token5_value(i),
543 p_token6_name => x_results.token6_name(i),
544 p_token6_value => x_results.token6_value(i));
545 END IF;
546 END LOOP;
547
548 d_position := 30;
549
550 IF (PO_LOG.d_proc) THEN
551 PO_LOG.proc_end(d_module);
552 END IF;
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 PO_MESSAGE_S.add_exc_msg
557 ( p_pkg_name => d_pkg_name,
558 p_procedure_name => d_api_name || '.' || d_position
559 );
560 RAISE;
561
562 END process_val_type_errors;
563
564
565 -----------------------------------------------------------------------
566 --Start of Comments
567 --Name: derive_parent_interface_ids
568 --Function:
569 -- Procedure to derive the parent interface ids for a given entity
570 --Parameters:
571 --IN:
572 --p_table_name - values include: PO_PDOI_CONSTANTS.g_PO_HEADERS_INTERFACE
573 -- PO_PDOI_CONSTANTS.g_PO_LINES_INTERFACE
574 -- PO_PDOI_CONSTANTS.g_PO_LINE_LOCATIONS_INTERFACE
575 -- PO_PDOI_CONSTANTS.g_PO_DISTRIBUTIONS_INTERFACE
576 -- PO_PDOI_CONSTANTS.g_PO_PRICE_DIFF_INTERFACE
577 --
578 --p_table_id_tbl - Table of interface ids that correspond to the table name passed in
579 --
580 --IN OUT:
581 --OUT:
582 --x_intf_hdr_id_tbl - Table of interface header ids
583 --x_intf_line_id_tbl - Table of interface line ids
584 --x_intf_line_loc_id_tbl - Table of interface line location ids
585 --x_intf_dist_id_tbl - Table of interface distribution ids
586 --x_intf_price_diff_id_tbl - Table of interface price differential ids
587 --End of Comments
588 ------------------------------------------------------------------------
589 PROCEDURE derive_parent_interface_ids
590 ( p_table_name IN VARCHAR2,
591 p_table_id_tbl IN PO_TBL_NUMBER,
592 x_intf_header_id_tbl OUT NOCOPY PO_TBL_NUMBER,
593 x_intf_line_id_tbl OUT NOCOPY PO_TBL_NUMBER,
594 x_intf_line_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
595 x_intf_dist_id_tbl OUT NOCOPY PO_TBL_NUMBER,
596 x_intf_price_diff_id_tbl OUT NOCOPY PO_TBL_NUMBER
597 ) IS
598
599 l_key NUMBER;
600 l_seq PO_TBL_NUMBER := PO_TBL_NUMBER();
601
602 d_api_name CONSTANT VARCHAR2(30) := 'derive_parent_interface_ids';
603 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
604 d_position NUMBER;
605
606 BEGIN
607 d_position := 0;
608
609 IF (PO_LOG.d_proc) THEN
610 PO_LOG.proc_begin(d_module, 'table_name', p_table_name);
611 END IF;
612
613 x_intf_header_id_tbl := PO_TBL_NUMBER();
614 x_intf_line_id_tbl := PO_TBL_NUMBER();
615 x_intf_line_loc_id_tbl := PO_TBL_NUMBER();
616 x_intf_dist_id_tbl := PO_TBL_NUMBER();
617 x_intf_price_diff_id_tbl := PO_TBL_NUMBER();
618
619 l_key := PO_CORE_S.get_session_gt_nextval;
620
621 l_seq.extend (p_table_id_tbl.COUNT);
622
623 -- This will intialize all out variables to have correct size
624 x_intf_header_id_tbl.extend(p_table_id_tbl.COUNT);
625 x_intf_line_id_tbl.extend(p_table_id_tbl.COUNT);
626 x_intf_line_loc_id_tbl.extend(p_table_id_tbl.COUNT);
627 x_intf_dist_id_tbl.extend(p_table_id_tbl.COUNT);
628 x_intf_price_diff_id_tbl.extend(p_table_id_tbl.COUNT);
629
630 d_position := 10;
631
632 FOR i IN 1..l_seq.COUNT LOOP
633 -- define a sequence
634 l_seq(i) := i;
635 END LOOP;
636
637 d_position := 20;
638
639 FORALL i IN 1..p_table_id_tbl.COUNT
640 INSERT INTO po_session_gt
641 ( key, index_num1, num1 )
642 VALUES
643 (l_key, l_seq(i), p_table_id_tbl(i));
644
645 d_position := 30;
646
647 IF (p_table_name = PO_PDOI_CONSTANTS.g_PO_HEADERS_INTERFACE) THEN
648
649 d_position := 40;
650
651 SELECT PHI.interface_header_id
652 BULK COLLECT
653 INTO x_intf_header_id_tbl
654 FROM po_session_gt PSG,
655 po_headers_interface PHI
656 WHERE PSG.key = l_key AND
657 PSG.num1 = PHI.interface_header_id
658 ORDER BY PSG.index_num1;
659
660 ELSIF (p_table_name = PO_PDOI_CONSTANTS.g_PO_LINES_INTERFACE) THEN
661
662 d_position := 50;
663
664 SELECT PLI.interface_header_id,
665 PLI.interface_line_id
666 BULK COLLECT
667 INTO x_intf_header_id_tbl,
668 x_intf_line_id_tbl
669 FROM po_session_gt PSG,
670 po_lines_interface PLI
671 WHERE PSG.key = l_key
672 AND PSG.num1 = PLI.interface_line_id
673 ORDER BY PSG.index_num1;
674
675 ELSIF (p_table_name = PO_PDOI_CONSTANTS.g_PO_LINE_LOCATIONS_INTERFACE) THEN
676
677 d_position := 60;
678
679 SELECT PLLI.interface_header_id,
680 PLLI.interface_line_id,
681 PLLI.interface_line_location_id
682 BULK COLLECT
683 INTO x_intf_header_id_tbl,
684 x_intf_line_id_tbl,
685 x_intf_line_loc_id_tbl
686 FROM po_session_gt PSG,
687 po_line_locations_interface PLLI
688 WHERE PSG.key = l_key
689 AND PSG.num1 = PLLI.interface_line_location_id
690 ORDER BY PSG.index_num1;
691
692 ELSIF (p_table_name = PO_PDOI_CONSTANTS.g_PO_DISTRIBUTIONS_INTERFACE) THEN
693
694 d_position := 70;
695
696 SELECT PDI.interface_header_id,
697 PDI.interface_line_id,
698 PDI.interface_line_location_id,
699 PDI.interface_distribution_id
700 BULK COLLECT
701 INTO x_intf_header_id_tbl,
702 x_intf_line_id_tbl,
703 x_intf_line_loc_id_tbl,
704 x_intf_dist_id_tbl
705 FROM po_session_gt PSG,
706 po_distributions_interface PDI
707 WHERE PSG.key = l_key
708 AND PSG.num1 = PDI.interface_distribution_id
709 ORDER BY PSG.index_num1;
710
711 ELSIF (p_table_name = PO_PDOI_CONSTANTS.g_PO_PRICE_DIFF_INTERFACE) THEN
712
713 d_position := 80;
714
715 SELECT PPDI.interface_header_id,
716 PPDI.interface_line_id,
717 PPDI.interface_line_location_id,
718 PPDI.price_diff_interface_id
719 BULK COLLECT
720 INTO x_intf_header_id_tbl,
721 x_intf_line_id_tbl,
722 x_intf_line_loc_id_tbl,
723 x_intf_price_diff_id_tbl
724 FROM po_session_gt PSG,
725 po_price_diff_interface PPDI
726 WHERE PSG.key = l_key
727 AND PSG.num1 = PPDI.price_diff_interface_id
728 ORDER BY PSG.index_num1;
729
730 END IF;
731
732 d_position := 90;
733
734 PO_PDOI_UTL.remove_session_gt_records(p_key => l_key);
735
736 d_position := 100;
737
738 IF (PO_LOG.d_proc) THEN
739 PO_LOG.proc_end(d_module);
740 END IF;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 PO_MESSAGE_S.add_exc_msg
745 ( p_pkg_name => d_pkg_name,
746 p_procedure_name => d_api_name || '.' || d_position
747 );
748 RAISE;
749
750 END derive_parent_interface_ids;
751
752 --------------------------------------------------------------------------
753 ---------------------- PRIVATE PROCEDURES --------------------------------
754 --------------------------------------------------------------------------
755
756
757 -----------------------------------------------------------------------
758 --Start of Comments
759 --Name: map_error_message
760 --Function:
761 -- funtion to map error message thrown by validation framework to ip
762 -- specific error messages based on validation id
763 --Parameters:
764 --IN:
765 -- p_validation_id
766 -- unique identifier for each validation
767 -- p_headers
768 -- batch of header records in interface table
769 -- p_lines
770 -- batch of line records in interface table
771 -- p_line_locs
772 -- batch of location records in the interface table
773 -- p_price_diffs
774 -- batch of price differential records in the interface table
775 -- p_table_name
776 -- name of interface table which contains the errorous record
777 -- p_interface_header_id
778 -- corresponding interface_header_id of the error record
779 -- p_interface_line_id
780 -- corresponding interface_line_id of the error record
781 -- p_interface_line_location_id
782 -- corresponding interface_line_location_id of the error record
783 -- p_price_diff_interface_id
784 -- corresponding price_diff_interface_id of the error record
785 --IN OUT:
786 --OUT:
787 -- x_mapping_exists
788 -- FND_API.G_TRUE if mapping exists, FND_API.G_FALSE otherwise
789 -- x_mapped_err_msg
790 -- Message that gets returned from the mapping
791 --End of Comments
792 ------------------------------------------------------------------------
793 PROCEDURE map_error_message
794 (
795 p_validation_id IN NUMBER,
796 p_headers IN PO_PDOI_TYPES.headers_rec_type := NULL,
797 p_lines IN PO_PDOI_TYPES.lines_rec_type := NULL,
798 p_line_locs IN PO_PDOI_TYPES.line_locs_rec_type := NULL,
799 p_price_diffs IN PO_PDOI_TYPES.price_diffs_rec_type := NULL,
800 p_table_name IN VARCHAR2 := NULL,
801 p_interface_header_id IN NUMBER,
802 p_interface_line_id IN NUMBER := NULL,
803 p_interface_line_location_id IN NUMBER := NULL,
804 p_price_diff_interface_id IN NUMBER := NULL,
805 x_mapping_exists OUT NOCOPY VARCHAR2,
806 x_mapped_err_msg OUT NOCOPY PO_MSG_MAPPING_UTL.msg_rec_type
807 ) IS
808
809 d_api_name CONSTANT VARCHAR2(30) := 'map_error_message';
810 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
811 d_position NUMBER;
812
813
814 BEGIN
815 d_position := 0;
816
817 IF (PO_LOG.d_proc) THEN
818 PO_LOG.proc_begin(d_module, 'p_validation_id', p_validation_id);
819 PO_LOG.proc_begin(d_module, 'p_table_name', p_table_name);
820 PO_LOG.proc_begin(d_module, 'p_interface_header_id', p_interface_header_id);
821 PO_LOG.proc_begin(d_module, 'p_interface_line_id', p_interface_line_id);
822 PO_LOG.proc_begin(d_module, 'p_interface_line_location_id',
823 p_interface_line_location_id);
824 PO_LOG.proc_begin(d_module, 'p_price_diff_interface_id',
825 p_price_diff_interface_id);
826 END IF;
827
828 -- find the message mapping, if it exists
829 PO_MSG_MAPPING_UTL.find_msg
830 ( p_context => PO_PDOI_PARAMS.g_request.calling_module,
831 p_id => p_validation_id,
832 x_msg_exists => x_mapping_exists,
833 x_msg_rec => x_mapped_err_msg
834 );
835
836 -- check whether mapping exists
837 IF (x_mapping_exists = FND_API.G_FALSE) THEN
838 IF (PO_LOG.d_proc) THEN
839 PO_LOG.proc_end(d_module, 'no mapping exists for validation id ',
840 p_validation_id);
841 END IF;
842
843 RETURN;
844 END IF;
845
846 -- get column value
847 IF (x_mapped_err_msg.column_value_key IS NOT NULL) THEN
848 x_mapped_err_msg.column_value :=
849 get_value_from_key
850 (
851 p_key => x_mapped_err_msg.column_value_key,
852 p_headers => p_headers,
853 p_lines => p_lines,
854 p_line_locs => p_line_locs,
855 p_price_diffs => p_price_diffs,
856 p_table_name => p_table_name,
857 p_interface_header_id => p_interface_header_id,
858 p_interface_line_id => p_interface_line_id,
859 p_interface_line_location_id => p_interface_line_location_id,
860 p_price_diff_interface_id => p_price_diff_interface_id
861 );
862
863 END IF;
864
865 -- get token values
866 FOR i IN 1..x_mapped_err_msg.num_of_tokens
867 LOOP
868 CASE i
869 WHEN 1 THEN
870 IF (x_mapped_err_msg.token1_value_key <>
871 x_mapped_err_msg.column_value_key) THEN
872 x_mapped_err_msg.token1_value :=
873 get_value_from_key
874 (
875 p_key => x_mapped_err_msg.token1_value_key,
876 p_headers => p_headers,
877 p_lines => p_lines,
878 p_line_locs => p_line_locs,
879 p_price_diffs => p_price_diffs,
880 p_table_name => p_table_name,
881 p_interface_header_id => p_interface_header_id,
882 p_interface_line_id => p_interface_line_id,
883 p_interface_line_location_id => p_interface_line_location_id,
884 p_price_diff_interface_id => p_price_diff_interface_id
885 );
886 ELSE
887 x_mapped_err_msg.token1_value := x_mapped_err_msg.column_value;
888 END IF;
889 WHEN 2 THEN
890 IF (x_mapped_err_msg.token2_value_key <>
891 x_mapped_err_msg.column_value_key) THEN
892 x_mapped_err_msg.token2_value :=
893 get_value_from_key
894 (
895 p_key => x_mapped_err_msg.token2_value_key,
896 p_headers => p_headers,
897 p_lines => p_lines,
898 p_line_locs => p_line_locs,
899 p_price_diffs => p_price_diffs,
900 p_table_name => p_table_name,
901 p_interface_header_id => p_interface_header_id,
902 p_interface_line_id => p_interface_line_id,
903 p_interface_line_location_id => p_interface_line_location_id,
904 p_price_diff_interface_id => p_price_diff_interface_id
905 );
906 ELSE
907 x_mapped_err_msg.token2_value := x_mapped_err_msg.column_value;
908 END IF;
909 WHEN 3 THEN
910 IF (x_mapped_err_msg.token3_value_key <>
911 x_mapped_err_msg.column_value_key) THEN
912 x_mapped_err_msg.token3_value :=
913 get_value_from_key
914 (
915 p_key => x_mapped_err_msg.token3_value_key,
916 p_headers => p_headers,
917 p_lines => p_lines,
918 p_line_locs => p_line_locs,
919 p_price_diffs => p_price_diffs,
920 p_table_name => p_table_name,
921 p_interface_header_id => p_interface_header_id,
922 p_interface_line_id => p_interface_line_id,
923 p_interface_line_location_id => p_interface_line_location_id,
924 p_price_diff_interface_id => p_price_diff_interface_id
925 );
926 ELSE
927 x_mapped_err_msg.token3_value := x_mapped_err_msg.column_value;
928 END IF;
929 WHEN 4 THEN
930 IF (x_mapped_err_msg.token4_value_key <>
931 x_mapped_err_msg.column_value_key) THEN
932 x_mapped_err_msg.token4_value :=
933 get_value_from_key
934 (
935 p_key => x_mapped_err_msg.token4_value_key,
936 p_headers => p_headers,
937 p_lines => p_lines,
938 p_line_locs => p_line_locs,
939 p_price_diffs => p_price_diffs,
940 p_table_name => p_table_name,
941 p_interface_header_id => p_interface_header_id,
942 p_interface_line_id => p_interface_line_id,
943 p_interface_line_location_id => p_interface_line_location_id,
944 p_price_diff_interface_id => p_price_diff_interface_id
945 );
946 ELSE
947 x_mapped_err_msg.token4_value := x_mapped_err_msg.column_value;
948 END IF;
949 WHEN 5 THEN
950 IF (x_mapped_err_msg.token5_value_key <>
951 x_mapped_err_msg.column_value_key) THEN
952 x_mapped_err_msg.token5_value :=
953 get_value_from_key
954 (
955 p_key => x_mapped_err_msg.token5_value_key,
956 p_headers => p_headers,
957 p_lines => p_lines,
958 p_line_locs => p_line_locs,
959 p_price_diffs => p_price_diffs,
960 p_table_name => p_table_name,
961 p_interface_header_id => p_interface_header_id,
962 p_interface_line_id => p_interface_line_id,
963 p_interface_line_location_id => p_interface_line_location_id,
964 p_price_diff_interface_id => p_price_diff_interface_id
965 );
966 ELSE
967 x_mapped_err_msg.token5_value := x_mapped_err_msg.column_value;
968 END IF;
969 WHEN 6 THEN
970 IF (x_mapped_err_msg.token6_value_key <>
971 x_mapped_err_msg.column_value_key) THEN
972 x_mapped_err_msg.token6_value :=
973 get_value_from_key
974 (
975 p_key => x_mapped_err_msg.token6_value_key,
976 p_headers => p_headers,
977 p_lines => p_lines,
978 p_line_locs => p_line_locs,
979 p_price_diffs => p_price_diffs,
980 p_table_name => p_table_name,
981 p_interface_header_id => p_interface_header_id,
982 p_interface_line_id => p_interface_line_id,
983 p_interface_line_location_id => p_interface_line_location_id,
984 p_price_diff_interface_id => p_price_diff_interface_id
985 );
986 ELSE
987 x_mapped_err_msg.token6_value := x_mapped_err_msg.column_value;
988 END IF;
989 END CASE;
990 END LOOP;
991
992 IF (PO_LOG.d_proc) THEN
993 PO_LOG.proc_end(d_module);
994 END IF;
995
996 EXCEPTION
997 WHEN OTHERS THEN
998 PO_MESSAGE_S.add_exc_msg
999 ( p_pkg_name => d_pkg_name,
1000 p_procedure_name => d_api_name || '.' || d_position
1001 );
1002 RAISE;
1003 END map_error_message;
1004
1005 -----------------------------------------------------------------------
1006 --Start of Comments
1007 --Name: get_value_from_key
1008 --Function:
1009 -- funtion to get interface record value based on the key
1010 --Parameters:
1011 --IN:
1012 -- p_key
1013 -- unique identifier for a particular attribute in interface record
1014 -- p_headers
1015 -- batch of header records in interface table
1016 -- p_lines
1017 -- batch of line records in interface table
1018 -- p_line_locs
1019 -- batch of location records in the interface table
1020 -- p_price_diffs
1021 -- batch of price differential records in the interface table
1022 -- p_table_name
1023 -- name of interface table which contains the errorous record
1024 -- p_interface_header_id
1025 -- corresponding interface_header_id of the error record
1026 -- p_interface_line_id
1027 -- corresponding interface_line_id of the error record
1028 -- p_interface_line_location_id
1029 -- corresponding interface_line_location_id of the error record
1030 -- p_price_diff_interface_id
1031 -- corresponding price_diff_interface_id of the error record
1032 --IN OUT:
1033 --OUT:
1034 --RETURN: value of an attribute in interface record for the particular key
1035 --End of Comments
1036 ------------------------------------------------------------------------
1037 FUNCTION get_value_from_key
1038 (
1039 p_key IN VARCHAR2,
1040 p_headers IN PO_PDOI_TYPES.headers_rec_type := NULL,
1041 p_lines IN PO_PDOI_TYPES.lines_rec_type := NULL,
1042 p_line_locs IN PO_PDOI_TYPES.line_locs_rec_type := NULL,
1043 p_price_diffs IN PO_PDOI_TYPES.price_diffs_rec_type := NULL,
1044 p_table_name IN VARCHAR2 := NULL,
1045 p_interface_header_id IN NUMBER,
1046 p_interface_line_id IN NUMBER := NULL,
1047 p_interface_line_location_id IN NUMBER := NULL,
1048 p_price_diff_interface_id IN NUMBER := NULL
1049 ) RETURN VARCHAR2 IS
1050
1051 d_api_name CONSTANT VARCHAR2(30) := 'get_value_from_key';
1052 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1053 d_position NUMBER;
1054
1055 l_value VARCHAR2(4000) := NULL;
1056 l_index NUMBER;
1057 BEGIN
1058 d_position := 0;
1059
1060 IF (PO_LOG.d_proc) THEN
1061 PO_LOG.proc_begin(d_module, 'p_key', p_key);
1062 PO_LOG.proc_begin(d_module, 'p_table_name', p_table_name);
1063 PO_LOG.proc_begin(d_module, 'p_interface_header_id', p_interface_header_id);
1064 PO_LOG.proc_begin(d_module, 'p_interface_line_id', p_interface_line_id);
1065 PO_LOG.proc_begin(d_module, 'p_interface_line_location_id',
1066 p_interface_line_location_id);
1067 PO_LOG.proc_begin(d_module, 'p_price_diff_interface_id',
1068 p_price_diff_interface_id);
1069 END IF;
1070
1071 -- get value from each interface record based on p_table_name
1072 CASE p_table_name
1073 WHEN PO_PDOI_CONSTANTS.g_PO_HEADERS_INTERFACE THEN
1074 d_position := 10;
1075 l_index := p_headers.intf_id_index_tbl(p_interface_header_id);
1076 IF (p_key = PO_MSG_MAPPING_UTL.c_rate_type) THEN
1077 l_value := p_headers.rate_type_tbl(l_index);
1078 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_agent_name) THEN
1079 l_value := p_headers.agent_name_tbl(l_index);
1080 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_ship_to_location) THEN
1081 l_value := p_headers.ship_to_loc_tbl(l_index);
1082 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_bill_to_location) THEN
1083 l_value := p_headers.bill_to_loc_tbl(l_index);
1084 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_payment_terms) THEN
1085 l_value := p_headers.payment_terms_tbl(l_index);
1086 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_vendor_name) THEN
1087 l_value := p_headers.vendor_name_tbl(l_index);
1088 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_vendor_site_code) THEN
1089 l_value := p_headers.vendor_site_code_tbl(l_index);
1090 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_vendor_contact) THEN
1091 l_value := p_headers.vendor_contact_tbl(l_index);
1092 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_style_display_name) THEN
1093 l_value := p_headers.style_display_name_tbl(l_index);
1094 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_from_rfq_num) THEN
1095 l_value := p_headers.from_rfq_num_tbl(l_index);
1096 ELSE
1097 NULL;
1098 END IF;
1099 WHEN PO_PDOI_CONSTANTS.g_PO_LINES_INTERFACE THEN
1100 d_position := 20;
1101 l_index := p_lines.intf_id_index_tbl(p_interface_line_id);
1102 IF (p_key = PO_MSG_MAPPING_UTL.c_item) THEN
1103 l_value := p_lines.item_tbl(l_index);
1104 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_item_desc) THEN
1105 l_value := p_lines.item_desc_tbl(l_index);
1106 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_job_business_group_name) THEN
1107 l_value := p_lines.job_business_group_name_tbl(l_index);
1108 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_job_name) THEN
1109 l_value := p_lines.job_name_tbl(l_index);
1110 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_category) THEN
1111 l_value := p_lines.category_tbl(l_index);
1112 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_ip_category) THEN
1113 l_value := p_lines.ip_category_tbl(l_index);
1114 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_uom_code) THEN
1115 -- bug5619358
1116 -- For Catalog Upload, we need to get the value from
1117 -- UNI_MEAS_LOOKUP_CODE if UOM_CODE is NULL
1118 IF (PO_PDOI_PARAMS.g_request.calling_module =
1119 PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD) THEN
1120
1121 l_value := NVL(p_lines.uom_code_tbl(l_index),
1122 p_lines.unit_of_measure_tbl(l_index));
1123 END IF;
1124 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_line_type) THEN
1125 l_value := p_lines.line_type_tbl(l_index);
1126 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_un_number) THEN
1127 l_value := p_lines.un_number_tbl(l_index);
1128 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_hazard_class) THEN
1129 l_value := p_lines.hazard_class_tbl(l_index);
1130 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_template_name) THEN
1131 l_value := p_lines.template_name_tbl(l_index);
1132 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_amount) THEN
1133 l_value := p_lines.amount_tbl(l_index);
1134 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_unit_price) THEN
1135 l_value := p_lines.unit_price_tbl(l_index);
1136 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_line_num) THEN
1137 l_value := p_lines.line_num_tbl(l_index);
1138 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_quantity) THEN
1139 l_value := p_lines.quantity_tbl(l_index);
1140 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_item_revision) THEN
1141 l_value := p_lines.item_revision_tbl(l_index);
1142 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_ga_flag) THEN
1143 l_value := p_lines.hd_global_agreement_flag_tbl(l_index);
1144 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_negotiated_flag) THEN
1145 l_value := p_lines.negotiated_flag_tbl(l_index);
1146 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_created_language) THEN
1147 l_value := p_lines.hd_created_language_tbl(l_index);
1148 ELSE
1149 NULL;
1150 END IF;
1151 WHEN PO_PDOI_CONSTANTS.g_PO_LINE_LOCATIONS_INTERFACE THEN
1152 d_position := 30;
1153 l_index := p_line_locs.intf_id_index_tbl(p_interface_line_location_id);
1154 IF (p_key = PO_MSG_MAPPING_UTL.c_ship_to_organization_code) THEN
1155 l_value := p_line_locs.ship_to_org_code_tbl(l_index);
1156 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_loc_ship_to_location) THEN
1157 l_value := p_line_locs.ship_to_loc_tbl(l_index);
1158 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_payment_terms) THEN
1159 l_value := p_line_locs.payment_terms_tbl(l_index);
1160 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_receiving_routing) THEN
1161 l_value := p_line_locs.receiving_routing_tbl(l_index);
1162 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_tax_code_id) THEN
1163 l_value := p_line_locs.tax_code_id_tbl(l_index);
1164 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_price_discount) THEN
1165 l_value := p_line_locs.price_discount_tbl(l_index);
1166 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_style_id) THEN
1167 l_value := p_line_locs.hd_style_id_tbl(l_index);
1168 ELSIF (p_key = PO_MSG_MAPPING_UTL.c_start_date) THEN
1169 l_value := p_line_locs.start_date_tbl(l_index);
1170 ELSE
1171 NULL;
1172 END IF;
1173 WHEN PO_PDOI_CONSTANTS.g_PO_PRICE_DIFF_INTERFACE THEN
1174 NULL;
1175 END CASE;
1176
1177 IF (PO_LOG.d_proc) THEN
1178 PO_LOG.proc_end(d_module);
1179 END IF;
1180
1181 RETURN l_value;
1182
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 PO_MESSAGE_S.add_exc_msg
1186 ( p_pkg_name => d_pkg_name,
1187 p_procedure_name => d_api_name || '.' || d_position
1188 );
1189 RAISE;
1190 END get_value_from_key;
1191
1192
1193 END PO_PDOI_ERR_UTL;