[Home] [Help]
PACKAGE BODY: APPS.FA_MASSADD_PREPARE_PKG
Source
1 package body FA_MASSADD_PREPARE_PKG as
2 /* $Header: FAMAPREPB.pls 120.7.12020000.2 2012/07/23 08:34:03 rmandali ship $ */
3
4 -- Private type declarations
5
6 -- Private constant declarations
7
8 -- Private variable declarations
9 g_log_level_rec fa_api_types.log_level_rec_type;
10 -- Function and procedure implementations
11
12 /*===============================End Of FUNCTION/PROCEDURE===============================*/
13 FUNCTION update_mass_additions (p_mass_add_rec_tbl FA_MASSADD_PREPARE_PKG.mass_add_rec_tbl
14 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type DEFAULT NULL)
15 RETURN BOOLEAN IS
16 TYPE mass_add_tbl IS TABLE OF fa_mass_additions%ROWTYPE
17 INDEX BY PLS_INTEGER;
18
19 l_mass_add_tbl mass_add_tbl;
20
21 TYPE num_tbl IS TABLE OF NUMBER
22 INDEX BY PLS_INTEGER;
23
24 l_mass_add_id_tbl num_tbl;
25 l_debug_str VARCHAR2 (1000);
26 l_errors NUMBER;
27 l_calling_fn VARCHAR2 (40) := 'update_mass_additions';
28 dml_errors EXCEPTION;
29 PRAGMA EXCEPTION_INIT (dml_errors, -24381);
30 BEGIN
31 l_debug_str := 'Updating Mass Addions';
32
33 IF (p_log_level_rec.statement_level) THEN
34 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
35 p_log_level_rec => p_log_level_rec);
36 END IF;
37
38 FOR counter IN 1 .. p_mass_add_rec_tbl.COUNT LOOP
39 l_mass_add_id_tbl (counter) := p_mass_add_rec_tbl (counter).mass_addition_id;
40 l_mass_add_tbl (counter).mass_addition_id := p_mass_add_rec_tbl ( counter).mass_addition_id;
41 l_mass_add_tbl (counter).ASSET_NUMBER := p_mass_add_rec_tbl (counter).ASSET_NUMBER;
42 l_mass_add_tbl (counter).TAG_NUMBER := p_mass_add_rec_tbl (counter).TAG_NUMBER;
43 l_mass_add_tbl (counter).DESCRIPTION := p_mass_add_rec_tbl (counter).DESCRIPTION;
44 l_mass_add_tbl (counter).ASSET_CATEGORY_ID := p_mass_add_rec_tbl ( counter).ASSET_CATEGORY_ID;
45 l_mass_add_tbl (counter).MANUFACTURER_NAME := p_mass_add_rec_tbl ( counter).MANUFACTURER_NAME;
46 l_mass_add_tbl (counter).SERIAL_NUMBER := p_mass_add_rec_tbl ( counter).SERIAL_NUMBER;
47 l_mass_add_tbl (counter).MODEL_NUMBER := p_mass_add_rec_tbl (counter).MODEL_NUMBER;
48 l_mass_add_tbl (counter).BOOK_TYPE_CODE := p_mass_add_rec_tbl ( counter).BOOK_TYPE_CODE;
49 l_mass_add_tbl (counter).DATE_PLACED_IN_SERVICE := p_mass_add_rec_tbl ( counter).DATE_PLACED_IN_SERVICE;
50 l_mass_add_tbl (counter).FIXED_ASSETS_COST := p_mass_add_rec_tbl ( counter).FIXED_ASSETS_COST;
51 l_mass_add_tbl (counter).PAYABLES_UNITS := p_mass_add_rec_tbl ( counter).PAYABLES_UNITS;
52 l_mass_add_tbl (counter).FIXED_ASSETS_UNITS := p_mass_add_rec_tbl ( counter).FIXED_ASSETS_UNITS;
53 l_mass_add_tbl (counter).PAYABLES_CODE_COMBINATION_ID := p_mass_add_rec_tbl ( counter).PAYABLES_CODE_COMBINATION_ID;
54 l_mass_add_tbl (counter).EXPENSE_CODE_COMBINATION_ID := p_mass_add_rec_tbl ( counter).EXPENSE_CODE_COMBINATION_ID;
55 l_mass_add_tbl (counter).LOCATION_ID := p_mass_add_rec_tbl (counter).LOCATION_ID;
56 l_mass_add_tbl (counter).ASSIGNED_TO := p_mass_add_rec_tbl (counter).ASSIGNED_TO;
57 l_mass_add_tbl (counter).FEEDER_SYSTEM_NAME := p_mass_add_rec_tbl ( counter).FEEDER_SYSTEM_NAME;
58 l_mass_add_tbl (counter).CREATE_BATCH_DATE := p_mass_add_rec_tbl ( counter).CREATE_BATCH_DATE;
59 l_mass_add_tbl (counter).CREATE_BATCH_ID := p_mass_add_rec_tbl ( counter).CREATE_BATCH_ID;
60 l_mass_add_tbl (counter).LAST_UPDATE_DATE := p_mass_add_rec_tbl ( counter).LAST_UPDATE_DATE;
61 l_mass_add_tbl (counter).LAST_UPDATED_BY := p_mass_add_rec_tbl ( counter).LAST_UPDATED_BY;
62 l_mass_add_tbl (counter).REVIEWER_COMMENTS := p_mass_add_rec_tbl ( counter).REVIEWER_COMMENTS;
63 l_mass_add_tbl (counter).INVOICE_NUMBER := p_mass_add_rec_tbl ( counter).INVOICE_NUMBER;
64 l_mass_add_tbl (counter).INVOICE_LINE_NUMBER := p_mass_add_rec_tbl ( counter).INVOICE_LINE_NUMBER; -- bug8984263
65 l_mass_add_tbl (counter).INVOICE_DISTRIBUTION_ID := p_mass_add_rec_tbl ( counter).INVOICE_DISTRIBUTION_ID; -- bug8984263
66 l_mass_add_tbl (counter).VENDOR_NUMBER := p_mass_add_rec_tbl ( counter).VENDOR_NUMBER;
67 l_mass_add_tbl (counter).PO_VENDOR_ID := p_mass_add_rec_tbl (counter).PO_VENDOR_ID;
68 l_mass_add_tbl (counter).PO_NUMBER := p_mass_add_rec_tbl (counter).PO_NUMBER;
69 l_mass_add_tbl (counter).POSTING_STATUS := p_mass_add_rec_tbl ( counter).POSTING_STATUS;
70 l_mass_add_tbl (counter).QUEUE_NAME := p_mass_add_rec_tbl (counter).QUEUE_NAME;
71 l_mass_add_tbl (counter).INVOICE_DATE := p_mass_add_rec_tbl (counter).INVOICE_DATE;
72 l_mass_add_tbl (counter).INVOICE_CREATED_BY := p_mass_add_rec_tbl ( counter).INVOICE_CREATED_BY;
73 l_mass_add_tbl (counter).INVOICE_UPDATED_BY := p_mass_add_rec_tbl ( counter).INVOICE_UPDATED_BY;
74 l_mass_add_tbl (counter).PAYABLES_COST := p_mass_add_rec_tbl ( counter).PAYABLES_COST;
75 l_mass_add_tbl (counter).INVOICE_ID := p_mass_add_rec_tbl (counter).INVOICE_ID;
76 l_mass_add_tbl (counter).PAYABLES_BATCH_NAME := p_mass_add_rec_tbl ( counter).PAYABLES_BATCH_NAME;
77 l_mass_add_tbl (counter).DEPRECIATE_FLAG := p_mass_add_rec_tbl ( counter).DEPRECIATE_FLAG;
78 l_mass_add_tbl (counter).PARENT_MASS_ADDITION_ID := p_mass_add_rec_tbl ( counter).PARENT_MASS_ADDITION_ID;
79 l_mass_add_tbl (counter).PARENT_ASSET_ID := p_mass_add_rec_tbl ( counter).PARENT_ASSET_ID;
80 l_mass_add_tbl (counter).SPLIT_MERGED_CODE := p_mass_add_rec_tbl ( counter).SPLIT_MERGED_CODE;
81 l_mass_add_tbl (counter).AP_DISTRIBUTION_LINE_NUMBER := p_mass_add_rec_tbl ( counter).AP_DISTRIBUTION_LINE_NUMBER;
82 l_mass_add_tbl (counter).POST_BATCH_ID := p_mass_add_rec_tbl ( counter).POST_BATCH_ID;
83 l_mass_add_tbl (counter).ADD_TO_ASSET_ID := p_mass_add_rec_tbl ( counter).ADD_TO_ASSET_ID;
84 l_mass_add_tbl (counter).AMORTIZE_FLAG := p_mass_add_rec_tbl ( counter).AMORTIZE_FLAG;
85 l_mass_add_tbl (counter).NEW_MASTER_FLAG := p_mass_add_rec_tbl ( counter).NEW_MASTER_FLAG;
86 l_mass_add_tbl (counter).ASSET_KEY_CCID := p_mass_add_rec_tbl ( counter).ASSET_KEY_CCID;
87 l_mass_add_tbl (counter).ASSET_TYPE := p_mass_add_rec_tbl (counter).ASSET_TYPE;
88 l_mass_add_tbl (counter).DEPRN_RESERVE := p_mass_add_rec_tbl ( counter).DEPRN_RESERVE;
89 l_mass_add_tbl (counter).YTD_DEPRN := p_mass_add_rec_tbl (counter).YTD_DEPRN;
90 l_mass_add_tbl (counter).BEGINNING_NBV := p_mass_add_rec_tbl ( counter).BEGINNING_NBV;
91 l_mass_add_tbl (counter).CREATED_BY := p_mass_add_rec_tbl (counter).CREATED_BY;
92 l_mass_add_tbl (counter).CREATION_DATE := p_mass_add_rec_tbl ( counter).CREATION_DATE;
93 l_mass_add_tbl (counter).LAST_UPDATE_LOGIN := p_mass_add_rec_tbl ( counter).LAST_UPDATE_LOGIN;
94 l_mass_add_tbl (counter).SALVAGE_VALUE := p_mass_add_rec_tbl ( counter).SALVAGE_VALUE;
95 l_mass_add_tbl (counter).ACCOUNTING_DATE := p_mass_add_rec_tbl ( counter).ACCOUNTING_DATE;
96 l_mass_add_tbl (counter).ATTRIBUTE1 := p_mass_add_rec_tbl (counter).ATTRIBUTE1;
97 l_mass_add_tbl (counter).ATTRIBUTE2 := p_mass_add_rec_tbl (counter).ATTRIBUTE2;
98 l_mass_add_tbl (counter).ATTRIBUTE3 := p_mass_add_rec_tbl (counter).ATTRIBUTE3;
99 l_mass_add_tbl (counter).ATTRIBUTE4 := p_mass_add_rec_tbl (counter).ATTRIBUTE4;
100 l_mass_add_tbl (counter).ATTRIBUTE5 := p_mass_add_rec_tbl (counter).ATTRIBUTE5;
101 l_mass_add_tbl (counter).ATTRIBUTE6 := p_mass_add_rec_tbl (counter).ATTRIBUTE6;
102 l_mass_add_tbl (counter).ATTRIBUTE7 := p_mass_add_rec_tbl (counter).ATTRIBUTE7;
103 l_mass_add_tbl (counter).ATTRIBUTE8 := p_mass_add_rec_tbl (counter).ATTRIBUTE8;
104 l_mass_add_tbl (counter).ATTRIBUTE9 := p_mass_add_rec_tbl (counter).ATTRIBUTE9;
105 l_mass_add_tbl (counter).ATTRIBUTE10 := p_mass_add_rec_tbl (counter).ATTRIBUTE10;
106 l_mass_add_tbl (counter).ATTRIBUTE11 := p_mass_add_rec_tbl (counter).ATTRIBUTE11;
107 l_mass_add_tbl (counter).ATTRIBUTE12 := p_mass_add_rec_tbl (counter).ATTRIBUTE12;
108 l_mass_add_tbl (counter).ATTRIBUTE13 := p_mass_add_rec_tbl (counter).ATTRIBUTE13;
109 l_mass_add_tbl (counter).ATTRIBUTE14 := p_mass_add_rec_tbl (counter).ATTRIBUTE14;
110 l_mass_add_tbl (counter).ATTRIBUTE15 := p_mass_add_rec_tbl (counter).ATTRIBUTE15;
111 l_mass_add_tbl (counter).ATTRIBUTE_CATEGORY_CODE := p_mass_add_rec_tbl ( counter).ATTRIBUTE_CATEGORY_CODE;
112 l_mass_add_tbl (counter).FULLY_RSVD_REVALS_COUNTER := p_mass_add_rec_tbl ( counter).FULLY_RSVD_REVALS_COUNTER;
113 l_mass_add_tbl (counter).MERGE_INVOICE_NUMBER := p_mass_add_rec_tbl ( counter).MERGE_INVOICE_NUMBER;
114 l_mass_add_tbl (counter).MERGE_VENDOR_NUMBER := p_mass_add_rec_tbl ( counter).MERGE_VENDOR_NUMBER;
115 l_mass_add_tbl (counter).PRODUCTION_CAPACITY := p_mass_add_rec_tbl ( counter).PRODUCTION_CAPACITY;
116 l_mass_add_tbl (counter).REVAL_AMORTIZATION_BASIS := p_mass_add_rec_tbl ( counter).REVAL_AMORTIZATION_BASIS;
117 l_mass_add_tbl (counter).REVAL_RESERVE := p_mass_add_rec_tbl ( counter).REVAL_RESERVE;
118 l_mass_add_tbl (counter).UNIT_OF_MEASURE := p_mass_add_rec_tbl ( counter).UNIT_OF_MEASURE;
119 l_mass_add_tbl (counter).UNREVALUED_COST := p_mass_add_rec_tbl ( counter).UNREVALUED_COST;
120 l_mass_add_tbl (counter).YTD_REVAL_DEPRN_EXPENSE := p_mass_add_rec_tbl ( counter).YTD_REVAL_DEPRN_EXPENSE;
121 l_mass_add_tbl (counter).ATTRIBUTE16 := p_mass_add_rec_tbl (counter).ATTRIBUTE16;
122 l_mass_add_tbl (counter).ATTRIBUTE17 := p_mass_add_rec_tbl (counter).ATTRIBUTE17;
123 l_mass_add_tbl (counter).ATTRIBUTE18 := p_mass_add_rec_tbl (counter).ATTRIBUTE18;
124 l_mass_add_tbl (counter).ATTRIBUTE19 := p_mass_add_rec_tbl (counter).ATTRIBUTE19;
125 l_mass_add_tbl (counter).ATTRIBUTE20 := p_mass_add_rec_tbl (counter).ATTRIBUTE20;
126 l_mass_add_tbl (counter).ATTRIBUTE21 := p_mass_add_rec_tbl (counter).ATTRIBUTE21;
127 l_mass_add_tbl (counter).ATTRIBUTE22 := p_mass_add_rec_tbl (counter).ATTRIBUTE22;
128 l_mass_add_tbl (counter).ATTRIBUTE23 := p_mass_add_rec_tbl (counter).ATTRIBUTE23;
129 l_mass_add_tbl (counter).ATTRIBUTE24 := p_mass_add_rec_tbl (counter).ATTRIBUTE24;
130 l_mass_add_tbl (counter).ATTRIBUTE25 := p_mass_add_rec_tbl (counter).ATTRIBUTE25;
131 l_mass_add_tbl (counter).ATTRIBUTE26 := p_mass_add_rec_tbl (counter).ATTRIBUTE26;
132 l_mass_add_tbl (counter).ATTRIBUTE27 := p_mass_add_rec_tbl (counter).ATTRIBUTE27;
133 l_mass_add_tbl (counter).ATTRIBUTE28 := p_mass_add_rec_tbl (counter).ATTRIBUTE28;
134 l_mass_add_tbl (counter).ATTRIBUTE29 := p_mass_add_rec_tbl (counter).ATTRIBUTE29;
135 l_mass_add_tbl (counter).ATTRIBUTE30 := p_mass_add_rec_tbl (counter).ATTRIBUTE30;
136 l_mass_add_tbl (counter).MERGED_CODE := p_mass_add_rec_tbl (counter).MERGED_CODE;
137 l_mass_add_tbl (counter).SPLIT_CODE := p_mass_add_rec_tbl (counter).SPLIT_CODE;
138 l_mass_add_tbl (counter).MERGE_PARENT_MASS_ADDITIONS_ID := p_mass_add_rec_tbl ( counter).MERGE_PARENT_MASS_ADD_ID;
139 l_mass_add_tbl (counter).SPLIT_PARENT_MASS_ADDITIONS_ID := p_mass_add_rec_tbl ( counter).SPLIT_PARENT_MASS_ADD_ID;
140 l_mass_add_tbl (counter).PROJECT_ASSET_LINE_ID := p_mass_add_rec_tbl ( counter).PROJECT_ASSET_LINE_ID;
141 l_mass_add_tbl (counter).PROJECT_ID := p_mass_add_rec_tbl (counter).PROJECT_ID;
142 l_mass_add_tbl (counter).TASK_ID := p_mass_add_rec_tbl (counter).TASK_ID;
143 l_mass_add_tbl (counter).SUM_UNITS := p_mass_add_rec_tbl (counter).SUM_UNITS;
144 l_mass_add_tbl (counter).DIST_NAME := p_mass_add_rec_tbl (counter).DIST_NAME;
145 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE1 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE1;
146 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE2 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE2;
147 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE3 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE3;
148 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE4 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE4;
149 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE5 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE5;
150 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE6 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE6;
151 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE7 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE7;
152 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE8 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE8;
153 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE9 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE9;
154 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE10 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE10;
155 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE11 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE11;
156 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE12 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE12;
157 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE13 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE13;
158 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE14 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE14;
159 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE15 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE15;
160 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE16 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE16;
161 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE17 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE17;
162 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE18 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE18;
163 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE19 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE19;
164 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE20 := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE20;
165 l_mass_add_tbl (counter).GLOBAL_ATTRIBUTE_CATEGORY := p_mass_add_rec_tbl ( counter).GLOBAL_ATTRIBUTE_CATEGORY;
166 l_mass_add_tbl (counter).CONTEXT := p_mass_add_rec_tbl (counter).CONTEXT;
167 l_mass_add_tbl (counter).INVENTORIAL := p_mass_add_rec_tbl (counter).INVENTORIAL;
168 l_mass_add_tbl (counter).SHORT_FISCAL_YEAR_FLAG := p_mass_add_rec_tbl ( counter).SHORT_FISCAL_YEAR_FLAG;
169 l_mass_add_tbl (counter).CONVERSION_DATE := p_mass_add_rec_tbl ( counter).CONVERSION_DATE;
170 l_mass_add_tbl (counter).ORIGINAL_DEPRN_START_DATE := p_mass_add_rec_tbl ( counter).ORIGINAL_DEPRN_START_DATE;
171 l_mass_add_tbl (counter).GROUP_ASSET_ID := p_mass_add_rec_tbl ( counter).GROUP_ASSET_ID;
172 l_mass_add_tbl (counter).CUA_PARENT_HIERARCHY_ID := p_mass_add_rec_tbl ( counter).CUA_PARENT_HIERARCHY_ID;
173 l_mass_add_tbl (counter).UNITS_TO_ADJUST := p_mass_add_rec_tbl ( counter).UNITS_TO_ADJUST;
174 l_mass_add_tbl (counter).BONUS_YTD_DEPRN := p_mass_add_rec_tbl ( counter).BONUS_YTD_DEPRN;
175 l_mass_add_tbl (counter).BONUS_DEPRN_RESERVE := p_mass_add_rec_tbl ( counter).BONUS_DEPRN_RESERVE;
176 l_mass_add_tbl (counter).AMORTIZE_NBV_FLAG := p_mass_add_rec_tbl ( counter).AMORTIZE_NBV_FLAG;
177 l_mass_add_tbl (counter).AMORTIZATION_START_DATE := p_mass_add_rec_tbl ( counter).AMORTIZATION_START_DATE;
178 l_mass_add_tbl (counter).TRANSACTION_TYPE_CODE := p_mass_add_rec_tbl ( counter).TRANSACTION_TYPE_CODE;
179 l_mass_add_tbl (counter).TRANSACTION_DATE := p_mass_add_rec_tbl ( counter).TRANSACTION_DATE;
180 l_mass_add_tbl (counter).WARRANTY_ID := p_mass_add_rec_tbl (counter).WARRANTY_ID;
181 l_mass_add_tbl (counter).LEASE_ID := p_mass_add_rec_tbl (counter).LEASE_ID;
182 l_mass_add_tbl (counter).LESSOR_ID := p_mass_add_rec_tbl (counter).LESSOR_ID;
183 l_mass_add_tbl (counter).PROPERTY_TYPE_CODE := p_mass_add_rec_tbl ( counter).PROPERTY_TYPE_CODE;
184 l_mass_add_tbl (counter).PROPERTY_1245_1250_CODE := p_mass_add_rec_tbl ( counter).PROPERTY_1245_1250_CODE;
185 l_mass_add_tbl (counter).IN_USE_FLAG := p_mass_add_rec_tbl (counter).IN_USE_FLAG;
186 l_mass_add_tbl (counter).OWNED_LEASED := p_mass_add_rec_tbl (counter).OWNED_LEASED;
187 l_mass_add_tbl (counter).NEW_USED := p_mass_add_rec_tbl (counter).NEW_USED;
188 l_mass_add_tbl (counter).ASSET_ID := p_mass_add_rec_tbl (counter).ASSET_ID;
189 l_mass_add_tbl (counter).MATERIAL_INDICATOR_FLAG := p_mass_add_rec_tbl ( counter).MATERIAL_INDICATOR_FLAG;
190 END LOOP;
191
192 FORALL i IN 1 .. l_mass_add_tbl.COUNT SAVE EXCEPTIONS
193 UPDATE fa_mass_additions
194 SET ROW = l_mass_add_tbl (i)
195 WHERE mass_addition_id = l_mass_add_id_tbl (i);
196
197 l_mass_add_id_tbl.delete;
198 COMMIT;
199 RETURN TRUE;
200 EXCEPTION
201 WHEN dml_errors THEN
202 l_errors := SQL%BULK_EXCEPTIONS.COUNT;
203
204 IF (p_log_level_rec.statement_level) THEN
205 fa_debug_pkg.add (l_calling_fn,
206 'Number of errors is ',
207 l_errors,
208 p_log_level_rec => p_log_level_rec);
209 END IF;
210
211 FOR i IN 1 .. l_errors LOOP
212 IF (p_log_level_rec.statement_level) THEN
213 fa_debug_pkg.add (l_calling_fn,
214 'Error ' || i || ' occurred during ' ||
215 'iteration ',
216 SQL%BULK_EXCEPTIONS (i).ERROR_INDEX,
217 p_log_level_rec => p_log_level_rec);
218
219 fa_debug_pkg.add (l_calling_fn,
220 'Oracle error is ',
221 SQLERRM ( -SQL%BULK_EXCEPTIONS (i).ERROR_CODE),
222 p_log_level_rec => p_log_level_rec);
223 END IF;
224 END LOOP;
225
226 COMMIT;
227 RETURN FALSE; /*need to check */
228 END;
229
230 /*===============================End Of FUNCTION/PROCEDURE===============================*/
231 /*main procedure for concurrent program*/
232 PROCEDURE prepare_mass_additions (errbuf OUT NOCOPY VARCHAR2,
233 retcode OUT NOCOPY NUMBER,
234 p_book_type_code IN VARCHAR2 ) IS
235 l_mass_add_rec FA_MASSADD_PREPARE_PKG.mass_add_rec;
236
237 l_procedure_name VARCHAR2 (4000);
238 l_label VARCHAR2 (4000);
239 l_request_id NUMBER;
240
241 l_batch_size NUMBER := 500;
242 l_count NUMBER;
243 l_debug_str VARCHAR2 (1000);
244
245 --type mass_add_dist_tbl is table of mass_add_dist_rec;
246
247 TYPE v30_tbl IS TABLE OF VARCHAR2 (30)
248 INDEX BY BINARY_INTEGER;
249
250 TYPE num_tbl IS TABLE OF NUMBER
251 INDEX BY BINARY_INTEGER;
252
253 TYPE date_tbl IS TABLE OF DATE
254 INDEX BY BINARY_INTEGER;
255
256 TYPE v100_tbl IS TABLE OF VARCHAR2 (100)
257 INDEX BY BINARY_INTEGER;
258
259 l_mass_add_rec_tbl FA_MASSADD_PREPARE_PKG.mass_add_rec_tbl;
260 --l_mass_add_dist_tbl mass_add_dist_tbl;
261
262 status VARCHAR2 (15);
263 l_dist_MASSADD_DIST_ID num_tbl;
264 l_dist_UNITS num_tbl;
265 l_dist_DEPRN_EXPENSE_CCID num_tbl;
266 l_dist_LOCATION_ID num_tbl;
267 l_dist_EMPLOYEE_ID num_tbl;
268
269 l_prev_category_id NUMBER := -1;
270 l_prev_asset_key_ccid NUMBER := -1;
271 l_curr_category_id NUMBER := 0;
272 l_curr_asset_key_ccid NUMBER := 0;
273 merge_cost NUMBER;
274 --l_distributions_table fa_mass_add_dist_tbl;
275
276 old_expense_ccid NUMBER := -1;
277 new_expense_ccid NUMBER := -1;
278
279 l_lookup_rule_value VARCHAR2 (60);
280 l_status NUMBER;
281 l_calling_fn VARCHAR2 (40) := 'prepare_mass_additions';
282 mass_prepare EXCEPTION;
283
284 --Cursor to get all mass_addition lines
285 --check about the book_type_code
286 CURSOR get_mass_add (l_book_type_code VARCHAR2) IS
287 SELECT MASS_ADDITION_ID,
288 ASSET_NUMBER,
289 TAG_NUMBER,
290 DESCRIPTION,
291 ASSET_CATEGORY_ID,
292 MANUFACTURER_NAME,
293 SERIAL_NUMBER,
294 MODEL_NUMBER,
295 BOOK_TYPE_CODE,
296 DATE_PLACED_IN_SERVICE,
297 FIXED_ASSETS_COST,
298 PAYABLES_UNITS,
299 FIXED_ASSETS_UNITS,
300 PAYABLES_CODE_COMBINATION_ID,
301 EXPENSE_CODE_COMBINATION_ID,
302 LOCATION_ID,
303 ASSIGNED_TO,
304 FEEDER_SYSTEM_NAME,
305 CREATE_BATCH_DATE,
306 CREATE_BATCH_ID,
307 LAST_UPDATE_DATE,
308 LAST_UPDATED_BY,
309 REVIEWER_COMMENTS,
310 INVOICE_NUMBER,
311 INVOICE_LINE_NUMBER, -- bug8984263
312 INVOICE_DISTRIBUTION_ID, -- bug8984263
313 VENDOR_NUMBER,
314 PO_VENDOR_ID,
315 PO_NUMBER,
316 POSTING_STATUS,
317 QUEUE_NAME,
318 INVOICE_DATE,
319 INVOICE_CREATED_BY,
320 INVOICE_UPDATED_BY,
321 PAYABLES_COST,
322 INVOICE_ID,
323 PAYABLES_BATCH_NAME,
324 DEPRECIATE_FLAG,
325 PARENT_MASS_ADDITION_ID,
326 PARENT_ASSET_ID,
327 SPLIT_MERGED_CODE,
328 AP_DISTRIBUTION_LINE_NUMBER,
329 POST_BATCH_ID,
330 ADD_TO_ASSET_ID,
331 AMORTIZE_FLAG,
332 NEW_MASTER_FLAG,
333 ASSET_KEY_CCID,
334 ASSET_TYPE,
335 DEPRN_RESERVE,
336 YTD_DEPRN,
337 BEGINNING_NBV,
338 CREATED_BY,
339 CREATION_DATE,
340 LAST_UPDATE_LOGIN,
341 SALVAGE_VALUE,
342 ACCOUNTING_DATE,
343 ATTRIBUTE1,
344 ATTRIBUTE2,
345 ATTRIBUTE3,
346 ATTRIBUTE4,
347 ATTRIBUTE5,
348 ATTRIBUTE6,
349 ATTRIBUTE7,
350 ATTRIBUTE8,
351 ATTRIBUTE9,
352 ATTRIBUTE10,
353 ATTRIBUTE11,
354 ATTRIBUTE12,
355 ATTRIBUTE13,
356 ATTRIBUTE14,
357 ATTRIBUTE15,
358 ATTRIBUTE_CATEGORY_CODE,
359 FULLY_RSVD_REVALS_COUNTER,
360 MERGE_INVOICE_NUMBER,
361 MERGE_VENDOR_NUMBER,
362 PRODUCTION_CAPACITY,
363 REVAL_AMORTIZATION_BASIS,
364 REVAL_RESERVE,
365 UNIT_OF_MEASURE,
366 UNREVALUED_COST,
367 YTD_REVAL_DEPRN_EXPENSE,
368 ATTRIBUTE16,
369 ATTRIBUTE17,
370 ATTRIBUTE18,
371 ATTRIBUTE19,
372 ATTRIBUTE20,
373 ATTRIBUTE21,
374 ATTRIBUTE22,
375 ATTRIBUTE23,
376 ATTRIBUTE24,
377 ATTRIBUTE25,
378 ATTRIBUTE26,
379 ATTRIBUTE27,
380 ATTRIBUTE28,
381 ATTRIBUTE29,
382 ATTRIBUTE30,
383 MERGED_CODE,
384 SPLIT_CODE,
385 MERGE_PARENT_MASS_ADDITIONS_ID,
386 SPLIT_PARENT_MASS_ADDITIONS_ID,
387 PROJECT_ASSET_LINE_ID,
388 PROJECT_ID,
389 TASK_ID,
390 SUM_UNITS,
391 DIST_NAME,
392 GLOBAL_ATTRIBUTE1,
393 GLOBAL_ATTRIBUTE2,
394 GLOBAL_ATTRIBUTE3,
395 GLOBAL_ATTRIBUTE4,
396 GLOBAL_ATTRIBUTE5,
397 GLOBAL_ATTRIBUTE6,
398 GLOBAL_ATTRIBUTE7,
399 GLOBAL_ATTRIBUTE8,
400 GLOBAL_ATTRIBUTE9,
401 GLOBAL_ATTRIBUTE10,
402 GLOBAL_ATTRIBUTE11,
403 GLOBAL_ATTRIBUTE12,
404 GLOBAL_ATTRIBUTE13,
405 GLOBAL_ATTRIBUTE14,
406 GLOBAL_ATTRIBUTE15,
407 GLOBAL_ATTRIBUTE16,
408 GLOBAL_ATTRIBUTE17,
409 GLOBAL_ATTRIBUTE18,
410 GLOBAL_ATTRIBUTE19,
411 GLOBAL_ATTRIBUTE20,
412 GLOBAL_ATTRIBUTE_CATEGORY,
413 CONTEXT,
414 INVENTORIAL,
415 SHORT_FISCAL_YEAR_FLAG,
416 CONVERSION_DATE,
417 ORIGINAL_DEPRN_START_DATE,
418 GROUP_ASSET_ID,
419 CUA_PARENT_HIERARCHY_ID,
420 UNITS_TO_ADJUST,
421 BONUS_YTD_DEPRN,
422 BONUS_DEPRN_RESERVE,
423 AMORTIZE_NBV_FLAG,
424 AMORTIZATION_START_DATE,
425 TRANSACTION_TYPE_CODE,
426 TRANSACTION_DATE,
427 WARRANTY_ID,
428 LEASE_ID,
429 LESSOR_ID,
430 PROPERTY_TYPE_CODE,
431 PROPERTY_1245_1250_CODE,
432 IN_USE_FLAG,
433 OWNED_LEASED,
434 NEW_USED,
435 ASSET_ID,
436 MATERIAL_INDICATOR_FLAG,
437 CAST (MULTISET (SELECT MASSADD_DIST_ID dist_id,
438 MASS_ADDITION_ID mass_add_id,
439 UNITS,
440 DEPRN_EXPENSE_CCID,
441 LOCATION_ID,
442 EMPLOYEE_ID
443 FROM FA_MASSADD_DISTRIBUTIONS mass_dist
444 WHERE mass_dist.mass_addition_id =
445 mass_add.mass_addition_id) AS fa_mass_add_dist_tbl)
446 dists
447 FROM fa_mass_additions mass_add
448 WHERE posting_status IN ('NEW', 'ON HOLD')
449 AND book_type_code = l_book_type_code
450 AND NVL (merged_code, '1') NOT IN ('MC');
451
452 CURSOR lookup_cur (c_lookup_type VARCHAR2) IS
453 SELECT lookup_code
454 FROM fa_lookups
455 WHERE lookup_type = c_lookup_type
456 AND enabled_flag = 'Y'
457 AND NVL (end_date_active, SYSDATE) >= SYSDATE
458 AND ROWNUM = 1;
459 BEGIN
460 l_procedure_name := 'fa.plsql.FA_AUTO_PREP_PKG.do_prepare_mass_addtions';
461 l_label := 'fa.plsql.FA_AUTO_PREP_PKG.do_prepare_mass_addtions.';
462
463 --Call log header
464 IF (NOT g_log_level_rec.initialized) THEN
465 IF (NOT fa_util_pub.get_log_level_rec ( x_log_level_rec => g_log_level_rec)) THEN
466 RAISE mass_prepare;
467 END IF;
468 END IF;
469
470 SAVEPOINT Work;
471
472 /* ------------------------------------------------------------------------------
473 | Get the package type for all attributes. Assumption here is that it will |
474 | return either DEFAULT or CUSTOM or ENERGY as lookup code |
475 ------------------------------------------------------------------------------
476 */
477
478 FOR rec IN lookup_cur ('MASS ADD PREPARE RULES') LOOP
479 l_lookup_rule_value := rec.lookup_code;
480 END LOOP;
481
482 /* ------------------------------------------------------------------------------
483 | Call to prepare the asset key and category. The function will internally |
484 | call either the package for common customers which will be empty stubs for |
485 | now or will call the package for Energy Cutomers which will have code to |
486 | prepare Asset Key and Category_id. |
487 ------------------------------------------------------------------------------
488 */
489
490 l_debug_str := 'Calling prepare_asset_key_category';
491
492 IF (g_log_level_rec.statement_level) THEN
493 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
494 p_log_level_rec => g_log_level_rec);
495 END IF;
496
497 IF (g_log_level_rec.statement_level) THEN
498 fa_debug_pkg.add (l_calling_fn,
499 'l_lookup_rule_value',
500 l_lookup_rule_value,
501 p_log_level_rec => g_log_level_rec);
502 END IF;
503
504 /*call the asset key function deprneding upon the package*/
505 IF (l_lookup_rule_value = 'CUSTOM ENERGY') THEN
506 IF NOT FA_MASSADD_PREP_ENERGY_PKG.prep_asset_key_category ( p_book_type_code, p_log_level_rec => g_log_level_rec) THEN
507 l_debug_str := 'Energy prepare asset key returned failure';
508 END IF;
509
510 /* ------------------------------------------------------------------------------
511 | Call to merge the mass additions lines. The functionw ill internally call |
512 | either the package for common customers which will be standard merge code |
513 | or will call the package for Energy Cutomers which will have code to merge |
514 | to merge the lines with identical Asset Key and Category_id. |
515 ------------------------------------------------------------------------------
516 */
517 l_debug_str := 'Calling merge_mass_additions';
518
519 IF (g_log_level_rec.statement_level) THEN
520 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
521 p_log_level_rec => g_log_level_rec);
522 END IF;
523
524 IF NOT FA_MASSADD_PREP_ENERGY_PKG.merge_lines (p_book_type_code,
525 p_log_level_rec => g_log_level_rec) THEN
526 l_debug_str := 'Energy merge_lines returned failure';
527 END IF;
528 END IF;
529
530 /* ------------------------------------------------------------------------------
531 | Loop through all the mass additions lines and call the main attribute |
532 | fucntion which will process the all other attributes of the mass addition |
533 | lines. The fucntion will internally call either the package for the common |
534 | customers or the package for energy customers which will have an extra call |
535 | to the group function which will further call the Create Summary Assets |
536 | fucntion to process Create Summary Asset. |
537 ------------------------------------------------------------------------------
538 */
539 l_debug_str := 'Processing mass additons lines for other attributes';
540
541 --Open the cursor for the mass additions
542 OPEN GET_MASS_ADD (p_book_type_code);
543
544 -- Process all the records
545 WHILE TRUE LOOP
546 l_debug_str := 'In Loop';
547
548 --fetch the records as per batch size
549 FETCH GET_MASS_ADD
550 BULK COLLECT INTO l_mass_add_rec_tbl
551 LIMIT l_batch_size;
552
553 --exit from the loop if no more records
554 IF (GET_MASS_ADD%NOTFOUND) AND (l_mass_add_rec_tbl.COUNT < 1) THEN
555 EXIT;
556 END IF;
557
558 --Loop to get process each mass addition line
559 FOR l_count IN 1 .. l_mass_add_rec_tbl.COUNT LOOP
560 l_debug_str := 'Calling prepare_attributes';
561
562 IF (g_log_level_rec.statement_level) THEN
563 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
564 p_log_level_rec => g_log_level_rec);
565 END IF;
566
567 IF (l_lookup_rule_value = 'DEFAULT') THEN
568 IF NOT FA_MASSADD_PREP_DEFAULT_PKG.prepare_attributes ( l_mass_add_rec_tbl ( l_count), p_log_level_rec => g_log_level_rec) THEN
569 l_debug_str := 'Custom prepare attributes returned failure';
570
571 IF (g_log_level_rec.statement_level) THEN
572 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
573 p_log_level_rec => g_log_level_rec);
574 END IF;
575 END IF;
576 ELSIF (l_lookup_rule_value = 'CUSTOM') THEN
577 IF NOT FA_MASSADD_PREP_CUSTOM_PKG.prepare_attributes ( l_mass_add_rec_tbl ( l_count), p_log_level_rec => g_log_level_rec) THEN
578 l_debug_str := 'Custom prepare attributes returned failure';
579
580 IF (g_log_level_rec.statement_level) THEN
581 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
582 p_log_level_rec => g_log_level_rec);
583 END IF;
584 END IF;
585 ELSIF (l_lookup_rule_value = 'CUSTOM ENERGY') THEN
586 IF NOT FA_MASSADD_PREP_ENERGY_PKG.prepare_attributes ( l_mass_add_rec_tbl ( l_count), p_log_level_rec => g_log_level_rec) THEN
587 l_debug_str := 'Energy prepare attributes returned failure';
588
589 IF (g_log_level_rec.statement_level) THEN
590 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
591 p_log_level_rec => g_log_level_rec);
592 END IF;
593 END IF;
594 END IF;
595 END LOOP;
596
597 l_debug_str := 'Calling update_mass_additions';
598
599 IF (g_log_level_rec.statement_level) THEN
600 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
601 p_log_level_rec => g_log_level_rec);
602 END IF;
603
604 IF NOT update_mass_additions (l_mass_add_rec_tbl,
605 p_log_level_rec => g_log_level_rec) THEN
606 l_debug_str := 'error in update_mass_additions';
607
608 IF (g_log_level_rec.statement_level) THEN
609 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
610 p_log_level_rec => g_log_level_rec);
611 END IF;
612 END IF;
613 END LOOP;
614
615 COMMIT;
616 retcode := 0;
617 EXCEPTION
618 WHEN OTHERS THEN
619 retcode := 2;
620 ROLLBACK;
621 FA_SRVR_MSG.ADD_MESSAGE ( CALLING_FN => 'FA_MASSADD_PREPARE_PKG.prepare_mass_additions', p_log_level_rec => g_log_level_rec);
622 END;
623 /*===============================End Of FUNCTION/PROCEDURE===============================*/
624 END FA_MASSADD_PREPARE_PKG;