[Home] [Help]
PACKAGE BODY: APPS.FA_LOAD_TBL_PKG
Source
1 PACKAGE BODY FA_LOAD_TBL_PKG as
2 /* $Header: FAXVTBLB.pls 120.5 2005/07/25 10:06:18 yyoon ship $ */
3
4 /* Procedure load_dist_table
5
6 Usage Called by client to load all distributions in the
7 global table dist_line_tbl before calling the API
8 */
9
10 PROCEDURE load_dist_table
11 (p_row_id varchar2 default null,
12 p_dist_id number default null,
13 p_asset_id number default null,
14 p_units number,
15 p_date_effective date,
16 p_ccid number,
17 p_location_id number,
18 p_th_id_in number,
19 p_assigned_to number,
20 p_trans_units number,
21 p_record_status varchar2,
22 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
23 IS
24 BEGIN
25 if (g_dist_count=0) then /* initialize the table */
26 dist_table.delete;
27 end if;
28 g_dist_count := g_dist_count + 1;
29 dist_table(g_dist_count).row_id := p_row_id;
30 dist_table(g_dist_count).dist_id := p_dist_id;
31 dist_table(g_dist_count).asset_id := p_asset_id;
32 dist_table(g_dist_count).units := p_units;
33 dist_table(g_dist_count).ccid := p_ccid;
34 dist_table(g_dist_count).location_id := p_location_id;
35 dist_table(g_dist_count).th_id_in := p_th_id_in;
36 dist_table(g_dist_count).assigned_to := p_assigned_to;
37 dist_table(g_dist_count).trans_units := p_trans_units;
38 dist_table(g_dist_count).record_status := p_record_status;
39
40 END load_dist_table;
41
42 PROCEDURE load_inv_table (
43 p_rowid varchar2,
44 p_source_line_id number default null,
45 p_asset_id number default null,
46 p_po_vendor_id number default null,
47 p_asset_invoice_id number default null,
48 p_fixed_assets_cost number,
49 p_po_number varchar2 default null,
50 p_invoice_number varchar2 default null,
51 p_payables_batch_name varchar2 default null,
52 p_payables_ccid number default null,
53 p_feeder_system_name varchar2 default null,
54 p_create_batch_date date default null,
55 p_create_batch_id number default null,
56 p_invoice_date date default null,
57 p_payables_cost number default null,
58 p_post_batch_id number default null,
59 p_invoice_id number default null,
60 p_ap_dist_line_num number default null,
61 p_payables_units number default null,
62 p_description varchar2 default null,
63 p_project_asset_line_id number default null,
64 p_project_id number default null,
65 p_task_id number default null,
66 p_material_indicator_flag varchar2 default null,
67 p_deleted_flag varchar2,
68 p_inv_transfer_cost number,
69 p_inv_update_only varchar2,
70 p_inv_new_cost number,
71 p_depreciate_in_group_flag varchar2,
72 p_attribute1 varchar2 default null,
73 p_attribute2 varchar2 default null,
74 p_attribute3 varchar2 default null,
75 p_attribute4 varchar2 default null,
76 p_attribute5 varchar2 default null,
77 p_attribute6 varchar2 default null,
78 p_attribute7 varchar2 default null,
79 p_attribute8 varchar2 default null,
80 p_attribute9 varchar2 default null,
81 p_attribute10 varchar2 default null,
82 p_attribute11 varchar2 default null,
83 p_attribute12 varchar2 default null,
84 p_attribute13 varchar2 default null,
85 p_attribute14 varchar2 default null,
86 p_attribute15 varchar2 default null,
87 p_attribute_cat_code varchar2 default null,
88 p_invoice_distribution_id number default null,
89 p_invoice_line_number number default null,
90 p_po_distribution_id number default null,
91 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
92 IS
93 BEGIN
94 if (g_inv_count=0) then /* initialize inv_line_tbl */
95 inv_table.delete;
96 end if;
97 g_inv_count := g_inv_count + 1;
98
99 inv_table(g_inv_count).rowid := p_rowid;
100 inv_table(g_inv_count).source_line_id := p_source_line_id;
101 inv_table(g_inv_count).asset_id := p_asset_id;
102 inv_table(g_inv_count).po_vendor_id
103 := p_po_vendor_id;
104 inv_table(g_inv_count).asset_invoice_id
105 := p_asset_invoice_id;
106 inv_table(g_inv_count).fixed_assets_cost
107 := p_fixed_assets_cost;
108 inv_table(g_inv_count).po_number
109 := p_po_number;
110 inv_table(g_inv_count).invoice_number
111 := p_invoice_number;
112 inv_table(g_inv_count).payables_batch_name
113 := p_payables_batch_name;
114 inv_table(g_inv_count).payables_ccid
115 := p_payables_ccid;
116 inv_table(g_inv_count).feeder_system_name
117 := p_feeder_system_name;
118 inv_table(g_inv_count).create_batch_date
119 := p_create_batch_date;
120 inv_table(g_inv_count).create_batch_id
121 := p_create_batch_id;
122 inv_table(g_inv_count).invoice_date
123 := p_invoice_date;
124 inv_table(g_inv_count).payables_cost
125 := p_payables_cost;
126 inv_table(g_inv_count).post_batch_id
127 := p_post_batch_id;
128 inv_table(g_inv_count).invoice_id
129 := p_invoice_id;
130 inv_table(g_inv_count).ap_dist_line_num
131 := p_ap_dist_line_num;
132 inv_table(g_inv_count).payables_units
133 := p_payables_units;
134 inv_table(g_inv_count).description
135 := p_description;
136 inv_table(g_inv_count).project_asset_line_id
137 := p_project_asset_line_id;
138 inv_table(g_inv_count).project_id
139 := p_project_id;
140 inv_table(g_inv_count).task_id
141 := p_task_id;
142 inv_table(g_inv_count).material_indicator_flag
143 := p_material_indicator_flag;
144 inv_table(g_inv_count).deleted_flag
145 := p_deleted_flag;
146 inv_table(g_inv_count).inv_transfer_cost
147 := p_inv_transfer_cost;
148 inv_table(g_inv_count).inv_update_only
149 := p_inv_update_only;
150 inv_table(g_inv_count).inv_new_cost
151 := p_inv_new_cost;
152 inv_table(g_inv_count).depreciate_in_group_flag
153 := p_depreciate_in_group_flag;
154 inv_table(g_inv_count).attribute1
155 := p_attribute1;
156 inv_table(g_inv_count).attribute2
157 := p_attribute2;
158 inv_table(g_inv_count).attribute3
159 := p_attribute3;
160 inv_table(g_inv_count).attribute4
161 := p_attribute4;
162 inv_table(g_inv_count).attribute5
163 := p_attribute5;
164 inv_table(g_inv_count).attribute6
165 := p_attribute6;
166 inv_table(g_inv_count).attribute7
167 := p_attribute7;
168 inv_table(g_inv_count).attribute8
169 := p_attribute8;
170 inv_table(g_inv_count).attribute9
171 := p_attribute9;
172 inv_table(g_inv_count).attribute10
173 := p_attribute10;
174 inv_table(g_inv_count).attribute11
175 := p_attribute11;
176 inv_table(g_inv_count).attribute12
177 := p_attribute12;
178 inv_table(g_inv_count).attribute13
179 := p_attribute13;
180 inv_table(g_inv_count).attribute14
181 := p_attribute14;
182 inv_table(g_inv_count).attribute15
183 := p_attribute15;
184 inv_table(g_inv_count).attribute_cat_code
185 := p_attribute_cat_code;
186 inv_table(g_inv_count).invoice_distribution_id
187 := p_invoice_distribution_id;
188 inv_table(g_inv_count).invoice_line_number
189 := p_invoice_line_number;
190 inv_table(g_inv_count).po_distribution_id
191 := p_po_distribution_id;
192 END load_inv_table;
193
194
195 -- function to reset g_dist_count
196 FUNCTION reset_g_dist_count(p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
197 RETURN BOOLEAN
198 IS
199 BEGIN
200 g_dist_count := 0;
201
202 dist_table.delete;
203
204 return (TRUE);
205
206 EXCEPTION
207 when others then
208 fa_srvr_msg.add_message(
209 calling_fn => 'FA_LOAD_TBL_PKG.reset_g_dist_count'
210 ,p_log_level_rec => p_log_level_rec);
211 return(FALSE);
212 END;
213
214
215 -- procedure to reset g_inv_count
216 PROCEDURE reset_g_inv_count(p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
217 IS
218 BEGIN
219 g_inv_count := 0;
220
221 inv_table.delete;
222 END;
223
224
225 -- Procedure to load default depreciation rules for the specified
226 -- corporate book and its associated tax books in a specific category
227 -- into a global table(deprn_table.) This procedure should be
228 -- called by a mass reclass program wrapper procedure, before calling
229 -- the Reclass Public API for each asset.
230
231 PROCEDURE Load_Deprn_Rules_Tbl(
232 p_corp_book VARCHAR2,
233 p_category_id NUMBER,
234 x_return_status OUT NOCOPY BOOLEAN,
235 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
236 CURSOR BOOK_CR IS
237 SELECT bc.book_type_code
238 FROM fa_category_books cb, fa_book_controls bc
239 WHERE p_corp_book =
240 decode(bc.book_class, 'CORPORATE', bc.book_type_code,
241 'TAX', bc.distribution_source_book, '')
242 AND bc.book_type_code = cb.book_type_code
243 AND cb.category_id = p_category_id
244 AND nvl(bc.date_ineffective, sysdate + 1) > sysdate;
245 h_book VARCHAR2(15);
246 deprn_rules asset_deprn_info;
247 CURSOR DEFAULT_RULES IS
248 SELECT h_book, cbd.start_dpis, cbd.end_dpis,
249 cbd.prorate_convention_code, cbd.deprn_method,
250 cbd.life_in_months,cbd.basic_rate,cbd.adjusted_rate,
251 cbd.production_capacity, cbd.unit_of_measure,
252 cbd.bonus_rule, NULL, cbd.ceiling_name,
253 cbd.depreciate_flag, cbd.allowed_deprn_limit,
254 cbd.special_deprn_limit_amount,cbd.percent_salvage_value
255 FROM FA_CATEGORY_BOOK_DEFAULTS cbd
256 WHERE cbd.book_type_code = h_book
257 AND cbd.category_id = p_category_id;
258
259 BEGIN
260 -- For each book, select default depreciation rules from
261 -- FA_CATEGORY_BOOK_DEFAULTS table and fill the global table(deprn_table.)
262
263 OPEN BOOK_CR;
264
265 LOOP
266 FETCH BOOK_CR INTO h_book;
267 EXIT WHEN BOOK_CR%NOTFOUND;
268
269 -- select default depreciation rules.
270 OPEN DEFAULT_RULES;
271 LOOP
272 FETCH DEFAULT_RULES INTO deprn_rules;
273 EXIT WHEN DEFAULT_RULES%NOTFOUND;
274
275 -- load the table.
276 if (g_deprn_count = 0) then /* initialize the table. */
277 deprn_table.delete;
278 end if;
279 g_deprn_count := g_deprn_count + 1;
280 deprn_table(g_deprn_count) := deprn_rules;
281
282 END LOOP;
283 CLOSE DEFAULT_RULES;
284
285 END LOOP;
286
287 CLOSE BOOK_CR;
288
289 x_return_status := TRUE;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 FA_SRVR_MSG.ADD_SQL_ERROR (
294 CALLING_FN => 'FA_LOAD_TBL_PKG.Load_Deprn_Rules_Tbl'
295 ,p_log_level_rec => p_log_level_rec);
296 x_return_status := FALSE;
297 END Load_Deprn_Rules_Tbl;
298
299
300 -- Procedure that fetches a record of new depreciation rules for the
301 -- given book. x_found indicates whether the record was found or not.
302
303 PROCEDURE Get_Deprn_Rules(
304 p_book_type_code VARCHAR2,
305 p_date_placed_in_service DATE,
306 x_deprn_rules_rec OUT NOCOPY asset_deprn_info,
307 x_found OUT NOCOPY BOOLEAN,
308 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
309 found BOOLEAN := FALSE;
310 BEGIN
311 FOR i IN deprn_table.FIRST .. deprn_table.LAST LOOP
312 if deprn_table.exists(i) then
313 if (deprn_table(i).book_type_code = p_book_type_code and
314 p_date_placed_in_service between deprn_table(i).start_dpis
315 and nvl(deprn_table(i).end_dpis,
316 to_date('31-12-4712', 'DD-MM-YYYY'))) then
317 x_deprn_rules_rec := deprn_table(i);
318 found := TRUE;
319 exit; -- exit the loop when found.
320 end if;
321 end if;
322 END LOOP;
323
324 x_found := found;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 FA_SRVR_MSG.Add_SQL_Error(
329 CALLING_FN => 'FA_LOAD_TBL_PKG.Get_Deprn_Rules'
330 ,p_log_level_rec => p_log_level_rec);
331 x_found := FALSE;
332 END Get_Deprn_Rules;
333
334 -- Procedure to find the index position of a specific depreciation
335 -- rules record in the table, deprn_table, based on the book and
336 -- date placed in service, which uniquely identifies one record in the table.
337 -- If the record is not found, NULL is returned.
338
339 PROCEDURE Find_Position_Deprn_Rules(
340 p_book_type_code VARCHAR2,
341 p_date_placed_in_service DATE,
342 x_pos OUT NOCOPY NUMBER,
343 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
344 BEGIN
345 x_pos := NULL;
346 FOR i IN deprn_table.FIRST .. deprn_table.LAST LOOP
347 IF deprn_table.exists(i) THEN
348 IF (deprn_table(i).book_type_code = p_book_type_code AND
349 p_date_placed_in_service between deprn_table(i).start_dpis
350 and nvl(deprn_table(i).end_dpis,
351 to_date('31-12-4712', 'DD-MM-YYYY'))) THEN
352 x_pos := i;
353 exit; -- exit the loop when found.
354 END IF;
355 END IF;
356 END LOOP;
357 EXCEPTION
358 WHEN OTHERS THEN
359 x_pos := NULL;
360 FA_SRVR_MSG.Add_SQL_Error(
361 CALLING_FN => 'FA_LOAD_TBL_PKG.Find_Position_Deprn_Rules'
362 ,p_log_level_rec => p_log_level_rec);
363 raise;
364 END Find_Position_Deprn_Rules;
365
366
367 END FA_LOAD_TBL_PKG;