[Home] [Help]
PACKAGE BODY: APPS.FA_TRANSACTION_ITF_PKG
Source
1 package body FA_TRANSACTION_ITF_PKG as
2 /* $Header: FATRXITFB.pls 120.1.12010000.2 2009/07/19 12:58:55 glchen ship $ */
3
4
5 -- Private type declarations
6
7 -- Private constant declarations
8
9 -- Private variable declarations
10 g_log_level_rec fa_api_types.log_level_rec_type;
11
12 -- Private Function and Procedures declarations
13 -- Function and procedure implementations
14 function check_asset_book(p_book_type_code varchar2, p_asset_id number)
15 return boolean is
16 l_check number;
17 begin
18 begin
19 select 1
20 into l_check
21 from fa_books
22 where book_type_code = p_book_type_code
23 and asset_id = p_asset_id
24 and transaction_header_id_out is null
25 and rownum < 2;
26 return true;
27 exception
28 when no_data_found then
29 return false;
30 when others then
31 return false;
32 end;
33 end;
34 -- Author : SKCHAWLA
35 -- Created : 7/18/2005 1:58:52 PM
36 -- Purpose : To process the assets for AFE Reclassification
37 procedure process_transaction_interface(p_book_type_code in varchar2,
38 x_request_id OUT NOCOPY number,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 x_return_status OUT NOCOPY number) is
42
43 TYPE varchar30_tbl IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
44 TYPE trans_int_rec_tbl IS TABLE OF FA_API_TYPES.trans_interface_rec_type INDEX BY BINARY_INTEGER;
45 l_akey_segment varchar30_tbl;
46 l_akey_ccid number;
47 l_trans_int_rec FA_API_TYPES.trans_interface_rec_type;
48 l_trans_int_rec_tbl trans_int_rec_tbl;
49 l_batch_size number := 100;
50 l_akey_grp_seg varchar2(30);
51 l_akey_grp_seg_index number;
52 l_here_key_seg varchar2(30);
53
54 l_here_key_seg_index number;
55
56 l_asset_id number;
57 l_new_asset_key_ccid number;
58 l_err_stage varchar2(640);
59 l_log_level_rec FA_API_TYPES.log_level_rec_type;
60 afe_err EXCEPTION;
61 l_calling_fn varchar2(250) := 'FA_AFE_RECLASS_PKG.process_transaction_interface';
62 cursor get_interface_assets(c_book_type_code varchar2) is
63 Select TRANSACTION_INTERFACE_ID,
64 TRANSACTION_DATE,
65 TRANSACTION_TYPE_CODE,
66 POSTING_STATUS,
67 BOOK_TYPE_CODE,
68 ASSET_KEY_PROJECT_VALUE,
69 ASSET_KEY_HIERARCHY_VALUE,
70 ASSET_KEY_NEW_HIERARCHY_VALUE,
71 REFERENCE_NUMBER,
72 COMMENTS,
73 CONCURRENT_REQUEST_ID,
74 CREATED_BY,
75 CREATION_DATE,
76 LAST_UPDATED_BY,
77 LAST_UPDATE_DATE,
78 LAST_UPDATE_LOGIN
79 from fa_transaction_interface
80 where book_type_code = c_book_type_Code;
81
82 CURSOR lookup_cur(c_lookup_type varchar2) IS
83 select lookup_code
84 from fa_lookups
85 where lookup_type = c_lookup_type
86 and enabled_flag = 'Y'
87 and nvl(end_date_active, sysdate) >= sysdate
88 and rownum = 1;
89
90 cursor get_asset_key_ccid(c_akey_seg varchar30_tbl) is
91 SELECT code_combination_id
92 INTO l_akey_ccid
93 FROM fa_asset_keywords
94 WHERE nvl(segment1, '-1') = nvl(c_akey_seg(1), '-1')
95 and nvl(segment2, '-1') = nvl(c_akey_seg(2), '-1')
96 and nvl(segment3, '-1') = nvl(c_akey_seg(3), '-1')
97 and nvl(segment4, '-1') = nvl(c_akey_seg(4), '-1')
98 and nvl(segment5, '-1') = nvl(c_akey_seg(5), '-1')
99 and nvl(segment6, '-1') = nvl(c_akey_seg(6), '-1')
100 and nvl(segment7, '-1') = nvl(c_akey_seg(7), '-1')
101 and nvl(segment8, '-1') = nvl(c_akey_seg(8), '-1')
102 and nvl(segment9, '-1') = nvl(c_akey_seg(9), '-1')
103 and nvl(segment10, '-1') = nvl(c_akey_seg(10), '-1');
104
105 cursor get_cip_assets(c_akey_seg varchar30_tbl) is
106 select asset_id
107 from fa_additions
108 where asset_type = 'CIP'
109 and asset_key_ccid in
110 (SELECT code_combination_id
111 FROM fa_asset_keywords
112 WHERE nvl(segment1, '-1') =
113 nvl(c_akey_seg(1), nvl(segment1, '-1'))
114 and nvl(segment2, '-1') =
115 nvl(c_akey_seg(2), nvl(segment2, '-1'))
116 and nvl(segment3, '-1') =
117 nvl(c_akey_seg(3), nvl(segment1, '-1'))
118 and nvl(segment4, '-1') =
119 nvl(c_akey_seg(4), nvl(segment4, '-1'))
120 and nvl(segment5, '-1') =
121 nvl(c_akey_seg(5), nvl(segment5, '-1'))
122 and nvl(segment6, '-1') =
123 nvl(c_akey_seg(6), nvl(segment6, '-1'))
124 and nvl(segment7, '-1') =
125 nvl(c_akey_seg(7), nvl(segment7, '-1'))
126 and nvl(segment8, '-1') =
127 nvl(c_akey_seg(8), nvl(segment8, '-1'))
128 and nvl(segment9, '-1') =
129 nvl(c_akey_seg(9), nvl(segment9, '-1'))
130 and nvl(segment10, '-1') =
131 nvl(c_akey_seg(10), nvl(segment10, '-1')));
132
133 begin
134
135 SAVEPOINT AFE_Reclass_Asset_Begin;
136 l_err_stage := 'Begin FA_AFE_RECLASS_PKG.process_transaction_interface';
137 if (not g_log_level_rec.initialized) then
138 if (NOT
139 fa_util_pub.get_log_level_rec(x_log_level_rec => g_log_level_rec)) then
140 raise FND_API.G_EXC_UNEXPECTED_ERROR;
141 end if;
142 end if;
143
144 l_err_stage := 'Get Lookup values';
145 if (l_log_level_rec.statement_level) then
146 fa_debug_pkg.add(l_calling_fn,
147 '-',
148 'before ' || l_err_stage,
149 p_log_level_rec => l_log_level_rec);
150 end if;
151
152 /* get the asset key mapping */
153 FOR rec IN lookup_cur('ASSET KEY PROJECT MAPPING') LOOP
154 l_akey_grp_seg := rec.lookup_code;
155 l_akey_grp_seg_index := to_number(substr(l_akey_grp_seg, 8));
156 END LOOP;
157
158 FOR rec IN lookup_cur('ASSET KEY HIERARCHY MAPPING') LOOP
159 l_here_key_seg := rec.lookup_code;
160 l_here_key_seg_index := to_number(substr(l_here_key_seg, 8));
161 END LOOP;
162 l_err_stage := 'Fetching all records from the interface table ';
163 /* get all the records from interface table */
164 open get_interface_assets(p_book_type_code);
165 while true loop
166
167 fetch get_interface_assets BULK COLLECT
168 INTO l_trans_int_rec_tbl limit l_batch_size;
169
170 if (get_interface_assets%NOTFOUND) and
171 (l_trans_int_rec_tbl.count < 1) then
172 exit;
173 end if;
174 l_err_stage := 'Looping through all records of interface table';
175 if (l_log_level_rec.statement_level) then
176 fa_debug_pkg.add(l_calling_fn,
177 '-',
178 'before ' || l_err_stage,
179 p_log_level_rec => l_log_level_rec);
180 end if;
181
182 for l_count in 1 .. l_trans_int_rec_tbl.count loop
183 l_trans_int_rec := l_trans_int_rec_tbl(l_count);
184
185 if (l_trans_int_rec.ASSET_KEY_NEW_HIERARCHY_VALUE is not null) then
186 l_err_stage := 'Find the new asset key for new report center';
187 for i in 1 .. 30 loop
188 if (i = l_here_key_seg_index) then
189 l_akey_segment(i) := l_trans_int_rec.ASSET_KEY_NEW_HIERARCHY_VALUE;
190 elsif (i = l_akey_grp_seg_index) then
191 l_akey_segment(i) := l_trans_int_rec.ASSET_KEY_PROJECT_VALUE;
192 else
193 l_akey_segment(i) := null;
194 end if;
195 end loop;
196
197 begin
198 open get_asset_key_ccid(l_akey_segment);
199 fetch get_asset_key_ccid
200 into l_new_asset_key_ccid;
201 close get_asset_key_ccid;
202 exception
203 when no_data_found then
204 null;
205 end;
206 end if;
207
208 for i in 1 .. 30 loop
209 if (i = l_here_key_seg_index) then
210 l_akey_segment(i) := l_trans_int_rec.ASSET_KEY_HIERARCHY_VALUE;
211 elsif (i = l_akey_grp_seg_index) then
212 l_akey_segment(i) := l_trans_int_rec.ASSET_KEY_PROJECT_VALUE;
213 else
214 l_akey_segment(i) := null;
215 end if;
216 end loop;
217 l_err_stage := '*get all assets matching AFE Number and HIERARCHY value';
218 if (l_log_level_rec.statement_level) then
219 fa_debug_pkg.add(l_calling_fn,
220 '-',
221 'before ' || l_err_stage,
222 p_log_level_rec => l_log_level_rec);
223 end if;
224
225 /*get all assets matching AFE Number and HIERARCHY value*/
226 open get_cip_assets(l_akey_segment);
227 while true loop
228 /*fetch the cipa sset from fa_Additions */
229 fetch get_cip_assets
230 into l_asset_id;
231
232 if (get_cip_assets%NOTFOUND) then
233 exit;
234 end if;
235 l_err_stage := 'check whether asset belongs to selected book';
236 if (l_log_level_rec.statement_level) then
237 fa_debug_pkg.add(l_calling_fn,
238 '-',
239 'before ' || l_err_stage,
240 p_log_level_rec => l_log_level_rec);
241 end if;
242
243 /* check whether asset belongs to selected book or not */
244 if (check_asset_book(p_book_type_code, l_asset_id)) then
245
246 if (l_trans_int_rec.transaction_type_code = 'CAPITALIZE') then
247
248 l_err_stage := 'Calling FA_AFE_TRANSACTIONS_PKG.process_capitalize';
249 if (l_log_level_rec.statement_level) then
250 fa_debug_pkg.add(l_calling_fn,
251 '-',
252 'before ' || l_err_stage,
253 p_log_level_rec => l_log_level_rec);
254 end if;
255
256 if not
257 (FA_AFE_TRANSACTIONS_PKG.process_capitalize(l_trans_int_rec,
258 l_asset_id,
259 l_new_asset_key_ccid,
260 l_log_level_rec)) then
261 l_err_stage := 'FA_AFE_TRANSACTIONS_PKG.process_capitalize failed';
262 if (l_log_level_rec.statement_level) then
263 fa_debug_pkg.add(l_calling_fn,
264 '-',
265 l_err_stage || 'For ' ||
266 l_trans_int_rec.transaction_interface_id,
267 p_log_level_rec => l_log_level_rec);
268 end if;
269
270 end if;
271 elsif (l_trans_int_rec.transaction_type_code = 'DRY HOLE') then
272 l_err_stage := 'Calling FA_AFE_TRANSACTIONS_PKG.process_dry_hole';
273 if (l_log_level_rec.statement_level) then
274 fa_debug_pkg.add(l_calling_fn,
275 '-',
276 'before ' || l_err_stage,
277 p_log_level_rec => l_log_level_rec);
278 end if;
279
280 if not
281 (FA_AFE_TRANSACTIONS_PKG.process_dry_hole(l_trans_int_rec,
282 l_asset_id,
283 l_new_asset_key_ccid,
284 l_log_level_rec)) then
285
286 l_err_stage := 'FA_AFE_TRANSACTIONS_PKG.process_dry_hole failed';
287 if (l_log_level_rec.statement_level) then
288 fa_debug_pkg.add(l_calling_fn,
289 '-',
290 l_err_stage || 'For ' ||
291 l_trans_int_rec.transaction_interface_id,
292 p_log_level_rec => l_log_level_rec);
293 end if;
294 end if;
295 elsif (l_trans_int_rec.transaction_type_code = 'EXPENSE') then
296
297 l_err_stage := 'Calling FA_AFE_TRANSACTIONS_PKG.process_expense';
298 if (l_log_level_rec.statement_level) then
299 fa_debug_pkg.add(l_calling_fn,
300 '-',
301 'before ' || l_err_stage,
302 p_log_level_rec => l_log_level_rec);
303 end if;
304
305 if not
306 (FA_AFE_TRANSACTIONS_PKG.process_expense(l_trans_int_rec,
307 l_asset_id,
308 l_log_level_rec)) then
309 l_err_stage := 'FA_AFE_TRANSACTIONS_PKG.process_expense failed';
310 if (l_log_level_rec.statement_level) then
311 fa_debug_pkg.add(l_calling_fn,
312 '-',
313 l_err_stage || 'For ' ||
314 l_trans_int_rec.transaction_interface_id,
315 p_log_level_rec => l_log_level_rec);
316 end if;
317
318 end if;
319 else
320 null; --do nothing....
321 end if;
322 end if;
323 end loop; /* end of get_cip_assets loop*/
324 close get_cip_assets;
325 end loop;
326 commit;
327 end loop;
328 close get_interface_assets;
329 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
330
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332 EXCEPTION
333 WHEN FND_API.G_EXC_ERROR THEN
334 FA_SRVR_MSG.Add_Message(calling_fn => 'FA_TRANSFER_PUB.do_transfer',
335 p_log_level_rec => g_log_level_rec);
336
337 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
338 p_data => x_msg_data);
339 ROLLBACK TO AFE_Reclass_Asset_Begin;
340 x_return_status := FND_API.G_RET_STS_ERROR;
341
342 WHEN OTHERS THEN
343 FA_SRVR_MSG.add_sql_error(calling_fn => 'FA_TRANSFER_PUB.do_transfer',
344 p_log_level_rec => g_log_level_rec);
345
346 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
347 p_data => x_msg_data);
348 ROLLBACK TO AFE_Reclass_Asset_Begin;
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 end process_transaction_interface;
351
352 end FA_TRANSACTION_ITF_PKG;