[Home] [Help]
PACKAGE BODY: APPS.CSI_PROCESS_TXN_GRP
Source
1 PACKAGE BODY csi_process_txn_grp as
2 /* $Header: csigptxb.pls 120.6.12020000.5 2013/03/22 12:40:08 mvaradam ship $ */
3
4
5 /* local debugging procedure */
6 PROCEDURE debug(
7 p_message IN varchar2)
8 IS
9 BEGIN
10 csi_t_gen_utility_pvt.add(p_message);
11 END debug;
12
13 /*-------------------------------------------------------------------*/
14 /* Group API used to process one source transaction line */
15 /* This api reads a set op pl/sql tables and converts them in to */
16 /* instances .If an instance reference is found then it updates the */
17 /* instance with the new location and party attributes */
18 /*-------------------------------------------------------------------*/
19
20 PROCEDURE process_transaction(
21 p_api_version IN NUMBER,
22 p_commit IN VARCHAR2 := fnd_api.g_false,
23 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
24 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
25 p_validate_only_flag IN VARCHAR2,
26 p_in_out_flag IN VARCHAR2, -- valid values are 'IN','OUT'
27 p_dest_location_rec IN OUT NOCOPY dest_location_rec,
28 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
29 p_instances_tbl IN OUT NOCOPY txn_instances_tbl,
30 p_i_parties_tbl IN OUT NOCOPY txn_i_parties_tbl,
31 p_ip_accounts_tbl IN OUT NOCOPY txn_ip_accounts_tbl,
32 p_org_units_tbl IN OUT NOCOPY txn_org_units_tbl,
33 p_ext_attrib_vlaues_tbl IN OUT NOCOPY txn_ext_attrib_values_tbl,
34 p_pricing_attribs_tbl IN OUT NOCOPY txn_pricing_attribs_tbl,
35 p_instance_asset_tbl IN OUT NOCOPY txn_instance_asset_tbl,
36 p_ii_relationships_tbl IN OUT NOCOPY txn_ii_relationships_tbl,
37 px_txn_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_inv_txn_typeid_from_cs IN NUMBER DEFAULT -1) -- Bug 10415554
42 IS
43
44 g_user_id NUMBER := fnd_global.user_id;
45 g_login_id NUMBER := fnd_global.login_id;
46 g_sysdate DATE := sysdate;
47
48 l_api_name CONSTANT VARCHAR2(30) := 'process_transaction';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
51 l_msg_count NUMBER;
52 l_msg_data VARCHAR2(2000);
53 l_debug_level NUMBER;
54
55 l_transaction_rec csi_datastructures_pub.transaction_rec;
56 l_sub_type_rec csi_txn_sub_types%rowtype;
57 l_instances_tbl csi_process_txn_grp.txn_instances_tbl;
58 l_parties_tbl csi_process_txn_grp.txn_i_parties_tbl;
59 l_item_attr_rec csi_process_txn_pvt.item_attr_rec;
60 l_instance_id csi_item_instances.instance_id%TYPE;
61
62 BEGIN
63
64 -- Initialize API return status to success
65 x_return_status := fnd_api.g_ret_sts_success;
66
67 -- Standard Start of API savepoint
68 SAVEPOINT process_transaction;
69
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 IF FND_API.To_Boolean( p_init_msg_list ) THEN
72 FND_MSG_PUB.Initialize;
73 END IF;
74
75 -- Standard call to check for call compatibility.
76 IF NOT
77
78 FND_API.Compatible_API_Call (
79 p_current_version_number => l_api_version,
80 p_caller_version_number => p_api_version,
81 p_api_name => l_api_name,
82 p_pkg_name => g_pkg_name) THEN
83
84 RAISE fnd_api.g_exc_unexpected_error;
85
86 END IF;
87
88 csi_t_gen_utility_pvt.dump_api_info(
89 p_pkg_name => g_pkg_name,
90 p_api_name => l_api_name);
91
92 -- debug messages
93 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
94
95 debug('Transaction Flow: '||p_in_out_flag);
96
97 -- MAIN API code starts here
98
99 /* check if IB is active */
100 csi_utility_grp.check_ib_active;
101
102 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
103 csi_gen_utility_pvt.populate_install_param_rec;
104 END IF;
105
106 l_instances_tbl := p_instances_tbl;
107 l_parties_tbl := p_i_parties_tbl;
108
109 csi_process_txn_pvt.get_sub_type_rec(
110 p_txn_type_id => p_txn_rec.transaction_type_id,
111 p_sub_type_id => p_txn_rec.txn_sub_type_id,
112 x_sub_type_rec => l_sub_type_rec,
113 x_return_status => l_return_status);
114
115 IF l_return_status <> fnd_api.g_ret_sts_success THEN
116 RAISE fnd_api.g_exc_error;
117 END IF;
118
119 /* ---------------------------------------------------------------- */
120 /* This routine validates the sub types and the passed instance */
121 /* information with the sub type definition */
122 /* ---------------------------------------------------------------- */
123
124 csi_process_txn_pvt.sub_type_validations(
125 p_sub_type_rec => l_sub_type_rec,
126 p_txn_instances_tbl => l_instances_tbl,
127 p_txn_i_parties_tbl => l_parties_tbl,
128 x_return_status => l_return_status);
129
130 IF l_return_status <> fnd_api.g_ret_sts_success THEN
131 RAISE fnd_api.g_exc_error;
132 END IF;
133
134 csi_process_txn_pvt.validate_dest_location_rec(
135 p_in_out_flag => p_in_out_flag,
136 p_dest_location_rec => p_dest_location_rec,
137 x_return_status => l_return_status);
138
139 IF l_return_status <> fnd_api.g_ret_sts_success THEN
140 RAISE fnd_api.g_exc_error;
141 END IF;
142
143 debug('Parse I');
144
145 IF l_instances_tbl.COUNT > 0 THEN
146
147 /* Create transaction and use the same transaction rec throughout the program */
148
149 l_transaction_rec := p_txn_rec;
150
151 IF nvl(l_transaction_rec.transaction_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
152
153 csi_t_gen_utility_pvt.dump_api_info(
154 p_pkg_name => 'csi_transactions_pvt',
155 p_api_name => 'create_transaction');
156
157 csi_transactions_pvt.create_transaction (
158 p_api_version => 1.0,
159 p_commit => fnd_api.g_false,
160 p_init_msg_list => fnd_api.g_true,
161 p_validation_level => fnd_api.g_valid_level_full,
162 p_success_if_exists_flag => 'Y',
163 p_transaction_rec => l_transaction_rec,
164 x_return_status => l_return_status,
165 x_msg_count => l_msg_count,
166 x_msg_data => l_msg_data );
167
168 IF l_return_status <> fnd_api.g_ret_sts_success THEN
169 RAISE fnd_api.g_exc_error;
170 END IF;
171
172 p_txn_rec := l_transaction_rec;
173
174 END IF;
175
176 debug('CSI Transaction ID: '||l_transaction_rec.transaction_id);
177
178 FOR l_i_ind IN l_instances_tbl.FIRST .. l_instances_tbl.LAST
179 LOOP
180
181 IF nvl(l_instances_tbl(l_i_ind).instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
182 THEN
183
184 debug('Source instance reference not specified.');
185
186 /* ------------------------------------------------------------- */
187 /* This routine gets all the item attributes required for */
188 /* validating the instance and to determine the query parameters */
189 /* ------------------------------------------------------------- */
190
191 -- Bug 12574008
192 -- If p_inv_txn_typeid_from_cs is passed from Field Service
193 -- then assign
194 -- p_txn_rec.ATTRIBUTE14 = FIELD_SERVICE_TXN
195 -- p_txn_rec.ATTRIBUTE15 = p_inv_txn_typeid_from_cs
196 IF p_inv_txn_typeid_from_cs <> -1 THEN
197 l_transaction_rec.ATTRIBUTE14 := 'FIELD_SERVICE_TXN';
198 l_transaction_rec.ATTRIBUTE15 := to_char(p_inv_txn_typeid_from_cs);
199 END IF;
200
201 -- Bug 10415554
202 csi_process_txn_pvt.get_item_attributes(
203 p_transaction_id => p_txn_rec.inv_material_transaction_id,-- Added for bug#14835893
204 p_in_out_flag => p_in_out_flag,
205 p_sub_type_rec => l_sub_type_rec,
206 p_inventory_item_id => l_instances_tbl(l_i_ind).inventory_item_id,
207 p_organization_id => l_instances_tbl(l_i_ind).vld_organization_id,
208 p_mtl_transaction_id => p_txn_rec.inv_material_transaction_id,
209 x_item_attr_rec => l_item_attr_rec,
210 x_return_status => l_return_status,
211 p_inv_txn_typeid_from_cs => p_inv_txn_typeid_from_cs);
212
213 IF l_return_status <> fnd_api.g_ret_sts_success THEN
214 RAISE fnd_api.g_exc_error;
215 END IF;
216
217 IF l_item_attr_rec.stockable_flag = 'Y' THEN
218
219 debug('Stockable item, So trying to figure OUT NOCOPY the source instance.');
220
221 /* ------------------------------------------------------------- */
222 /* This routine validates the mandatory components to query the */
223 /* instance, builds the query record and fetches the instance */
224 /* This routine returns an error when multiple instances matches */
225 /* the given criteria */
226 /* ------------------------------------------------------------- */
227
228 /* exclude misc receipt (source location attributes are null) */
229 /* and WIP assy completion. These transactions will not have */
230 /* source instances. */
231
232 IF nvl(l_instances_tbl(l_i_ind).location_type_code,fnd_api.g_miss_char) <> fnd_api.g_miss_char
233 OR
234 nvl(l_instances_tbl(l_i_ind).location_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
235 OR
236 nvl(l_instances_tbl(l_i_ind).inv_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
237 OR
238 nvl(l_instances_tbl(l_i_ind).inv_subinventory_name, fnd_api.g_miss_char)<> fnd_api.g_miss_char
239 THEN
240
241 csi_process_txn_pvt.get_src_instance_id(
242 p_in_out_flag => p_in_out_flag,
243 p_sub_type_rec => l_sub_type_rec,
244 p_instance_rec => l_instances_tbl(l_i_ind),
245 p_dest_location_rec => p_dest_location_rec,
246 p_item_attr_rec => l_item_attr_rec,
247 p_transaction_rec => l_transaction_rec,
248 x_instance_id => l_instance_id,
249 x_return_status => l_return_status);
250
251 IF l_return_status <> fnd_api.g_ret_sts_success THEN
252 RAISE fnd_api.g_exc_error;
253 END IF;
254
255 l_instances_tbl(l_i_ind).new_instance_id := l_instance_id;
256
257 ELSE
258 l_instances_tbl(l_i_ind).new_instance_id := fnd_api.g_miss_num;
259 END IF;
260
261 IF nvl(l_instances_tbl(l_i_ind).new_instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
262 debug('Source instance identified. Instance ID :'||l_instances_tbl(l_i_ind).new_instance_id);
263 ELSE
264 debug('Process Transaction could not identity a source instance.');
265 END IF;
266
267 END IF; -- stockable flag = 'Y'
268
269 ELSE
270
271 debug('Source instance reference specified.');
272
273 l_instances_tbl(l_i_ind).new_instance_id := l_instances_tbl(l_i_ind).instance_id;
274
275 END IF; -- instance_id = fnd_api.g_miss_num
276
277 END LOOP; -- txn_instance_tbl loop
278
279 END IF; -- IF txn_instance_tbl.COUNT > 0
280
281
282 /* --------------------------------------------------------------- */
283 /* SECOND Parse -- call process IB when validate_only flag = FALSE */
284 /* This section actually does the interface with IB */
285 /* --------------------------------------------------------------- */
286
287 debug('Parse II');
288
289 IF nvl(p_validate_only_flag, fnd_api.g_false) = fnd_api.g_false THEN
290
291 IF l_instances_tbl.COUNT > 0 THEN
292
293 FOR l_instance_index IN l_instances_tbl.FIRST .. l_instances_tbl.LAST
294 LOOP
295
296 /* process only the source transaction lines */
297
298 IF l_instances_tbl(l_instance_index).ib_txn_segment_flag = 'S' THEN
299
300 /* ------------------------------------------------------------- */
301 /* This routine gets all the item attributes required for */
302 /* validating the instance and to determine the query parameters */
303 /* ------------------------------------------------------------- */
304
305 -- Bug 12574008
306 -- If p_inv_txn_typeid_from_cs is passed from Field Service
307 -- then assign
308 -- p_txn_rec.ATTRIBUTE14 = FIELD_SERVICE_TXN
309 -- p_txn_rec.ATTRIBUTE15 = p_inv_txn_typeid_from_cs
310 IF p_inv_txn_typeid_from_cs <> -1 THEN
311 l_transaction_rec.ATTRIBUTE14 := 'FIELD_SERVICE_TXN';
312 l_transaction_rec.ATTRIBUTE15 := to_char(p_inv_txn_typeid_from_cs);
313 END IF;
314
315 -- Bug 10415554
316 csi_process_txn_pvt.get_item_attributes(
317 p_transaction_id => p_txn_rec.inv_material_transaction_id,-- Added for bug#14835893
318 p_in_out_flag => p_in_out_flag,
319 p_sub_type_rec => l_sub_type_rec,
320 p_inventory_item_id => l_instances_tbl(l_instance_index).inventory_item_id,
321 p_organization_id => l_instances_tbl(l_instance_index).vld_organization_id,
322 p_mtl_transaction_id => p_txn_rec.inv_material_transaction_id,
323 x_item_attr_rec => l_item_attr_rec,
324 x_return_status => l_return_status,
325 p_inv_txn_typeid_from_cs => p_inv_txn_typeid_from_cs);
326
327 IF l_return_status <> fnd_api.g_ret_sts_success THEN
328 RAISE fnd_api.g_exc_error;
329 END IF;
330
331 /* -------------------------------------------------------------- */
332 /* call the process_IB routine that either creates a new instance */
333 /* or updates the instances for the location and party attributes */
334 /* based on the value in the instance_id/new_instance_id column */
335 /* if an inst ID is found at this level it is treated for update */
336 /* otherwise create an instance */
337 /* -------------------------------------------------------------- */
338
339
340 /* Added the validation for considering the serialised at sales order
341 issue items with a not null serial number for deployment transactions
342 as serialised items */
343
344 IF p_in_out_flag = 'INT' and l_instances_tbl(l_instance_index).serial_number IS NOT NULL THEN
345
346 IF l_transaction_rec.transaction_type_id IN (106,107,108,109,110,111) THEN
347
348
349
350 debug('Processing of serial items for deployment transaction');
351 l_item_attr_rec.src_serial_control_flag := 'Y';
352 l_item_attr_rec.dst_serial_control_flag := 'Y';
353
354 END IF;
355
356 END IF;
357
358 --Added for bug 14368955 - Start
359 IF p_ii_relationships_tbl.count > 0 AND l_transaction_rec.transaction_type_id =55 THEN
360 FOR i in p_ii_relationships_tbl.first..p_ii_relationships_tbl.last loop
361 IF p_ii_relationships_tbl(i).relationship_type_code in
362 ('REPLACED-BY', 'REPLACEMENT-FOR', 'UPGRADED-FROM') AND
363 ( p_ii_relationships_tbl(i).object_index = l_instance_index OR
364 p_ii_relationships_tbl(i).subject_index = l_instance_index ) THEN
365
366 l_instances_tbl(l_instance_index).call_contracts := FND_API.G_FALSE;
367
368 FOR ipa_rec in p_ip_accounts_tbl.first..p_ip_accounts_tbl.last loop
369 p_ip_accounts_tbl(ipa_rec).call_contracts := FND_API.G_FALSE;
370 end loop;
371
372 exit;
373 END IF;
374 END LOOP;
375 END IF;
376 --Added for bug 14368955 - End
377
378 csi_process_txn_pvt.process_ib(
379 p_in_out_flag => p_in_out_flag,
380 p_sub_type_rec => l_sub_type_rec,
381 p_item_attr_rec => l_item_attr_rec,
382 p_instance_index => l_instance_index,
383 p_instance_rec => l_instances_tbl(l_instance_index),
384 p_dest_location_rec => p_dest_location_rec,
385 p_i_parties_tbl => p_i_parties_tbl,
386 p_ip_accounts_tbl => p_ip_accounts_tbl,
387 p_ext_attrib_vals_tbl => p_ext_attrib_vlaues_tbl,
388 p_pricing_attribs_tbl => p_pricing_attribs_tbl,
389 p_org_units_tbl => p_org_units_tbl,
390 p_instance_asset_tbl => p_instance_asset_tbl,
391 p_transaction_rec => l_transaction_rec,
392 px_txn_error_rec => px_txn_error_rec,
393 x_return_status => l_return_status);
394
395 IF l_return_status <> fnd_api.g_ret_sts_success THEN
396 RAISE fnd_api.g_exc_error;
397 END IF;
398
399 END IF;
400
401 END LOOP; -- txn_instance_tbl loop
402
403 END IF; -- txn_instance_tbl COUNT > 0
404
405 p_instances_tbl := l_instances_tbl;
406
407 -- call the build relations routine
408
409 IF p_ii_relationships_tbl.COUNT > 0 THEN
410
411 csi_process_txn_pvt.process_relation(
412 p_instances_tbl => p_instances_tbl,
413 p_ii_relationships_tbl => p_ii_relationships_tbl,
414 p_transaction_rec => l_transaction_rec,
415 x_return_status => l_return_status);
416
417 IF l_return_status <> fnd_api.g_ret_sts_success THEN
418 RAISE fnd_api.g_exc_error;
419 END IF;
420
421 END IF;
422
423 END IF; -- validate_only_flag = FALSE
424
425 -- END MAIN API Code
426
427 -- Standard check of p_commit.
428 IF fnd_api.to_boolean( p_commit ) then
429 commit work;
430 END IF;
431
432 debug('Process transaction successful.');
433
434 csi_t_gen_utility_pvt.set_debug_off;
435
436 -- Standard call to get message count and if count is get message info.
437 fnd_msg_pub.count_and_get(
438 p_count => x_msg_count,
439 p_data => x_msg_data);
440
441 EXCEPTION
442 WHEN fnd_api.g_exc_error THEN
443
444 rollback to process_transaction;
445 x_return_status := fnd_api.g_ret_sts_error;
446
447 fnd_msg_pub.count_and_get (
448 p_count => x_msg_count,
449 p_data => x_msg_data);
450
451 x_msg_data := csi_t_gen_utility_pvt.dump_error_stack;
452 debug('Error(E) :'||x_msg_data);
453
454 WHEN fnd_api.g_exc_unexpected_error THEN
455
456 rollback to process_transaction;
457 x_return_status := fnd_api.g_ret_sts_unexp_error;
458
459 fnd_msg_pub.count_and_get(
460 p_count => x_msg_count,
461 p_data => x_msg_data);
462
463 x_msg_data := csi_t_gen_utility_pvt.dump_error_stack;
464 debug('Error(U) :'||x_msg_data);
465
466 WHEN others THEN
467
468 rollback to process_transaction;
469
470 x_return_status := fnd_api.g_ret_sts_unexp_error ;
471
472 fnd_msg_pub.add_exc_msg(
473 p_pkg_name => g_pkg_name,
474 p_procedure_name => l_api_name);
475
476 fnd_msg_pub.count_and_get(
477 p_count => x_msg_count,
478 p_data => x_msg_data);
479
480 x_msg_data := csi_t_gen_utility_pvt.dump_error_stack;
481 debug('Error(E) :'||x_msg_data);
482
483 END process_transaction;
484
485 PROCEDURE process_transaction (
486 p_api_version IN NUMBER,
487 p_commit IN VARCHAR2 := fnd_api.g_false,
488 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
489 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
490 p_validate_only_flag IN VARCHAR2 := fnd_api.g_false,
491 p_in_out_flag IN VARCHAR2, -- valid values are 'IN', 'OUT'
492 p_dest_location_rec IN OUT NOCOPY dest_location_rec,
493 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
494 p_instances_tbl IN OUT NOCOPY txn_instances_tbl,
495 p_i_parties_tbl IN OUT NOCOPY txn_i_parties_tbl,
496 p_ip_accounts_tbl IN OUT NOCOPY txn_ip_accounts_tbl,
497 p_org_units_tbl IN OUT NOCOPY txn_org_units_tbl,
498 p_ext_attrib_vlaues_tbl IN OUT NOCOPY txn_ext_attrib_values_tbl,
499 p_pricing_attribs_tbl IN OUT NOCOPY txn_pricing_attribs_tbl,
500 p_instance_asset_tbl IN OUT NOCOPY txn_instance_asset_tbl,
501 p_ii_relationships_tbl IN OUT NOCOPY txn_ii_relationships_tbl,
502 x_return_status OUT NOCOPY VARCHAR2,
503 x_msg_count OUT NOCOPY NUMBER,
504 x_msg_data OUT NOCOPY VARCHAR2 )
505 IS
506 l_error_rec csi_datastructures_pub.transaction_error_rec;
507 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
508 l_msg_count number;
509 l_msg_data varchar2(2000);
510 BEGIN
511
512 x_return_status := fnd_api.g_ret_sts_success;
513
514 csi_process_txn_grp.process_transaction(
515 p_api_version => p_api_version,
516 p_commit => p_commit,
517 p_init_msg_list => p_init_msg_list,
518 p_validation_level => p_validation_level,
519 p_validate_only_flag => p_validate_only_flag,
520 p_in_out_flag => p_in_out_flag,
521 p_dest_location_rec => p_dest_location_rec,
522 p_txn_rec => p_txn_rec,
523 p_instances_tbl => p_instances_tbl,
524 p_i_parties_tbl => p_i_parties_tbl,
525 p_ip_accounts_tbl => p_ip_accounts_tbl,
526 p_org_units_tbl => p_org_units_tbl,
527 p_ext_attrib_vlaues_tbl => p_ext_attrib_vlaues_tbl,
528 p_pricing_attribs_tbl => p_pricing_attribs_tbl,
529 p_instance_asset_tbl => p_instance_asset_tbl,
530 p_ii_relationships_tbl => p_ii_relationships_tbl,
531 px_txn_error_rec => l_error_rec,
532 x_return_status => l_return_status,
533 x_msg_count => x_msg_count,
534 x_msg_data => x_msg_data );
535
536 IF l_return_status <> fnd_api.g_ret_sts_success THEN
537 RAISE fnd_api.g_exc_error;
538 END IF;
539
540 EXCEPTION
541 WHEN fnd_api.g_exc_error THEN
542 x_return_status := fnd_api.g_ret_sts_error;
543
544 WHEN others THEN
545 x_return_status := fnd_api.g_ret_sts_error;
546
547 fnd_msg_pub.add_exc_msg(
548 p_pkg_name => 'csi_process_txn_grp',
549 p_procedure_name => 'process_transaction');
550
551 x_msg_data := csi_t_gen_utility_pvt.dump_error_stack;
552 debug('Error(E) :'||x_msg_data);
553
554 END process_transaction;
555
556 END csi_process_txn_grp;