[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FA_DISTRIBUTION_HISTORY_PKG
Source
1 PACKAGE BODY FA_DISTRIBUTION_HISTORY_PKG as
2 /* $Header: faxidhb.pls 120.4 2009/03/27 02:36:25 bridgway 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) 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', p_log_level_rec => p_log_level_rec);
85 raise;
86 /* FA_STANDARD_PKG.RAISE_ERROR(
87 CALLED_FN => 'fa_distribution_history_pkg.insert_row',
88 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec); */
89 END Insert_Row;
90
91
92 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
93
94 X_Distribution_Id NUMBER,
95 X_Book_Type_Code VARCHAR2,
96 X_Asset_Id NUMBER,
97 X_Units_Assigned NUMBER,
98 X_Date_Effective DATE,
99 X_Code_Combination_Id NUMBER,
100 X_Location_Id NUMBER,
101 X_Transaction_Header_Id_In NUMBER,
102 X_Date_Ineffective DATE DEFAULT NULL,
103 X_Assigned_To NUMBER DEFAULT NULL,
104 X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
105 X_Transaction_Units NUMBER DEFAULT NULL,
106 X_Retirement_Id NUMBER DEFAULT NULL,
107 X_Calling_Fn VARCHAR2
108 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
109 CURSOR C IS
110 SELECT *
111 FROM fa_distribution_history
112 WHERE rowid = X_Rowid
113 FOR UPDATE of Distribution_Id NOWAIT;
114 Recinfo C%ROWTYPE;
115
116
117 BEGIN
118 OPEN C;
119 FETCH C INTO Recinfo;
120 if (C%NOTFOUND) then
121 CLOSE C;
122 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
123 APP_EXCEPTION.Raise_Exception;
124 end if;
125 CLOSE C;
126 if (
127
128 (Recinfo.distribution_id = X_Distribution_Id)
129 AND (Recinfo.book_type_code = X_Book_Type_Code)
130 AND (Recinfo.asset_id = X_Asset_Id)
131 AND (Recinfo.units_assigned = X_Units_Assigned)
132 AND (Recinfo.date_effective = X_Date_Effective)
133 AND (Recinfo.code_combination_id = X_Code_Combination_Id)
134 AND (Recinfo.location_id = X_Location_Id)
135 AND (Recinfo.transaction_header_id_in = X_Transaction_Header_Id_In)
136 AND ( (Recinfo.date_ineffective = X_Date_Ineffective)
137 OR ( (Recinfo.date_ineffective IS NULL)
138 AND (X_Date_Ineffective IS NULL)))
139 AND ( (Recinfo.assigned_to = X_Assigned_To)
140 OR ( (Recinfo.assigned_to IS NULL)
141 AND (X_Assigned_To IS NULL)))
142 AND ( (Recinfo.transaction_header_id_out =
143 X_Transaction_Header_Id_Out)
144 OR ( (Recinfo.transaction_header_id_out IS NULL)
145 AND (X_Transaction_Header_Id_Out IS NULL)))
146 AND ( (Recinfo.transaction_units = X_Transaction_Units)
147 OR ( (Recinfo.transaction_units IS NULL)
148 AND (X_Transaction_Units IS NULL)))
149 AND ( (Recinfo.retirement_id = X_Retirement_Id)
150 OR ( (Recinfo.retirement_id IS NULL)
151 AND (X_Retirement_Id IS NULL)))
152 ) then
153 return;
154 else
155 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
156 APP_EXCEPTION.Raise_Exception;
157 end if;
158 END Lock_Row;
159
160
161
162 PROCEDURE Update_Row(X_Rowid VARCHAR2 DEFAULT NULL,
163 X_Distribution_Id NUMBER,
164 X_Book_Type_Code VARCHAR2,
165 X_Asset_Id NUMBER,
166 X_Units_Assigned NUMBER,
167 X_Date_Effective DATE,
168 X_Code_Combination_Id NUMBER,
169 X_Location_Id NUMBER,
170 X_Transaction_Header_Id_In NUMBER,
171 X_Last_Update_Date DATE,
172 X_Last_Updated_By NUMBER,
173 X_Date_Ineffective DATE,
174 X_Assigned_To NUMBER,
175 X_Transaction_Header_Id_Out NUMBER,
176 X_Transaction_Units NUMBER,
177 X_Retirement_Id NUMBER,
178 X_Last_Update_Login NUMBER,
179 X_Calling_Fn VARCHAR2
180
181 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) 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', p_log_level_rec => p_log_level_rec);
232 raise;
233 /* FA_STANDARD_PKG.RAISE_ERROR(
234 CALLED_FN => 'fa_distribution_history_pkg.update_row',
235 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec); */
236
237 END Update_Row;
238
239 PROCEDURE Delete_Row(X_Rowid VARCHAR2 DEFAULT NULL,
240 X_Asset_Id NUMBER DEFAULT NULL,
241 X_Book_Type_Code VARCHAR2 DEFAULT NULL,
242 X_Transaction_Header_Id NUMBER DEFAULT NULL,
243 X_Calling_Fn VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
244 BEGIN
245 if X_Rowid is not null then
246 DELETE FROM fa_distribution_history
247 WHERE rowid = X_Rowid;
248 elsif X_Transaction_Header_Id is not null then
249 DELETE FROM fa_distribution_history
250 WHERE transaction_header_id_in = X_Transaction_Header_Id
251 AND asset_id = X_Asset_Id
252 AND book_type_code = X_Book_Type_Code;
253 elsif X_Asset_Id is not null then
254 DELETE FROM fa_distribution_history
255 WHERE asset_id = X_Asset_Id;
256 else
257 -- print some error message
258 null;
259 end if;
260 if (SQL%NOTFOUND) then
261 Raise NO_DATA_FOUND;
262 end if;
263
264 exception
265 when others then
266 fa_srvr_msg.add_sql_error(calling_fn=>
267 'fa_distribution_history_pkg.delete_row', p_log_level_rec => p_log_level_rec);
268 raise;
269 /* FA_STANDARD_PKG.RAISE_ERROR(
270 CALLED_FN => 'fa_distribution_history_pkg.delete_row',
271 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec); */
272 END Delete_Row;
273
274 PROCEDURE Reactivate_Row(X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
275 X_Asset_Id NUMBER,
276 X_Book_Type_Code VARCHAR2,
277 X_Calling_Fn VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
278 X_Book_Class Varchar2(20);
279 BEGIN
280 -- The update to the Distribution History needs to be done only
281 -- when the Book_Class is Corporate.
282
283 Select Book_Class Into X_Book_Class
284 From FA_BOOK_Controls
285 Where Book_Type_Code = X_Book_Type_Code;
286
287 If (X_Book_Class <> 'CORPORATE') then
288 return;
289 End If;
290
291
292 if X_Transaction_Header_Id_Out is not null then
293 UPDATE fa_distribution_history
294 SET transaction_units = null,
295 transaction_header_id_out = null,
296 date_ineffective = null,
297 retirement_id = null
298 WHERE transaction_header_id_out = X_Transaction_Header_Id_Out
299 and asset_id = X_Asset_Id
300 and book_type_code = X_Book_Type_Code;
301 else
302 UPDATE fa_distribution_history
303 set transaction_units = null,
304 retirement_id = null
305 WHERE asset_id = X_Asset_Id
306 and book_type_code = X_Book_Type_Code
307 and date_ineffective is null;
308 end if;
309 --
310 if (SQL%NOTFOUND) then
311 Raise NO_DATA_FOUND;
312 end if;
313
314 exception
315 when others then
316 FA_STANDARD_PKG.RAISE_ERROR(
317 CALLED_FN => 'fa_distribution_history_pkg.reactivate_row',
318 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec);
319 END Reactivate_Row;
320
321 END FA_DISTRIBUTION_HISTORY_PKG;