[Home] [Help]
PACKAGE BODY: APPS.FA_PERIOD_MAPS_PKG
Source
1 PACKAGE BODY FA_PERIOD_MAPS_PKG as
2 /* $Header: faxipdmb.pls 120.8 2009/04/10 07:48:50 deemitta ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_QUARTER in NUMBER,
7 X_QTR_FIRST_PERIOD in NUMBER,
8 X_QTR_LAST_PERIOD in NUMBER,
9 X_YEAR_FIRST_PERIOD in NUMBER,
10 X_YEAR_LAST_PERIOD in NUMBER,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
17
18 CURSOR C is SELECT rowid FROM fa_period_maps
19 WHERE nvl (year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
20
21 begin
22
23 INSERT INTO fa_period_maps (
24 quarter,
25 qtr_first_period,
26 qtr_last_period,
27 year_first_period,
28 year_last_period,
29 created_by,
30 creation_date,
31 last_updated_by,
32 last_update_date,
33 last_update_login
34 ) VALUES (
35 X_Quarter,
36 X_Qtr_First_Period,
37 X_Qtr_Last_Period,
38 X_Year_First_Period,
39 X_Year_Last_Period,
40 X_Created_By,
41 X_Creation_Date,
42 X_Last_Updated_By,
43 X_Last_Update_Date,
44 X_Last_Update_Login
45 );
46
47 OPEN C;
48 FETCH C INTO X_Rowid;
49 if (C%NOTFOUND) then
50 CLOSE C;
51 Raise NO_DATA_FOUND;
52 end if;
53 CLOSE C;
54
55 exception
56 when others then
57 fa_srvr_msg.add_sql_error(
58 calling_fn => 'fa_period_maps_pkg.insert_row', p_log_level_rec => p_log_level_rec);
59 raise;
60
61 end INSERT_ROW;
62
63 procedure LOCK_ROW (
64 X_QUARTER in NUMBER,
65 X_QTR_FIRST_PERIOD in NUMBER,
66 X_QTR_LAST_PERIOD in NUMBER,
67 X_YEAR_FIRST_PERIOD in NUMBER,
68 X_YEAR_LAST_PERIOD in NUMBER
69 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
70 CURSOR C IS
71 SELECT quarter,
72 qtr_first_period,
73 qtr_last_period,
74 year_first_period,
75 year_last_period
76 FROM fa_period_maps
77 WHERE nvl (year_last_period, -9999) = nvl (X_Year_Last_Period, -9999)
78 FOR UPDATE of year_last_period NOWAIT;
79 Recinfo C%ROWTYPE;
80
81 begin
82
83 OPEN C;
84 FETCH C INTO Recinfo;
85 if (C%NOTFOUND) then
86 CLOSE C;
87 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
88 APP_EXCEPTION.Raise_Exception;
89 end if;
90 CLOSE C;
91 if (
92 ((Recinfo.Quarter = X_Quarter)
93 OR ((Recinfo.Quarter IS NULL)
94 AND (X_Quarter IS NULL)))
95 AND ((Recinfo.Qtr_First_Period = X_Qtr_First_Period)
96 OR ((Recinfo.Qtr_First_Period IS NULL)
97 AND (X_Qtr_First_Period IS NULL)))
98 AND ((Recinfo.Qtr_Last_Period = X_Qtr_Last_Period)
99 OR ((Recinfo.Qtr_Last_Period IS NULL)
100 AND (X_Qtr_Last_Period IS NULL)))
101 AND ((Recinfo.Year_First_Period = X_Year_First_Period)
102 OR ((Recinfo.Year_First_Period IS NULL)
103 AND (X_Year_First_Period IS NULL)))
104 AND ((Recinfo.Year_Last_Period = X_Year_Last_Period)
105 OR ((Recinfo.Year_Last_Period IS NULL)
106 AND (X_Year_Last_Period IS NULL)))
107 ) then
108 return;
109 else
110 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
111 APP_EXCEPTION.Raise_Exception;
112 end if;
113
114 end LOCK_ROW;
115
116 procedure UPDATE_ROW (
117 X_QUARTER in NUMBER,
118 X_QTR_FIRST_PERIOD in NUMBER,
119 X_QTR_LAST_PERIOD in NUMBER,
120 X_YEAR_FIRST_PERIOD in NUMBER,
121 X_YEAR_LAST_PERIOD in NUMBER,
122 X_LAST_UPDATE_DATE in DATE,
123 X_LAST_UPDATED_BY in NUMBER,
124 X_LAST_UPDATE_LOGIN in NUMBER
125 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
126
127 begin
128
129 UPDATE fa_period_maps
130 SET quarter = X_Quarter,
131 qtr_first_period = X_Qtr_First_Period,
132 qtr_last_period = X_Qtr_Last_Period,
133 year_first_period = X_Year_First_Period,
134 year_last_period = X_Year_Last_Period,
135 last_update_date = X_Last_Update_Date,
136 last_updated_by = X_Last_Updated_By,
137 last_update_login = X_Last_Update_Login
138 WHERE nvl (year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
139
140 if (SQL%NOTFOUND) then
141 Raise NO_DATA_FOUND;
142 end if;
143
144 exception
145 when others then
146 fa_srvr_msg.add_sql_error(
147 calling_fn => 'fa_period_maps_pkg.update_row', p_log_level_rec => p_log_level_rec);
148 raise;
149
150 end UPDATE_ROW;
151
152 procedure DELETE_ROW (
153 X_YEAR_LAST_PERIOD in NUMBER
154 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
155
156 begin
157
158 DELETE FROM fa_period_maps
159 WHERE nvl (year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
160
161 if (SQL%NOTFOUND) then
162 Raise NO_DATA_FOUND;
163 end if;
164
165 exception
166 when others then
167 fa_srvr_msg.add_sql_error(
168 calling_fn => 'fa_period_maps_pkg.delete_row', p_log_level_rec => p_log_level_rec);
169 raise;
170
171 end DELETE_ROW;
172
173 procedure LOAD_ROW (
174 X_OWNER in VARCHAR2,
175 X_QUARTER in NUMBER,
176 X_QTR_FIRST_PERIOD in NUMBER,
177 X_QTR_LAST_PERIOD in NUMBER,
178 X_YEAR_FIRST_PERIOD in NUMBER,
179 X_YEAR_LAST_PERIOD in NUMBER
180 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
181
182 h_record_exists number(15);
183
184 user_id number;
185 row_id varchar2(64);
186
187 begin
188
189 if (X_Owner = 'SEED') then
190 user_id := 1;
191 else
192 user_id := 0;
193 end if;
194
195 select count(*)
196 into h_record_exists
197 from fa_period_maps pm
198 where nvl (pm.year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
199
200 if (h_record_exists > 0) then
201 fa_period_maps_pkg.update_row (
202 X_Quarter => X_Quarter,
203 X_Qtr_First_Period => X_Qtr_First_Period,
204 X_Qtr_Last_Period => X_Qtr_Last_Period,
205 X_Year_First_Period => X_Year_First_Period,
206 X_Year_Last_Period => X_Year_Last_Period,
207 X_Last_Update_Date => sysdate,
208 X_Last_Updated_By => user_id,
209 X_Last_Update_Login => 0
210 , p_log_level_rec => p_log_level_rec);
211 else
212 fa_period_maps_pkg.insert_row (
213 X_Rowid => row_id,
214 X_Quarter => X_Quarter,
215 X_Qtr_First_Period => X_Qtr_First_Period,
216 X_Qtr_Last_Period => X_Qtr_Last_Period,
217 X_Year_First_Period => X_Year_First_Period,
218 X_Year_Last_Period => X_Year_Last_Period,
219 X_Creation_Date => sysdate,
220 X_Created_By => user_id,
221 X_Last_Update_Date => sysdate,
222 X_Last_Updated_By => user_id,
223 X_Last_Update_Login => 0
224 , p_log_level_rec => p_log_level_rec);
225 end if;
226
227 exception
228 when others then
229 FA_STANDARD_PKG.RAISE_ERROR(
230 CALLED_FN => 'fa_period_maps_pkg.load_row',
231 CALLING_FN => 'upload fa_period_maps', p_log_level_rec => p_log_level_rec);
232
233 end LOAD_ROW;
234 /*Bug 8355119 overloading function for release specific signatures*/
235 procedure LOAD_ROW (
236 X_CUSTOM_MODE in VARCHAR2,
237 X_OWNER in VARCHAR2,
238 X_LAST_UPDATE_DATE in DATE,
239 X_QUARTER in NUMBER,
240 X_QTR_FIRST_PERIOD in NUMBER,
241 X_QTR_LAST_PERIOD in NUMBER,
242 X_YEAR_FIRST_PERIOD in NUMBER,
243 X_YEAR_LAST_PERIOD in NUMBER,
244 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
245
246 h_record_exists number(15);
247
248 user_id number;
249 row_id varchar2(64);
250
251 db_last_updated_by number;
252 db_last_update_date date;
253
254 begin
255
256 user_id := fnd_load_util.owner_id (X_Owner);
257
258 select count(*)
259 into h_record_exists
260 from fa_period_maps pm
261 where nvl (pm.year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
262
263 if (h_record_exists > 0) then
264
265 select last_updated_by, last_update_date
266 into db_last_updated_by, db_last_update_date
267 from fa_period_maps pm
268 where nvl (pm.year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
269
270 if (fnd_load_util.upload_test(user_id, x_last_update_date,
271 db_last_updated_by, db_last_update_date,
272 X_CUSTOM_MODE)) then
273
274 fa_period_maps_pkg.update_row (
275 X_Quarter => X_Quarter,
276 X_Qtr_First_Period => X_Qtr_First_Period,
277 X_Qtr_Last_Period => X_Qtr_Last_Period,
278 X_Year_First_Period => X_Year_First_Period,
279 X_Year_Last_Period => X_Year_Last_Period,
280 X_Last_Update_Date => sysdate,
281 X_Last_Updated_By => user_id,
282 X_Last_Update_Login => 0
283 ,p_log_level_rec => p_log_level_rec);
284 end if;
285 else
286 fa_period_maps_pkg.insert_row (
287 X_Rowid => row_id,
288 X_Quarter => X_Quarter,
289 X_Qtr_First_Period => X_Qtr_First_Period,
290 X_Qtr_Last_Period => X_Qtr_Last_Period,
291 X_Year_First_Period => X_Year_First_Period,
292 X_Year_Last_Period => X_Year_Last_Period,
293 X_Creation_Date => sysdate,
294 X_Created_By => user_id,
295 X_Last_Update_Date => sysdate,
296 X_Last_Updated_By => user_id,
297 X_Last_Update_Login => 0
298 ,p_log_level_rec => p_log_level_rec);
299 end if;
300
301 exception
302 when others then
303 FA_STANDARD_PKG.RAISE_ERROR(
304 CALLED_FN => 'fa_period_maps_pkg.load_row',
305 CALLING_FN => 'upload fa_period_maps'
306 ,p_log_level_rec => p_log_level_rec);
307
308 end LOAD_ROW;
309 /*bug 8355119 adding R12 specific funtion LOAD_SEED_ROW*/
310 procedure LOAD_SEED_ROW (
311 x_upload_mode IN VARCHAR2,
312 x_custom_mode IN VARCHAR2,
313 x_owner IN VARCHAR2,
314 x_last_update_date IN DATE,
315 x_quarter IN NUMBER,
316 x_qtr_first_period IN NUMBER,
317 x_qtr_last_period IN NUMBER,
318 x_year_first_period IN NUMBER,
319 x_year_last_period IN NUMBER) IS
320
321
322 BEGIN
323
324 if (x_upload_mode = 'NLS') then
325 null;
326 else
327 fa_period_maps_pkg.LOAD_ROW (
328 x_custom_mode => x_custom_mode,
329 x_owner => x_owner,
330 x_last_update_date => x_last_update_date,
331 x_quarter => x_quarter,
332 x_qtr_first_period => x_qtr_first_period,
333 x_qtr_last_period => x_qtr_last_period,
334 x_year_first_period => x_year_first_period,
335 x_year_last_period => x_year_last_period);
336 end if;
337
338 END LOAD_SEED_ROW;
339
340
341 END FA_PERIOD_MAPS_PKG;