[Home] [Help]
PACKAGE BODY: APPS.CSE_DEPLOYMENT_GRP
Source
1 PACKAGE BODY cse_deployment_grp AS
2 /* $Header: CSEDPLGB.pls 120.21.12010000.7 2010/01/12 21:03:56 devijay ship $ */
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 name: interface_nl_to_pa */
485 /* Description : Added for Bug 8670632 */
486 /* This procedure is used to interface the */
487 /* project transfer transaction to expenditures */
488 /* in projects. */
489 /*---------------------------------------------------------------*/
490
491 PROCEDURE interface_nl_to_pa(
492 p_trf_pa_attr_rec IN cse_datastructures_pub.Proj_Itm_Insv_PA_ATTR_REC_TYPE,
493 p_conc_request_id IN NUMBER ,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_error_message OUT NOCOPY VARCHAR2)
496 IS
497 l_api_name CONSTANT VARCHAR2(30) := 'cse_deployment_grp';
498 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
499 l_error_message VARCHAR2(2000);
500 l_msg_count NUMBER;
501 l_msg_data VARCHAR2(2000);
502 l_api_version NUMBER DEFAULT 1.0;
503 l_commit VARCHAR2(1) DEFAULT FND_API.G_FALSE;
504 l_init_msg_list VARCHAR2(1) DEFAULT FND_API.G_TRUE;
505 l_validation_level NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL;
506 l_active_instance_only VARCHAR2(1) DEFAULT FND_API.G_TRUE;
507 l_txn_rec csi_datastructures_pub.transaction_rec;
508 l_asset_location_rec csi_datastructures_pub.instance_asset_location_rec;
509 l_asset_location_tbl csi_datastructures_pub.instance_asset_location_tbl;
510 l_nl_pa_interface_tbl CSE_IPA_TRANS_PKG.nl_pa_interface_tbl_type;
511 l_burden_cost_sum NUMBER;
512 l_qty_sum NUMBER;
513 l_sum_of_qty NUMBER;
514 l_fa_location_id NUMBER;
515 l_attribute8 VARCHAR2(150);
516 l_attribute9 VARCHAR2(150);
517 l_attribute10 VARCHAR2(150);
518 l_proj_itm_trf_qty NUMBER;
519 l_hz_location_id NUMBER;
520 i PLS_INTEGER := 0;
521 l_org_id NUMBER;
522 l_incurred_by_org_id PA_EXPENDITURES_ALL.INCURRED_BY_ORGANIZATION_ID%TYPE;
523 l_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
524 l_user_id NUMBER DEFAULT FND_GLOBAL.USER_ID;
525 l_transaction_source PA_EXPENDITURE_ITEMS_ALL.TRANSACTION_SOURCE%TYPE;
526 l_sysdate DATE:=sysdate;
527 l_ref_sufix NUMBER;
528 l_project_number VARCHAR2(25);
529 l_task_number VARCHAR2(25);
530 l_from_project_number VARCHAR2(25);
531 l_from_task_number VARCHAR2(25);
532 l_organization_name VARCHAR2(240);
533 l_app_short_name VARCHAR2(8):='CSE';
534 TYPE exp_item_rec IS RECORD (
535 expenditure_item_id number,
536 expenditure_id number,
537 quantity number,
538 split_flag varchar2(1),
539 split_quantity number);
540
541 l_exp_item_rec exp_item_rec;
542
543 CURSOR ei_cur IS
544 SELECT item.expenditure_item_id,
545 item.project_id,
546 item.task_id,
547 item.transaction_source,
548 item.org_id,
549 item.expenditure_type,
550 item.expenditure_item_date,
551 item.denom_currency_code,
552 item.attribute6,
553 item.attribute7,
554 item.quantity quantity,
555 item.raw_cost raw_cost,
556 item.denom_raw_cost denom_raw_cost,
557 item.denom_raw_cost/item.quantity unit_denom_raw_cost,
558 item.raw_cost_rate,
559 item.burden_cost burden_cost,
560 item.burden_cost/item.quantity burden_cost_rate,
561 item.override_to_organization_id,
562 item.system_linkage_function,
563 item.orig_transaction_reference,
564 dist.dr_code_combination_id,
565 dist.cr_code_combination_id,
566 dist.gl_date,
567 dist.acct_raw_cost,
568 dist.system_reference1,
569 dist.system_reference2,
570 dist.system_reference3,
571 dist.system_reference4,
572 dist.system_reference5,
573 exp.expenditure_id,
574 exp.expenditure_ending_date,
575 exp.incurred_by_organization_id
576 FROM pa_expenditure_items_all item,
577 pa_cost_distribution_lines_all dist,
578 pa_expenditures_all exp
579 WHERE item.transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
580 AND item.project_id = p_trf_pa_attr_rec.project_id
581 AND item.task_id = p_trf_pa_attr_rec.task_id
582 AND item.attribute8 IS null
583 AND item.attribute9 IS null
584 AND item.attribute10 IS null
585 AND item.quantity > 0
586 AND item.attribute6 = l_item_name
587 AND nvl(item.attribute7, '**xyz**') = NVL(p_trf_pa_attr_rec.serial_number, '**xyz**')
588 AND nvl(item.net_zero_adjustment_flag, 'N') <> 'Y'
589 AND dist.expenditure_item_id = item.expenditure_item_id
590 AND dist.line_type = 'R'
591 AND nvl(dist.reversed_flag, 'N') <> 'Y'
592 AND dist.cr_code_combination_id IS NOT NULL
593 AND dist.dr_code_combination_id IS NOT NULL
594 AND exp.expenditure_id = item.expenditure_id;
595
596 l_paapi_status NUMBER;
597 l_found BOOLEAN:=FALSE;
598
599 Cursor txn_intf_csr IS
600 SELECT transaction_source,
601 batch_name,
602 expenditure_ending_date,
603 employee_number,
604 organization_name,
605 expenditure_item_date,
606 project_number,
607 task_number,
608 expenditure_type,
609 non_labor_resource,
610 non_labor_resource_org_name,
611 quantity, raw_cost,
612 expenditure_comment,
613 transaction_status_code,
614 transaction_rejection_code,
615 expenditure_id,
616 orig_transaction_reference,
617 attribute_category,
618 attribute1,
619 attribute2,
620 attribute3,
621 attribute4,
622 attribute5,
623 attribute6,
624 attribute7,
625 attribute8,
626 attribute9,
627 attribute10,
628 raw_cost_rate,
629 interface_id,
630 unmatched_negative_txn_flag,
631 expenditure_item_id,
632 org_id,
633 dr_code_combination_id,
634 cr_code_combination_id,
635 cdl_system_reference1,
636 cdl_system_reference2,
637 cdl_system_reference3,
638 cdl_system_reference4,
639 cdl_system_reference5,
640 gl_date,
641 burdened_cost,
642 burdened_cost_rate,
643 system_linkage,
644 txn_interface_id,
645 user_transaction_source,
646 created_by,
647 creation_date,
648 last_updated_by,
649 last_update_date,
650 receipt_currency_amount,
651 receipt_currency_code,
652 receipt_exchange_rate,
653 denom_currency_code,
654 denom_raw_cost,
655 denom_burdened_cost,
656 acct_rate_date,
657 acct_rate_type,
658 acct_exchange_rate,
659 acct_raw_cost,
660 acct_burdened_cost,
661 acct_exchange_rounding_limit,
662 project_currency_code,
663 project_rate_date,
664 project_rate_type,
665 project_exchange_rate,
666 orig_exp_txn_reference1,
667 orig_exp_txn_reference2,
668 orig_exp_txn_reference3,
669 orig_user_exp_txn_reference,
670 vendor_number,
671 override_to_organization_name,
672 reversed_orig_txn_reference,
673 billable_flag,
674 person_business_group_name,
675 override_to_organization_id,
676 denom_raw_cost/quantity unit_denom_raw_cost
677 FROM pa_transaction_interface_all
678 WHERE transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
679 AND project_number = l_from_project_number
680 AND task_number = l_from_task_number
681 AND attribute8 IS NULL
682 AND attribute9 IS NULL
683 AND attribute10 IS NULL
684 AND quantity > 0
685 AND attribute6 = l_item_name
686 AND nvl(attribute7, '**xyz**') = NVL(p_trf_pa_attr_rec.serial_number, '**xyz**')
687 AND ROWNUM=1;
688
689 CURSOR c_Business_Group_cur( c_org_id NUMBER ) IS
690 SELECT ho.name
691 FROM hr_all_organization_units ho, hr_all_organization_units hoc
692 WHERE hoc.organization_id = c_org_id
693 AND ho.organization_id = hoc.business_group_id ;
694
695 l_Business_Group_rec c_Business_Group_cur%ROWTYPE;
696
697 BEGIN
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699 x_error_message := NULL;
700 cse_util_pkg.set_debug;
701
702 debug('Inside API cse_deployment_grp.interface_nl_to_pa');
703
704 debug(' inventory_item_id : '||p_trf_pa_attr_rec.item_id);
705 debug(' organization_id : '||p_trf_pa_attr_rec.inv_master_org_id);
706 debug(' project_id : '||p_trf_pa_attr_rec.project_id);
707 debug(' task_id : '||p_trf_pa_attr_rec.task_id);
708 debug(' serial_number : '||p_trf_pa_attr_rec.serial_number);
709 debug(' transaction_id : '||p_trf_pa_attr_rec.transaction_id);
710 debug(' in_service_qty : '||p_trf_pa_attr_rec.quantity);
711 debug(' to_project_id : '||p_trf_pa_attr_rec.to_project_id);
712 debug(' to_task_id : '||p_trf_pa_attr_rec.to_task_id);
713 debug(' instance_id : '||p_trf_pa_attr_rec.instance_id);
714
715 SELECT concatenated_segments
716 INTO l_item_name
717 FROM mtl_system_items_kfv
718 WHERE inventory_item_id = p_trf_pa_attr_rec.item_id
719 AND organization_id = p_trf_pa_attr_rec.inv_master_org_id;
720
721 debug(' item : '||l_item_name);
722
723 SELECT segment1
724 INTO l_project_number
725 FROM pa_projects_all
726 WHERE project_id = p_trf_pa_attr_rec.to_project_id;
727
728 SELECT task_number
729 INTO l_task_number
730 FROM pa_tasks
731 WHERE task_id = p_trf_pa_attr_rec.to_task_id;
732
733 l_proj_itm_trf_qty := p_trf_pa_attr_rec.quantity;
734 i := 0;
735
736 FOR ei_rec IN ei_cur LOOP
737 l_found:=TRUE;
738 debug('cursor record # '||ei_cur%rowcount);
739
740 debug(' expenditure_item_id : '||ei_rec.expenditure_item_id);
741 debug(' quantity : '||ei_rec.quantity);
742 debug(' l_proj_itm_trf_qty : '||l_proj_itm_trf_qty);
743 dbms_application_info.set_client_info(ei_rec.org_id);
744 IF l_proj_itm_trf_qty = 0 THEN
745 EXIT;
746 END IF;
747
748 IF ei_rec.quantity <= l_proj_itm_trf_qty THEN
749 l_proj_itm_trf_qty := l_proj_itm_trf_qty - ei_rec.quantity;
750 l_exp_item_rec.expenditure_item_id := ei_rec.expenditure_item_id;
751 l_exp_item_rec.expenditure_id := ei_rec.expenditure_id;
752 l_exp_item_rec.quantity := ei_rec.quantity;
753 l_exp_item_rec.split_flag := 'N';
754 ELSE
755 l_exp_item_rec.expenditure_item_id := ei_rec.expenditure_item_id;
756 l_exp_item_rec.expenditure_id := ei_rec.expenditure_id;
757 l_exp_item_rec.quantity := l_proj_itm_trf_qty;
758 l_exp_item_rec.split_flag := 'Y';
759 l_exp_item_rec.split_quantity := ei_rec.quantity - l_proj_itm_trf_qty;
760 END IF;
761
762 debug('Inside API pa_nl_installed.reverse_eib_ei');
763 debug('expenditure_item_id : '||l_exp_item_rec.expenditure_item_id);
764 -- This code does the reversal
765 pa_nl_installed.reverse_eib_ei(
766 x_exp_item_id => l_exp_item_rec.expenditure_item_id,
767 x_expenditure_id => l_exp_item_rec.expenditure_id,
768 x_transfer_status_code => 'V',
769 x_status => l_paapi_status);
770
771 IF l_paapi_status <> 0 THEN
772 l_error_message := sqlerrm;
773 RAISE fnd_api.g_exc_error;
774 END IF;
775
776 SELECT name
777 INTO l_organization_name
778 FROM hr_organization_units
779 WHERE organization_id =
780 nvl(ei_rec.override_to_organization_id, ei_rec.incurred_by_organization_id);
781
782 i := i+1;
783
784 debug('capitalizable record # '||i);
785 debug(' capitalizable exp_item_id : '||l_exp_item_rec.expenditure_item_id);
786 debug(' capitalizable quantity : '||l_exp_item_rec.quantity);
787
788 SELECT csi_pa_interface_s.nextval
789 INTO l_ref_sufix
790 FROM sys.dual;
791
792 OPEN c_Business_Group_cur( ei_rec.org_id ) ;
793 FETCH c_Business_Group_cur INTO l_Business_Group_rec;
794 CLOSE c_Business_Group_cur;
795
796 l_nl_pa_interface_tbl(i).transaction_source := ei_rec.transaction_source;
797
798 IF( p_trf_pa_attr_rec.transaction_id = FND_API.G_MISS_NUM)
799 THEN
800 l_nl_pa_interface_tbl(i).batch_name := FND_API.G_MISS_CHAR;
801 ELSE
802 l_nl_pa_interface_tbl(i).batch_name := p_trf_pa_attr_rec.transaction_id;
803 END IF;
804
805 l_nl_pa_interface_tbl(i).expenditure_ending_date := ei_rec.expenditure_ending_date;
806 l_nl_pa_interface_tbl(i).employee_number := null;
807 l_nl_pa_interface_tbl(i).organization_name := l_organization_name;
808 l_nl_pa_interface_tbl(i).expenditure_item_date := ei_rec.expenditure_item_date;
809 l_nl_pa_interface_tbl(i).project_number := l_project_number;
810 l_nl_pa_interface_tbl(i).task_number := l_task_number;
811 l_nl_pa_interface_tbl(i).expenditure_type := ei_rec.expenditure_type;
812 l_nl_pa_interface_tbl(i).expenditure_comment := 'ENTERPRISE INSTALL BASE';
813 l_nl_pa_interface_tbl(i).transaction_status_code := 'P';
814 l_nl_pa_interface_tbl(i).orig_transaction_reference
815 := p_trf_pa_attr_rec.instance_id||'-'||l_ref_sufix;
816 l_nl_pa_interface_tbl(i).attribute_category := NULL;
817 l_nl_pa_interface_tbl(i).attribute1 := NULL;
818 l_nl_pa_interface_tbl(i).attribute2 := NULL;
819 l_nl_pa_interface_tbl(i).attribute3 := NULL;
820 l_nl_pa_interface_tbl(i).attribute4 := NULL;
821 l_nl_pa_interface_tbl(i).attribute5 := NULL;
822 l_nl_pa_interface_tbl(i).attribute6 := l_item_name;
823 l_nl_pa_interface_tbl(i).attribute7 := p_trf_pa_attr_rec.serial_number;
824 l_nl_pa_interface_tbl(i).attribute8 := Null;
825 l_nl_pa_interface_tbl(i).attribute9 := Null;
826 l_nl_pa_interface_tbl(i).attribute10 := Null;
827 l_nl_pa_interface_tbl(i).interface_id := NULL;
828 l_nl_pa_interface_tbl(i).unmatched_negative_txn_flag := 'Y';
829 l_nl_pa_interface_tbl(i).org_id := ei_rec.org_id;
830 l_nl_pa_interface_tbl(i).dr_code_combination_id := ei_rec.dr_code_combination_id;
831 l_nl_pa_interface_tbl(i).cr_code_combination_id := ei_rec.cr_code_combination_id;
832 l_nl_pa_interface_tbl(i).gl_date := ei_rec.gl_date;
833 l_nl_pa_interface_tbl(i).system_linkage := ei_rec.system_linkage_function;
834 l_nl_pa_interface_tbl(i).person_business_group_name := l_Business_Group_rec.name;
835 l_nl_pa_interface_tbl(i).inventory_item_id := p_trf_pa_attr_rec.item_id;
836
837 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' THEN
838 BEGIN
839 SELECT segment1
840 INTO l_nl_pa_interface_tbl(i).vendor_number
841 FROM po_vendors
842 WHERE vendor_id = ei_rec.system_reference1;
843 EXCEPTION
844 WHEN no_data_found THEN
845 l_nl_pa_interface_tbl(i).system_linkage := 'INV';
846 END;
847 END IF;
848 l_nl_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE';
849 --Added for bug 8670632 --
850 l_nl_pa_interface_tbl(i).cdl_system_reference1 := ei_rec.system_reference1;
851 l_nl_pa_interface_tbl(i).cdl_system_reference2 := ei_rec.system_reference2;
852 l_nl_pa_interface_tbl(i).cdl_system_reference3 := ei_rec.system_reference3;
853 l_nl_pa_interface_tbl(i).cdl_system_reference4 := ei_rec.system_reference4;
854 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' AND ei_rec.system_reference5 is NULL THEN
855 l_nl_pa_interface_tbl(i).cdl_system_reference5 := cse_asset_util_pkg.get_rcv_sub_ledger_id(ei_rec.system_reference4);
856 ELSE
857 l_nl_pa_interface_tbl(i).cdl_system_reference5 := ei_rec.system_reference5;
858 END IF;
859
860 debug(' system_reference4 : '||ei_rec.system_reference4);
861 debug(' system_reference5 : '||ei_rec.system_reference5);
862 debug(' system_reference5 : '||l_nl_pa_interface_tbl(i).cdl_system_reference5);
863 --Added for bug 8670632 --
864
865 l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
866 l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
867 l_nl_pa_interface_tbl(i).creation_date := l_sysdate;
868 l_nl_pa_interface_tbl(i).created_by := l_user_id;
869 l_nl_pa_interface_tbl(i).billable_flag := 'Y';
870 l_nl_pa_interface_tbl(i).quantity := l_exp_item_rec.quantity;
871
872 l_nl_pa_interface_tbl(i).denom_raw_cost :=
873 ei_rec.unit_denom_raw_cost * l_exp_item_rec.quantity;
874
875 l_nl_pa_interface_tbl(i).acct_raw_cost :=
876 ei_rec.unit_denom_raw_cost * l_exp_item_rec.quantity;
877
878 IF l_exp_item_rec.split_flag = 'Y' THEN
879
880 i := i + 1;
881
882 debug(' spillover record # '||i);
883 debug(' spillover exp_item_id : '|| l_exp_item_rec.expenditure_item_id);
884 debug(' spillover quantity : '|| l_exp_item_rec.split_quantity);
885
886 l_nl_pa_interface_tbl(i) := l_nl_pa_interface_tbl(i-1);
887
888 SELECT csi_pa_interface_s.nextval
889 INTO l_ref_sufix
890 FROM sys.dual;
891
892 SELECT segment1
893 INTO l_nl_pa_interface_tbl(i).project_number
894 FROM pa_projects_all
895 WHERE project_id = p_trf_pa_attr_rec.project_id;
896
897 SELECT task_number
898 INTO l_nl_pa_interface_tbl(i).task_number
899 FROM pa_tasks
900 WHERE task_id = p_trf_pa_attr_rec.task_id;
901
902 l_nl_pa_interface_tbl(i).orig_transaction_reference := p_trf_pa_attr_rec.transaction_id;
903 l_nl_pa_interface_tbl(i).attribute8 := null;
904 l_nl_pa_interface_tbl(i).attribute9 := null;
905 l_nl_pa_interface_tbl(i).attribute10 := null;
906 l_nl_pa_interface_tbl(i).quantity := l_exp_item_rec.split_quantity;
907 l_nl_pa_interface_tbl(i).denom_raw_cost :=
908 ei_rec.unit_denom_raw_cost * l_exp_item_rec.split_quantity;
909 l_nl_pa_interface_tbl(i).acct_raw_cost :=
910 ei_rec.unit_denom_raw_cost * l_exp_item_rec.split_quantity;
911 EXIT;
912 END IF;
913
914 END LOOP;
915
916 -- Here we write the logic for the records not found in pa_expenditure_items_all
917 -- but found in pa_transaction_interface_all
918 BEGIN
919 SELECT segment1
920 INTO l_from_project_number
921 FROM pa_projects_all
922 WHERE project_id = p_trf_pa_attr_rec.project_id;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 NULL;
926 END;
927
928 BEGIN
929 SELECT task_number
930 INTO l_from_task_number
931 FROM pa_tasks
932 WHERE task_id = p_trf_pa_attr_rec.task_id;
933 EXCEPTION
934 WHEN NO_DATA_FOUND THEN
935 NULL;
936 END;
937
938
939 IF NOT(l_found)
940 THEN
941 debug('Since the record was not found in pa_expenditure_items_all checking in pa_txn_intf_all ');
942 FOR l_txn_intf_csr IN txn_intf_csr
943 LOOP
944 l_found:=TRUE;
945 i := i+1;
946 debug('Record found in pa_txn_intf_all ');
947 SELECT csi_pa_interface_s.nextval
948 INTO l_ref_sufix
949 FROM sys.dual;
950
951 IF l_txn_intf_csr.transaction_source = 'CSE_PO_RECEIPT'
952 THEN
953 l_nl_pa_interface_tbl(i).vendor_number :=l_txn_intf_csr.vendor_number;
954 IF l_nl_pa_interface_tbl(i).vendor_number IS NULL
955 THEN
956 l_nl_pa_interface_tbl(i).system_linkage := 'INV';
957 END IF;
958 END IF;
959
960 OPEN c_Business_Group_cur( l_txn_intf_csr.org_id ) ;
961 FETCH c_Business_Group_cur INTO l_Business_Group_rec;
962 CLOSE c_Business_Group_cur;
963
964 -- Here we build a record that will have a -ve qty
965 l_nl_pa_interface_tbl(i).transaction_source := l_txn_intf_csr.transaction_source;
966 l_nl_pa_interface_tbl(i).batch_name := l_txn_intf_csr.batch_name; --p_trf_pa_attr_rec.transaction_id;
967 l_nl_pa_interface_tbl(i).expenditure_ending_date :=l_txn_intf_csr.expenditure_ending_date;
968 l_nl_pa_interface_tbl(i).employee_number :=NULL;
969 l_nl_pa_interface_tbl(i).organization_name :=l_txn_intf_csr.organization_name;
970 l_nl_pa_interface_tbl(i).expenditure_item_date :=l_txn_intf_csr.expenditure_item_date;
971 l_nl_pa_interface_tbl(i).project_number :=l_from_project_number;
972 l_nl_pa_interface_tbl(i).task_number := l_from_task_number;
973 l_nl_pa_interface_tbl(i).expenditure_type :=l_txn_intf_csr.expenditure_type;
974 l_nl_pa_interface_tbl(i).quantity := (0-p_trf_pa_attr_rec.quantity);
975 l_nl_pa_interface_tbl(i).expenditure_comment:='ENTERPRISE INSTALL BASE';
976 l_nl_pa_interface_tbl(i).transaction_status_code:='P';
977 l_nl_pa_interface_tbl(i).expenditure_id := l_txn_intf_csr.expenditure_id;
978 l_nl_pa_interface_tbl(i).orig_transaction_reference :=p_trf_pa_attr_rec.transaction_id||'-'||l_ref_sufix;
979 l_nl_pa_interface_tbl(i).attribute_category := null;
980 l_nl_pa_interface_tbl(i).attribute1 := null;
981 l_nl_pa_interface_tbl(i).attribute2 := null;
982 l_nl_pa_interface_tbl(i).attribute3 := null;
983 l_nl_pa_interface_tbl(i).attribute4 := null;
984 l_nl_pa_interface_tbl(i).attribute5 := null;
985 l_nl_pa_interface_tbl(i).attribute6 := l_item_name;
986 l_nl_pa_interface_tbl(i).attribute7 := p_trf_pa_attr_rec.serial_number;
987 l_nl_pa_interface_tbl(i).attribute8 := Null;
988 l_nl_pa_interface_tbl(i).attribute9 := Null;
989 l_nl_pa_interface_tbl(i).attribute10 := Null;
990 l_nl_pa_interface_tbl(i).interface_id := NULL;
991 l_nl_pa_interface_tbl(i).unmatched_negative_txn_flag:='Y';
992 l_nl_pa_interface_tbl(i).expenditure_item_id:=l_txn_intf_csr.expenditure_item_id;
993 l_nl_pa_interface_tbl(i).org_id :=l_txn_intf_csr.org_id;
994 l_nl_pa_interface_tbl(i).dr_code_combination_id := l_txn_intf_csr.dr_code_combination_id;
995 l_nl_pa_interface_tbl(i).cr_code_combination_id := l_txn_intf_csr.cr_code_combination_id;
996 l_nl_pa_interface_tbl(i).gl_date :=l_txn_intf_csr.gl_date;
997 l_nl_pa_interface_tbl(i).system_linkage := l_txn_intf_csr.system_linkage;
998 l_nl_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE';
999 --Added for bug 8670632 --
1000 l_nl_pa_interface_tbl(i).cdl_system_reference1 := l_txn_intf_csr.cdl_system_reference1;
1001 l_nl_pa_interface_tbl(i).cdl_system_reference2 := l_txn_intf_csr.cdl_system_reference2;
1002 l_nl_pa_interface_tbl(i).cdl_system_reference3 := l_txn_intf_csr.cdl_system_reference3;
1003 l_nl_pa_interface_tbl(i).cdl_system_reference4 := l_txn_intf_csr.cdl_system_reference4;
1004 IF l_txn_intf_csr.transaction_source = 'CSE_PO_RECEIPT' AND l_txn_intf_csr.cdl_system_reference5 is NULL THEN
1005 l_nl_pa_interface_tbl(i).cdl_system_reference5 := cse_asset_util_pkg.get_rcv_sub_ledger_id(l_txn_intf_csr.cdl_system_reference4);
1006 ELSE
1007 l_nl_pa_interface_tbl(i).cdl_system_reference5 := l_txn_intf_csr.cdl_system_reference5;
1008 END IF;
1009 --Added for bug 8670632 --
1010 l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
1011 l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
1012 l_nl_pa_interface_tbl(i).creation_date := l_sysdate;
1013 l_nl_pa_interface_tbl(i).created_by := l_user_id;
1014 l_nl_pa_interface_tbl(i).person_business_group_name := l_Business_Group_rec.name;
1015 l_nl_pa_interface_tbl(i).inventory_item_id := p_trf_pa_attr_rec.item_id;
1016 l_nl_pa_interface_tbl(i).denom_raw_cost :=
1017 -1 * (l_txn_intf_csr.unit_denom_raw_cost * p_trf_pa_attr_rec.quantity);
1018
1019 l_nl_pa_interface_tbl(i).acct_raw_cost :=
1020 -1 * (l_txn_intf_csr.unit_denom_raw_cost * p_trf_pa_attr_rec.quantity);
1021
1022 l_nl_pa_interface_tbl(i).billable_flag := l_txn_intf_csr.billable_flag;
1023
1024 -- Here we build a new record with +ve quantity and new proj_number and new task_number
1025 i := i + 1;
1026
1027 l_nl_pa_interface_tbl(i) := l_nl_pa_interface_tbl(i-1);
1028
1029 SELECT csi_pa_interface_s.nextval
1030 INTO l_ref_sufix
1031 FROM sys.dual;
1032
1033 SELECT segment1
1034 INTO l_nl_pa_interface_tbl(i).project_number
1035 FROM pa_projects_all
1036 WHERE project_id = p_trf_pa_attr_rec.to_project_id;
1037
1038 SELECT task_number
1039 INTO l_nl_pa_interface_tbl(i).task_number
1040 FROM pa_tasks
1041 WHERE task_id = p_trf_pa_attr_rec.to_task_id;
1042
1043 l_nl_pa_interface_tbl(i).orig_transaction_reference := p_trf_pa_attr_rec.transaction_id||'-'||l_ref_sufix;
1044 l_nl_pa_interface_tbl(i).attribute8 := null;
1045 l_nl_pa_interface_tbl(i).attribute9 := null;
1046 l_nl_pa_interface_tbl(i).attribute10 := null;
1047 l_nl_pa_interface_tbl(i).quantity := p_trf_pa_attr_rec.quantity;
1048
1049 l_nl_pa_interface_tbl(i).denom_raw_cost :=
1050 l_txn_intf_csr.unit_denom_raw_cost * p_trf_pa_attr_rec.quantity;
1051
1052 l_nl_pa_interface_tbl(i).acct_raw_cost :=
1053 l_txn_intf_csr.unit_denom_raw_cost * p_trf_pa_attr_rec.quantity;
1054
1055 EXIT;
1056
1057 END LOOP;
1058 END IF;
1059
1060 debug('l_nl_pa_interface_tbl.count : '||l_nl_pa_interface_tbl.COUNT);
1061
1062 IF l_nl_pa_interface_tbl.COUNT > 0
1063 THEN
1064 debug('Calling API cse_ipa_trans_pkg.populate_pa_interface');
1065 cse_ipa_trans_pkg.populate_pa_interface(
1066 p_nl_pa_interface_tbl => l_nl_pa_interface_tbl,
1067 x_return_status => l_return_status,
1068 x_error_message => l_error_message);
1069 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1070 debug('error_ message : '||l_error_message);
1071 RAISE fnd_api.g_exc_error;
1072 END IF;
1073
1074 --update transaction record with new txn_status_code = 'INTERFACE_TO_PA'
1075 /* --commented for bug 8670632 --
1076 l_txn_rec := CSE_UTIL_PKG.init_txn_rec;
1077 l_txn_rec.transaction_id := p_trf_pa_attr_rec.transaction_id;
1078 l_txn_rec.source_group_ref_id := p_conc_request_id;
1079 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_INTERFACED_TO_PA;
1080 select object_version_number
1081 into l_txn_rec.object_version_number
1082 from csi_transactions
1083 where transaction_id = l_txn_rec.transaction_id;
1084 l_txn_rec.transaction_date := sysdate;
1085 l_txn_rec.source_transaction_date := sysdate;
1086 l_txn_rec.transaction_type_id:= 152; --cse_util_pkg.get_txn_type_id('PROJECT_TRANSFER', l_app_short_name);
1087
1088 debug('Calling API csi_transactions_pvt.update_transactions');
1089 debug(' transaction_id : '||l_txn_rec.transaction_id);
1090
1091 csi_transactions_pvt.update_transactions(
1092 p_api_version => l_api_version,
1093 p_init_msg_list => l_init_msg_list,
1094 p_commit => l_commit,
1095 p_validation_level => l_validation_level,
1096 p_transaction_rec => l_txn_rec,
1097 x_return_status => l_return_status,
1098 x_msg_count => l_msg_count,
1099 x_msg_data => l_msg_data);
1100
1101 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1102 RAISE fnd_api.g_exc_error;
1103 END IF;
1104 --commented for bug 8670632 --
1105 */
1106 END IF;
1107
1108 EXCEPTION
1109 WHEN fnd_api.g_exc_error THEN
1110 x_return_status := l_return_status;
1111 x_error_message := l_error_message;
1112 debug('Error in cse_deployment_grp.interface_nl_to_pa : '||x_error_message);
1113 WHEN OTHERS THEN
1114 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
1115 fnd_message.set_token('ERR_MSG',l_api_name||'='|| SQLERRM);
1116 x_error_message := fnd_message.get;
1117 x_return_status := fnd_api.g_ret_sts_unexp_error;
1118 debug('Inside others exception in cse_deployment_grp.interface_nl_to_pa : ' ||x_error_message);
1119 END interface_nl_to_pa;
1120 -- Added for 8670632--
1121
1122 PROCEDURE process_transaction (
1123 p_instance_tbl IN txn_instances_tbl,
1124 p_dest_location_tbl IN dest_location_tbl,
1125 p_ext_attrib_values_tbl IN OUT NOCOPY txn_ext_attrib_values_tbl,
1126 p_txn_tbl IN OUT NOCOPY transaction_tbl,
1127 x_return_status OUT NOCOPY VARCHAR2,
1128 x_error_msg OUT NOCOPY VARCHAR2 )
1129 IS
1130 l_txn_error_rec csi_datastructures_pub.transaction_error_rec ;
1131 l_txn_rec csi_datastructures_pub.transaction_rec ;
1132 l_t_inst_tbl csi_process_txn_grp.txn_instances_tbl ;
1133 l_t_party_tbl csi_process_txn_grp.txn_i_parties_tbl ;
1134 l_t_pty_acct_tbl csi_process_txn_grp.txn_ip_accounts_tbl ;
1135 l_t_ou_tbl csi_process_txn_grp.txn_org_units_tbl ;
1136 l_t_eav_tbl csi_process_txn_grp.txn_ext_attrib_values_tbl ;
1137 l_t_price_tbl csi_process_txn_grp.txn_pricing_attribs_tbl ;
1138 l_t_ia_tbl csi_process_txn_grp.txn_instance_asset_tbl ;
1139 l_t_iir_tbl csi_process_txn_grp.txn_ii_relationships_tbl ;
1140 l_trf_pa_attr_rec cse_datastructures_pub.Proj_Itm_Insv_PA_ATTR_REC_TYPE; --Added for bug 8670632
1141 l_return_status varchar2(1);
1142 l_msg_data varchar2(2000);
1143 l_msg_count number ;
1144 l_msg_index number ;
1145 l_error_msg varchar2(2000);
1146
1147 ind binary_integer := 0;
1148 l_dest_location_rec csi_process_txn_grp.dest_location_rec ;
1149 l_sysdate date ;
1150 l_redeploy_flag varchar2(1);
1151 l_depreciable varchar2(1);
1152 l_project_id number;
1153 l_task_id number;
1154
1155 l_last_project_id number;
1156 l_last_task_id number;
1157
1158 l_owner_party_id number;
1159 l_owner_party_account_id number;
1160 l_acct_class_code varchar2(80);
1161 l_location_type_code varchar2(80);
1162 l_location_id number;
1163 l_instance_usage_code varchar2(80);
1164 l_operational_status_code varchar2(80);
1165 l_t_eav_tbl_empty csi_process_txn_grp.txn_ext_attrib_values_tbl ;--Added for 9262531
1166 BEGIN
1167
1168 x_return_status := fnd_api.g_ret_sts_success ;
1169
1170 savepoint process_transaction;
1171
1172 cse_util_pkg.set_debug;
1173
1174 csi_t_gen_utility_pvt.build_file_name(
1175 p_file_segment1 => 'cse',
1176 p_file_segment2 => to_char(sysdate, 'DDMONYYYY'));
1177
1178 SELECT sysdate INTO l_sysdate FROM sys.dual;
1179
1180 debug('Inside API cse_deployment_grp.process_transaction '||to_char(l_sysdate, 'dd-mon-yyyy hh24:mi:ss'));
1181 debug(' instance_tbl.count : '||p_instance_tbl.count);
1182 debug(' dest_loc_tbl.count : '||p_dest_location_tbl.count);
1183 debug(' ea_val_tbl.count : '||p_ext_attrib_values_tbl.count);
1184 debug(' txn_tbl.count : '||p_txn_tbl.count);
1185
1186 IF p_instance_tbl.COUNT > 0 THEN
1187 FOR si_ind IN p_instance_tbl.FIRST .. p_instance_tbl.LAST
1188 LOOP
1189
1190 debug('instance_tbl record # '||si_ind);
1191 debug(' instance_id : '||p_instance_tbl(si_ind).instance_id);
1192
1193 SELECT inventory_item_id,
1194 last_vld_organization_id,
1195 serial_number,
1196 lot_number,
1197 inventory_revision,
1198 operational_status_code,
1199 unit_of_measure,
1200 pa_project_id,
1201 pa_project_task_id,
1202 last_pa_project_id,
1203 last_pa_task_id,
1204 owner_party_id,
1205 owner_party_account_id,
1206 accounting_class_code,
1207 location_type_code,
1208 location_id,
1209 instance_usage_code,
1210 operational_status_code
1211 INTO l_t_inst_tbl(1).inventory_item_id ,
1212 l_t_inst_tbl(1).vld_organization_id ,
1213 l_t_inst_tbl(1).serial_number,
1214 l_t_inst_tbl(1).lot_number,
1215 l_t_inst_tbl(1).inventory_revision,
1216 l_t_inst_tbl(1).operational_status_code,
1217 l_t_inst_tbl(1).unit_of_measure,
1218 l_project_id,
1219 l_task_id,
1220 l_last_project_id,
1221 l_last_task_id,
1222 l_owner_party_id,
1223 l_owner_party_account_id,
1224 l_acct_class_code,
1225 l_location_type_code,
1226 l_location_id,
1227 l_instance_usage_code,
1228 l_operational_status_code
1229 FROM csi_item_instances
1230 WHERE instance_id = p_instance_tbl(si_ind).instance_id;
1231
1232 l_t_inst_tbl(1).ib_txn_segment_flag := 'S';
1233 l_t_inst_tbl(1).instance_id := p_instance_tbl(si_ind).instance_id ;
1234 l_t_inst_tbl(1).active_start_date := p_instance_tbl(si_ind).active_start_date ;
1235 l_t_inst_tbl(1).active_end_date := p_instance_tbl(si_ind).active_end_date ;
1236 l_t_inst_tbl(1).instance_status_id := p_instance_tbl(si_ind).instance_status_id;
1237 l_t_inst_tbl(1).quantity := p_txn_tbl(si_ind).transaction_quantity ;
1238
1239 IF l_t_inst_tbl(1).serial_number is not null THEN --4616287
1240 l_t_inst_tbl(1).instance_status_id := p_instance_tbl(si_ind).instance_status_id ;
1241 END IF;
1242
1243 debug(' serial_number : '||l_t_inst_tbl(1).serial_number);
1244 debug(' lot_number : '||l_t_inst_tbl(1).lot_number);
1245 debug(' transaction_quantity : '||l_t_inst_tbl(1).quantity);
1246
1247
1248 -- transaction entity
1249 l_txn_rec.source_group_ref := p_txn_tbl(si_ind).source_group_ref ;
1250 l_txn_rec.source_group_ref_id := p_txn_tbl(si_ind).source_group_ref_id;
1251
1252 IF l_project_id is not null OR l_last_project_id is not null THEN
1253 l_txn_rec.source_header_ref_id := nvl(l_project_id, l_last_project_id);
1254 l_txn_rec.source_line_ref_id := nvl(l_task_id, l_last_task_id);
1255 ELSE
1256 l_txn_rec.source_header_ref_id := p_txn_tbl(si_ind).source_header_ref_id;
1257 l_txn_rec.source_line_ref_id := fnd_api.g_miss_num;
1258 END IF;
1259
1260 l_txn_rec.source_header_ref := p_txn_tbl(si_ind).source_header_ref;
1261 l_txn_rec.source_line_ref := fnd_api.g_miss_char;
1262 l_txn_rec.txn_sub_type_id := p_txn_tbl(si_ind).txn_sub_type_id ;
1263 l_txn_rec.source_transaction_date := p_txn_tbl(si_ind).source_transaction_date ;
1264 l_txn_rec.transaction_quantity := p_txn_tbl(si_ind).transaction_quantity ;
1265
1266
1267 l_txn_rec.transaction_type_id := p_txn_tbl(si_ind).transaction_type_id ;
1268 l_txn_rec.transaction_status_code := 'COMPLETE';
1269
1270 IF p_txn_tbl(si_ind).transaction_type_id = 106 THEN -- Proj Item Install
1271 IF l_project_id IS NULL THEN
1272 l_txn_rec.transaction_type_id := 154; -- item install
1273 END IF;
1274 ELSIF p_txn_tbl(si_ind).transaction_type_id = 109 THEN -- In Service
1275 IF l_last_project_id IS NOT NULL THEN
1276 l_txn_rec.transaction_type_id := 108; -- project item in service
1277 l_txn_rec.transaction_status_code := 'PENDING';
1278 END IF;
1279 -- Added for bug 8628510 -- For cases where project item is put into service without installing it
1280 IF l_project_id IS NOT NULL AND l_last_project_id IS NULL THEN
1281 l_txn_rec.transaction_type_id := 108; -- project item in service
1282 l_txn_rec.transaction_status_code := 'PENDING';
1283 END IF;
1284 ELSIF p_txn_tbl(si_ind).transaction_type_id = 107 THEN -- project item uninstall
1285 IF l_last_project_id IS NULL THEN
1286 l_txn_rec.transaction_type_id := 155; -- item uninstall
1287 END IF;
1288 ELSIF l_txn_rec.transaction_type_id = 111 THEN -- item move
1289 l_txn_rec.transaction_status_code := 'PENDING';
1290 ELSIF p_txn_tbl(si_ind).transaction_type_id = 107 THEN -- project item uninstall
1291 IF l_last_project_id IS NULL THEN
1292 l_txn_rec.transaction_type_id := 155; -- item uninstall
1293 END IF;
1294 END IF;
1295
1296 debug(' transaction_type_id : '||l_txn_rec.transaction_type_id);
1297
1298 -- for customer owned item instances we do not allow updates to FA. these transactions
1299 -- should not be visible for Asset Tracking programs. so mark the txn status as complete.
1300 IF l_owner_party_account_id is not null THEN
1301 l_txn_rec.transaction_status_code := 'COMPLETE';
1302 END IF;
1303
1304 IF p_dest_location_tbl.COUNT > 0 THEN
1305
1306 FOR dl_ind IN p_dest_location_tbl.FIRST .. p_dest_location_tbl.LAST
1307 LOOP
1308
1309 IF p_dest_location_tbl(dl_ind).parent_tbl_index = si_ind THEN
1310
1311 l_dest_location_rec.parent_tbl_index := p_dest_location_tbl(dl_ind).parent_tbl_index ;
1312
1313 IF p_dest_location_tbl(dl_ind).location_type_code = 'HR_LOCATIONS' THEN
1314 l_dest_location_rec.location_type_code := 'INTERNAL_SITE';
1315 ELSE
1316 l_dest_location_rec.location_type_code := p_dest_location_tbl(dl_ind).location_type_code ;
1317 -- Added for bug 8628510 -- For cases where project item is put into service without installing it
1318 IF l_txn_rec.transaction_type_id in (108, 109) AND l_project_id IS NOT NULL AND l_last_project_id IS NULL THEN
1319 l_dest_location_rec.location_type_code := 'INTERNAL_SITE';
1320 END IF;
1321 END IF;
1322 l_dest_location_rec.location_id := p_dest_location_tbl(dl_ind).location_id ;
1323 l_dest_location_rec.last_pa_project_id := p_dest_location_tbl(dl_ind).last_pa_project_id ;
1324 l_dest_location_rec.last_pa_project_task_id := p_dest_location_tbl(dl_ind).last_pa_project_task_id ;
1325 l_dest_location_rec.external_reference := p_dest_location_tbl(dl_ind).external_reference ;
1326 l_dest_location_rec.operational_status_code := p_dest_location_tbl(dl_ind).operational_status_code ;
1327 l_dest_location_rec.instance_usage_code := p_dest_location_tbl(dl_ind).instance_usage_code;
1328
1329 IF l_dest_location_rec.location_type_code = 'PROJECT' THEN
1330 l_dest_location_rec.pa_project_id := p_dest_location_tbl(dl_ind).pa_project_id;
1331 l_dest_location_rec.pa_project_task_id := p_dest_location_tbl(dl_ind).pa_project_task_id;
1332 END IF;
1333
1334 debug(' location_type_code : '||l_dest_location_rec.location_type_code);
1335 debug(' location_id : '||l_dest_location_rec.location_id);
1336
1337 END IF ;
1338 END LOOP ;
1339
1340 ELSE
1341 fnd_message.set_name('CSI','CSI_DPL_INVALID_LOCATION');
1342 fnd_msg_pub.add;
1343 RAISE fnd_api.g_exc_error ;
1344 END IF ; --p_dest_location_tbl.COUNT
1345
1346
1347 -- override destination location attribs based on transaction type
1348 IF l_txn_rec.transaction_type_id in (154,106) THEN --Item Install
1349 l_dest_location_rec.operational_status_code := 'INSTALLED' ;
1350 l_dest_location_rec.instance_usage_code := 'INSTALLED';
1351 IF l_txn_rec.transaction_type_id = 106 THEN
1352 l_dest_location_rec.last_pa_project_id := l_project_id;
1353 l_dest_location_rec.last_pa_project_task_id := l_task_id ;
1354 END IF;
1355
1356 IF l_project_id is not null THEN
1357 l_t_inst_tbl(1).last_pa_project_id := l_project_id;
1358 l_t_inst_tbl(1).last_pa_task_id := l_task_id;
1359 l_t_inst_tbl(1).pa_project_id := null;
1360 l_t_inst_tbl(1).pa_project_task_id := null;
1361 END IF;
1362
1363 ELSIF l_txn_rec.transaction_type_id in (108, 109) THEN -- In Service
1364 l_dest_location_rec.operational_status_code := 'IN_SERVICE' ;
1365 l_dest_location_rec.instance_usage_code := 'IN_SERVICE';
1366 -- Added for bug 8628510 -- For cases where project item is put into service without installing it
1367 IF l_project_id IS NOT NULL AND l_last_project_id IS NULL THEN
1368 l_dest_location_rec.last_pa_project_id := l_project_id;
1369 l_dest_location_rec.last_pa_project_task_id := l_task_id ;
1370 l_t_inst_tbl(1).last_pa_project_id := l_project_id;
1371 l_t_inst_tbl(1).last_pa_task_id := l_task_id;
1372 l_t_inst_tbl(1).pa_project_id := null;
1373 l_t_inst_tbl(1).pa_project_task_id := null;
1374 END IF;
1375 ELSIF l_txn_rec.transaction_type_id = 110 THEN -- out of service
1376 l_dest_location_rec.operational_status_code := 'OUT_OF_SERVICE' ;
1377 l_dest_location_rec.instance_usage_code := 'OUT_OF_SERVICE';
1378 ELSIF l_txn_rec.transaction_type_id = 111 THEN -- item move
1379 --fix for the bug 4620445
1380 IF nvl(l_dest_location_rec.location_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
1381 OR
1382 nvl(l_dest_location_rec.location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1383 THEN
1384 fnd_message.set_name('CSI','CSI_DPL_INVALID_LOCATION');
1385 fnd_msg_pub.add;
1386 RAISE fnd_api.g_exc_error;
1387 END IF;
1388 l_dest_location_rec.operational_status_code := l_operational_status_code;
1389 l_dest_location_rec.instance_usage_code := l_instance_usage_code;
1390 ELSIF l_txn_rec.transaction_type_id in (107, 155) THEN -- uninstall
1391 IF l_last_project_id is not null THEN
1392 l_t_inst_tbl(1).pa_project_id := l_last_project_id;
1393 l_t_inst_tbl(1).pa_project_task_id := l_last_task_id;
1394 l_t_inst_tbl(1).last_pa_project_id := NULL;
1395 l_t_inst_tbl(1).last_pa_task_id := NULL;
1396 l_dest_location_rec.location_type_code := 'PROJECT';
1397 l_dest_location_rec.pa_project_id := l_last_project_id; --Addded for bug 8667816
1398 l_dest_location_rec.pa_project_task_id := l_last_task_id; --Addded for bug 8667816
1399 END IF;
1400 l_dest_location_rec.operational_status_code := 'NOT_USED';
1401 l_dest_location_rec.instance_usage_code := 'IN_PROCESS';
1402 ELSIF l_txn_rec.transaction_type_id = 152 THEN -- project transfer
1403 l_t_inst_tbl(1).location_type_code := l_location_type_code;
1404 l_t_inst_tbl(1).location_id := l_location_id;
1405 IF l_project_id is not null THEN
1406 l_t_inst_tbl(1).pa_project_id := l_project_id;
1407 l_t_inst_tbl(1).pa_project_task_id := l_task_id;
1408 END IF;
1409 IF l_last_project_id is not null THEN
1410 l_t_inst_tbl(1).last_pa_project_id := l_last_project_id;
1411 l_t_inst_tbl(1).last_pa_task_id := l_last_task_id;
1412 END IF;
1413 ELSIF l_txn_rec.transaction_type_id = 104 THEN -- asset retirements
1414 l_dest_location_rec.operational_status_code := 'OUT_OF_SERVICE';
1415 ELSE
1416 fnd_message.set_name('CSI','CSI_INVALID_TXN_TYPE_ID');
1417 fnd_msg_pub.add;
1418 RAISE fnd_api.g_exc_error;
1419 END IF; ---Txn Type
1420
1421 -- Bug 9262531
1422 ind := 0;
1423 IF p_ext_attrib_values_tbl.COUNT > 0 THEN
1424 l_t_eav_tbl := l_t_eav_tbl_empty; --Added for bug 9262531
1425 FOR av_ind IN p_ext_attrib_values_tbl.FIRST .. p_ext_attrib_values_tbl.LAST
1426 LOOP
1427 IF p_ext_attrib_values_tbl(av_ind).parent_tbl_index = si_ind THEN
1428 ind := ind+1;
1429 l_t_eav_tbl(ind).attribute_value_id := p_ext_attrib_values_tbl(av_ind).attribute_value_id ;
1430 -- Bug 9262531
1431 -- The parent tbl index will always be 1
1432 -- as the l_t_inst_tbl is built with index 1
1433 --l_t_eav_tbl(ind).parent_tbl_index := p_ext_attrib_values_tbl(av_ind).parent_tbl_index ;
1434 l_t_eav_tbl(ind).parent_tbl_index := 1 ;
1435 l_t_eav_tbl(ind).instance_id := p_ext_attrib_values_tbl(av_ind).instance_id ;
1436 l_t_eav_tbl(ind).attribute_id := p_ext_attrib_values_tbl(av_ind).attribute_id ;
1437 l_t_eav_tbl(ind).attribute_code := p_ext_attrib_values_tbl(av_ind).attribute_code ;
1438 l_t_eav_tbl(ind).attribute_value := p_ext_attrib_values_tbl(av_ind).attribute_value ;
1439 l_t_eav_tbl(ind).object_version_number := p_ext_attrib_values_tbl(av_ind).object_version_number;
1440 END IF ;
1441 END LOOP ;
1442 END IF ;--p_ext_attribs_values_tbl.COUNT > 0
1443
1444 debug(' instance_usage_code : '||l_dest_location_rec.instance_usage_code);
1445 debug(' operation_status_code : '||l_dest_location_rec.operational_status_code);
1446
1447 -- not taking the retirement transactions thru the process transaction api
1448 IF l_txn_rec.transaction_type_id = 104 THEN
1449 process_retirements(
1450 p_instance_id => p_instance_tbl(si_ind).instance_id,
1451 p_asset_id => p_instance_tbl(si_ind).asset_id,
1452 p_proceeds_of_sale => p_txn_tbl(si_ind).proceeds_of_sale,
1453 p_cost_of_removal => p_txn_tbl(si_ind).cost_of_removal,
1454 p_operational_flag => p_txn_tbl(si_ind).operational_flag, --Bug 8712734
1455 p_financial_flag => p_txn_tbl(si_ind).financial_flag,
1456 px_txn_rec => l_txn_rec,
1457 x_return_status => l_return_status);
1458
1459 IF l_return_status not in (fnd_api.g_ret_sts_success, 'W') THEN
1460 RAISE fnd_api.g_exc_error;
1461 END IF;
1462 ELSE
1463
1464 IF l_owner_party_account_id is not null AND l_t_inst_tbl(1).serial_number is null THEN
1465
1466 debug(' owner_account_id : '||l_owner_party_account_id);
1467 debug(' acct_class_code : '||l_acct_class_code);
1468
1469 -- put logic here to re-build the external party and account
1470 rebuild_child_entities(
1471 p_instance_id => l_t_inst_tbl(1).instance_id,
1472 x_t_party_tbl => l_t_party_tbl,
1473 x_t_pty_acct_tbl => l_t_pty_acct_tbl,
1474 x_t_ou_tbl => l_t_ou_tbl,
1475 x_t_price_tbl => l_t_price_tbl,
1476 x_return_status => l_return_status);
1477
1478 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1479 RAISE fnd_api.g_exc_error;
1480 END IF;
1481
1482 END IF;
1483
1484 csi_process_txn_grp.process_transaction(
1485 p_api_version => 1.0,
1486 p_commit => fnd_api.g_false,
1487 p_init_msg_list => fnd_api.g_false,
1488 p_validation_level => fnd_api.g_valid_level_full,
1489 p_validate_only_flag => fnd_api.g_false,
1490 p_in_out_flag => 'INT',
1491 p_dest_location_rec => l_dest_location_rec ,
1492 p_txn_rec => l_txn_rec ,
1493 p_instances_tbl => l_t_inst_tbl,
1494 p_i_parties_tbl => l_t_party_tbl,
1495 p_ip_accounts_tbl => l_t_pty_acct_tbl,
1496 p_org_units_tbl => l_t_ou_tbl,
1497 p_ext_attrib_vlaues_tbl => l_t_eav_tbl,
1498 p_pricing_attribs_tbl => l_t_price_tbl,
1499 p_instance_asset_tbl => l_t_ia_tbl,
1500 p_ii_relationships_tbl => l_t_iir_tbl,
1501 px_txn_error_rec => l_txn_error_rec,
1502 x_return_status => l_return_status,
1503 x_msg_count => l_msg_count,
1504 x_msg_data => l_msg_data);
1505
1506 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1507 RAISE fnd_api.g_exc_error;
1508 END IF;
1509 END IF; -- retirement/non retirement transactions
1510
1511 --Added for Bug 8670632
1512 IF l_txn_rec.transaction_type_id = 152 THEN -- project transfer
1513
1514 debug('INSTANCE_ID is ' || p_instance_tbl(si_ind).INSTANCE_ID);
1515
1516 l_trf_pa_attr_rec.instance_id := p_instance_tbl(si_ind).INSTANCE_ID;
1517 select INV_MASTER_ORGANIZATION_ID into l_trf_pa_attr_rec.inv_master_org_id
1518 from csi_item_instances where INSTANCE_ID = l_trf_pa_attr_rec.instance_id;
1519
1520 l_trf_pa_attr_rec.serial_number := p_instance_tbl(si_ind).SERIAL_NUMBER;
1521 l_trf_pa_attr_rec.item_id := p_instance_tbl(si_ind).INVENTORY_ITEM_ID;
1522 l_trf_pa_attr_rec.transaction_id := l_txn_rec.TRANSACTION_ID;
1523 l_trf_pa_attr_rec.quantity := p_txn_tbl(si_ind).transaction_quantity;
1524 l_trf_pa_attr_rec.project_id := l_dest_location_rec.last_pa_project_id;
1525 l_trf_pa_attr_rec.task_id := l_dest_location_rec.last_pa_project_task_id;
1526 l_trf_pa_attr_rec.to_project_id := l_dest_location_rec.pa_project_id;
1527 l_trf_pa_attr_rec.to_task_id := l_dest_location_rec.pa_project_task_id;
1528
1529 debug('Calling interface_nl_to_pa');
1530 cse_deployment_grp.interface_nl_to_pa(
1531 p_trf_pa_attr_rec => l_trf_pa_attr_rec,
1532 p_conc_request_id => 111 ,
1533 x_return_status => l_return_status,
1534 x_error_message => l_error_msg );
1535
1536 END IF;
1537 --Added for Bug 8670632
1538 END LOOP ;
1539 END IF ; --p_instance_tbl.COUNT > 0
1540
1541 EXCEPTION
1542 WHEN fnd_api.g_exc_error THEN
1543 x_return_status := fnd_api.g_ret_sts_error;
1544 x_error_msg := nvl(l_error_msg, cse_util_pkg.dump_error_stack);
1545 debug('Error : '||x_error_msg);
1546 rollback to process_transaction;
1547 END process_transaction;
1548
1549 END cse_deployment_grp;