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