[Home] [Help]
PACKAGE BODY: APPS.FA_DISTRIBUTION_HISTORY_PKG
Source
1 PACKAGE BODY FA_DISTRIBUTION_HISTORY_PKG as
2 /* $Header: faxidhb.pls 120.3 2005/07/25 10:01:32 yyoon ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5
6 X_Distribution_Id IN OUT NOCOPY NUMBER,
7 X_Book_Type_Code VARCHAR2,
8 X_Asset_Id NUMBER,
9 X_Units_Assigned NUMBER,
10 X_Date_Effective DATE,
11 X_Code_Combination_Id NUMBER,
12 X_Location_Id NUMBER,
13 X_Transaction_Header_Id_In NUMBER,
14 X_Last_Update_Date DATE,
15 X_Last_Updated_By NUMBER,
16 X_Date_Ineffective DATE DEFAULT NULL,
17 X_Assigned_To NUMBER DEFAULT NULL,
18 X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
19 X_Transaction_Units NUMBER DEFAULT NULL,
20 X_Retirement_Id NUMBER DEFAULT NULL,
21 X_Last_Update_Login NUMBER DEFAULT NULL,
22 X_Calling_Fn VARCHAR2,
23 p_log_level_rec in fa_api_types.log_level_rec_type default null) IS
24 CURSOR C IS SELECT rowid FROM fa_distribution_history
25 WHERE distribution_id = X_Distribution_Id;
26 CURSOR C2 IS SELECT fa_distribution_history_s.nextval FROM sys.dual;
27 BEGIN
28 if (X_Distribution_Id is NULL) then
29 OPEN C2;
30 FETCH C2 INTO X_Distribution_Id;
31 CLOSE C2;
32 end if;
33
34 INSERT INTO fa_distribution_history(
35
36 distribution_id,
37 book_type_code,
38 asset_id,
39 units_assigned,
40 date_effective,
41 code_combination_id,
42 location_id,
43 transaction_header_id_in,
44 last_update_date,
45 last_updated_by,
46 date_ineffective,
47 assigned_to,
48 transaction_header_id_out,
49 transaction_units,
50 retirement_id,
51 last_update_login
52 ) VALUES (
53
54 X_Distribution_Id,
55 X_Book_Type_Code,
56 X_Asset_Id,
57 X_Units_Assigned,
58 X_Date_Effective,
59 X_Code_Combination_Id,
60 X_Location_Id,
61 X_Transaction_Header_Id_In,
62 X_Last_Update_Date,
63 X_Last_Updated_By,
64 X_Date_Ineffective,
65 X_Assigned_To,
66 X_Transaction_Header_Id_Out,
67 X_Transaction_Units,
68 X_Retirement_Id,
69 X_Last_Update_Login
70
71 );
72
73 OPEN C;
74 FETCH C INTO X_Rowid;
75 if (C%NOTFOUND) then
76 CLOSE C;
77 Raise NO_DATA_FOUND;
78 end if;
79 CLOSE C;
80
81 exception
82 when others then
83 fa_srvr_msg.add_sql_error(calling_fn=>
84 'fa_distribution_history_pkg.insert_row'
85 ,p_log_level_rec => p_log_level_rec);
86 raise;
87 /* FA_STANDARD_PKG.RAISE_ERROR(
88 CALLED_FN => 'fa_distribution_history_pkg.insert_row',
89 CALLING_FN => X_Calling_Fn
90 ,p_log_level_rec => p_log_level_rec); */
91 END Insert_Row;
92
93
94 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
95
96 X_Distribution_Id NUMBER,
97 X_Book_Type_Code VARCHAR2,
98 X_Asset_Id NUMBER,
99 X_Units_Assigned NUMBER,
100 X_Date_Effective DATE,
101 X_Code_Combination_Id NUMBER,
102 X_Location_Id NUMBER,
103 X_Transaction_Header_Id_In NUMBER,
104 X_Date_Ineffective DATE DEFAULT NULL,
105 X_Assigned_To NUMBER DEFAULT NULL,
106 X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
107 X_Transaction_Units NUMBER DEFAULT NULL,
108 X_Retirement_Id NUMBER DEFAULT NULL,
109 X_Calling_Fn VARCHAR2,
110 p_log_level_rec in fa_api_types.log_level_rec_type default null) IS
111 CURSOR C IS
112 SELECT *
113 FROM fa_distribution_history
114 WHERE rowid = X_Rowid
115 FOR UPDATE of Distribution_Id NOWAIT;
116 Recinfo C%ROWTYPE;
117
118
119 BEGIN
120 OPEN C;
121 FETCH C INTO Recinfo;
122 if (C%NOTFOUND) then
123 CLOSE C;
124 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
125 APP_EXCEPTION.Raise_Exception;
126 end if;
127 CLOSE C;
128 if (
129
130 (Recinfo.distribution_id = X_Distribution_Id)
131 AND (Recinfo.book_type_code = X_Book_Type_Code)
132 AND (Recinfo.asset_id = X_Asset_Id)
133 AND (Recinfo.units_assigned = X_Units_Assigned)
134 AND (Recinfo.date_effective = X_Date_Effective)
135 AND (Recinfo.code_combination_id = X_Code_Combination_Id)
136 AND (Recinfo.location_id = X_Location_Id)
137 AND (Recinfo.transaction_header_id_in = X_Transaction_Header_Id_In)
138 AND ( (Recinfo.date_ineffective = X_Date_Ineffective)
139 OR ( (Recinfo.date_ineffective IS NULL)
140 AND (X_Date_Ineffective IS NULL)))
141 AND ( (Recinfo.assigned_to = X_Assigned_To)
142 OR ( (Recinfo.assigned_to IS NULL)
143 AND (X_Assigned_To IS NULL)))
144 AND ( (Recinfo.transaction_header_id_out =
145 X_Transaction_Header_Id_Out)
146 OR ( (Recinfo.transaction_header_id_out IS NULL)
147 AND (X_Transaction_Header_Id_Out IS NULL)))
148 AND ( (Recinfo.transaction_units = X_Transaction_Units)
149 OR ( (Recinfo.transaction_units IS NULL)
150 AND (X_Transaction_Units IS NULL)))
151 AND ( (Recinfo.retirement_id = X_Retirement_Id)
152 OR ( (Recinfo.retirement_id IS NULL)
153 AND (X_Retirement_Id IS NULL)))) then
154 return;
155 else
156 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
157 APP_EXCEPTION.Raise_Exception;
158 end if;
159 END Lock_Row;
160
161
162
163 PROCEDURE Update_Row(X_Rowid VARCHAR2 DEFAULT NULL,
164 X_Distribution_Id NUMBER,
165 X_Book_Type_Code VARCHAR2,
166 X_Asset_Id NUMBER,
167 X_Units_Assigned NUMBER,
168 X_Date_Effective DATE,
169 X_Code_Combination_Id NUMBER,
170 X_Location_Id NUMBER,
171 X_Transaction_Header_Id_In NUMBER,
172 X_Last_Update_Date DATE,
173 X_Last_Updated_By NUMBER,
174 X_Date_Ineffective DATE,
175 X_Assigned_To NUMBER,
176 X_Transaction_Header_Id_Out NUMBER,
177 X_Transaction_Units NUMBER,
178 X_Retirement_Id NUMBER,
179 X_Last_Update_Login NUMBER,
180 X_Calling_Fn VARCHAR2,
181 p_log_level_rec in fa_api_types.log_level_rec_type default null) IS
182 BEGIN
183 if X_Rowid is not null then
184 UPDATE fa_distribution_history
185 SET
186 distribution_id = X_Distribution_Id,
187 book_type_code = X_Book_Type_Code,
188 asset_id = X_Asset_Id,
189 units_assigned = X_Units_Assigned,
190 code_combination_id = X_Code_Combination_Id,
191 location_id = X_Location_Id,
192 transaction_header_id_in = X_Transaction_Header_Id_In,
193 last_update_date = X_Last_Update_Date,
194 last_updated_by = X_Last_Updated_By,
195 date_ineffective = X_Date_Ineffective,
196 assigned_to = X_Assigned_To,
197 transaction_header_id_out = X_Transaction_Header_Id_Out,
198 transaction_units = X_Transaction_Units,
199 retirement_id = X_Retirement_Id,
200 last_update_login = X_Last_Update_Login
201 WHERE rowid = X_Rowid;
202 else
203 UPDATE fa_distribution_history
204 SET
205 distribution_id = X_Distribution_Id,
206 book_type_code = X_Book_Type_Code,
207 asset_id = X_Asset_Id,
208 units_assigned = X_Units_Assigned,
209 date_effective = X_Date_Effective,
210 code_combination_id = X_Code_Combination_Id,
211 location_id = X_Location_Id,
212 transaction_header_id_in = X_Transaction_Header_Id_In,
213 last_update_date = X_Last_Update_Date,
214 last_updated_by = X_Last_Updated_By,
215 date_ineffective = X_Date_Ineffective,
216 assigned_to = X_Assigned_To,
217 transaction_header_id_out = X_Transaction_Header_Id_Out,
218 transaction_units = X_Transaction_Units,
219 retirement_id = X_Retirement_Id,
220 last_update_login = X_Last_Update_Login
221 WHERE distribution_id = X_Distribution_id;
222 end if;
223
224 if (SQL%NOTFOUND) then
225 Raise NO_DATA_FOUND;
226 end if;
227
228 exception
229 when others then
230 fa_srvr_msg.add_sql_error(calling_fn=>
231 'fa_distribution_history_pkg.update_row'
232 ,p_log_level_rec => p_log_level_rec);
233 raise;
234 /* FA_STANDARD_PKG.RAISE_ERROR(
235 CALLED_FN => 'fa_distribution_history_pkg.update_row',
236 CALLING_FN => X_Calling_Fn
237 ,p_log_level_rec => p_log_level_rec); */
238
239 END Update_Row;
240
241 PROCEDURE Delete_Row(X_Rowid VARCHAR2 DEFAULT NULL,
242 X_Asset_Id NUMBER DEFAULT NULL,
243 X_Book_Type_Code VARCHAR2 DEFAULT NULL,
244 X_Transaction_Header_Id NUMBER DEFAULT NULL,
245 X_Calling_Fn VARCHAR2,
246 p_log_level_rec in fa_api_types.log_level_rec_type default null) IS
247 BEGIN
248 if X_Rowid is not null then
249 DELETE FROM fa_distribution_history
250 WHERE rowid = X_Rowid;
251 elsif X_Transaction_Header_Id is not null then
252 DELETE FROM fa_distribution_history
253 WHERE transaction_header_id_in = X_Transaction_Header_Id
254 AND asset_id = X_Asset_Id
255 AND book_type_code = X_Book_Type_Code;
256 elsif X_Asset_Id is not null then
257 DELETE FROM fa_distribution_history
258 WHERE asset_id = X_Asset_Id;
259 else
260 -- print some error message
261 null;
262 end if;
263 if (SQL%NOTFOUND) then
264 Raise NO_DATA_FOUND;
265 end if;
266
267 exception
268 when others then
269 fa_srvr_msg.add_sql_error(calling_fn=>
270 'fa_distribution_history_pkg.delete_row'
271 ,p_log_level_rec => p_log_level_rec);
272 raise;
273 /* FA_STANDARD_PKG.RAISE_ERROR(
274 CALLED_FN => 'fa_distribution_history_pkg.delete_row',
275 CALLING_FN => X_Calling_Fn
276 ,p_log_level_rec => p_log_level_rec); */
277 END Delete_Row;
278
279 PROCEDURE Reactivate_Row(X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
280 X_Asset_Id NUMBER,
281 X_Book_Type_Code VARCHAR2,
282 X_Calling_Fn VARCHAR2,
283 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
284 X_Book_Class Varchar2(20);
285 BEGIN
286 -- The update to the Distribution History needs to be done only
287 -- when the Book_Class is Corporate.
288
289 Select Book_Class Into X_Book_Class
290 From FA_BOOK_Controls
291 Where Book_Type_Code = X_Book_Type_Code;
292
293 If (X_Book_Class <> 'CORPORATE') then
294 return;
295 End If;
296
297
298 if X_Transaction_Header_Id_Out is not null then
299 UPDATE fa_distribution_history
300 SET transaction_units = null,
301 transaction_header_id_out = null,
302 date_ineffective = null,
303 retirement_id = null
304 WHERE transaction_header_id_out = X_Transaction_Header_Id_Out
305 and asset_id = X_Asset_Id
306 and book_type_code = X_Book_Type_Code;
307 else
308 UPDATE fa_distribution_history
309 set transaction_units = null,
310 retirement_id = null
311 WHERE asset_id = X_Asset_Id
312 and book_type_code = X_Book_Type_Code
313 and date_ineffective is null;
314 end if;
315 --
316 if (SQL%NOTFOUND) then
317 Raise NO_DATA_FOUND;
318 end if;
319
320 exception
321 when others then
322 FA_STANDARD_PKG.RAISE_ERROR(
323 CALLED_FN => 'fa_distribution_history_pkg.reactivate_row',
324 CALLING_FN => X_Calling_Fn
325 ,p_log_level_rec => p_log_level_rec);
326 END Reactivate_Row;
327
328 END FA_DISTRIBUTION_HISTORY_PKG;