1 PACKAGE BODY GL_CONS_SEGMENT_MAP_PKG as
2 /* $Header: glicosrb.pls 120.7 2005/05/05 01:06:04 kvora ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Segment_Map_Id IN OUT NOCOPY NUMBER,
6 X_Coa_Mapping_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_To_Value_Set_Id NUMBER,
10 X_To_Application_Column_Name VARCHAR2,
11 X_Segment_Map_Type VARCHAR2,
12 X_Creation_Date DATE,
13 X_Created_By NUMBER,
14 X_Last_Update_Login NUMBER,
15 X_From_Value_Set_Id NUMBER,
16 X_From_Application_Column_Name VARCHAR2,
17 X_Single_Value VARCHAR2,
18 X_Attribute1 VARCHAR2,
19 X_Attribute2 VARCHAR2,
20 X_Attribute3 VARCHAR2,
21 X_Attribute4 VARCHAR2,
22 X_Attribute5 VARCHAR2,
23 X_Context VARCHAR2,
24 X_Parent_Rollup_Value VARCHAR2
25 ) IS
26 CURSOR C IS SELECT rowid FROM gl_cons_segment_map
27
28 WHERE segment_map_id = X_Segment_Map_Id;
29
30 CURSOR C2 IS SELECT gl_cons_segment_map_s.nextval FROM dual;
31 BEGIN
32
33 -- Issue check_duplicate_rules call from server instead of in client.
34
35 --Check_Duplicate_Rules(X_Rowid,
36 -- X_Single_Value,
37 -- X_Parent_Rollup_Value,
38 -- X_Coa_Mapping_Id,
39 -- X_To_Application_Column_Name,
40 -- X_From_Application_Column_Name,
41 -- X_To_Value_Set_Id,
42 -- X_From_Value_Set_Id,
43 -- X_Segment_Map_Type );
44
45 if (X_Segment_Map_Id is NULL) then
46 OPEN C2;
47 FETCH C2 INTO X_Segment_Map_Id;
48 CLOSE C2;
49 end if;
50 INSERT INTO gl_cons_segment_map(
51 segment_map_id,
52 coa_mapping_id,
53 last_update_date,
54 last_updated_by,
55 to_value_set_id,
56 to_application_column_name,
57 segment_map_type,
58 creation_date,
59 created_by,
60 last_update_login,
61 from_value_set_id,
62 from_application_column_name,
63 single_value,
64 attribute1,
65 attribute2,
66 attribute3,
67 attribute4,
68 attribute5,
69 context,
70 parent_rollup_value
71 ) VALUES (
72 X_Segment_Map_Id,
73 X_Coa_Mapping_Id,
74 X_Last_Update_Date,
75 X_Last_Updated_By,
76 X_To_Value_Set_Id,
77 X_To_Application_Column_Name,
78 X_Segment_Map_Type,
79 X_Creation_Date,
80 X_Created_By,
81 X_Last_Update_Login,
82 X_From_Value_Set_Id,
83 X_From_Application_Column_Name,
84 X_Single_Value,
85 X_Attribute1,
86 X_Attribute2,
87 X_Attribute3,
88 X_Attribute4,
89 X_Attribute5,
90 X_Context,
91 X_Parent_Rollup_Value
92 );
93
94 OPEN C;
95 FETCH C INTO X_Rowid;
96 if (C%NOTFOUND) then
97 CLOSE C;
98 RAISE NO_DATA_FOUND;
99 end if;
100 CLOSE C;
101 END Insert_Row;
102
103 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
104
105 X_Segment_Map_Id NUMBER,
106 X_Coa_Mapping_Id NUMBER,
107 X_To_Value_Set_Id NUMBER,
108 X_To_Application_Column_Name VARCHAR2,
109 X_Segment_Map_Type VARCHAR2,
110 X_From_Value_Set_Id NUMBER,
111 X_From_Application_Column_Name VARCHAR2,
112 X_Single_Value VARCHAR2,
113 X_Attribute1 VARCHAR2,
114 X_Attribute2 VARCHAR2,
115 X_Attribute3 VARCHAR2,
116 X_Attribute4 VARCHAR2,
117 X_Attribute5 VARCHAR2,
118 X_Context VARCHAR2,
119 X_Parent_Rollup_Value VARCHAR2
120 ) IS
121 CURSOR C IS
122 SELECT *
123 FROM gl_cons_segment_map
124 WHERE rowid = X_Rowid
125 FOR UPDATE of Segment_Map_Id NOWAIT;
126 Recinfo C%ROWTYPE;
127 BEGIN
128 OPEN C;
129 FETCH C INTO Recinfo;
130 if (C%NOTFOUND) then
131 CLOSE C;
132 RAISE NO_DATA_FOUND;
133 end if;
134 CLOSE C;
135 if (
136 ( (Recinfo.segment_map_id = X_Segment_Map_Id)
137 OR ( (Recinfo.segment_map_id IS NULL)
138 AND (X_Segment_Map_Id IS NULL)))
139 AND ( (Recinfo.coa_mapping_id = X_Coa_Mapping_Id)
140 OR ( (Recinfo.coa_mapping_id IS NULL)
141 AND (X_Coa_Mapping_Id IS NULL)))
142 AND ( (Recinfo.to_value_set_id = X_To_Value_Set_Id)
143 OR ( (Recinfo.to_value_set_id IS NULL)
144 AND (X_To_Value_Set_Id IS NULL)))
145 AND ( (Recinfo.to_application_column_name = X_To_Application_Column_Name)
146 OR ( (Recinfo.to_application_column_name IS NULL)
147 AND (X_To_Application_Column_Name IS NULL)))
148 AND ( (Recinfo.segment_map_type = X_Segment_Map_Type)
149 OR ( (Recinfo.segment_map_type IS NULL)
150 AND (X_Segment_Map_Type IS NULL)))
151 AND ( (Recinfo.from_value_set_id = X_From_Value_Set_Id)
152 OR ( (Recinfo.from_value_set_id IS NULL)
153 AND (X_From_Value_Set_Id IS NULL)))
154 AND ( (Recinfo.from_application_column_name = X_From_Application_Column_Name)
155 OR ( (Recinfo.from_application_column_name IS NULL)
156 AND (X_From_Application_Column_Name IS NULL)))
157 AND ( (Recinfo.single_value = X_Single_Value)
158 OR ( (Recinfo.single_value IS NULL)
159 AND (X_Single_Value IS NULL)))
160 AND ( (Recinfo.attribute1 = X_Attribute1)
161 OR ( (Recinfo.attribute1 IS NULL)
162 AND (X_Attribute1 IS NULL)))
163 AND ( (Recinfo.attribute2 = X_Attribute2)
164 OR ( (Recinfo.attribute2 IS NULL)
165 AND (X_Attribute2 IS NULL)))
166 AND ( (Recinfo.attribute3 = X_Attribute3)
167 OR ( (Recinfo.attribute3 IS NULL)
168 AND (X_Attribute3 IS NULL)))
169 AND ( (Recinfo.attribute4 = X_Attribute4)
170 OR ( (Recinfo.attribute4 IS NULL)
171 AND (X_Attribute4 IS NULL)))
172 AND ( (Recinfo.attribute5 = X_Attribute5)
173 OR ( (Recinfo.attribute5 IS NULL)
174 AND (X_Attribute5 IS NULL)))
175 AND ( (Recinfo.context = X_Context)
176 OR ( (Recinfo.context IS NULL)
177 AND (X_Context IS NULL)))
178 AND ( (Recinfo.context = X_Parent_Rollup_Value)
179 OR ( (Recinfo.context IS NULL)
180 AND (X_Context IS NULL)))
181 ) then
182 return;
183 else
184 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 end if;
187 END Lock_Row;
188
189 PROCEDURE Update_Row(X_Rowid VARCHAR2,
190 X_Segment_Map_Id NUMBER,
191 X_Coa_Mapping_Id NUMBER,
192 X_Last_Update_Date DATE,
193 X_Last_Updated_By NUMBER,
194 X_To_Value_Set_Id NUMBER,
195 X_To_Application_Column_Name VARCHAR2,
196 X_Segment_Map_Type VARCHAR2,
197 X_Last_Update_Login NUMBER,
198 X_From_Value_Set_Id NUMBER,
199 X_From_Application_Column_Name VARCHAR2,
200 X_Single_Value VARCHAR2,
201 X_Attribute1 VARCHAR2,
202 X_Attribute2 VARCHAR2,
203 X_Attribute3 VARCHAR2,
204 X_Attribute4 VARCHAR2,
205 X_Attribute5 VARCHAR2,
206 X_Context VARCHAR2,
207 X_Parent_Rollup_Value VARCHAR2
208 ) IS
209 BEGIN
210
211
212 -- Issue check_duplicate_rules call from server instead of in client.
213
214 --Check_Duplicate_Rules(X_Rowid,
215 -- X_Single_Value,
216 -- X_Parent_Rollup_Value,
217 -- X_Coa_Mapping_Id,
218 -- X_To_Application_Column_Name,
219 -- X_From_Application_Column_Name,
220 -- X_To_Value_Set_Id,
221 -- X_From_Value_Set_Id,
222 -- X_Segment_Map_Type );
223
224 -- The following delete statement deletes orphaned detail rows
225 -- from gl_cons_flex_hierarchies table - This is introduced here
226 -- because the Consolidation program insert row into
227 -- GL_CONS_FLEX_HIERARCHIES table, and if the user changes the
228 -- segment rule type from Parent Rollup to Detail Rollup, the
229 -- corresponding rows in gl_cons_flex_hierarchies are hanging
230 -- loose. Hence the delete.
231 -- IF ( X_Segment_Rule_Changed = 'Y' ) THEN
232 -- DELETE FROM GL_CONS_FLEX_HIERARCHIES
233 -- WHERE segment_map_id = X_Segment_Map_Id;
234 -- END IF;
235
236 UPDATE gl_cons_segment_map
237 SET
238
239 segment_map_id = X_Segment_Map_Id,
240 coa_mapping_id = X_Coa_Mapping_Id,
241 last_update_date = X_Last_Update_Date,
242 last_updated_by = X_Last_Updated_By,
243 to_value_set_id = X_To_Value_Set_Id,
244 to_application_column_name = X_To_Application_Column_Name,
245 segment_map_type = X_Segment_Map_Type,
246 last_update_login = X_Last_Update_Login,
247 from_value_set_id = X_From_Value_Set_Id,
248 from_application_column_name = X_From_Application_Column_Name,
249 single_value = X_Single_Value,
250 attribute1 = X_Attribute1,
251 attribute2 = X_Attribute2,
252 attribute3 = X_Attribute3,
253 attribute4 = X_Attribute4,
254 attribute5 = X_Attribute5,
255 context = X_Context,
256 parent_rollup_value = X_Parent_Rollup_Value
257 WHERE rowid = X_rowid;
258
259 if (SQL%NOTFOUND) then
260 RAISE NO_DATA_FOUND;
261 end if;
262
263 END Update_Row;
264
265 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Segment_Map_Id NUMBER ) IS
266 BEGIN
267
268 --Previously from Pre-Delete
269 DELETE FROM GL_CONS_FLEX_HIERARCHIES
270 WHERE SEGMENT_MAP_ID = X_Segment_Map_Id;
271
272
273 DELETE FROM gl_cons_segment_map
274 WHERE rowid = X_Rowid;
275
276 if (SQL%NOTFOUND) then
277 RAISE NO_DATA_FOUND;
278 end if;
279 END Delete_Row;
280
281 FUNCTION Check_Duplicate_Rules(X_Rowid VARCHAR2,
282 X_Single_Value VARCHAR2,
283 X_Parent_Rollup_Value VARCHAR2,
284 X_Coa_Mapping_Id NUMBER,
285 X_To_Application_Column_Name VARCHAR2,
286 X_From_Application_Column_Name VARCHAR2,
287 X_To_Value_Set_Id NUMBER,
288 X_From_Value_Set_Id NUMBER,
289 X_Segment_Map_Type VARCHAR2)
290 RETURN NUMBER IS
291 CURSOR DUPS1 IS
292 select 'x' from gl_cons_segment_map
293 where coa_mapping_id = X_Coa_Mapping_Id
294 and to_application_column_name = X_To_Application_Column_Name
295 and to_value_set_id = X_To_Value_Set_Id
296 and segment_map_type in ('S', 'C')
297 and (rowid <> X_Rowid OR X_Rowid is NULL);
298
299 CURSOR DUPS2 IS
300 select 'x' from gl_cons_segment_map
301 where coa_mapping_id = X_Coa_Mapping_id
302 and to_application_column_name = X_To_Application_Column_Name
303 and to_value_set_id = X_To_Value_Set_Id
304 and segment_map_type NOT IN ('S', 'C')
305 and (rowid <> X_Rowid OR X_Rowid is NULL);
306
307 -- the current detail parent rules used (fvh_curr) and the new one to be added
308 -- in (fvh_new) are checked for overlaps. Also, the new detail parent rule is
309 -- checked against detail ranges for overlaps in the second part of the union.
310 -- This only cathes overlaps with the same target specified.
311 CURSOR DUPS3 IS
312 select 'x'
313 from gl_cons_segment_map csm,
314 fnd_flex_value_hierarchies fvh_curr,
315 fnd_flex_value_hierarchies fvh_new
316 where coa_mapping_id = X_Coa_Mapping_id
317 and csm.to_application_column_name = X_To_Application_Column_Name
318 and csm.to_value_set_id = X_To_Value_Set_Id
319 and single_value = X_Single_Value
320 and csm.from_application_column_name = X_from_Application_Column_Name
321 and csm.from_value_set_id = X_From_Value_Set_Id
322 and csm.segment_map_type = 'P'
323 and fvh_curr.flex_value_set_id = X_From_Value_Set_Id
324 and fvh_new.flex_value_set_id = X_From_Value_Set_Id
325 and fvh_curr.parent_flex_value = csm.parent_rollup_value
326 and fvh_new.parent_flex_value = X_Parent_Rollup_Value
327 and ((fvh_new.child_flex_value_low between
328 fvh_curr.child_flex_value_low and fvh_curr.child_flex_value_high)
329 OR
330 (fvh_new.child_flex_value_high between
331 fvh_curr.child_flex_value_low and fvh_curr.child_flex_value_high)
332 OR
333 (fvh_curr.child_flex_value_low between
334 fvh_new.child_flex_value_low and fvh_new.child_flex_value_high)
335 OR
336 (fvh_curr.child_flex_value_high between
337 fvh_new.child_flex_value_low and fvh_new.child_flex_value_high))
338 and (csm.rowid <> X_Rowid OR X_Rowid is NULL)
339 UNION
340 select 'x'
341 from gl_cons_flex_hierarchies cfh,
342 gl_cons_segment_map csm,
343 fnd_flex_value_hierarchies fvh
344 where csm.segment_map_id = cfh.segment_map_id
345 and csm.coa_mapping_id = X_Coa_Mapping_id
346 and single_value = X_Single_Value
347 and csm.to_application_column_name = X_To_Application_Column_Name
348 and csm.from_application_column_name = X_From_Application_Column_Name
349 and csm.to_value_set_id = X_To_Value_Set_Id
350 and csm.from_value_set_id = X_From_Value_Set_Id
351 and csm.segment_map_type = 'R'
352 and fvh.flex_value_set_id = X_From_Value_Set_Id
353 and fvh.parent_flex_value = X_Parent_Rollup_Value
354 and ((cfh.child_flex_value_low between
355 fvh.child_flex_value_low and fvh.child_flex_value_high)
356 OR
357 (cfh.child_flex_value_high between
358 fvh.child_flex_value_low and fvh.child_flex_value_high)
359 OR
360 (fvh.child_flex_value_low between
361 cfh.child_flex_value_low and cfh.child_flex_value_high)
362 OR
363 (fvh.child_flex_value_high between
364 cfh.child_flex_value_low and cfh.child_flex_value_high))
365 ;
366
367 -- the current detail parent rules used (fvh_curr) and the new one to be added
368 -- in (fvh_new) are checked for overlaps. Also, the new detail parent rule is
369 -- checked against detail ranges for overlaps in the second part of the union.
370 -- This will catch overlaps that go to separate targets.
371 CURSOR DUPS4 IS
372 select 'x'
373 from gl_cons_segment_map csm,
374 fnd_flex_value_hierarchies fvh_curr,
375 fnd_flex_value_hierarchies fvh_new
376 where coa_mapping_id = X_Coa_Mapping_id
377 and csm.to_application_column_name = X_To_Application_Column_Name
378 and csm.to_value_set_id = X_To_Value_Set_Id
379 and csm.from_application_column_name = X_from_Application_Column_Name
380 and csm.from_value_set_id = X_From_Value_Set_Id
381 and csm.segment_map_type = 'P'
382 and fvh_curr.flex_value_set_id = X_From_Value_Set_Id
383 and fvh_new.flex_value_set_id = X_From_Value_Set_Id
384 and fvh_curr.parent_flex_value = csm.parent_rollup_value
385 and fvh_new.parent_flex_value = X_Parent_Rollup_Value
386 and ((fvh_new.child_flex_value_low between
387 fvh_curr.child_flex_value_low and fvh_curr.child_flex_value_high)
388 OR
389 (fvh_new.child_flex_value_high between
390 fvh_curr.child_flex_value_low and fvh_curr.child_flex_value_high)
391 OR
392 (fvh_curr.child_flex_value_low between
393 fvh_new.child_flex_value_low and fvh_new.child_flex_value_high)
394 OR
395 (fvh_curr.child_flex_value_high between
396 fvh_new.child_flex_value_low and fvh_new.child_flex_value_high))
397 and (csm.rowid <> X_Rowid OR X_Rowid is NULL)
398 UNION
399 select 'x'
400 from gl_cons_flex_hierarchies cfh,
401 gl_cons_segment_map csm,
402 fnd_flex_value_hierarchies fvh
403 where csm.segment_map_id = cfh.segment_map_id
404 and csm.coa_mapping_id = X_Coa_Mapping_id
405 and csm.to_application_column_name = X_To_Application_Column_Name
406 and csm.from_application_column_name = X_From_Application_Column_Name
407 and csm.to_value_set_id = X_To_Value_Set_Id
408 and csm.from_value_set_id = X_From_Value_Set_Id
409 and csm.segment_map_type = 'R'
410 and fvh.flex_value_set_id = X_From_Value_Set_Id
411 and fvh.parent_flex_value = X_Parent_Rollup_Value
412 and ((cfh.child_flex_value_low between
413 fvh.child_flex_value_low and fvh.child_flex_value_high)
414 OR
415 (cfh.child_flex_value_high between
416 fvh.child_flex_value_low and fvh.child_flex_value_high)
417 OR
418 (fvh.child_flex_value_low between
419 cfh.child_flex_value_low and cfh.child_flex_value_high)
420 OR
421 (fvh.child_flex_value_high between
422 cfh.child_flex_value_low and cfh.child_flex_value_high))
423 ;
424
425 ROWS1 VARCHAR2(1);
426 ROWS2 VARCHAR2(1);
427 ROWS3 VARCHAR2(1);
428 ROWS4 VARCHAR2(1);
429
430 BEGIN
431 OPEN DUPS1;
432 FETCH DUPS1 into ROWS1;
433
434 IF (DUPS1%FOUND) THEN
435 CLOSE DUPS1;
436 fnd_message.set_name('SQLGL','GL_ONE_RULE_FOR_PARENT_SEG');
437 app_exception.raise_exception;
438 END IF;
439
440 CLOSE DUPS1;
441
442 IF ( X_Segment_Map_Type in ( 'S', 'C' ) ) THEN
443 OPEN DUPS2;
444 FETCH DUPS2 into ROWS2;
445 IF ( DUPS2%FOUND ) THEN
446 CLOSE DUPS2;
447 fnd_message.set_name('SQLGL','GL_ONE_RULE_FOR_PARENT_SEG');
448 app_exception.raise_exception;
449 END IF;
450 CLOSE DUPS2;
451 END IF;
452
453 IF ( X_Segment_Map_Type = 'P' ) THEN
454 OPEN DUPS3;
455 FETCH DUPS3 into ROWS3;
456 IF ( DUPS3%FOUND ) THEN
457 CLOSE DUPS3;
458 fnd_message.set_name('SQLGL','GL_OVERLAPPING_ROLLUP_RANGES');
459 app_exception.raise_exception;
460 END IF;
461 CLOSE DUPS3;
462
463 OPEN DUPS4;
464 FETCH DUPS4 into ROWS4;
465 IF ( DUPS4%FOUND ) THEN
466 CLOSE DUPS4;
467 return(1);
468 END IF;
469 END IF;
470
471 return(0);
472 END Check_Duplicate_Rules;
473
474 PROCEDURE Get_Validation_Type(X_To_Value_Set_Id NUMBER,
475 X_Validation_Type IN OUT NOCOPY VARCHAR2) IS
476
477 CURSOR V_TYPE IS
478 SELECT validation_type
479 FROM fnd_flex_value_sets
480 WHERE flex_value_set_id = X_To_Value_Set_Id;
481
482 BEGIN
483 OPEN V_TYPE;
484 FETCH V_TYPE INTO X_Validation_Type;
485 IF (V_TYPE%NOTFOUND) THEN
486 CLOSE V_TYPE;
487 fnd_message.set_name('SQLGL','GL_INVALID_VALUE_SET_ID');
488 fnd_message.set_token('VSID',to_char(X_To_Value_Set_Id));
489 app_exception.raise_exception;
490 END IF;
491 CLOSE V_TYPE;
492 END Get_Validation_Type;
493
494 PROCEDURE Check_Any_Parent_Rules(X_Coa_Mapping_Id IN OUT NOCOPY NUMBER,
495 X_Parent_Rules_Present IN OUT NOCOPY VARCHAR2) IS
496
497 CURSOR X_TYPE IS
498 SELECT 'Y'
499 FROM GL_CONS_SEGMENT_MAP
500 WHERE coa_mapping_id = X_Coa_Mapping_Id
501 AND segment_map_type IN ( 'U', 'V' );
502
503 BEGIN
504 OPEN X_TYPE;
505 X_Parent_Rules_Present := 'N';
506 FETCH X_TYPE INTO X_Parent_Rules_Present;
507 IF (X_TYPE%FOUND) THEN
508 X_Parent_Rules_Present := 'Y';
509 CLOSE X_TYPE;
510 ELSE
511 X_Parent_Rules_Present := 'N';
512 CLOSE X_TYPE;
513 END IF;
514 END Check_Any_Parent_Rules;
515
516 END GL_CONS_SEGMENT_MAP_PKG;
[Home] [Help]