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