[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCCOAINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncCOAInbound_PVT AS
2 /* ARCS: $Header: itgvscib.pls 120.3 2006/09/15 13:37:47 pvaddana noship $
3 * CVS: itgvscib.pls,v 1.17 2002/12/23 21:20:30 ecoe Exp
4 */
5
6 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_SyncCOAInbound_PVT';
8 g_action VARCHAR2(100);
9
10 /* SEE AFQUTILB.pls: FND_IP_UTIL_PKG.processFlexValue() */
11
12 TYPE flex_val_rec_type IS RECORD (
13 flex_value_id fnd_flex_values.flex_value_id%TYPE,
14 enabled_flag fnd_flex_values.enabled_flag%TYPE
15 );
16
17 TYPE flex_val_tbl_type IS TABLE OF flex_val_rec_type INDEX BY BINARY_INTEGER;
18
19
20 FUNCTION get_flex_data(
21 p_flex_value IN VARCHAR2,
22 p_vset_id IN NUMBER,
23 x_flex_val_tbl OUT NOCOPY flex_val_tbl_type
24 ) RETURN BOOLEAN
25 IS
26 CURSOR flex_data_csr IS
27 SELECT flex_value_id,
28 enabled_flag
29 FROM fnd_flex_values
30 WHERE flex_value = p_flex_value
31 AND flex_value_set_id = p_vset_id;
32
33 l_found BOOLEAN := FALSE;
34 BEGIN
35 x_flex_val_tbl.delete;
36 FOR rec IN flex_data_csr LOOP
37 x_flex_val_tbl(flex_data_csr%ROWCOUNT) := rec;
38 l_found := TRUE;
39 END LOOP;
40 RETURN l_found;
41 END get_flex_data;
42
43
44 FUNCTION vset_is_not_valid(
45 p_vset_id NUMBER
46 ) RETURN BOOLEAN
47 IS
48 l_count NUMBER;
49 BEGIN
50 SELECT count(*)
51 INTO l_count
52 FROM fnd_id_flex_segments fs,
53 fnd_flex_value_sets vs
54 WHERE fs.enabled_flag = 'Y'
55 AND fs.application_id = 101
56 AND fs.id_flex_code = 'GL#'
57 AND fs.flex_value_set_id = vs.flex_value_set_id
58 AND vs.flex_value_set_id = p_vset_id;
59
60 RETURN l_count = 0;
61 END vset_is_not_valid;
62
63 /*Decfined Private Function to check MAX size of flex value against the value defined in Setup : Flexfields : Validation : Sets to fix bug : 5533589
64 All Flex values are constrained to their value-set defined validations in the above form */
65 FUNCTION flex_value_is_not_valid(
66 p_vset_id IN NUMBER,
67 p_flex_value IN VARCHAR2
68 ) RETURN BOOLEAN
69 IS
70 l_vset_max_size NUMBER;
71 BEGIN
72
73 SELECT MAXIMUM_SIZE
74 INTO l_vset_max_size
75 FROM FND_FLEX_VALUE_SETS
76 WHERE FLEX_VALUE_SET_ID =p_vset_id;
77 RETURN LENGTH(p_flex_value) > l_vset_max_size;
78
79 END flex_value_is_not_valid;
80
81 FUNCTION acct_type_required(
82 p_vset_id IN NUMBER
83 ) RETURN BOOLEAN
84 IS
85 l_count NUMBER;
86 BEGIN
87 SELECT count(*)
88 INTO l_count
89 FROM fnd_id_flex_segments s,
90 fnd_segment_attribute_values sav,
91 fnd_segment_attribute_types sat
92 WHERE s.application_id = sav.application_id
93 AND s.id_flex_code = sav.id_flex_code
94 AND s.id_flex_num = sav.id_flex_num
95 AND s.enabled_flag = 'Y'
96 AND s.flex_value_set_id = p_vset_id
97 AND s.application_column_name = sav.application_column_name
98 AND sav.application_id = sat.application_id
99 AND sav.id_flex_code = sat.id_flex_code
100 AND sav.attribute_value = 'Y'
101 AND sav.segment_attribute_type = sat.segment_attribute_type
102 AND sat.application_id = 101
103 AND sat.id_flex_code = 'GL#'
104 AND sat.unique_flag = 'Y'
105 AND sat.segment_attribute_type = 'GL_ACCOUNT';
106
107 RETURN l_count > 0;
108 END acct_type_required;
109
110 /* Public procs */
111
112 PROCEDURE Add_FlexValue(
113 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
116 p_flex_value IN VARCHAR2,
117 p_vset_id IN NUMBER,
118 p_flex_desc IN VARCHAR2,
119 p_creation_date IN DATE := NULL,
120 p_effective_date IN DATE,
121 p_expiration_date IN DATE,
122 p_acct_type IN VARCHAR2,
123 p_enabled_flag IN VARCHAR2
124 )
125 IS
126 l_api_name CONSTANT VARCHAR2(30) := 'Add_FlexValue';
127 l_api_version CONSTANT NUMBER := 1.0;
128 l_flex_val_tbl flex_val_tbl_type;
129 l_comp_attrs FND_FLEX_VALUES.compiled_value_attributes%TYPE;
130 l_next_id NUMBER;
131 l_rowid VARCHAR2(100);
132 l_creation_date DATE;
133
134 BEGIN
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 IF (l_Debug_Level <= 2) THEN
138 itg_debug_pub.Add('ENTERING Add_FlexValue',2);
139 END IF;
140
141 BEGIN
142 ITG_Debug.setup(
143 p_reset => TRUE,
144 p_pkg_name => G_PKG_NAME,
145 p_proc_name => l_api_name);
146
147 g_action := 'Flex-value Insert';
148
149 IF (l_Debug_Level <= 1) THEN
150 itg_debug_pub.Add('AFV - Top of procedure.' ,1);
151 itg_debug_pub.Add('AFV - p_flex_value' ||p_flex_value ,1);
152 itg_debug_pub.Add('AFV - p_vset_id' ||p_vset_id ,1);
153 itg_debug_pub.Add('AFV - p_flex_desc' ||p_flex_desc ,1);
154 itg_debug_pub.Add('AFV - p_creation_date' ||p_creation_date ,1);
155 itg_debug_pub.Add('AFV - p_effective_date' ||p_effective_date ,1);
156 itg_debug_pub.Add('AFV - p_expiration_date' ||p_expiration_date,1);
157 itg_debug_pub.Add('AFV - p_acct_type' ||p_acct_type ,1);
158 itg_debug_pub.Add('AFV - p_enabled_flag' ||p_enabled_flag ,1);
159 END IF;
160
161 DECLARE
162 l_param_name VARCHAR2(30) := NULL;
163 l_param_value VARCHAR2(2000) := 'NULL';
164 BEGIN
165 g_action := 'Flex-parameters validation';
166
167 IF p_flex_value IS NULL THEN
168 l_param_name := 'ORACLEITG.FIELDVALUE';
169 ElSIF p_vset_id IS NULL THEN
170 l_param_name := 'ORACLEITG.FIELDID';
171 ELSIF p_effective_date IS NULL THEN
172 l_param_name := 'DATETIME(EFFECTIVE)';
173 ELSIF p_expiration_date IS NULL THEN
174 l_param_name := 'DATETIME(EXPIRATION)';
175 ELSIF nvl(upper(p_enabled_flag), 'z') NOT IN ('Y', 'N') THEN
176 l_param_name := 'ORACLEITG.ACTIVE';
177 l_param_value := p_enabled_flag;
178 ELSIF vset_is_not_valid(p_vset_id) THEN
179 itg_msg.no_vset(p_vset_id);
180 RAISE FND_API.G_EXC_ERROR;
181 ELSIF flex_value_is_not_valid( p_vset_id,p_flex_value ) THEN --Added below elsif branch to validate flex-val max size to fix bug :5533589
182 ITG_MSG.INVALID_FLEXVAL_LENGTH(p_vset_id,p_flex_value);
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 IF l_param_name IS NOT NULL THEN
187 ITG_MSG.missing_element_value(l_param_name, l_param_value);
188 RAISE FND_API.G_EXC_ERROR;
189 END IF;
190
191 IF get_flex_data(p_flex_value, p_vset_id, l_flex_val_tbl) THEN
192 ITG_MSG.existing_flex_value(p_flex_value, p_vset_id);
193 RAISE FND_API.G_EXC_ERROR;
194 END IF;
195
196 IF acct_type_required(p_vset_id) AND (nvl(p_acct_type, 'z') NOT IN ('A', 'L', 'O', 'R', 'E')) THEN
197 ITG_MSG.invalid_account_type(p_acct_type);
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200 END;
201
202 g_action := 'Flex-value insert';
203
204 IF acct_type_required(p_vset_id) THEN
205 /* Key:
206 * Allow Budgeting = Y,
207 * Allow Posting = Y,
208 * Account Type = value,
209 * Reconciliation = N
210 */
211 l_comp_attrs := 'Y'||fnd_global.local_chr(10)||'Y'||
212 fnd_global.local_chr(10)||p_acct_type||
213 fnd_global.local_chr(10)||'N';
214 ELSE
215 /* Key:
216 * Allow Budgeting = Y,
217 * Allow Posting = Y,
218 */
219 l_comp_attrs := 'Y'||fnd_global.local_chr(10)||'Y';
220 END IF;
221
222 /* next flex value id. */
223 SELECT fnd_flex_values_s.nextval
224 INTO l_next_id
225 FROM dual;
226
227 l_creation_date := nvl(p_creation_date, SYSDATE);
228
229 BEGIN
230 IF (l_Debug_Level <= 1) THEN
231 itg_debug_pub.Add('Inserting flex value row.' ,1);
232 END IF;
233
234 g_action := 'Flex-value insert';
235 FND_FLEX_VALUES_PKG.insert_row(
236 x_rowid => l_rowid,
237 x_flex_value_id => l_next_id,
238 x_flex_value_set_id => p_vset_id,
239 x_flex_value => p_flex_value,
240 x_enabled_flag => p_enabled_flag,
241 x_summary_flag => 'N',
242 x_start_date_active => p_effective_date,
243 x_end_date_active => p_expiration_date,
244 x_parent_flex_value_low => NULL,
245 x_parent_flex_value_high => NULL,
246 x_structured_hierarchy_level => NULL,
247 x_hierarchy_level => NULL,
248 x_compiled_value_attributes => l_comp_attrs,
249 x_value_category => NULL,
250 x_flex_value_meaning => p_flex_value,
251 x_description => p_flex_desc,
252 x_creation_date => l_creation_date,
253 x_created_by => FND_GLOBAL.user_id,
254 x_last_update_date => l_creation_date,
255 x_last_updated_by => FND_GLOBAL.user_id,
256 x_last_update_login => FND_GLOBAL.login_id,
257 x_attribute_sort_order => NULL,
258 /* Why didn't the flex people default these to NULL??? */
259 x_attribute1 => NULL, x_attribute2 => NULL, x_attribute3 => NULL,
260 x_attribute4 => NULL, x_attribute5 => NULL, x_attribute6 => NULL,
261 x_attribute7 => NULL, x_attribute8 => NULL, x_attribute9 => NULL,
262 x_attribute10 => NULL, x_attribute11 => NULL, x_attribute12 => NULL,
263 x_attribute13 => NULL, x_attribute14 => NULL, x_attribute15 => NULL,
264 x_attribute16 => NULL, x_attribute17 => NULL, x_attribute18 => NULL,
265 x_attribute19 => NULL, x_attribute20 => NULL, x_attribute21 => NULL,
266 x_attribute22 => NULL, x_attribute23 => NULL, x_attribute24 => NULL,
267 x_attribute25 => NULL, x_attribute26 => NULL, x_attribute27 => NULL,
268 x_attribute28 => NULL, x_attribute29 => NULL, x_attribute30 => NULL,
269 x_attribute31 => NULL, x_attribute32 => NULL, x_attribute33 => NULL,
270 x_attribute34 => NULL, x_attribute35 => NULL, x_attribute36 => NULL,
271 x_attribute37 => NULL, x_attribute38 => NULL, x_attribute39 => NULL,
272 x_attribute40 => NULL, x_attribute41 => NULL, x_attribute42 => NULL,
273 x_attribute43 => NULL, x_attribute44 => NULL, x_attribute45 => NULL,
274 x_attribute46 => NULL, x_attribute47 => NULL, x_attribute48 => NULL,
275 x_attribute49 => NULL, x_attribute50 => NULL
276 );
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 ITG_MSG.flex_insert_fail(p_flex_value);
280 IF (l_Debug_Level <= 5) THEN
281 itg_debug_pub.Add('EXITING Add_FlexValue :NO_DATA_FOUND ' ,5);
282 END IF;
283
284 RAISE FND_API.G_EXC_ERROR;
285 END;
286
287 IF (l_Debug_Level <= 2) THEN
288 itg_debug_pub.Add('EXITING Add_FlexValue' ,2);
289 END IF;
290 END;
291 END Add_FlexValue;
292
293
294
295 PROCEDURE Change_FlexValue(
296 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
299 p_flex_value IN VARCHAR2,
300 p_vset_id IN NUMBER,
301 p_flex_desc IN VARCHAR2,
302 p_update_date IN DATE := NULL,
303 p_effective_date IN DATE,
304 p_expiration_date IN DATE,
305 p_enabled_flag IN VARCHAR2
306 )
307 IS
308 l_api_name CONSTANT VARCHAR2(30) := 'Change_FlexValue';
309 l_api_version CONSTANT NUMBER := 1.0;
310
311 l_flex_val_tbl flex_val_tbl_type;
312 l_next_id NUMBER;
313 l_value_id NUMBER;
314 l_update_date DATE;
315 BEGIN
316
317 IF (l_Debug_Level <= 2) THEN
318 itg_debug_pub.Add('ENTERING Change_FlexValue' ,2);
319 END IF;
320
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322 g_action := 'Flex-value update';
323
324 BEGIN
325 ITG_Debug.setup(
326 p_reset => TRUE,
327 p_pkg_name => G_PKG_NAME,
328 p_proc_name => l_api_name);
329
330 IF (l_Debug_Level <= 1) THEN
331 itg_debug_pub.Add('CFV - Top of procedure.' ,1);
332 itg_debug_pub.Add('CFV - p_flex_value' ||p_flex_value ,1);
333 itg_debug_pub.Add('CFV - p_vset_id' ||p_vset_id ,1);
334 itg_debug_pub.Add('CFV - p_flex_desc' ||p_flex_desc ,1);
335 itg_debug_pub.Add('CFV - p_update_date' ||p_update_date ,1);
336 itg_debug_pub.Add('CFV - p_effective_date' ||p_effective_date ,1);
337 itg_debug_pub.Add('CFV - p_expiration_date'||p_expiration_date ,1);
338 itg_debug_pub.Add('CFV - p_enabled_flag' ||p_enabled_flag ,1);
339 END IF;
340
341 /* I am not sure about the value of preventing these validations. */
342 DECLARE
343 l_param_name VARCHAR2(30) := NULL;
344 l_param_value VARCHAR2(2000) := 'NULL';
345 BEGIN
346 g_action := 'Flex-parameters validation';
347 IF p_flex_value IS NULL THEN
348 l_param_name := 'ORACLEITG.FIELDVALUE';
349 ElSIF p_vset_id IS NULL THEN
350 l_param_name := 'ORACLEITG.FIELDID';
351 ELSIF p_effective_date IS NULL THEN
352 l_param_name := 'DATETIME(EFFECTIVE)';
353 ELSIF p_expiration_date IS NULL THEN
354 l_param_name := 'DATETIME(EXPIRATION)';
355 ELSIF nvl(upper(p_enabled_flag), 'z') NOT IN ('Y', 'N') THEN
356 l_param_name := 'ORACLEITG.ACTIVE';
357 l_param_value := p_enabled_flag;
358 ELSIF vset_is_not_valid(p_vset_id) THEN
359 itg_msg.no_vset(p_vset_id);
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363 IF l_param_name IS NOT NULL THEN
364 ITG_MSG.missing_element_value(l_param_name, l_param_value);
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367
368 IF NOT get_flex_data(p_flex_value, p_vset_id, l_flex_val_tbl) THEN
369 ITG_MSG.flex_update_fail_novalue(p_flex_value);
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372 END;
373
374 g_action := 'Flex-value update';
375 l_update_date := nvl(p_update_date, SYSDATE);
376 l_next_id := l_flex_val_tbl.FIRST;
377
378 WHILE l_next_id IS NOT NULL LOOP
379 l_value_id := l_flex_val_tbl(l_next_id).flex_value_id;
380 /* Do the flex_value... */
381 UPDATE fnd_flex_values
382 SET start_date_active = nvl(p_effective_date, start_date_active),
383 end_date_active = nvl(p_expiration_date, end_date_active),
384 enabled_flag = p_enabled_flag,
385 last_update_date = l_update_date,
386 last_updated_by = FND_GLOBAL.user_id
387 WHERE flex_value_id = l_value_id;
388
389 IF SQL%ROWCOUNT = 0 THEN
390 ITG_MSG.flex_update_fail_novalue(p_flex_value);
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 /* ...and the translated description */
395 UPDATE fnd_flex_values_tl
396 SET description = p_flex_desc,
397 last_update_date = l_update_date,
398 last_updated_by = FND_GLOBAL.user_id
399 WHERE flex_value_id = l_value_id
400 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
401
402 IF SQL%ROWCOUNT = 0 THEN
403 ITG_MSG.flex_update_fail_notl(p_flex_value);
404 RAISE FND_API.G_EXC_ERROR;
405 END IF;
406
407 /* Iterate */
408 l_next_id := l_flex_val_tbl.NEXT(l_next_id);
409 END LOOP;
410
411 IF (l_Debug_Level <= 2) THEN
412 itg_debug_pub.Add('EXITING Change_FlexValue' ,2);
413 END IF;
414 END;
415 END Change_FlexValue;
416
417
418 PROCEDURE Sync_FlexValue(
419 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
420 x_msg_count OUT NOCOPY NUMBER,
421 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
422 p_syncind IN VARCHAR2, /* 'A'dd or 'C'hange */
423 p_flex_value IN VARCHAR2,
424 p_vset_id IN NUMBER,
425 p_flex_desc IN VARCHAR2,
426 p_action_date IN DATE := NULL,
427 p_effective_date IN DATE,
428 p_expiration_date IN DATE,
429 p_acct_type IN VARCHAR2,
430 p_enabled_flag IN VARCHAR2
431 )
432 IS
433 l_api_name CONSTANT VARCHAR2(30) := 'Sync_FlexValue';
434 l_api_version CONSTANT NUMBER := 1.0;
435 BEGIN
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 IF (l_Debug_Level <= 2) THEN
439 itg_debug_pub.Add('ENTERING Sync_FlexValue' ,2);
440 END IF;
441
442 BEGIN
443 SAVEPOINT Sync_FlexValue_PVT;
444 ITG_Debug.setup(
445 p_reset => TRUE,
446 p_pkg_name => G_PKG_NAME,
447 p_proc_name => l_api_name);
448
449
450 IF (l_Debug_Level <= 1) THEN
451 itg_debug_pub.Add('SFV - Top of procedure.' ,1);
452 itg_debug_pub.Add('SFV - p_syncind'||p_syncind ,1);
453 END IF;
454
455 g_action := 'Flex-value sync';
456
457 IF UPPER(p_syncind) = 'A' THEN
458 Add_FlexValue(
459 x_return_status => x_return_status,
460 x_msg_count => x_msg_count,
461 x_msg_data => x_msg_data,
462 p_flex_value => p_flex_value,
463 p_vset_id => p_vset_id,
464 p_flex_desc => p_flex_desc,
465 p_creation_date => p_action_date,
466 p_effective_date => p_effective_date,
467 p_expiration_date => p_expiration_date,
468 p_acct_type => p_acct_type,
469 p_enabled_flag => p_enabled_flag);
470 ELSIF UPPER(p_syncind) = 'C' THEN
471 Change_FlexValue(
472 x_return_status => x_return_status,
473 x_msg_count => x_msg_count,
474 x_msg_data => x_msg_data,
475 p_flex_value => p_flex_value,
476 p_vset_id => p_vset_id,
477 p_flex_desc => p_flex_desc,
478 p_update_date => p_action_date,
479 p_effective_date => p_effective_date,
480 p_expiration_date => p_expiration_date,
481 p_enabled_flag => p_enabled_flag);
482 ELSE
483 g_action := 'Flex-parameters validation';
484 ITG_MSG.missing_element_value('SYNCIND', p_syncind);
485 RAISE FND_API.G_EXC_ERROR;
486 END IF;
487
488 COMMIT WORK;
489
490 IF (l_Debug_Level <= 2) THEN
491 itg_debug_pub.Add('SFV - Done. EXITING Sync_FlexValue' ,2);
492 END IF;
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495 ROLLBACK TO Sync_FlexValue_PVT;
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 ITG_msg.checked_error(g_action);
498 IF (l_Debug_Level <= 6) THEN
499 itg_debug_pub.Add('SFV - Done. EXITING Sync_FlexValue with ERROR' ,6);
500 END IF;
501
502 WHEN OTHERS THEN
503 ROLLBACK TO Sync_FlexValue_PVT;
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505 itg_debug.msg('Unexpected error (COA sync) - ' || substr(SQLERRM,1,255),true);
506 ITG_msg.unexpected_error(g_action);
507 IF (l_Debug_Level <= 6) THEN
508 itg_debug_pub.Add('SFV - Done. EXITING Sync_FlexValue with OTHER ERROR' ,6);
509 END IF;
510
511 END;
512 END Sync_FlexValue;
513
514 END ITG_SyncCOAInbound_PVT;