1 PACKAGE BODY gl_coa_seg_val_imp_pkg AS
2 /* $Header: GLSVISPB.pls 120.5.12020000.2 2012/08/28 20:58:12 phmullap ship $ */
3 /***********************************************************************************************
4 Created By : Somnath Mukherjee
5 Date Created By: 01-AUG-2008
6 Purpose : This package has the 2 sub processes, which will be called from
7 Chart of Accounts Segment Values API.
8 process 1 : create_gl_coa_flex_values
9 Imports GL COA flex values (segment values)
10 process 2 : create_gl_coa_flex_values_nh
11 Imports Child ranges for the segment values.
12
13 Known limitations,enhancements,remarks:
14 Change History (in reverse chronological order)
15 Who When What
16 ********************************************************************************************** */
17
18 g_n_user_id fnd_flex_values.created_by%TYPE := NVL(fnd_global.user_id,-1); -- Stores the User Id
19 g_n_login_id fnd_flex_values.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
20 g_n_sysdate DATE := SYSDATE;
21
22 --PL/SQL table to store the unique flex value set ids.
23 TYPE flex_vl_set_id_tbl_type IS TABLE OF fnd_flex_values.flex_value_set_id%TYPE INDEX BY BINARY_INTEGER;
24 flex_vl_set_id_tab flex_vl_set_id_tbl_type;
25
26 --Cursor to verify that the Value set name passed is valid.
27 CURSOR c_fnd_flex_values ( cp_flex_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE) IS
28 SELECT ffvs.rowid,ffvs.*
29 FROM FND_FLEX_VALUE_SETS ffvs
30 WHERE FLEX_VALUE_SET_NAME = cp_flex_value_set_name;
31
32 l_cur_co c_fnd_flex_values%ROWTYPE;
33
34 FUNCTION isexists(p_flex_value_set_id IN fnd_flex_values.flex_value_set_id%TYPE,
35 p_tab_flex_value_set_id IN flex_vl_set_id_tbl_type) RETURN BOOLEAN AS
36 /***********************************************************************************************
37 Created By : sommukhe
38 Date Created By: 01-AUG-2008.
39 Purpose : This utility procedure is to check if a flex Value Set id exists in a pl/sql table
40
41 Known limitations,enhancements,remarks:
42 Change History (in reverse chronological order)
43 Who When What
44 ***********************************************************************************************/
45 BEGIN
46 FOR i in 1..p_tab_flex_value_set_id.count LOOP
47 IF p_flex_value_set_id = p_tab_flex_value_set_id(i) THEN
48 RETURN TRUE;
49 END IF;
50 END LOOP;
51 RETURN FALSE;
52 END isexists;
53
54 PROCEDURE set_msg(p_c_msg_name IN VARCHAR2,
55 p_c_token IN VARCHAR2
56 )AS
57 /***********************************************************************************************
58 Created By : Sommukhe
59 Date Created By: 01-AUG-2008
60 Purpose : This procedure sets the particular message in the message stack.
61 Based upon the input arguments this procedure does the following functions
62 -- if the p_c_msg_name is null then returns immediately
63
64 Known limitations,enhancements,remarks:
65 Change History (in reverse chronological order)
66 Who When What
67 ********************************************************************************************** */
68 l_n_count NUMBER;
69 BEGIN
70 -- If the message name is null, then return false
71 IF p_c_msg_name IS NULL THEN
72 RETURN;
73 END IF;
74
75 FND_MESSAGE.SET_NAME('GL',p_c_msg_name);
76 IF p_c_token IS NOT NULL THEN
77 FND_MESSAGE.SET_TOKEN('PARAM',p_c_token);
78 END IF;
79 FND_MSG_PUB.ADD;
80
81 END set_msg;
82
83 --Create process for fnd_flex_values
84 PROCEDURE create_gl_coa_flex_values(
85 p_gl_flex_values_tbl IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_tbl_type,
86 p_c_rec_status OUT NOCOPY VARCHAR2,
87 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL /* DRM: Added */
88
89 ) AS
90 /***********************************************************************************************
91 Created By : Sommukhe
92 Date Created By: 01-AUG-2008
93 Purpose : This procedure is a sub process to import records of s.
94
95 Known limitations,enhancements,remarks:
96 Change History (in reverse chronological order)
97 Who When What
98 SOMMUKHE 28-JAN-2009 Bug#8208641 Included manual updates to fnd_flex_values as suugested by ATG in 7668121/7528069
99 SOMMUKHE 10-Nov-2011 Bug#13249707 Included checks to avoid nullifying of the DFF data during update operation.
100 ********************************************************************************************** */
101 l_insert_update VARCHAR2(1);
102 v_message_name VARCHAR2(30);
103 v_compiled_value_attribute_s VARCHAR2(2000);
104 req_id NUMBER;
105 result BOOLEAN;
106 row_count NUMBER;
107 vsid NUMBER;
108 --cursor to fetch the existing data
109 CURSOR c_fnd_flex_val ( cp_flex_value_set_id fnd_flex_values.flex_value_set_id%TYPE,
110 cp_flex_value fnd_flex_values.flex_value%TYPE) IS
111 SELECT ffvs.rowid,ffvs.*
112 FROM fnd_flex_values ffvs
113 WHERE flex_value_set_id = cp_flex_value_set_id
114 AND flex_value = cp_flex_value;
115
116 rec_fnd_flex_val c_fnd_flex_val%ROWTYPE;
117
118 /* Private Procedures for create_gl_coa_flex_values */
119 PROCEDURE trim_values ( gl_coa_flex_values_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_rec_type ) AS
120 BEGIN
121
122 gl_coa_flex_values_rec.value_set_name := trim(gl_coa_flex_values_rec.value_set_name);
123 gl_coa_flex_values_rec.flex_value := trim(gl_coa_flex_values_rec.flex_value);
124 gl_coa_flex_values_rec.flex_desc := trim(gl_coa_flex_values_rec.flex_desc);
125 gl_coa_flex_values_rec.parent_flex_value := trim(gl_coa_flex_values_rec.parent_flex_value);
126 gl_coa_flex_values_rec.summary_flag := trim(gl_coa_flex_values_rec.summary_flag);
127 gl_coa_flex_values_rec.roll_up_group := trim(gl_coa_flex_values_rec.roll_up_group);
128 gl_coa_flex_values_rec.hierarchy_level := trim(gl_coa_flex_values_rec.hierarchy_level);
129 gl_coa_flex_values_rec.allow_budgeting := trim(gl_coa_flex_values_rec.allow_budgeting);
130 gl_coa_flex_values_rec.allow_posting := trim(gl_coa_flex_values_rec.allow_posting);
131 gl_coa_flex_values_rec.account_type := trim(gl_coa_flex_values_rec.account_type);
132 gl_coa_flex_values_rec.reconcile := trim(gl_coa_flex_values_rec.reconcile);
133 gl_coa_flex_values_rec.third_party_control_account := trim(gl_coa_flex_values_rec.third_party_control_account);
134 gl_coa_flex_values_rec.enabled_flag := trim(gl_coa_flex_values_rec.enabled_flag);
135 gl_coa_flex_values_rec.effective_from := gl_coa_flex_values_rec.effective_from;
136 gl_coa_flex_values_rec.effective_to := gl_coa_flex_values_rec.effective_to;
137
138 END trim_values;
139
140 --Check the validity of the Value set name and derive the Flex_value_set_id
141 PROCEDURE validate_derivations ( gl_coa_flex_values_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_rec_type ) AS
142 BEGIN
143 OPEN c_fnd_flex_values(gl_coa_flex_values_rec.value_set_name);
144 FETCH c_fnd_flex_values INTO l_cur_co;
145 IF c_fnd_flex_values%NOTFOUND THEN
146 CLOSE c_fnd_flex_values;
147 set_msg('GL_COA_SVI_INV_VSET', gl_coa_flex_values_rec.value_set_name);
148 gl_coa_flex_values_rec.status := 'E';
149 ELSE
150 CLOSE c_fnd_flex_values;
151 END IF;
152
153 END validate_derivations;
154
155 -- validate parameters passed.
156 PROCEDURE validate_parameters ( gl_coa_flex_values_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_rec_type ) AS
157 BEGIN
158
159 /* Check for Mandatory Parameters */
160 IF gl_coa_flex_values_rec.value_set_name IS NULL THEN
161 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'VALUE_SET_NAME');
162 gl_coa_flex_values_rec.status := 'E';
163 END IF;
164 IF gl_coa_flex_values_rec.flex_value IS NULL THEN
165 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'FLEX_VALUE');
166 gl_coa_flex_values_rec.status := 'E';
167 END IF;
168
169 END validate_parameters;
170
171
172 -- Check for Update. If the flex value passed is already existing then certain validations need to be performed.
173 FUNCTION check_insert_update ( gl_coa_flex_values_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_rec_type ) RETURN VARCHAR2 IS
174 E_RESOURCE_BUSY EXCEPTION;
175 PRAGMA EXCEPTION_INIT(E_RESOURCE_BUSY, -54);
176 BEGIN
177 OPEN c_fnd_flex_val(l_cur_co.flex_value_set_id,gl_coa_flex_values_rec.flex_value);
178 FETCH c_fnd_flex_val INTO rec_fnd_flex_val;
179 IF c_fnd_flex_val%NOTFOUND THEN
180 CLOSE c_fnd_flex_val;
181 RETURN 'I';
182 ELSE
183 CLOSE c_fnd_flex_val;
184 RETURN 'U';
185 END IF;
186 EXCEPTION
187 WHEN E_RESOURCE_BUSY THEN
188 CLOSE c_fnd_flex_values;
189 fnd_message.set_name( 'GL', 'GL_COA_SVI_REC_LOCK');
190 fnd_msg_pub.add;
191 gl_coa_flex_values_rec.status := 'E';
192
193 END check_insert_update;
194
195 -- Assign default values to the parameters passed.
196 PROCEDURE assign_defaults ( gl_coa_flex_values_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_rec_type, p_insert IN VARCHAR2) IS
197 -- Cursor to check if child ranges exist for the flex value passed
198 CURSOR c_fnd_flex_value_nh_exists(cp_flex_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE,
199 cp_parent_flex_value fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE ) IS
200 SELECT 'X'
201 FROM fnd_flex_value_norm_hierarchy
202 WHERE flex_value_set_id = cp_flex_value_set_id
203 AND parent_flex_value = cp_parent_flex_value;
204
205 rec_fnd_flex_value_nh_exists c_fnd_flex_value_nh_exists%ROWTYPE;
206 BEGIN
207 --Insert Operation
208 IF p_insert = 'I' THEN
209 --Default summary_flag to N
210 IF ( gl_coa_flex_values_rec.summary_flag IS NULL ) THEN
211 gl_coa_flex_values_rec.summary_flag := 'N';
212 END IF;
213
214 --Default enabled_flag to N
215 IF ( gl_coa_flex_values_rec.enabled_flag IS NULL ) THEN
216 gl_coa_flex_values_rec.enabled_flag := 'N';
217 END IF;
218 END IF;
219
220 --Update Operation
221 IF p_insert = 'U' THEN
222 --Default summary_flag to the db value
223 IF ( gl_coa_flex_values_rec.summary_flag IS NULL ) THEN
224 gl_coa_flex_values_rec.summary_flag := rec_fnd_flex_val.summary_flag;
225 END IF;
226
227 --If update is being performed and summary_flag is changed to 'N' then delete the children.
228 IF ( gl_coa_flex_values_rec.summary_flag = 'N' AND rec_fnd_flex_val.summary_flag = 'Y') THEN
229 OPEN c_fnd_flex_value_nh_exists(l_cur_co.flex_value_set_id,gl_coa_flex_values_rec.flex_value);
230 FETCH c_fnd_flex_value_nh_exists INTO rec_fnd_flex_value_nh_exists;
231 IF c_fnd_flex_value_nh_exists%NOTFOUND THEN
232 CLOSE c_fnd_flex_value_nh_exists;
233 ELSE
234 CLOSE c_fnd_flex_value_nh_exists;
235 DELETE FROM FND_FLEX_VALUE_NORM_HIERARCHY WHERE FLEX_VALUE_SET_ID = l_cur_co.flex_value_set_id AND PARENT_FLEX_VALUE = gl_coa_flex_values_rec.flex_value;
236 END IF;
237 END IF;
238
239 --Default enabled_flag to the db value
240 IF ( gl_coa_flex_values_rec.enabled_flag IS NULL ) THEN
241 gl_coa_flex_values_rec.enabled_flag := rec_fnd_flex_val.enabled_flag;
242 END IF;
243 END IF;
244
245
246 END assign_defaults;
247
248
249 -- Carry out business validations
250 PROCEDURE validate_flex_values ( gl_coa_flex_values_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_rec_type ) AS
251 -- Cursor to validate the Roll up group information
252 CURSOR c_roll_up_group ( cp_flex_value_set_id IN fnd_flex_hierarchies.flex_value_set_id%TYPE,
253 cp_hierarchy_code IN fnd_flex_hierarchies.hierarchy_code%TYPE) IS
254 SELECT'X'
255 FROM fnd_flex_hierarchies
256 WHERE FLEX_VALUE_SET_ID =cp_flex_value_set_id
257 AND HIERARCHY_CODE =cp_hierarchy_code;
258 rec_roll_up_group c_roll_up_group%ROWTYPE;
259
260 -- Cursor to validate the flex qualifiers and to form appropriate compiled_value_attribute
261 CURSOR c_flex_val_qual ( cp_flex_value_set_id IN fnd_flex_hierarchies.flex_value_set_id%TYPE,
262 cp_id_flex_code IN fnd_flex_validation_qualifiers.id_flex_code%TYPE,
263 cp_id_flex_application_id IN fnd_flex_validation_qualifiers.id_flex_application_id%TYPE) IS
264 SELECT segment_attribute_type,value_attribute_type
265 FROM fnd_flex_validation_qualifiers
266 WHERE id_flex_code = cp_id_flex_code
267 AND id_flex_application_id = cp_id_flex_application_id
268 AND flex_value_set_id = cp_flex_value_set_id
269 ORDER BY assignment_date, value_attribute_type;
270
271 rec_flex_val_qual c_flex_val_qual%ROWTYPE;
272
273 BEGIN
274
275 -- DRM Change: perform validations only if p_validation_level is FULL
276 IF p_validation_level = FND_API.g_valid_level_full
277 THEN
278
279 IF gl_coa_flex_values_rec.summary_flag IS NOT NULL AND gl_coa_flex_values_rec.summary_flag NOT IN ('N','Y') THEN
280 set_msg('GL_COA_SVI_Y_OR_N', 'SUMMARY_FLAG');
281 gl_coa_flex_values_rec.status := 'E';
282 END IF;
283
284 IF gl_coa_flex_values_rec.roll_up_group IS NOT NULL THEN
285 OPEN c_roll_up_group(l_cur_co.flex_value_set_id,gl_coa_flex_values_rec.roll_up_group);
286 FETCH c_roll_up_group INTO rec_roll_up_group;
287 IF c_fnd_flex_values%NOTFOUND THEN
288 CLOSE c_roll_up_group;
289 set_msg('GL_COA_SVI_INVALID_VALUE', 'ROLL_UP_GROUP');
290 gl_coa_flex_values_rec.status := 'E';
291 ELSE
292 CLOSE c_roll_up_group;
293 END IF;
294
295 END IF;
296
297 IF gl_coa_flex_values_rec.allow_budgeting IS NOT NULL AND gl_coa_flex_values_rec.allow_budgeting NOT IN ('N','Y') THEN
298 set_msg('GL_COA_SVI_Y_OR_N', 'ALLOW_BUDGETING');
299 gl_coa_flex_values_rec.status := 'E';
300 END IF;
301
302 IF gl_coa_flex_values_rec.allow_posting IS NOT NULL AND gl_coa_flex_values_rec.allow_posting NOT IN ('N','Y') THEN
303 set_msg('GL_COA_SVI_Y_OR_N', 'ALLOW_POSTING');
304 gl_coa_flex_values_rec.status := 'E';
305 END IF;
306
307 IF gl_coa_flex_values_rec.account_type IS NOT NULL AND gl_coa_flex_values_rec.account_type NOT IN ('A','L','R','E','O') THEN
308 fnd_message.set_name('GL','GL_COA_SVI_INV_AC_TYPE');
309 fnd_msg_pub.add;
310 gl_coa_flex_values_rec.status := 'E';
311 END IF;
312
313 IF gl_coa_flex_values_rec.reconcile IS NOT NULL AND gl_coa_flex_values_rec.reconcile NOT IN ('N','Y') THEN
314 set_msg('GL_COA_SVI_Y_OR_N', 'RECONCILE');
315 gl_coa_flex_values_rec.status := 'E';
316 END IF;
317
318 IF gl_coa_flex_values_rec.third_party_control_account IS NOT NULL AND gl_coa_flex_values_rec.third_party_control_account NOT IN ('N','Y') THEN
319 set_msg('GL_COA_SVI_Y_OR_N', 'THIRD_PARTY_CONTROL_ACCOUNT');
320 gl_coa_flex_values_rec.status := 'E';
321 END IF;
322
323 END IF; -- DRM Change: End of conditional validation
324
325 /* Validation for compiled value attributes*/
326 v_compiled_value_attribute_s := NULL; -- DRM: Added
327 FOR rec_c_flex_val_qual IN c_flex_val_qual(l_cur_co.flex_value_set_id,'GL#',101)
328 LOOP
329 IF rec_c_flex_val_qual.value_attribute_type = 'DETAIL_BUDGETING_ALLOWED' AND gl_coa_flex_values_rec.allow_budgeting IS NOT NULL THEN
330 -- DRM: Changed
331 -- v_compiled_value_attribute_s := gl_coa_flex_values_rec.allow_budgeting;
332 v_compiled_value_attribute_s := LTrim(v_compiled_value_attribute_s||FND_GLOBAL.newline||
333 gl_coa_flex_values_rec.allow_budgeting,FND_GLOBAL.newline);
334 END IF;
335
336 IF rec_c_flex_val_qual.value_attribute_type = 'DETAIL_POSTING_ALLOWED' AND gl_coa_flex_values_rec.allow_posting IS NOT NULL THEN
337 -- DRM: Changed
338 -- v_compiled_value_attribute_s := v_compiled_value_attribute_s||FND_GLOBAL.newline||gl_coa_flex_values_rec.allow_posting;
339 v_compiled_value_attribute_s := LTrim(v_compiled_value_attribute_s||FND_GLOBAL.newline||
340 gl_coa_flex_values_rec.allow_posting,FND_GLOBAL.newline);
341 END IF;
342
343 IF rec_c_flex_val_qual.value_attribute_type = 'GL_ACCOUNT_TYPE' AND gl_coa_flex_values_rec.account_type IS NOT NULL THEN
344 -- DRM: Changed
345 -- v_compiled_value_attribute_s := v_compiled_value_attribute_s||FND_GLOBAL.newline||gl_coa_flex_values_rec.account_type;
346 v_compiled_value_attribute_s := LTrim(v_compiled_value_attribute_s||FND_GLOBAL.newline||
347 gl_coa_flex_values_rec.account_type,FND_GLOBAL.newline);
348 END IF;
349
350 IF rec_c_flex_val_qual.value_attribute_type = 'RECONCILIATION FLAG' AND gl_coa_flex_values_rec.reconcile IS NOT NULL THEN
351 -- DRM: Changed
352 -- v_compiled_value_attribute_s := v_compiled_value_attribute_s||FND_GLOBAL.newline||gl_coa_flex_values_rec.reconcile;
353 v_compiled_value_attribute_s := LTrim(v_compiled_value_attribute_s||FND_GLOBAL.newline||
354 gl_coa_flex_values_rec.reconcile,FND_GLOBAL.newline);
355 END IF;
356
357 IF rec_c_flex_val_qual.value_attribute_type = 'GL_CONTROL_ACCOUNT' AND gl_coa_flex_values_rec.third_party_control_account IS NOT NULL THEN
358 -- DRM: Changed
359 --v_compiled_value_attribute_s := v_compiled_value_attribute_s||FND_GLOBAL.newline||gl_coa_flex_values_rec.third_party_control_account;
360 v_compiled_value_attribute_s := LTrim(v_compiled_value_attribute_s||FND_GLOBAL.newline||
361 gl_coa_flex_values_rec.third_party_control_account,FND_GLOBAL.newline);
362 END IF;
363 END LOOP;
364
365
366 END validate_flex_values;
367
368
369 /* Main Flex Values Sub Process */
370 BEGIN
371
372 v_compiled_value_attribute_s := NULL;
373 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
374 fnd_log.string( fnd_log.level_procedure, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.start_logging_for','Fnd Flex Values');
375 END IF;
376
377 p_c_rec_status := 'S';
378 FOR I in 1..p_gl_flex_values_tbl.LAST LOOP
379 IF p_gl_flex_values_tbl.EXISTS(I) THEN
380
381 p_gl_flex_values_tbl(I).status := 'S';
382 p_gl_flex_values_tbl(I).msg_from := fnd_msg_pub.count_msg;
383 trim_values(p_gl_flex_values_tbl(I) );
384
385 -- DRM Change: Added IF condition, fetch value set properties for the first value
386 -- and after that only when value-set changes
387 IF I=1 OR l_cur_co.flex_value_set_name = p_gl_flex_values_tbl(I).value_set_name
388 THEN
389 validate_derivations( p_gl_flex_values_tbl(I) );
390 END IF;
391
392 IF p_gl_flex_values_tbl(I).status = 'S' THEN
393 -- DRM Change: Conditional validation
394 IF p_validation_level = FND_API.g_valid_level_full THEN
395 validate_parameters(p_gl_flex_values_tbl(I));
396 END IF;
397
398 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
399 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Status_after_validate_parameters',
400 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
401 ||p_gl_flex_values_tbl(I).flex_value||' '||'Status:'||p_gl_flex_values_tbl(I).status );
402 END IF;
403 END IF;
404
405 --Find out whether it is insert/update of record
406 l_insert_update:='I';
407 IF p_gl_flex_values_tbl(I).status = 'S' THEN
408 l_insert_update:= check_insert_update(p_gl_flex_values_tbl(I));
409 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
410 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Insert_update',
411 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
412 ||p_gl_flex_values_tbl(I).flex_value||' '||'Insert_update:'||l_insert_update);
413 END IF;
414 END IF;
415
416 --Defaulting depending upon insert or update
417 IF p_gl_flex_values_tbl(I).status = 'S' THEN
418 assign_defaults(p_gl_flex_values_tbl(I),l_insert_update);
419 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
420 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Status_after_assign_defaults',
421 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
422 ||p_gl_flex_values_tbl(I).flex_value||' '||'Status:'||p_gl_flex_values_tbl(I).status );
423 END IF;
424 END IF;
425
426 /* Business Validations */
427 IF p_gl_flex_values_tbl(I).status = 'S' THEN
428 validate_flex_values( p_gl_flex_values_tbl(I) );
429 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
430 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Status_after_Business_Val',
431 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
432 ||p_gl_flex_values_tbl(I).flex_value||' '||'Status:'||p_gl_flex_values_tbl(I).status );
433 END IF;
434 END IF;
435
436 IF p_gl_flex_values_tbl(I).status = 'S' THEN
437 BEGIN
438 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
439 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Creation_values',
440 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
441 ||p_gl_flex_values_tbl(I).flex_value||' '||'enabled_flag:'||p_gl_flex_values_tbl(I).enabled_flag||' '||'summary_flag:'||
442 p_gl_flex_values_tbl(I).summary_flag||' '||'roll_up_group:'||p_gl_flex_values_tbl(I).roll_up_group
443 ||' '||'hierarchy_level:'||p_gl_flex_values_tbl(I).hierarchy_level
444 ||' '||'compiled_value_attribute_s:'||v_compiled_value_attribute_s
445 ||' '||'flex_desc:'||p_gl_flex_values_tbl(I).flex_desc);
446 END IF;
447
448 --introduced for the bug 13249707
449 IF l_insert_update = 'I' THEN
450 rec_fnd_flex_val.VALUE_CATEGORY := NULL;
451 rec_fnd_flex_val.ATTRIBUTE1 := NULL;
452 rec_fnd_flex_val.ATTRIBUTE2 := NULL;
453 rec_fnd_flex_val.ATTRIBUTE3 := NULL;
454 rec_fnd_flex_val.ATTRIBUTE4 := NULL;
455 rec_fnd_flex_val.ATTRIBUTE5 := NULL;
456 rec_fnd_flex_val.ATTRIBUTE6 := NULL;
457 rec_fnd_flex_val.ATTRIBUTE7 := NULL;
458 rec_fnd_flex_val.ATTRIBUTE8 := NULL;
459 rec_fnd_flex_val.ATTRIBUTE9 := NULL;
460 rec_fnd_flex_val.ATTRIBUTE10 := NULL;
461 rec_fnd_flex_val.ATTRIBUTE11 := NULL;
462 rec_fnd_flex_val.ATTRIBUTE12 := NULL;
463 rec_fnd_flex_val.ATTRIBUTE13 := NULL;
464 rec_fnd_flex_val.ATTRIBUTE14 := NULL;
465 rec_fnd_flex_val.ATTRIBUTE15 := NULL;
466 rec_fnd_flex_val.ATTRIBUTE16 := NULL;
467 rec_fnd_flex_val.ATTRIBUTE17 := NULL;
468 rec_fnd_flex_val.ATTRIBUTE18 := NULL;
469 rec_fnd_flex_val.ATTRIBUTE19 := NULL;
470 rec_fnd_flex_val.ATTRIBUTE20 := NULL;
471 rec_fnd_flex_val.ATTRIBUTE21 := NULL;
472 rec_fnd_flex_val.ATTRIBUTE22 := NULL;
473 rec_fnd_flex_val.ATTRIBUTE23 := NULL;
474 rec_fnd_flex_val.ATTRIBUTE24 := NULL;
475 rec_fnd_flex_val.ATTRIBUTE25 := NULL;
476 rec_fnd_flex_val.ATTRIBUTE26 := NULL;
477 rec_fnd_flex_val.ATTRIBUTE27 := NULL;
478 rec_fnd_flex_val.ATTRIBUTE28 := NULL;
479 rec_fnd_flex_val.ATTRIBUTE29 := NULL;
480 rec_fnd_flex_val.ATTRIBUTE30 := NULL;
481 rec_fnd_flex_val.ATTRIBUTE31 := NULL;
482 rec_fnd_flex_val.ATTRIBUTE32 := NULL;
483 rec_fnd_flex_val.ATTRIBUTE33 := NULL;
484 rec_fnd_flex_val.ATTRIBUTE34 := NULL;
485 rec_fnd_flex_val.ATTRIBUTE35 := NULL;
486 rec_fnd_flex_val.ATTRIBUTE36 := NULL;
487 rec_fnd_flex_val.ATTRIBUTE37 := NULL;
488 rec_fnd_flex_val.ATTRIBUTE38 := NULL;
489 rec_fnd_flex_val.ATTRIBUTE39 := NULL;
490 rec_fnd_flex_val.ATTRIBUTE40 := NULL;
491 rec_fnd_flex_val.ATTRIBUTE41 := NULL;
492 rec_fnd_flex_val.ATTRIBUTE42 := NULL;
493 rec_fnd_flex_val.ATTRIBUTE43 := NULL;
494 rec_fnd_flex_val.ATTRIBUTE44 := NULL;
495 rec_fnd_flex_val.ATTRIBUTE45 := NULL;
496 rec_fnd_flex_val.ATTRIBUTE46 := NULL;
497 rec_fnd_flex_val.ATTRIBUTE47 := NULL;
498 rec_fnd_flex_val.ATTRIBUTE48 := NULL;
499 rec_fnd_flex_val.ATTRIBUTE49 := NULL;
500 rec_fnd_flex_val.ATTRIBUTE50 := NULL;
501 END IF;
502
503
504 fnd_flex_loader_apis.up_value_set_value
505 (p_upload_phase => 'BEGIN',
506 p_upload_mode => NULL,
507 p_custom_mode => 'FORCE',
508 p_flex_value_set_name => p_gl_flex_values_tbl(I).value_set_name,
509 p_parent_flex_value_low => p_gl_flex_values_tbl(I).parent_flex_value,
510 p_flex_value => p_gl_flex_values_tbl(I).flex_value,
511 p_owner => NULL,
512 p_last_update_date => to_char(g_n_sysdate,'YYYY/MM/DD HH24:MI:SS'),
513 p_enabled_flag => p_gl_flex_values_tbl(I).enabled_flag,
514 p_summary_flag => p_gl_flex_values_tbl(I).summary_flag,
515 p_start_date_active => to_char(p_gl_flex_values_tbl(I).effective_from,'YYYY/MM/DD HH24:MI:SS'),
516 p_end_date_active => to_char(p_gl_flex_values_tbl(I).effective_to,'YYYY/MM/DD HH24:MI:SS'),
517 p_parent_flex_value_high => NULL,
518 p_rollup_flex_value_set_name => NULL,
519 p_rollup_hierarchy_code => p_gl_flex_values_tbl(I).roll_up_group,
520 p_hierarchy_level => p_gl_flex_values_tbl(I).hierarchy_level,
521 p_compiled_value_attributes => v_compiled_value_attribute_s,
522 p_value_category => rec_fnd_flex_val.value_category,
523 p_attribute1 => rec_fnd_flex_val.attribute1,
524 p_attribute2 => rec_fnd_flex_val.attribute2,
525 p_attribute3 => rec_fnd_flex_val.attribute3,
526 p_attribute4 => rec_fnd_flex_val.attribute4,
527 p_attribute5 => rec_fnd_flex_val.attribute5,
528 p_attribute6 => rec_fnd_flex_val.attribute6,
529 p_attribute7 => rec_fnd_flex_val.attribute7,
530 p_attribute8 => rec_fnd_flex_val.attribute8,
531 p_attribute9 => rec_fnd_flex_val.attribute9,
532 p_attribute10 => rec_fnd_flex_val.attribute10,
533 p_attribute11 => rec_fnd_flex_val.attribute11,
534 p_attribute12 => rec_fnd_flex_val.attribute12,
535 p_attribute13 => rec_fnd_flex_val.attribute13,
536 p_attribute14 => rec_fnd_flex_val.attribute14,
537 p_attribute15 => rec_fnd_flex_val.attribute15,
538 p_attribute16 => rec_fnd_flex_val.attribute16,
539 p_attribute17 => rec_fnd_flex_val.attribute17,
540 p_attribute18 => rec_fnd_flex_val.attribute18,
541 p_attribute19 => rec_fnd_flex_val.attribute19,
542 p_attribute20 => rec_fnd_flex_val.attribute20,
543 p_attribute21 => rec_fnd_flex_val.attribute21,
544 p_attribute22 => rec_fnd_flex_val.attribute22,
545 p_attribute23 => rec_fnd_flex_val.attribute23,
546 p_attribute24 => rec_fnd_flex_val.attribute24,
547 p_attribute25 => rec_fnd_flex_val.attribute25,
548 p_attribute26 => rec_fnd_flex_val.attribute26,
549 p_attribute27 => rec_fnd_flex_val.attribute27,
550 p_attribute28 => rec_fnd_flex_val.attribute28,
551 p_attribute29 => rec_fnd_flex_val.attribute29,
552 p_attribute30 => rec_fnd_flex_val.attribute30,
553 p_attribute31 => rec_fnd_flex_val.attribute31,
554 p_attribute32 => rec_fnd_flex_val.attribute32,
555 p_attribute33 => rec_fnd_flex_val.attribute33,
556 p_attribute34 => rec_fnd_flex_val.attribute34,
557 p_attribute35 => rec_fnd_flex_val.attribute35,
558 p_attribute36 => rec_fnd_flex_val.attribute36,
559 p_attribute37 => rec_fnd_flex_val.attribute37,
560 p_attribute38 => rec_fnd_flex_val.attribute38,
561 p_attribute39 => rec_fnd_flex_val.attribute39,
562 p_attribute40 => rec_fnd_flex_val.attribute40,
563 p_attribute41 => rec_fnd_flex_val.attribute41,
564 p_attribute42 => rec_fnd_flex_val.attribute42,
565 p_attribute43 => rec_fnd_flex_val.attribute43,
566 p_attribute44 => rec_fnd_flex_val.attribute44,
567 p_attribute45 => rec_fnd_flex_val.attribute45,
568 p_attribute46 => rec_fnd_flex_val.attribute46,
569 p_attribute47 => rec_fnd_flex_val.attribute47,
570 p_attribute48 => rec_fnd_flex_val.attribute48,
571 p_attribute49 => rec_fnd_flex_val.attribute49,
572 p_attribute50 => rec_fnd_flex_val.attribute50,
573 p_flex_value_meaning => NULL,
574 p_description => p_gl_flex_values_tbl(I).flex_desc);
575 ---As suggested by the Flex team in the bug 7668121 going ahead with the direct update on the LUD
576 ---As approved by Flex Dev team in the bug 13345598
577 IF l_insert_update = 'U' THEN
578 UPDATE fnd_flex_values
579 SET last_update_date = sysdate,
580 last_updated_by = g_n_user_id,
581 last_update_login = g_n_login_id
582 WHERE rowid = rec_fnd_flex_val.rowid ;
583 ELSE
584 UPDATE fnd_flex_values
585 SET last_update_date = sysdate ,
586 last_updated_by = g_n_user_id,
587 created_by = g_n_user_id,
588 creation_date = sysdate
589 WHERE flex_value_set_id = l_cur_co.flex_value_set_id
590 AND flex_value = p_gl_flex_values_tbl(I).flex_value;
591 END IF;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
596 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Error_in_flex_API',
597 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
598 ||p_gl_flex_values_tbl(I).flex_value||' '||'Error_Message:'||FND_MESSAGE.GET
599 ||' '||'Unhandled Exception :'||sqlerrm);
600 END IF;
601 fnd_message.set_name( 'GL', 'GL_COA_SVI_FLEX_UN_EX');
602 fnd_msg_pub.add;
603 p_gl_flex_values_tbl(I).status := 'E';
604 END;
605
606 END IF;--insert/update
607
608 IF p_gl_flex_values_tbl(I).status = 'S' THEN
609 p_gl_flex_values_tbl(I).msg_from := NULL;
610 p_gl_flex_values_tbl(I).msg_to := NULL;
611
612 IF flex_vl_set_id_tab.count = 0 THEN
613 flex_vl_set_id_tab(flex_vl_set_id_tab.count+1) :=l_cur_co.flex_value_set_id;
614 ELSE
615 IF NOT isExists(l_cur_co.flex_value_set_id,flex_vl_set_id_tab) THEN
616 flex_vl_set_id_tab(flex_vl_set_id_tab.count+1) :=l_cur_co.flex_value_set_id;
617 END IF;
618 END IF;
619
620 ELSE
621 IF p_c_rec_status = 'S' THEN
622 p_c_rec_status := p_gl_flex_values_tbl(I).status;
623 END IF;
624 p_gl_flex_values_tbl(I).msg_from := p_gl_flex_values_tbl(I).msg_from+1;
625 p_gl_flex_values_tbl(I).msg_to := fnd_msg_pub.count_msg;
626 IF p_gl_flex_values_tbl(I).status = 'E' THEN
627 NULL;--RETURN;
628 END IF;
629 END IF;
630
631 END IF;--exists
632 END LOOP;
633
634 /* Fork the Compile value set hierarchies Program for the Distinct Set of Value set ids having at least one successful create*/
635 FOR i in 1..flex_vl_set_id_tab.count LOOP
636 result := fnd_request.set_options('NO', 'NO', NULL, NULL);
637 req_id := fnd_request.submit_request(
638 'FND', 'FDFCHY', '', '', FALSE,
639 TO_CHAR(flex_vl_set_id_tab(i)), chr(0),
640 '', '', '', '', '', '', '', '',
641 '', '', '', '', '', '', '', '', '', '',
642 '', '', '', '', '', '', '', '', '', '',
643 '', '', '', '', '', '', '', '', '', '',
644 '', '', '', '', '', '', '', '', '', '',
645 '', '', '', '', '', '', '', '', '', '',
646 '', '', '', '', '', '', '', '', '', '',
647 '', '', '', '', '', '', '', '', '', '',
648 '', '', '', '', '', '', '', '', '', '',
649 '', '', '', '', '', '', '', '', '', '');
650 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
651 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Triggered_FDFCHY',
652 'Value Set id:'||TO_CHAR(flex_vl_set_id_tab(i))||' '||'Request id:'||req_id);
653 END IF;
654 IF (req_id = 0) THEN
655 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
656 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.Error_in_FDFCHY',
657 'Value Set Name:'||p_gl_flex_values_tbl(I).value_set_name||' '||'Parent Flex Value:'||p_gl_flex_values_tbl(I).parent_flex_value||' '||'Flex Value:'
658 ||p_gl_flex_values_tbl(I).flex_value||' '||'Value_set_id:'||TO_CHAR(flex_vl_set_id_tab(i))||' '||'Error_Message:'||
659 FND_MESSAGE.GET);
660 END IF;
661 fnd_message.set_name ( 'GL', 'GL_COA_SVI_COM_HIER_ERR' );
662 fnd_msg_pub.add;
663 END IF;
664 END LOOP;
665
666 flex_vl_set_id_tab.delete;
667
668 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
669 fnd_log.string( fnd_log.level_procedure, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values.status_after_import',p_c_rec_status);
670 END IF;
671 END create_gl_coa_flex_values;
672
673 PROCEDURE create_gl_coa_flex_values_nh(
674 p_gl_flex_values_nh_tbl IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_tbl_type,
675 p_c_rec_status OUT NOCOPY VARCHAR2,
676 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL /* DRM: Added */
677
678 ) AS
679 /***********************************************************************************************
680 Created By : Sommukhe
681 Date Created By: 01-AUG-2008
682 Purpose : This procedure is a sub process to import records of s.
683
684 Known limitations,enhancements,remarks:
685 Change History (in reverse chronological order)
686 Who When What
687 ********************************************************************************************** */
688 l_insert_update VARCHAR2(1);
689 v_message_name VARCHAR2(30);
690 req_id NUMBER;
691 result BOOLEAN;
692 row_count NUMBER;
693 vsid NUMBER;
694 l_nh_exists BOOLEAN;
695 flex_vl_set_id_del_tab flex_vl_set_id_tbl_type;
696
697 /* Private Procedures for create_gl_coa_flex_values_nh */
698 PROCEDURE trim_values ( gl_coa_flex_values_nh_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_rec_type ) AS
699 BEGIN
700 gl_coa_flex_values_nh_rec.value_set_name := trim(gl_coa_flex_values_nh_rec.value_set_name);
701 gl_coa_flex_values_nh_rec.parent_flex_value := trim(gl_coa_flex_values_nh_rec.parent_flex_value);
702 gl_coa_flex_values_nh_rec.range_attribute := trim(gl_coa_flex_values_nh_rec.range_attribute);
703 gl_coa_flex_values_nh_rec.child_flex_value_low := trim(gl_coa_flex_values_nh_rec.child_flex_value_low);
704 gl_coa_flex_values_nh_rec.child_flex_value_high := trim(gl_coa_flex_values_nh_rec.child_flex_value_high);
705
706 END trim_values;
707
708 PROCEDURE validate_derivations ( gl_coa_flex_values_nh_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_rec_type, p_nh_exists OUT BOOLEAN ) AS
709 --Cursor to check if child ranges already exist
710 CURSOR c_fnd_flex_value_nh_exists(cp_flex_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE
711 /*DRM Removed: ,cp_parent_flex_value fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE*/
712 ) IS
713 SELECT 'X'
714 FROM fnd_flex_value_norm_hierarchy
715 WHERE flex_value_set_id = cp_flex_value_set_id;
716 /*DRM Removed: AND parent_flex_value = cp_parent_flex_value;*/
717
718 rec_fnd_flex_value_nh_exists c_fnd_flex_value_nh_exists%ROWTYPE;
719
720 BEGIN
721 --Validate the Value set name
722 OPEN c_fnd_flex_values(gl_coa_flex_values_nh_rec.value_set_name);
723 FETCH c_fnd_flex_values INTO l_cur_co;
724 IF c_fnd_flex_values%NOTFOUND THEN
725 CLOSE c_fnd_flex_values;
726 fnd_message.set_name('GL','GL_COA_SVI_INVALID_VALUE');
727 fnd_msg_pub.add;
728 gl_coa_flex_values_nh_rec.status := 'E';
729 ELSE
730 CLOSE c_fnd_flex_values;
731 END IF;
732
733 OPEN c_fnd_flex_value_nh_exists(l_cur_co.flex_value_set_id);/* DRM removed: ,gl_coa_flex_values_nh_rec.parent_flex_value);*/
734 FETCH c_fnd_flex_value_nh_exists INTO rec_fnd_flex_value_nh_exists;
735 IF c_fnd_flex_value_nh_exists%NOTFOUND THEN
736 CLOSE c_fnd_flex_value_nh_exists;
737 p_nh_exists := FALSE;
738 ELSE
739 CLOSE c_fnd_flex_value_nh_exists;
740 p_nh_exists := TRUE;
741 END IF;
742
743 END validate_derivations;
744
745 -- validate parameters passed.
746 PROCEDURE validate_parameters ( gl_coa_flex_values_nh_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_rec_type ) AS
747 BEGIN
748
749 IF gl_coa_flex_values_nh_rec.value_set_name IS NULL THEN
750 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'VALUE_SET_NAME');
751 gl_coa_flex_values_nh_rec.status := 'E';
752 END IF;
753
754 IF gl_coa_flex_values_nh_rec.parent_flex_value IS NULL THEN
755 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'PARENT_FLEX_VALUE');
756 gl_coa_flex_values_nh_rec.status := 'E';
757 END IF;
758
759 IF gl_coa_flex_values_nh_rec.range_attribute IS NULL THEN
760 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'RANGE_ATTRIBUTE');
761 gl_coa_flex_values_nh_rec.status := 'E';
762 END IF;
763
764 IF gl_coa_flex_values_nh_rec.child_flex_value_low IS NULL THEN
765 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'CHILD_FLEX_VALUE_LOW');
766 gl_coa_flex_values_nh_rec.status := 'E';
767 END IF;
768
769 IF gl_coa_flex_values_nh_rec.child_flex_value_high IS NULL THEN
770 set_msg('GL_COA_SVI_SEG_VAL_MAND', 'CHILD_FLEX_VALUE_HIGH');
771 gl_coa_flex_values_nh_rec.status := 'E';
772 END IF;
773
774 END validate_parameters;
775
776 -- Validate Database Constraints
777 PROCEDURE validate_db_cons (gl_coa_flex_values_nh_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_rec_type ) AS
778 --Cursor to validate the parent flex value
779 CURSOR c_fnd_flex_value_exists(cp_flex_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE,
780 cp_parent_flex_value fnd_flex_value_norm_hierarchy.parent_flex_value%TYPE ) IS
781 SELECT summary_flag
782 FROM fnd_flex_values
783 WHERE flex_value_set_id = cp_flex_value_set_id
784 AND flex_value = cp_parent_flex_value;
785
786 rec_fnd_flex_value_exists c_fnd_flex_value_exists%ROWTYPE;
787 BEGIN
788 -- Parent Flex Value should be valid
789 OPEN c_fnd_flex_value_exists(l_cur_co.flex_value_set_id,gl_coa_flex_values_nh_rec.parent_flex_value);
790 FETCH c_fnd_flex_value_exists INTO rec_fnd_flex_value_exists;
791 IF c_fnd_flex_value_exists%NOTFOUND THEN
792 CLOSE c_fnd_flex_value_exists;
793 set_msg('GL_COA_SVI_INV_P_FLEX', gl_coa_flex_values_nh_rec.parent_flex_value);
794 gl_coa_flex_values_nh_rec.status := 'E';
795 ELSE
796 IF rec_fnd_flex_value_exists.summary_flag <> 'Y' THEN
797 set_msg('GL_COA_SVI_NO_SUM_FLG', gl_coa_flex_values_nh_rec.parent_flex_value);
798 gl_coa_flex_values_nh_rec.status := 'E';
799 END IF;
800 CLOSE c_fnd_flex_value_exists;
801 END IF;
802 END validate_db_cons;
803
804 -- Carry out business validations
805 PROCEDURE validate_nh ( gl_coa_flex_values_nh_rec IN OUT NOCOPY gl_coa_seg_val_imp_pub.gl_flex_values_nh_rec_type ) AS
806 BEGIN
807
808 --If Range Attribute should be in ('C','P')
809 IF gl_coa_flex_values_nh_rec.range_attribute NOT IN ('C','P') THEN
810 set_msg('GL_COA_SVI_INVALID_VALUE', 'RANGE_ATTRIBUTE');
811 gl_coa_flex_values_nh_rec.status := 'E';
812 END IF;
813
814 -- Child Flex Value Low should be less than Child Flex Value high
815 IF gl_coa_flex_values_nh_rec.child_flex_value_low > gl_coa_flex_values_nh_rec.child_flex_value_high THEN
816 fnd_message.set_name('GL','GL_COA_SVI_CFH_LESS_CFL');
817 fnd_msg_pub.add;
818 gl_coa_flex_values_nh_rec.status := 'E';
819 END IF;
820
821 --Loop detected if Range Attribute is 'P' and the parent flex value falls in between child_flex_value_low and child_flex_value_high
822 IF gl_coa_flex_values_nh_rec.range_attribute = 'P' THEN
823 IF gl_coa_flex_values_nh_rec.parent_flex_value BETWEEN gl_coa_flex_values_nh_rec.child_flex_value_low AND gl_coa_flex_values_nh_rec.child_flex_value_high THEN
824 set_msg('GL_COA_SVI_FLEX_HIER_LOOP', gl_coa_flex_values_nh_rec.parent_flex_value);
825 gl_coa_flex_values_nh_rec.status := 'E';
826 END IF;
827 END IF;
828
829 END validate_nh;
830
831
832
833 /* Main Child ranges Sub Process */
834 BEGIN
835 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
836 fnd_log.string( fnd_log.level_procedure, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values_nh.start_logging_for','Child ranges');
837 END IF;
838
839 p_c_rec_status := 'S';
840 l_nh_exists := NULL;
841 FOR I in 1..p_gl_flex_values_nh_tbl.LAST LOOP
842 IF p_gl_flex_values_nh_tbl.EXISTS(I) THEN
843
844 p_gl_flex_values_nh_tbl(I).status := 'S';
845 p_gl_flex_values_nh_tbl(I).msg_from := fnd_msg_pub.count_msg;
846 trim_values(p_gl_flex_values_nh_tbl(I) );
847 -- DRM Change: Added IF condition, fetch value set properties for the first value
848 -- and after that only when value-set changes
849 IF I=1 OR l_cur_co.flex_value_set_name = p_gl_flex_values_nh_tbl(I).value_set_name
850 THEN
851 validate_derivations(p_gl_flex_values_nh_tbl(I),l_nh_exists );
852 END IF;
853
854 --Check for the presence of the mandatory parameters
855 IF p_gl_flex_values_nh_tbl(I).status = 'S' THEN
856 -- DRM Change: Conditional validation
857 IF p_validation_level = FND_API.g_valid_level_full THEN
858 validate_parameters ( p_gl_flex_values_nh_tbl(I) );
859 END IF;
860 END IF;
861
862 --Parent Flex Value should be valid
863 IF p_gl_flex_values_nh_tbl(I).status = 'S' THEN
864 -- DRM Change: Conditional validation
865 IF p_validation_level = FND_API.g_valid_level_full THEN
866 validate_db_cons (p_gl_flex_values_nh_tbl(I));
867 END IF;
868 END IF;
869
870 --Business Validations for the fnd_flex_value_norm_hierarchy entities
871 IF p_gl_flex_values_nh_tbl(I).status = 'S' THEN
872 -- DRM Change: Conditional validation
873 IF p_validation_level = FND_API.g_valid_level_full THEN
874 validate_nh ( p_gl_flex_values_nh_tbl(I) );
875 END IF;
876 END IF;
877
878 /* Delete the already existing child ranges records*/
879 -- DRM Change: bug in deletion of hierarchies
880 --IF flex_vl_set_id_del_tab.count = 0
881 --THEN
882 -- IF l_nh_exists
883 -- THEN
884 -- flex_vl_set_id_del_tab(flex_vl_set_id_del_tab.count+1) := l_cur_co.flex_value_set_id;
885 -- DELETE fnd_flex_value_norm_hierarchy
886 -- WHERE flex_value_set_id = l_cur_co.flex_value_set_id
887 -- AND parent_flex_value = p_gl_flex_values_nh_tbl(I).parent_flex_value;
888 -- END IF;
889 --ELSE
890 IF NOT isExists(l_cur_co.flex_value_set_id,flex_vl_set_id_del_tab)
891 THEN
892 -- IF l_nh_exists
893 -- THEN
894 flex_vl_set_id_del_tab(flex_vl_set_id_del_tab.count+1) := l_cur_co.flex_value_set_id;
895 DELETE FROM fnd_flex_value_norm_hierarchy
896 WHERE flex_value_set_id =l_cur_co.flex_value_set_id;
897 -- AND parent_flex_value =p_gl_flex_values_nh_tbl(I).parent_flex_value;
898 -- END IF;
899 END IF;
900 --END IF;
901
902 IF p_gl_flex_values_nh_tbl(I).status = 'S' THEN
903 BEGIN
904 fnd_flex_loader_apis.up_vset_value_hierarchy
905 (p_upload_phase => 'BEGIN' ,
906 p_upload_mode => NULL,
907 p_custom_mode => 'FORCE',
908 p_flex_value_set_name => p_gl_flex_values_nh_tbl(I).value_set_name,
909 p_parent_flex_value => p_gl_flex_values_nh_tbl(I).parent_flex_value,
910 p_range_attribute => p_gl_flex_values_nh_tbl(I).range_attribute,
911 p_child_flex_value_low => p_gl_flex_values_nh_tbl(I).child_flex_value_low,
912 p_child_flex_value_high => p_gl_flex_values_nh_tbl(I).child_flex_value_high,
913 p_owner => NULL,
914 p_last_update_date => NULL,
915 p_start_date_active => NULL,
916 p_end_date_active => NULL);
917 ---As approved by Flex Dev team in the bug 13345598
918 UPDATE fnd_flex_value_norm_hierarchy
919 SET last_update_date = sysdate,
920 last_updated_by = g_n_user_id,
921 created_by = g_n_user_id,
922 creation_date = sysdate,
923 last_update_login = g_n_login_id
924 WHERE flex_value_set_id = l_cur_co.flex_value_set_id
925 AND parent_flex_value = p_gl_flex_values_nh_tbl(I).parent_flex_value
926 AND child_flex_value_low = p_gl_flex_values_nh_tbl(I).child_flex_value_low
927 AND child_flex_value_high = p_gl_flex_values_nh_tbl(I).child_flex_value_high
928 AND range_attribute= p_gl_flex_values_nh_tbl(I).range_attribute;
929 EXCEPTION
930 WHEN OTHERS THEN
931 fnd_message.set_name( 'GL', 'GL_COA_SVI_FLEX_UN_EX');
932 fnd_msg_pub.add;
933 p_gl_flex_values_nh_tbl(I).status := 'E';
934 END;
935 END IF;
936
937 IF p_gl_flex_values_nh_tbl(I).status = 'S' THEN
938 p_gl_flex_values_nh_tbl(I).msg_from := NULL;
939 p_gl_flex_values_nh_tbl(I).msg_to := NULL;
940
941 IF flex_vl_set_id_tab.count = 0 THEN
942 flex_vl_set_id_tab(flex_vl_set_id_tab.count+1) :=l_cur_co.flex_value_set_id;
943 ELSE
944 IF NOT isExists(l_cur_co.flex_value_set_id,flex_vl_set_id_tab) THEN
945 flex_vl_set_id_tab(flex_vl_set_id_tab.count+1) :=l_cur_co.flex_value_set_id;
946 END IF;
947 END IF;
948
949 ELSE
950 IF p_c_rec_status = 'S' THEN
951 p_c_rec_status := p_gl_flex_values_nh_tbl(I).status;
952 END IF;
953 p_gl_flex_values_nh_tbl(I).msg_from := p_gl_flex_values_nh_tbl(I).msg_from+1;
954 p_gl_flex_values_nh_tbl(I).msg_to := fnd_msg_pub.count_msg;
955 IF p_gl_flex_values_nh_tbl(I).status = 'E' THEN
956 NULL;--RETURN;
957 END IF;
958 END IF;
959
960 END IF;-- if exists
961
962
963 END LOOP;
964
965 /* Fork the Compile value set hierarchies Program for the Distinct Set of Value set ids having at least one successful create*/
966 FOR i in 1..flex_vl_set_id_tab.count LOOP
967 result := fnd_request.set_options('NO', 'NO', NULL, NULL);
968 req_id := fnd_request.submit_request(
969 'FND', 'FDFCHY', '', '', FALSE,
970 TO_CHAR(flex_vl_set_id_tab(i)), chr(0),
971 '', '', '', '', '', '', '', '',
972 '', '', '', '', '', '', '', '', '', '',
973 '', '', '', '', '', '', '', '', '', '',
974 '', '', '', '', '', '', '', '', '', '',
975 '', '', '', '', '', '', '', '', '', '',
976 '', '', '', '', '', '', '', '', '', '',
977 '', '', '', '', '', '', '', '', '', '',
978 '', '', '', '', '', '', '', '', '', '',
979 '', '', '', '', '', '', '', '', '', '',
980 '', '', '', '', '', '', '', '', '', '');
981 IF (req_id = 0) THEN
982 fnd_message.set_name ( 'GL', 'GL_COA_SVI_COM_HIER_ERR' );
983 fnd_msg_pub.add;
984 END IF;
985 END LOOP;
986
987 flex_vl_set_id_tab.delete;
988
989 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
990 fnd_log.string( fnd_log.level_procedure, 'gl.plsql.gl_coa_seg_val_imp_pkg.create_gl_coa_flex_values_nh.status_after_import',p_c_rec_status);
991 END IF;
992
993 END create_gl_coa_flex_values_nh;
994
995 END gl_coa_seg_val_imp_pkg;
[Home] [Help]