1 PACKAGE BODY EC_Code_Conversion_PVT AS
2 -- $Header: ECVXREFB.pls 120.2.12000000.2 2007/02/09 17:32:02 cpeixoto ship $
3
4 debug_mode_on_int BOOLEAN := FALSE;
5
6
7 PROCEDURE Convert_from_ext_to_int
8 (p_api_version_number IN NUMBER,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_count OUT NOCOPY NUMBER,
15 p_msg_data OUT NOCOPY VARCHAR2,
16 p_Category IN VARCHAR2,
17 p_Key1 IN VARCHAR2 := NULL,
18 p_Key2 IN VARCHAR2 := NULL,
19 p_Key3 IN VARCHAR2 := NULL,
20 p_Key4 IN VARCHAR2 := NULL,
21 p_Key5 IN VARCHAR2 := NULL,
22 p_Ext_val1 IN VARCHAR2,
23 p_Ext_val2 IN VARCHAR2 := NULL,
24 p_Ext_val3 IN VARCHAR2 := NULL,
25 p_Ext_val4 IN VARCHAR2 := NULL,
26 p_Ext_val5 IN VARCHAR2 := NULL,
27 p_Int_val OUT NOCOPY VARCHAR2) IS
28
29 l_api_name CONSTANT VARCHAR2(30) := 'Convert_from_ext_to_int';
30 l_api_version_number CONSTANT NUMBER := 1.0;
31
32 /* bug 5865153
33 l_Int_val varchar2(80) := NULL; */
34
35 l_Int_val ece_xref_data.xref_int_value%TYPE := NULL;
36
37
38 CURSOR match_5 IS
39 SELECT XREF_INT_VALUE
40 FROM ECE_XREF_DATA
41 WHERE XREF_KEY1 IS NOT NULL AND
42 XREF_KEY2 IS NOT NULL AND
43 XREF_KEY3 IS NOT NULL AND
44 XREF_KEY4 IS NOT NULL AND
45 XREF_KEY5 IS NOT NULL AND
46 XREF_KEY1 = p_Key1 AND
47 XREF_KEY2 = p_Key2 AND
48 XREF_KEY3 = p_Key3 AND
49 XREF_KEY4 = p_Key4 AND
50 XREF_KEY5 = p_Key5 AND
51 XREF_CATEGORY_CODE = p_Category AND
52 XREF_EXT_VALUE1 = p_Ext_val1 AND
53 (DIRECTION = 'IN' or DIRECTION = 'BOTH') AND
54 NVL(XREF_EXT_VALUE2,FND_API.G_MISS_CHAR) = NVL(p_Ext_val2, FND_API.G_MISS_CHAR) AND
55 NVL(XREF_EXT_VALUE3,FND_API.G_MISS_CHAR) = NVL(p_Ext_val3, FND_API.G_MISS_CHAR) AND
56 NVL(XREF_EXT_VALUE4,FND_API.G_MISS_CHAR) = NVL(p_Ext_val4, FND_API.G_MISS_CHAR) AND
57 NVL(XREF_EXT_VALUE5,FND_API.G_MISS_CHAR) = NVL(p_Ext_val5, FND_API.G_MISS_CHAR);
58
59 CURSOR match_4 IS
60 SELECT XREF_INT_VALUE
61 FROM ECE_XREF_DATA
62 WHERE XREF_KEY1 IS NOT NULL AND
63 XREF_KEY2 IS NOT NULL AND
64 XREF_KEY3 IS NOT NULL AND
65 XREF_KEY4 IS NOT NULL AND
66 XREF_KEY5 IS NULL AND
67 XREF_KEY1 = p_Key1 AND
68 XREF_KEY2 = p_Key2 AND
69 XREF_KEY3 = p_Key3 AND
70 XREF_KEY4 = p_Key4 AND
71 XREF_CATEGORY_CODE = p_Category AND
72 XREF_EXT_VALUE1 = p_Ext_val1 AND
73 (DIRECTION = 'IN' or DIRECTION = 'BOTH') AND
74 NVL(XREF_EXT_VALUE2,FND_API.G_MISS_CHAR) = NVL(p_Ext_val2, FND_API.G_MISS_CHAR) AND
75 NVL(XREF_EXT_VALUE3,FND_API.G_MISS_CHAR) = NVL(p_Ext_val3, FND_API.G_MISS_CHAR) AND
76 NVL(XREF_EXT_VALUE4,FND_API.G_MISS_CHAR) = NVL(p_Ext_val4, FND_API.G_MISS_CHAR) AND
77 NVL(XREF_EXT_VALUE5,FND_API.G_MISS_CHAR) = NVL(p_Ext_val5, FND_API.G_MISS_CHAR);
78
79 CURSOR match_3 IS
80 SELECT XREF_INT_VALUE
81 FROM ECE_XREF_DATA
82 WHERE XREF_KEY1 IS NOT NULL AND
83 XREF_KEY2 IS NOT NULL AND
84 XREF_KEY3 IS NOT NULL AND
85 XREF_KEY4 IS NULL AND
86 XREF_KEY5 IS NULL AND
87 XREF_KEY1 = p_Key1 AND
88 XREF_KEY2 = p_Key2 AND
89 XREF_KEY3 = p_Key3 AND
90 XREF_CATEGORY_CODE = p_Category AND
91 XREF_EXT_VALUE1 = p_Ext_val1 AND
92 (DIRECTION = 'IN' or DIRECTION = 'BOTH') AND
93 NVL(XREF_EXT_VALUE2,FND_API.G_MISS_CHAR) = NVL(p_Ext_val2, FND_API.G_MISS_CHAR) AND
94 NVL(XREF_EXT_VALUE3,FND_API.G_MISS_CHAR) = NVL(p_Ext_val3, FND_API.G_MISS_CHAR) AND
95 NVL(XREF_EXT_VALUE4,FND_API.G_MISS_CHAR) = NVL(p_Ext_val4, FND_API.G_MISS_CHAR) AND
96 NVL(XREF_EXT_VALUE5,FND_API.G_MISS_CHAR) = NVL(p_Ext_val5, FND_API.G_MISS_CHAR);
97
98 CURSOR match_2 IS
99 SELECT XREF_INT_VALUE
100 FROM ECE_XREF_DATA
101 WHERE XREF_KEY1 IS NOT NULL AND
102 XREF_KEY2 IS NOT NULL AND
103 XREF_KEY3 IS NULL AND
104 XREF_KEY4 IS NULL AND
105 XREF_KEY5 IS NULL AND
106 XREF_KEY1 = p_Key1 AND
107 XREF_KEY2 = p_Key2 AND
108 XREF_CATEGORY_CODE = p_Category AND
109 XREF_EXT_VALUE1 = p_Ext_val1 AND
110 (DIRECTION = 'IN' or DIRECTION = 'BOTH') AND
111 NVL(XREF_EXT_VALUE2,FND_API.G_MISS_CHAR) = NVL(p_Ext_val2, FND_API.G_MISS_CHAR) AND
112 NVL(XREF_EXT_VALUE3,FND_API.G_MISS_CHAR) = NVL(p_Ext_val3, FND_API.G_MISS_CHAR) AND
113 NVL(XREF_EXT_VALUE4,FND_API.G_MISS_CHAR) = NVL(p_Ext_val4, FND_API.G_MISS_CHAR) AND
114 NVL(XREF_EXT_VALUE5,FND_API.G_MISS_CHAR) = NVL(p_Ext_val5, FND_API.G_MISS_CHAR);
115
116 CURSOR match_1 IS
117 SELECT XREF_INT_VALUE
118 FROM ECE_XREF_DATA
119 WHERE XREF_KEY1 IS NOT NULL AND
120 XREF_KEY2 IS NULL AND
121 XREF_KEY3 IS NULL AND
122 XREF_KEY4 IS NULL AND
123 XREF_KEY5 IS NULL AND
124 XREF_KEY1 = p_Key1 AND
125 XREF_CATEGORY_CODE = p_Category AND
126 XREF_EXT_VALUE1 = p_Ext_val1 AND
127 (DIRECTION = 'IN' or DIRECTION = 'BOTH') AND
128 NVL(XREF_EXT_VALUE2,FND_API.G_MISS_CHAR) = NVL(p_Ext_val2, FND_API.G_MISS_CHAR) AND
129 NVL(XREF_EXT_VALUE3,FND_API.G_MISS_CHAR) = NVL(p_Ext_val3, FND_API.G_MISS_CHAR) AND
130 NVL(XREF_EXT_VALUE4,FND_API.G_MISS_CHAR) = NVL(p_Ext_val4, FND_API.G_MISS_CHAR) AND
131 NVL(XREF_EXT_VALUE5,FND_API.G_MISS_CHAR) = NVL(p_Ext_val5, FND_API.G_MISS_CHAR);
132
133 CURSOR match_global IS
134 SELECT XREF_INT_VALUE
135 FROM ECE_XREF_DATA
136 WHERE XREF_KEY1 IS NULL AND
137 XREF_KEY2 IS NULL AND
138 XREF_KEY3 IS NULL AND
139 XREF_KEY4 IS NULL AND
140 XREF_KEY5 IS NULL AND
141 XREF_CATEGORY_CODE = p_Category AND
142 XREF_EXT_VALUE1 = p_Ext_val1 AND
143 (DIRECTION = 'IN' or DIRECTION = 'BOTH') AND
144 NVL(XREF_EXT_VALUE2,FND_API.G_MISS_CHAR) = NVL(p_Ext_val2, FND_API.G_MISS_CHAR) AND
145 NVL(XREF_EXT_VALUE3,FND_API.G_MISS_CHAR) = NVL(p_Ext_val3, FND_API.G_MISS_CHAR) AND
146 NVL(XREF_EXT_VALUE4,FND_API.G_MISS_CHAR) = NVL(p_Ext_val4, FND_API.G_MISS_CHAR) AND
147 NVL(XREF_EXT_VALUE5,FND_API.G_MISS_CHAR) = NVL(p_Ext_val5, FND_API.G_MISS_CHAR);
148
149 BEGIN
150 if EC_DEBUG.G_debug_level >= 2 then
151 EC_DEBUG.PUSH('EC_Code_Conversion_PVT.Convert_from_ext_to_int');
152 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
153 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
154 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
155 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
156 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
157 EC_DEBUG.PL(3, 'p_Category: ',p_Category);
158 EC_DEBUG.PL(3, 'p_Key1: ',p_Key1);
159 EC_DEBUG.PL(3, 'p_Key2: ',p_Key2);
160 EC_DEBUG.PL(3, 'p_Key3: ',p_Key3);
161 EC_DEBUG.PL(3, 'p_Key4: ',p_Key4);
162 EC_DEBUG.PL(3, 'p_Key5: ',p_Key5);
163 EC_DEBUG.PL(3, 'p_Ext_val1: ',p_Ext_val1);
164 EC_DEBUG.PL(3, 'p_Ext_val2: ',p_Ext_val2);
165 EC_DEBUG.PL(3, 'p_Ext_val3: ',p_Ext_val3);
166 EC_DEBUG.PL(3, 'p_Ext_val4: ',p_Ext_val4);
167 EC_DEBUG.PL(3, 'p_Ext_val5: ',p_Ext_val5);
168 end if;
169 -- Standard Start of API savepoint
170 -- SAVEPOINT Convert_from_ext_to_int_PVT;
171
172 -- Standard call to check for call compatibility.
173 IF NOT FND_API.Compatible_API_Call
174 (l_api_version_number,
175 p_api_version_number,
176 l_api_name, G_PKG_NAME) THEN
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179
180 -- Initialize message list if p_init_msg_list is set to TRUE.
181 IF FND_API.to_Boolean(p_init_msg_list) THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184
185 -- Initialize API return status to success
186 p_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 -- *******************************************************
189 -- first validate the input key1-5
190 -- The input has to be one of the following
191 -- 1. key1, key2, key3, key4, key5 all not null
192 -- 2. key1, key2, key3, key4 all not null
193 -- 3. key1, key2, key3 all not null
194 -- 4. key1, key2 all not null
195 -- 5. key1 all not null
196 -- 6. All null
197 -- *******************************************************
198 IF (((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND (p_Key3 IS NOT NULL) AND
199 (p_Key4 IS NOT NULL) AND (p_Key5 IS NOT NULL))
200 OR ((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND (p_Key3 IS NOT NULL) AND
201 (p_Key4 IS NOT NULL) AND (p_Key5 IS NULL))
202 OR ((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND (p_Key3 IS NOT NULL) AND
203 (p_Key4 IS NULL) AND (p_Key5 IS NULL))
204 OR ((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND (p_Key3 IS NULL) AND
205 (p_Key4 IS NULL) AND (p_Key5 IS NULL))
206 OR ((p_Key1 IS NOT NULL) AND (p_Key2 IS NULL) AND (p_Key3 IS NULL) AND
207 (p_Key4 IS NULL) AND (p_Key5 IS NULL))) THEN
208
209 -- *******************************************************
210 -- Start with matching all five keys
211 -- if no 5-key matches -> search to match 4 keys
212 -- if no 4-key matches -> search to match 3 keys
213 -- if no 3-key matches -> search to match 2 keys
214 -- if no 2-key matches -> search to match 1 keys
215 -- if no 1-key matches -> search for generic match
216 -- *******************************************************
217
218 -- *******************************************************
219 -- All 5 keys are supplied
220 -- *******************************************************
221 IF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL) OR (p_Key3 IS NULL) OR
222 (p_Key4 IS NULL) OR (p_Key5 IS NULL))) THEN
223 OPEN match_5;
224 FETCH match_5 INTO l_Int_val;
225 IF match_5%NOTFOUND THEN
226 OPEN match_4;
227 FETCH match_4 INTO l_Int_val;
228 IF match_4%NOTFOUND THEN
229 OPEN match_3;
230 FETCH match_3 INTO l_Int_val;
231 IF match_3%NOTFOUND THEN
232 OPEN match_2;
233 FETCH match_2 INTO l_Int_val;
234 IF match_2%NOTFOUND THEN
235 OPEN match_1;
236 FETCH match_1 INTO l_Int_val;
237 IF match_1%NOTFOUND THEN
238 OPEN match_global;
239 FETCH match_global INTO l_Int_val;
240 CLOSE match_global;
241 END IF;
242 CLOSE match_1;
243 END IF;
244 CLOSE match_2;
245 END IF;
246 CLOSE match_3;
247 END IF;
248 CLOSE match_4;
249 END IF;
250 CLOSE match_5;
251
252 -- *******************************************************
253 -- Four (4) keys are supplied
254 -- *******************************************************
255 ELSIF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL) OR
256 (p_Key3 IS NULL) OR (p_Key4 IS NULL))) THEN
257 OPEN match_4;
258 FETCH match_4 INTO l_Int_val;
259 IF match_4%NOTFOUND THEN
260 OPEN match_3;
261 FETCH match_3 INTO l_Int_val;
262 IF match_3%NOTFOUND THEN
263 OPEN match_2;
264 FETCH match_2 INTO l_Int_val;
265 IF match_2%NOTFOUND THEN
266 OPEN match_1;
267 FETCH match_1 INTO l_Int_val;
268 IF match_1%NOTFOUND THEN
269 OPEN match_global;
270 FETCH match_global INTO l_Int_val;
271 CLOSE match_global;
272 END IF;
273 CLOSE match_1;
274 END IF;
275 CLOSE match_2;
276 END IF;
277 CLOSE match_3;
278 END IF;
279 CLOSE match_4;
280
281 -- *******************************************************
282 -- Three (3) keys are supplied
283 -- *******************************************************
284 ELSIF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL) OR
285 (p_Key3 IS NULL))) THEN
286 OPEN match_3;
287 FETCH match_3 INTO l_Int_val;
288 IF match_3%NOTFOUND THEN
289 OPEN match_2;
290 FETCH match_2 INTO l_Int_val;
291 IF match_2%NOTFOUND THEN
292 OPEN match_1;
293 FETCH match_1 INTO l_Int_val;
294 IF match_1%NOTFOUND THEN
295 OPEN match_global;
296 FETCH match_global INTO l_Int_val;
297 CLOSE match_global;
298 END IF;
299 CLOSE match_1;
300 END IF;
301 CLOSE match_2;
302 END IF;
303 CLOSE match_3;
304 if EC_DEBUG.G_debug_level >= 3 then
305 EC_DEBUG.PL(3, 'l_Int_val :', l_Int_val);
306 end if;
307 -- *******************************************************
308 -- Two (2) keys are supplied
309 -- *******************************************************
310 ELSIF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL))) THEN
311 OPEN match_2;
312 FETCH match_2 INTO l_Int_val;
313 IF match_2%NOTFOUND THEN
314 OPEN match_1;
315 FETCH match_1 INTO l_Int_val;
316 IF match_1%NOTFOUND THEN
317 OPEN match_global;
318 FETCH match_global INTO l_Int_val;
319 CLOSE match_global;
320 END IF;
321 CLOSE match_1;
322 END IF;
323 CLOSE match_2;
324
325 -- *******************************************************
326 -- One (1) key is supplied
327 -- *******************************************************
328 ELSIF (p_Key1 IS NOT NULL) THEN
329 OPEN match_1;
330 FETCH match_1 INTO l_Int_val;
331 IF match_1%NOTFOUND THEN
332 OPEN match_global;
333 FETCH match_global INTO l_Int_val;
334 CLOSE match_global;
335 END IF;
336 CLOSE match_1;
337 END IF;
338 ELSE
339
340 -- *******************************
341 -- all keys (1-5) are NULLs
342 -- *******************************
343 OPEN match_global;
344 FETCH match_global INTO l_Int_val;
345 CLOSE match_global;
346 END IF;
347
348 -- *******************************************************
349 -- Standard check of p_simulate and p_commit parameters
350 -- *******************************************************
351 /*
352 IF FND_API.To_Boolean(p_simulate) THEN
353 ROLLBACK TO Convert_from_ext_to_int_PVT;
354 ELSIF FND_API.To_Boolean(p_commit) THEN
355 COMMIT WORK;
356 END IF;
357 */
358 if l_Int_val is null
359 then
360 /* p_return_status := EC_Code_Conversion_PVT.G_XREF_NOT_FOUND;
361 fnd_message.set_name('EC','ECE_XREF_NOT_FOUND');
365 -- We change the behavior of the code conversion.
362 fnd_message.set_token('DATA', p_Ext_val1);
363 p_msg_data := fnd_message.get; */
364
366 -- If it can't find the value, then simply copy the external
367 -- value1 to internal value instead of giving out an error message.
368 p_Int_val := p_Ext_val1;
369 else
370 if EC_DEBUG.G_debug_level = 3 then
371 EC_DEBUG.PL(3, 'l_Int_val :', l_Int_val);
372 end if;
373 p_Int_val := l_Int_val;
374
375 end if;
376
377
378 -- Standard call to get message count and if count is 1, get message info.
379 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
380 p_data => p_msg_data);
381
382 if EC_DEBUG.G_debug_level >= 2 then
383 EC_DEBUG.POP('EC_Code_Conversion_PVT.Convert_from_ext_to_int');
384 end if;
385 EXCEPTION
386 WHEN FND_API.G_EXC_ERROR THEN
387 -- ROLLBACK TO Convert_from_ext_to_int_PVT;
388 p_return_status := FND_API.G_RET_STS_ERROR;
389 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
390 p_data => p_msg_data);
391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
392 -- ROLLBACK TO Convert_from_ext_to_int_PVT;
393 p_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
395 p_data => p_msg_data);
396 WHEN OTHERS THEN
397 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
398 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
399 -- ROLLBACK TO Convert_from_ext_to_int_PVT;
400 p_return_status := FND_API.G_RET_STS_ERROR;
401 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
402 FND_MSG_PUB.Add_Exc_Msg(G_FILE_NAME, G_PKG_NAME, l_api_name);
403 END IF;
404 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
405 p_data => p_msg_data);
406 END Convert_from_ext_to_int;
407
408 PROCEDURE Convert_from_int_to_ext
409 (p_api_version_number IN NUMBER,
410 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
411 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
412 p_commit IN VARCHAR2 := FND_API.G_FALSE,
413 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
414 p_return_status OUT NOCOPY VARCHAR2,
415 p_msg_count OUT NOCOPY NUMBER,
416 p_msg_data OUT NOCOPY VARCHAR2,
417 p_Category IN VARCHAR2,
418 p_Key1 IN VARCHAR2 := NULL,
419 p_Key2 IN VARCHAR2 := NULL,
420 p_Key3 IN VARCHAR2 := NULL,
421 p_Key4 IN VARCHAR2 := NULL,
422 p_Key5 IN VARCHAR2 := NULL,
423 p_Int_val IN VARCHAR2,
424 p_Ext_val1 OUT NOCOPY VARCHAR2,
425 p_Ext_val2 OUT NOCOPY VARCHAR2,
426 p_Ext_val3 OUT NOCOPY VARCHAR2,
427 p_Ext_val4 OUT NOCOPY VARCHAR2,
428 p_Ext_val5 OUT NOCOPY VARCHAR2) IS
429
430 l_api_name CONSTANT VARCHAR2(30) := 'Convert_from_int_to_ext';
431 l_api_version_number CONSTANT NUMBER := 1.0;
432
433 /* bug 5865153
434 l_Ext_val1 varchar2(500) := NULL;
435 l_Ext_val2 varchar2(500) := NULL;
436 l_Ext_val3 varchar2(500) := NULL;
437 l_Ext_val4 varchar2(500) := NULL;
438 l_Ext_val5 varchar2(500) := NULL; */
439
440 l_Ext_val1 ece_xref_data.xref_ext_value1%TYPE := NULL;
441 l_Ext_val2 ece_xref_data.xref_ext_value2%TYPE := NULL;
442 l_Ext_val3 ece_xref_data.xref_ext_value3%TYPE := NULL;
443 l_Ext_val4 ece_xref_data.xref_ext_value4%TYPE := NULL;
444 l_Ext_val5 ece_xref_data.xref_ext_value5%TYPE := NULL;
445
446
447 CURSOR match_5 IS
448 SELECT XREF_EXT_VALUE1,
449 XREF_EXT_VALUE2,
450 XREF_EXT_VALUE3,
451 XREF_EXT_VALUE4,
452 XREF_EXT_VALUE5
453 FROM ECE_XREF_DATA
454 WHERE XREF_KEY1 IS NOT NULL AND
455 XREF_KEY2 IS NOT NULL AND
456 XREF_KEY3 IS NOT NULL AND
457 XREF_KEY4 IS NOT NULL AND
458 XREF_KEY5 IS NOT NULL AND
459 XREF_KEY1 = p_Key1 AND
460 XREF_KEY2 = p_Key2 AND
461 XREF_KEY3 = p_Key3 AND
462 XREF_KEY4 = p_Key4 AND
463 XREF_KEY5 = p_Key5 AND
464 XREF_INT_VALUE = p_Int_val AND
465 XREF_CATEGORY_CODE = p_Category AND
466 (DIRECTION = 'OUT' or DIRECTION = 'BOTH');
467
468 CURSOR match_4 IS
469 SELECT XREF_EXT_VALUE1,
470 XREF_EXT_VALUE2,
471 XREF_EXT_VALUE3,
472 XREF_EXT_VALUE4,
473 XREF_EXT_VALUE5
474 FROM ECE_XREF_DATA
475 WHERE XREF_KEY1 IS NOT NULL AND
476 XREF_KEY2 IS NOT NULL AND
477 XREF_KEY3 IS NOT NULL AND
478 XREF_KEY4 IS NOT NULL AND
479 XREF_KEY5 IS NULL AND
480 XREF_KEY1 = p_Key1 AND
481 XREF_KEY2 = p_Key2 AND
482 XREF_KEY3 = p_Key3 AND
483 XREF_KEY4 = p_Key4 AND
484 XREF_INT_VALUE = p_Int_val AND
485 XREF_CATEGORY_CODE = p_Category AND
489 SELECT XREF_EXT_VALUE1,
486 (DIRECTION = 'OUT' or DIRECTION = 'BOTH');
487
488 CURSOR match_3 IS
490 XREF_EXT_VALUE2,
491 XREF_EXT_VALUE3,
492 XREF_EXT_VALUE4,
493 XREF_EXT_VALUE5
494 FROM ECE_XREF_DATA
495 WHERE XREF_KEY1 IS NOT NULL AND
496 XREF_KEY2 IS NOT NULL AND
497 XREF_KEY3 IS NOT NULL AND
498 XREF_KEY4 IS NULL AND
499 XREF_KEY5 IS NULL AND
500 XREF_KEY1 = p_Key1 AND
501 XREF_KEY2 = p_Key2 AND
502 XREF_KEY3 = p_Key3 AND
503 XREF_INT_VALUE = p_Int_val AND
504 XREF_CATEGORY_CODE = p_Category AND
505 (DIRECTION = 'OUT' or DIRECTION = 'BOTH');
506
507 CURSOR match_2 IS
508 SELECT XREF_EXT_VALUE1,
509 XREF_EXT_VALUE2,
510 XREF_EXT_VALUE3,
511 XREF_EXT_VALUE4,
512 XREF_EXT_VALUE5
513 FROM ECE_XREF_DATA
514 WHERE XREF_KEY1 IS NOT NULL AND
515 XREF_KEY2 IS NOT NULL AND
516 XREF_KEY3 IS NULL AND
517 XREF_KEY4 IS NULL AND
518 XREF_KEY5 IS NULL AND
519 XREF_KEY1 = p_Key1 AND
520 XREF_KEY2 = p_Key2 AND
521 XREF_INT_VALUE = p_Int_val AND
522 XREF_CATEGORY_CODE = p_Category AND
523 (DIRECTION = 'OUT' or DIRECTION = 'BOTH');
524
525 CURSOR match_1 IS
526 SELECT XREF_EXT_VALUE1,
527 XREF_EXT_VALUE2,
528 XREF_EXT_VALUE3,
529 XREF_EXT_VALUE4,
530 XREF_EXT_VALUE5
531 FROM ECE_XREF_DATA
532 WHERE XREF_KEY1 IS NOT NULL AND
533 XREF_KEY2 IS NULL AND
534 XREF_KEY3 IS NULL AND
535 XREF_KEY4 IS NULL AND
536 XREF_KEY5 IS NULL AND
537 XREF_KEY1 = p_Key1 AND
538 XREF_INT_VALUE = p_Int_val AND
539 XREF_CATEGORY_CODE = p_Category AND
540 (DIRECTION = 'OUT' or DIRECTION = 'BOTH');
541
542 CURSOR match_global IS
543 SELECT XREF_EXT_VALUE1,
544 XREF_EXT_VALUE2,
545 XREF_EXT_VALUE3,
546 XREF_EXT_VALUE4,
547 XREF_EXT_VALUE5
548 FROM ECE_XREF_DATA
549 WHERE XREF_KEY1 IS NULL AND
550 XREF_KEY2 IS NULL AND
551 XREF_KEY3 IS NULL AND
552 XREF_KEY4 IS NULL AND
553 XREF_KEY5 IS NULL AND
554 XREF_INT_VALUE = p_Int_val AND
555 XREF_CATEGORY_CODE = p_Category AND
556 (DIRECTION = 'OUT' or DIRECTION = 'BOTH');
557
558 BEGIN
559
560 if EC_DEBUG.G_debug_level >= 2 then
561 EC_DEBUG.PUSH('EC_Code_Conversion_PVT.Convert_from_int_to_ext');
562 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
563 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
564 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
565 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
566 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
567 EC_DEBUG.PL(3, 'p_Category: ',p_Category);
568 EC_DEBUG.PL(3, 'p_Key1: ',p_Key1);
569 EC_DEBUG.PL(3, 'p_Key2: ',p_Key2);
570 EC_DEBUG.PL(3, 'p_Key3: ',p_Key3);
571 EC_DEBUG.PL(3, 'p_Key4: ',p_Key4);
572 EC_DEBUG.PL(3, 'p_Key5: ',p_Key5);
573 EC_DEBUG.PL(3, 'p_Int_val: ',p_Int_val);
574 end if;
575 -- Standard Start of API savepoint
576 -- SAVEPOINT Convert_from_int_to_ext_PVT;
577
578 -- Standard call to check for call compatibility.
579 IF NOT FND_API.Compatible_API_Call
580 (l_api_version_number,
581 p_api_version_number,
582 l_api_name,
583 G_PKG_NAME) THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 -- Initialize message list if p_init_msg_list is set to TRUE.
588 IF FND_API.to_Boolean(p_init_msg_list) THEN
589 FND_MSG_PUB.initialize;
590 END IF;
591
592 -- Initialize API return status to success
593 p_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 -- *******************************************************
596 -- first validate the input key1-5
597 -- The input has to be one of the following
598 -- 1. key1, key2, key3, key4, key5 all not null
599 -- 2. key1, key2, key3, key4 all not null
600 -- 3. key1, key2, key3 all not null
601 -- 4. key1, key2 all not null
602 -- 5. key1 all not null
603 -- 6. All null
604 -- *******************************************************
605 IF (((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND (p_Key3 IS NOT NULL) AND
606 (p_Key4 IS NOT NULL) AND (p_Key5 IS NOT NULL)) OR ((p_Key1 IS NOT NULL) AND
607 (p_Key2 IS NOT NULL) AND (p_Key3 IS NOT NULL) AND (p_Key4 IS NOT NULL) AND
608 (p_Key5 IS NULL)) OR ((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND
609 (p_Key3 IS NOT NULL) AND (p_Key4 IS NULL) AND (p_Key5 IS NULL)) OR
610 ((p_Key1 IS NOT NULL) AND (p_Key2 IS NOT NULL) AND (p_Key3 IS NULL) AND
611 (p_Key4 IS NULL) AND (p_Key5 IS NULL)) OR ((p_Key1 IS NOT NULL) AND
615 -- *******************************************************
612 (p_Key2 IS NULL) AND (p_Key3 IS NULL) AND (p_Key4 IS NULL) AND
613 (p_Key5 IS NULL)))
614 THEN
616 -- Start with matching all five keys
617 -- if no 5-key matches -> search to match 4 keys
618 -- if no 4-key matches -> search to match 3 keys
619 -- if no 3-key matches -> search to match 2 keys
620 -- if no 2-key matches -> search to match 1 keys
621 -- if no 1-key matches -> search for generic match
622 -- *******************************************************
623
624 -- *******************************************************
625 -- All 5 keys are supplied
626 -- *******************************************************
627 IF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL) OR (p_Key3 IS NULL) OR
628 (p_Key4 IS NULL) OR (p_Key5 IS NULL))) THEN
629 OPEN match_5;
630 FETCH match_5 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
631 l_Ext_val4,l_Ext_val5;
632 IF match_5%NOTFOUND THEN
633 OPEN match_4;
634 FETCH match_4 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
635 l_Ext_val4,l_Ext_val5;
636 IF match_4%NOTFOUND THEN
637 OPEN match_3;
638 FETCH match_3 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
639 l_Ext_val4,l_Ext_val5;
640 IF match_3%NOTFOUND THEN
641 OPEN match_2;
642 FETCH match_2 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
643 l_Ext_val4,l_Ext_val5;
644 IF match_2%NOTFOUND THEN
645 OPEN match_1;
646 FETCH match_1 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
647 l_Ext_val4,l_Ext_val5;
648 IF match_1%NOTFOUND THEN
649 OPEN match_global;
650 FETCH match_global INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
651 l_Ext_val4,l_Ext_val5;
652 CLOSE match_global;
653 END IF;
654 CLOSE match_1;
655 END IF;
656 CLOSE match_2;
657 END IF;
658 CLOSE match_3;
659 END IF;
660 CLOSE match_4;
661 END IF;
662 CLOSE match_5;
663
664 -- *******************************************************
665 -- Four (4) keys are supplied
666 -- *******************************************************
667 ELSIF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL) OR
668 (p_Key3 IS NULL) OR (p_Key4 IS NULL))) THEN
669 OPEN match_4;
670 FETCH match_4 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
671 l_Ext_val4,l_Ext_val5;
672 IF match_4%NOTFOUND THEN
673 OPEN match_3;
674 FETCH match_3 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
675 l_Ext_val4,l_Ext_val5;
676 IF match_3%NOTFOUND THEN
677 OPEN match_2;
678 FETCH match_2 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
679 l_Ext_val4,l_Ext_val5;
680 IF match_2%NOTFOUND THEN
681 OPEN match_1;
682 FETCH match_1 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
683 l_Ext_val4,l_Ext_val5;
684 IF match_1%NOTFOUND THEN
685 OPEN match_global;
686 FETCH match_global INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
687 l_Ext_val4,l_Ext_val5;
688 CLOSE match_global;
689 END IF;
690 CLOSE match_1;
691 END IF;
692 CLOSE match_2;
693 END IF;
694 CLOSE match_3;
695 END IF;
696 CLOSE match_4;
697
698 -- *******************************************************
699 -- Three (3) keys are supplied
700 -- *******************************************************
701 ELSIF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL) OR (p_Key3 IS NULL))) THEN
702 OPEN match_3;
703 FETCH match_3 INTO l_Ext_val1,l_Ext_val2,
704 l_Ext_val3,l_Ext_val4,l_Ext_val5;
705 IF match_3%NOTFOUND THEN
706 OPEN match_2;
707 FETCH match_2 INTO l_Ext_val1,l_Ext_val2,
708 l_Ext_val3,l_Ext_val4,l_Ext_val5;
709 IF match_2%NOTFOUND THEN
710 OPEN match_1;
711 FETCH match_1 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
712 l_Ext_val4,l_Ext_val5;
713 IF match_1%NOTFOUND THEN
714 OPEN match_global;
715 FETCH match_global INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
716 l_Ext_val4,l_Ext_val5;
717 CLOSE match_global;
718 END IF;
719 CLOSE match_1;
720 END IF;
721 CLOSE match_2;
722 END IF;
723 CLOSE match_3;
724
725 -- *******************************************************
726 -- Two (2) keys are supplied
730 FETCH match_2 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
727 -- *******************************************************
728 ELSIF (NOT((p_Key1 IS NULL) OR (p_Key2 IS NULL))) THEN
729 OPEN match_2;
731 l_Ext_val4,l_Ext_val5;
732 IF match_2%NOTFOUND THEN
733 OPEN match_1;
734 FETCH match_1 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
735 l_Ext_val4,l_Ext_val5;
736 IF match_1%NOTFOUND THEN
737 OPEN match_global;
738 FETCH match_global INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
739 l_Ext_val4,l_Ext_val5;
740 CLOSE match_global;
741 END IF;
742 CLOSE match_1;
743 END IF;
744 CLOSE match_2;
745
746 -- *******************************************************
747 -- One (1) key is supplied
748 -- *******************************************************
749 ELSIF (p_Key1 IS NOT NULL) THEN
750 OPEN match_1;
751 FETCH match_1 INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
752 l_Ext_val4,l_Ext_val5;
753 IF match_1%NOTFOUND THEN
754 OPEN match_global;
755 FETCH match_global INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
756 l_Ext_val4,l_Ext_val5;
757 CLOSE match_global;
758 END IF;
759 CLOSE match_1;
760 END IF;
761
762 ELSE
763
764 -- *******************************
765 -- all keys (1-5) are NULLs
766 -- *******************************
767 OPEN match_global;
768 FETCH match_global INTO l_Ext_val1,l_Ext_val2,l_Ext_val3,
769 l_Ext_val4,l_Ext_val5;
770 CLOSE match_global;
771 END IF;
772
773 if l_Ext_val1 is null
774 and l_Ext_val2 is null
775 and l_Ext_val3 is null
776 and l_Ext_val4 is null
777 and l_Ext_val5 is null
778 then
779 /* p_return_status := EC_Code_Conversion_PVT.G_XREF_NOT_FOUND;
780 fnd_message.set_name('EC','ECE_XREF_NOT_FOUND');
781 fnd_message.set_token('DATA', p_Int_val);
782 p_msg_data := fnd_message.get; */
783
784 -- We change the behavior of the code conversion.
785 -- If it can't find the value, then simply copy the internal
786 -- value to external value1 instead of giving out an error message.
787 p_Ext_val1 := p_Int_val;
788 else
789 p_Ext_val1 := l_Ext_val1;
790 p_Ext_val2 := l_Ext_val2;
791 p_Ext_val3 := l_Ext_val3;
792 p_Ext_val4 := l_Ext_val4;
793 p_Ext_val5 := l_Ext_val5;
794 if EC_DEBUG.G_debug_level = 3 then
795 EC_DEBUG.PL(3, 'l_Ext_val1: ',l_Ext_val1);
796 EC_DEBUG.PL(3, 'l_Ext_val2: ',l_Ext_val2);
797 EC_DEBUG.PL(3, 'l_Ext_val3: ',l_Ext_val3);
798 EC_DEBUG.PL(3, 'l_Ext_val4: ',l_Ext_val4);
799 EC_DEBUG.PL(3, 'l_Ext_val5: ',l_Ext_val5);
800 end if;
801 end if;
802
803 -- *******************************************************
804 -- Standard check of p_simulate and p_commit parameters
805 -- *******************************************************
806 /*
807 IF FND_API.To_Boolean(p_simulate) THEN
808 ROLLBACK TO Convert_from_int_to_ext_PVT;
809 ELSIF FND_API.To_Boolean(p_commit) THEN
810 COMMIT WORK;
811 END IF;
812 */
813 -- Standard call to get message count and if count is 1, get message info.
814 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,p_data => p_msg_data);
815
816 if EC_DEBUG.G_debug_level >= 2 then
817 EC_DEBUG.POP('EC_Code_Conversion_PVT.Convert_from_int_to_ext');
818 end if;
819 EXCEPTION
820 WHEN FND_API.G_EXC_ERROR THEN
821 -- ROLLBACK TO Convert_from_int_to_ext_PVT;
822 p_return_status := FND_API.G_RET_STS_ERROR;
823 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
824 p_data => p_msg_data);
825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
826 -- ROLLBACK TO Convert_from_int_to_ext_PVT;
827 p_return_status := FND_API.G_RET_STS_ERROR;
828 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
829 p_data => p_msg_data);
830 WHEN OTHERS THEN
831 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
832 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
833 -- ROLLBACK TO Convert_from_int_to_ext_PVT;
834 p_return_status := FND_API.G_RET_STS_ERROR;
835 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
836 FND_MSG_PUB.Add_Exc_Msg(G_FILE_NAME, G_PKG_NAME, l_api_name);
837 END IF;
838 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
839 p_data => p_msg_data);
840
841 END Convert_from_int_to_ext;
842
843 PROCEDURE populate_plsql_tbl_with_extval
844 (p_api_version_number IN NUMBER,
845 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
846 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
847 p_commit IN VARCHAR2 := FND_API.G_FALSE,
848 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
852 p_key_tbl IN ece_flatfile_pvt.Interface_tbl_type,
849 p_return_status OUT NOCOPY VARCHAR2,
850 p_msg_count OUT NOCOPY NUMBER,
851 p_msg_data OUT NOCOPY VARCHAR2,
853 p_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type) IS
854
855 l_api_name CONSTANT VARCHAR2(30) := 'populate_plsql_tbl_with_extval';
856 l_api_version_number CONSTANT NUMBER := 1.0;
857
858 ckey1_used_flag VARCHAR2(1);
859 ckey2_used_flag VARCHAR2(1);
860 ckey3_used_flag VARCHAR2(1);
861 ckey4_used_flag VARCHAR2(1);
862 ckey5_used_flag VARCHAR2(1);
863
864 ckey1_used_table VARCHAR2(80);
865 ckey2_used_table VARCHAR2(80);
866 ckey3_used_table VARCHAR2(80);
867 ckey4_used_table VARCHAR2(80);
868 ckey5_used_table VARCHAR2(80);
869
870 ckey1_used_column VARCHAR2(80);
871 ckey2_used_column VARCHAR2(80);
872 ckey3_used_column VARCHAR2(80);
873 ckey4_used_column VARCHAR2(80);
874 ckey5_used_column VARCHAR2(80);
875
876 cxref_category_code VARCHAR2(30);
877
878 key1 VARCHAR2(500); -- 4011384
879 key2 VARCHAR2(500);
880 key3 VARCHAR2(500);
881 key4 VARCHAR2(500);
882 key5 VARCHAR2(500);
883
884 ext1 VARCHAR2(500);
885 ext2 VARCHAR2(500);
886 ext3 VARCHAR2(500);
887 ext4 VARCHAR2(500);
888 ext5 VARCHAR2(500);
889
890 return_code NUMBER;
891 icount NUMBER;
892 l_return_status VARCHAR2(2000);
893 l_msg_count NUMBER;
894 l_msg_data VARCHAR2(2000);
895
896 i INTEGER;
897 j INTEGER;
898 l_ext_pos NUMBER;
899 b_xref_data_found BOOLEAN;
900
901 BEGIN
902
903 if EC_DEBUG.G_debug_level >= 2 then
904 EC_DEBUG.PUSH('EC_Code_Conversion_PVT.populate_plsql_tbl_with_extval');
905 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
906 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
907 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
908 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
909 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
910 end if;
911 -- Standard Start of API savepoint
912 SAVEPOINT populate_plsql_tbl_PVT;
913
914 -- Standard call to check for call compatibility.
915 IF NOT FND_API.Compatible_API_Call
916 (l_api_version_number,
917 p_api_version_number,
918 l_api_name,
919 G_PKG_NAME) THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922
923 -- Initialize message list if p_init_msg_list is set to TRUE.
924 IF FND_API.to_Boolean(p_init_msg_list) THEN
925 FND_MSG_PUB.initialize;
926 END IF;
927
928 -- Initialize API return status to success
929 p_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 -- *******************************************************
932 -- work on each row in the p_tbl pl/sql
933 -- table to fill in the ext1-5 value
934 -- *******************************************************
935 icount := p_tbl.COUNT;
936
937 FOR i IN 1..icount LOOP
938 IF (p_tbl(i).xref_category_id IS NOT NULL) THEN
939 -- use this xref_id, go to ece_xref_category to find out all the
940 -- enabled keys
941 SELECT key1_used_flag, key2_used_flag, key3_used_flag,
942 key4_used_flag, key5_used_flag,
943 key1_used_table, key2_used_table, key3_used_table,
944 key4_used_table, key5_used_table,
945 key1_used_column, key2_used_column, key3_used_column,
946 key4_used_column, key5_used_column,
947 xref_category_code
948 INTO ckey1_used_flag, ckey2_used_flag, ckey3_used_flag,
949 ckey4_used_flag, ckey5_used_flag,
950 ckey1_used_table, ckey2_used_table, ckey3_used_table,
951 ckey4_used_table, ckey5_used_table,
952 ckey1_used_column, ckey2_used_column, ckey3_used_column,
953 ckey4_used_column, ckey5_used_column,
954 cxref_category_code
955 FROM ece_xref_categories
956 WHERE ece_xref_categories.xref_category_id = p_tbl(i).xref_category_id;
957
958 IF ckey1_used_flag = 'Y' AND
959 p_tbl(i).xref_key1_source_column IS NOT NULL THEN
960 FOR j IN 1..p_key_tbl.count LOOP
961 IF p_tbl(i).xref_key1_source_column =
962 p_key_tbl(j).interface_column_name THEN
963 key1 := p_key_tbl(j).value;
964 if EC_DEBUG.G_debug_level >= 3 then
965 EC_DEBUG.PL(3, 'key1 :', key1);
966 end if;
967 EXIT;
968 END IF;
969 END LOOP;
970 -- we assume all the key can be found in the pl/sql table
971 END IF;
972
973 IF ckey2_used_flag = 'Y' AND
974 p_tbl(i).xref_key2_source_column IS NOT NULL THEN
975 FOR j IN 1..p_key_tbl.count LOOP
976 IF p_tbl(i).xref_key2_source_column =
980 EC_DEBUG.PL(3, 'key2 :', key2);
977 p_key_tbl(j).interface_column_name THEN
978 key2 := p_key_tbl(j).value;
979 if EC_DEBUG.G_debug_level >= 3 then
981 end if;
982 EXIT;
983 END IF;
984 END LOOP;
985 END IF;
986
987 IF ckey3_used_flag = 'Y' AND
988 p_tbl(i).xref_key3_source_column IS NOT NULL THEN
989 FOR j in 1..p_key_tbl.count LOOP
990 IF p_tbl(i).xref_key3_source_column =
991 p_key_tbl(j).interface_column_name THEN
992 key3 := p_key_tbl(j).value;
993 if EC_DEBUG.G_debug_level >= 3 then
994 EC_DEBUG.PL(3, 'key3 :', key3);
995 end if;
996 EXIT;
997 END IF;
998 END LOOP;
999 END IF;
1000
1001 IF ckey4_used_flag = 'Y' AND
1002 p_tbl(i).xref_key4_source_column IS NOT NULL THEN
1003 FOR j IN 1..p_key_tbl.count LOOP
1004 IF p_tbl(i).xref_key4_source_column =
1005 p_key_tbl(j).interface_column_name THEN
1006 key4 := p_key_tbl(j).value;
1007 if EC_DEBUG.G_debug_level >= 3 then
1008 EC_DEBUG.PL(3, 'key4 :', key4);
1009 end if;
1010 EXIT;
1011 END IF;
1012 END LOOP;
1013 END IF;
1014
1015 IF ckey5_used_flag = 'Y' AND
1016 p_tbl(i).xref_key5_source_column IS NOT NULL THEN
1017 FOR j IN 1..p_key_tbl.count LOOP
1018 IF p_tbl(i).xref_key5_source_column =
1019 p_key_tbl(j).interface_column_name THEN
1020 key5 := p_key_tbl(j).value;
1021 if EC_DEBUG.G_debug_level >= 3 then
1022 EC_DEBUG.PL(3, 'key5 :', key5);
1023 end if;
1024 EXIT;
1025 END IF;
1026 END LOOP;
1027 END IF;
1028
1029 -- Now we know the int_value, the actual value of the key1-5,
1030 -- so we just need to call int_2_ext APIs to get the
1031 -- the ext1-5 value
1032 EC_Code_Conversion_PVT.Convert_from_int_to_ext
1033 (p_api_version_number => 1.0,
1034 p_return_status => l_return_status,
1035 p_msg_count => l_msg_count,
1036 p_msg_data => l_msg_data,
1037 p_Category => cxref_category_code,
1038 p_Key1 => key1,
1039 p_Key2 => key2,
1040 p_Key3 => key3,
1041 p_Key4 => key4,
1042 p_Key5 => key5,
1043 p_Int_val => p_tbl(i).value,
1044 p_Ext_val1 => ext1,
1045 p_Ext_val2 => ext2,
1046 p_Ext_val3 => ext3,
1047 p_Ext_val4 => ext4,
1048 p_Ext_val5 => ext5);
1049
1050 p_tbl(i).ext_val1 := ext1;
1051 p_tbl(i).ext_val2 := ext2;
1052 p_tbl(i).ext_val3 := ext3;
1053 p_tbl(i).ext_val4 := ext4;
1054 p_tbl(i).ext_val5 := ext5;
1055
1056 key1 := NULL;
1057 key2 := NULL;
1058 key3 := NULL;
1059 key4 := NULL;
1060 key5 := NULL;
1061
1062 -- ******************************
1063 -- Need to populate the value column
1064 -- of all the corresponding external
1065 -- values, e.g. UOM_CODE_EXT1
1066 -- ******************************
1067
1068 IF ext1 is NOT NULL
1069 THEN
1070 if EC_DEBUG.G_debug_level = 3 then
1071 EC_DEBUG.PL(3, 'ext1 :', ext1);
1072 end if;
1073 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1074 (p_gateway_tbl => p_tbl,
1075 p_conversion_group => p_tbl(i).conversion_group_id,
1076 p_sequence_num => 1,
1077 p_Pos => l_ext_pos);
1078
1079 IF b_xref_data_found THEN
1080 p_tbl(l_ext_pos).value := ext1;
1081 END IF;
1082 END IF;
1083
1084 IF ext2 is NOT NULL
1085 THEN
1086 if EC_DEBUG.G_debug_level = 3 then
1087 EC_DEBUG.PL(3, 'ext2 :', ext2);
1088 end if;
1089 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1090 (p_gateway_tbl => p_tbl,
1091 p_conversion_group => p_tbl(i).conversion_group_id,
1092 p_sequence_num => 2,
1093 p_Pos => l_ext_pos);
1094
1095 IF b_xref_data_found THEN
1096 p_tbl(l_ext_pos).value := ext2;
1097 END IF;
1098 END IF;
1099
1100 IF ext3 is NOT NULL
1101 THEN
1102 if EC_DEBUG.G_debug_level = 3 then
1103 EC_DEBUG.PL(3, 'ext3 :', ext3);
1104 end if;
1105 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1106 (p_gateway_tbl => p_tbl,
1107 p_conversion_group => p_tbl(i).conversion_group_id,
1108 p_sequence_num => 3,
1109 p_Pos => l_ext_pos);
1110
1114 END IF;
1111 IF b_xref_data_found THEN
1112 p_tbl(l_ext_pos).value := ext3;
1113 END IF;
1115
1116 IF ext4 is NOT NULL
1117 THEN
1118 if EC_DEBUG.G_debug_level = 3 then
1119 EC_DEBUG.PL(3, 'ext4 :', ext4);
1120 end if;
1121 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1122 (p_gateway_tbl => p_tbl,
1123 p_conversion_group => p_tbl(i).conversion_group_id,
1124 p_sequence_num => 4,
1125 p_Pos => l_ext_pos);
1126
1127 IF b_xref_data_found THEN
1128 p_tbl(l_ext_pos).value := ext4;
1129 END IF;
1130 END IF;
1131
1132 IF ext5 is NOT NULL
1133 THEN
1134 if EC_DEBUG.G_debug_level = 3 then
1135 EC_DEBUG.PL(3, 'ext5 :', ext5);
1136 end if;
1137 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1138 (p_gateway_tbl => p_tbl,
1139 p_conversion_group => p_tbl(i).conversion_group_id,
1140 p_sequence_num => 5,
1141 p_Pos => l_ext_pos);
1142
1143 IF b_xref_data_found THEN
1144 p_tbl(l_ext_pos).value := ext5;
1145 END IF;
1146 END IF;
1147
1148 -- This is to copy the internal value to external value if
1149 -- there is no category code assigned to the interface column and
1150 -- the external value1 is null.
1151
1152 ELSIF (p_tbl(i).conversion_seq = 0) THEN
1153 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1154 (p_gateway_tbl => p_tbl,
1155 p_conversion_group => p_tbl(i).conversion_group_id,
1156 p_sequence_num => 1,
1157 p_Pos => l_ext_pos);
1158
1159 IF b_xref_data_found THEN
1160 p_tbl(l_ext_pos).value := p_tbl(i).value;
1161 p_tbl(i).ext_val1 := p_tbl(i).value;
1162 END IF;
1163
1164 END IF;
1165 END LOOP;
1166
1167 -- *******************************************************
1168 -- Standard check of p_simulate and p_commit parameters
1169 -- *******************************************************
1170 IF FND_API.To_Boolean(p_simulate) THEN
1171 ROLLBACK TO populate_plsql_tbl_PVT;
1172 ELSIF FND_API.To_Boolean(p_commit) THEN
1173 COMMIT WORK;
1174 END IF;
1175
1176 -- Standard call to get message count and if count is 1, get message info.
1177 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1178 p_data => p_msg_data);
1179
1180 if l_return_status = EC_Code_Conversion_PVT.G_XREF_NOT_FOUND
1181 then
1182 p_return_status := EC_Code_Conversion_PVT.G_XREF_NOT_FOUND;
1183 end if;
1184
1185 if EC_DEBUG.G_debug_level >= 2 then
1186 EC_DEBUG.POP('EC_Code_Conversion_PVT.populate_plsql_tbl_with_extval');
1187 end if;
1188 EXCEPTION
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 ROLLBACK TO populate_plsql_tbl_PVT;
1191 p_return_status := FND_API.G_RET_STS_ERROR;
1192 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1193 p_data => p_msg_data);
1194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1195 ROLLBACK TO populate_plsql_tbl_PVT;
1196 p_return_status := FND_API.G_RET_STS_ERROR;
1197 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1198 p_data => p_msg_data);
1199 WHEN OTHERS THEN
1200 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1201 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1202 ROLLBACK TO populate_plsql_tbl_PVT;
1203 p_return_status := FND_API.G_RET_STS_ERROR;
1204 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1205 FND_MSG_PUB.Add_Exc_Msg(G_FILE_NAME, G_PKG_NAME, l_api_name);
1206 END IF;
1207 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1208 p_data => p_msg_data);
1209
1210 END populate_plsql_tbl_with_extval;
1211
1212 PROCEDURE populate_plsql_tbl_with_extval
1213 (p_api_version_number IN NUMBER,
1214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1215 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
1216 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1218 p_return_status OUT NOCOPY VARCHAR2,
1219 p_msg_count OUT NOCOPY NUMBER,
1220 p_msg_data OUT NOCOPY VARCHAR2,
1221 p_tbl IN OUT NOCOPY ec_utils.mapping_tbl,
1222 p_level IN NUMBER) IS
1223
1224 l_api_name CONSTANT VARCHAR2(30) := 'populate_plsql_tbl_with_extval';
1225 l_api_version_number CONSTANT NUMBER := 1.0;
1226
1227 ckey1_used_flag VARCHAR2(1);
1228 ckey2_used_flag VARCHAR2(1);
1229 ckey3_used_flag VARCHAR2(1);
1230 ckey4_used_flag VARCHAR2(1);
1231 ckey5_used_flag VARCHAR2(1);
1232
1233 ckey1_used_table VARCHAR2(80);
1234 ckey2_used_table VARCHAR2(80);
1235 ckey3_used_table VARCHAR2(80);
1236 ckey4_used_table VARCHAR2(80);
1237 ckey5_used_table VARCHAR2(80);
1241 ckey3_used_column VARCHAR2(80);
1238
1239 ckey1_used_column VARCHAR2(80);
1240 ckey2_used_column VARCHAR2(80);
1242 ckey4_used_column VARCHAR2(80);
1243 ckey5_used_column VARCHAR2(80);
1244
1245 cxref_category_code VARCHAR2(30);
1246
1247 key1 VARCHAR2(500); --4011384
1248 key2 VARCHAR2(500);
1249 key3 VARCHAR2(500);
1250 key4 VARCHAR2(500);
1251 key5 VARCHAR2(500);
1252
1253 ext1 VARCHAR2(500);
1254 ext2 VARCHAR2(500);
1255 ext3 VARCHAR2(500);
1256 ext4 VARCHAR2(500);
1257 ext5 VARCHAR2(500);
1258
1259 return_code pls_integer;
1260 icount pls_integer;
1261 l_return_status VARCHAR2(2000);
1262 l_msg_count pls_integer;
1263 l_msg_data VARCHAR2(2000);
1264
1265 i pls_integer;
1266 j pls_integer;
1267 l_ext_pos pls_integer;
1268 b_xref_data_found BOOLEAN;
1269
1270 BEGIN
1271
1272 if EC_DEBUG.G_debug_level >= 2 then
1273 EC_DEBUG.PUSH('EC_Code_Conversion_PVT.populate_plsql_tbl_with_extval');
1274 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
1275 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
1276 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
1277 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
1278 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
1279 end if;
1280 -- Standard Start of API savepoint
1281 SAVEPOINT populate_plsql_tbl_PVT;
1282
1283 -- Standard call to check for call compatibility.
1284 IF NOT FND_API.Compatible_API_Call
1285 (l_api_version_number,
1286 p_api_version_number,
1287 l_api_name,
1288 G_PKG_NAME) THEN
1289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1290 END IF;
1291
1292 -- Initialize message list if p_init_msg_list is set to TRUE.
1293 IF FND_API.to_Boolean(p_init_msg_list) THEN
1294 FND_MSG_PUB.initialize;
1295 END IF;
1296
1297 -- Initialize API return status to success
1298 p_return_status := FND_API.G_RET_STS_SUCCESS;
1299
1300 -- *******************************************************
1301 -- work on each row in the p_tbl pl/sql
1302 -- table to fill in the ext1-5 value
1303 -- *******************************************************
1304 icount := p_tbl.COUNT;
1305
1306 FOR i IN ec_utils.g_int_levels(p_level).file_start_pos..ec_utils.g_int_levels(p_level).file_end_pos
1307 LOOP
1308 IF (p_tbl(i).xref_category_id IS NOT NULL) THEN
1309 -- use this xref_id, go to ece_xref_category to find out all the
1310 -- enabled keys
1311 SELECT key1_used_flag, key2_used_flag, key3_used_flag,
1312 key4_used_flag, key5_used_flag,
1313 key1_used_table, key2_used_table, key3_used_table,
1314 key4_used_table, key5_used_table,
1315 key1_used_column, key2_used_column, key3_used_column,
1316 key4_used_column, key5_used_column,
1317 xref_category_code
1318 INTO ckey1_used_flag, ckey2_used_flag, ckey3_used_flag,
1319 ckey4_used_flag, ckey5_used_flag,
1320 ckey1_used_table, ckey2_used_table, ckey3_used_table,
1321 ckey4_used_table, ckey5_used_table,
1322 ckey1_used_column, ckey2_used_column, ckey3_used_column,
1323 ckey4_used_column, ckey5_used_column,
1324 cxref_category_code
1325 FROM ece_xref_categories
1326 WHERE ece_xref_categories.xref_category_id = p_tbl(i).xref_category_id;
1327
1328 -- Bug 2828072
1329 IF ckey1_used_flag = 'Y' AND
1330 p_tbl(i).xref_key1_source_column IS NOT NULL THEN
1331 FOR j IN REVERSE 1..p_level LOOP
1332 FOR k IN ec_utils.g_int_levels(j).file_start_pos..ec_utils.g_int_levels(j).file_end_pos
1333 LOOP
1334 IF p_tbl(i).xref_key1_source_column =
1335 p_tbl(k).interface_column_name THEN
1336 key1 := p_tbl(k).value;
1337 EXIT;
1338 END IF;
1339 END LOOP;
1340 IF key1 IS NOT NULL THEN
1341 EXIT;
1342 END IF;
1343 END LOOP;
1344 -- we assume all the key can be found in the pl/sql table
1345 END IF;
1346
1347 IF ckey2_used_flag = 'Y' AND
1348 p_tbl(i).xref_key2_source_column IS NOT NULL THEN
1349 FOR j IN REVERSE 1..p_level LOOP
1350 FOR k IN ec_utils.g_int_levels(j).file_start_pos..ec_utils.g_int_levels(j).file_end_pos
1351 LOOP
1352 IF p_tbl(i).xref_key2_source_column =
1353 p_tbl(k).interface_column_name THEN
1354 key2 := p_tbl(k).value;
1355 EXIT;
1356 END IF;
1357 END LOOP;
1358 IF key2 IS NOT NULL THEN
1359 EXIT;
1360 END IF;
1361 END LOOP;
1362 END IF;
1363
1364 IF ckey3_used_flag = 'Y' AND
1365 p_tbl(i).xref_key3_source_column IS NOT NULL THEN
1366 FOR j IN REVERSE 1..p_level LOOP
1370 p_tbl(k).interface_column_name THEN
1367 FOR k IN ec_utils.g_int_levels(j).file_start_pos..ec_utils.g_int_levels(j).file_end_pos
1368 LOOP
1369 IF p_tbl(i).xref_key3_source_column =
1371 key3 := p_tbl(k).value;
1372 EXIT;
1373 END IF;
1374 END LOOP;
1375 IF key3 IS NOT NULL THEN
1376 EXIT;
1377 END IF;
1378 END LOOP;
1379 END IF;
1380
1381 IF ckey4_used_flag = 'Y' AND
1382 p_tbl(i).xref_key4_source_column IS NOT NULL THEN
1383 FOR j IN REVERSE 1..p_level LOOP
1384 FOR k IN ec_utils.g_int_levels(j).file_start_pos..ec_utils.g_int_levels(j).file_end_pos
1385 LOOP
1386 IF p_tbl(i).xref_key4_source_column =
1387 p_tbl(k).interface_column_name THEN
1388 key4 := p_tbl(k).value;
1389 EXIT;
1390 END IF;
1391 END LOOP;
1392 IF key4 IS NOT NULL THEN
1393 EXIT;
1394 END IF;
1395 END LOOP;
1396 END IF;
1397
1398 IF ckey5_used_flag = 'Y' AND
1399 p_tbl(i).xref_key5_source_column IS NOT NULL THEN
1400 FOR j IN REVERSE 1..p_level LOOP
1401 FOR k IN ec_utils.g_int_levels(j).file_start_pos..ec_utils.g_int_levels(j).file_end_pos
1402 LOOP
1403 IF p_tbl(i).xref_key5_source_column =
1404 p_tbl(k).interface_column_name THEN
1405 key5 := p_tbl(k).value;
1406 EXIT;
1407 END IF;
1408 END LOOP;
1409 IF key5 IS NOT NULL THEN
1410 EXIT;
1411 END IF;
1412 END LOOP;
1413 END IF;
1414
1415 IF EC_DEBUG.G_debug_level = 3 then
1416 EC_DEBUG.PL(3, 'key1 :', key1);
1417 EC_DEBUG.PL(3, 'key2 :', key2);
1418 EC_DEBUG.PL(3, 'key3 :', key3);
1419 EC_DEBUG.PL(3, 'key4 :', key4);
1420 EC_DEBUG.PL(3, 'key5 :', key5);
1421 END IF;
1422 -- Now we know the int_value, the actual value of the key1-5,
1423 -- so we just need to call int_2_ext APIs to get the
1424 -- the ext1-5 value
1425 EC_Code_Conversion_PVT.Convert_from_int_to_ext
1426 (p_api_version_number => 1.0,
1427 p_return_status => l_return_status,
1428 p_msg_count => l_msg_count,
1429 p_msg_data => l_msg_data,
1430 p_Category => cxref_category_code,
1431 p_Key1 => key1,
1432 p_Key2 => key2,
1433 p_Key3 => key3,
1434 p_Key4 => key4,
1435 p_Key5 => key5,
1436 p_Int_val => p_tbl(i).value,
1437 p_Ext_val1 => ext1,
1438 p_Ext_val2 => ext2,
1439 p_Ext_val3 => ext3,
1440 p_Ext_val4 => ext4,
1441 p_Ext_val5 => ext5);
1442
1443 p_tbl(i).ext_val1 := ext1;
1444 p_tbl(i).ext_val2 := ext2;
1445 p_tbl(i).ext_val3 := ext3;
1446 p_tbl(i).ext_val4 := ext4;
1447 p_tbl(i).ext_val5 := ext5;
1448
1449 key1 := NULL;
1450 key2 := NULL;
1451 key3 := NULL;
1452 key4 := NULL;
1453 key5 := NULL;
1454
1455 -- ******************************
1456 -- Need to populate the value column
1457 -- of all the corresponding external
1458 -- values, e.g. UOM_CODE_EXT1
1459 -- ******************************
1460
1461 IF ext1 is NOT NULL
1462 THEN
1463 if EC_DEBUG.G_debug_level = 3 then
1464 EC_DEBUG.PL(3, 'ext1 :', ext1);
1465 end if;
1466 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1467 (p_level => p_level,
1468 p_conversion_group => p_tbl(i).conversion_group_id,
1469 p_sequence_num => 1,
1470 p_Pos => l_ext_pos);
1471
1472 IF b_xref_data_found THEN
1473 p_tbl(l_ext_pos).value := ext1;
1474 END IF;
1475 END IF;
1476
1477 IF ext2 is NOT NULL
1478 THEN
1479 if EC_DEBUG.G_debug_level = 3 then
1480 EC_DEBUG.PL(3, 'ext2 :', ext2);
1481 end if;
1482 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1483 (p_level => p_level,
1484 p_conversion_group => p_tbl(i).conversion_group_id,
1485 p_sequence_num => 2,
1486 p_Pos => l_ext_pos);
1487
1488 IF b_xref_data_found THEN
1489 p_tbl(l_ext_pos).value := ext2;
1490 END IF;
1491 END IF;
1492
1493 IF ext3 is NOT NULL
1494 THEN
1495 if EC_DEBUG.G_debug_level = 3 then
1496 EC_DEBUG.PL(3, 'ext3 :', ext3);
1497 end if;
1498 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1499 (p_level => p_level,
1503
1500 p_conversion_group => p_tbl(i).conversion_group_id,
1501 p_sequence_num => 3,
1502 p_Pos => l_ext_pos);
1504 IF b_xref_data_found THEN
1505 p_tbl(l_ext_pos).value := ext3;
1506 END IF;
1507 END IF;
1508
1509 IF ext4 is NOT NULL
1510 THEN
1511 if EC_DEBUG.G_debug_level = 3 then
1512 EC_DEBUG.PL(3, 'ext4 :', ext4);
1513 end if;
1514 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1515 (p_level => p_level,
1516 p_conversion_group => p_tbl(i).conversion_group_id,
1517 p_sequence_num => 4,
1518 p_Pos => l_ext_pos);
1519
1520 IF b_xref_data_found THEN
1521 p_tbl(l_ext_pos).value := ext4;
1522 END IF;
1523 END IF;
1524
1525 IF ext5 is NOT NULL
1526 THEN
1527 if EC_DEBUG.G_debug_level = 3 then
1528 EC_DEBUG.PL(3, 'ext5 :', ext5);
1529 end if;
1530 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1531 (p_level => p_level,
1532 p_conversion_group => p_tbl(i).conversion_group_id,
1533 p_sequence_num => 5,
1534 p_Pos => l_ext_pos);
1535
1536 IF b_xref_data_found THEN
1537 p_tbl(l_ext_pos).value := ext5;
1538 END IF;
1539 END IF;
1540
1541 -- This is to copy the internal value to external value if
1542 -- there is no category code assigned to the interface column and
1543 -- the external value1 is null.
1544
1545 ELSIF (p_tbl(i).conversion_sequence = 0) THEN
1546 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1547 (p_level => p_level,
1548 p_conversion_group => p_tbl(i).conversion_group_id,
1549 p_sequence_num => 1,
1550 p_Pos => l_ext_pos);
1551
1552 IF b_xref_data_found THEN
1553 p_tbl(l_ext_pos).value := p_tbl(i).value;
1554 p_tbl(i).ext_val1 := p_tbl(i).value;
1555 END IF;
1556
1557 END IF;
1558 END LOOP;
1559
1560 -- *******************************************************
1561 -- Standard check of p_simulate and p_commit parameters
1562 -- *******************************************************
1563 IF FND_API.To_Boolean(p_simulate) THEN
1564 ROLLBACK TO populate_plsql_tbl_PVT;
1565 ELSIF FND_API.To_Boolean(p_commit) THEN
1566 COMMIT WORK;
1567 END IF;
1568
1569 -- Standard call to get message count and if count is 1, get message info.
1570 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1571 p_data => p_msg_data);
1572
1573 if l_return_status = EC_Code_Conversion_PVT.G_XREF_NOT_FOUND
1574 then
1575 p_return_status := EC_Code_Conversion_PVT.G_XREF_NOT_FOUND;
1576 end if;
1577
1578 if EC_DEBUG.G_debug_level >= 2 then
1579 EC_DEBUG.POP('EC_Code_Conversion_PVT.populate_plsql_tbl_with_extval');
1580 end if;
1581 EXCEPTION
1582 WHEN FND_API.G_EXC_ERROR THEN
1583 ROLLBACK TO populate_plsql_tbl_PVT;
1584 p_return_status := FND_API.G_RET_STS_ERROR;
1585 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1586 p_data => p_msg_data);
1587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1588 ROLLBACK TO populate_plsql_tbl_PVT;
1589 p_return_status := FND_API.G_RET_STS_ERROR;
1590 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1591 p_data => p_msg_data);
1592 WHEN OTHERS THEN
1593 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1594 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1595 ROLLBACK TO populate_plsql_tbl_PVT;
1596 p_return_status := FND_API.G_RET_STS_ERROR;
1597 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1598 FND_MSG_PUB.Add_Exc_Msg(G_FILE_NAME, G_PKG_NAME, l_api_name);
1599 END IF;
1600 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1601 p_data => p_msg_data);
1602
1603 END populate_plsql_tbl_with_extval;
1604
1605 PROCEDURE populate_plsql_tbl_with_intval
1606 (p_api_version_number IN NUMBER,
1607 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1608 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
1609 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1610 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1611 p_return_status OUT NOCOPY VARCHAR2,
1612 p_msg_count OUT NOCOPY NUMBER,
1613 p_msg_data OUT NOCOPY VARCHAR2,
1614 p_key_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
1615 p_apps_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type) IS
1616
1617 l_api_name CONSTANT VARCHAR2(30) := 'populate_plsql_tbl_with_intval';
1618 l_api_version_number CONSTANT NUMBER := 1.0;
1619
1620 ckey1_used_flag VARCHAR2(1);
1621 ckey2_used_flag VARCHAR2(1);
1622 ckey3_used_flag VARCHAR2(1);
1623 ckey4_used_flag VARCHAR2(1);
1627 ckey2_used_table VARCHAR2(80);
1624 ckey5_used_flag VARCHAR2(1);
1625
1626 ckey1_used_table VARCHAR2(80);
1628 ckey3_used_table VARCHAR2(80);
1629 ckey4_used_table VARCHAR2(80);
1630 ckey5_used_table VARCHAR2(80);
1631
1632 ckey1_used_column VARCHAR2(80);
1633 ckey2_used_column VARCHAR2(80);
1634 ckey3_used_column VARCHAR2(80);
1635 ckey4_used_column VARCHAR2(80);
1636 ckey5_used_column VARCHAR2(80);
1637
1638 cxref_category_code VARCHAR2(30);
1639
1640 key1 VARCHAR2(500); -- 4011384
1641 key2 VARCHAR2(500);
1642 key3 VARCHAR2(500);
1643 key4 VARCHAR2(500);
1644 key5 VARCHAR2(500);
1645
1646 int_val VARCHAR2(500);
1647 c_ext_value1 VARCHAR2(500);
1648 c_ext_value2 VARCHAR2(500);
1649 c_ext_value3 VARCHAR2(500);
1650 c_ext_value4 VARCHAR2(500);
1651 c_ext_value5 VARCHAR2(500);
1652
1653 l_int_data_loc_pos NUMBER;
1654 l_key_data_loc_pos NUMBER;
1655
1656 return_code NUMBER;
1657 icount NUMBER;
1658 l_return_status VARCHAR2(2000);
1659 l_msg_count NUMBER;
1660 l_msg_data VARCHAR2(2000);
1661
1662 b_xref_data_found BOOLEAN := FALSE;
1663
1664 j INTEGER := 1;
1665 k INTEGER;
1666
1667 BEGIN
1668 if EC_DEBUG.G_debug_level >= 2 then
1669 EC_DEBUG.PUSH('EC_Code_Conversion_PVT.populate_plsql_tbl_with_intval');
1670 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
1671 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
1672 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
1673 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
1674 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
1675 end if;
1676 -- Standard Start of API savepoint
1677 SAVEPOINT populate_plsql_tbl_PVT;
1678
1679 -- Standard call to check for call compatibility.
1680 IF NOT FND_API.Compatible_API_Call
1681 (l_api_version_number,
1682 p_api_version_number,
1683 l_api_name,
1684 G_PKG_NAME) THEN
1685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1686 END IF;
1687
1688 -- Initialize message list if p_init_msg_list is set to TRUE.
1689 IF FND_API.to_Boolean(p_init_msg_list) THEN
1690 FND_MSG_PUB.initialize;
1691 END IF;
1692
1693 -- Initialize API return status to success
1694 p_return_status := FND_API.G_RET_STS_SUCCESS;
1695
1696 -- *******************************************************
1697 -- Move data from interface to source PL/SQL table
1698 -- These are data that do not need xref.
1699 -- *******************************************************
1700 WHILE j <= p_apps_tbl.count LOOP
1701
1702 /* Debugging Code: DO NOT DELETE! */
1703 /*
1704 BEGIN
1705 INSERT INTO ece_error(run_id, line_id, text)
1706 VALUES(
1707 81,
1708 ece_error_s.nextval,
1709 'Rec =' || p_apps_tbl(j).record_num ||
1710 '- Pos= ' || p_apps_tbl(j).Position ||
1711 '- Val=' || p_apps_tbl(j).extvalue ||
1712 ' Width= ' || p_apps_tbl(j).data_length ||
1713 ' Int Col= '|| p_apps_tbl(j).interface_column_name ||
1714 '- Apps col= ' || p_apps_tbl(j).base_column_name ||
1715 '- val= '|| p_apps_tbl(j).value);
1716 EXCEPTION
1717 WHEN OTHERS THEN
1718 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1719 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1720 END;
1721 */
1722
1723 -- *******************************************************
1724 -- If the data need xref to convert to internal value
1725 -- *******************************************************
1726 IF p_apps_tbl(j).xref_category_id IS NOT NULL AND
1727 p_apps_tbl(j).conversion_seq = 0 THEN
1728
1729 -- *******************************************************
1730 -- These are data that need xref.
1731 -- First find all external values for xref.
1732 -- *******************************************************
1733
1734 -- If the value is NOT NULL, the flat file already supplied
1735 -- the internal value and no XREF work needs to be done... (apark)
1736 IF p_apps_tbl(j).value IS NULL THEN
1737
1738 -- We're going to go through each of the Conversion Seqs and
1739 -- see if they exist or not.
1740
1741 -- 1
1742 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1743 (p_gateway_tbl => p_apps_tbl,
1744 p_conversion_group => p_apps_tbl(j).conversion_group_id,
1745 p_sequence_num => 1,
1746 p_Pos => l_int_data_loc_pos);
1747
1748 IF b_xref_data_found THEN
1749 c_ext_value1 := p_apps_tbl(l_int_data_loc_pos).value;
1750 if EC_DEBUG.G_debug_level >= 3 then
1754
1751 EC_DEBUG.PL(3, 'c_ext_value1 :', c_ext_value1);
1752 end if;
1753 END IF;
1755 -- 2
1756 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1757 (p_gateway_tbl => p_apps_tbl,
1758 p_conversion_group => p_apps_tbl(j).conversion_group_id,
1759 p_sequence_num => 2,
1760 p_Pos => l_int_data_loc_pos);
1761
1762 IF b_xref_data_found THEN
1763 c_ext_value2 := p_apps_tbl(l_int_data_loc_pos).value;
1764 if EC_DEBUG.G_debug_level >= 3 then
1765 EC_DEBUG.PL(3, 'c_ext_value2 :', c_ext_value2);
1766 end if;
1767 END IF;
1768
1769 -- 3
1770 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1771 (p_gateway_tbl => p_apps_tbl,
1772 p_conversion_group => p_apps_tbl(j).conversion_group_id,
1773 p_sequence_num => 3,
1774 p_Pos => l_int_data_loc_pos);
1775
1776 IF b_xref_data_found THEN
1777 c_ext_value3 := p_apps_tbl(l_int_data_loc_pos).value;
1778 if EC_DEBUG.G_debug_level >= 3 then
1779 EC_DEBUG.PL(3, 'c_ext_value3 :', c_ext_value3);
1780 end if;
1781 END IF;
1782
1783 -- 4
1784 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1785 (p_gateway_tbl => p_apps_tbl,
1786 p_conversion_group => p_apps_tbl(j).conversion_group_id,
1787 p_sequence_num => 4,
1788 p_Pos => l_int_data_loc_pos);
1789
1790 IF b_xref_data_found THEN
1791 c_ext_value4 := p_apps_tbl(l_int_data_loc_pos).value;
1792 if EC_DEBUG.G_debug_level >= 3 then
1793 EC_DEBUG.PL(3, 'c_ext_value4 :', c_ext_value4);
1794 end if;
1795 END IF;
1796
1797 -- 5
1798 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1799 (p_gateway_tbl => p_apps_tbl,
1800 p_conversion_group => p_apps_tbl(j).conversion_group_id,
1801 p_sequence_num => 5,
1802 p_Pos => l_int_data_loc_pos);
1803
1804 IF b_xref_data_found THEN
1805 c_ext_value5 := p_apps_tbl(l_int_data_loc_pos).value;
1806 if EC_DEBUG.G_debug_level >= 3 then
1807 EC_DEBUG.PL(3, 'c_ext_value5 :', c_ext_value5);
1808 end if;
1809 END IF;
1810
1811 -- ***************************************
1812 --
1813 -- Find out what is the xref catergory code
1814 -- The code is in the apps pl/sql table
1815 -- therefore, need to find the matching
1816 -- data loc id in apps pl/sql table
1817 --
1818 -- ***************************************
1819
1820 -- use this xref_id, go to ece_xref_category to find out all the
1821 -- enabled keys
1822 SELECT key1_used_flag, key2_used_flag, key3_used_flag,
1823 key4_used_flag, key5_used_flag,
1824 key1_used_table, key2_used_table, key3_used_table,
1825 key4_used_table, key5_used_table,
1826 key1_used_column, key2_used_column, key3_used_column,
1827 key4_used_column, key5_used_column,
1828 xref_category_code
1829 INTO ckey1_used_flag, ckey2_used_flag, ckey3_used_flag,
1830 ckey4_used_flag, ckey5_used_flag,
1831 ckey1_used_table, ckey2_used_table, ckey3_used_table,
1832 ckey4_used_table, ckey5_used_table,
1833 ckey1_used_column, ckey2_used_column, ckey3_used_column,
1834 ckey4_used_column, ckey5_used_column,
1835 cxref_category_code
1836 FROM ece_xref_categories
1837 WHERE ece_xref_categories.xref_category_id = p_apps_tbl(j).xref_category_id;
1838
1839 IF ckey1_used_flag = 'Y' AND
1840 p_apps_tbl(j).xref_key1_source_column IS NOT NULL THEN
1841 FOR k IN 1..p_key_tbl.count LOOP
1842 IF p_apps_tbl(j).xref_key1_source_column =
1843 p_key_tbl(k).interface_column_name THEN
1844 key1 := p_key_tbl(k).value;
1845 if EC_DEBUG.G_debug_level >= 3 then
1846 EC_DEBUG.PL(3, 'key1 :', key1);
1847 end if;
1848 EXIT;
1849 END IF;
1850 END LOOP;
1851 -- we assume all the key can be found in the pl/sql table
1852 END IF;
1853
1854 -- 2
1855 IF ckey2_used_flag = 'Y' AND
1856 p_apps_tbl(j).xref_key2_source_column IS NOT NULL THEN
1857 FOR k IN 1..p_key_tbl.count LOOP
1858 IF p_apps_tbl(j).xref_key2_source_column =
1859 p_key_tbl(k).interface_column_name THEN
1860 key2 := p_key_tbl(k).value;
1861 if EC_DEBUG.G_debug_level >= 3 then
1862 EC_DEBUG.PL(3, 'key2 :', key2);
1863 end if;
1864 EXIT;
1865 END IF;
1866 END LOOP;
1867 END IF;
1868
1869 -- 3
1873 IF p_apps_tbl(j).xref_key3_source_column =
1870 IF ckey3_used_flag = 'Y' AND
1871 p_apps_tbl(j).xref_key3_source_column IS NOT NULL THEN
1872 FOR k IN 1..p_key_tbl.count LOOP
1874 p_key_tbl(k).interface_column_name THEN
1875 key3 := p_key_tbl(k).value;
1876 if EC_DEBUG.G_debug_level >= 3 then
1877 EC_DEBUG.PL(3, 'key3 :', key3);
1878 end if;
1879 EXIT;
1880 END IF;
1881 END LOOP;
1882 END IF;
1883
1884 -- 4
1885 IF ckey4_used_flag = 'Y' AND
1886 p_apps_tbl(j).xref_key4_source_column IS NOT NULL THEN
1887 FOR k IN 1..p_key_tbl.count LOOP
1888 IF p_apps_tbl(j).xref_key4_source_column =
1889 p_key_tbl(k).interface_column_name THEN
1890 key4 := p_key_tbl(k).value;
1891 if EC_DEBUG.G_debug_level >= 3 then
1892 EC_DEBUG.PL(3, 'key4 :', key4);
1893 end if;
1894 EXIT;
1895 END IF;
1896 END LOOP;
1897 END IF;
1898
1899 -- 5
1900 IF ckey5_used_flag = 'Y' AND
1901 p_apps_tbl(j).xref_key5_source_column IS NOT NULL THEN
1902 FOR k IN 1..p_key_tbl.count LOOP
1903 IF p_apps_tbl(j).xref_key5_source_column =
1904 p_key_tbl(k).interface_column_name THEN
1905 key5 := p_key_tbl(k).value;
1906 if EC_DEBUG.G_debug_level >= 3 then
1907 EC_DEBUG.PL(3, 'key5 :', key5);
1908 end if;
1909 EXIT;
1910 END IF;
1911 END LOOP;
1912 END IF;
1913
1914 /* Debugging Code: DO NOT DELETE! */
1915 /*
1916 IF (debug_mode_on_int) THEN
1917 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'xref code =' || cxref_category_code);
1918 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'column =' || p_apps_tbl(j).interface_column_name);
1919 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'key 1 =' || key1);
1920 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'key 2 =' || key2);
1921 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'key 3 =' || key3);
1922 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'key 4 =' || key4);
1923 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'key 5 =' || key5);
1924 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'ext 1 =' || c_ext_value1);
1925 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'ext 2 =' || c_ext_value2);
1926 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'ext 3 =' || c_ext_value3);
1927 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'ext 4 =' || c_ext_value4);
1928 INSERT INTO ece_error VALUES (431, ece_error_s.nextval, 'ext 5 =' || c_ext_value5);
1929 COMMIT;
1930 END IF;
1931 */
1932
1933 -- Now we know the int_value, the actual value of the key1-5,
1934 -- so we just need to call int_2_ext APIs to get the
1935 -- the ext1-5 value
1936 EC_Code_Conversion_PVT.Convert_from_ext_to_int
1937 (p_api_version_number => 1.0,
1938 p_return_status => l_return_status,
1939 p_msg_count => l_msg_count,
1940 p_msg_data => l_msg_data,
1941 p_Category => cxref_category_code,
1942 p_Key1 => key1,
1943 p_Key2 => key2,
1944 p_Key3 => key3,
1945 p_Key4 => key4,
1946 p_Key5 => key5,
1947 p_Ext_val1 => c_ext_value1,
1948 p_Ext_val2 => c_ext_value2,
1949 p_Ext_val3 => c_ext_value3,
1950 p_Ext_val4 => c_ext_value4,
1951 p_Ext_val5 => c_ext_value5,
1952 p_Int_val => int_val);
1953
1954 p_apps_tbl(j).value := int_val;
1955 if EC_DEBUG.G_debug_level >= 3 then
1956 EC_DEBUG.PL(3, 'int_val :', int_val);
1957 end if;
1958 key1 := NULL;
1959 key2 := NULL;
1960 key3 := NULL;
1961 key4 := NULL;
1962 key5 := NULL;
1963
1964 c_ext_value1 := NULL;
1965 c_ext_value2 := NULL;
1966 c_ext_value3 := NULL;
1967 c_ext_value4 := NULL;
1968 c_ext_value5 := NULL;
1969 END IF; -- IF p_apps_tbl(j).value IS NULL THEN
1970
1971 -- This is to copy the external value1 to internal value if
1972 -- there is no category code assigned to the interface column and
1973 -- the internal value is null.
1974 ELSIF (p_apps_tbl(j).xref_category_id is NULL AND
1975 p_apps_tbl(j).conversion_seq = 0 AND
1976 p_apps_tbl(j).value is NULL) THEN
1977
1978 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
1979 (
1980 p_gateway_tbl => p_apps_tbl,
1981 p_conversion_group => p_apps_tbl(j).conversion_group_id,
1985
1982 p_sequence_num => 1,
1983 p_Pos => l_int_data_loc_pos
1984 );
1986 IF b_xref_data_found THEN
1987 c_ext_value1 := p_apps_tbl(l_int_data_loc_pos).value;
1988 if (c_ext_value1 is not null) then
1989 if EC_DEBUG.G_debug_level >= 3 then
1990 EC_DEBUG.PL(3, 'c_ext_value1 :', c_ext_value1);
1991 end if;
1992 p_apps_tbl(j).value := c_ext_value1;
1993 end if;
1994 END IF;
1995
1996 END IF; -- IF p_apps_tbl(j).xref_category_id IS NOT NULL AND p_apps_tbl(j).conversion_seq = 0 THEN
1997 j := j + 1;
1998 END LOOP; --WHILE j <= p_apps_tbl.count LOOP
1999
2000 -- *******************************************************
2001 -- Standard check of p_simulate and p_commit parameters
2002 -- *******************************************************
2003 IF FND_API.To_Boolean(p_simulate) THEN
2004 ROLLBACK TO populate_plsql_tbl_PVT;
2005 ELSIF FND_API.To_Boolean(p_commit) THEN
2006 COMMIT WORK;
2007 END IF;
2008
2009 -- Standard call to get message count and if count is 1, get message info.
2010 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2011 p_data => p_msg_data);
2012
2013 if l_return_status = EC_Code_Conversion_PVT.G_XREF_NOT_FOUND
2014 then
2015 p_return_status := EC_Code_Conversion_PVT.G_XREF_NOT_FOUND;
2016 end if;
2017
2018 if EC_DEBUG.G_debug_level >= 2 then
2019 EC_DEBUG.POP('EC_Code_Conversion_PVT.populate_plsql_tbl_with_intval');
2020 end if;
2021 EXCEPTION
2022 WHEN FND_API.G_EXC_ERROR THEN
2023 ROLLBACK TO populate_plsql_tbl_PVT;
2024 p_return_status := FND_API.G_RET_STS_ERROR;
2025 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2026 p_data => p_msg_data);
2027 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2028 ROLLBACK TO populate_plsql_tbl_PVT;
2029 p_return_status := FND_API.G_RET_STS_ERROR;
2030 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2031 p_data => p_msg_data);
2032 WHEN OTHERS THEN
2033 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
2034 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2035 ROLLBACK TO populate_plsql_tbl_PVT;
2036 p_return_status := FND_API.G_RET_STS_ERROR;
2037 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2038 FND_MSG_PUB.Add_Exc_Msg(G_FILE_NAME, G_PKG_NAME, l_api_name);
2039 END IF;
2040 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2041 p_data => p_msg_data);
2042 END populate_plsql_tbl_with_intval;
2043
2044 PROCEDURE populate_plsql_tbl_with_intval
2045 (
2046 p_api_version_number IN NUMBER,
2047 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2048 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
2049 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2050 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2051 p_return_status OUT NOCOPY VARCHAR2,
2052 p_msg_count OUT NOCOPY NUMBER,
2053 p_msg_data OUT NOCOPY VARCHAR2,
2054 p_apps_tbl IN OUT NOCOPY ec_utils.mapping_tbl,
2055 p_level IN NUMBER
2056 ) IS
2057
2058
2059 l_api_name CONSTANT VARCHAR2(30) := 'populate_plsql_tbl_with_intval';
2060 l_api_version_number CONSTANT NUMBER := 1.0;
2061
2062 ckey1_used_flag VARCHAR2(1);
2063 ckey2_used_flag VARCHAR2(1);
2064 ckey3_used_flag VARCHAR2(1);
2065 ckey4_used_flag VARCHAR2(1);
2066 ckey5_used_flag VARCHAR2(1);
2067
2068 ckey1_used_table VARCHAR2(80);
2069 ckey2_used_table VARCHAR2(80);
2070 ckey3_used_table VARCHAR2(80);
2071 ckey4_used_table VARCHAR2(80);
2072 ckey5_used_table VARCHAR2(80);
2073
2074 ckey1_used_column VARCHAR2(80);
2075 ckey2_used_column VARCHAR2(80);
2076 ckey3_used_column VARCHAR2(80);
2077 ckey4_used_column VARCHAR2(80);
2078 ckey5_used_column VARCHAR2(80);
2079
2080 cxref_category_code VARCHAR2(30);
2081
2082 key1 VARCHAR2(500); -- 4011384
2083 key2 VARCHAR2(500);
2084 key3 VARCHAR2(500);
2085 key4 VARCHAR2(500);
2086 key5 VARCHAR2(500);
2087
2088 int_val VARCHAR2(500);
2089 c_ext_value1 VARCHAR2(500);
2090 c_ext_value2 VARCHAR2(500);
2091 c_ext_value3 VARCHAR2(500);
2092 c_ext_value4 VARCHAR2(500);
2093 c_ext_value5 VARCHAR2(500);
2094
2095 l_int_data_loc_pos NUMBER;
2096 l_key_data_loc_pos NUMBER;
2097
2098 return_code NUMBER;
2099 icount NUMBER;
2100 l_return_status VARCHAR2(2000);
2101 l_msg_count NUMBER;
2102 l_msg_data VARCHAR2(2000);
2103
2107
2104 b_xref_data_found BOOLEAN := FALSE;
2105
2106 k INTEGER;
2108 BEGIN
2109 if EC_DEBUG.G_debug_level >= 2 then
2110 EC_DEBUG.PUSH('EC_Code_Conversion_PVT.populate_plsql_tbl_with_intval');
2111 EC_DEBUG.PL(3, 'API version : ',p_api_version_number);
2112 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
2113 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
2114 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
2115 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
2116 EC_DEBUG.PL(3, 'p_level',p_level);
2117 end if;
2118 -- Standard Start of API savepoint
2119 SAVEPOINT populate_plsql_tbl_PVT;
2120
2121 -- Standard call to check for call compatibility.
2122 IF NOT FND_API.Compatible_API_Call
2123 (l_api_version_number,
2124 p_api_version_number,
2125 l_api_name,
2126 G_PKG_NAME) THEN
2127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2128 END IF;
2129
2130 -- Initialize message list if p_init_msg_list is set to TRUE.
2131 IF FND_API.to_Boolean(p_init_msg_list) THEN
2132 FND_MSG_PUB.initialize;
2133 END IF;
2134
2135 -- Initialize API return status to success
2136 p_return_status := FND_API.G_RET_STS_SUCCESS;
2137
2138 -- *******************************************************
2139 -- Move data from interface to source PL/SQL table
2140 -- These are data that do not need xref.
2141 -- *******************************************************
2142 for j in ec_utils.g_ext_levels(p_level).file_start_pos..ec_utils.g_ext_levels(p_level).file_end_pos
2143 LOOP
2144
2145 -- This is to copy the external value1 to internal value if
2146 -- there is no category code assigned to the interface column and
2147 -- the internal value is null.
2148 -- Bug 2708573
2149 -- Moved the ELSIF to the begin of IF, as less time is consumed
2150 -- for IS NULL check as compared to IS NOT NULL.
2151 IF p_apps_tbl(j).xref_category_id is NULL THEN
2152
2153 IF p_apps_tbl(j).conversion_sequence = 0 AND
2154 p_apps_tbl(j).value is NULL THEN
2155
2156 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2157 (
2158 p_level => p_level,
2159 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2160 p_sequence_num => 1,
2161 p_Pos => l_int_data_loc_pos
2162 );
2163
2164 IF b_xref_data_found THEN
2165 c_ext_value1 := p_apps_tbl(l_int_data_loc_pos).value;
2166 if (c_ext_value1 is not null) then
2167 p_apps_tbl(j).value := c_ext_value1;
2168 end if;
2169 END IF;
2170 if EC_DEBUG.G_debug_level >= 3 then
2171 ec_debug.pl(3,'Interface Column Name ',p_apps_tbl(j).Interface_Column_Name);
2172 ec_debug.pl(3,'Internal Value ',p_apps_tbl(j).value);
2173 EC_DEBUG.PL(3, 'c_ext_value1 :', c_ext_value1);
2174 end if;
2175 END IF;
2176
2177 -- *******************************************************
2178 -- If the data need xref to convert to internal value
2179 -- *******************************************************
2180 ELSE
2181 -- Bug 2708573
2182 -- Removed p_apps_tbl(j).xref_category_id IS NOT NULL check
2183 -- in the condition below.
2184
2185 IF p_apps_tbl(j).conversion_sequence = 0 AND
2186 p_apps_tbl(j).value IS NULL THEN
2187
2188 if EC_DEBUG.G_debug_level >= 3 then
2189 ec_debug.pl(3,'Interface Column Name ',p_apps_tbl(j).Interface_Column_Name);
2190 ec_debug.pl(3,'Internal Value ',p_apps_tbl(j).value);
2191 end if;
2192 -- *******************************************************
2193 -- These are data that need xref.
2194 -- First find all external values for xref.
2195 -- *******************************************************
2196
2197 -- If the value is NOT NULL, the flat file already supplied
2198 -- the internal value and no XREF work needs to be done... (apark)
2199 -- IF p_apps_tbl(j).value IS NULL THEN : Bug 2708573
2200
2201 -- We're going to go through each of the Conversion Seqs and
2202 -- see if they exist or not.
2203
2204 -- 1
2205 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2206 (
2207 p_level => p_level ,
2208 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2209 p_sequence_num => 1,
2210 p_Pos => l_int_data_loc_pos
2211 );
2212
2213 IF b_xref_data_found THEN
2214 c_ext_value1 := p_apps_tbl(l_int_data_loc_pos).value;
2215 END IF;
2216
2217 -- 2
2218 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2219 (
2220 p_level => p_level ,
2221 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2222 p_sequence_num => 2,
2223 p_Pos => l_int_data_loc_pos
2224 );
2225
2226 IF b_xref_data_found THEN
2227 c_ext_value2 := p_apps_tbl(l_int_data_loc_pos).value;
2231 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2228 END IF;
2229
2230 -- 3
2232 (
2233 p_level => p_level ,
2234 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2235 p_sequence_num => 3,
2236 p_Pos => l_int_data_loc_pos
2237 );
2238
2239 IF b_xref_data_found THEN
2240 c_ext_value3 := p_apps_tbl(l_int_data_loc_pos).value;
2241 END IF;
2242
2243 -- 4
2244 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2245 (
2246 p_level => p_level ,
2247 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2248 p_sequence_num => 4,
2249 p_Pos => l_int_data_loc_pos
2250 );
2251
2252 IF b_xref_data_found THEN
2253 c_ext_value4 := p_apps_tbl(l_int_data_loc_pos).value;
2254 END IF;
2255
2256 -- 5
2257 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2258 (
2259 p_level => p_level ,
2260 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2261 p_sequence_num => 5,
2262 p_Pos => l_int_data_loc_pos
2263 );
2264
2265 IF b_xref_data_found THEN
2266 c_ext_value5 := p_apps_tbl(l_int_data_loc_pos).value;
2267 END IF;
2268
2269 if EC_DEBUG.G_debug_level >= 3 then
2270 EC_DEBUG.PL(3, 'c_ext_value1 :', c_ext_value1);
2271 EC_DEBUG.PL(3, 'c_ext_value2 :', c_ext_value2);
2272 EC_DEBUG.PL(3, 'c_ext_value3 :', c_ext_value3);
2273 EC_DEBUG.PL(3, 'c_ext_value4 :', c_ext_value4);
2274 EC_DEBUG.PL(3, 'c_ext_value5 :', c_ext_value5);
2275 end if;
2276 -- ***************************************
2277 --
2278 -- Find out what is the xref catergory code
2279 -- The code is in the apps pl/sql table
2280 -- therefore, need to find the matching
2281 -- data loc id in apps pl/sql table
2282 --
2283 -- ***************************************
2284
2285 -- use this xref_id, go to ece_xref_category to find out all the
2286 -- enabled keys
2287 SELECT key1_used_flag, key2_used_flag, key3_used_flag,
2288 key4_used_flag, key5_used_flag,
2289 key1_used_table, key2_used_table, key3_used_table,
2290 key4_used_table, key5_used_table,
2291 key1_used_column, key2_used_column, key3_used_column,
2292 key4_used_column, key5_used_column,
2293 xref_category_code
2294 INTO ckey1_used_flag, ckey2_used_flag, ckey3_used_flag,
2295 ckey4_used_flag, ckey5_used_flag,
2296 ckey1_used_table, ckey2_used_table, ckey3_used_table,
2297 ckey4_used_table, ckey5_used_table,
2298 ckey1_used_column, ckey2_used_column, ckey3_used_column,
2299 ckey4_used_column, ckey5_used_column,
2300 cxref_category_code
2301 FROM ece_xref_categories
2302 WHERE ece_xref_categories.xref_category_id = p_apps_tbl(j).xref_category_id;
2303 if EC_DEBUG.G_debug_level >= 3 then
2304 ec_debug.pl(3,'xref_category_id',p_apps_tbl(j).xref_category_id);
2305 ec_debug.pl(3,'cxref_category_code',cxref_category_code);
2306 end if;
2307
2308 -- Bug 2828072
2309 IF ckey1_used_flag = 'Y' AND
2310 p_apps_tbl(j).xref_key1_source_column IS NOT NULL THEN
2311 FOR k IN REVERSE 1..p_level LOOP
2312 FOR i in ec_utils.g_ext_levels(k).file_start_pos..ec_utils.g_ext_levels(k).file_end_pos
2313 LOOP
2314 IF p_apps_tbl(j).xref_key1_source_column =
2315 p_apps_tbl(i).interface_column_name THEN
2316 key1 := p_apps_tbl(i).value;
2317 EXIT;
2318 END IF;
2319 END LOOP;
2320 IF key1 IS NOT NULL THEN
2321 EXIT;
2322 END IF;
2323 END LOOP;
2324 -- we assume all the key can be found in the pl/sql table
2325 END IF;
2326
2327 -- 2
2328 IF ckey2_used_flag = 'Y' AND
2329 p_apps_tbl(j).xref_key2_source_column IS NOT NULL THEN
2330 FOR k IN REVERSE 1..p_level LOOP
2331 FOR i in ec_utils.g_ext_levels(k).file_start_pos..ec_utils.g_ext_levels(k).file_end_pos
2332 LOOP
2333 IF p_apps_tbl(j).xref_key2_source_column =
2334 p_apps_tbl(i).interface_column_name THEN
2335 key2 := p_apps_tbl(i).value;
2336 EXIT;
2337 END IF;
2338 END LOOP;
2339 IF key2 IS NOT NULL THEN
2340 EXIT;
2341 END IF;
2342 END LOOP;
2343 END IF;
2344
2345 -- 3
2346 IF ckey3_used_flag = 'Y' AND
2347 p_apps_tbl(j).xref_key3_source_column IS NOT NULL THEN
2348 FOR k IN REVERSE 1..p_level LOOP
2349 FOR i in ec_utils.g_ext_levels(k).file_start_pos..ec_utils.g_ext_levels(k).file_end_pos
2350 LOOP
2354 EXIT;
2351 IF p_apps_tbl(j).xref_key3_source_column =
2352 p_apps_tbl(i).interface_column_name THEN --bug 4136922
2353 key3 := p_apps_tbl(i).value;
2355 END IF;
2356 END LOOP;
2357 IF key3 IS NOT NULL THEN
2358 EXIT;
2359 END IF;
2360 END LOOP;
2361 END IF;
2362
2363 -- 4
2364 IF ckey4_used_flag = 'Y' AND
2365 p_apps_tbl(j).xref_key4_source_column IS NOT NULL THEN
2366 FOR k IN REVERSE 1..p_level LOOP
2367 FOR i in ec_utils.g_ext_levels(k).file_start_pos..ec_utils.g_ext_levels(k).file_end_pos
2368 LOOP
2369 IF p_apps_tbl(j).xref_key4_source_column =
2370 p_apps_tbl(i).interface_column_name THEN
2371 key4 := p_apps_tbl(i).value;
2372 EXIT;
2373 END IF;
2374 END LOOP;
2375 IF key4 IS NOT NULL THEN
2376 EXIT;
2377 END IF;
2378 END LOOP;
2379 END IF;
2380
2381 -- 5
2382 IF ckey5_used_flag = 'Y' AND
2383 p_apps_tbl(j).xref_key5_source_column IS NOT NULL THEN
2384 FOR k IN REVERSE 1..p_level LOOP
2385 FOR i in ec_utils.g_ext_levels(k).file_start_pos..ec_utils.g_ext_levels(k).file_end_pos
2386 LOOP
2387 IF p_apps_tbl(j).xref_key5_source_column =
2388 p_apps_tbl(i).interface_column_name THEN
2389 key5 := p_apps_tbl(i).value;
2390 EXIT;
2391 END IF;
2392 END LOOP;
2393 IF key5 IS NOT NULL THEN
2394 EXIT;
2395 END IF;
2396 END LOOP;
2397 END IF;
2398
2399 IF EC_DEBUG.G_debug_level = 3 THEN
2400 EC_DEBUG.PL(3, 'key1 :', key1);
2401 EC_DEBUG.PL(3, 'key2 :', key2);
2402 EC_DEBUG.PL(3, 'key3 :', key3);
2403 EC_DEBUG.PL(3, 'key4 :', key4);
2404 EC_DEBUG.PL(3, 'key5 :', key5);
2405 END IF;
2406
2407 -- Now we know the int_value, the actual value of the key1-5,
2408 -- so we just need to call int_2_ext APIs to get the
2409 -- the ext1-5 value
2410 EC_Code_Conversion_PVT.Convert_from_ext_to_int
2411 (p_api_version_number => 1.0,
2412 p_return_status => l_return_status,
2413 p_msg_count => l_msg_count,
2414 p_msg_data => l_msg_data,
2415 p_Category => cxref_category_code,
2416 p_Key1 => key1,
2417 p_Key2 => key2,
2418 p_Key3 => key3,
2419 p_Key4 => key4,
2420 p_Key5 => key5,
2421 p_Ext_val1 => c_ext_value1,
2422 p_Ext_val2 => c_ext_value2,
2423 p_Ext_val3 => c_ext_value3,
2424 p_Ext_val4 => c_ext_value4,
2425 p_Ext_val5 => c_ext_value5,
2426 p_Int_val => int_val);
2427
2428 p_apps_tbl(j).value := int_val;
2429 if EC_DEBUG.G_debug_level >= 3 then
2430 EC_DEBUG.PL(3, 'Internal value after code conversion:', int_val);
2431 end if;
2432 key1 := NULL;
2433 key2 := NULL;
2434 key3 := NULL;
2435 key4 := NULL;
2436 key5 := NULL;
2437
2438 c_ext_value1 := NULL;
2439 c_ext_value2 := NULL;
2440 c_ext_value3 := NULL;
2441 c_ext_value4 := NULL;
2442 c_ext_value5 := NULL;
2443 END IF; -- IF p_apps_tbl(j).value IS NULL THEN
2444
2445 -- This is to copy the external value1 to internal value if
2446 -- there is no category code assigned to the interface column and
2447 -- the internal value is null.
2448 /* Bug 2708573
2449 ELSIF (p_apps_tbl(j).xref_category_id is NULL AND
2450 p_apps_tbl(j).conversion_sequence = 0 AND
2451 p_apps_tbl(j).value is NULL) THEN
2452 if EC_DEBUG.G_debug_level >= 3 then
2453 ec_debug.pl(3,'Interface Column Name ',p_apps_tbl(j).Interface_Column_Name);
2454 ec_debug.pl(3,'Internal Value ',p_apps_tbl(j).value);
2455 end if;
2456 b_xref_data_found := ece_flatfile_pvt.match_xref_conv_seq
2457 (
2458 p_level => p_level,
2459 p_conversion_group => p_apps_tbl(j).conversion_group_id,
2460 p_sequence_num => 1,
2461 p_Pos => l_int_data_loc_pos
2462 );
2463
2464 IF b_xref_data_found THEN
2465 c_ext_value1 := p_apps_tbl(l_int_data_loc_pos).value;
2466 if (c_ext_value1 is not null) then
2467 if EC_DEBUG.G_debug_level >= 3 then
2468 EC_DEBUG.PL(3, 'c_ext_value1 :', c_ext_value1);
2469 end if;
2470 p_apps_tbl(j).value := c_ext_value1;
2471 end if;
2472 END IF;
2473 */
2474 END IF; -- IF p_apps_tbl(j).xref_category_id IS NOT NULL AND p_apps_tbl(j).conversion_sequence = 0 THEN
2475
2476 END LOOP;
2477
2478 -- *******************************************************
2479 -- Standard check of p_simulate and p_commit parameters
2480 -- *******************************************************
2481 IF FND_API.To_Boolean(p_simulate) THEN
2482 ROLLBACK TO populate_plsql_tbl_PVT;
2483 ELSIF FND_API.To_Boolean(p_commit) THEN
2484 COMMIT WORK;
2485 END IF;
2486
2487 -- Standard call to get message count and if count is 1, get message info.
2488 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2489 p_data => p_msg_data);
2490
2491 if l_return_status = EC_Code_Conversion_PVT.G_XREF_NOT_FOUND
2492 then
2493 p_return_status := EC_Code_Conversion_PVT.G_XREF_NOT_FOUND;
2494 end if;
2495
2496 if EC_DEBUG.G_debug_level >= 2 then
2497 EC_DEBUG.POP('EC_Code_Conversion_PVT.populate_plsql_tbl_with_intval');
2498 end if;
2499 EXCEPTION
2500 WHEN FND_API.G_EXC_ERROR THEN
2501 ROLLBACK TO populate_plsql_tbl_PVT;
2502 p_return_status := FND_API.G_RET_STS_ERROR;
2503 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2504 p_data => p_msg_data);
2505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2506 ROLLBACK TO populate_plsql_tbl_PVT;
2507 p_return_status := FND_API.G_RET_STS_ERROR;
2508 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2509 p_data => p_msg_data);
2510 WHEN OTHERS THEN
2511 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
2512 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2513 ROLLBACK TO populate_plsql_tbl_PVT;
2514 p_return_status := FND_API.G_RET_STS_ERROR;
2515 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2516 FND_MSG_PUB.Add_Exc_Msg(G_FILE_NAME, G_PKG_NAME, l_api_name);
2517 END IF;
2518 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2519 p_data => p_msg_data);
2520 END populate_plsql_tbl_with_intval;
2521
2522 END EC_Code_Conversion_PVT;
[Home] [Help]