[Home] [Help]
PACKAGE BODY: APPS.CSE_DEPLOYMENT_GRP
Source
1 PACKAGE BODY cse_deployment_grp AS
2 /* $Header: CSEDPLGB.pls 120.21 2006/06/27 07:51:46 brmanesh noship $ */
3
4 l_debug varchar2(1) := NVL(fnd_profile.value('cse_debug_option'),'N');
5
6 PROCEDURE debug(
7 p_message IN varchar2)
8 IS
9 BEGIN
10 IF l_debug = 'Y' THEN
11 cse_debug_pub.add(p_message);
12 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
13 fnd_file.put_line(fnd_file.log,p_message);
14 END IF;
15 END IF;
16
17 EXCEPTION
18 WHEN others THEN
19 null;
20 END debug;
21
22 PROCEDURE process_retirements(
23 p_instance_id IN number,
24 p_asset_id IN number,
25 p_proceeds_of_sale IN number,
26 p_cost_of_removal IN number,
27 p_operational_flag IN varchar2 default 'N',
28 p_financial_flag IN varchar2 default 'N',
29 px_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
30 x_return_status OUT nocopy varchar2)
31 IS
32
33 CURSOR inst_asset_cur IS
34 SELECT instance_asset_id,
35 instance_id,
36 fa_asset_id,
37 fa_book_type_code,
38 fa_location_id
39 FROM csi_i_assets
40 WHERE instance_id = p_instance_id
41 AND fa_asset_id = p_asset_id
42 AND asset_quantity > 0
43 AND sysdate between nvl(active_start_date, sysdate-1) and nvl(active_end_date, sysdate+1);
44
45 l_inst_asset_found boolean := FALSE;
46 l_asset_id number;
47
48 l_location_type_code varchar2(30);
49 l_operational_status_code varchar2(30);
50 l_instance_usage_code varchar2(30);
51 l_accounting_class_code varchar2(30);
52 l_quantity number;
53 l_last_vld_organization_id number;
54 l_object_version_number number;
55
56
57 l_source_instance_rec csi_datastructures_pub.instance_rec;
58 l_new_instance_rec csi_datastructures_pub.instance_rec;
59 l_source_instance_qty number;
60 l_new_instance_qty number;
61
62 l_u_instance_rec csi_datastructures_pub.instance_rec;
63 l_u_parties_tbl csi_datastructures_pub.party_tbl;
64 l_u_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
65 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
66 l_u_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
67 l_u_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
68 l_u_assets_tbl csi_datastructures_pub.instance_asset_tbl;
69 l_instance_ids_list csi_datastructures_pub.id_tbl;
70
71 l_msg_data varchar2(2000);
72 l_msg_count number;
73 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
74 l_error_message varchar2(2000);
75
76 BEGIN
77
78 x_return_status := fnd_api.g_ret_sts_success;
79
80 debug('inside api process_retirements');
81 debug(' p_instance_id : '||p_instance_id);
82 debug(' p_asset_id : '||p_asset_id);
83 debug(' p_operational_flag : '||p_operational_flag);
84 debug(' p_financial_flag : '||p_financial_flag);
85
86 savepoint process_retirements;
87
88 IF p_financial_flag = 'Y' THEN
89 FOR inst_asset_rec in inst_asset_cur
90 LOOP
91
92 cse_fa_txn_pkg.asset_retirement(
93 p_instance_id => inst_asset_rec.instance_id,
94 p_book_type_code => inst_asset_rec.fa_book_type_code,
95 p_asset_id => inst_asset_rec.fa_asset_id,
96 p_units => px_txn_rec.transaction_quantity,
97 p_trans_date => px_txn_rec.source_transaction_date,
98 p_trans_by => px_txn_rec.transacted_by,
99 px_txn_rec => px_txn_rec,
100 x_return_status => l_return_status,
101 x_error_message => l_error_message);
102
103 IF l_return_status <> fnd_api.g_ret_sts_success THEN
104 RAISE fnd_api.g_exc_error;
105 END IF;
106
107 cse_fa_txn_pkg.populate_retirement_interface(
108 p_csi_txn_id => px_txn_rec.transaction_id,
109 p_asset_id => inst_asset_rec.fa_asset_id,
110 p_book_type_code => inst_asset_rec.fa_book_type_code,
111 p_fa_location_id => inst_asset_rec.fa_location_id,
112 p_proceeds_of_sale => p_proceeds_of_sale,
113 p_cost_of_removal => p_cost_of_removal,
114 p_retirement_units => px_txn_rec.transaction_quantity,
115 p_retirement_date => px_txn_rec.source_transaction_date,
116 x_return_status => l_return_status);
117
118 IF l_return_status <> fnd_api.g_ret_sts_success THEN
119 RAISE fnd_api.g_exc_error;
120 END IF;
121
122 END LOOP;
123 END IF;
124
125 IF p_operational_flag = 'Y' THEN
126 debug('Operational Update :-');
127
128 SELECT location_type_code,
129 operational_status_code,
130 instance_usage_code,
131 accounting_class_code,
132 quantity,
133 last_vld_organization_id,
134 object_version_number
135 INTO l_location_type_code,
136 l_operational_status_code,
137 l_instance_usage_code,
138 l_accounting_class_code,
139 l_quantity,
140 l_last_vld_organization_id,
141 l_object_version_number
142 FROM csi_item_instances
143 WHERE instance_id = p_instance_id;
144
145 debug(' instance_usage_code : '||l_instance_usage_code);
146 debug(' acct_class_code : '||l_accounting_class_code);
147 debug(' location_type_code : '||l_location_type_code);
148 debug(' instance_quantity : '||l_quantity);
149
150 FOR inst_asset_rec IN inst_asset_cur
151 LOOP
152 l_inst_asset_found := TRUE;
153 l_asset_id := inst_asset_rec.fa_asset_id;
154 exit;
155 END LOOP;
156
157 -- operational retirement without financial check for cia link
158 IF l_instance_usage_code = 'IN_SERVICE' OR l_accounting_class_code = 'ASSET' THEN
159 IF p_financial_flag = 'N' THEN
160
161 IF l_inst_asset_found THEN
162 fnd_message.set_name('CSE', 'CSE_WFM_RETIRE_FLAG_ERROR');
163 fnd_message.set_token('ASSET_ID',l_asset_id);
164 fnd_msg_pub.add;
165 RAISE fnd_api.g_exc_error;
166 END IF;
167
168 END IF;
169 END IF; -- in_service or acct_class_code = 'ASSET'
170
171 IF l_location_type_code = 'PROJECT' THEN
172 IF NOT(l_inst_asset_found) THEN
173 fnd_message.set_name('CSE', 'CSE_WFM_RETIRE_CIP_ERROR');
174 fnd_msg_pub.add;
175 RAISE fnd_api.g_exc_error;
176 END IF;
177 ELSIF l_location_type_code IN ('INVENTORY', 'WIP') THEN
178 fnd_message.set_name('CSE', 'CSE_WFM_RETIRE_INT_ERROR');
179 fnd_message.set_token('INST_ID', p_instance_id);
180 fnd_msg_pub.add;
181 RAISE fnd_api.g_exc_error;
182 END IF;
183
184 IF l_quantity > px_txn_rec.transaction_quantity THEN
185 -- partial retirement
186 -- split
187 l_source_instance_rec.instance_id := p_instance_id;
188 l_source_instance_rec.vld_organization_id := l_last_vld_organization_id;
189 l_source_instance_rec.object_version_number := l_object_version_number;
190
191 l_source_instance_qty := l_quantity - px_txn_rec.transaction_quantity;
192 l_new_instance_qty := px_txn_rec.transaction_quantity;
193
194 debug('Calling API csi_item_instance_pvt.split_item_instance');
195
196 csi_item_instance_pvt.split_item_instance (
197 p_api_version => 1.0,
198 p_commit => fnd_api.g_false,
199 p_init_msg_list => fnd_api.g_true,
200 p_validation_level => fnd_api.g_valid_level_full,
201 p_source_instance_rec => l_source_instance_rec,
202 p_quantity1 => l_source_instance_qty,
203 p_quantity2 => l_new_instance_qty,
204 p_copy_ext_attribs => fnd_api.g_true,
205 p_copy_org_assignments => fnd_api.g_true,
206 p_copy_parties => fnd_api.g_true,
207 p_copy_accounts => fnd_api.g_true,
208 p_copy_asset_assignments => fnd_api.g_false,
209 p_copy_pricing_attribs => fnd_api.g_true,
210 p_txn_rec => px_txn_rec,
211 x_new_instance_rec => l_new_instance_rec,
212 x_return_status => l_return_status,
213 x_msg_count => l_msg_count,
214 x_msg_data => l_msg_data);
215
216 IF l_return_status <> fnd_api.g_ret_sts_success THEN
217 RAISE fnd_api.g_exc_error;
218 END IF;
219
220 debug(' new_instance_id : '||l_new_instance_rec.instance_id);
221 debug(' new_instance_quantity: '||l_new_instance_rec.quantity);
222
223 l_u_instance_rec.instance_id := l_new_instance_rec.instance_id;
224
225 ELSE
226 -- full retirement
227 l_u_instance_rec.instance_id := p_instance_id;
228 END IF;
229
230
231 SELECT object_version_number
232 INTO l_u_instance_rec.object_version_number
233 FROM csi_item_instances
234 WHERE instance_id = l_u_instance_rec.instance_id;
235
236 l_u_instance_rec.active_end_date := sysdate;
237 l_u_instance_rec.instance_usage_code := 'OUT_OF_SERVICE';
238 l_u_instance_rec.operational_status_code := 'OUT_OF_SERVICE';
239
240 debug('Calling API csi_item_instance_pub.update_item_instance');
241 debug(' instance_id : '||l_u_instance_rec.instance_id);
242 debug(' active_end_date : '||l_u_instance_rec.active_end_date);
243 debug(' instance_usage_code : '||l_u_instance_rec.instance_usage_code);
244 debug(' operation_status_code : '||l_u_instance_rec.operational_status_code);
245
246 csi_item_instance_pub.update_item_instance(
247 p_api_version => 1.0,
248 p_commit => fnd_api.g_false,
249 p_init_msg_list => fnd_api.g_true,
250 p_validation_level => fnd_api.g_valid_level_full,
251 p_instance_rec => l_u_instance_rec,
252 p_party_tbl => l_u_parties_tbl,
253 p_account_tbl => l_u_pty_accts_tbl,
254 p_org_assignments_tbl => l_u_org_units_tbl,
255 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
256 p_pricing_attrib_tbl => l_u_pricing_tbl,
257 p_asset_assignment_tbl => l_u_assets_tbl,
258 p_txn_rec => px_txn_rec,
259 x_instance_id_lst => l_instance_ids_list,
260 x_return_status => l_return_status,
261 x_msg_count => l_msg_count,
262 x_msg_data => l_msg_data);
263
264 IF l_return_status <> fnd_api.g_ret_sts_success THEN
265 RAISE fnd_api.g_exc_error;
266 END IF;
267
268 END IF; -- operational_flag = 'Y'
269
270 EXCEPTION
271 WHEN fnd_api.g_exc_error THEN
272 rollback to process_retirements;
273 x_return_status := fnd_api.g_ret_sts_error;
274 END process_retirements;
275
276 PROCEDURE rebuild_child_entities(
277 p_instance_id IN number,
278 x_t_party_tbl OUT nocopy csi_process_txn_grp.txn_i_parties_tbl,
279 x_t_pty_acct_tbl OUT nocopy csi_process_txn_grp.txn_ip_accounts_tbl,
280 x_t_ou_tbl OUT nocopy csi_process_txn_grp.txn_org_units_tbl,
281 x_t_price_tbl OUT nocopy csi_process_txn_grp.txn_pricing_attribs_tbl,
282 x_return_status OUT nocopy varchar2)
283 IS
284
285 -- giid variables
286 l_inst_rec csi_datastructures_pub.instance_header_rec ;
287 l_pty_tbl csi_datastructures_pub.party_header_tbl ;
288 l_pty_acct_tbl csi_datastructures_pub.party_account_header_tbl ;
289 l_org_tbl csi_datastructures_pub.org_units_header_tbl ;
290 l_price_tbl csi_datastructures_pub.pricing_attribs_tbl ;
291 l_ea_tbl csi_datastructures_pub.extend_attrib_tbl ;
292 l_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl ;
293 l_ia_tbl csi_datastructures_pub.instance_asset_header_tbl;
294 l_time_stamp date := null;
295
296 -- out variables build
297 l_t_pty_tbl csi_process_txn_grp.txn_i_parties_tbl ;
298 l_t_pty_acct_tbl csi_process_txn_grp.txn_ip_accounts_tbl ;
299 l_t_org_tbl csi_process_txn_grp.txn_org_units_tbl ;
300 l_t_price_tbl csi_process_txn_grp.txn_pricing_attribs_tbl ;
301
302 xp_ind binary_integer := 0;
303 xpa_ind binary_integer := 0;
304 xo_ind binary_integer := 0;
305 xpr_ind binary_integer := 0;
306 xr_ind binary_integer := 0;
307
308 l_msg_data varchar2(2000);
309 l_msg_count number;
310 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
311
312 BEGIN
313
314 x_return_status := fnd_api.g_ret_sts_success;
315
316 IF nvl(p_instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
317
318 l_inst_rec.instance_id := p_instance_id;
319
320 csi_item_instance_pub.get_item_instance_details(
321 p_api_version => 1.0,
322 p_commit => fnd_api.g_false,
323 p_init_msg_list => fnd_api.g_true,
324 p_validation_level => fnd_api.g_valid_level_full,
325 p_instance_rec => l_inst_rec,
326 p_get_parties => fnd_api.g_true,
327 p_party_header_tbl => l_pty_tbl,
328 p_get_accounts => fnd_api.g_true,
329 p_account_header_tbl => l_pty_acct_tbl,
330 p_get_org_assignments => fnd_api.g_true,
331 p_org_header_tbl => l_org_tbl,
332 p_get_pricing_attribs => fnd_api.g_true,
333 p_pricing_attrib_tbl => l_price_tbl,
334 p_get_ext_attribs => fnd_api.g_false,
335 p_ext_attrib_tbl => l_eav_tbl,
336 p_ext_attrib_def_tbl => l_ea_tbl,
337 p_get_asset_assignments => fnd_api.g_false,
338 p_asset_header_tbl => l_ia_tbl,
339 p_resolve_id_columns => fnd_api.g_false,
340 p_time_stamp => l_time_stamp,
341 x_return_status => l_return_status,
342 x_msg_count => l_msg_count,
343 x_msg_data => l_msg_data );
344 IF l_return_status <> fnd_api.g_ret_sts_success THEN
345 RAISE fnd_api.g_exc_error;
346 END IF;
347
348 IF l_pty_tbl.count > 0 THEN
349
350 FOR p_ind IN l_pty_tbl.FIRST .. l_pty_tbl.LAST
351 LOOP
352 IF nvl(l_pty_tbl(p_ind).active_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
353
354 xp_ind := xp_ind + 1;
355
356 l_t_pty_tbl(xp_ind).instance_party_id := fnd_api.g_miss_num;
357 l_t_pty_tbl(xp_ind).parent_tbl_index := 1;
358 l_t_pty_tbl(xp_ind).instance_id := p_instance_id;
359 l_t_pty_tbl(xp_ind).party_source_table := l_pty_tbl(p_ind).party_source_table;
360 l_t_pty_tbl(xp_ind).party_id := l_pty_tbl(p_ind).party_id;
361 l_t_pty_tbl(xp_ind).relationship_type_code:= l_pty_tbl(p_ind).relationship_type_code;
362 l_t_pty_tbl(xp_ind).contact_flag := l_pty_tbl(p_ind).contact_flag;
363 l_t_pty_tbl(xp_ind).contact_ip_id := l_pty_tbl(p_ind).contact_ip_id;
364 l_t_pty_tbl(xp_ind).active_start_date := fnd_api.g_miss_date;
365 l_t_pty_tbl(xp_ind).active_end_date := fnd_api.g_miss_date;
366 l_t_pty_tbl(xp_ind).context := l_pty_tbl(p_ind).context;
367 l_t_pty_tbl(xp_ind).attribute1 := l_pty_tbl(p_ind).attribute1;
368 l_t_pty_tbl(xp_ind).attribute2 := l_pty_tbl(p_ind).attribute2;
369 l_t_pty_tbl(xp_ind).attribute3 := l_pty_tbl(p_ind).attribute3;
370 l_t_pty_tbl(xp_ind).attribute4 := l_pty_tbl(p_ind).attribute4;
371 l_t_pty_tbl(xp_ind).attribute5 := l_pty_tbl(p_ind).attribute5;
372 l_t_pty_tbl(xp_ind).attribute6 := l_pty_tbl(p_ind).attribute6;
373 l_t_pty_tbl(xp_ind).attribute7 := l_pty_tbl(p_ind).attribute7;
374 l_t_pty_tbl(xp_ind).attribute8 := l_pty_tbl(p_ind).attribute8;
375 l_t_pty_tbl(xp_ind).attribute9 := l_pty_tbl(p_ind).attribute9;
376 l_t_pty_tbl(xp_ind).attribute10 := l_pty_tbl(p_ind).attribute10;
377 l_t_pty_tbl(xp_ind).attribute11 := l_pty_tbl(p_ind).attribute11;
378 l_t_pty_tbl(xp_ind).attribute12 := l_pty_tbl(p_ind).attribute12;
379 l_t_pty_tbl(xp_ind).attribute13 := l_pty_tbl(p_ind).attribute13;
380 l_t_pty_tbl(xp_ind).attribute14 := l_pty_tbl(p_ind).attribute14;
381 l_t_pty_tbl(xp_ind).attribute15 := l_pty_tbl(p_ind).attribute15;
382 l_t_pty_tbl(xp_ind).object_version_number := 1;
383
384 IF l_pty_acct_tbl.COUNT > 0 THEN
385 FOR pa_ind IN l_pty_acct_tbl.FIRST .. l_pty_acct_tbl.LAST
386 LOOP
387
388 IF l_pty_acct_tbl(pa_ind).instance_party_id = l_pty_tbl(p_ind).instance_party_id
389 AND
390 nvl(l_pty_acct_tbl(pa_ind).active_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date
391 THEN
392 xpa_ind := xpa_ind + 1;
393 l_t_pty_acct_tbl(xpa_ind).ip_account_id := fnd_api.g_miss_num;
394 l_t_pty_acct_tbl(xpa_ind).parent_tbl_index := xp_ind;
395 l_t_pty_acct_tbl(xpa_ind).instance_party_id := fnd_api.g_miss_num;
396 l_t_pty_acct_tbl(xpa_ind).party_account_id := l_pty_acct_tbl(pa_ind).party_account_id;
397 l_t_pty_acct_tbl(xpa_ind).relationship_type_code:= l_pty_acct_tbl(pa_ind).relationship_type_code;
398 l_t_pty_acct_tbl(xpa_ind).bill_to_address := l_pty_acct_tbl(pa_ind).bill_to_address;
399 l_t_pty_acct_tbl(xpa_ind).ship_to_address := l_pty_acct_tbl(pa_ind).ship_to_address;
400 l_t_pty_acct_tbl(xpa_ind).active_start_date := fnd_api.g_miss_date;
401 l_t_pty_acct_tbl(xpa_ind).active_end_date := fnd_api.g_miss_date;
402 l_t_pty_acct_tbl(xpa_ind).context := l_pty_acct_tbl(pa_ind).context;
403 l_t_pty_acct_tbl(xpa_ind).attribute1 := l_pty_acct_tbl(pa_ind).attribute1;
404 l_t_pty_acct_tbl(xpa_ind).attribute2 := l_pty_acct_tbl(pa_ind).attribute2;
405 l_t_pty_acct_tbl(xpa_ind).attribute3 := l_pty_acct_tbl(pa_ind).attribute3;
406 l_t_pty_acct_tbl(xpa_ind).attribute4 := l_pty_acct_tbl(pa_ind).attribute4;
407 l_t_pty_acct_tbl(xpa_ind).attribute5 := l_pty_acct_tbl(pa_ind).attribute5;
408 l_t_pty_acct_tbl(xpa_ind).attribute6 := l_pty_acct_tbl(pa_ind).attribute6;
409 l_t_pty_acct_tbl(xpa_ind).attribute7 := l_pty_acct_tbl(pa_ind).attribute7;
410 l_t_pty_acct_tbl(xpa_ind).attribute8 := l_pty_acct_tbl(pa_ind).attribute8;
411 l_t_pty_acct_tbl(xpa_ind).attribute9 := l_pty_acct_tbl(pa_ind).attribute9;
412 l_t_pty_acct_tbl(xpa_ind).attribute10 := l_pty_acct_tbl(pa_ind).attribute10;
413 l_t_pty_acct_tbl(xpa_ind).attribute11 := l_pty_acct_tbl(pa_ind).attribute11;
414 l_t_pty_acct_tbl(xpa_ind).attribute12 := l_pty_acct_tbl(pa_ind).attribute12;
415 l_t_pty_acct_tbl(xpa_ind).attribute13 := l_pty_acct_tbl(pa_ind).attribute13;
416 l_t_pty_acct_tbl(xpa_ind).attribute14 := l_pty_acct_tbl(pa_ind).attribute14;
417 l_t_pty_acct_tbl(xpa_ind).attribute15 := l_pty_acct_tbl(pa_ind).attribute15;
418 l_t_pty_acct_tbl(xpa_ind).object_version_number := 1;
419 END IF;
420
421 END LOOP;
422
423 END IF;
424 END IF;
425 END LOOP;
426
427 END IF;
428
429 IF l_org_tbl.count > 0 THEN
430 FOR o_ind IN l_org_tbl.FIRST .. l_org_tbl.LAST
431 LOOP
432 IF nvl(l_org_tbl(o_ind).active_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
433 xo_ind := xo_ind + 1;
434 l_t_org_tbl(xo_ind).instance_ou_id := fnd_api.g_miss_num;
435 l_t_org_tbl(xo_ind).parent_tbl_index := 1;
436 l_t_org_tbl(xo_ind).instance_id := p_instance_id;
437 l_t_org_tbl(xo_ind).operating_unit_id := l_org_tbl(o_ind).operating_unit_id;
438 l_t_org_tbl(xo_ind).relationship_type_code:= l_org_tbl(o_ind).relationship_type_code;
439 l_t_org_tbl(xo_ind).active_start_date := fnd_api.g_miss_date;
440 l_t_org_tbl(xo_ind).active_end_date := fnd_api.g_miss_date;
441 l_t_org_tbl(xo_ind).context := l_org_tbl(o_ind).context;
442 l_t_org_tbl(xo_ind).attribute1 := l_org_tbl(o_ind).attribute1;
443 l_t_org_tbl(xo_ind).attribute2 := l_org_tbl(o_ind).attribute2;
444 l_t_org_tbl(xo_ind).attribute3 := l_org_tbl(o_ind).attribute3;
445 l_t_org_tbl(xo_ind).attribute4 := l_org_tbl(o_ind).attribute4;
446 l_t_org_tbl(xo_ind).attribute5 := l_org_tbl(o_ind).attribute5;
447 l_t_org_tbl(xo_ind).attribute6 := l_org_tbl(o_ind).attribute6;
448 l_t_org_tbl(xo_ind).attribute7 := l_org_tbl(o_ind).attribute7;
449 l_t_org_tbl(xo_ind).attribute8 := l_org_tbl(o_ind).attribute8;
450 l_t_org_tbl(xo_ind).attribute9 := l_org_tbl(o_ind).attribute9;
451 l_t_org_tbl(xo_ind).attribute10 := l_org_tbl(o_ind).attribute10;
452 l_t_org_tbl(xo_ind).attribute11 := l_org_tbl(o_ind).attribute11;
453 l_t_org_tbl(xo_ind).attribute12 := l_org_tbl(o_ind).attribute12;
454 l_t_org_tbl(xo_ind).attribute13 := l_org_tbl(o_ind).attribute13;
455 l_t_org_tbl(xo_ind).attribute14 := l_org_tbl(o_ind).attribute14;
456 l_t_org_tbl(xo_ind).attribute15 := l_org_tbl(o_ind).attribute15;
457 l_t_org_tbl(xo_ind).object_version_number := 1;
458 END IF;
459 END LOOP;
460 END IF;
461
462 IF l_price_tbl.count > 0 THEN
463 FOR pr_ind IN l_price_tbl.FIRST .. l_price_tbl.LAST
464 LOOP
465 IF nvl(l_price_tbl(pr_ind).active_end_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
466 xpr_ind := xpr_ind + 1;
467 END IF;
468 END LOOP;
469 END IF;
470
471 END IF;
472
473 x_t_party_tbl := l_t_pty_tbl;
474 x_t_pty_acct_tbl := l_t_pty_acct_tbl;
475 x_t_ou_tbl := l_t_org_tbl;
476 x_t_price_tbl := l_t_price_tbl;
477
478 EXCEPTION
479 WHEN fnd_api.g_exc_error THEN
480 x_return_status := fnd_api.g_ret_sts_error;
481 END rebuild_child_entities;
482
483
484 PROCEDURE process_transaction (
485 p_instance_tbl IN txn_instances_tbl,
486 p_dest_location_tbl IN dest_location_tbl,
487 p_ext_attrib_values_tbl IN OUT NOCOPY txn_ext_attrib_values_tbl,
488 p_txn_tbl IN OUT NOCOPY transaction_tbl,
489 x_return_status OUT NOCOPY VARCHAR2,
490 x_error_msg OUT NOCOPY VARCHAR2 )
491 IS
492 l_txn_error_rec csi_datastructures_pub.transaction_error_rec ;
493 l_txn_rec csi_datastructures_pub.transaction_rec ;
494 l_t_inst_tbl csi_process_txn_grp.txn_instances_tbl ;
495 l_t_party_tbl csi_process_txn_grp.txn_i_parties_tbl ;
496 l_t_pty_acct_tbl csi_process_txn_grp.txn_ip_accounts_tbl ;
497 l_t_ou_tbl csi_process_txn_grp.txn_org_units_tbl ;
498 l_t_eav_tbl csi_process_txn_grp.txn_ext_attrib_values_tbl ;
499 l_t_price_tbl csi_process_txn_grp.txn_pricing_attribs_tbl ;
500 l_t_ia_tbl csi_process_txn_grp.txn_instance_asset_tbl ;
501 l_t_iir_tbl csi_process_txn_grp.txn_ii_relationships_tbl ;
502 l_return_status varchar2(1);
503 l_msg_data varchar2(2000);
504 l_msg_count number ;
505 l_msg_index number ;
506 l_error_msg varchar2(2000);
507
508 ind binary_integer := 0;
509 l_dest_location_rec csi_process_txn_grp.dest_location_rec ;
510 l_sysdate date ;
511 l_redeploy_flag varchar2(1);
512 l_depreciable varchar2(1);
513 l_project_id number;
514 l_task_id number;
515
516 l_last_project_id number;
517 l_last_task_id number;
518
519 l_owner_party_id number;
520 l_owner_party_account_id number;
521 l_acct_class_code varchar2(80);
522 l_location_type_code varchar2(80);
523 l_location_id number;
524 l_instance_usage_code varchar2(80);
525 l_operational_status_code varchar2(80);
526
527 BEGIN
528
529 x_return_status := fnd_api.g_ret_sts_success ;
530
531 cse_util_pkg.set_debug;
532
533 csi_t_gen_utility_pvt.build_file_name(
534 p_file_segment1 => 'cse',
535 p_file_segment2 => to_char(sysdate, 'DDMONYYYY'));
536
537 SELECT sysdate INTO l_sysdate FROM sys.dual;
538
539 debug('Inside API cse_deployment_grp.process_transaction '||to_char(l_sysdate, 'dd-mon-yyyy hh24:mi:ss'));
540 debug(' instance_tbl.count : '||p_instance_tbl.count);
541 debug(' dest_loc_tbl.count : '||p_dest_location_tbl.count);
542 debug(' ea_val_tbl.count : '||p_ext_attrib_values_tbl.count);
543 debug(' txn_tbl.count : '||p_txn_tbl.count);
544
545 IF p_instance_tbl.COUNT > 0 THEN
546 FOR si_ind IN p_instance_tbl.FIRST .. p_instance_tbl.LAST
547 LOOP
548
549 debug('instance_tbl record # '||si_ind);
550 debug(' instance_id : '||p_instance_tbl(si_ind).instance_id);
551
552 SELECT inventory_item_id,
553 last_vld_organization_id,
554 serial_number,
555 lot_number,
556 inventory_revision,
557 operational_status_code,
558 unit_of_measure,
559 pa_project_id,
560 pa_project_task_id,
561 last_pa_project_id,
562 last_pa_task_id,
563 owner_party_id,
564 owner_party_account_id,
565 accounting_class_code,
566 location_type_code,
567 location_id,
568 instance_usage_code,
569 operational_status_code
570 INTO l_t_inst_tbl(1).inventory_item_id ,
571 l_t_inst_tbl(1).vld_organization_id ,
572 l_t_inst_tbl(1).serial_number,
573 l_t_inst_tbl(1).lot_number,
574 l_t_inst_tbl(1).inventory_revision,
575 l_t_inst_tbl(1).operational_status_code,
576 l_t_inst_tbl(1).unit_of_measure,
577 l_project_id,
578 l_task_id,
579 l_last_project_id,
580 l_last_task_id,
581 l_owner_party_id,
582 l_owner_party_account_id,
583 l_acct_class_code,
584 l_location_type_code,
585 l_location_id,
586 l_instance_usage_code,
587 l_operational_status_code
588 FROM csi_item_instances
589 WHERE instance_id = p_instance_tbl(si_ind).instance_id;
590
591 l_t_inst_tbl(1).ib_txn_segment_flag := 'S';
592 l_t_inst_tbl(1).instance_id := p_instance_tbl(si_ind).instance_id ;
593 l_t_inst_tbl(1).active_start_date := p_instance_tbl(si_ind).active_start_date ;
594 l_t_inst_tbl(1).active_end_date := p_instance_tbl(si_ind).active_end_date ;
595 l_t_inst_tbl(1).instance_status_id := p_instance_tbl(si_ind).instance_status_id;
596 l_t_inst_tbl(1).quantity := p_txn_tbl(si_ind).transaction_quantity ;
597
598 IF l_t_inst_tbl(1).serial_number is not null THEN --4616287
599 l_t_inst_tbl(1).instance_status_id := p_instance_tbl(si_ind).instance_status_id ;
600 END IF;
601
602 debug(' serial_number : '||l_t_inst_tbl(1).serial_number);
603 debug(' lot_number : '||l_t_inst_tbl(1).lot_number);
604 debug(' transaction_quantity : '||l_t_inst_tbl(1).quantity);
605
606
607 -- transaction entity
608 l_txn_rec.source_group_ref := p_txn_tbl(si_ind).source_group_ref ;
609 l_txn_rec.source_group_ref_id := p_txn_tbl(si_ind).source_group_ref_id;
610
611 IF l_project_id is not null OR l_last_project_id is not null THEN
612 l_txn_rec.source_header_ref_id := nvl(l_project_id, l_last_project_id);
613 l_txn_rec.source_line_ref_id := nvl(l_task_id, l_last_task_id);
614 ELSE
615 l_txn_rec.source_header_ref_id := p_txn_tbl(si_ind).source_header_ref_id;
616 l_txn_rec.source_line_ref_id := fnd_api.g_miss_num;
617 END IF;
618
619 l_txn_rec.source_header_ref := p_txn_tbl(si_ind).source_header_ref;
620 l_txn_rec.source_line_ref := fnd_api.g_miss_char;
621 l_txn_rec.txn_sub_type_id := p_txn_tbl(si_ind).txn_sub_type_id ;
622 l_txn_rec.source_transaction_date := p_txn_tbl(si_ind).source_transaction_date ;
623 l_txn_rec.transaction_quantity := p_txn_tbl(si_ind).transaction_quantity ;
624
625
626 l_txn_rec.transaction_type_id := p_txn_tbl(si_ind).transaction_type_id ;
627 l_txn_rec.transaction_status_code := 'COMPLETE';
628
629 IF p_txn_tbl(si_ind).transaction_type_id = 106 THEN -- Proj Item Install
630 IF l_project_id IS NULL THEN
631 l_txn_rec.transaction_type_id := 154; -- item install
632 END IF;
633 ELSIF p_txn_tbl(si_ind).transaction_type_id = 109 THEN -- In Service
634 IF l_last_project_id IS NOT NULL THEN
635 l_txn_rec.transaction_type_id := 108; -- project item in service
636 l_txn_rec.transaction_status_code := 'PENDING';
637 END IF;
638 ELSIF p_txn_tbl(si_ind).transaction_type_id = 107 THEN -- project item uninstall
639 IF l_last_project_id IS NULL THEN
640 l_txn_rec.transaction_type_id := 155; -- item uninstall
641 END IF;
642 ELSIF l_txn_rec.transaction_type_id = 111 THEN -- item move
643 l_txn_rec.transaction_status_code := 'PENDING';
644 ELSIF p_txn_tbl(si_ind).transaction_type_id = 107 THEN -- project item uninstall
645 IF l_last_project_id IS NULL THEN
646 l_txn_rec.transaction_type_id := 155; -- item uninstall
647 END IF;
648 END IF;
649
650 debug(' transaction_type_id : '||l_txn_rec.transaction_type_id);
651
652 -- for customer owned item instances we do not allow updates to FA. these transactions
653 -- should not be visible for Asset Tracking programs. so mark the txn status as complete.
654 IF l_owner_party_account_id is not null THEN
655 l_txn_rec.transaction_status_code := 'COMPLETE';
656 END IF;
657
658 IF p_dest_location_tbl.COUNT > 0 THEN
659
660 FOR dl_ind IN p_dest_location_tbl.FIRST .. p_dest_location_tbl.LAST
661 LOOP
662
663 IF p_dest_location_tbl(dl_ind).parent_tbl_index = si_ind THEN
664
665 l_dest_location_rec.parent_tbl_index := p_dest_location_tbl(dl_ind).parent_tbl_index ;
666
667 IF p_dest_location_tbl(dl_ind).location_type_code = 'HR_LOCATIONS' THEN
668 l_dest_location_rec.location_type_code := 'INTERNAL_SITE';
669 ELSE
670 l_dest_location_rec.location_type_code := p_dest_location_tbl(dl_ind).location_type_code ;
671 END IF;
672 l_dest_location_rec.location_id := p_dest_location_tbl(dl_ind).location_id ;
673 l_dest_location_rec.last_pa_project_id := p_dest_location_tbl(dl_ind).last_pa_project_id ;
674 l_dest_location_rec.last_pa_project_task_id := p_dest_location_tbl(dl_ind).last_pa_project_task_id ;
675 l_dest_location_rec.external_reference := p_dest_location_tbl(dl_ind).external_reference ;
676 l_dest_location_rec.operational_status_code := p_dest_location_tbl(dl_ind).operational_status_code ;
677 l_dest_location_rec.instance_usage_code := p_dest_location_tbl(dl_ind).instance_usage_code;
678
679 IF l_dest_location_rec.location_type_code = 'PROJECT' THEN
680 l_dest_location_rec.pa_project_id := p_dest_location_tbl(dl_ind).pa_project_id;
681 l_dest_location_rec.pa_project_task_id := p_dest_location_tbl(dl_ind).pa_project_task_id;
682 END IF;
683
684 debug(' location_type_code : '||l_dest_location_rec.location_type_code);
685 debug(' location_id : '||l_dest_location_rec.location_id);
686
687 END IF ;
688 END LOOP ;
689
690 ELSE
691 fnd_message.set_name('CSI','CSI_DPL_INVALID_LOCATION');
692 fnd_msg_pub.add;
693 RAISE fnd_api.g_exc_error ;
694 END IF ; --p_dest_location_tbl.COUNT
695
696
697 -- override destination location attribs based on transaction type
698 IF l_txn_rec.transaction_type_id in (154,106) THEN --Item Install
699 l_dest_location_rec.operational_status_code := 'INSTALLED' ;
700 l_dest_location_rec.instance_usage_code := 'INSTALLED';
701 IF l_txn_rec.transaction_type_id = 106 THEN
702 l_dest_location_rec.last_pa_project_id := l_project_id;
703 l_dest_location_rec.last_pa_project_task_id := l_task_id ;
704 END IF;
705
706 IF l_project_id is not null THEN
707 l_t_inst_tbl(1).last_pa_project_id := l_project_id;
708 l_t_inst_tbl(1).last_pa_task_id := l_task_id;
709 l_t_inst_tbl(1).pa_project_id := null;
710 l_t_inst_tbl(1).pa_project_task_id := null;
711 END IF;
712
713 ELSIF l_txn_rec.transaction_type_id in (108, 109) THEN -- In Service
714 l_dest_location_rec.operational_status_code := 'IN_SERVICE' ;
715 l_dest_location_rec.instance_usage_code := 'IN_SERVICE';
716 ELSIF l_txn_rec.transaction_type_id = 110 THEN -- out of service
717 l_dest_location_rec.operational_status_code := 'OUT_OF_SERVICE' ;
718 l_dest_location_rec.instance_usage_code := 'OUT_OF_SERVICE';
719 ELSIF l_txn_rec.transaction_type_id = 111 THEN -- item move
720 --fix for the bug 4620445
721 IF nvl(l_dest_location_rec.location_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
722 OR
723 nvl(l_dest_location_rec.location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
724 THEN
725 fnd_message.set_name('CSI','CSI_DPL_INVALID_LOCATION');
726 fnd_msg_pub.add;
727 RAISE fnd_api.g_exc_error;
728 END IF;
729 l_dest_location_rec.operational_status_code := l_operational_status_code;
730 l_dest_location_rec.instance_usage_code := l_instance_usage_code;
731 ELSIF l_txn_rec.transaction_type_id in (107, 155) THEN -- uninstall
732 IF l_last_project_id is not null THEN
733 l_t_inst_tbl(1).pa_project_id := l_last_project_id;
734 l_t_inst_tbl(1).pa_project_task_id := l_last_task_id;
735 l_t_inst_tbl(1).last_pa_project_id := NULL;
736 l_t_inst_tbl(1).last_pa_task_id := NULL;
737 l_dest_location_rec.location_type_code := 'PROJECT';
738 END IF;
739 l_dest_location_rec.operational_status_code := 'NOT_USED';
740 l_dest_location_rec.instance_usage_code := 'IN_PROCESS';
741 ELSIF l_txn_rec.transaction_type_id = 152 THEN -- project transfer
742 l_t_inst_tbl(1).location_type_code := l_location_type_code;
743 l_t_inst_tbl(1).location_id := l_location_id;
744 IF l_project_id is not null THEN
745 l_t_inst_tbl(1).pa_project_id := l_project_id;
746 l_t_inst_tbl(1).pa_project_task_id := l_task_id;
747 END IF;
748 IF l_last_project_id is not null THEN
749 l_t_inst_tbl(1).last_pa_project_id := l_last_project_id;
750 l_t_inst_tbl(1).last_pa_task_id := l_last_task_id;
751 END IF;
752 ELSIF l_txn_rec.transaction_type_id = 104 THEN -- asset retirements
753 l_dest_location_rec.operational_status_code := 'OUT_OF_SERVICE';
754 ELSE
755 fnd_message.set_name('CSI','CSI_INVALID_TXN_TYPE_ID');
756 fnd_msg_pub.add;
757 RAISE fnd_api.g_exc_error;
758 END IF; ---Txn Type
759
760 IF p_ext_attrib_values_tbl.COUNT > 0 THEN
761 FOR av_ind IN p_ext_attrib_values_tbl.FIRST .. p_ext_attrib_values_tbl.LAST
762 LOOP
763 IF p_ext_attrib_values_tbl(av_ind).parent_tbl_index = si_ind THEN
764 ind := ind+1;
765 l_t_eav_tbl(ind).attribute_value_id := p_ext_attrib_values_tbl(av_ind).attribute_value_id ;
766 l_t_eav_tbl(ind).parent_tbl_index := p_ext_attrib_values_tbl(av_ind).parent_tbl_index ;
767 l_t_eav_tbl(ind).instance_id := p_ext_attrib_values_tbl(av_ind).instance_id ;
768 l_t_eav_tbl(ind).attribute_id := p_ext_attrib_values_tbl(av_ind).attribute_id ;
769 l_t_eav_tbl(ind).attribute_code := p_ext_attrib_values_tbl(av_ind).attribute_code ;
770 l_t_eav_tbl(ind).attribute_value := p_ext_attrib_values_tbl(av_ind).attribute_value ;
771 l_t_eav_tbl(ind).object_version_number := p_ext_attrib_values_tbl(av_ind).object_version_number;
772 END IF ;
773 END LOOP ;
774 END IF ;--p_ext_attribs_values_tbl.COUNT > 0
775
776 debug(' instance_usage_code : '||l_dest_location_rec.instance_usage_code);
777 debug(' operation_status_code : '||l_dest_location_rec.operational_status_code);
778
779 -- not taking the retirement transactions thru the process transaction api
780 IF l_txn_rec.transaction_type_id = 104 THEN
781 process_retirements(
782 p_instance_id => p_instance_tbl(si_ind).instance_id,
783 p_asset_id => p_instance_tbl(si_ind).asset_id,
784 p_proceeds_of_sale => p_txn_tbl(si_ind).proceeds_of_sale,
785 p_cost_of_removal => p_txn_tbl(si_ind).cost_of_removal,
786 p_operational_flag => p_txn_tbl(si_ind).financial_flag,
787 p_financial_flag => p_txn_tbl(si_ind).financial_flag,
788 px_txn_rec => l_txn_rec,
789 x_return_status => l_return_status);
790
791 IF l_return_status not in (fnd_api.g_ret_sts_success, 'W') THEN
792 RAISE fnd_api.g_exc_error;
793 END IF;
794 ELSE
795
796 IF l_owner_party_account_id is not null AND l_t_inst_tbl(1).serial_number is null THEN
797
798 debug(' owner_account_id : '||l_owner_party_account_id);
799 debug(' acct_class_code : '||l_acct_class_code);
800
801 -- put logic here to re-build the external party and account
802 rebuild_child_entities(
803 p_instance_id => l_t_inst_tbl(1).instance_id,
804 x_t_party_tbl => l_t_party_tbl,
805 x_t_pty_acct_tbl => l_t_pty_acct_tbl,
806 x_t_ou_tbl => l_t_ou_tbl,
807 x_t_price_tbl => l_t_price_tbl,
808 x_return_status => l_return_status);
809
810 IF l_return_status <> fnd_api.g_ret_sts_success THEN
811 RAISE fnd_api.g_exc_error;
812 END IF;
813
814 END IF;
815
816 csi_process_txn_grp.process_transaction(
817 p_api_version => 1.0,
818 p_commit => fnd_api.g_false,
819 p_init_msg_list => fnd_api.g_false,
820 p_validation_level => fnd_api.g_valid_level_full,
821 p_validate_only_flag => fnd_api.g_false,
822 p_in_out_flag => 'INT',
823 p_dest_location_rec => l_dest_location_rec ,
824 p_txn_rec => l_txn_rec ,
825 p_instances_tbl => l_t_inst_tbl,
826 p_i_parties_tbl => l_t_party_tbl,
827 p_ip_accounts_tbl => l_t_pty_acct_tbl,
828 p_org_units_tbl => l_t_ou_tbl,
829 p_ext_attrib_vlaues_tbl => l_t_eav_tbl,
830 p_pricing_attribs_tbl => l_t_price_tbl,
831 p_instance_asset_tbl => l_t_ia_tbl,
832 p_ii_relationships_tbl => l_t_iir_tbl,
833 px_txn_error_rec => l_txn_error_rec,
834 x_return_status => l_return_status,
835 x_msg_count => l_msg_count,
836 x_msg_data => l_msg_data);
837
838 IF l_return_status <> fnd_api.g_ret_sts_success THEN
839 RAISE fnd_api.g_exc_error;
840 END IF;
841 END IF; -- retirement/non retirement transactions
842
843 END LOOP ;
844 END IF ; --p_instance_tbl.COUNT > 0
845
846 EXCEPTION
847 WHEN fnd_api.g_exc_error THEN
848 x_return_status := fnd_api.g_ret_sts_error;
849 x_error_msg := nvl(l_error_msg, cse_util_pkg.dump_error_stack);
850 debug('Error : '||x_error_msg);
851 END process_transaction;
852 END cse_deployment_grp;