[Home] [Help]
PACKAGE BODY: APPS.CSE_PO_RECEIPT_INTO_PROJECT
Source
1 PACKAGE BODY cse_po_receipt_into_project AS
2 /* $Header: CSEPORCB.pls 120.22.12000000.2 2007/07/02 09:13:05 amourya 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 EXCEPTION
17 WHEN others THEN
18 null;
19 END debug;
20
21 PROCEDURE decode_message(
22 p_msg_header IN xnp_message.msg_header_rec_type,
23 p_msg_text IN varchar2,
24 x_return_status OUT NOCOPY varchar2,
25 x_error_message OUT NOCOPY varchar2,
26 x_rcv_attributes_rec OUT NOCOPY cse_datastructures_pub.rcv_attributes_rec_type)
27 IS
28 l_rcv_transaction_id number;
29 l_file varchar2(500);
30 BEGIN
31
32 x_return_status := g_ret_sts_success;
33 x_error_message := null;
34
35 cse_util_pkg.set_debug;
36
37 debug('==============================================================================');
38 debug('decode_message : po_receipt_into_project :- '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
39
40 debug(' msg.message_id : '||p_msg_header.message_id);
41 debug(' msg.message_code : '||p_msg_header.message_code);
42 debug(' msg.creation_date : '||p_msg_header.creation_date);
43
44 xnp_xml_utils.decode(p_msg_text, 'RCV_TRANSACTION_ID', l_rcv_transaction_id);
45
46 IF (nvl(l_Rcv_Transaction_Id,fnd_api.g_miss_num) = fnd_api.g_miss_num) THEN
47 RAISE fnd_api.g_exc_error;
48 END IF;
49
50 x_rcv_attributes_rec.rcv_transaction_id := l_rcv_transaction_id;
51 x_rcv_attributes_rec.message_Id := p_msg_header.message_id;
52
53 debug(' rcv_transaction_id : '||l_rcv_transaction_id);
54
55 EXCEPTION
56 WHEN fnd_api.g_exc_error THEN
57 fnd_message.set_name('CSE','CSE_DECODE_MSG_ERROR');
58 fnd_message.set_token('MESSAGE_ID',p_msg_header.message_id);
59 fnd_message.set_token('MESSAGE_CODE',p_msg_header.message_code);
60 x_return_status := fnd_api.g_ret_sts_error;
61 x_error_message := fnd_message.get;
62 END decode_message;
63
64 PROCEDURE cleanup_transaction_temps(
65 p_rcv_transaction_id IN number)
66 IS
67 l_interface_transaction_id number;
68 BEGIN
69
70 SELECT interface_transaction_id
71 INTO l_interface_transaction_id
72 FROM rcv_transactions
73 WHERE transaction_id = p_rcv_transaction_id;
74
75 DELETE FROM mtl_serial_numbers_temp
76 WHERE transaction_temp_id = l_interface_transaction_id;
77
78 DELETE FROM mtl_transaction_lots_temp
79 WHERE transaction_temp_id = l_interface_transaction_id;
80
81 END cleanup_transaction_temps;
82
83 PROCEDURE update_csi_data(
84 p_rcv_attributes_rec IN cse_datastructures_pub.rcv_attributes_rec_type,
85 x_rcv_txn_tbl OUT NOCOPY cse_datastructures_pub.rcv_txn_tbl_type,
86 x_return_status OUT NOCOPY varchar2,
87 x_error_message OUT NOCOPY varchar2)
88 IS
89 l_rcv_transaction_id NUMBER;
90 l_instance_rec csi_datastructures_pub.Instance_Rec;
91 l_instance_query_rec csi_datastructures_pub.Instance_Query_Rec;
92 l_api_version NUMBER DEFAULT 1.0;
93 l_commit varchar2(1) DEFAULT FND_API.G_FALSE;
94 l_init_msg_list varchar2(1) DEFAULT FND_API.G_TRUE;
95 l_active_instance_only varchar2(1) DEFAULT FND_API.G_FALSE;
96 l_resolve_id_columns varchar2(1) DEFAULT FND_API.G_FALSE;
97 l_time_stamp varchar2(50);
98 l_object_version_number NUMBER := 1;
99 l_sysdate DATE := SYSDATE;
100 l_instance_header_tbl_out csi_datastructures_pub.instance_header_tbl;
101 l_error_message varchar2(2000);
102 l_return_status varchar2(1);
103 l_msg_count NUMBER;
104 l_msg_data varchar2(2000);
105 l_Party_Query_Rec csi_datastructures_pub.party_query_rec;
106 l_Account_Query_Rec csi_datastructures_pub.party_account_query_rec;
107 l_Validation_Level NUMBER := fnd_api.g_valid_level_full;
108 l_Is_Item_Serialized varchar2(2);
109 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
110 l_party_tbl csi_datastructures_pub.party_tbl;
111 l_account_tbl csi_datastructures_pub.party_account_tbl;
112 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
113 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
114 l_txn_rec csi_datastructures_pub.transaction_rec;
115 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
116 l_Transaction_Id NUMBER;
117 l_Instance_Id_Lst csi_datastructures_pub.Id_Tbl;
118 l_employee_id NUMBER;
119 l_Depreciable varchar2(3);
120 l_Transaction_Status_Code varchar2(30);
121 l_Location_Id NUMBER;
122 l_Location_Type varchar2(30);
123 l_master_org NUMBER;
124 l_dflt_inst_status_id number;
125
126 FUNCTION Is_Expired(P_Status_Id IN NUMBER) RETURN BOOLEAN IS
127 l_Status_Id NUMBER;
128 CURSOR Status_Cur IS
129 SELECT Instance_Status_Id
130 FROM CSI_Instance_Statuses
131 WHERE UPPER(NAME)='EXPIRED';
132 BEGIN
133 OPEN Status_Cur;
134 FETCH Status_Cur INTO l_Status_Id;
135 IF Status_Cur%NOTFOUND THEN
136 l_Status_Id := NULL;
137 END IF;
138
139 CLOSE Status_Cur;
140 IF l_Status_Id = p_Status_id THEN
141 RETURN TRUE;
142 ELSE
143 RETURN FALSE;
144 END IF;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 RETURN FALSE;
149 END Is_Expired;
150
151 BEGIN
152
153 x_return_status := g_ret_sts_success;
154 x_error_message := NULL;
155
156 debug('Inside API cse_po_receipt_into_project.update_csi_data');
157 debug(' rcv_transaction_id : '||p_rcv_attributes_rec.rcv_transaction_id);
158
159 l_rcv_transaction_id := p_rcv_attributes_rec.rcv_transaction_id;
160
161 get_rcv_transaction_details(
162 p_rcv_transaction_id => l_rcv_transaction_id,
163 x_rcv_txn_tbl => x_rcv_txn_tbl,
164 x_return_status => l_return_status,
165 x_error_message => l_error_message);
166
167 IF NOT l_return_status = g_ret_sts_success THEN
168 fnd_message.set_name('CSE','CSE_RCV_VALIDATION_ERROR');
169 fnd_message.set_token('RCV_TRANSACTION_ID',l_Rcv_Transaction_Id);
170 RAISE fnd_api.g_exc_error;
171 END IF;
172
173 IF x_rcv_txn_tbl.count = 0 THEN
174 fnd_message.set_name('CSE','CSE_RCV_DETAILS_NOT_FOUND');
175 fnd_message.set_token('RCV_TRANSACTION_ID',l_Rcv_Transaction_Id);
176 RAISE fnd_api.g_exc_error;
177 END IF;
178
179 IF x_rcv_txn_tbl(1).serial_number IS NULL THEN
180 l_is_item_serialized :='N';
181 debug(' non serialized item');
182 ELSIF NOT x_rcv_txn_tbl(1).serial_number IS NULL THEN
183 debug(' serialized item');
184 l_is_item_serialized :='Y';
185 END IF;
186
187 cse_util_pkg.check_depreciable(
188 p_inventory_item_id => x_rcv_txn_tbl(1).Inventory_Item_Id,
189 p_depreciable => l_Depreciable);
190
191 IF l_depreciable = 'Y' THEN
192 l_transaction_status_code := cse_datastructures_pub.G_PENDING;
193 debug(' depreciable item');
194 ELSIF l_depreciable = 'N' THEN
195 l_transaction_status_code := cse_datastructures_pub.G_COMPLETE;
196 debug(' normal item');
197 END IF;
198
199 l_location_id := cse_util_pkg.get_dflt_project_location_id;
200 l_location_type := cse_util_pkg.get_location_type_code('Project');
201
202 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('PO_RECEIPT_INTO_PROJECT','PO');
203 l_txn_rec.transaction_status_code := l_transaction_status_code;
204 l_txn_rec.source_header_ref_Id := x_rcv_txn_tbl(1).po_header_id;
205 l_txn_rec.source_header_ref := x_rcv_txn_tbl(1).po_number;
206 l_txn_rec.source_line_ref_id := x_rcv_txn_tbl(1).po_line_id;
207 l_txn_rec.source_line_ref := x_rcv_txn_tbl(1).po_line_number;
208 l_txn_rec.source_dist_ref_id1 := x_rcv_txn_tbl(1).po_distribution_id;
209 l_txn_rec.source_dist_ref_id2 := x_rcv_txn_tbl(1).rcv_transaction_id;
210 l_txn_rec.message_id := p_rcv_attributes_rec.message_id;
211 l_txn_rec.transaction_date := sysdate;
212 l_txn_rec.source_transaction_date := x_rcv_txn_tbl(1).transaction_date;
213 l_txn_rec.transaction_quantity := x_rcv_txn_tbl(1).quantity;
214 l_txn_rec.transaction_uom_code := x_rcv_txn_tbl(1).uom;
215 l_txn_rec.transacted_by := cse_util_pkg.get_fnd_employee_id(x_rcv_txn_tbl(1).transacted_by);
216
217 l_dflt_inst_status_id := cse_util_pkg.get_default_status_id(l_txn_rec.transaction_type_id);
218
219 FOR i IN x_rcv_txn_tbl.FIRST .. x_rcv_txn_tbl.LAST
220 LOOP
221
222 debug('processing record # '||i);
223
224 cse_util_pkg.get_master_organization(
225 p_organization_id => x_rcv_txn_tbl(i).Organization_Id,
226 p_master_organization_id => l_Master_Org,
227 x_return_status => l_return_status,
228 x_error_message => l_error_message);
229
230 IF NOT l_return_status = g_ret_sts_success THEN
231 RAISE fnd_api.g_exc_error;
232 END IF;
233
234 l_instance_query_rec := cse_util_pkg.init_instance_query_rec;
235
236 IF x_rcv_txn_tbl(i).serial_number is null THEN
237 l_instance_query_rec.inventory_item_id := x_rcv_txn_tbl(i).inventory_item_id;
238 l_instance_query_rec.inventory_revision := NVL(x_rcv_txn_tbl(i).revision_id,g_miss_char);
239 l_instance_query_rec.inv_master_organization_id := l_master_org;
240 l_instance_query_rec.serial_number := NVL(x_rcv_txn_tbl(i).serial_number,g_miss_char);
241 l_instance_query_rec.lot_number := NVL(x_rcv_txn_tbl(i).lot_number,g_miss_char);
242 l_instance_query_rec.pa_project_id := x_rcv_txn_tbl(i).project_id;
243 l_instance_query_rec.pa_project_task_id := x_rcv_txn_tbl(i).task_id;
244 l_instance_query_rec.instance_usage_code := cse_datastructures_pub.g_in_process;
245 ELSE
246 l_instance_query_rec.inventory_item_id := x_rcv_txn_tbl(i).inventory_item_id;
247 l_instance_query_rec.serial_number := NVL(x_rcv_txn_tbl(i).serial_number,g_miss_char);
248 END IF;
249
250 debug('Calling API csi_item_instance_pub.get_item_instances');
251
252 csi_item_instance_pub.get_item_instances(
253 p_api_version => l_api_version,
254 p_commit => l_commit,
255 p_init_msg_list => l_init_msg_list,
256 p_validation_level => l_Validation_Level,
257 p_instance_Query_rec => l_instance_query_rec,
258 p_party_query_rec => l_Party_Query_Rec,
259 p_account_query_rec => l_account_query_rec,
260 p_transaction_id => l_transaction_id,
261 p_resolve_id_columns => l_resolve_id_columns,
262 p_active_instance_only => l_Active_Instance_Only,
263 x_Instance_Header_Tbl => l_instance_header_tbl_out,
264 x_return_status => l_return_status,
265 x_msg_count => l_msg_count,
266 x_msg_data => l_msg_data );
267
268 IF NOT l_return_status = g_ret_sts_success THEN
269 RAISE fnd_api.g_exc_error;
270 END IF;
271
272 debug(' l_instance_header_tbl_out.count : '||l_instance_header_tbl_out.count);
273
274 IF NOT l_IS_Item_Serialized ='Y' THEN
275
276 IF l_instance_header_tbl_out.COUNT = 0 THEN
277
278 l_instance_rec := cse_util_pkg.init_Instance_Create_Rec;
279 l_instance_rec.inventory_Item_Id := x_rcv_txn_tbl(i).Inventory_Item_Id;
280 l_instance_rec.inventory_Revision := x_rcv_txn_tbl(i).Revision_Id;
281 l_instance_rec.vld_organization_Id := x_rcv_txn_tbl(i).Organization_Id;
282 l_instance_rec.inv_Master_Organization_Id := l_master_org;
283 l_instance_rec.serial_number := NULL; -- remove this later
284 l_instance_rec.lot_number := x_rcv_txn_tbl(i).Lot_Number;
285 l_instance_rec.pa_Project_Id := x_rcv_txn_tbl(i).Project_Id;
286 l_instance_rec.pa_Project_Task_Id := x_rcv_txn_tbl(i).Task_Id;
287 l_instance_rec.quantity := x_rcv_txn_tbl(i).Quantity;
288 l_instance_rec.unit_of_measure := x_rcv_txn_tbl(i).UOM;
289 l_instance_rec.mfg_serial_number_flag := 'N';
290 l_instance_rec.location_id := l_Location_Id;
291 l_instance_rec.location_type_code := l_Location_Type;
292 l_instance_rec.last_po_po_line_id := x_Rcv_txn_Tbl(i).PO_Line_Id; --5184815
293 l_instance_rec.instance_usage_code := cse_datastructures_pub.g_in_process;
294 l_instance_rec.version_label := 'AS-CREATED';
295 l_instance_rec.active_start_date := l_sysdate;
296 l_instance_rec.creation_complete_flag := 'Y';
297 l_instance_rec.customer_view_flag := 'N';
298 l_instance_rec.merchant_view_flag := 'Y';
299 l_instance_rec.object_version_number := l_object_version_number;
300 l_instance_rec.vld_organization_id := x_rcv_txn_tbl(i).organization_id;
301 l_instance_rec.instance_status_id := l_dflt_inst_status_id;
302
303 l_ext_attrib_values_tbl := cse_util_pkg.init_ext_attrib_values_tbl;
304 l_party_tbl := cse_util_pkg.init_party_tbl;
305 l_account_tbl := cse_util_pkg.init_account_tbl;
306 l_pricing_attrib_tbl := cse_util_pkg.init_pricing_attribs_tbl;
307 l_org_assignments_tbl := cse_util_pkg.init_org_assignments_tbl;
308 l_asset_assignment_tbl := cse_util_pkg.init_asset_assignment_tbl;
309
310 debug('Calling API csi_item_instance_pub.create_item_instance - nsrl destination create');
311
312 csi_item_instance_pub.create_item_instance(
313 p_api_version => l_api_version,
314 p_commit => l_commit,
315 p_init_msg_list => l_init_msg_list,
316 p_validation_level => l_validation_level,
317 p_instance_Rec => l_instance_rec,
318 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
319 p_party_tbl => l_party_tbl,
320 p_account_tbl => l_account_tbl,
321 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
322 p_org_assignments_tbl => l_org_assignments_tbl,
323 p_asset_assignment_tbl => l_asset_assignment_tbl,
324 p_txn_rec => l_txn_rec,
325 x_return_status => l_return_status,
326 x_msg_count => l_msg_count,
327 x_msg_data => l_msg_data );
328
329 IF NOT l_return_status = g_ret_sts_success THEN
330 RAISE fnd_api.g_exc_error;
331 END IF;
332
333 debug(' instance_id : '||l_instance_rec.instance_id);
334
335 x_rcv_txn_tbl(i).csi_transaction_id := l_txn_rec.transaction_id;
336
337 ELSIF l_instance_header_tbl_out.COUNT > 0 THEN
338
339 debug('Calling API cse_util_pkg.get_destination_instance');
340
341 cse_util_pkg.get_destination_instance(
342 P_Dest_Instance_Tbl => l_instance_header_tbl_out,
343 X_Instance_Rec => l_instance_rec,
344 x_return_status => l_return_status,
345 x_error_message => l_error_message);
346
347 IF NOT l_return_status = g_ret_sts_success THEN
348 RAISE fnd_api.g_exc_error;
349 END IF;
350
351 debug(' dest_instance_id : '||l_instance_rec.instance_id);
352
353 l_instance_rec.Quantity := l_instance_rec.Quantity + x_rcv_txn_tbl(i).Quantity;
354 l_instance_rec.Active_End_Date := NULL;
355 l_instance_rec.last_po_po_line_id := x_Rcv_txn_Tbl(i).PO_Line_Id; --5184815
356
357 l_party_tbl.DELETE;
358 l_account_tbl := cse_util_pkg.init_account_tbl;
359 l_pricing_attrib_tbl := cse_util_pkg.init_pricing_attribs_tbl;
360 l_org_assignments_tbl := cse_util_pkg.init_org_assignments_tbl;
361 l_asset_assignment_tbl := cse_util_pkg.init_asset_assignment_tbl;
362 l_ext_attrib_values_tbl := cse_util_pkg.init_ext_attrib_values_tbl;
363
364 IF is_expired(l_instance_rec.instance_status_id) THEN
365 l_instance_rec.instance_status_id := l_dflt_inst_status_id;
366 END IF;
367
368 debug('Calling API csi_item_instance_pub.update_item_instance - nsrl destination update');
369
370 csi_item_instance_pub.update_item_instance(
371 p_api_version => l_api_version,
372 p_commit => l_commit,
373 p_validation_level => l_Validation_Level,
374 p_init_msg_list => l_init_msg_list,
375 p_instance_rec => l_instance_rec,
376 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
377 p_party_tbl => l_party_tbl,
378 p_account_tbl => l_account_tbl,
379 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
380 p_org_assignments_tbl => l_org_assignments_tbl,
381 p_txn_rec => l_txn_rec,
382 p_asset_assignment_tbl => l_asset_assignment_tbl,
383 x_instance_id_lst => l_instance_id_lst,
384 x_return_status => l_return_status,
385 x_msg_count => l_msg_count,
386 x_msg_data => l_msg_data );
387
388 IF NOT l_return_status = g_ret_sts_success THEN
389 RAISE fnd_api.g_exc_error;
390 END IF;
391 x_rcv_txn_tbl(i).CSI_Transaction_Id := l_txn_rec.Transaction_Id;
392 END IF;
393
394 ELSIF l_IS_Item_Serialized ='Y' THEN
395
396 IF l_instance_header_tbl_out.COUNT = 1 THEN
397
398 debug(' dest_instance_id : '||l_instance_header_tbl_out(1).instance_id);
399
400 l_instance_rec := cse_util_pkg.init_instance_update_rec;
401 l_instance_rec.instance_id := l_instance_header_tbl_out(1).instance_id;
402 l_instance_rec.Quantity := 1;
403 l_instance_rec.last_po_po_line_id := x_Rcv_txn_Tbl(i).PO_Line_Id; --5184815
404 l_instance_rec.Object_version_Number := l_instance_header_tbl_out(1).Object_Version_Number;
405 l_instance_rec.active_end_date := null;
406 l_instance_rec.instance_usage_code := cse_datastructures_pub.g_in_process;
407
408 IF is_expired(l_instance_rec.instance_status_id) THEN
409 l_instance_rec.instance_status_id := l_dflt_inst_status_id;
410 END IF;
411
412 l_party_tbl.DELETE;
413 l_account_tbl := cse_util_pkg.init_account_tbl;
414 l_pricing_attrib_tbl := cse_util_pkg.init_pricing_attribs_tbl;
415 l_org_assignments_tbl := cse_util_pkg.init_org_assignments_tbl;
416 l_asset_assignment_tbl := cse_util_pkg.init_asset_assignment_tbl;
417 l_ext_attrib_values_tbl := cse_util_pkg.init_ext_attrib_values_tbl;
418
419 debug('Calling API csi_item_instance_pub.update_item_instance - srl destination update');
420
421 csi_item_instance_pub.update_item_instance(
422 p_api_version => l_api_version,
423 p_commit => l_commit,
424 p_validation_level => l_Validation_Level,
425 p_init_msg_list => l_init_msg_list,
426 p_instance_rec => l_instance_rec,
427 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
428 p_party_tbl => l_party_tbl,
429 p_account_tbl => l_account_tbl,
430 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
431 p_org_assignments_tbl => l_org_assignments_tbl,
432 p_txn_rec => l_txn_rec,
433 p_asset_assignment_tbl => l_asset_assignment_tbl,
434 x_instance_id_lst => l_instance_id_lst,
435 x_return_status => l_return_status,
436 x_msg_count => l_msg_count,
437 x_msg_data => l_msg_data );
438
439 IF NOT l_return_status = g_ret_sts_success THEN
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 x_rcv_txn_tbl(i).csi_transaction_id := l_txn_rec.transaction_id;
444
445 ELSIF l_instance_header_tbl_out.COUNT = 0 THEN
446
447 l_instance_rec := cse_util_pkg.init_instance_create_rec;
448 l_instance_rec.inventory_item_id := x_rcv_txn_tbl(i).inventory_item_id;
449 l_instance_rec.inventory_revision := x_rcv_txn_tbl(i).revision_id;
450 l_instance_rec.vld_organization_id := x_rcv_txn_tbl(i).organization_id;
451 l_instance_rec.inv_master_organization_id := l_master_org;
452 l_instance_rec.serial_number := x_rcv_txn_tbl(i).serial_number;
453 l_instance_rec.lot_number := x_rcv_txn_tbl(i).lot_number;
454 l_instance_rec.pa_project_id := x_rcv_txn_tbl(i).project_id;
455 l_instance_rec.pa_project_task_id := x_rcv_txn_tbl(i).task_id;
456 l_instance_rec.last_po_po_line_id := x_Rcv_txn_Tbl(i).PO_Line_Id; --5184815
457 l_instance_rec.quantity := 1;
458 l_instance_rec.unit_of_measure := x_rcv_txn_tbl(i).uom;
459 l_instance_rec.mfg_serial_number_flag := 'Y';
460 l_instance_rec.instance_usage_code := cse_datastructures_pub.g_in_process;
461 l_instance_rec.version_label := 'AS-CREATED';
462 l_instance_rec.active_start_date := l_sysdate;
463 l_instance_rec.active_end_date := NULL;
464 l_instance_rec.creation_complete_flag := 'Y';
465 l_instance_rec.customer_view_flag := 'N';
466 l_instance_rec.merchant_view_flag := 'Y';
467 l_instance_rec.object_version_number := l_object_version_number;
468 l_instance_rec.vld_organization_id := x_rcv_txn_tbl(i).organization_id;
469 l_instance_rec.location_id := l_location_id;
470 l_instance_rec.location_type_code := l_location_type;
471 l_instance_rec.instance_status_id := l_dflt_inst_status_id;
472
473 l_party_tbl := cse_util_pkg.init_party_tbl;
474 l_account_tbl := cse_util_pkg.init_account_tbl;
475 l_pricing_attrib_tbl := cse_util_pkg.init_pricing_attribs_tbl;
476 l_org_assignments_tbl := cse_util_pkg.init_org_assignments_tbl;
477 l_asset_assignment_tbl := cse_util_pkg.init_asset_assignment_tbl;
478 l_ext_attrib_values_tbl := cse_util_pkg.init_ext_attrib_values_tbl;
479
480 debug('Calling API csi_item_instance_pub.create_item_instance - srl destination create');
481
482 csi_item_instance_pub.create_item_instance(
483 p_api_version => l_api_version,
484 p_commit => l_commit,
485 p_init_msg_list => l_init_msg_list,
486 p_validation_level => l_validation_level,
487 p_instance_Rec => l_instance_rec,
488 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
489 p_party_tbl => l_party_tbl,
490 p_account_tbl => l_account_tbl,
491 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
492 p_org_assignments_tbl => l_org_assignments_tbl,
493 p_asset_assignment_tbl => l_asset_assignment_tbl,
494 p_txn_rec => l_txn_rec,
495 x_return_status => l_return_status,
496 x_msg_count => l_msg_count,
497 x_msg_data => l_msg_data );
498
499 IF NOT l_return_status = g_ret_sts_success THEN
500 RAISE fnd_api.g_exc_error;
501 END IF;
502
503 debug(' instance_id : '||l_instance_rec.instance_id);
504
505 x_rcv_txn_tbl(i).csi_transaction_id := l_txn_rec.transaction_id;
506
507 END IF;
508
509 BEGIN
510 IF x_rcv_txn_tbl(i).serial_number IS NOT NULL THEN
511 UPDATE mtl_serial_numbers
512 SET current_status = 4,
513 last_txn_source_name = 'CSE_PO_RECEIPT',
514 last_txn_source_id = x_rcv_txn_tbl(i).rcv_transaction_id
515 WHERE inventory_item_id = x_rcv_txn_tbl(i).inventory_item_id
516 AND serial_number = x_rcv_txn_tbl(i).serial_number;
517 END IF;
518 END;
519
520 END IF; -- serial
521 END LOOP;
522
523 debug(' csi_transaction_id : '||l_txn_rec.transaction_id);
524 debug('update_csi_data successful. rcv_transaction_id : '||p_rcv_attributes_rec.rcv_transaction_id);
525 commit;
526
527 EXCEPTION
528 WHEN fnd_api.g_exc_error THEN
529 x_return_status := g_ret_sts_error;
530 x_error_message := nvl(l_error_message, cse_util_pkg.dump_error_stack);
531 WHEN OTHERS THEN
532 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
533 fnd_message.set_token('ERR_MSG', SQLERRM);
534 x_error_message := fnd_message.get;
535 x_return_status := g_ret_sts_unexp_error;
536 END update_csi_data;
537
538 PROCEDURE get_rcv_transaction_details(
539 p_rcv_transaction_id IN NUMBER,
540 x_rcv_txn_tbl OUT NOCOPY cse_datastructures_pub.rcv_txn_tbl_type,
541 x_return_status OUT NOCOPY varchar2,
542 x_error_message OUT NOCOPY varchar2)
543 IS
544
545 l_lot_code number;
546 l_serial_code number;
547
548 l_rcv_txn_rec cse_datastructures_pub.rcv_txn_rec_type;
549 l_rcv_txn_tbl cse_datastructures_pub.rcv_txn_tbl_type;
550 ind binary_integer := 0;
551
552 CURSOR rcvtxn_cur(p_rcv_txn_id IN NUMBER) IS
553 SELECT rt.transaction_id transaction_id,
554 rt.transaction_date transaction_date,
555 rt.transaction_type transaction_type,
556 rt.destination_type_code destination_type_code,
557 rt.employee_id transacted_by,
558 rt.organization_id organization_id,
559 rt.quantity quantity,
560 rt.po_header_id po_header_id,
561 rt.po_line_id po_line_id,
562 rt.po_distribution_id po_distribution_id,
563 rt.uom_code txn_uom_code,
564 rt.vendor_id po_vendor_id,
565 rt.shipment_header_id shipment_header_id,
566 rt.shipment_line_id shipment_line_id,
567 rt.interface_transaction_id interface_transaction_id,
568 pda.project_id project_id,
569 pda.task_id task_id,
570 pda.rate rate,
571 pda.org_id org_id,
572 plla.price_override price_override,
573 pla.item_id item_id,
574 pla.item_revision item_revision,
575 to_char(pla.line_num) po_line_number,
576 pha.segment1 po_number
577 FROM rcv_transactions rt,
578 po_distributions_all pda,
579 po_line_locations_all plla,
580 po_lines_all pla,
581 po_headers_all pha
582 WHERE rt.transaction_id = p_rcv_txn_id
583 AND rt.po_distribution_id = pda.po_distribution_id
584 AND rt.po_line_location_id = plla.line_location_id
585 AND rt.po_line_id = pla.po_line_id
586 AND rt.po_header_id = pha.po_header_id;
587
588 CURSOR lotsrl_cur(p_interface_transaction_id in number, p_quantity in number) IS
589 SELECT mtlt.lot_number lot_number,
590 msn.serial_number serial_number,
591 decode(mtlt.serial_transaction_temp_id,null,nvl(mtlt.transaction_quantity,p_quantity),1) quantity
592 FROM mtl_transaction_lots_temp mtlt,
593 mtl_serial_numbers msn
594 WHERE mtlt.transaction_temp_id = p_interface_transaction_id
595 AND msn.line_mark_id(+) = mtlt.serial_transaction_temp_id;
596
597 CURSOR srl_cur(p_interface_transaction_id in number) IS
598 SELECT msn.serial_number serial_number
599 FROM mtl_serial_numbers msn
600 WHERE EXISTS (
601 SELECT 'x' FROM mtl_serial_numbers_temp msnt
602 WHERE msnt.transaction_temp_id = p_interface_transaction_id
603 AND msnt.transaction_temp_id = msn.line_mark_id) ;
604
605 BEGIN
606
607 x_return_status := g_ret_sts_success;
608 x_error_message := null;
609
610 debug('Inside API cse_po_receipt_into_project.get_rcv_transaction_details');
611 debug(' rcv_transaction_id : '||p_rcv_transaction_id);
612
613 FOR rcvtxn_rec IN rcvtxn_cur(p_rcv_transaction_id)
614 LOOP
615
616 mo_global.set_policy_context('S', rcvtxn_rec.org_id);
617
618 debug(' po_number : '||rcvtxn_rec.po_number);
619 debug(' po_line_number : '||rcvtxn_rec.po_line_number);
620 debug(' po_header_id : '||rcvtxn_rec.po_header_id);
621 debug(' po_line_id : '||rcvtxn_rec.po_line_id);
622 debug(' po_distribution_id : '||rcvtxn_rec.po_distribution_id);
623 debug(' po_vendor_id : '||rcvtxn_rec.po_vendor_id);
624 debug(' project_id : '||rcvtxn_rec.project_id);
625 debug(' task_id : '||rcvtxn_rec.task_id);
626 debug(' quantity : '||rcvtxn_rec.quantity);
627 debug(' uom_code : '||rcvtxn_rec.txn_uom_code);
628 debug(' intf_transaction_id : '||rcvtxn_rec.interface_transaction_id);
629
630 SELECT primary_uom_code,
631 serial_number_control_code,
632 lot_control_code
633 INTO l_rcv_txn_rec.uom,
634 l_serial_code,
635 l_lot_code
636 FROM mtl_system_items
637 WHERE inventory_item_id = rcvtxn_rec.item_id
638 AND organization_id = rcvtxn_rec.organization_id;
639
640 l_rcv_txn_rec.rcv_transaction_id := rcvtxn_rec.transaction_id;
641 l_rcv_txn_rec.temp_txn_id := rcvtxn_rec.interface_transaction_id;
642 l_rcv_txn_rec.organization_id := rcvtxn_rec.organization_id;
643 l_rcv_txn_rec.po_header_id := rcvtxn_rec.po_header_id;
644 l_rcv_txn_rec.po_number := rcvtxn_rec.po_number;
645 l_rcv_txn_rec.po_line_id := rcvtxn_rec.po_line_id;
646 l_rcv_txn_rec.po_line_number := rcvtxn_rec.po_line_number;
647 l_rcv_txn_rec.po_distribution_id := rcvtxn_rec.po_distribution_id;
648 l_rcv_txn_rec.project_id := rcvtxn_rec.project_id;
649 l_rcv_txn_rec.task_id := rcvtxn_rec.task_id;
650 l_rcv_txn_rec.transacted_by := rcvtxn_rec.transacted_by;
651 l_rcv_txn_rec.transaction_date := rcvtxn_rec.transaction_date;
652 l_rcv_txn_rec.inventory_item_id := rcvtxn_rec.item_id;
653 l_rcv_txn_rec.revision_id := rcvtxn_rec.item_revision;
654 l_rcv_txn_rec.lot_number := null;
655 l_rcv_txn_rec.serial_number := null;
656 l_rcv_txn_rec.quantity := rcvtxn_rec.quantity;
657 l_rcv_txn_rec.amount := rcvtxn_rec.price_override*nvl(rcvtxn_rec.rate,1)* rcvtxn_rec.quantity;
658 l_rcv_txn_rec.po_vendor_id := rcvtxn_rec.po_vendor_id;
659 l_rcv_txn_rec.transaction_type := rcvtxn_rec.transaction_type;
660 l_rcv_txn_rec.destination_type_code := rcvtxn_rec.destination_type_code;
661
662 IF rcvtxn_rec.project_id IS NOT NULL THEN
663
664 IF l_lot_code <> 1 THEN
665 FOR lotsrl_rec IN lotsrl_cur(rcvtxn_rec.interface_transaction_id, rcvtxn_rec.quantity)
666 LOOP
667 ind := ind + 1;
668 l_rcv_txn_tbl(ind) := l_rcv_txn_rec;
669 l_rcv_txn_tbl(ind).lot_number := lotsrl_rec.lot_number;
670 l_rcv_txn_tbl(ind).serial_number := lotsrl_rec.serial_number;
671 l_rcv_txn_tbl(ind).quantity := lotsrl_rec.quantity;
672 l_rcv_txn_tbl(ind).amount := rcvtxn_rec.price_override* nvl(rcvtxn_rec.Rate,1)* lotsrl_rec.quantity;
673 END LOOP;
674 ELSIF l_serial_code <> 1 and l_lot_code = 1 THEN
675 FOR srl_rec IN srl_cur(rcvtxn_rec.interface_transaction_id)
676 LOOP
677 ind := ind + 1;
678 l_rcv_txn_tbl(ind) := l_rcv_txn_rec;
679 l_rcv_txn_tbl(ind).lot_number := null;
680 l_rcv_txn_tbl(ind).serial_number := srl_rec.serial_number;
681 l_rcv_txn_tbl(ind).quantity := 1;
682 l_rcv_txn_tbl(ind).amount := rcvtxn_rec.price_override* nvl(rcvtxn_rec.rate,1);
683 END LOOP;
684 ELSE -- non serial
685 ind := ind + 1;
686 l_rcv_txn_tbl(ind) := l_rcv_txn_rec;
687 END IF;
688 END IF;
689 END LOOP;
690
691 debug('rcv_txn_tbl.COUNT : '||l_rcv_txn_tbl.count);
692
693 IF l_rcv_txn_tbl.count > 0 THEN
694 FOR ind IN l_rcv_txn_tbl.FIRST .. l_rcv_txn_tbl.LAST
695 LOOP
696 debug('record # '||ind);
697 debug(' serial_number : '||l_rcv_txn_tbl(ind).serial_number);
698 debug(' lot_number : '||l_rcv_txn_tbl(ind).lot_number);
699 debug(' quantity : '||l_rcv_txn_tbl(ind).quantity);
700 debug(' amount : '||l_rcv_txn_tbl(ind).amount);
701 END LOOP;
702 END IF;
703
704 x_rcv_txn_tbl := l_rcv_txn_tbl;
705
706 EXCEPTION
707 WHEN fnd_api.g_exc_error THEN
708 x_return_status := fnd_api.g_ret_sts_error;
709 x_error_message := fnd_message.get;
710 END get_rcv_transaction_details;
711
712 PROCEDURE knock_the_commitment(
713 p_rcv_transaction_id IN number,
714 x_return_status OUT NOCOPY varchar2)
715 IS
716 l_sql_stmt varchar2(540);
717 BEGIN
718
719 x_return_status := fnd_api.g_ret_sts_success;
720
721 l_sql_stmt := 'UPDATE rcv_receiving_sub_ledger '||
722 'SET pa_addition_flag = ''Y'''||
723 'WHERE rcv_transaction_id = :rcv_txn_id ';
724 BEGIN
725 execute immediate l_sql_stmt using p_rcv_transaction_id;
726 UPDATE rcv_transactions
727 SET pa_addition_flag = 'Y'
728 WHERE transaction_id = p_rcv_transaction_id;
729 EXCEPTION
730 WHEN others THEN
731 UPDATE rcv_transactions
732 SET pa_addition_flag = 'Y'
733 WHERE transaction_id = p_rcv_transaction_id;
734 END;
735
736 EXCEPTION
737 WHEN fnd_api.g_exc_error THEN
738 x_return_status := fnd_api.g_ret_sts_success;
739 END knock_the_commitment;
740
741 PROCEDURE interface_nl_to_pa(
742 p_rcv_txn_tbl IN cse_datastructures_pub.Rcv_Txn_Tbl_Type,
743 x_return_status OUT NOCOPY varchar2,
744 x_error_message OUT NOCOPY varchar2)
745 IS
746 l_Item_Name varchar2(40);
747 l_project_name varchar2(150);
748 l_Task_Number varchar2(100);
749 l_nl_pa_interface_tbl pa_interface_tbl_type;
750 l_Transaction_Source varchar2(30);
751 l_Transaction_Type varchar2(50);
752 l_Batch_Name varchar2(50);
753 l_Cr_Code_Combination_Id NUMBER DEFAULT NULL;
754 l_Dr_Code_Combination_Id NUMBER DEFAULT NULL;
755 l_Price_Var_CC_Id NUMBER DEFAULT NULL;
756 l_User_Id NUMBER;
757 l_System_Linkage varchar2(3) := 'VI';
758 l_return_status varchar2(1);
759 l_error_message varchar2(2000);
760 l_Sysdate DATE := SYSDATE;
761 l_Expenditure_Ending_Date DATE;
762 l_Depreciable varchar2(3);
763 l_Vendor_Num varchar2(50);
764 l_vendor_id number;
765 l_Operating_Unit NUMBER;
766
767 CURSOR item_name_cur(p_item_id IN number, p_org_id IN number) IS
768 SELECT concatenated_segments
769 FROM mtl_system_items_kfv
770 WHERE inventory_item_id = p_item_id
771 AND organization_id = p_org_id;
772
773 CURSOR project_name_cur(p_project_id IN NUMBER) IS
774 SELECT segment1
775 FROM pa_projects_all
776 WHERE project_id = p_project_id;
777
778 CURSOR Task_Number_Cur(P_Project_Id IN NUMBER, P_Task_Id IN NUMBER) IS
779 SELECT pt.task_number
780 FROM pa_tasks pt
781 WHERE pt.task_id = p_task_id
782 AND pt.project_id = p_project_id;
783
784 CURSOR vendor_cur(p_po_header_id IN number) IS
785 SELECT pv.vendor_id, pv.segment1
786 FROM po_vendors pv, po_headers_all ph
787 WHERE ph.po_header_id = p_po_header_id
788 AND pv.vendor_id = ph.vendor_id;
789
790 CURSOR Exp_Details_Cur(P_PO_Distribution_ID IN NUMBER) IS
791 SELECT pod.Org_Id Org_ID,
792 SYSDATE Expenditure_Item_Date,
793 pod.expenditure_type Expenditure_Type,
794 pod.expenditure_organization_id Expenditure_Org_Id,
795 pod.code_combination_id Dr_CC_Id,
796 hr.Name Expenditure_Organization_Name
797 FROM po_distributions_all pod,
798 hr_organization_units hr
799 WHERE pod.po_distribution_id = p_po_distribution_id
800 AND hr.organization_id = pod.Expenditure_Organization_Id;
801
802 Exp_Details_Rec Exp_Details_Cur%ROWTYPE;
803
804 CURSOR cr_cc_cur(p_org_id in number) IS
805 SELECT accts_pay_code_combination_id
806 FROM ap_system_parameters_all
807 WHERE org_id = p_org_id;
808
809 BEGIN
810
811 x_return_status := g_ret_sts_success;
812
813 debug('Inside API cse_po_receipt_into_project.interface_nl_to_pa');
814
815 IF p_rcv_txn_tbl.COUNT = 0 THEN
816 fnd_message.set_name('CSE','CSE_RCV_TXN_TBL_NO_ROWS');
817 fnd_msg_pub.add;
818 RAISE FND_API.g_Exc_Error;
819 END IF;
820
821 l_user_id := fnd_global.user_id ;
822
823 OPEN Project_Name_Cur(p_rcv_txn_tbl(1).Project_Id);
824 FETCH Project_Name_Cur INTO l_Project_Name;
825 CLOSE Project_Name_Cur;
826
827 debug(' project_name : '||l_project_name);
828
829 OPEN Task_Number_Cur(p_rcv_txn_tbl(1).Project_Id, p_rcv_txn_tbl(1).Task_Id);
830 FETCH Task_Number_Cur INTO l_Task_Number;
831 CLOSE Task_Number_Cur;
832
833 debug(' task_number : '||l_task_number);
834
835 OPEN Exp_Details_Cur(p_rcv_txn_tbl(1).po_distribution_id);
836 FETCH Exp_Details_Cur INTO Exp_Details_Rec;
837 CLOSE Exp_Details_Cur;
838
839 l_Expenditure_Ending_Date:= pa_utils.getweekending(Exp_Details_Rec.expenditure_item_date);
840 l_Dr_Code_Combination_Id := Exp_Details_Rec.Dr_CC_Id;
841 l_Operating_Unit := Exp_Details_Rec.org_id;
842
843 OPEN Cr_CC_Cur(exp_details_rec.org_id);
844 FETCH Cr_CC_Cur INTO l_Cr_Code_Combination_Id;
845 CLOSE Cr_CC_Cur;
846
847 OPEN Item_Name_Cur(p_rcv_txn_tbl(1).Inventory_Item_Id, p_rcv_txn_tbl(1).Organization_Id);
848 FETCH Item_Name_Cur INTO l_Item_Name;
849 CLOSE Item_Name_Cur;
850
851 debug(' item_name : '||l_item_name);
852
853 cse_util_pkg.check_depreciable(
854 p_inventory_item_id => p_rcv_txn_tbl(1).inventory_item_id,
855 p_depreciable => l_depreciable);
856
857 IF l_depreciable ='Y' THEN
858 l_transaction_source :='CSE_PO_RECEIPT_DEPR';
859 ELSIF l_depreciable ='N' THEN
860 l_transaction_source :='CSE_PO_RECEIPT';
861 END IF;
862
863 OPEN Vendor_Cur(p_rcv_txn_tbl(1).PO_Header_Id);
864 FETCH Vendor_Cur INTO l_vendor_id, l_vendor_num;
865 CLOSE Vendor_Cur;
866
867 debug(' vendor_number : '||l_vendor_num);
868
869 FOR i IN p_rcv_txn_tbl.FIRST ..p_rcv_txn_tbl.LAST
870 LOOP
871
872 l_nl_pa_interface_tbl(i).transaction_source := l_transaction_source;
873 l_nl_pa_interface_tbl(i).batch_name := to_char(p_rcv_txn_tbl(i).csi_transaction_id);
874 l_nl_pa_interface_tbl(i).expenditure_ending_date := l_expenditure_ending_date;
875 l_nl_pa_interface_tbl(i).organization_name := exp_details_rec.expenditure_organization_name;
876 l_nl_pa_interface_tbl(i).expenditure_item_date := exp_details_rec.expenditure_item_date;
877 l_nl_pa_interface_tbl(i).project_number := l_project_name;
878 l_nl_pa_interface_tbl(i).task_number := l_task_number;
879 l_nl_pa_interface_tbl(i).expenditure_type := exp_details_rec.expenditure_type;
880 l_nl_pa_interface_tbl(i).quantity := p_rcv_txn_tbl(i).quantity;
881 l_nl_pa_interface_tbl(i).expenditure_comment := 'ENTERPRISE INSTALL BASE';
882 l_nl_pa_interface_tbl(i).transaction_status_Code := 'P';
883
884 IF p_rcv_txn_tbl(i).serial_number IS NOT NULL THEN
885 l_nl_pa_interface_tbl(i).orig_transaction_reference :=
886 p_rcv_txn_tbl(i).Rcv_Transaction_Id||'-'||p_rcv_txn_tbl(i).Serial_Number;
887 ELSE
888 l_nl_pa_interface_tbl(i).Orig_Transaction_Reference:= p_rcv_txn_tbl(i).rcv_transaction_id;
889 END IF;
890
891 l_nl_pa_interface_tbl(i).attribute6 := l_item_name;
892 l_nl_pa_interface_tbl(i).attribute7 := p_rcv_txn_tbl(i).serial_number;
893 l_nl_pa_interface_tbl(i).attribute8 := null;
894 l_nl_pa_interface_tbl(i).attribute9 := null;
895 l_nl_pa_interface_tbl(i).attribute10 := null;
896 l_nl_pa_interface_tbl(i).interface_id := null;
897 l_nl_pa_interface_tbl(i).org_Id := l_operating_unit;
898 l_nl_pa_interface_tbl(i).dr_code_combination_id := l_dr_code_combination_id;
899 l_nl_pa_interface_tbl(i).cr_code_combination_id := l_cr_code_combination_id;
900 l_nl_pa_interface_tbl(i).cdl_system_reference1 := p_rcv_txn_tbl(i).po_vendor_id;
901 l_nl_pa_interface_tbl(i).cdl_system_reference2 := p_rcv_txn_tbl(i).po_header_id;
902 l_nl_pa_interface_tbl(i).cdl_system_reference3 := p_rcv_txn_tbl(i).po_distribution_id;
903 l_nl_pa_interface_tbl(i).cdl_system_reference4 := p_rcv_txn_tbl(i).rcv_transaction_id;
904 l_nl_pa_interface_tbl(i).cdl_system_reference5 :=
905 cse_asset_util_pkg.get_rcv_sub_ledger_id(p_rcv_txn_tbl(i).rcv_transaction_id);
906 l_nl_pa_interface_tbl(i).gl_date := l_Expenditure_Ending_Date;
907 l_nl_pa_interface_tbl(i).system_linkage := l_System_Linkage;
908 l_nl_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE';
909 l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
910 l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
911 l_nl_pa_interface_tbl(i).creation_date := l_sysdate;
912 l_nl_pa_interface_tbl(i).created_by := l_user_id;
913 l_nl_pa_interface_tbl(i).vendor_number := l_vendor_num;
914 l_nl_pa_interface_tbl(i).acct_raw_cost := p_rcv_txn_tbl(i).amount;
915 l_nl_pa_interface_tbl(i).denom_raw_cost := p_rcv_txn_tbl(i).amount;
916 l_nl_pa_interface_tbl(i).billable_flag := 'Y';
917 l_nl_pa_interface_tbl(i).unmatched_negative_txn_flag := 'Y';
918 l_nl_pa_interface_tbl(i).organization_id := p_rcv_txn_tbl(i).organization_id;
919 l_nl_pa_interface_tbl(i).inventory_item_id := p_rcv_txn_tbl(i).inventory_item_id;
920 l_nl_pa_interface_tbl(i).po_header_id := p_rcv_txn_tbl(i).po_header_id;
921 l_nl_pa_interface_tbl(i).po_line_id := p_rcv_txn_tbl(i).po_line_id;
922 l_nl_pa_interface_tbl(i).po_number := p_rcv_txn_tbl(i).po_number;
923 l_nl_pa_interface_tbl(i).po_line_num := p_rcv_txn_tbl(i).po_line_number;
924 l_nl_pa_interface_tbl(i).vendor_id := l_vendor_id;
925 l_nl_pa_interface_tbl(i).project_id := p_rcv_txn_tbl(i).project_id;
926 l_nl_pa_interface_tbl(i).task_id := p_rcv_txn_tbl(i).task_id;
927 l_nl_pa_interface_tbl(i).document_type := p_rcv_txn_tbl(i).destination_type_code;
928 l_nl_pa_interface_tbl(i).document_distribution_type := p_rcv_txn_tbl(i).transaction_type;
929
930 END LOOP;
931
932 debug(' pa_interface_tbl.count : '||l_nl_pa_interface_tbl.COUNT);
933
934 IF NOT l_nl_pa_interface_tbl.COUNT = 0 THEN
935
936 cse_ipa_trans_pkg.populate_pa_interface(
937 p_nl_pa_interface_tbl => l_nl_pa_interface_tbl,
938 x_return_status => l_return_status,
939 x_error_message => l_error_message);
940
941 IF l_return_status = fnd_api.g_ret_sts_success THEN
942 knock_the_commitment(
943 p_rcv_transaction_id => p_rcv_txn_tbl(1).rcv_transaction_id,
944 x_return_status => l_return_status);
945 commit;
946 END IF;
947
948 END IF;
949
950 x_return_status := l_return_status;
951 x_error_message := l_error_message;
952
953 debug('interface_nl_to_pa successful. rcv_transaction_id : '||p_rcv_txn_tbl(1).rcv_transaction_id);
954 debug('==============================================================================');
955
956 EXCEPTION
957 WHEN FND_API.G_Exc_Error THEN
958 x_error_message := fnd_message.get;
959 x_return_status := g_ret_sts_error;
960 WHEN OTHERS THEN
961 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
962 fnd_message.set_token('ERR_MSG', SQLERRM);
963 x_error_message := fnd_message.get;
964 x_return_status := g_ret_sts_unexp_error;
965 END interface_nl_to_pa;
966
967 END cse_po_receipt_into_project;