1 PACKAGE BODY IGI_IMP_IAC_WEBADI_PKG AS
2 -- $Header: igiimpwb.pls 120.10.12000000.1 2007/08/01 16:21:55 npandya noship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiimpwb.igi_imp_iac_webadi_pkg.';
13
14 -- ===================================================================
15 -- FUNCTION Validate_Book: This function is used to test if the book
16 -- is valid and the request status for the book
17 -- ===================================================================
18 FUNCTION Validate_Book(n_book_code IN VARCHAR2)
19 RETURN VARCHAR2 IS
20 l_req_status igi_imp_iac_controls.request_status%TYPE;
21 BEGIN
22 SELECT request_status
23 INTO l_req_status
24 FROM igi_imp_iac_controls
25 WHERE book_type_code = n_book_code;
26
27 RETURN(l_req_status);
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 l_req_status := null;
31 RETURN(l_req_status);
32 END Validate_Book;
33
34 -- ===================================================================
35 -- FUNCTION Validate_Category: This function is used to validate the
36 -- category
37 -- ===================================================================
38 FUNCTION Validate_Category(n_book_code IN VARCHAR2,
39 n_category_desc IN VARCHAR2)
40 RETURN NUMBER IS
41 l_count NUMBER;
42 BEGIN
43
44 SELECT COUNT(*)
45 INTO l_count
46 FROM igi_imp_iac_interface_ctrl
47 WHERE book_type_code = n_book_code
48 AND category_id = (SELECT category_id
49 FROM fa_categories_b_kfv
50 WHERE concatenated_segments = n_category_desc);
51
52 RETURN(l_count);
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 l_count := 0;
56 RETURN(l_count);
57 END Validate_Category;
58
59 -- ===================================================================
60 -- FUNCTION Validate_Transfer: This function is used to validate the
61 -- category
62 -- ===================================================================
63 FUNCTION Validate_Transfer(n_book_code IN VARCHAR2,
64 n_category_desc IN VARCHAR2)
65 RETURN VARCHAR2 IS
66 l_tfr_stat igi_imp_iac_interface_ctrl.transfer_status%TYPE;
67 BEGIN
68 SELECT transfer_status
69 INTO l_tfr_stat
70 FROM igi_imp_iac_interface_ctrl
71 WHERE book_type_code = n_book_code
72 AND category_id = (SELECT category_id
73 FROM fa_categories_b_kfv
74 WHERE concatenated_segments = n_category_desc);
75
76 RETURN(l_tfr_stat);
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79 l_tfr_stat := NULL;
80 RETURN(l_tfr_stat);
81 END Validate_Transfer;
82
83 -- ===================================================================
84 -- FUNCTION Validate_Asset: This function is used to validate the
85 -- asset
86 -- ===================================================================
87 FUNCTION Validate_Asset(n_book_code IN VARCHAR2,
88 n_category_desc IN VARCHAR2,
89 n_asset_number IN VARCHAR2)
90 RETURN NUMBER IS
91 l_count NUMBER;
92 BEGIN
93
94 SELECT COUNT(*)
95 INTO l_count
96 FROM igi_imp_iac_interface
97 WHERE asset_id = (SELECT asset_id
98 FROM fa_additions
99 WHERE asset_number = n_asset_number)
100 AND book_type_code = n_book_code
101 AND category_id = (SELECT category_id
102 FROM fa_categories_b_kfv
103 WHERE concatenated_segments = n_category_desc);
104
105 RETURN(l_count);
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 l_count := 0;
109 RETURN(l_count);
110 END Validate_Asset;
111
112 -- ===================================================================
113 -- FUNCTION Validate_GroupId: This function is used to validate the
114 -- asset
115 -- ===================================================================
116 FUNCTION Validate_GroupId(n_book_code IN VARCHAR2,
117 n_category_desc IN VARCHAR2,
118 n_asset_number IN VARCHAR2,
119 n_group_id IN NUMBER)
120 RETURN NUMBER IS
121 l_count NUMBER;
122 BEGIN
123
124 SELECT COUNT(*)
125 INTO l_count
126 FROM igi_imp_iac_interface
127 WHERE asset_id = (SELECT asset_id
128 FROM fa_additions
129 WHERE asset_number = n_asset_number)
130 AND book_type_code = n_book_code
131 AND group_id = n_group_id
132 AND category_id = (SELECT category_id
133 FROM fa_categories_b_kfv
134 WHERE concatenated_segments = n_category_desc);
135
136 RETURN(l_count);
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 l_count := 0;
140 RETURN(l_count);
141 END Validate_GroupId;
142
143 -- ===================================================================
144 -- PROCEDURE Upload_Data: This is the main procedure that will be
145 -- called by Web ADI, to validate and update the data into the IMP
146 -- interface table
147 -- ===================================================================
148 PROCEDURE Upload_Data(
149
150 p_asset_number IN VARCHAR2,
151 p_book_code IN VARCHAR2,
152 p_category_desc IN VARCHAR2,
153 p_cost_mhca IN NUMBER,
154 p_ytd_mhca IN NUMBER,
155 p_accum_deprn_mhca IN NUMBER,
156 p_reval_reserve_mhca IN NUMBER,
157 p_backlog_mhca IN NUMBER,
158 p_general_fund_mhca IN NUMBER,
159 p_operating_account_cost IN NUMBER,
160 p_operating_account_backlog IN NUMBER,
161 p_group_id IN NUMBER
162 )
163
164 IS
165 CURSOR c_asset_exists
166 IS
167 SELECT COUNT(*)
168 FROM igi_imp_iac_interface
169 WHERE asset_id = (SELECT asset_id
170 FROM fa_additions
171 WHERE asset_number = p_asset_number)
172 AND book_type_code = p_book_code
173 AND category_id = (SELECT category_id
174 FROM fa_categories_b_kfv
175 WHERE concatenated_segments = p_category_desc);
176
177 Cursor C_book_class( cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE) is
178 Select book_class
179 from fa_booK_controls
180 where book_type_code = cp_book_type_code;
181
182 cursor c_deprn_flag(cp_book_type_code igi_imp_iac_interface.book_type_code%TYPE,
183 cp_asset_num igi_imp_iac_interface.asset_number%TYPE ) is
184 select depreciate_flag
185 from fa_books fb,fa_additions fa
186 where book_type_code =cp_book_type_code
187 and fa.asset_number = cp_asset_num
188 and fb.asset_id =fa.asset_id
189 and transaction_header_id_out is null;
190
191
192 l_exists VARCHAR2(1);
193 l_count NUMBER;
194 l_adi_enabled VARCHAR2(3);
195 l_request_status igi_imp_iac_controls.request_status%TYPE;
196 l_transfer_status igi_imp_iac_interface_ctrl.transfer_status%TYPE;
197
198 l_valid BOOLEAN := TRUE;
199
200 l_deprn_flag fa_books.depreciate_flag%type;
201 l_book_class fa_booK_controls.book_class%type;
202
203 -- exceptions
204 e_iac_not_enabled EXCEPTION;
205 e_web_adi_not_enabled EXCEPTION;
206 e_invalid_book EXCEPTION;
207 e_incomplete_preparation EXCEPTION;
208 e_transfer_completed EXCEPTION;
209 e_invalid_category_id EXCEPTION;
210 e_asset_invalid EXCEPTION;
211 e_groupid_invalid EXCEPTION;
212 e_update_error EXCEPTION;
213
214 BEGIN
215
216 l_valid := TRUE;
217 -- check if IAC is enabled
218 IF NOT igi_gen.is_req_installed('IAC') THEN
219 RAISE e_iac_not_enabled;
220 END IF;
221
222 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'IAC is enabled');
223
224 -- Check if the profile option to use WebADI for import/export
225 -- is set to 'Y'
226 l_adi_enabled := fnd_profile.value('IGI_IMP_IAC_USE_WEB_ADI');
227
228 IF (l_adi_enabled <> 'Y') THEN
229 l_valid := FALSE;
230 RAISE e_web_adi_not_enabled;
231 END IF;
232
233 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'Web ADI is enabled');
234
235 -- validate if p_book_type_code exists in control table igi_imp_iac_controls
236 l_request_status := validate_book(p_book_code);
237 IF (l_request_status IS NULL) THEN
238 l_valid := FALSE;
239 RAISE e_invalid_book;
240 END IF;
241
242 /* open c_booK_class(p_book_code);
243 Fetch c_booK_class into l_booK_class;
244 Close c_book_class; */
245
246 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'Book Validated');
247
248 -- validate the request status
249 IF UPPER(l_request_status) <> 'C' THEN
250 l_valid := FALSE;
251 RAISE e_incomplete_preparation;
252 END IF;
253
254 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'Incomplete preparation');
255
256 -- Validate the category id
257 l_count := validate_category(p_book_code,
258 p_category_desc);
259 IF l_count = 0 THEN
260 l_valid := FALSE;
261 RAISE e_invalid_category_id;
262 END IF;
263
264 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'Validated Category');
265
266 -- validate the transfer, if complete, then record should not be updated
267 l_transfer_status := validate_transfer(p_book_code,
268 p_category_desc);
269 IF UPPER(l_transfer_status) = 'C' THEN
270 l_valid := FALSE;
271 RAISE e_transfer_completed;
272 END IF;
273
274 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'Transfer Status Validated');
275
276 -- validate the group_id
277 l_count := validate_groupid(p_book_code,
278 p_category_desc,
279 p_asset_number,
280 p_group_id);
281 IF (l_count = 0) THEN
282 l_valid := FALSE;
283 RAISE e_groupid_invalid;
284 END IF;
285
286 -- if there is no row or multiple rows for the asset return error
287 -- only 1 row must exist for the asset for the book and category
288
289 l_count := validate_asset(p_book_code,
290 p_category_desc,
291 p_asset_number);
292 IF (l_count <> 1) THEN
293 l_valid := FALSE;
294 RAISE e_asset_invalid;
295 END IF;
296
297 igi_iac_debug_pkg.debug_other_string(g_state_level, g_path||'upload_data', 'Asset Validated');
298
299 -- update the asset if it is has passed all validation
300
301 l_deprn_flag := 'YES';
302 --IF l_book_class = 'CORPORATE' THEN
303 open c_deprn_flag(p_book_code,p_asset_number);
304 fetch c_deprn_flag into l_deprn_flag;
305 close c_deprn_flag;
306 --END IF;
307
308
309 If l_deprn_flag = 'YES' Then
310 UPDATE igi_imp_iac_interface
311 SET
312 cost_mhca = p_cost_mhca,
313 ytd_mhca = p_ytd_mhca,
314 accum_deprn_mhca = p_accum_deprn_mhca,
315 reval_reserve_mhca = p_reval_reserve_mhca,
316 backlog_mhca = p_backlog_mhca,
317 general_fund_mhca = p_general_fund_mhca,
318 operating_account_cost = nvl(p_operating_account_cost,0) * -1,
319 operating_account_backlog = nvl(p_operating_account_backlog,0) * -1,
320 last_update_login = fnd_global.login_id,
321 last_update_date = sysdate,
322 last_updated_by = fnd_global.login_id,
323 valid_flag = 'N' -- Bug 5137813
324 WHERE group_id = p_group_id
325 AND asset_number = p_asset_number
326 AND book_type_code = p_book_code;
327 Else
328 UPDATE igi_imp_iac_interface
329 SET
330 cost_mhca = p_cost_mhca,
331 reval_reserve_mhca = p_reval_reserve_mhca,
332 operating_account_cost = nvl(p_operating_account_cost,0) * -1,
333 last_update_login = fnd_global.login_id,
334 last_update_date = sysdate,
335 last_updated_by = fnd_global.login_id,
336 valid_flag = 'N' --Bug 5137813
337 WHERE group_id = p_group_id
338 AND asset_number = p_asset_number
339 AND book_type_code = p_book_code;
340 End if;
341
342
343
344 IF SQL%ROWCOUNT = 0 THEN
345 RAISE e_update_error;
346 END IF;
347
348 igi_iac_debug_pkg.debug_other_string(g_event_level, g_path||'upload_data', 'Update successful');
349
350
351 --ROLLBACK;
352
353 EXCEPTION
354 WHEN e_iac_not_enabled THEN
355 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
356 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
357 FND_MESSAGE.Raise_Error;
358
359 WHEN e_web_adi_not_enabled THEN
360 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_USE_IMP_BUTTON');
361 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
362 FND_MESSAGE.Raise_Error;
363
364 WHEN e_invalid_book THEN
365 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_INVALID_BOOK');
366 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',p_book_code);
367 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
368 FND_MESSAGE.Raise_Error;
369
370 WHEN e_incomplete_preparation THEN
371 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_PREP_NOT_COMPLETE');
372 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',p_book_code);
373 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
374 FND_MESSAGE.Raise_Error;
375
376 WHEN e_transfer_completed THEN
377 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_ASSET_ERR_ADI');
378 FND_MESSAGE.Set_Token('ASSET_NUMBER',p_asset_number);
379 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',p_book_code);
380 FND_MESSAGE.Set_Token('CATEGORY_DESC',p_category_desc);
381 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
382 FND_MESSAGE.Raise_Error;
383
384 WHEN e_invalid_category_id THEN
385 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_CAT_ERR_ADI');
386 FND_MESSAGE.Set_Token('CATEGORY_DESC',p_category_desc);
387 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',p_book_code);
388 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
389 FND_MESSAGE.Raise_Error;
390
391 WHEN e_asset_invalid THEN
392 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_INTFACE_ERR_ADI');
393 FND_MESSAGE.Set_Token('ASSET_NUMBER',p_asset_number);
394 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
395 FND_MESSAGE.Raise_Error;
396
397 WHEN e_groupid_invalid THEN
398 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_GROUPID_ERR_ADI');
399 FND_MESSAGE.Set_Token('GROUP_ID',p_group_id);
400 FND_MESSAGE.Set_Token('ASSET_NUMBER',p_asset_number);
401 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',p_book_code);
402 FND_MESSAGE.Set_Token('CATEGORY_DESC',p_category_desc);
403 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
404 FND_MESSAGE.Raise_Error;
405
406 WHEN e_update_error THEN
407 FND_MESSAGE.Set_Name('IGI','IGI_IMP_IAC_UPDATE_ERR_ADI');
408 FND_MESSAGE.Set_Token('ASSET_NUMBER',p_asset_number);
409 FND_MESSAGE.Set_Token('BOOK_TYPE_CODE',p_book_code);
410 FND_MESSAGE.Set_Token('CATEGORY_DESC',p_category_desc);
411 igi_iac_debug_pkg.debug_other_msg(g_error_level, g_path||'upload_data', FALSE);
412 FND_MESSAGE.Raise_Error;
413
414 WHEN OTHERS THEN
415 igi_iac_debug_pkg.debug_unexpected_msg(g_path||'upload_data');
416 FND_MESSAGE.Raise_Error;
417 END Upload_Data;
418
419 END IGI_IMP_IAC_WEBADI_PKG; -- Package body
420
421
[Home] [Help]