[Home] [Help]
PACKAGE BODY: APPS.GL_CONS_SEGMENT_ACTIONS_PKG
Source
1 PACKAGE BODY GL_CONS_SEGMENT_ACTIONS_PKG as
2 /* $Header: glicosab.pls 120.5 2005/05/05 01:05:50 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 if (X_Segment_Map_Id is NULL) then
34 OPEN C2;
35 FETCH C2 INTO X_Segment_Map_Id;
36 CLOSE C2;
37 end if;
38 INSERT INTO gl_cons_segment_map(
39 segment_map_id,
40 coa_mapping_id,
41 last_update_date,
42 last_updated_by,
43 to_value_set_id,
44 to_application_column_name,
45 segment_map_type,
46 creation_date,
47 created_by,
48 last_update_login,
49 from_value_set_id,
50 from_application_column_name,
51 single_value,
52 attribute1,
53 attribute2,
54 attribute3,
55 attribute4,
56 attribute5,
57 context,
58 parent_rollup_value
59 ) VALUES (
60 X_Segment_Map_Id,
61 X_Coa_Mapping_Id,
62 X_Last_Update_Date,
63 X_Last_Updated_By,
64 X_To_Value_Set_Id,
65 X_To_Application_Column_Name,
66 X_Segment_Map_Type,
67 X_Creation_Date,
68 X_Created_By,
69 X_Last_Update_Login,
70 X_From_Value_Set_Id,
71 X_From_Application_Column_Name,
72 X_Single_Value,
73 X_Attribute1,
74 X_Attribute2,
75 X_Attribute3,
76 X_Attribute4,
77 X_Attribute5,
78 X_Context,
79 X_Parent_Rollup_Value
80 );
81
82 OPEN C;
83 FETCH C INTO X_Rowid;
84 if (C%NOTFOUND) then
85 CLOSE C;
86 RAISE NO_DATA_FOUND;
87 end if;
88 CLOSE C;
89 END Insert_Row;
90
91 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
92 X_Segment_Map_Id NUMBER
93 ) IS
94 CURSOR C IS
95 SELECT *
96 FROM gl_cons_segment_map
97 WHERE rowid = X_Rowid
98 FOR UPDATE of Segment_Map_Id NOWAIT;
99 Recinfo C%ROWTYPE;
100 BEGIN
101 OPEN C;
102 FETCH C INTO Recinfo;
103 if (C%NOTFOUND) then
104 CLOSE C;
105 RAISE NO_DATA_FOUND;
106 end if;
107 CLOSE C;
108 if (
109 ( (Recinfo.segment_map_id = X_Segment_Map_Id)
110 OR ( (Recinfo.segment_map_id IS NULL)
111 AND (X_Segment_Map_Id IS NULL)))
112 ) then
113 return;
114 else
115 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
116 APP_EXCEPTION.RAISE_EXCEPTION;
117 end if;
118 END Lock_Row;
119
120 PROCEDURE Update_Row(X_Rowid VARCHAR2,
121 X_Segment_Map_Id NUMBER,
122 X_Coa_Mapping_Id NUMBER,
123 X_Last_Update_Date DATE,
124 X_Last_Updated_By NUMBER,
125 X_To_Value_Set_Id NUMBER,
126 X_To_Application_Column_Name VARCHAR2,
127 X_Segment_Map_Type VARCHAR2,
128 X_Last_Update_Login NUMBER,
129 X_From_Value_Set_Id NUMBER,
130 X_From_Application_Column_Name VARCHAR2,
131 X_Single_Value VARCHAR2,
132 X_attribute1 VARCHAR2,
133 X_attribute2 VARCHAR2,
134 X_attribute3 VARCHAR2,
135 X_attribute4 VARCHAR2,
136 X_attribute5 VARCHAR2,
137 X_context VARCHAR2,
138 X_parent_rollup_value VARCHAR2
139 ) IS
140 BEGIN
141
142 UPDATE gl_cons_segment_map
143 SET
144 segment_map_id = X_Segment_Map_Id,
145 coa_mapping_id = X_Coa_Mapping_Id,
146 last_update_date = X_Last_Update_Date,
147 last_updated_by = X_Last_Updated_By,
148 to_value_set_id = X_To_Value_Set_Id,
149 to_application_column_name = X_To_Application_Column_Name,
150 segment_map_type = X_Segment_Map_Type,
151 last_update_login = X_Last_Update_Login,
152 from_value_set_id = X_From_Value_Set_Id,
153 from_application_column_name = X_From_Application_Column_Name,
154 single_value = X_Single_Value,
155 attribute1 = X_Attribute1,
156 attribute2 = X_Attribute2,
157 attribute3 = X_Attribute3,
158 attribute4 = X_Attribute4,
159 attribute5 = X_Attribute5,
160 context = X_Context,
161 parent_rollup_value = X_Parent_Rollup_Value
162 WHERE rowid = X_rowid;
163
164 if (SQL%NOTFOUND) then
165 RAISE NO_DATA_FOUND;
166 end if;
167
168 END Update_Row;
169
170 PROCEDURE Delete_Row(X_Coa_Mapping_Id NUMBER, X_to_application_column_name VARCHAR2 ) IS
171 BEGIN
172
173 DELETE FROM GL_CONS_FLEX_HIERARCHIES
174 WHERE SEGMENT_MAP_ID IN ( SELECT segment_map_id
175 FROM GL_CONS_SEGMENT_MAP
176 WHERE coa_mapping_id = X_Coa_Mapping_id
177 AND to_application_column_name = X_to_application_column_name
178 );
179
180 DELETE FROM gl_cons_segment_map
181 WHERE coa_mapping_id = X_Coa_Mapping_Id
182 AND to_application_column_name = X_to_application_column_name;
183
184 --if (SQL%NOTFOUND) then
185 -- RAISE NO_DATA_FOUND;
186 --end if;
187 END Delete_Row;
188
189 PROCEDURE Check_Duplicate_Rules(X_Rowid VARCHAR2,
190 X_Single_Value VARCHAR2,
191 X_Parent_Rollup_Value VARCHAR2,
192 X_Coa_Mapping_Id NUMBER,
193 X_To_Application_Column_Name VARCHAR2,
194 X_From_Application_Column_Name VARCHAR2,
195 X_To_Value_Set_Id NUMBER,
196 X_From_Value_Set_Id NUMBER,
197 X_Segment_Map_Type VARCHAR2) IS
198 CURSOR DUPS1 IS
199 select 'x' from gl_cons_segment_map
200 where coa_mapping_id = X_Coa_Mapping_id
201 and to_application_column_name = X_To_Application_Column_Name
202 and to_value_set_id = X_To_Value_Set_Id
203 and segment_map_type in ('S', 'C')
204 and (rowid <> X_Rowid OR X_Rowid is NULL);
205
206 CURSOR DUPS2 IS
207 select 'x' from gl_cons_segment_map
208 where coa_mapping_id = X_Coa_Mapping_id
209 and to_application_column_name = X_To_Application_Column_Name
210 and to_value_set_id = X_To_Value_Set_Id
211 and segment_map_type NOT IN ('S', 'C')
212 and (rowid <> X_Rowid OR X_Rowid is NULL);
213
214 CURSOR DUPS3 IS
215 select 'x' from gl_cons_segment_map
216 where coa_mapping_id = X_Coa_Mapping_id
217 and to_application_column_name = X_To_Application_Column_Name
218 and to_value_set_id = X_To_Value_Set_Id
219 and single_value = X_Single_Value
220 and from_application_column_name = X_from_Application_Column_Name
221 and from_value_set_id = X_From_Value_Set_Id
222 and parent_rollup_value = X_Parent_Rollup_Value
223 and segment_map_type = 'P'
224 and (rowid <> X_Rowid OR X_Rowid is NULL);
225
226 ROWS1 VARCHAR2(1);
227 ROWS2 VARCHAR2(1);
228 ROWS3 VARCHAR2(1);
229
230 BEGIN
231 OPEN DUPS1;
232 FETCH DUPS1 into ROWS1;
233
234 IF (DUPS1%FOUND) THEN
235 CLOSE DUPS1;
236 fnd_message.set_name('SQLGL','GL_ONE_RULE_FOR_PARENT_SEG');
237 app_exception.raise_exception;
238 END IF;
239
240 CLOSE DUPS1;
241
242 IF ( X_Segment_Map_Type in ( 'S', 'C' ) ) THEN
243 OPEN DUPS2;
244 FETCH DUPS2 into ROWS2;
245 IF ( DUPS2%FOUND ) THEN
246 CLOSE DUPS2;
247 fnd_message.set_name('SQLGL','GL_ONE_RULE_FOR_PARENT_SEG');
248 app_exception.raise_exception;
249 END IF;
250 CLOSE DUPS2;
251 END IF;
252
253 IF ( X_Segment_Map_Type = 'P' ) THEN
254 OPEN DUPS3;
255 FETCH DUPS3 into ROWS3;
256 IF ( DUPS3%FOUND ) THEN
257 CLOSE DUPS3;
258 fnd_message.set_name('SQLGL','GL_NO_PARENT_ROLLUP_DUPLICATES');
259 app_exception.raise_exception;
260 END IF;
261 CLOSE DUPS3;
262 END IF;
263
264 END Check_Duplicate_Rules;
265
266 PROCEDURE Get_Validation_Type(X_To_Value_Set_Id NUMBER,
267 X_Validation_Type IN OUT NOCOPY VARCHAR2) IS
268
269 CURSOR V_TYPE IS
270 SELECT validation_type
271 FROM fnd_flex_value_sets
272 WHERE flex_value_set_id = X_To_Value_Set_Id;
273
274 BEGIN
275 OPEN V_TYPE;
276 FETCH V_TYPE INTO X_Validation_Type;
277 IF (V_TYPE%NOTFOUND) THEN
278 CLOSE V_TYPE;
279 fnd_message.set_name('SQLGL','GL_INVALID_VALUE_SET_ID');
280 fnd_message.set_token('VSID',to_char(X_To_Value_Set_Id));
281 app_exception.raise_exception;
282 END IF;
283 CLOSE V_TYPE;
284 END Get_Validation_Type;
285
286 PROCEDURE set_criteria (X_coa_mapping_id NUMBER,
287 X_to_chart_of_accounts_id NUMBER,
288 X_from_chart_of_accounts_id NUMBER) IS
289 BEGIN
290 GL_CONS_SEGMENT_ACTIONS_PKG.coa_mapping_id := X_coa_mapping_id;
291 GL_CONS_SEGMENT_ACTIONS_PKG.to_chart_of_accounts_id := X_to_chart_of_accounts_id;
292 GL_CONS_SEGMENT_ACTIONS_PKG.from_chart_of_accounts_id := X_from_chart_of_accounts_id;
293 END set_criteria;
294
295 FUNCTION Validate_From_Segment (X_from_value_set_id NUMBER,
296 X_to_value_set_id NUMBER) RETURN BOOLEAN IS
297 from_vs_max_size NUMBER;
298 to_vs_max_size NUMBER;
299 BEGIN
300 SELECT maximum_size
301 INTO from_vs_max_size
302 FROM FND_FLEX_VALUE_SETS
303 WHERE flex_value_set_id = X_from_value_set_id;
304
305 SELECT maximum_size
306 INTO to_vs_max_size
307 FROM FND_FLEX_VALUE_SETS
308 WHERE flex_value_set_id = X_to_value_set_id;
309
310 if (from_vs_max_size > to_vs_max_size) then
311 return (FALSE);
312 end if;
313
314 -- from_vs_max_size <= to_vs_max_size
315 return (TRUE);
316 END Validate_From_Segment;
317
318
319 --
320 -- PUBLIC FUNCTIONS
321 --
322 FUNCTION get_coa_mapping_id RETURN NUMBER IS
323 BEGIN
324 RETURN GL_CONS_SEGMENT_ACTIONS_PKG.coa_mapping_id;
325 END get_coa_mapping_id;
326
327 FUNCTION get_to_coa_id RETURN NUMBER IS
328 BEGIN
329 RETURN GL_CONS_SEGMENT_ACTIONS_PKG.to_chart_of_accounts_id;
330 END get_to_coa_id;
331
332 FUNCTION get_from_coa_id RETURN NUMBER IS
333 BEGIN
334 RETURN GL_CONS_SEGMENT_ACTIONS_PKG.from_chart_of_accounts_id;
335 END get_from_coa_id;
336
337 END GL_CONS_SEGMENT_ACTIONS_PKG;