[Home] [Help]
PACKAGE BODY: APPS.FA_PERIOD_MAPS_PKG
Source
1 PACKAGE BODY FA_PERIOD_MAPS_PKG as
2 /* $Header: faxipdmb.pls 120.6 2005/07/28 00:20:01 tkawamur 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 default null) 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'
59 ,p_log_level_rec => p_log_level_rec);
60 raise;
61
62 end INSERT_ROW;
63
64 procedure LOCK_ROW (
65 X_QUARTER in NUMBER,
66 X_QTR_FIRST_PERIOD in NUMBER,
67 X_QTR_LAST_PERIOD in NUMBER,
68 X_YEAR_FIRST_PERIOD in NUMBER,
69 X_YEAR_LAST_PERIOD in NUMBER,
70 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
71 CURSOR C IS
72 SELECT quarter,
73 qtr_first_period,
74 qtr_last_period,
75 year_first_period,
76 year_last_period
77 FROM fa_period_maps
78 WHERE nvl (year_last_period, -9999) = nvl (X_Year_Last_Period, -9999)
79 FOR UPDATE of year_last_period NOWAIT;
80 Recinfo C%ROWTYPE;
81
82 begin
83
84 OPEN C;
85 FETCH C INTO Recinfo;
86 if (C%NOTFOUND) then
87 CLOSE C;
88 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
89 APP_EXCEPTION.Raise_Exception;
90 end if;
91 CLOSE C;
92 if (
93 ((Recinfo.Quarter = X_Quarter)
94 OR ((Recinfo.Quarter IS NULL)
95 AND (X_Quarter IS NULL)))
96 AND ((Recinfo.Qtr_First_Period = X_Qtr_First_Period)
97 OR ((Recinfo.Qtr_First_Period IS NULL)
98 AND (X_Qtr_First_Period IS NULL)))
99 AND ((Recinfo.Qtr_Last_Period = X_Qtr_Last_Period)
100 OR ((Recinfo.Qtr_Last_Period IS NULL)
101 AND (X_Qtr_Last_Period IS NULL)))
102 AND ((Recinfo.Year_First_Period = X_Year_First_Period)
103 OR ((Recinfo.Year_First_Period IS NULL)
104 AND (X_Year_First_Period IS NULL)))
105 AND ((Recinfo.Year_Last_Period = X_Year_Last_Period)
106 OR ((Recinfo.Year_Last_Period IS NULL)
107 AND (X_Year_Last_Period IS NULL)))) 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 default null) 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'
148 ,p_log_level_rec => p_log_level_rec);
149 raise;
150
151 end UPDATE_ROW;
152
153 procedure DELETE_ROW (
154 X_YEAR_LAST_PERIOD in NUMBER,
155 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
156
157 begin
158
159 DELETE FROM fa_period_maps
160 WHERE nvl (year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
161
162 if (SQL%NOTFOUND) then
163 Raise NO_DATA_FOUND;
164 end if;
165
166 exception
167 when others then
168 fa_srvr_msg.add_sql_error(
169 calling_fn => 'fa_period_maps_pkg.delete_row'
170 ,p_log_level_rec => p_log_level_rec);
171 raise;
172
173 end DELETE_ROW;
174
175 procedure LOAD_ROW (
176 X_CUSTOM_MODE in VARCHAR2,
177 X_OWNER in VARCHAR2,
178 X_LAST_UPDATE_DATE in DATE,
179 X_QUARTER in NUMBER,
180 X_QTR_FIRST_PERIOD in NUMBER,
181 X_QTR_LAST_PERIOD in NUMBER,
182 X_YEAR_FIRST_PERIOD in NUMBER,
183 X_YEAR_LAST_PERIOD in NUMBER,
184 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
185
186 h_record_exists number(15);
187
188 user_id number;
189 row_id varchar2(64);
190
191 db_last_updated_by number;
192 db_last_update_date date;
193
194 begin
195
196 user_id := fnd_load_util.owner_id (X_Owner);
197
198 select count(*)
199 into h_record_exists
200 from fa_period_maps pm
201 where nvl (pm.year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
202
203 if (h_record_exists > 0) then
204
205 select last_updated_by, last_update_date
206 into db_last_updated_by, db_last_update_date
207 from fa_period_maps pm
208 where nvl (pm.year_last_period, -9999) = nvl (X_Year_Last_Period, -9999);
209
210 if (fnd_load_util.upload_test(user_id, x_last_update_date,
211 db_last_updated_by, db_last_update_date,
212 X_CUSTOM_MODE)) then
213
214 fa_period_maps_pkg.update_row (
215 X_Quarter => X_Quarter,
216 X_Qtr_First_Period => X_Qtr_First_Period,
217 X_Qtr_Last_Period => X_Qtr_Last_Period,
218 X_Year_First_Period => X_Year_First_Period,
219 X_Year_Last_Period => X_Year_Last_Period,
220 X_Last_Update_Date => sysdate,
221 X_Last_Updated_By => user_id,
222 X_Last_Update_Login => 0
223 ,p_log_level_rec => p_log_level_rec);
224 end if;
225 else
226 fa_period_maps_pkg.insert_row (
227 X_Rowid => row_id,
228 X_Quarter => X_Quarter,
229 X_Qtr_First_Period => X_Qtr_First_Period,
230 X_Qtr_Last_Period => X_Qtr_Last_Period,
231 X_Year_First_Period => X_Year_First_Period,
232 X_Year_Last_Period => X_Year_Last_Period,
233 X_Creation_Date => sysdate,
234 X_Created_By => user_id,
235 X_Last_Update_Date => sysdate,
236 X_Last_Updated_By => user_id,
237 X_Last_Update_Login => 0
238 ,p_log_level_rec => p_log_level_rec);
239 end if;
240
241 exception
242 when others then
243 FA_STANDARD_PKG.RAISE_ERROR(
244 CALLED_FN => 'fa_period_maps_pkg.load_row',
245 CALLING_FN => 'upload fa_period_maps'
246 ,p_log_level_rec => p_log_level_rec);
247
248 end LOAD_ROW;
249
250 procedure LOAD_SEED_ROW (
251 x_upload_mode IN VARCHAR2,
252 x_custom_mode IN VARCHAR2,
253 x_owner IN VARCHAR2,
254 x_last_update_date IN DATE,
255 x_quarter IN NUMBER,
256 x_qtr_first_period IN NUMBER,
257 x_qtr_last_period IN NUMBER,
258 x_year_first_period IN NUMBER,
259 x_year_last_period IN NUMBER) IS
260
261
262 BEGIN
263
264 if (x_upload_mode = 'NLS') then
265 null;
266 else
267 fa_period_maps_pkg.LOAD_ROW (
268 x_custom_mode => x_custom_mode,
269 x_owner => x_owner,
270 x_last_update_date => x_last_update_date,
271 x_quarter => x_quarter,
272 x_qtr_first_period => x_qtr_first_period,
273 x_qtr_last_period => x_qtr_last_period,
274 x_year_first_period => x_year_first_period,
275 x_year_last_period => x_year_last_period);
276 end if;
277
278 END LOAD_SEED_ROW;
279
280 END FA_PERIOD_MAPS_PKG;