[Home] [Help]
PACKAGE BODY: APPS.CSE_ASSET_WIP_PKG
Source
1 PACKAGE BODY CSE_ASSET_WIP_PKG AS
2 -- $Header: CSEFAWPB.pls 115.5 2003/01/10 21:02:48 nnewadka noship $
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'),'N');
5
6 --------------------------------------------------------------------------------
7 ---Finds the immediate children from Installed Base configuration
8 ---For each depreciable component, perfroms unit and cost adjustment
9 --------------------------------------------------------------------------------
10 PROCEDURE update_comp_assets(
11 p_top_instance_id IN NUMBER
12 , x_return_status OUT NOCOPY VARCHAR2
13 , x_error_msg OUT NOCOPY VARCHAR2
14 )
15 IS
16 l_relationship_tbl csi_datastructures_pub.ii_relationship_tbl ;
17 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec ;
18 l_return_status VARCHAR2(10);
19 l_msg_count NUMBER ;
20 l_msg_data VARCHAR2(2000);
21 l_error_message VARCHAR2(2000);
22 l_msg_index NUMBER ;
23 l_depreciable VARCHAR2(1);
24 l_sysdate DATE ;
25 l_unit_asset_cost NUMBER ;
26 l_total_asset_cost NUMBER ;
27 l_total_asset_units NUMBER ;
28 l_inst_units_to_be_adjusted NUMBER ;
29 l_adjust_units NUMBER ;
30 l_cost_to_adjust NUMBER ;
31 l_wip_txn_id NUMBER ;
32
33 e_error_exception EXCEPTION ;
34 CURSOR get_item_instance_cur (c_comp_instance_id IN NUMBER)
35 IS
36 SELECT instance_id,
37 instance_usage_code,
38 inventory_item_id,
39 quantity,
40 serial_number
41 FROM csi_item_instances
42 WHERE instance_id = c_comp_instance_id ;
43
44 CURSOR get_wip_txn_id_cur(c_instance_id IN NUMBER)
45 IS
46 SELECT transaction_id,
47 ABS(quantity)
48 FROM csi_inst_txn_details_v
49 WHERE instance_id = c_instance_id
50 AND source_transaction_type = 'WIP_ISSUE' ;
51
52 CURSOR get_instance_assets_cur (c_instance_id IN NUMBER)
53 IS
54 SELECT cia.fa_asset_id,
55 cia.fa_book_type_code,
56 cia.fa_location_id,
57 fdh.units_assigned,
58 cia.update_status,
59 fdh.code_combination_id,
60 fdh.assigned_to
61 FROM csi_i_assets cia,
62 fa_distribution_history fdh
63 WHERE instance_id = c_instance_id
64 AND update_status = 'IN_SERVICE'
65 AND asset_quantity > 0
66 AND TRUNC(active_start_date) <= l_sysdate
67 AND NVL(TRUNC(active_end_date), l_sysdate) >= l_sysdate
68 AND fdh.asset_id = cia.fa_asset_id
69 AND fdh.book_type_code = cia.fa_book_type_code
70 AND fdh.location_id = cia.fa_location_id
71 AND fdh.date_ineffective IS NULL
72 ORDER BY cia.fa_asset_id ;
73
74 CURSOR get_asset_unit_cost_cur (c_book_type_code IN VARCHAR2,
75 c_asset_id IN NUMBER)
76 IS
77 SELECT fab.cost,
78 faa.current_units
79 FROM fa_additions faa,
80 fa_books fab
81 WHERE fab.book_type_code = c_book_type_code
82 AND fab.asset_id = c_asset_id
83 AND faa.asset_id = fab.asset_id ;
84
85 BEGIN
86 cse_util_pkg.write_log('Begin update_comp_assets for top instance :'
87 || p_top_instance_id);
88 SELECT TRUNC(SYSDATE) INTO l_sysdate FROM DUAL ;
89 ---get all the immediate children of p_top_instance_id
90 l_relationship_query_rec.object_id := p_top_instance_id ;
91 l_relationship_query_rec.relationship_type_code := 'COMPONENT-OF' ;
92
93 csi_ii_relationships_pub.get_relationships(
94 p_api_version => 1.0,
95 p_commit => fnd_api.g_false,
96 p_init_msg_list => fnd_api.g_true,
97 p_validation_level => fnd_api.g_valid_level_full,
98 p_relationship_query_rec => l_relationship_query_rec,
99 p_depth => 1,
100 p_time_stamp => null ,
101 p_active_relationship_only => fnd_api.g_true,
102 x_relationship_tbl => l_relationship_tbl,
103 x_return_status => l_return_status,
104 x_msg_count => l_msg_count,
105 x_msg_data => l_msg_data );
106
107 cse_util_pkg.write_log('l_return_status After calling get_relationships :'||l_return_status);
108 IF l_return_status <> FND_API.G_Ret_Sts_Success
109 THEN
110 l_msg_index := 1;
111 l_error_message := l_msg_data;
112 WHILE l_msg_count > 0
113 LOOP
114 l_error_message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE)||l_error_message;
115 l_msg_index := l_msg_index + 1;
116 l_Msg_Count := l_Msg_Count - 1;
117 END LOOP;
118 END IF;
119
120 IF l_relationship_tbl.COUNT > 0
121 THEN
122 FOR i IN 1..l_relationship_tbl.COUNT
123 LOOP
124 cse_util_pkg.write_log('This is IN_RELATIONSHIP Instance ID :'||
125 l_relationship_tbl(i).subject_id);
126
127 OPEN get_wip_txn_id_cur(l_relationship_tbl(i).subject_id) ;
128 FETCH get_wip_txn_id_cur INTO l_wip_txn_id ,l_inst_units_to_be_adjusted ;
129 CLOSE get_wip_txn_id_cur ;
130 cse_util_pkg.write_log('CSI-WIP Transaction ID:'|| l_wip_txn_id);
131
132 FOR get_item_instance_rec IN
133 get_item_instance_cur(l_relationship_tbl(i).subject_id)
134 LOOP
135 --is item depreciable?
136 cse_util_pkg.write_log('This is component : '||l_relationship_tbl(i).subject_id);
137 cse_util_pkg.check_depreciable(get_item_instance_rec.inventory_item_id
138 ,l_depreciable);
139 cse_util_pkg.write_log('After checking item depreciable :'|| l_depreciable);
140 IF l_depreciable = 'Y'
141 THEN
142 cse_util_pkg.write_log('Total asset units to be adjusted :'||
143 l_inst_units_to_be_adjusted);
144 ---get asset associated with this instance
145 FOR get_instance_assets_rec IN
146 get_instance_assets_cur (get_item_instance_rec.instance_id)
147 LOOP
148 IF l_inst_units_to_be_adjusted > 0
149 THEN
150
151 --Get, how many asset units to adjust
152 IF l_inst_units_to_be_adjusted <= get_instance_assets_rec.units_assigned
153 THEN
154 l_adjust_units := l_inst_units_to_be_adjusted ;
155 l_inst_units_to_be_adjusted := 0;
156 ELSE
157 l_adjust_units := get_instance_assets_rec.units_assigned ;
158 l_inst_units_to_be_adjusted :=
159 l_inst_units_to_be_adjusted- l_adjust_units ;
160 END IF ;
161
162 cse_util_pkg.write_log('l_adjust_units: '|| l_adjust_units);
163 OPEN get_asset_unit_cost_cur (get_instance_assets_rec.fa_book_type_code ,
164 get_instance_assets_rec.fa_asset_id );
165 FETCH get_asset_unit_cost_cur INTO l_total_asset_cost,
166 l_total_asset_units ;
167 CLOSE get_asset_unit_cost_cur ;
168
169 cse_util_pkg.write_log('l_total_asset_cost: '|| l_total_asset_cost);
170 cse_util_pkg.write_log('l_total_asset_units: '|| l_total_asset_units);
171 l_cost_to_adjust := ROUND((l_total_asset_cost/l_total_asset_units)
172 *l_adjust_units ,2);
173
174 l_adjust_units := (-1)*l_adjust_units ;
175 l_cost_to_adjust := (-1)*l_cost_to_adjust ;
176
177 IF get_item_instance_rec.serial_number is NOT NULL
178 THEN
179 l_adjust_units := NULL ;
180 END IF ;
181
182 adjust_fa_cost_n_unit(
183 p_asset_id => get_instance_assets_rec.fa_asset_id
184 ,p_book_type_code => get_instance_assets_rec.fa_book_type_code
185 ,p_location_id => get_instance_assets_rec.fa_location_id
186 ,p_expense_ccid => get_instance_assets_rec.code_combination_id
187 ,p_employee_id => get_instance_assets_rec.assigned_to
188 ,p_unit_to_adjust => l_adjust_units
189 ,p_cost_to_adjust => l_cost_to_adjust
190 ,p_reviewer_comments => get_item_instance_rec.instance_id
191 ,x_error_msg => l_error_message
192 ,x_return_status => l_return_status );
193
194 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
195 THEN
196 RAISE e_error_exception ;
197 END IF ;
198 ELSE
199 EXIT ;
200 END IF ; ---l_inst_units_to_be_adjusted
201 END LOOP ; --get_instance_assets_rec
202 END IF ;
203 END LOOP ; --get_item_instance_rec
204 END LOOP ; --1..l_relationship_tbl.COUNT
205 END IF ; --l_relationship_tbl.COUNT > 0
206
207 END update_comp_assets ;
208
209 --------------------------------------------------------------------------------
210 ---Insert records into FA_MASS_ADDITIONS for COST
211 ---and UNIT adjustment
212 --------------------------------------------------------------------------------
213 PROCEDURE adjust_fa_cost_n_unit(
214 p_asset_id IN NUMBER
215 , p_book_type_code IN VARCHAR2
216 , p_location_id IN NUMBER
217 , p_expense_ccid IN NUMBER
218 , p_employee_id IN NUMBER
219 , p_unit_to_adjust IN NUMBER
220 , p_cost_to_adjust IN NUMBER
221 , p_reviewer_comments IN VARCHAR2
222 , x_return_status OUT NOCOPY VARCHAR2
223 , x_error_msg OUT NOCOPY VARCHAR2
224 )
225 IS
226 l_mass_add_rec fa_mass_additions%ROWTYPE := NULL ;
227 l_sysdate DATE ;
228 l_return_status VARCHAR2(1) ;
229 l_msg_count NUMBER ;
230 l_msg_data VARCHAR2(2000);
231 l_error_msg VARCHAR2(2000);
232 l_new_dist_id NUMBER ;
233
234 CURSOR fa_book_cur
235 IS
236 SELECT fab.date_placed_in_service ,
237 faa.description,
238 faa.asset_category_id,
239 faa.asset_key_ccid
240 FROM fa_books fab ,
241 fa_additions faa
242 WHERE fab.book_type_code = p_book_type_code
243 AND fab.asset_id = p_asset_id
244 AND faa.asset_id = fab.asset_id
245 AND fab.date_ineffective IS NULL ;
246
247 e_error EXCEPTION ;
248 BEGIN
249 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
250 cse_util_pkg.write_log ('Asset ID : '||p_asset_id);
251 cse_util_pkg.write_log ('Book Type: '||p_book_type_code);
252 cse_util_pkg.write_log ('FA Location : '||p_location_id);
253 cse_util_pkg.write_log ('Expense CCID :'|| p_expense_ccid);
254 cse_util_pkg.write_log ('Employee ID:'|| p_employee_id);
255 cse_util_pkg.write_log ('Unit to adjust :'|| p_unit_to_adjust);
256 cse_util_pkg.write_log ('Cost to adjust :'|| p_cost_to_adjust);
257 cse_util_pkg.write_log ('p_reviewer_comments : '|| p_reviewer_comments);
258
259 FOR fa_book_rec IN fa_book_cur
260 LOOP
261 l_mass_add_rec.date_placed_in_service := fa_book_rec.date_placed_in_service ;
262 l_mass_add_rec.description := fa_book_rec.description ;
263 l_mass_add_rec.asset_category_id := fa_book_rec.asset_category_id ;
264 l_mass_add_rec.asset_key_ccid := fa_book_rec.asset_key_ccid ;
265 END LOOP ;
266
267 SELECT SYSDATE INTO l_sysdate FROM DUAL ;
268
269 l_mass_add_rec.payables_cost := p_cost_to_adjust ;
270 l_mass_add_rec.fixed_assets_cost := p_cost_to_adjust ;
271 l_mass_add_rec.payables_units := p_unit_to_adjust ;
272 l_mass_add_rec.fixed_assets_units := p_unit_to_adjust ;
273 l_mass_add_rec.reviewer_comments := p_reviewer_comments ;
274 l_mass_add_rec.book_type_code := p_book_type_code ;
275 l_mass_add_rec.location_id := p_location_id ;
276 l_mass_add_rec.expense_code_combination_id := p_expense_ccid ;
277 l_mass_add_rec.assigned_to := p_employee_id ;
278 l_mass_add_rec.add_to_asset_id := p_asset_id ;
279 ----l_mass_add_rec.units_to_adjust := p_unit_to_adjust ;
280 l_mass_add_rec.feeder_system_name := cse_asset_util_pkg.G_FA_FEEDER_NAME;
281
282 l_mass_add_rec.queue_name := 'ADD TO ASSET' ;
283 l_mass_add_rec.posting_status := 'POST' ;
284 l_mass_add_rec.asset_type := 'CAPITALIZED' ;
285 l_mass_add_rec.depreciate_flag := 'YES' ;
286 l_mass_add_rec.creation_date := l_sysdate;
287 l_mass_add_rec.last_update_date := l_sysdate;
288 l_mass_add_rec.created_by := fnd_global.user_id ;
289 l_mass_add_rec.last_updated_by := fnd_global.user_id ;
290 l_mass_add_rec.last_update_login := fnd_global.login_id ;
291 l_mass_add_rec.last_update_login := fnd_global.login_id ;
292
293 cse_asset_util_pkg.insert_mass_add(
294 p_api_version => 1.0
295 ,p_commit => FND_API.G_FALSE
296 ,p_init_msg_list => FND_API.G_TRUE
297 ,p_mass_add_rec => l_mass_add_rec
298 ,x_return_status => l_return_status
299 ,x_msg_count => l_msg_count
300 ,x_msg_data => l_msg_data );
301
302 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
303 THEN
304 l_error_msg := l_msg_data ;
305 RAISE e_error ;
306 END IF ;
307
308 IF p_unit_to_adjust IS NOT NULL
309 THEN
310 --Now call adjust_fa_distribution for UNIT adjustment
311 cse_ifa_trans_pkg.adjust_fa_distribution
312 (p_asset_id => p_asset_id,
313 p_book_type_code => p_book_type_code,
314 p_units => p_unit_to_adjust,
315 p_location_id => p_location_id,
316 p_expense_ccid => p_expense_ccid,
317 p_employee_id => p_employee_id,
318 x_new_dist_id => l_new_dist_id,
319 x_return_status => l_return_status,
320 x_error_msg => l_error_msg);
321
322 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
323 THEN
324 RAISE e_error ;
325 END IF ;
326 END IF ;
327
328 EXCEPTION
329 WHEN e_error THEN
330 x_return_status := fnd_api.G_RET_STS_ERROR ;
331 x_error_msg := l_error_msg ;
332 cse_util_pkg.write_log('Error in adjust_fa_cost_n_unit :'|| x_error_msg);
333 WHEN OTHERS THEN
334 x_return_status := fnd_api.G_RET_STS_ERROR ;
335 x_error_msg := SQLERRM ;
336 cse_util_pkg.write_log('Error in adjust_fa_cost_n_unit :'|| x_error_msg);
337 END adjust_fa_cost_n_unit ;
338
339 END CSE_ASSET_WIP_PKG ;