[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_LINE_DTLS_PVT
Source
1 PACKAGE BODY csi_t_txn_line_dtls_pvt AS
2 /* $Header: csivttdb.pls 120.10 2008/06/03 21:48:28 devijay ship $*/
3
4 g_user_id number := fnd_global.user_id;
5 g_login_id number := fnd_global.login_id;
6
7 /* local routine to wrap the gen utility debug stuff */
8
9 PROCEDURE debug(
10 p_message IN varchar2)
11 IS
12 BEGIN
13 csi_t_gen_utility_pvt.add(p_message);
14 END debug;
15
16 PROCEDURE api_log(
17 p_api_name IN varchar2)
18 IS
19 BEGIN
20 csi_t_gen_utility_pvt.dump_api_info(
21 p_pkg_name => 'csi_t_txn_line_dtls_pvt',
22 p_api_name => p_api_name);
23 END api_log;
24
25 PROCEDURE create_txn_line_dtls(
26 p_api_version IN number,
27 p_commit IN varchar2 := fnd_api.g_false,
28 p_init_msg_list IN varchar2 := fnd_api.g_false,
29 p_validation_level IN number := fnd_api.g_valid_level_full,
30 p_txn_line_dtl_index IN number,
31 p_txn_line_dtl_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
32 px_txn_party_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
33 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
34 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
35 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
36 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
37 x_return_status OUT NOCOPY varchar2,
38 x_msg_count OUT NOCOPY number,
39 x_msg_data OUT NOCOPY varchar2)
40
41 IS
42
43 l_api_name CONSTANT varchar2(30) := 'create_txn_line_dtls';
44 l_api_version CONSTANT number := 1.0;
45 l_debug_level number;
46 l_return_status varchar2(1);
47 l_msg_count number;
48 l_msg_data varchar2(2000);
49
50 l_txn_line_detail_id number;
51 l_uom_code mtl_units_of_measure.uom_code%TYPE;
52 l_quantity number;
53 l_processing_status varchar2(30);
54 l_preserve_detail_flag varchar2(1);
55 l_valid boolean := TRUE;
56 l_creation_flag varchar2(1) := 'N';
57
58 l_txn_party_rec csi_t_datastructures_grp.txn_party_detail_rec;
59 l_txn_ii_rltns_rec csi_t_datastructures_grp.txn_ii_rltns_rec;
60 l_txn_oa_rec csi_t_datastructures_grp.txn_org_assgn_rec;
61 l_txn_ea_rec csi_t_datastructures_grp.txn_ext_attrib_vals_rec;
62
63 --contact party id variables
64 l_tmp_party_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
65 l_contact_party_id number;
66 l_cascade_owner_flag varchar2(1);-- bug 2972082
67 l_contact_party_index varchar2(1) := 'N';
68
69 l_src_transaction_type_id NUMBER; -- ER 6936037
70
71 BEGIN
72
73 -- Standard Start of API savepoint
74 SAVEPOINT create_txn_line_dtls;
75
76 -- Initialize message list if p_init_msg_list is set to TRUE.
77 IF fnd_api.to_boolean( p_init_msg_list ) THEN
78 fnd_msg_pub.initialize;
79 END IF;
80
81 -- Initialize API return status to success
82 x_return_status := fnd_api.g_ret_sts_success;
83
84 -- Standard call to check for call compatibility.
85 IF NOT
86
87 fnd_api.compatible_API_call (
88 p_current_version_number => l_api_version,
89 p_caller_version_number => p_api_version,
90 p_api_name => l_api_name,
91 p_pkg_name => g_pkg_name) THEN
92
93 RAISE fnd_api.g_exc_unexpected_error;
94
95 END IF;
96
97 -- debug messages
98 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
99
100 csi_t_gen_utility_pvt.dump_api_info(
101 p_pkg_name => g_pkg_name,
102 p_api_name => l_api_name);
103
104 debug(p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
105
106 IF l_debug_level > 1 then
107
108 csi_t_gen_utility_pvt.dump_line_detail_rec(
109 p_line_detail_rec => p_txn_line_dtl_rec);
110
111 END IF;
112
113 -- Main API code
114
115 -- check for required parameters
116
117 csi_t_vldn_routines_pvt.check_reqd_param(
118 p_value => p_txn_line_dtl_rec.sub_type_id,
119 p_param_name => 'p_txn_line_dtl_rec.sub_type_id',
120 p_api_name => l_api_name);
121
122 /* Commenting/Removing this restriction and API will default value for this flag based on the value for instance id column - shegde*/
123 /*
124 IF p_txn_line_dtl_rec.instance_exists_flag = 'Y' THEN
125 csi_t_vldn_routines_pvt.check_reqd_param(
126 p_value => p_txn_line_dtl_rec.instance_exists_flag,
127 p_param_name => 'p_txn_line_dtl_rec.instance_exists_flag',
128 p_api_name => l_api_name);
129 END IF;
130 */
131
132 csi_t_vldn_routines_pvt.check_reqd_param(
133 p_value => p_txn_line_dtl_rec.inventory_item_id,
134 p_param_name => 'p_txn_line_dtl_rec.inventory_item_id',
135 p_api_name => l_api_name);
136
137 csi_t_vldn_routines_pvt.check_reqd_param(
138 p_value => p_txn_line_dtl_rec.inv_organization_id,
139 p_param_name => 'p_txn_line_dtl_rec.inv_organization_id',
140 p_api_name => l_api_name);
141
142 csi_t_vldn_routines_pvt.check_reqd_param(
143 p_value => p_txn_line_dtl_rec.quantity,
144 p_param_name => 'p_txn_line_dtl_rec.quantity',
145 p_api_name => l_api_name);
146
147 csi_t_vldn_routines_pvt.check_reqd_param(
148 p_value => p_txn_line_dtl_rec.unit_of_measure,
149 p_param_name => 'p_txn_line_dtl_rec.unit_of_measure',
150 p_api_name => l_api_name);
151
152 csi_t_vldn_routines_pvt.check_reqd_param(
153 p_value => p_txn_line_dtl_rec.source_transaction_flag,
154 p_param_name => 'p_txn_line_dtl_rec.source_transaction_flag',
155 p_api_name => l_api_name);
156
157 IF NVL(p_txn_line_dtl_rec.location_id, fnd_api.g_miss_num) <>
158 fnd_api.g_miss_num
159 THEN
160
161 csi_t_vldn_routines_pvt.check_reqd_param(
162 p_value => p_txn_line_dtl_rec.location_type_code,
163 p_param_name => 'p_txn_line_dtl_rec.location_type_code',
164 p_api_name => l_api_name);
165
166 END IF;
167 /* no longer required. all serialized item instances have the serials numbers in INV now. 3593990
168 IF NVL(p_txn_line_dtl_rec.serial_number, fnd_api.g_miss_char) <>
169 fnd_api.g_miss_char
170 THEN
171
172 csi_t_vldn_routines_pvt.check_reqd_param(
173 p_value => p_txn_line_dtl_rec.mfg_serial_number_flag,
174 p_param_name => 'p_txn_line_dtl_rec.mfg_serial_number_flag',
175 p_api_name => l_api_name);
176
177 END IF;
178 */
179
180 debug('Dtls: End of required parameters check .');
181
182 -- validate txn sub_type_id
183 IF nvl(p_txn_line_dtl_rec.sub_type_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
184
185 --debug('Validate sub type id.');
186
187 csi_t_vldn_routines_pvt.validate_sub_type_id(
188 p_transaction_line_id => p_txn_line_dtl_rec.transaction_line_id,
189 p_sub_type_id => p_txn_line_dtl_rec.sub_type_id,
190 x_return_status => l_return_status);
191
192 IF l_return_status <> fnd_api.g_ret_sts_success THEN
193 debug('Validate sub type id failed.');
194
195 fnd_message.set_name('CSI','CSI_TXN_SUB_TYPE_ID_INVALID');
196 fnd_message.set_token('SUB_TYPE_ID',p_txn_line_dtl_rec.sub_type_id);
197 fnd_msg_pub.add;
198 raise fnd_api.g_exc_error;
199
200 END IF;
201
202 END IF;
203
204 -- validate instance type
205 IF NVL(p_txn_line_dtl_rec.instance_type_code, fnd_api.g_miss_char) <>
206 fnd_api.g_miss_char
207 THEN
208
209 --debug('Validate instance type code.');
210
211 IF NOT
212 csi_item_instance_vld_pvt.is_valid_instance_type(
213 p_instance_type_code => p_txn_line_dtl_rec.instance_type_code)
214 THEN
215 debug('Validate instance type code failed.');
216 RAISE fnd_api.g_exc_error;
217 END IF;
218
219 END IF;
220
221 -- validate item instance
222 -- IF p_txn_line_dtl_rec.instance_exists_flag = 'Y' THEN
223 IF nvl(p_txn_line_dtl_rec.instance_id, fnd_api.g_miss_num) <>
224 fnd_api.g_miss_num THEN
225
226 --debug('Validate instance id .');
227 csi_t_vldn_routines_pvt.validate_instance_id(
228 p_instance_id => p_txn_line_dtl_rec.instance_id,
229 x_return_status => l_return_status);
230
231 IF l_return_status <> fnd_api.g_ret_sts_success THEN
232
233 debug('Validate instance id failed.');
234
235 FND_MESSAGE.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
236 FND_MESSAGE.set_token('INSTANCE_ID',p_txn_line_dtl_rec.instance_id);
237 fnd_msg_pub.add;
238 RAISE fnd_api.g_exc_error;
239
240 END IF;
241
242 END IF;
243
244 -- validate system id
245 IF NVL(p_txn_line_dtl_rec.csi_system_id, fnd_api.g_miss_num) <>
246 fnd_api.g_miss_num
247 THEN
248
249 --debug('Validate csi system id .');
250 IF NOT
251 csi_item_instance_vld_pvt.is_valid_system_id(
252 p_system_id => p_txn_line_dtl_rec.csi_system_id) THEN
253
254 debug('Validate csi system id failed.');
255 RAISE fnd_api.g_exc_error;
256 END IF;
257
258 END IF;
259
260 -- Start of Addition for ER 6936037
261 BEGIN
262 SELECT source_transaction_type_id
263 into l_src_transaction_type_id
264 from CSI_T_TRANSACTION_LINES
265 WHERE transaction_line_id = p_txn_line_dtl_rec.transaction_line_id;
266 EXCEPTION
267 WHEN OTHERS THEN
268 debug('No Transaction Type Id found for transaction_line_id - ' || p_txn_line_dtl_rec.transaction_line_id);
269 null;
270 END;
271 -- End of Addition for ER 6936037
272
273 -- If the transaction is a mass update, IB trackable checking is skipped
274 -- as per ER 6936037
275 IF NVL(l_src_transaction_type_id, fnd_api.g_miss_num) <> 3 THEN
276
277 -- is item trackable (inventory_item_id)
278 IF NVL(p_txn_line_dtl_rec.inventory_item_id, fnd_api.g_miss_num) <>
279 fnd_api.g_miss_num THEN
280
281 --debug('Validate item id for trackabality .');
282 IF NOT
283 csi_item_instance_vld_pvt.is_trackable(
284 p_inv_item_id => p_txn_line_dtl_rec.inventory_item_id,
285 p_org_id => p_txn_line_dtl_rec.inv_organization_id) THEN
286
287 debug('Validate item id for trackabality failed.');
288 RAISE fnd_api.g_exc_error;
289 END IF;
290
291 END IF;
292
293 END IF; -- Checking for Mass Addition transaction
294
295 -- item condition codes against the mtl_material_statuses
296 IF NVL(p_txn_line_dtl_rec.item_condition_id,fnd_api.g_miss_num) <>
297 fnd_api.g_miss_num
298 THEN
299
300 --debug('Validate item condition id .');
301
302 csi_item_instance_vld_pvt.is_valid_condition(
303 p_instance_condition_id => p_txn_line_dtl_rec.item_condition_id,
304 p_creation_complete_flag => l_creation_flag,
305 l_return_value => l_valid);
306
307 IF not(l_valid) THEN
308 debug('Validate item condition id failed.');
309 RAISE fnd_api.g_exc_error;
310 END IF;
311
312 END IF;
313
314 -- item revision
315 IF nvl(p_txn_line_dtl_rec.inventory_revision, fnd_api.g_miss_char) <>
316 fnd_api.g_miss_char
317 THEN
318
319 --debug('Validate item revision .');
320
321 csi_item_instance_vld_pvt.validate_revision(
322 p_inv_item_id => p_txn_line_dtl_rec.inventory_item_id,
323 p_inv_org_id => p_txn_line_dtl_rec.inv_organization_id,
324 p_revision => p_txn_line_dtl_rec.inventory_revision,
325 p_creation_complete_flag => l_creation_flag,
326 l_return_value => l_valid);
327
328 IF NOT(l_valid) THEN
329 debug('Validate item revision failed.');
330 RAISE fnd_api.g_exc_error;
331 END IF;
332
333 END IF;
334
335 l_uom_code := p_txn_line_dtl_rec.unit_of_measure;
336 l_quantity := p_txn_line_dtl_rec.quantity;
337
338 -- validate quantity
339 IF nvl(l_quantity,0) <= 0 THEN
340 fnd_message.set_name('CSI','CSI_TXN_QTY_INVALID');
341 fnd_message.set_token('ITEM_ID',p_txn_line_dtl_rec.inventory_item_id);
342 fnd_message.set_token('QTY',l_quantity);
343 fnd_msg_pub.add;
344 raise fnd_api.g_exc_error;
345 END IF;
346
347 --debug('Validate item uom .');
348 -- validate uom
349 csi_item_instance_vld_pvt.is_valid_uom(
350 p_inv_org_id => p_txn_line_dtl_rec.inv_organization_id,
351 p_inv_item_id => p_txn_line_dtl_rec.inventory_item_id,
352 p_uom_code => l_uom_code,
353 p_quantity => l_quantity,
354 p_creation_complete_flag => l_creation_flag,
355 l_return_value => l_valid);
356
357 IF not (l_valid) then
358 debug('Validate item uom failed.');
359 RAISE fnd_api.g_exc_error;
360 END IF;
361
362 -- serial number
363 IF nvl(p_txn_line_dtl_rec.serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
364 --AND
365 --nvl(p_txn_line_dtl_rec.mfg_serial_number_flag, fnd_api.g_miss_char) = 'Y' bug 3593990
366 THEN
367
368 debug('Validating Serial Number.');
369
370 csi_t_vldn_routines_pvt.validate_serial_number(
371 p_inventory_item_id => p_txn_line_dtl_rec.inventory_item_id,
372 p_organization_id => p_txn_line_dtl_rec.inv_organization_id,
373 p_serial_number => p_txn_line_dtl_rec.serial_number,
374 x_return_status => l_return_status);
375
376 IF l_return_status <> fnd_api.g_ret_sts_success THEN
377 debug('csi_t_vldn_routines_pvt.validate_serial_number Failed.');
378 RAISE fnd_api.g_exc_error;
379 END IF;
380
381 /*
382 csi_item_instance_vld_pvt.validate_serial_number(
383 p_inv_org_id => p_txn_line_dtl_rec.inv_organization_id,
384 p_inv_item_id => p_txn_line_dtl_rec.inventory_item_id,
385 p_serial_number => p_txn_line_dtl_rec.serial_number,
386 p_mfg_serial_number_flag => p_txn_line_dtl_rec.mfg_serial_number_flag,
387 p_creation_complete_flag => l_creation_flag,
388 l_return_value => l_valid);
389
390 IF NOT(l_valid) THEN
391 debug('Validate item serial number failed.');
392 RAISE fnd_api.g_exc_error;
393 END IF;
394 */
395
396 END IF;
397
398 -- lot number
399 IF nvl(p_txn_line_dtl_rec.lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
400 THEN
401
402 debug('Validating line details lot number .');
403
404 csi_t_vldn_routines_pvt.validate_lot_number(
405 p_inventory_item_id => p_txn_line_dtl_rec.inventory_item_id,
406 p_organization_id => p_txn_line_dtl_rec.inv_organization_id,
407 p_lot_number => p_txn_line_dtl_rec.lot_number,
408 x_return_status => l_return_status);
409
410 IF l_return_status <> fnd_api.g_ret_sts_success THEN
411 debug('csi_t_vldn_routines_pvt.validate_lot_number Failed.');
412 RAISE fnd_api.g_exc_error;
413 END IF;
414
415 /*
416 csi_item_instance_vld_pvt.validate_lot_number(
417 p_inv_org_id => p_txn_line_dtl_rec.inv_organization_id,
418 p_inv_item_id => p_txn_line_dtl_rec.inventory_item_id,
419 p_lot_number => p_txn_line_dtl_rec.lot_number,
420 p_mfg_serial_number_flag => p_txn_line_dtl_rec.mfg_serial_number_flag,
421 p_creation_complete_flag => l_creation_flag,
422 l_return_value => l_valid);
423
424 IF NOT(l_valid) THEN
425 debug('Validate item lot number failed.');
426 RAISE fnd_api.g_exc_error;
427 END IF;
428 */
429
430 END IF;
431
432 --debug('Validate location type code .');
433
434 -- location_type_code
435 IF NOT
436 csi_item_instance_vld_pvt.is_valid_location_source(
437 p_loc_source_table => p_txn_line_dtl_rec.location_type_code)
438 THEN
439 debug('Validate location type code failed.');
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 IF NVL(p_txn_line_dtl_rec.location_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
444 THEN
445
446 -- debug('Validate location id .');
447 l_valid := csi_item_instance_vld_pvt.is_valid_location_id(
448 p_location_source_table => p_txn_line_dtl_rec.location_type_code,
449 p_location_id => p_txn_line_dtl_rec.location_id);
450
451 IF NOT (l_valid) then
452 debug('Validate location id failed.');
453 RAISE fnd_api.g_exc_error;
454 END IF;
455
456 END IF;
457
458 /* SELECT decode(nvl(p_txn_line_dtl_rec.processing_status,fnd_api.g_miss_char),
459 fnd_api.g_miss_char, 'SUBMIT', p_txn_line_dtl_rec.processing_status)
460 INTO l_processing_status
461 FROM sys.dual;
462
463 SELECT decode(nvl(p_txn_line_dtl_rec.preserve_detail_flag,fnd_api.g_miss_char),
464 fnd_api.g_miss_char, 'Y', p_txn_line_dtl_rec.preserve_detail_flag)
465 INTO l_preserve_detail_flag
466 FROM sys.dual;*/
467
468 -- Start Removed decode from sys.dual for bug 5897107
469 IF nvl(p_txn_line_dtl_rec.processing_status,fnd_api.g_miss_char) = fnd_api.g_miss_char then
470 l_processing_status := 'SUBMIT';
471 ELSE
472 l_processing_status := p_txn_line_dtl_rec.processing_status;
473 END IF;
474
475 IF nvl(p_txn_line_dtl_rec.preserve_detail_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char then
476 l_preserve_detail_flag := 'Y';
477 ELSE
478 l_preserve_detail_flag := p_txn_line_dtl_rec.preserve_detail_flag;
479 END IF;
480 -- End Removed decode from sys.dual for bug 5897107
481
482 IF NVL(p_txn_line_dtl_rec.reference_source_line_id, fnd_api.g_miss_num) <>
483 fnd_api.g_miss_num -- RMA fulfillment 11.5.9 ER
484 THEN
485
486 csi_t_vldn_routines_pvt.validate_txn_source_id(
487 p_txn_source_name => 'ORDER_ENTRY',
488 p_txn_source_id => p_txn_line_dtl_rec.reference_source_line_id,
489 x_return_status => l_return_status);
490
491 IF l_return_status <> fnd_api.g_ret_sts_success THEN
492 debug('csi_t_vldn_routines_pvt.validate_txn_source_id.');
493 RAISE fnd_api.g_exc_error;
494 END IF;
495
496 END IF;
497
498 /*
499 --commented for the M-M enhancement since txn_line_detail_id will now be passed as sort of an index identifier
500 in the ii relationships table (passed as a parameter)
501 IF nvl(p_txn_line_dtl_rec.txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
502 l_txn_line_detail_id := p_txn_line_dtl_rec.txn_line_detail_id;
503 END IF;
504 */
505 l_txn_line_detail_id := fnd_api.g_miss_num;
506
507
508 IF nvl(p_txn_line_dtl_rec.cascade_owner_flag, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
509
510 --commented SQL below to make changes for the bug 4028827
511 /*
512 Begin
513 Select nvl(ownership_cascade_at_txn, 'N')
514 Into l_cascade_owner_flag
515 From csi_install_parameters;
516 Exception when others then
517 l_cascade_owner_flag := 'N';
518 End;
519 */
520 l_cascade_owner_flag := NVL(csi_datastructures_pub.g_install_param_rec. ownership_cascade_at_txn,'N');
521 ELSE
522 l_cascade_owner_flag := p_txn_line_dtl_rec.cascade_owner_flag;
523 END IF;
524
525 IF nvl(p_txn_line_dtl_rec.parent_instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
526 csi_t_vldn_routines_pvt.validate_instance_id(
527 p_instance_id => p_txn_line_dtl_rec.parent_instance_id,
528 x_return_status => l_return_status);
529
530 IF l_return_status <> fnd_api.g_ret_sts_success THEN
531
532 debug('Validate instance id failed.');
533
534 FND_MESSAGE.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
535 FND_MESSAGE.set_token('INSTANCE_ID',p_txn_line_dtl_rec.parent_instance_id);
536 fnd_msg_pub.add;
537 RAISE fnd_api.g_exc_error;
538
539 END IF;
540 END IF;
541 -- Added the below IF for the R12 TSO with Equipment MACD to handle removal
542 -- of CZ keys on Disconnect in the Core APIs by stamping instance_id in CSIT
543 -- upfront and later depending on it
544
545 IF (NVL(p_txn_line_dtl_rec.config_inst_hdr_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num
546 AND NVL(p_txn_line_dtl_rec.config_inst_item_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num
547 AND NVL(p_txn_line_dtl_rec.config_inst_baseline_rev_num , fnd_api.g_miss_num) <> fnd_api.g_miss_num)
548 THEN
549 -- with the baseline rev and config keys , get the associated instance id
550 -- if there is a baseline rev num then it is a existing item instance
551 Begin
552 Select serial_number, instance_id, lot_number
553 Into p_txn_line_dtl_rec.serial_number, p_txn_line_dtl_rec.instance_id,
554 p_txn_line_dtl_rec.lot_number
555 From csi_item_instances
556 Where config_inst_hdr_id = p_txn_line_dtl_rec.config_inst_hdr_id
557 AND config_inst_item_id = p_txn_line_dtl_rec.config_inst_item_id;
558 --AND config_inst_rev_num = p_txn_line_dtl_rec.config_inst_baseline_rev_num;
559 Exception when others then
560 null; -- do nothing...
561 End;
562 END IF ;
563
564 -- Added the below IF for bug 2563265
565 IF nvl(p_txn_line_dtl_rec.serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
566 IF nvl(p_txn_line_dtl_rec.instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
567 Begin
568 Select serial_number, instance_id, lot_number
569 Into p_txn_line_dtl_rec.serial_number, p_txn_line_dtl_rec.instance_id,
570 p_txn_line_dtl_rec.lot_number
571 From csi_item_instances
572 Where inventory_item_id = p_txn_line_dtl_rec.inventory_item_id
573 and serial_number = p_txn_line_dtl_rec.serial_number;
574 Exception when others then
575 null;-- do nothing. This is just to sync instance data on the tld rec...
576 End;
577 END IF;
578 ELSIF nvl(p_txn_line_dtl_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
579 Begin
580 Select serial_number, instance_id, lot_number
581 Into p_txn_line_dtl_rec.serial_number, p_txn_line_dtl_rec.instance_id,
582 p_txn_line_dtl_rec.lot_number
583 From csi_item_instances
584 Where instance_id = p_txn_line_dtl_rec.instance_id;
585 Exception when others then
586 null;-- do nothing. This should not arise though.
587 End;
588 END IF;
589
590 -- validate instance status id
591 IF NVL(p_txn_line_dtl_rec.instance_status_id, fnd_api.g_miss_num) <>
592 fnd_api.g_miss_num
593 THEN
594 --debug('Validate instance status id .');
595 csi_item_instance_vld_pvt.is_valid_status(
596 p_instance_status_id => p_txn_line_dtl_rec.instance_status_id,
597 p_creation_complete_flag => l_creation_flag,
598 l_return_value => l_valid);
599
600 IF not(l_valid) THEN
601 debug('Validate instance status id failed.');
602 RAISE fnd_api.g_exc_error;
603 END IF;
604 END IF;
605
606
607 -- call table handler to insert in to table
608 begin
609
610 csi_t_gen_utility_pvt.dump_api_info(
611 p_api_name => 'insert_row',
612 p_pkg_name => 'csi_t_txn_line_details_pkg');
613
614 csi_t_txn_line_details_pkg.insert_row(
615 px_txn_line_detail_id => l_txn_line_detail_id,
616 p_transaction_line_id => p_txn_line_dtl_rec.transaction_line_id,
617 p_sub_type_id => p_txn_line_dtl_rec.sub_type_id,
618 p_instance_exists_flag => p_txn_line_dtl_rec.instance_exists_flag,
619 p_source_transaction_flag => p_txn_line_dtl_rec.source_transaction_flag,
620 p_instance_id => p_txn_line_dtl_rec.instance_id,
621 p_csi_system_id => p_txn_line_dtl_rec.csi_system_id,
622 p_inventory_item_id => p_txn_line_dtl_rec.inventory_item_id,
623 p_inv_organization_id => p_txn_line_dtl_rec.inv_organization_id,
624 p_inventory_revision => p_txn_line_dtl_rec.inventory_revision,
625 p_instance_type_code => p_txn_line_dtl_rec.instance_type_code,
626 p_item_condition_id => p_txn_line_dtl_rec.item_condition_id,
627 p_quantity => p_txn_line_dtl_rec.quantity,
628 p_unit_of_measure => p_txn_line_dtl_rec.unit_of_measure,
629 p_qty_remaining => p_txn_line_dtl_rec.qty_remaining,
630 p_serial_number => p_txn_line_dtl_rec.serial_number,
631 p_lot_number => p_txn_line_dtl_rec.lot_number,
632 p_mfg_serial_number_flag => p_txn_line_dtl_rec.mfg_serial_number_flag,
633 p_location_type_code => p_txn_line_dtl_rec.location_type_code,
634 p_location_id => p_txn_line_dtl_rec.location_id,
635 p_installation_date => p_txn_line_dtl_rec.installation_date,
636 p_in_service_date => p_txn_line_dtl_rec.in_service_date,
637 p_external_reference => p_txn_line_dtl_rec.external_reference,
638 p_version_label => p_txn_line_dtl_rec.version_label,
639 p_transaction_system_id => p_txn_line_dtl_rec.transaction_system_id,
640 p_sellable_flag => p_txn_line_dtl_rec.sellable_flag,
641 p_return_by_date => p_txn_line_dtl_rec.return_by_date,
642 p_active_start_date => p_txn_line_dtl_rec.active_start_date,
643 p_active_end_date => p_txn_line_dtl_rec.active_end_date,
644 p_preserve_detail_flag => l_preserve_detail_flag,
645 p_changed_instance_id => p_txn_line_dtl_rec.changed_instance_id,
646 p_reference_source_id => p_txn_line_dtl_rec.reference_source_id,
647 p_reference_source_line_id => p_txn_line_dtl_rec.reference_source_line_id,
648 p_reference_source_date => p_txn_line_dtl_rec.reference_source_date,
649 p_csi_transaction_id => p_txn_line_dtl_rec.csi_transaction_id,
650 p_source_txn_line_detail_id => p_txn_line_dtl_rec.source_txn_line_detail_id,
651 p_inv_mtl_transaction_id => p_txn_line_dtl_rec.inv_mtl_transaction_id,
652 p_processing_status => l_processing_status,
653 p_error_code => p_txn_line_dtl_rec.error_code,
654 p_error_explanation => p_txn_line_dtl_rec.error_explanation,
655 -- Added for CZ Integration (Begin)
656 p_config_inst_hdr_id => p_txn_line_dtl_rec.config_inst_hdr_id ,
657 p_config_inst_rev_num => p_txn_line_dtl_rec.config_inst_rev_num ,
658 p_config_inst_item_id => p_txn_line_dtl_rec.config_inst_item_id ,
659 p_config_inst_baseline_rev_num => p_txn_line_dtl_rec.config_inst_baseline_rev_num ,
660 p_target_commitment_date => p_txn_line_dtl_rec.target_commitment_date ,
661 p_instance_description => p_txn_line_dtl_rec.instance_description ,
662 -- Added for CZ Integration (End)
663 -- Added for Partner Ordering (Begin)
664 p_install_location_type_code => p_txn_line_dtl_rec.install_location_type_code,
665 p_install_location_id => p_txn_line_dtl_rec.install_location_id,
666 -- Added for Partner Ordering (End)
667 p_cascade_owner_flag => l_cascade_owner_flag, -- bug 2972082
668 p_attribute1 => p_txn_line_dtl_rec.attribute1,
669 p_attribute2 => p_txn_line_dtl_rec.attribute2,
670 p_attribute3 => p_txn_line_dtl_rec.attribute3,
671 p_attribute4 => p_txn_line_dtl_rec.attribute4,
672 p_attribute5 => p_txn_line_dtl_rec.attribute5,
673 p_attribute6 => p_txn_line_dtl_rec.attribute6,
674 p_attribute7 => p_txn_line_dtl_rec.attribute7,
675 p_attribute8 => p_txn_line_dtl_rec.attribute8,
676 p_attribute9 => p_txn_line_dtl_rec.attribute9,
677 p_attribute10 => p_txn_line_dtl_rec.attribute10,
678 p_attribute11 => p_txn_line_dtl_rec.attribute11,
679 p_attribute12 => p_txn_line_dtl_rec.attribute12,
680 p_attribute13 => p_txn_line_dtl_rec.attribute13,
681 p_attribute14 => p_txn_line_dtl_rec.attribute14,
682 p_attribute15 => p_txn_line_dtl_rec.attribute15,
683 p_created_by => g_user_id,
684 p_creation_date => sysdate,
685 p_last_updated_by => g_user_id,
686 p_last_update_date => sysdate,
687 p_last_update_login => g_login_id,
688 p_object_version_number => 1.0,
689 p_context => p_txn_line_dtl_rec.context,
690 p_parent_instance_id => p_txn_line_dtl_rec.parent_instance_id,
691 p_assc_txn_line_detail_id => p_txn_line_dtl_rec.assc_txn_line_detail_id,
692 p_overriding_csi_txn_id => p_txn_line_dtl_rec.overriding_csi_txn_id,
693 p_instance_status_id => p_txn_line_dtl_rec.instance_status_id);
694 exception
695 when others then
696 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
697 fnd_message.set_token('MESSAGE',
698 'csi_t_txn_line_details_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
699 fnd_msg_pub.add;
700 raise fnd_api.g_exc_error;
701 end;
702
703 p_txn_line_dtl_rec.txn_line_detail_id := l_txn_line_detail_id;
704
705 IF px_txn_party_dtl_tbl.COUNT > 0 THEN
706
707 --loop thru party detail table
708 -- new attribute, R12 Mass Update API call, due to the API call from EO, UI is unable to correctly identify
709 -- and collect and pass the correct indexes.. need a additional attribute for update_transaction_dtls
710 -- first loop through and identify this new attribute is passed by caller or not...
711 l_contact_party_index := 'N';
712 FOR l_index IN px_txn_party_dtl_tbl.FIRST..px_txn_party_dtl_tbl.LAST
713 LOOP
714
715 IF l_contact_party_index = 'N' THEN
716 IF nvl(px_txn_party_dtl_tbl(l_index).txn_contact_party_index, fnd_api.g_miss_num) <>
717 fnd_api.g_miss_num
718 THEN
719 -- new attribute passed by caller, set the flag once
720 l_contact_party_index := 'Y';
721 END IF;
722 END IF;
723 --initialize row variable
724
725 if px_txn_party_dtl_tbl(l_index).txn_line_details_index =
726 p_txn_line_dtl_index then
727
728 l_txn_party_rec := px_txn_party_dtl_tbl(l_index);
729 l_txn_party_rec.txn_line_detail_id := l_txn_line_detail_id;
730
731 -- call api to create party detail records
732 csi_t_txn_parties_pvt.create_txn_party_dtls(
733 p_api_version => p_api_version,
734 p_commit => p_commit,
735 p_init_msg_list => p_init_msg_list,
736 p_validation_level => p_validation_level,
737 p_txn_party_dtl_index => l_index,
738 p_txn_party_detail_rec => l_txn_party_rec,
739 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
740 x_return_status => l_return_status,
741 x_msg_count => l_msg_count,
742 x_msg_data => l_msg_data);
743
744 IF l_return_status <> fnd_api.g_ret_sts_success THEN
745 RAISE fnd_api.g_exc_error;
746 END IF;
747
748 px_txn_party_dtl_tbl(l_index).txn_line_detail_id :=
749 l_txn_party_rec.txn_line_detail_id;
750 px_txn_party_dtl_tbl(l_index).txn_party_detail_id :=
751 l_txn_party_rec.txn_party_detail_id;
752 END IF;
753
754 END LOOP;
755
756 l_tmp_party_dtl_tbl := px_txn_party_dtl_tbl;
757
758 /* process the contact party id */
759 FOR cont_ind IN px_txn_party_dtl_tbl.FIRST .. px_txn_party_dtl_tbl.LAST
760 LOOP
761 IF nvl(px_txn_party_dtl_tbl(cont_ind).contact_party_id, fnd_api.g_miss_num) <>
762 fnd_api.g_miss_num AND nvl(px_txn_party_dtl_tbl(cont_ind).contact_flag, 'N') = 'Y'
763 THEN
764 IF nvl(l_contact_party_index, 'N') = 'Y' THEN
765 l_contact_party_id := null;
766 FOR p_ind IN l_tmp_party_dtl_tbl.FIRST .. l_tmp_party_dtl_tbl.LAST
767 LOOP
768 IF ( l_tmp_party_dtl_tbl(p_ind).txn_contact_party_index is not null
769 AND l_tmp_party_dtl_tbl(p_ind).txn_contact_party_index <> fnd_api.g_miss_num )
770 THEN
771 IF l_tmp_party_dtl_tbl(p_ind).txn_contact_party_index = px_txn_party_dtl_tbl(cont_ind).contact_party_id
772 AND ( nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,'N') = 'N' OR
773 nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char)
774 THEN
775 l_contact_party_id := l_tmp_party_dtl_tbl(p_ind).txn_party_detail_id;
776 exit;
777 END IF;
778 END IF;
779 END LOOP;
780 ELSE
781 l_contact_party_id := null;
782 FOR p_ind IN l_tmp_party_dtl_tbl.FIRST .. l_tmp_party_dtl_tbl.LAST
783 LOOP
784 IF p_ind = px_txn_party_dtl_tbl(cont_ind).contact_party_id
785 AND nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,'N') = 'N'
786 THEN
787 l_contact_party_id := l_tmp_party_dtl_tbl(p_ind).txn_party_detail_id;
788 exit;
789 END IF;
790 END LOOP;
791 END IF;
792
793 IF l_contact_party_id is not null THEN
794 update csi_t_party_details
795 set contact_party_id = l_contact_party_id
796 where txn_party_detail_id = px_txn_party_dtl_tbl(cont_ind).txn_party_detail_id;
797 END IF;
798 END IF;
799 END LOOP;
800 END IF;
801
802 IF px_txn_org_assgn_tbl.COUNT > 0 THEN
803
804 -- loop thru org assignments table
805 FOR l_index IN px_txn_org_assgn_tbl.FIRST..px_txn_org_assgn_tbl.LAST
806 LOOP
807
808 if px_txn_org_assgn_tbl(l_index).txn_line_details_index =
809 p_txn_line_dtl_index THEN
810
811 l_txn_oa_rec := px_txn_org_assgn_tbl(l_index);
812 l_txn_oa_rec.txn_line_detail_id := l_txn_line_detail_id;
813
814 -- call api to create organization assignment records
815 csi_t_txn_ous_pvt.create_txn_org_assgn_dtls(
816 p_api_version => p_api_version,
817 p_commit => p_commit,
818 p_init_msg_list => p_init_msg_list,
819 p_validation_level => p_validation_level,
820 p_txn_org_assgn_rec => l_txn_oa_rec,
821 x_return_status => l_return_status,
822 x_msg_count => l_msg_count,
823 x_msg_data => l_msg_data);
824
825 IF l_return_status <> fnd_api.g_ret_sts_success THEN
826 raise fnd_api.g_exc_error;
827 END IF;
828
829 px_txn_org_assgn_tbl(l_index).txn_line_detail_id :=
830 l_txn_oa_rec.txn_line_detail_id;
831
832 px_txn_org_assgn_tbl(l_index).txn_operating_unit_id :=
833 l_txn_oa_rec.txn_operating_unit_id;
834
835 END IF;
836
837 END LOOP;
838
839 END IF;
840
841 IF px_txn_ext_attrib_vals_tbl.COUNT > 0 then
842
843 -- loop thru ext attrib table
844 FOR l_index IN px_txn_ext_attrib_vals_tbl.FIRST..px_txn_ext_attrib_vals_tbl.LAST
845 LOOP
846
847 IF px_txn_ext_attrib_vals_tbl(l_index).txn_line_details_index =
848 p_txn_line_dtl_index THEN
849
850 l_txn_ea_rec := px_txn_ext_attrib_vals_tbl(l_index);
851 l_txn_ea_rec.txn_line_detail_id := l_txn_line_detail_id;
852
853 -- call api to create extended attribute
854 csi_t_txn_attribs_pvt.create_txn_ext_attrib_dtls(
855 p_api_version => p_api_version,
856 p_commit => p_commit,
857 p_init_msg_list => p_init_msg_list,
858 p_validation_level => p_validation_level,
859 p_txn_ext_attrib_vals_rec => l_txn_ea_rec,
860 x_return_status => l_return_status,
861 x_msg_count => l_msg_count,
862 x_msg_data => l_msg_data);
863
864 IF l_return_status <> fnd_api.g_ret_sts_success THEN
865 debug('call to csi_t_txn_attribs_pvt.create_txn_ext_attrib_dtls failed ');
866 RAISE fnd_api.g_exc_error;
867 END IF;
868 px_txn_ext_attrib_vals_tbl(l_index).txn_line_detail_id :=
869 l_txn_ea_rec.txn_line_detail_id;
870 px_txn_ext_attrib_vals_tbl(l_index).txn_attrib_detail_id :=
871 l_txn_ea_rec.txn_attrib_detail_id;
872
873 END IF;
874
875 END LOOP;
876
877 END IF;
878
879 -- Standard check of p_commit.
880 IF fnd_api.To_Boolean( p_commit ) THEN
881 COMMIT WORK;
882 END IF;
883
884 csi_t_gen_utility_pvt.set_debug_off;
885
886 -- Standard call to get message count and if count is get message info.
887 fnd_msg_pub.Count_And_Get(
888 p_count => x_msg_count,
889 p_data => x_msg_data);
890
891 EXCEPTION
892 WHEN fnd_api.G_EXC_ERROR THEN
893
894 ROLLBACK TO create_txn_line_dtls;
895 x_return_status := fnd_api.g_ret_sts_error ;
896 fnd_msg_pub.Count_And_Get (
897 p_count => x_msg_count,
898 p_data => x_msg_data);
899 csi_t_gen_utility_pvt.set_debug_off;
900
901 WHEN fnd_api.g_exc_unexpected_error THEN
902
903 ROLLBACK TO Create_Txn_Line_Dtls;
904 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
905
906 fnd_msg_pub.Count_And_Get(
907 p_count => x_msg_count,
908 p_data => x_msg_data);
909 csi_t_gen_utility_pvt.set_debug_off;
910
911 WHEN OTHERS THEN
912
913 ROLLBACK TO Create_Txn_Line_Dtls;
914 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
915
916 IF fnd_msg_pub.Check_Msg_Level(
917 p_message_level => fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
918
919 fnd_msg_pub.Add_Exc_Msg(
920 p_pkg_name => G_PKG_NAME,
921 p_procedure_name => l_api_name);
922
923 END IF;
924
925 fnd_msg_pub.Count_And_Get(
926 p_count => x_msg_count,
927 p_data => x_msg_data);
928 csi_t_gen_utility_pvt.set_debug_off;
929
930 END create_txn_line_dtls;
931
932 /* This procedure is used to update the transaction line details. */
933 PROCEDURE update_txn_line_dtls (
934 p_api_version IN NUMBER,
935 p_commit IN VARCHAR2 := fnd_api.g_false,
936 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
937 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
938 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
939 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
940 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
941 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
942 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
943 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
944 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
945 x_return_status OUT NOCOPY VARCHAR2,
946 x_msg_count OUT NOCOPY NUMBER ,
947 x_msg_data OUT NOCOPY VARCHAR2)
948 IS
949
950 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_line_dtls';
951 l_api_version CONSTANT NUMBER := 1.0;
952 l_debug_level NUMBER;
953
954 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
955 l_msg_count NUMBER;
956 l_msg_data VARCHAR2(2000);
957
958 l_td_rec csi_t_txn_line_details%ROWTYPE;
959 l_valid BOOLEAN;
960 l_creation_flag VARCHAR2(1) := 'N';
961 l_preserve_detail_flag varchar2(1);
962 l_found VARCHAR2(1) ;
963 l_src_transaction_type_id NUMBER;
964
965 CURSOR td_cur (p_line_dtl_id in number) IS
966 SELECT *
967 FROM csi_t_txn_line_details
968 WHERE txn_line_detail_id = p_line_dtl_id;
969
970 l_instance_party_id csi_i_parties.instance_party_id%TYPE;
971 l_pty_ids_tbl csi_t_datastructures_grp.txn_party_ids_tbl;
972 l_pty_ind binary_integer;
973 l_pty_acct_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
974 l_x_pty_acct_ids_tbl csi_t_datastructures_grp.txn_pty_acct_ids_tbl;
975 l_pty_acc_ind binary_integer;
976
977 CURSOR pty_cur (p_line_dtl_id in number) IS
978 SELECT *
979 FROM csi_t_party_details
980 WHERE txn_line_detail_id = p_line_dtl_id;
981
982 l_iir_ids_tbl csi_t_datastructures_grp.txn_ii_rltns_ids_tbl;
983 l_oa_ids_tbl csi_t_datastructures_grp.txn_org_assgn_ids_tbl;
984 l_oa_ind binary_integer;
985 l_instance_ou_id csi_t_org_assignments.instance_ou_id%type;
986
987 CURSOR oa_cur(p_line_dtl_id in number) IS
988 SELECT *
989 FROM csi_t_org_assignments
990 WHERE txn_line_detail_id = p_line_dtl_id;
991
992 l_ea_ids_tbl csi_t_datastructures_grp.txn_ext_attrib_ids_tbl;
993 l_ea_ind binary_integer;
994 l_attrib_source_id csi_t_extend_attribs.attrib_source_id%type;
995
996 CURSOR ea_cur(p_line_dtl_id in number) IS
997 SELECT *
998 FROM csi_t_extend_attribs
999 WHERE txn_line_detail_id = p_line_dtl_id;
1000
1001 l_pty_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1002 l_pty_acc_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1003 l_pty_upd_ind binary_integer;
1004
1005 l_u_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
1006 l_u_ea_ind binary_integer;
1007
1008 l_c_pty_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1009 l_u_pty_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1010 l_c_pty_ind binary_integer;
1011 l_u_pty_ind binary_integer;
1012
1013 l_c_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1014 l_u_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1015 l_c_pa_ind binary_integer;
1016 l_u_pa_ind binary_integer;
1017
1018 l_c_oa_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
1019 l_u_oa_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
1020 l_c_oa_ind binary_integer;
1021 l_u_oa_ind binary_integer;
1022
1023 l_c_ii_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1024 l_u_ii_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1025 l_c_ii_ind binary_integer;
1026 l_u_ii_ind binary_integer;
1027
1028 l_tmp_party_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1029 l_contact_party_id number;
1030 l_contact_party_index VARCHAR2(1) := 'N';
1031 l_tmp_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1032
1033 BEGIN
1034
1035 -- Standard Start of API savepoint
1036 SAVEPOINT update_txn_line_dtls;
1037
1038 -- Initialize message list if p_init_msg_list is set to TRUE.
1039 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
1040 fnd_msg_pub.initialize;
1041 END IF;
1042
1043 -- Initialize API return status to success
1044 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1045
1046 -- Standard call to check for call compatibility.
1047 IF NOT
1048
1049 fnd_api.Compatible_API_Call (
1050 p_current_version_number => l_api_version,
1051 p_caller_version_number => p_api_version,
1052 p_api_name => l_api_name,
1053 p_pkg_name => G_PKG_NAME) THEN
1054
1055 RAISE fnd_api.g_exc_unexpected_error;
1056
1057 END IF;
1058
1059 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1060
1061 csi_t_gen_utility_pvt.dump_api_info(
1062 p_pkg_name => g_pkg_name,
1063 p_api_name => l_api_name);
1064
1065 IF l_debug_level > 1 THEN
1066 debug(p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
1067 END IF;
1068
1069 -- Main API code
1070
1071 csi_t_txn_line_dtls_pvt.update_txn_line(
1072 p_txn_line_rec => p_txn_line_rec,
1073 x_return_status => l_return_status);
1074
1075 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1076 debug('Update transaction lines failed.');
1077 RAISE fnd_api.g_exc_error;
1078 END IF;
1079
1080 IF p_txn_line_detail_tbl.COUNT > 0 THEN
1081
1082 FOR l_ind in p_txn_line_detail_tbl.FIRST..p_txn_line_detail_tbl.LAST
1083 LOOP
1084 IF l_debug_level > 1 THEN
1085 csi_t_gen_utility_pvt.dump_line_detail_rec(
1086 p_line_detail_rec => p_txn_line_detail_tbl(l_ind));
1087 END IF;
1088
1089 l_td_rec.txn_line_detail_id := p_txn_line_detail_tbl(l_ind).txn_line_detail_id;
1090 l_td_rec.config_inst_hdr_id := p_txn_line_detail_tbl(l_ind).config_inst_hdr_id;
1091 l_td_rec.config_inst_rev_num := p_txn_line_detail_tbl(l_ind).config_inst_rev_num;
1092 l_td_rec.config_inst_item_id := p_txn_line_detail_tbl(l_ind).config_inst_item_id;
1093
1094 csi_t_vldn_routines_pvt.check_reqd_param(
1095 p_value => l_td_rec.txn_line_detail_id,
1096 p_param_name => 'l_td_rec.txn_line_detail_id',
1097 p_api_name => l_api_name);
1098
1099 csi_t_vldn_routines_pvt.validate_txn_line_detail_id(
1100 p_txn_line_detail_id => l_td_rec.txn_line_detail_id,
1101 x_return_status => l_return_status);
1102
1103 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1104
1105 FND_MESSAGE.set_name('CSI','CSI_TXN_LINE_DTL_ID_INVALID');
1106 FND_MESSAGE.set_token('LINE_DTL_ID',l_td_rec.txn_line_detail_id);
1107 fnd_msg_pub.add;
1108 RAISE fnd_api.g_exc_error;
1109
1110 END IF;
1111
1112 IF ( nvl(l_td_rec.config_inst_hdr_id,fnd_api.g_miss_num ) <> fnd_api.g_miss_num
1113 OR nvl(l_td_rec.config_inst_hdr_id,fnd_api.g_miss_num ) <> fnd_api.g_miss_num
1114 OR nvl(l_td_rec.config_inst_hdr_id,fnd_api.g_miss_num ) <> fnd_api.g_miss_num )
1115 THEN
1116
1117 csi_t_gen_utility_pvt.add('Validating against CZ view ');
1118 Begin
1119 SELECT 'Y'
1120 into l_found
1121 FROM cz_config_items_v
1122 WHERE instance_hdr_id = l_td_rec.config_inst_hdr_id
1123 AND instance_rev_nbr = l_td_rec.config_inst_rev_num
1124 AND config_item_id = l_td_rec.config_inst_item_id;
1125 Exception when no_data_found then
1126 fnd_message.set_name('CSI','CSI_TXN_CZ_INVALID_INST_KEY');
1127 fnd_message.set_token('INST_HDR_ID',l_td_rec.config_inst_hdr_id);
1128 fnd_message.set_token('INST_REV_NBR',l_td_rec.config_inst_rev_num);
1129 fnd_message.set_token('CONFIG_ITEM_ID',l_td_rec.config_inst_item_id);
1130 fnd_msg_pub.add;
1131 RAISE fnd_api.g_exc_error;
1132 when others then
1133 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1134 fnd_message.set_token('MESSAGE',
1135 'Error in getting Config Inst data Check Failed. '||substr(sqlerrm,1,200));
1136 fnd_msg_pub.add;
1137 RAISE fnd_api.g_exc_error;
1138 End;
1139 END IF;
1140
1141 -- fetch the old values using the cursor
1142 FOR l_td_cur_rec in td_cur(l_td_rec.txn_line_detail_id)
1143 LOOP
1144
1145 l_td_rec.transaction_line_id := l_td_cur_rec.transaction_line_id;
1146 l_td_rec.sub_type_id := p_txn_line_detail_tbl(l_ind).sub_type_id;
1147 l_td_rec.inv_organization_id := p_txn_line_detail_tbl(l_ind).inv_organization_id;
1148 l_td_rec.inventory_item_id := p_txn_line_detail_tbl(l_ind).inventory_item_id;
1149
1150 --validate sub_type_id
1151 IF l_td_rec.sub_type_id <> fnd_api.g_miss_num
1152 THEN
1153
1154 csi_t_vldn_routines_pvt.check_reqd_param(
1155 p_value => l_td_rec.sub_type_id,
1156 p_param_name => 'l_td_rec.sub_type_id',
1157 p_api_name => l_api_name);
1158
1159 --debug('To Validate subtype ID, We require more parameters.');
1160
1161 csi_t_vldn_routines_pvt.check_reqd_param(
1162 p_value => l_td_rec.transaction_line_id,
1163 p_param_name => 'l_td_rec.transaction_line_id',
1164 p_api_name => l_api_name);
1165 /* not sure why the following are required for an update OR validating sub type - commenting
1166
1167 csi_t_vldn_routines_pvt.check_reqd_param(
1168 p_value => l_td_rec.inventory_item_id,
1169 p_param_name => 'l_td_rec.inventory_item_id',
1170 p_api_name => l_api_name);
1171
1172 csi_t_vldn_routines_pvt.check_reqd_param(
1173 p_value => l_td_rec.inv_organization_id,
1174 p_param_name => 'l_td_rec.inv_organization_id',
1175 p_api_name => l_api_name);
1176 */
1177
1178 csi_t_vldn_routines_pvt.validate_sub_type_id(
1179 p_transaction_line_id => l_td_rec.transaction_line_id,
1180 p_sub_type_id => l_td_rec.sub_type_id,
1181 x_return_status => l_return_status);
1182
1183 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1184
1185 debug('Validate sub type id failed.');
1186 fnd_message.set_name('CSI','CSI_TXN_SUB_TYPE_ID_INVALID');
1187 fnd_message.set_token('SUB_TYPE_ID',l_td_rec.sub_type_id);
1188 fnd_msg_pub.add;
1189 raise fnd_api.g_exc_error;
1190
1191 END IF;
1192 END IF;
1193
1194 l_td_rec.instance_exists_flag := p_txn_line_detail_tbl(l_ind).instance_exists_flag;
1195 l_td_rec.instance_id := p_txn_line_detail_tbl(l_ind).instance_id;
1196
1197 /* Added this IF piece so that API will derive value for this based on the value for instance id column - shegde
1198 */
1199
1200 -- Commented and Added the following code as part of fix 2756727
1201 /*
1202 IF nvl(l_td_cur_rec.instance_exists_flag,fnd_api.g_miss_char)
1203 <> nvl(l_td_rec.instance_exists_flag,fnd_api.g_miss_char) THEN
1204 IF ( l_td_rec.instance_exists_flag = fnd_api.g_miss_char
1205 OR l_td_rec.instance_exists_flag is NULL ) THEN
1206 IF ( l_td_rec.instance_id <> fnd_api.g_miss_num
1207 AND l_td_rec.instance_id is NOT NULL ) THEN
1208 l_td_rec.instance_exists_flag := 'Y';
1209 ELSE
1210 l_td_rec.instance_exists_flag := 'N';
1211 END IF;
1212 END IF;
1213 END IF;
1214 */
1215
1216 IF nvl(l_td_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1217 THEN
1218 l_td_rec.instance_exists_flag := 'Y';
1219 ELSE
1220 l_td_rec.instance_exists_flag := 'N';
1221 END IF;
1222
1223 -- End code fix as part of fix for Bug 2756727.
1224
1225 -- validate instance ID
1226 -- IF l_td_rec.instance_exists_flag = 'Y' THEN
1227 IF nvl(l_td_rec.instance_id, fnd_api.g_miss_num) <>
1228 fnd_api.g_miss_num THEN
1229 -- csi_t_vldn_routines_pvt.check_reqd_param(
1230 -- p_value => l_td_rec.instance_id,
1231 -- p_param_name => 'l_td_rec.instance_id',
1232 -- p_api_name => l_api_name);
1233
1234 csi_t_vldn_routines_pvt.validate_instance_id(
1235 p_instance_id => l_td_rec.instance_id,
1236 x_return_status => l_return_status);
1237
1238 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1239
1240 FND_MESSAGE.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
1241 FND_MESSAGE.set_token('INSTANCE_ID',l_td_rec.instance_id);
1242 fnd_msg_pub.add;
1243 RAISE fnd_api.g_exc_error;
1244
1245 END IF;
1246
1247 END IF;
1248
1249 l_td_rec.source_transaction_flag :=
1250 p_txn_line_detail_tbl(l_ind).source_transaction_flag;
1251 l_td_rec.quantity := p_txn_line_detail_tbl(l_ind).quantity;
1252 l_td_rec.unit_of_measure := p_txn_line_detail_tbl(l_ind).unit_of_measure;
1253
1254 IF l_td_rec.source_transaction_flag = 'Y' THEN
1255
1256 /* not sure why the following are required for an update OR the tld is sourced - commenting
1257 in any case, for validating serial, lot, revision etc WHEN CHANGED
1258 we are checking for the same again...
1259
1260 -- mandate item , organization and quantity and uom
1261 csi_t_vldn_routines_pvt.check_reqd_param(
1262 p_value => l_td_rec.inv_organization_id,
1263 p_param_name => 'l_td_rec.inv_organization_id',
1264 p_api_name => l_api_name);
1265
1266 csi_t_vldn_routines_pvt.check_reqd_param(
1267 p_value => l_td_rec.inventory_item_id,
1268 p_param_name => 'l_td_rec.inventory_item_id',
1269 p_api_name => l_api_name);
1270
1271 csi_t_vldn_routines_pvt.check_reqd_param(
1272 p_value => l_td_rec.quantity,
1273 p_param_name => 'l_td_rec.quantity',
1274 p_api_name => l_api_name);
1275
1276 csi_t_vldn_routines_pvt.check_reqd_param(
1277 p_value => l_td_rec.unit_of_measure,
1278 p_param_name => 'l_td_rec.unit_of_measure',
1279 p_api_name => l_api_name);
1280 */
1281 -- need to add code here to call validate_source_integrity cause we do not seem to be revalidating
1282 -- if the quantity OR UOM is being changed. right now making it null since a complete check
1283 -- for the current usage of this API needs to be done
1284 null;
1285 END IF;
1286
1287 l_td_rec.csi_system_id := p_txn_line_detail_tbl(l_ind).csi_system_id;
1288
1289 -- validate csi_system_id
1290 IF l_td_rec.csi_system_id <> fnd_api.g_miss_num
1291 AND
1292 l_td_rec.csi_system_id is not null
1293 THEN
1294
1295 l_valid := csi_item_instance_vld_pvt.is_valid_system_id(
1296 p_system_id => l_td_rec.csi_system_id);
1297
1298 IF NOT (l_valid) THEN
1299 debug('Validate csi system id failed.');
1300 RAISE fnd_api.g_exc_error;
1301 END IF;
1302
1303 END IF;
1304
1305 -- ##validate organization_id
1306 IF l_td_rec.inv_organization_id <> fnd_api.g_miss_num THEN
1307 null;
1308 END IF;
1309
1310 -- Start of Addition for ER 6936037
1311 BEGIN
1312 SELECT source_transaction_type_id
1313 into l_src_transaction_type_id
1314 from CSI_T_TRANSACTION_LINES
1315 WHERE transaction_line_id = l_td_rec.transaction_line_id;
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 debug('No Transaction Type Id found for transaction_line_id - ' || l_td_rec.transaction_line_id);
1319 null;
1320 END;
1321 -- End of Addition for ER 6936037
1322
1323 -- validate inventory_item_id
1324 IF l_td_rec.inventory_item_id <> fnd_api.g_miss_num THEN
1325
1326 csi_t_vldn_routines_pvt.check_reqd_param(
1327 p_value => l_td_rec.inventory_item_id,
1328 p_param_name => 'l_td_rec.inventory_item_id',
1329 p_api_name => l_api_name);
1330
1331 -- If the transaction is mass update, IB trackable checking is skipped
1332 -- as per ER 6936037
1333 IF NVL(l_src_transaction_type_id, fnd_api.g_miss_num) <> 3 THEN
1334 l_valid := csi_item_instance_vld_pvt.is_trackable(
1335 p_inv_item_id => l_td_rec.inventory_item_id,
1336 p_org_id => l_td_rec.inv_organization_id);
1337
1338 IF NOT (l_valid) THEN
1339 debug('Validate item for trackabality failed.');
1340 RAISE fnd_api.g_exc_error;
1341 END IF;
1342 END IF; -- Checking for Mass Addition transaction
1343 END IF;
1344
1345 l_td_rec.inventory_revision := p_txn_line_detail_tbl(l_ind).inventory_revision;
1346 -- ##validate item_revision
1347 IF l_td_rec.inventory_revision <> fnd_api.g_miss_char
1348 AND
1349 l_td_rec.inventory_revision is not null
1350 THEN
1351 null;
1352 END IF;
1353
1354 l_td_rec.instance_type_code := p_txn_line_detail_tbl(l_ind).instance_type_code;
1355 -- validate instance_type_code
1356 IF l_td_rec.instance_type_code <> fnd_api.g_miss_char
1357 AND
1358 l_td_rec.instance_type_code is not null
1359 THEN
1360
1361 l_valid :=
1362 csi_item_instance_vld_pvt.is_valid_instance_type(
1363 p_instance_type_code => l_td_rec.instance_type_code);
1364
1365 IF NOT (l_valid) THEN
1366 debug('Validate instance type code failed.');
1367 RAISE fnd_api.g_exc_error;
1368 END IF;
1369
1370 END IF;
1371
1372 l_td_rec.item_condition_id := p_txn_line_detail_tbl(l_ind).item_condition_id;
1373
1374 --validate item_condition_id
1375 IF l_td_rec.item_condition_id <> fnd_api.g_miss_num
1376 AND
1377 l_td_rec.item_condition_id is not null
1378 THEN
1379
1380 csi_item_instance_vld_pvt.is_valid_condition(
1381 p_instance_condition_id => l_td_rec.item_condition_id,
1382 p_creation_complete_flag => l_creation_flag,
1383 l_return_value => l_valid);
1384
1385 IF not(l_valid) THEN
1386 debug('Validate item condition failed.');
1387 RAISE fnd_api.g_exc_error;
1388 END IF;
1389
1390 END IF;
1391
1392
1393 -- ##validate uom_code
1394 IF l_td_rec.unit_of_measure <> fnd_api.g_miss_char
1395 THEN
1396 null;
1397 END IF;
1398
1399 l_td_rec.qty_remaining := p_txn_line_detail_tbl(l_ind).qty_remaining;
1400
1401 l_td_rec.lot_number := p_txn_line_detail_tbl(l_ind).lot_number;
1402 -- validate lot number
1403 IF l_td_rec.lot_number <> fnd_api.g_miss_char
1404 AND
1405 l_td_rec.lot_number is not null
1406 THEN
1407
1408 csi_t_vldn_routines_pvt.check_reqd_param(
1409 p_value => l_td_rec.inventory_item_id,
1410 p_param_name => 'l_td_rec.inventory_item_id',
1411 p_api_name => l_api_name);
1412
1413 csi_t_vldn_routines_pvt.check_reqd_param(
1414 p_value => l_td_rec.inv_organization_id,
1415 p_param_name => 'l_td_rec.inv_organization_id',
1416 p_api_name => l_api_name);
1417
1418 debug('Validating line details lot number .');
1419
1420 csi_t_vldn_routines_pvt.validate_lot_number(
1421 p_inventory_item_id => l_td_rec.inventory_item_id,
1422 p_organization_id => l_td_rec.inv_organization_id,
1423 p_lot_number => l_td_rec.lot_number,
1424 x_return_status => l_return_status);
1425
1426 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1427 debug('csi_t_vldn_routines_pvt.validate_lot_number Failed.');
1428 RAISE fnd_api.g_exc_error;
1429 END IF;
1430
1431 END IF;
1432
1433 l_td_rec.serial_number := p_txn_line_detail_tbl(l_ind).serial_number;
1434 l_td_rec.mfg_serial_number_flag := p_txn_line_detail_tbl(l_ind).mfg_serial_number_flag;
1435
1436 -- IF l_td_rec.mfg_serial_number_flag = 'Y'
1437 -- AND bug 3593990
1438 IF nvl(l_td_rec.serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
1439
1440 csi_t_vldn_routines_pvt.check_reqd_param(
1441 p_value => l_td_rec.inventory_item_id,
1442 p_param_name => 'l_td_rec.inventory_item_id',
1443 p_api_name => l_api_name);
1444
1445 csi_t_vldn_routines_pvt.check_reqd_param(
1446 p_value => l_td_rec.inv_organization_id,
1447 p_param_name => 'l_td_rec.inv_organization_id',
1448 p_api_name => l_api_name);
1449
1450 debug('Validating Serial Number.');
1451
1452 csi_t_vldn_routines_pvt.validate_serial_number(
1453 p_inventory_item_id => l_td_rec.inventory_item_id,
1454 p_organization_id => l_td_rec.inv_organization_id,
1455 p_serial_number => l_td_rec.serial_number,
1456 x_return_status => l_return_status);
1457
1458 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1459 debug('csi_t_vldn_routines_pvt.validate_serial_number Failed.');
1460 RAISE fnd_api.g_exc_error;
1461 END IF;
1462
1463 /*
1464 --validate serial_number
1465 csi_item_instance_vld_pvt.validate_serial_number(
1466 p_inv_org_id => l_td_rec.inv_organization_id,
1467 p_inv_item_id => l_td_rec.inventory_item_id,
1468 p_serial_number => l_td_rec.serial_number,
1469 p_mfg_serial_number_flag => l_td_rec.mfg_serial_number_flag,
1470 p_creation_complete_flag => l_creation_flag,
1471 l_return_value => l_valid);
1472
1473 IF NOT (l_valid) THEN
1474 debug('Validate serial number failed.');
1475 RAISE fnd_api.g_exc_error;
1476 END IF;
1477 */
1478
1479 END IF;
1480
1481 l_td_rec.location_type_code := p_txn_line_detail_tbl(l_ind).location_type_code;
1482
1483 -- validate location_type_code
1484 IF l_td_rec.location_type_code <> fnd_api.g_miss_char
1485 AND
1486 l_td_rec.location_type_code is not null
1487 THEN
1488
1489 l_valid :=
1490 csi_item_instance_vld_pvt.is_valid_location_source(
1491 p_loc_source_table => l_td_rec.location_type_code);
1492
1493 IF NOT (l_valid) THEN
1494 debug('Validate location type code failed.');
1495 RAISE fnd_api.g_exc_error;
1496 END IF;
1497
1498 END IF;
1499
1500 l_td_rec.location_id := p_txn_line_detail_tbl(l_ind).location_id;
1501
1502 --validate location_id
1503 IF l_td_rec.location_id <> fnd_api.g_miss_num
1504 AND
1505 l_td_rec.location_id is not null
1506 THEN
1507
1508 l_valid := csi_item_instance_vld_pvt.is_valid_location_id(
1509 p_location_source_table => l_td_rec.location_type_code,
1510 p_location_id => l_td_rec.location_id);
1511
1512 IF NOT (l_valid) then
1513 debug('Validate location id failed.');
1514 RAISE fnd_api.g_exc_error;
1515 END IF;
1516
1517 END IF;
1518
1519 /* Begin FOR install Locationo_Id and Install_Location_Type_Code*/
1520
1521 l_td_rec.install_location_type_code := p_txn_line_detail_tbl(l_ind).install_location_type_code;
1522
1523 -- validate install_location_type_code
1524 IF l_td_rec.install_location_type_code <> fnd_api.g_miss_char
1525 AND
1526 l_td_rec.install_location_type_code is not null
1527 THEN
1528 l_valid := csi_item_instance_vld_pvt.is_valid_location_source(
1529 p_loc_source_table => l_td_rec.install_location_type_code);
1530
1531 IF NOT (l_valid) THEN
1532 debug('Validate Install location type code failed.');
1533 RAISE fnd_api.g_exc_error;
1534 END IF;
1535 END IF;
1536
1537 l_td_rec.install_location_id := p_txn_line_detail_tbl(l_ind).install_location_id;
1538
1539 -- validate install_location_id
1540 IF l_td_rec.install_location_id <> fnd_api.g_miss_num
1541 AND
1542 l_td_rec.install_location_id is not null
1543 THEN
1544 l_valid := csi_item_instance_vld_pvt.is_valid_location_id(
1545 p_location_source_table => l_td_rec.install_location_type_code,
1546 p_location_id => l_td_rec.install_location_id);
1547
1548 IF NOT (l_valid) then
1549 debug('Validate Install location id failed.');
1550 RAISE fnd_api.g_exc_error;
1551 END IF;
1552 END IF;
1553
1554 /* End FOR install Locationo_Id and Install_Location_Type_Code */
1555
1556 IF NVL(l_td_rec.reference_source_line_id, fnd_api.g_miss_num)
1557 <> fnd_api.g_miss_num
1558 THEN
1559
1560 csi_t_vldn_routines_pvt.validate_txn_source_id(
1561 p_txn_source_name => 'ORDER_ENTRY',
1562 p_txn_source_id => l_td_rec.reference_source_line_id,
1563 x_return_status => l_return_status);
1564
1565 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1566 debug('csi_t_vldn_routines_pvt.validate_txn_source_id.');
1567 RAISE fnd_api.g_exc_error;
1568 END IF;
1569
1570 END IF;
1571
1572 l_td_rec.instance_status_id := p_txn_line_detail_tbl(l_ind).instance_status_id;
1573 l_td_rec.overriding_csi_txn_id := p_txn_line_detail_tbl(l_ind).overriding_csi_txn_id;
1574 -- validate instance status id
1575 IF NVL(l_td_rec.instance_status_id, fnd_api.g_miss_num) <>
1576 fnd_api.g_miss_num
1577 THEN
1578 --debug('Validate instance status id .');
1579 csi_item_instance_vld_pvt.is_valid_status(
1580 p_instance_status_id => l_td_rec.instance_status_id,
1581 p_creation_complete_flag => l_creation_flag,
1582 l_return_value => l_valid);
1583
1584 IF not(l_valid) THEN
1585 debug('Validate instance status id failed.');
1586 RAISE fnd_api.g_exc_error;
1587 END IF;
1588 END IF;
1589
1590 l_td_rec.installation_date := p_txn_line_detail_tbl(l_ind).installation_date;
1591 l_td_rec.in_service_date := p_txn_line_detail_tbl(l_ind).in_service_date;
1592 l_td_rec.external_reference:= p_txn_line_detail_tbl(l_ind).external_reference;
1593 l_td_rec.version_label := p_txn_line_detail_tbl(l_ind).version_label;
1594 l_td_rec.transaction_system_id := p_txn_line_detail_tbl(l_ind).transaction_system_id;
1595 l_td_rec.sellable_flag := p_txn_line_detail_tbl(l_ind).sellable_flag;
1596 l_td_rec.return_by_date := p_txn_line_detail_tbl(l_ind).return_by_date;
1597 l_td_rec.active_start_date := p_txn_line_detail_tbl(l_ind).active_start_date;
1598 l_td_rec.active_end_date := p_txn_line_detail_tbl(l_ind).active_end_date;
1599 l_td_rec.preserve_detail_flag := p_txn_line_detail_tbl(l_ind).preserve_detail_flag;
1600 l_td_rec.changed_instance_id := p_txn_line_detail_tbl(l_ind).changed_instance_id;
1601 l_td_rec.reference_source_id := p_txn_line_detail_tbl(l_ind).reference_source_id;
1602 l_td_rec.reference_source_line_id := p_txn_line_detail_tbl(l_ind).reference_source_line_id;
1603 l_td_rec.reference_source_date:= p_txn_line_detail_tbl(l_ind).reference_source_date;
1604 l_td_rec.csi_transaction_id:= p_txn_line_detail_tbl(l_ind).csi_transaction_id;
1605 l_td_rec.source_txn_line_detail_id := p_txn_line_detail_tbl(l_ind).source_txn_line_detail_id;
1606 l_td_rec.inv_mtl_transaction_id := p_txn_line_detail_tbl(l_ind).inv_mtl_transaction_id;
1607 l_td_rec.processing_status := p_txn_line_detail_tbl(l_ind).processing_status;
1608 l_td_rec.error_code := p_txn_line_detail_tbl(l_ind).error_code;
1609 l_td_rec.error_explanation := p_txn_line_detail_tbl(l_ind).error_explanation;
1610 l_td_rec.config_inst_hdr_id := p_txn_line_detail_tbl(l_ind).config_inst_hdr_id;
1611 l_td_rec.config_inst_rev_num := p_txn_line_detail_tbl(l_ind).config_inst_rev_num;
1612 l_td_rec.config_inst_item_id := p_txn_line_detail_tbl(l_ind).config_inst_item_id;
1613 l_td_rec.config_inst_baseline_rev_num := p_txn_line_detail_tbl(l_ind).config_inst_baseline_rev_num;
1614 l_td_rec.target_commitment_date := p_txn_line_detail_tbl(l_ind).target_commitment_date;
1615 l_td_rec.instance_description := p_txn_line_detail_tbl(l_ind).instance_description;
1616 l_td_rec.cascade_owner_flag := p_txn_line_detail_tbl(l_ind).cascade_owner_flag;
1617 l_td_rec.attribute1 := p_txn_line_detail_tbl(l_ind).attribute1;
1618 l_td_rec.attribute2 := p_txn_line_detail_tbl(l_ind).attribute2;
1619 l_td_rec.attribute3 := p_txn_line_detail_tbl(l_ind).attribute3;
1620 l_td_rec.attribute4 := p_txn_line_detail_tbl(l_ind).attribute4;
1621 l_td_rec.attribute5 := p_txn_line_detail_tbl(l_ind).attribute5;
1622 l_td_rec.attribute6 := p_txn_line_detail_tbl(l_ind).attribute6;
1623 l_td_rec.attribute7 := p_txn_line_detail_tbl(l_ind).attribute7;
1624 l_td_rec.attribute8 := p_txn_line_detail_tbl(l_ind).attribute8;
1625 l_td_rec.attribute9 := p_txn_line_detail_tbl(l_ind).attribute9;
1626 l_td_rec.attribute10 := p_txn_line_detail_tbl(l_ind).attribute10;
1627 l_td_rec.attribute11 := p_txn_line_detail_tbl(l_ind).attribute11;
1628 l_td_rec.attribute12 := p_txn_line_detail_tbl(l_ind).attribute12;
1629 l_td_rec.attribute13 := p_txn_line_detail_tbl(l_ind).attribute13;
1630 l_td_rec.attribute14 := p_txn_line_detail_tbl(l_ind).attribute14;
1631 l_td_rec.attribute15 := p_txn_line_detail_tbl(l_ind).attribute15;
1632 l_td_rec.created_by := l_td_cur_rec.created_by;
1633 l_td_rec.creation_date := l_td_cur_rec.creation_date;
1634 l_td_rec.last_updated_by := g_user_id;
1635 l_td_rec.last_update_date := sysdate;
1636 l_td_rec.last_update_login := g_login_id;
1637 l_td_rec.object_version_number := p_txn_line_detail_tbl(l_ind).object_version_number;
1638 l_td_rec.context := p_txn_line_detail_tbl(l_ind).context;
1639 l_td_rec.parent_instance_id:= p_txn_line_detail_tbl(l_ind).parent_instance_id;
1640 l_td_rec.assc_txn_line_detail_id := p_txn_line_detail_tbl(l_ind).assc_txn_line_detail_id;
1641
1642 --logic for preserving children using the preserve detail flag
1643 l_pty_ind := 0;
1644 IF ( l_td_rec.instance_id <> fnd_api.g_miss_num
1645 AND nvl(l_td_rec.instance_id,-9999) <> nvl(l_td_cur_rec.instance_id,-9999) ) THEN
1646
1647 debug('Entering code for preserve details.');
1648
1649 /* I have to do this update statement here because all the child entities
1650 are processed before the txn line detail is updated */
1651
1652 update csi_t_txn_line_details
1653 set instance_id = l_td_rec.instance_id,
1654 instance_exists_flag = l_td_rec.instance_exists_flag
1655 where txn_line_detail_id = l_td_rec.txn_line_detail_id;
1656
1657 IF l_td_rec.instance_id <> nvl(l_td_cur_rec.instance_id,fnd_api.g_miss_num) THEN
1658
1659 debug('User is trying to switch the instance id.');
1660
1661 l_pty_upd_ind := 0;
1662
1663 FOR l_pty_cur_rec IN pty_cur(l_td_rec.txn_line_detail_id)
1664 LOOP
1665
1666 /* If the old instance id is null then preserve the children */
1667
1668 IF nvl(l_td_cur_rec.instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1669 l_preserve_detail_flag := 'Y';
1670 ELSE
1671 l_preserve_detail_flag := nvl(l_pty_cur_rec.preserve_detail_flag,'N');
1672 END IF;
1673
1674 IF l_preserve_detail_flag = 'Y' OR
1675 l_pty_cur_rec.relationship_type_code = 'OWNER' THEN
1676
1677 debug('Trying to presere the party record. txn_party_detail_id :'||
1678 l_pty_cur_rec.txn_party_detail_id);
1679
1680 BEGIN
1681 IF l_pty_cur_rec.Contact_party_id is NULL THEN
1682
1683 SELECT instance_party_id
1684 INTO l_instance_party_id
1685 FROM csi_i_parties
1686 WHERE instance_id = l_td_rec.instance_id -- new instance
1687 AND party_id = l_pty_cur_rec.party_source_id -- old party
1688 AND party_source_table = l_pty_cur_rec.party_source_table
1689 AND relationship_type_code = l_pty_cur_rec.relationship_type_code
1690 AND nvl(contact_flag,'N') = nvl(l_pty_cur_rec.contact_flag,'N')
1691 AND sysdate between nvl(active_start_date, sysdate-1)
1692 and nvl(active_end_date, sysdate+1);
1693
1694 ELSE
1695
1696 /* NEW QUERY FOR CONTACT_PARTY_ID is not null ADDED for the bug 4251709 */
1697
1698 SELECT csiip.instance_party_id
1699 INTO l_instance_party_id
1700 FROM
1701 csi_i_parties csiip,
1702 csi_i_parties csiipc,
1703 csi_t_party_details csitpd,
1704 csi_t_party_details csitpdc
1705 WHERE csitpdc.party_SOURCE_id = l_pty_cur_rec.party_source_id
1706 AND csiipc.instance_id=l_td_rec.instance_id
1707 AND csiip.contact_ip_id=csiipc.instance_party_id
1708 AND csitpdc.contact_party_id=csitpd.txn_party_detail_id
1709 AND csitpdc.party_source_table = csiip.party_source_table
1710 AND csitpdc.relationship_type_code = csiip.relationship_type_code
1711 AND nvl(csitpdc.contact_flag,'N')= nvl(csiip.contact_flag,'N')
1712 AND csitpdc.party_SOURCE_id=csiip.party_id
1713 AND csitpdc.party_source_table = l_pty_cur_rec.party_source_table
1714 AND csitpdc.relationship_type_code = l_pty_cur_rec.relationship_type_code
1715 AND csitpdc.txn_party_detail_id= l_pty_cur_rec.txn_party_detail_id
1716 AND nvl(csitpdc.contact_flag,'N') =nvl(l_pty_cur_rec.contact_flag,'N')
1717 AND csitpd.party_source_id= csiipc.party_id
1718 AND csitpd.party_source_table= csiipc.party_source_table
1719 AND csitpd.relationship_type_code = csiipc.relationship_type_code
1720 AND csitpd.contact_flag = csiipc.contact_flag
1721 AND sysdate between nvl(csiip.active_start_date, sysdate-1)
1722 AND nvl(csiip.active_end_date, sysdate+1);
1723
1724 END IF;
1725
1726 EXCEPTION
1727 WHEN no_data_found THEN
1728
1729 /* this query will return one and only one record */
1730 /* to make sure multiple parents are not created */
1731 IF l_pty_cur_rec.relationship_type_code = 'OWNER' THEN
1732
1733 SELECT instance_party_id
1734 INTO l_instance_party_id
1735 FROM csi_i_parties
1736 WHERE instance_id = l_td_rec.instance_id
1737 AND relationship_type_code = 'OWNER';
1738
1739 ELSE
1740
1741 l_instance_party_id := null;
1742
1743 END IF;
1744 END;
1745
1746 -- populate the party table to update the foreign key (instance party id)
1747
1748 l_pty_upd_ind := l_pty_upd_ind + 1;
1749
1750 l_pty_tbl(l_pty_upd_ind).txn_party_detail_id :=
1751 l_pty_cur_rec.txn_party_detail_id;
1752 l_pty_tbl(l_pty_upd_ind).txn_line_detail_id :=
1753 l_pty_cur_rec.txn_line_detail_id;
1754 l_pty_tbl(l_pty_upd_ind).instance_party_id :=
1755 l_instance_party_id;
1756 l_pty_tbl(l_pty_upd_ind).party_source_table :=
1757 l_pty_cur_rec.party_source_table;
1758 l_pty_tbl(l_pty_upd_ind).party_source_id :=
1759 l_pty_cur_rec.party_source_id;
1760 l_pty_tbl(l_pty_upd_ind).relationship_type_code :=
1761 l_pty_cur_rec.relationship_type_code;
1762 l_pty_tbl(l_pty_upd_ind).contact_flag :=
1763 l_pty_cur_rec.contact_flag;
1764 l_pty_tbl(l_pty_upd_ind).contact_party_id :=
1765 l_pty_cur_rec.contact_party_id;
1766 l_pty_tbl(l_pty_upd_ind).active_start_date :=
1767 l_pty_cur_rec.active_start_date;
1768 l_pty_tbl(l_pty_upd_ind).active_end_date :=
1769 l_pty_cur_rec.active_end_date;
1770 l_pty_tbl(l_pty_upd_ind).preserve_detail_flag :=
1771 l_pty_cur_rec.preserve_detail_flag;
1772 l_pty_tbl(l_pty_upd_ind).context := l_pty_cur_rec.context;
1773 l_pty_tbl(l_pty_upd_ind).attribute1 := l_pty_cur_rec.attribute1;
1774 l_pty_tbl(l_pty_upd_ind).attribute2 := l_pty_cur_rec.attribute2;
1775 l_pty_tbl(l_pty_upd_ind).attribute3 := l_pty_cur_rec.attribute3;
1776 l_pty_tbl(l_pty_upd_ind).attribute4 := l_pty_cur_rec.attribute4;
1777 l_pty_tbl(l_pty_upd_ind).attribute5 := l_pty_cur_rec.attribute5;
1778 l_pty_tbl(l_pty_upd_ind).attribute6 := l_pty_cur_rec.attribute6;
1779 l_pty_tbl(l_pty_upd_ind).attribute7 := l_pty_cur_rec.attribute7;
1780 l_pty_tbl(l_pty_upd_ind).attribute8 := l_pty_cur_rec.attribute8;
1781 l_pty_tbl(l_pty_upd_ind).attribute9 := l_pty_cur_rec.attribute9;
1782 l_pty_tbl(l_pty_upd_ind).attribute10:= l_pty_cur_rec.attribute10;
1783 l_pty_tbl(l_pty_upd_ind).attribute11:= l_pty_cur_rec.attribute11;
1784 l_pty_tbl(l_pty_upd_ind).attribute12:= l_pty_cur_rec.attribute12;
1785 l_pty_tbl(l_pty_upd_ind).attribute13:= l_pty_cur_rec.attribute13;
1786 l_pty_tbl(l_pty_upd_ind).attribute14:= l_pty_cur_rec.attribute14;
1787 l_pty_tbl(l_pty_upd_ind).attribute15:= l_pty_cur_rec.attribute15;
1788 l_pty_tbl(l_pty_upd_ind).object_version_number :=
1789 l_pty_cur_rec.object_version_number;
1790 l_pty_tbl(l_pty_upd_ind).primary_flag :=
1791 l_pty_cur_rec.primary_flag;
1792 l_pty_tbl(l_pty_upd_ind).preferred_flag :=
1793 l_pty_cur_rec.preferred_flag;
1794
1795 ELSE
1796 -- delete the non preserved children for parties
1797 l_pty_ind := l_pty_ind + 1;
1798
1799 l_pty_acct_ids_tbl(l_pty_ind).txn_party_detail_id :=
1800 l_pty_cur_rec.txn_party_detail_id;
1801 l_pty_acct_ids_tbl(l_pty_ind).txn_account_detail_id :=
1802 fnd_api.g_miss_num;
1803
1804 l_pty_ids_tbl(l_pty_ind).txn_line_detail_id :=
1805 l_pty_cur_rec.txn_line_detail_id;
1806 l_pty_ids_tbl(l_pty_ind).txn_party_detail_id :=
1807 l_pty_cur_rec.txn_party_detail_id;
1808
1809 END IF;
1810
1811 END LOOP;
1812
1813 IF l_pty_tbl.count > 0 THEN
1814
1815 csi_t_txn_parties_grp.update_txn_party_dtls(
1816 p_api_version => p_api_version,
1817 p_commit => p_commit,
1818 p_init_msg_list => p_init_msg_list,
1819 p_validation_level => p_validation_level,
1820 p_txn_party_detail_tbl => l_pty_tbl,
1821 px_txn_pty_acct_detail_tbl => l_pty_acc_tbl,
1822 x_return_status => l_return_status,
1823 x_msg_count => l_msg_count,
1824 x_msg_data => l_msg_data);
1825
1826 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1827 RAISE fnd_api.g_exc_error;
1828 END IF;
1829
1830 END IF;
1831
1832 END IF;
1833
1834 -- ii_relationships
1835 IF (l_td_rec.instance_id <> nvl(l_td_cur_rec.instance_id, fnd_api.g_miss_num))
1836 OR
1837 (l_td_rec.inventory_item_id <> nvl(l_td_cur_rec.inventory_item_id, fnd_api.g_miss_num))
1838 THEN
1839
1840 IF nvl(l_td_cur_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1841 l_iir_ids_tbl(1).transaction_line_id := l_td_rec.transaction_line_id;
1842 l_iir_ids_tbl(1).txn_relationship_id := fnd_api.g_miss_num;
1843 END IF;
1844
1845 END IF;
1846
1847 -- org assignments
1848 l_oa_ind := 0;
1849
1850 IF (l_td_rec.instance_id <> nvl(l_td_cur_rec.instance_id, fnd_api.g_miss_num))
1851 THEN
1852
1853 FOR l_oa_cur_rec IN oa_cur(l_td_rec.txn_line_detail_id)
1854 LOOP
1855
1856 IF nvl(l_td_cur_rec.instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1857 l_preserve_detail_flag := 'Y';
1858 ELSE
1859 l_preserve_detail_flag := nvl(l_oa_cur_rec.preserve_detail_flag,'N');
1860 END IF;
1861
1862 IF l_preserve_detail_flag = 'Y' THEN
1863 IF nvl(l_oa_cur_rec.instance_ou_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1864 THEN
1865 BEGIN
1866
1867 SELECT instance_ou_id
1868 INTO l_instance_ou_id
1869 FROM csi_i_org_assignments
1870 WHERE instance_id = l_td_rec.instance_id
1871 AND operating_unit_id = l_oa_cur_rec.operating_unit_id;
1872
1873 EXCEPTION
1874 WHEN no_data_found THEN
1875 l_instance_ou_id := null;
1876 END;
1877
1878 UPDATE csi_t_org_assignments
1879 SET instance_ou_id = l_instance_ou_id
1880 WHERE txn_operating_unit_id = l_oa_cur_rec.txn_operating_unit_id;
1881
1882 END IF;
1883 ELSE
1884
1885 l_oa_ind := l_oa_ind + 1;
1886
1887 l_oa_ids_tbl(l_oa_ind).txn_line_detail_id :=
1888 l_oa_cur_rec.txn_line_detail_id;
1889 l_oa_ids_tbl(l_oa_ind).txn_operating_unit_id :=
1890 l_oa_cur_rec.txn_operating_unit_id;
1891
1892 END IF;
1893
1894 END LOOP;
1895
1896 END IF;
1897
1898 -- extended attributes
1899 l_ea_ind := 0;
1900
1901 IF (l_td_rec.instance_id <> nvl(l_td_cur_rec.instance_id, fnd_api.g_miss_num))
1902 THEN
1903
1904 FOR l_ea_cur_rec IN ea_cur(l_td_rec.txn_line_detail_id)
1905 LOOP
1906
1907 IF nvl(l_td_cur_rec.instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1908 l_preserve_detail_flag := 'Y';
1909 ELSE
1910 l_preserve_detail_flag := nvl(l_ea_cur_rec.preserve_detail_flag,'N');
1911 END IF;
1912
1913 IF l_preserve_detail_flag = 'Y' THEN
1914
1915 IF l_ea_cur_rec.attrib_source_table = 'CSI_IEA_VALUES' THEN
1916
1917 BEGIN
1918
1919 SELECT attribute_value_id
1920 INTO l_attrib_source_id
1921 FROM csi_iea_values
1922 WHERE instance_id = l_td_rec.instance_id
1923 AND attribute_value = l_ea_cur_rec.attribute_value;
1924
1925 UPDATE csi_t_extend_attribs
1926 SET attrib_source_id = l_attrib_source_id
1927 WHERE txn_attrib_detail_id =
1928 l_ea_cur_rec.txn_attrib_detail_id;
1929
1930 EXCEPTION
1931 WHEN no_data_found THEN
1932 l_ea_ind := l_ea_ind + 1;
1933
1934 l_ea_ids_tbl(l_ea_ind).txn_line_detail_id :=
1935 l_ea_cur_rec.txn_line_detail_id;
1936 l_ea_ids_tbl(l_ea_ind).txn_attrib_detail_id :=
1937 l_ea_cur_rec.txn_attrib_detail_id;
1938 END;
1939
1940 END IF;
1941
1942 ELSE
1943 l_ea_ind := l_ea_ind + 1;
1944
1945 l_ea_ids_tbl(l_ea_ind).txn_line_detail_id :=
1946 l_ea_cur_rec.txn_line_detail_id;
1947 l_ea_ids_tbl(l_ea_ind).txn_attrib_detail_id :=
1948 l_ea_cur_rec.txn_attrib_detail_id;
1949 END IF;
1950
1951 END LOOP;
1952
1953 END IF;
1954
1955 END IF;
1956
1957 IF l_pty_ids_tbl.COUNT > 0 THEN
1958
1959 csi_t_txn_parties_pvt.delete_txn_party_dtls(
1960 p_api_version => p_api_version,
1961 p_commit => p_commit,
1962 p_init_msg_list => p_init_msg_list,
1963 p_validation_level => p_validation_level,
1964 p_txn_party_ids_tbl => l_pty_ids_tbl,
1965 x_txn_pty_acct_ids_tbl => l_x_pty_acct_ids_tbl,
1966 x_return_status => l_return_status,
1967 x_msg_count => l_msg_count,
1968 x_msg_data => l_msg_data);
1969
1970 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1971 raise fnd_api.g_exc_error;
1972 END IF;
1973
1974 END IF;
1975
1976 IF l_pty_acct_ids_tbl.COUNT > 0 THEN
1977
1978 csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
1979 p_api_version => p_api_version,
1980 p_commit => p_commit,
1981 p_init_msg_list => p_init_msg_list,
1982 p_validation_level => p_validation_level,
1983 p_txn_pty_acct_ids_tbl => l_pty_acct_ids_tbl,
1984 x_return_status => l_return_status,
1985 x_msg_count => l_msg_count,
1986 x_msg_data => l_msg_data);
1987
1988 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1989 raise fnd_api.g_exc_error;
1990 END IF;
1991
1992 END IF;
1993
1994 IF l_iir_ids_tbl.COUNT > 0 THEN
1995
1996 csi_t_txn_rltnshps_pvt.delete_txn_ii_rltns_dtls(
1997 p_api_version => p_api_version,
1998 p_commit => p_commit,
1999 p_init_msg_list => p_init_msg_list,
2000 p_validation_level => p_validation_level,
2001 p_txn_ii_rltns_ids_tbl => l_iir_ids_tbl,
2002 x_return_status => l_return_status,
2003 x_msg_count => l_msg_count,
2004 x_msg_data => l_msg_data);
2005
2006 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2007 raise fnd_api.g_exc_error;
2008 END IF;
2009
2010 END IF;
2011
2012 IF l_oa_ids_tbl.COUNT > 0 THEN
2013
2014 csi_t_txn_ous_pvt.delete_txn_org_assgn_dtls(
2015 p_api_version => p_api_version,
2016 p_commit => p_commit,
2017 p_init_msg_list => p_init_msg_list,
2018 p_validation_level => p_validation_level,
2019 p_txn_org_assgn_ids_tbl => l_oa_ids_tbl,
2020 x_return_status => l_return_status,
2021 x_msg_count => l_msg_count,
2022 x_msg_data => l_msg_data);
2023
2024 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2025 raise fnd_api.g_exc_error;
2026 END IF;
2027
2028 END IF;
2029
2030 IF l_ea_ids_tbl.COUNT > 0 THEN
2031
2032 csi_t_txn_attribs_pvt.delete_txn_ext_attrib_dtls(
2033 p_api_version => p_api_version,
2034 p_commit => p_commit,
2035 p_init_msg_list => p_init_msg_list,
2036 p_validation_level => p_validation_level,
2037 p_txn_ext_attrib_ids_tbl => l_ea_ids_tbl,
2038 x_return_status => l_return_status,
2039 x_msg_count => l_msg_count,
2040 x_msg_data => l_msg_data);
2041
2042 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2043 raise fnd_api.g_exc_error;
2044 END IF;
2045
2046 END IF;
2047
2048
2049 -- Added the below IF for bug 2563265
2050 IF nvl(l_td_rec.serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
2051 IF nvl(l_td_rec.instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2052 Begin
2053 Select serial_number, instance_id, lot_number
2054 Into l_td_rec.serial_number, l_td_rec.instance_id,
2055 l_td_rec.lot_number
2056 From csi_item_instances
2057 Where inventory_item_id = l_td_rec.inventory_item_id
2058 and serial_number = l_td_rec.serial_number;
2059 Exception when others then
2060 null;-- do nothing. This is just to sync instance data on the tld rec...
2061 End;
2062 END IF;
2063
2064 ELSIF nvl(l_td_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
2065 Begin
2066 Select serial_number, instance_id, lot_number
2067 Into l_td_rec.serial_number, l_td_rec.instance_id,
2068 l_td_rec.lot_number
2069 From csi_item_instances
2070 Where instance_id = l_td_rec.instance_id;
2071 Exception when others then
2072 null;-- do nothing. This should not arise though.
2073 End;
2074 END IF;
2075
2076 begin
2077
2078 csi_t_gen_utility_pvt.dump_api_info(
2079 p_api_name => 'update_row',
2080 p_pkg_name => 'csi_t_txn_line_details_pkg');
2081
2082 csi_t_txn_line_details_pkg.update_row(
2083 p_txn_line_detail_id => l_td_rec.txn_line_detail_id,
2084 p_transaction_line_id => l_td_rec.transaction_line_id,
2085 p_sub_type_id => l_td_rec.sub_type_id,
2086 p_instance_exists_flag => l_td_rec.instance_exists_flag,
2087 p_source_transaction_flag => l_td_rec.source_transaction_flag,
2088 p_instance_id => l_td_rec.instance_id,
2089 p_csi_system_id => l_td_rec.csi_system_id,
2090 p_inventory_item_id => l_td_rec.inventory_item_id,
2091 p_inv_organization_id => l_td_rec.inv_organization_id,
2092 p_inventory_revision => l_td_rec.inventory_revision,
2093 p_instance_type_code => l_td_rec.instance_type_code,
2094 p_item_condition_id => l_td_rec.item_condition_id,
2095 p_quantity => l_td_rec.quantity,
2096 p_unit_of_measure => l_td_rec.unit_of_measure,
2097 p_qty_remaining => l_td_rec.qty_remaining,
2098 p_serial_number => l_td_rec.serial_number,
2099 p_lot_number => l_td_rec.lot_number,
2100 p_mfg_serial_number_flag => l_td_rec.mfg_serial_number_flag,
2101 p_location_type_code => l_td_rec.location_type_code,
2102 p_location_id => l_td_rec.location_id,
2103 p_installation_date => l_td_rec.installation_date,
2104 p_in_service_date => l_td_rec.in_service_date,
2105 p_external_reference => l_td_rec.external_reference,
2106 p_version_label => l_td_rec.version_label,
2107 p_transaction_system_id => l_td_rec.transaction_system_id,
2108 p_sellable_flag => l_td_rec.sellable_flag,
2109 p_return_by_date => l_td_rec.return_by_date,
2110 p_active_start_date => l_td_rec.active_start_date,
2111 p_active_end_date => l_td_rec.active_end_date,
2112 p_preserve_detail_flag => l_td_rec.preserve_detail_flag,
2113 p_changed_instance_id => l_td_rec.changed_instance_id,
2114 p_reference_source_id => l_td_rec.reference_source_id,
2115 p_reference_source_line_id => l_td_rec.reference_source_line_id,
2116 p_reference_source_date => l_td_rec.reference_source_date,
2117 p_csi_transaction_id => l_td_rec.csi_transaction_id,
2118 p_source_txn_line_detail_id => l_td_rec.source_txn_line_detail_id,
2119 p_inv_mtl_transaction_id => l_td_rec.inv_mtl_transaction_id,
2120 p_processing_status => l_td_rec.processing_status,
2121 p_error_code => l_td_rec.error_code,
2122 p_error_explanation => l_td_rec.error_explanation,
2123 -- Added for CZ Integration (Begin)
2124 p_config_inst_hdr_id => l_td_rec.config_inst_hdr_id ,
2125 p_config_inst_rev_num => l_td_rec.config_inst_rev_num ,
2126 p_config_inst_item_id => l_td_rec.config_inst_item_id ,
2127 p_config_inst_baseline_rev_num => l_td_rec.config_inst_baseline_rev_num ,
2128 p_target_commitment_date => l_td_rec.target_commitment_date ,
2129 p_instance_description => l_td_rec.instance_description ,
2130 -- Added for CZ Integration (End)
2131 -- Added for partner ordering
2132 p_install_location_type_code => l_td_rec.install_location_type_code,
2133 p_install_location_id => l_td_rec.install_location_id,
2134 -- Added for partner ordering
2135 p_cascade_owner_flag => l_td_rec.cascade_owner_flag,
2136 p_attribute1 => l_td_rec.attribute1,
2137 p_attribute2 => l_td_rec.attribute2,
2138 p_attribute3 => l_td_rec.attribute3,
2139 p_attribute4 => l_td_rec.attribute4,
2140 p_attribute5 => l_td_rec.attribute5,
2141 p_attribute6 => l_td_rec.attribute6,
2142 p_attribute7 => l_td_rec.attribute7,
2143 p_attribute8 => l_td_rec.attribute8,
2144 p_attribute9 => l_td_rec.attribute9,
2145 p_attribute10 => l_td_rec.attribute10,
2146 p_attribute11 => l_td_rec.attribute11,
2147 p_attribute12 => l_td_rec.attribute12,
2148 p_attribute13 => l_td_rec.attribute13,
2149 p_attribute14 => l_td_rec.attribute14,
2150 p_attribute15 => l_td_rec.attribute15,
2151 p_created_by => l_td_rec.created_by,
2152 p_creation_date => l_td_rec.creation_date,
2153 p_last_updated_by => l_td_rec.last_updated_by,
2154 p_last_update_date => l_td_rec.last_update_date,
2155 p_last_update_login => l_td_rec.last_update_login,
2156 p_object_version_number => l_td_rec.object_version_number,
2157 p_context => l_td_rec.context,
2158 p_parent_instance_id => l_td_rec.parent_instance_id,
2159 p_assc_txn_line_detail_id => l_td_rec.assc_txn_line_detail_id,
2160 p_overriding_csi_txn_id => l_td_rec.overriding_csi_txn_id,
2161 p_instance_status_id => l_td_rec.instance_status_id);
2162 exception
2163 when others then
2164 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2165 fnd_message.set_token('MESSAGE',
2166 'csi_t_txn_line_details_pkg.update_row Failed. '||substr(sqlerrm,1,200));
2167 fnd_msg_pub.add;
2168 raise fnd_api.g_exc_error;
2169 end;
2170
2171 END LOOP;
2172
2173 END LOOP;
2174
2175 -- call grp api for parties
2176
2177 IF px_txn_party_detail_tbl.COUNT > 0 THEN
2178
2179 debug('Found party records for processing.'||px_txn_party_detail_tbl.COUNT);
2180
2181 l_c_pty_ind := 0;
2182 l_u_pty_ind := 0;
2183 -- Added for self bug, Mass update , Acct tables are not build or passed below
2184 l_c_pa_ind := 0;
2185 l_u_pa_ind := 0;
2186
2187 l_contact_party_index := 'N' ;
2188 l_tmp_party_detail_tbl := px_txn_party_detail_tbl;
2189 FOR l_ind IN px_txn_party_detail_tbl.FIRST .. px_txn_party_detail_tbl.LAST
2190 LOOP
2191
2192 IF nvl(px_txn_party_detail_tbl(l_ind).txn_party_detail_id, fnd_api.g_miss_num) =
2193 fnd_api.g_miss_num
2194 THEN
2195 -- new attribute added in R12
2196 IF nvl(px_txn_party_detail_tbl(l_ind).txn_contact_party_index, fnd_api.g_miss_num)
2197 <> fnd_api.g_miss_num
2198 THEN
2199 l_contact_party_index := 'Y' ;
2200 END IF;
2201 IF px_txn_pty_acct_detail_tbl.count > 0 THEN
2202 FOR m_ind IN px_txn_pty_acct_detail_tbl.FIRST .. px_txn_pty_acct_detail_tbl.LAST
2203 LOOP
2204 IF ( (nvl(px_txn_pty_acct_detail_tbl(m_ind).txn_account_detail_id,fnd_api.g_miss_num)
2205 = fnd_api.g_miss_num) AND
2206 (nvl(px_txn_pty_acct_detail_tbl(m_ind).txn_party_detail_id,fnd_api.g_miss_num)
2207 = fnd_api.g_miss_num) AND
2208 ( px_txn_pty_acct_detail_tbl(m_ind).txn_party_details_index = l_ind )
2209 )
2210 THEN
2211 l_c_pty_acct_tbl(l_c_pa_ind ) := px_txn_pty_acct_detail_tbl(m_ind);
2212 l_c_pty_acct_tbl(l_c_pa_ind).txn_party_details_index := l_c_pty_ind;
2213 l_c_pa_ind := l_c_pa_ind + 1;
2214
2215 END IF;
2216 END LOOP; -- acct tbl loop
2217 END IF; --acct tbl.count
2218 -- Resetting the Transaction Party Contacts table
2219 l_tmp_party_detail_tbl := px_txn_party_detail_tbl;
2220
2221 FOR con_ind IN l_tmp_party_detail_tbl.FIRST .. l_tmp_party_detail_tbl.LAST
2222 LOOP
2223 IF nvl(l_tmp_party_detail_tbl(con_ind).txn_party_detail_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
2224 AND nvl(l_tmp_party_detail_tbl(con_ind).contact_party_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
2225 AND nvl(l_tmp_party_detail_tbl(con_ind).contact_flag, 'N') = 'Y' THEN
2226 IF nvl(px_txn_party_detail_tbl(l_ind).txn_contact_party_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num
2227 AND ( nvl(px_txn_party_detail_tbl(l_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char
2228 OR nvl(px_txn_party_detail_tbl(l_ind).contact_flag,'N') = 'N') THEN
2229 IF l_tmp_party_detail_tbl(con_ind).contact_party_id
2230 = px_txn_party_detail_tbl(l_ind).txn_contact_party_index THEN
2231
2232 l_tmp_party_detail_tbl(con_ind).contact_party_id := l_c_pty_ind;
2233 END IF;
2234 END IF;
2235 END IF;
2236 END LOOP;
2237
2238 px_txn_party_detail_tbl := l_tmp_party_detail_tbl;
2239
2240 IF nvl(px_txn_party_detail_tbl(l_ind).txn_contact_party_index,fnd_api.g_miss_num) <> fnd_api.g_miss_num
2241 AND ( nvl(px_txn_party_detail_tbl(l_ind).contact_flag, fnd_api.g_miss_char) = fnd_api.g_miss_char
2242 OR nvl(px_txn_party_detail_tbl(l_ind).contact_flag, 'N') = 'N')
2243 THEN
2244 px_txn_party_detail_tbl(l_ind).txn_contact_party_index := l_c_pty_ind;
2245 END IF;
2246
2247 l_tmp_party_detail_tbl := px_txn_party_detail_tbl;
2248 l_c_pty_tbl(l_c_pty_ind) := px_txn_party_detail_tbl(l_ind);
2249 l_c_pty_ind := l_c_pty_ind + 1;
2250 ELSE
2251 debug('PTY Record No.: '||l_ind||' marked for update.');
2252 l_u_pty_tbl(l_u_pty_ind) := px_txn_party_detail_tbl(l_ind);
2253 IF px_txn_pty_acct_detail_tbl.count > 0 THEN
2254 FOR n_ind IN px_txn_pty_acct_detail_tbl.FIRST .. px_txn_pty_acct_detail_tbl.LAST
2255 LOOP
2256 IF ( (nvl(px_txn_pty_acct_detail_tbl(n_ind).txn_party_detail_id,fnd_api.g_miss_num)
2257 <> fnd_api.g_miss_num)
2258 AND
2259 ( px_txn_pty_acct_detail_tbl(n_ind).txn_party_detail_id
2260 = l_u_pty_tbl(l_u_pty_ind).txn_party_detail_id )
2261 ) THEN
2262
2263 l_u_pty_acct_tbl(l_u_pa_ind ) := px_txn_pty_acct_detail_tbl(n_ind);
2264 l_u_pa_ind := l_u_pa_ind + 1;
2265 END IF;
2266 END LOOP; -- acct loop
2267 END IF; -- acct tbl.count
2268
2269 l_u_pty_ind := l_u_pty_ind + 1;
2270
2271 END IF; -- update/create pty
2272 END LOOP;
2273
2274 IF l_c_pty_tbl.COUNT > 0 THEN
2275
2276 csi_t_txn_parties_grp.create_txn_party_dtls(
2277 p_api_version => p_api_version,
2278 p_commit => p_commit,
2279 p_init_msg_list => p_init_msg_list,
2280 p_validation_level => p_validation_level,
2281 px_txn_party_detail_tbl => l_c_pty_tbl,
2282 px_txn_pty_acct_detail_tbl => l_c_pty_acct_tbl,
2283 x_return_status => l_return_status,
2284 x_msg_count => l_msg_count,
2285 x_msg_data => l_msg_data);
2286
2287 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2288 RAISE fnd_api.g_exc_error;
2289 END IF;
2290
2291 -- we now need this code below for those cases where it is a new contact for an existing
2292 --txn pty detail , since for the rest of it, this is taken care of in the Party GRP
2293 -- need to assign the entire set of passed pty records
2294
2295 l_tmp_party_dtl_tbl := l_tmp_party_detail_tbl;--px_txn_party_detail_tbl;
2296
2297 -- process the contact party id
2298 FOR cont_ind IN l_c_pty_tbl.FIRST .. l_c_pty_tbl.LAST
2299 LOOP
2300 IF nvl(l_c_pty_tbl(cont_ind).contact_party_id, fnd_api.g_miss_num) <>
2301 fnd_api.g_miss_num AND l_c_pty_tbl(cont_ind).contact_flag = 'Y'
2302 THEN
2303 IF nvl(l_contact_party_index, 'N') = 'Y' THEN
2304 l_contact_party_id := null;
2305 FOR p_ind IN l_tmp_party_dtl_tbl.FIRST .. l_tmp_party_dtl_tbl.LAST
2306 LOOP
2307 IF ( l_tmp_party_dtl_tbl(p_ind).txn_contact_party_index is not null
2308 AND l_tmp_party_dtl_tbl(p_ind).txn_contact_party_index <> fnd_api.g_miss_num )
2309 THEN
2310 --do nothing 'cause the creates are already handled in the Party Grp
2311 null;
2312 ELSIF l_tmp_party_dtl_tbl(p_ind).txn_party_detail_id = l_c_pty_tbl(cont_ind).contact_party_id
2313 AND ( nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char
2314 OR nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,'N') = 'N') THEN
2315 l_contact_party_id := l_tmp_party_dtl_tbl(p_ind).txn_party_detail_id;
2316 exit;
2317 END IF;
2318 END LOOP;
2319 ELSE
2320 l_contact_party_id := null;
2321 FOR p_ind IN l_tmp_party_dtl_tbl.FIRST .. l_tmp_party_dtl_tbl.LAST
2322 LOOP
2323 IF p_ind = l_c_pty_tbl(cont_ind).contact_party_id
2324 AND ( nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char
2325 OR nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,'N') = 'N' )
2326 THEN
2327 --do nothing 'cause the creates are already handled in the Party Grp
2328 null;
2329 ELSIF l_tmp_party_dtl_tbl(p_ind).txn_party_detail_id = l_c_pty_tbl(cont_ind).contact_party_id
2330 AND ( nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,fnd_api.g_miss_char) = fnd_api.g_miss_char
2331 OR nvl(l_tmp_party_dtl_tbl(p_ind).contact_flag,'N') = 'N' ) THEN
2332 l_contact_party_id := l_tmp_party_dtl_tbl(p_ind).txn_party_detail_id;
2333 exit;
2334 END IF;
2335 END LOOP;
2336 END IF;
2337
2338 IF l_contact_party_id is not null THEN
2339 update csi_t_party_details
2340 set contact_party_id = l_contact_party_id
2341 where txn_party_detail_id = l_c_pty_tbl(cont_ind).txn_party_detail_id;
2342 END IF;
2343 END IF;
2344 END LOOP;
2345 END IF;
2346
2347 IF l_u_pty_tbl.COUNT > 0 THEN
2348
2349 csi_t_txn_parties_grp.update_txn_party_dtls(
2350 p_api_version => p_api_version,
2351 p_commit => p_commit,
2352 p_init_msg_list => p_init_msg_list,
2353 p_validation_level => p_validation_level,
2354 p_txn_party_detail_tbl => l_u_pty_tbl,
2355 px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
2356 x_return_status => l_return_status,
2357 x_msg_count => l_msg_count,
2358 x_msg_data => l_msg_data);
2359
2360 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2361 RAISE fnd_api.g_exc_error;
2362 END IF;
2363 END IF;
2364
2365 END IF; -- pty tbl.count
2366
2367 -- call grp api for ii_relationships
2368 IF px_txn_ii_rltns_tbl.COUNT > 0 THEN
2369
2370 debug('Found ii relationships for processing.');
2371
2372 l_c_ii_ind := 0;
2373 l_u_ii_ind := 0;
2374
2375 FOR l_ind IN px_txn_ii_rltns_tbl.FIRST .. px_txn_ii_rltns_tbl.LAST
2376 LOOP
2377 IF nvl(px_txn_ii_rltns_tbl(l_ind).txn_relationship_id, fnd_api.g_miss_num) =
2378 fnd_api.g_miss_num
2379 THEN
2380 debug('RLTNS Record No.: '||l_ind||' marked for create.');
2381 l_c_ii_tbl(l_ind) := px_txn_ii_rltns_tbl(l_ind);
2382 l_c_ii_ind := l_c_ii_ind + 1;
2383
2384 ELSE
2385 debug('RLTNS Record No.: '||l_ind||' marked for update.');
2386 l_u_ii_tbl(l_ind) := px_txn_ii_rltns_tbl(l_ind);
2387 l_u_ii_ind := l_u_ii_ind + 1;
2388 END IF;
2389
2390 END LOOP;
2391
2392 IF l_c_ii_tbl.COUNT > 0 THEN
2393
2394 csi_t_txn_rltnshps_grp.create_txn_ii_rltns_dtls(
2395 p_api_version => p_api_version,
2396 p_commit => p_commit,
2397 p_init_msg_list => p_init_msg_list,
2398 p_validation_level => p_validation_level,
2399 px_txn_ii_rltns_tbl => l_c_ii_tbl,
2400 x_return_status => l_return_status,
2401 x_msg_count => l_msg_count,
2402 x_msg_data => l_msg_data);
2403
2404 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2405 RAISE fnd_api.g_exc_error;
2406 END IF;
2407
2408 END IF;
2409
2410 IF l_u_ii_tbl.COUNT > 0 THEN
2411
2412 csi_t_txn_rltnshps_grp.update_txn_ii_rltns_dtls (
2413 p_api_version => p_api_version,
2414 p_commit => p_commit,
2415 p_init_msg_list => p_init_msg_list,
2416 p_validation_level => p_validation_level,
2417 p_txn_ii_rltns_tbl => l_u_ii_tbl,
2418 x_return_status => l_return_status,
2419 x_msg_count => l_msg_count,
2420 x_msg_data => l_msg_data);
2421
2422 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2423 RAISE fnd_api.g_exc_error;
2424 END IF;
2425
2426 END IF;
2427
2428 END IF;
2429
2430 -- call grp api for org_assignments
2431 IF px_txn_org_assgn_tbl.COUNT > 0 THEN
2432
2433 debug('Found org assignments records for processing.');
2434 l_c_oa_ind := 0;
2435 l_u_oa_ind := 0;
2436
2437 FOR l_ind IN px_txn_org_assgn_tbl.FIRST .. px_txn_org_assgn_tbl.LAST
2438 LOOP
2439
2440 IF nvl(px_txn_org_assgn_tbl(l_ind).txn_operating_unit_id, fnd_api.g_miss_num) =
2441 fnd_api.g_miss_num
2442 THEN
2443 debug('ORG Record No.: '||l_ind||' marked for create.');
2444 l_c_oa_tbl(l_c_oa_ind) := px_txn_org_assgn_tbl(l_ind);
2445 l_c_oa_ind := l_c_oa_ind + 1;
2446
2447 ELSE
2448 debug('ORG Record No.: '||l_ind||' marked for update.');
2449 l_u_oa_tbl(l_u_oa_ind) := px_txn_org_assgn_tbl(l_ind);
2450 l_u_oa_ind := l_u_oa_ind + 1;
2451 END IF;
2452
2453 END LOOP;
2454
2455 IF l_c_oa_tbl.COUNT > 0 THEN
2456
2457 csi_t_txn_ous_grp.create_txn_org_assgn_dtls(
2458 p_api_version => p_api_version,
2459 p_commit => p_commit,
2460 p_init_msg_list => p_init_msg_list,
2461 p_validation_level => p_validation_level,
2462 px_txn_org_assgn_tbl => l_c_oa_tbl,
2463 x_return_status => l_return_status,
2464 x_msg_count => l_msg_count,
2465 x_msg_data => l_msg_data);
2466
2467 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2468 RAISE fnd_api.g_exc_error;
2469 END IF;
2470
2471 END IF;
2472
2473 IF l_u_oa_tbl.COUNT > 0 THEN
2474
2475 csi_t_txn_ous_grp.update_txn_org_assgn_dtls(
2476 p_api_version => p_api_version,
2477 p_commit => p_commit,
2478 p_init_msg_list => p_init_msg_list,
2479 p_validation_level => p_validation_level,
2480 p_txn_org_assgn_tbl => l_u_oa_tbl,
2481 x_return_status => l_return_status,
2482 x_msg_count => l_msg_count,
2483 x_msg_data => l_msg_data);
2484
2485 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2486 RAISE fnd_api.g_exc_error;
2487 END IF;
2488 END IF;
2489
2490 END IF;
2491
2492 -- call grp api for ext attribs
2493 IF px_txn_ext_attrib_vals_tbl.COUNT > 0 THEN
2494
2495 debug('Found extended attributes to be processed.');
2496 l_u_ea_ind := 0;
2497
2498 FOR l_ind IN px_txn_ext_attrib_vals_tbl.FIRST .. px_txn_ext_attrib_vals_tbl.LAST
2499 LOOP
2500
2501 IF nvl(px_txn_ext_attrib_vals_tbl(l_ind).txn_attrib_detail_id,fnd_api.g_miss_num)
2502 = fnd_api.g_miss_num
2503 THEN
2504
2505 debug('EAV Record No.: '||l_ind||' marked for create.');
2506
2507 csi_t_txn_attribs_pvt.create_txn_ext_attrib_dtls(
2508 p_api_version => p_api_version,
2509 p_commit => p_commit,
2510 p_init_msg_list => p_init_msg_list,
2511 p_validation_level => p_validation_level,
2512 p_txn_ext_attrib_vals_rec => px_txn_ext_attrib_vals_tbl(l_ind),
2513 x_return_status => l_return_status,
2514 x_msg_count => l_msg_count,
2515 x_msg_data => l_msg_data);
2516
2517 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2518 RAISE fnd_api.g_exc_error;
2519 END IF;
2520 ELSE
2521
2522 debug('EAV Record No.: '||l_ind||' marked for update.');
2523
2524 l_u_eav_tbl(l_u_ea_ind) := px_txn_ext_attrib_vals_tbl(l_ind);
2525 l_u_ea_ind := l_u_ea_ind + 1;
2526
2527 END IF;
2528
2529 END LOOP;
2530
2531 IF l_u_eav_tbl.COUNT > 0 THEN
2532
2533 csi_t_txn_attribs_pvt.update_txn_ext_attrib_dtls(
2534 p_api_version => p_api_version,
2535 p_commit => p_commit,
2536 p_init_msg_list => p_init_msg_list,
2537 p_validation_level => p_validation_level,
2538 p_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
2539 x_return_status => l_return_status,
2540 x_msg_count => l_msg_count,
2541 x_msg_data => l_msg_data);
2542
2543 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2544 RAISE fnd_api.g_exc_error;
2545 END IF;
2546
2547 END IF;
2548
2549 END IF;
2550
2551 END IF;
2552
2553 debug('Transaction details updated successfully.');
2554
2555 -- Standard check of p_commit.
2556 IF fnd_api.To_Boolean( p_commit ) THEN
2557 COMMIT WORK;
2558 END IF;
2559
2560 -- Standard call to get message count and if count is get message info.
2561 fnd_msg_pub.Count_And_Get(
2562 p_count => x_msg_count,
2563 p_data => x_msg_data);
2564
2565 EXCEPTION
2566 WHEN fnd_api.G_EXC_ERROR THEN
2567
2568 ROLLBACK TO update_txn_line_dtls;
2569 x_return_status := fnd_api.g_ret_sts_error ;
2570 fnd_msg_pub.Count_And_Get (
2571 p_count => x_msg_count,
2572 p_data => x_msg_data);
2573
2574 WHEN fnd_api.g_exc_unexpected_error THEN
2575
2576 ROLLBACK TO update_txn_line_dtls;
2577 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2578
2579 fnd_msg_pub.count_and_get(
2580 p_count => x_msg_count,
2581 p_data => x_msg_data);
2582
2583 WHEN OTHERS THEN
2584
2585 ROLLBACK TO Update_Txn_Line_Dtls;
2586 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2587
2588 IF fnd_msg_pub.Check_Msg_Level(
2589 p_message_level => fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
2590
2591 fnd_msg_pub.Add_Exc_Msg(
2592 p_pkg_name => G_PKG_NAME,
2593 p_procedure_name => l_api_name);
2594
2595 END IF;
2596
2597 fnd_msg_pub.Count_And_Get(
2598 p_count => x_msg_count,
2599 p_data => x_msg_data);
2600
2601 END update_txn_line_dtls;
2602
2603 PROCEDURE bind_query_variables(
2604 p_dtl_qry_cur_id in integer,
2605 p_txn_line_query_rec in csi_t_datastructures_grp.txn_line_query_rec,
2606 p_txn_line_detail_query_rec in csi_t_datastructures_grp.txn_line_detail_query_rec,
2607 x_return_status OUT NOCOPY varchar2)
2608 IS
2609 BEGIN
2610
2611 api_log('bind_query_variables');
2612
2613 x_return_status := fnd_api.g_ret_sts_success;
2614
2615 -- transaction lines variables bind
2616 IF nvl(p_txn_line_query_rec.transaction_line_id,fnd_api.g_miss_num) <>
2617 fnd_api.g_miss_num THEN
2618 dbms_sql.bind_variable(p_dtl_qry_cur_id,'transaction_line_id',
2619 p_txn_line_query_rec.transaction_line_id);
2620 END IF;
2621
2622 IF nvl(p_txn_line_query_rec.source_txn_header_id,fnd_api.g_miss_num) <>
2623 fnd_api.g_miss_num THEN
2624 dbms_sql.bind_variable(p_dtl_qry_cur_id,'source_txn_header_id',
2625 p_txn_line_query_rec.source_txn_header_id);
2626 END IF;
2627
2628 IF nvl(p_txn_line_query_rec.source_transaction_id,fnd_api.g_miss_num) <>
2629 fnd_api.g_miss_num THEN
2630 dbms_sql.bind_variable(p_dtl_qry_cur_id,'source_transaction_id',
2631 p_txn_line_query_rec.source_transaction_id);
2632 END IF;
2633
2634 IF nvl(p_txn_line_query_rec.source_transaction_table,fnd_api.g_miss_char) <>
2635 fnd_api.g_miss_char THEN
2636 dbms_sql.bind_variable(p_dtl_qry_cur_id,'source_transaction_table',
2637 p_txn_line_query_rec.source_transaction_table);
2638 END IF;
2639
2640 IF nvl(p_txn_line_query_rec.error_code,fnd_api.g_miss_char) <>
2641 fnd_api.g_miss_char THEN
2642 dbms_sql.bind_variable(p_dtl_qry_cur_id,'error_code',
2643 p_txn_line_query_rec.error_code);
2644 END IF;
2645
2646 IF nvl(p_txn_line_query_rec.processing_status,fnd_api.g_miss_char) <>
2647 fnd_api.g_miss_char THEN
2648 dbms_sql.bind_variable(p_dtl_qry_cur_id,'processing_status',
2649 p_txn_line_query_rec.processing_status);
2650 END IF;
2651
2652 IF nvl(p_txn_line_query_rec.config_session_hdr_id,fnd_api.g_miss_num) <>
2653 fnd_api.g_miss_num THEN
2654 dbms_sql.bind_variable(p_dtl_qry_cur_id,'config_session_hdr_id',
2655 p_txn_line_query_rec.config_session_hdr_id);
2656 END IF;
2657
2658 IF nvl(p_txn_line_query_rec.config_session_rev_num,fnd_api.g_miss_num) <>
2659 fnd_api.g_miss_num THEN
2660 dbms_sql.bind_variable(p_dtl_qry_cur_id,'config_session_rev_num',
2661 p_txn_line_query_rec.config_session_rev_num);
2662 END IF;
2663
2664 IF nvl(p_txn_line_query_rec.config_session_item_id,fnd_api.g_miss_num) <>
2665 fnd_api.g_miss_num THEN
2666 dbms_sql.bind_variable(p_dtl_qry_cur_id,'config_session_item_id',
2667 p_txn_line_query_rec.config_session_item_id);
2668 END IF;
2669
2670 -- txn_line details variables bind
2671
2672 IF nvl(p_txn_line_detail_query_rec.transaction_line_id, fnd_api.g_miss_num) <>
2673 fnd_api.g_miss_num
2674 THEN
2675 dbms_sql.bind_variable(p_dtl_qry_cur_id, 'dtl_transaction_line_id',
2676 p_txn_line_detail_query_rec.transaction_line_id);
2677 END IF;
2678
2679 IF nvl(p_txn_line_detail_query_rec.txn_line_detail_id, fnd_api.g_miss_num) <>
2680 fnd_api.g_miss_num
2681 THEN
2682 dbms_sql.bind_variable(p_dtl_qry_cur_id,'txn_line_detail_id',
2683 p_txn_line_detail_query_rec.txn_line_detail_id);
2684 END IF;
2685
2686 IF nvl(p_txn_line_detail_query_rec.sub_type_id,fnd_api.g_miss_num) <>
2687 fnd_api.g_miss_num THEN
2688 dbms_sql.bind_variable(p_dtl_qry_cur_id,'sub_type_id',
2689 p_txn_line_detail_query_rec.sub_type_id);
2690 END IF;
2691
2692 IF nvl(p_txn_line_detail_query_rec.instance_exists_flag, fnd_api.g_miss_char) <>
2693 fnd_api.g_miss_char
2694 THEN
2695 dbms_sql.bind_variable(p_dtl_qry_cur_id,'instance_exists_flag',
2696 p_txn_line_detail_query_rec.instance_exists_flag);
2697 END IF;
2698
2699 IF nvl(p_txn_line_detail_query_rec.instance_id, fnd_api.g_miss_num) <>
2700 fnd_api.g_miss_num
2701 THEN
2702 dbms_sql.bind_variable(p_dtl_qry_cur_id,'instance_id',
2703 p_txn_line_detail_query_rec.instance_id);
2704 END IF;
2705
2706 IF nvl(p_txn_line_detail_query_rec.csi_transaction_id, fnd_api.g_miss_num) <>
2707 fnd_api.g_miss_num
2708 THEN
2709 dbms_sql.bind_variable(p_dtl_qry_cur_id,'csi_transaction_id',
2710 p_txn_line_detail_query_rec.csi_transaction_id);
2711 END IF;
2712
2713 IF nvl(p_txn_line_detail_query_rec.source_transaction_flag, fnd_api.g_miss_char) <>
2714 fnd_api.g_miss_char
2715 THEN
2716 dbms_sql.bind_variable(p_dtl_qry_cur_id,'source_transaction_flag',
2717 p_txn_line_detail_query_rec.source_transaction_flag);
2718 END IF;
2719
2720 IF nvl(p_txn_line_detail_query_rec.csi_system_id, fnd_api.g_miss_num) <>
2721 fnd_api.g_miss_num
2722 THEN
2723 dbms_sql.bind_variable(p_dtl_qry_cur_id,'csi_system_id',
2724 p_txn_line_detail_query_rec.csi_system_id);
2725 END IF;
2726
2727 IF nvl(p_txn_line_detail_query_rec.transaction_system_id, fnd_api.g_miss_num) <>
2728 fnd_api.g_miss_num
2729 THEN
2730 dbms_sql.bind_variable(p_dtl_qry_cur_id,'transaction_system_id',
2731 p_txn_line_detail_query_rec.transaction_system_id);
2732 END IF;
2733
2734 IF nvl(p_txn_line_detail_query_rec.inventory_item_id, fnd_api.g_miss_num) <>
2735 fnd_api.g_miss_num
2736 THEN
2737 dbms_sql.bind_variable(p_dtl_qry_cur_id,'inventory_item_id',
2738 p_txn_line_detail_query_rec.inventory_item_id);
2739 END IF;
2740
2741 IF nvl(p_txn_line_detail_query_rec.inventory_revision, fnd_api.g_miss_char) <>
2742 fnd_api.g_miss_char
2743 THEN
2744 dbms_sql.bind_variable(p_dtl_qry_cur_id,'inventory_revision',
2745 p_txn_line_detail_query_rec.inventory_revision);
2746 END IF;
2747
2748 IF nvl(p_txn_line_detail_query_rec.inv_organization_id, fnd_api.g_miss_num) <>
2749 fnd_api.g_miss_num
2750 THEN
2751 dbms_sql.bind_variable(p_dtl_qry_cur_id,'inv_organization_id',
2752 p_txn_line_detail_query_rec.inv_organization_id);
2753 END IF;
2754
2755 IF nvl(p_txn_line_detail_query_rec.serial_number, fnd_api.g_miss_char) <>
2756 fnd_api.g_miss_char
2757 THEN
2758 dbms_sql.bind_variable(p_dtl_qry_cur_id,'serial_number',
2759 p_txn_line_detail_query_rec.serial_number);
2760 END IF;
2761
2762 IF nvl(p_txn_line_detail_query_rec.mfg_serial_number_flag, fnd_api.g_miss_char) <>
2763 fnd_api.g_miss_char
2764 THEN
2765 dbms_sql.bind_variable(p_dtl_qry_cur_id,'mfg_serial_number_flag',
2766 p_txn_line_detail_query_rec.mfg_serial_number_flag);
2767 END IF;
2768
2769 IF nvl(p_txn_line_detail_query_rec.lot_number, fnd_api.g_miss_char) <>
2770 fnd_api.g_miss_char
2771 THEN
2772 dbms_sql.bind_variable(p_dtl_qry_cur_id,'lot_number',
2773 p_txn_line_detail_query_rec.lot_number);
2774 END IF;
2775
2776 IF nvl(p_txn_line_detail_query_rec.location_type_code, fnd_api.g_miss_char) <>
2777 fnd_api.g_miss_char
2778 THEN
2779 dbms_sql.bind_variable(p_dtl_qry_cur_id,'location_type_code',
2780 p_txn_line_detail_query_rec.location_type_code);
2781 END IF;
2782
2783 IF nvl(p_txn_line_detail_query_rec.external_reference, fnd_api.g_miss_char) <>
2784 fnd_api.g_miss_char
2785 THEN
2786 dbms_sql.bind_variable(p_dtl_qry_cur_id,'external_reference',
2787 p_txn_line_detail_query_rec.external_reference);
2788 END IF;
2789
2790 IF nvl(p_txn_line_detail_query_rec.error_code, fnd_api.g_miss_char) <>
2791 fnd_api.g_miss_char
2792 THEN
2793 dbms_sql.bind_variable(p_dtl_qry_cur_id,'dtl_error_code',
2794 p_txn_line_detail_query_rec.error_code);
2795 END IF;
2796
2797 IF nvl(p_txn_line_detail_query_rec.error_explanation, fnd_api.g_miss_char) <>
2798 fnd_api.g_miss_char
2799 THEN
2800 dbms_sql.bind_variable(p_dtl_qry_cur_id,'dtl_error_explanation',
2801 p_txn_line_detail_query_rec.error_explanation);
2802 END IF;
2803
2804 IF nvl(p_txn_line_detail_query_rec.return_by_date, fnd_api.g_miss_date) <>
2805 fnd_api.g_miss_date
2806 THEN
2807 dbms_sql.bind_variable(p_dtl_qry_cur_id,'return_by_date',
2808 p_txn_line_detail_query_rec.return_by_date);
2809 END IF;
2810
2811 EXCEPTION
2812 WHEN others THEN
2813 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2814 fnd_message.set_token('MESSAGE',substr(sqlerrm, 1, 255));
2815 fnd_msg_pub.add;
2816 x_return_status := fnd_api.g_ret_sts_error;
2817
2818 END bind_query_variables;
2819
2820 PROCEDURE get_dtls_dynamic(
2821 p_txn_line_query_rec IN csi_t_datastructures_grp.txn_line_query_rec,
2822 p_txn_line_detail_query_rec IN csi_t_datastructures_grp.txn_line_detail_query_rec,
2823 p_dtl_select_stmt IN varchar2,
2824 x_line_dtls_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
2825 x_return_status OUT NOCOPY varchar2)
2826 IS
2827
2828 l_dtl_qry_cur_id integer;
2829 l_dtl_qry_cur_rows number;
2830 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
2831 l_processed_rows number := 0;
2832 l_ind binary_integer;
2833 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2834
2835 BEGIN
2836
2837 api_log('get_dtls_dynamic');
2838
2839 l_dtl_qry_cur_id := dbms_sql.open_cursor;
2840
2841 dbms_sql.parse(l_dtl_qry_cur_id, p_dtl_select_stmt , dbms_sql.native);
2842
2843 bind_query_variables(
2844 p_dtl_qry_cur_id => l_dtl_qry_cur_id,
2845 p_txn_line_query_rec => p_txn_line_query_rec,
2846 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
2847 x_return_status => l_return_status);
2848
2849 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2850 RAISE fnd_api.g_exc_error;
2851 END IF;
2852
2853 dbms_sql.define_column(l_dtl_qry_cur_id, 1, l_line_dtl_rec.txn_line_detail_id);
2854 dbms_sql.define_column(l_dtl_qry_cur_id, 2, l_line_dtl_rec.transaction_line_id);
2855 dbms_sql.define_column(l_dtl_qry_cur_id, 3, l_line_dtl_rec.sub_type_id);
2856 dbms_sql.define_column(l_dtl_qry_cur_id, 4, l_line_dtl_rec.instance_exists_flag, 1);
2857 dbms_sql.define_column(l_dtl_qry_cur_id, 5, l_line_dtl_rec.source_transaction_flag,1);
2858 dbms_sql.define_column(l_dtl_qry_cur_id, 6, l_line_dtl_rec.instance_id);
2859 dbms_sql.define_column(l_dtl_qry_cur_id, 7, l_line_dtl_rec.changed_instance_id);
2860 dbms_sql.define_column(l_dtl_qry_cur_id, 8, l_line_dtl_rec.csi_system_id);
2861 dbms_sql.define_column(l_dtl_qry_cur_id, 9, l_line_dtl_rec.inventory_item_id);
2862 dbms_sql.define_column(l_dtl_qry_cur_id, 10, l_line_dtl_rec.inventory_revision,3);
2863 dbms_sql.define_column(l_dtl_qry_cur_id, 11, l_line_dtl_rec.inv_organization_id);
2864 dbms_sql.define_column(l_dtl_qry_cur_id, 12, l_line_dtl_rec.item_condition_id);
2865 dbms_sql.define_column(l_dtl_qry_cur_id, 13, l_line_dtl_rec.instance_type_code, 30);
2866 dbms_sql.define_column(l_dtl_qry_cur_id, 14, l_line_dtl_rec.quantity);
2867 dbms_sql.define_column(l_dtl_qry_cur_id, 15, l_line_dtl_rec.unit_of_measure,3);
2868 dbms_sql.define_column(l_dtl_qry_cur_id, 16, l_line_dtl_rec.qty_remaining);
2869 dbms_sql.define_column(l_dtl_qry_cur_id, 17, l_line_dtl_rec.serial_number,30);
2870 dbms_sql.define_column(l_dtl_qry_cur_id, 18, l_line_dtl_rec.mfg_serial_number_flag,1);
2871 dbms_sql.define_column(l_dtl_qry_cur_id, 19, l_line_dtl_rec.lot_number,80);
2872 dbms_sql.define_column(l_dtl_qry_cur_id, 20, l_line_dtl_rec.location_type_code,30);
2873 dbms_sql.define_column(l_dtl_qry_cur_id, 21, l_line_dtl_rec.location_id);
2874 dbms_sql.define_column(l_dtl_qry_cur_id, 22, l_line_dtl_rec.installation_date);
2875 dbms_sql.define_column(l_dtl_qry_cur_id, 23, l_line_dtl_rec.in_service_date);
2876 dbms_sql.define_column(l_dtl_qry_cur_id, 24, l_line_dtl_rec.external_reference,30);
2877 dbms_sql.define_column(l_dtl_qry_cur_id, 25, l_line_dtl_rec.transaction_system_id);
2878 dbms_sql.define_column(l_dtl_qry_cur_id, 26, l_line_dtl_rec.sellable_flag, 1);
2879 dbms_sql.define_column(l_dtl_qry_cur_id, 27, l_line_dtl_rec.version_label,240);
2880 dbms_sql.define_column(l_dtl_qry_cur_id, 28, l_line_dtl_rec.return_by_date);
2881 dbms_sql.define_column(l_dtl_qry_cur_id, 29, l_line_dtl_rec.active_start_date);
2882 dbms_sql.define_column(l_dtl_qry_cur_id, 30, l_line_dtl_rec.active_end_date);
2883 dbms_sql.define_column(l_dtl_qry_cur_id, 31, l_line_dtl_rec.preserve_detail_flag,1);
2884 dbms_sql.define_column(l_dtl_qry_cur_id, 32, l_line_dtl_rec.reference_source_id);
2885 dbms_sql.define_column(l_dtl_qry_cur_id, 33, l_line_dtl_rec.reference_source_date);
2886 dbms_sql.define_column(l_dtl_qry_cur_id, 34, l_line_dtl_rec.csi_transaction_id);
2887 dbms_sql.define_column(l_dtl_qry_cur_id, 35, l_line_dtl_rec.processing_status,30);
2888 dbms_sql.define_column(l_dtl_qry_cur_id, 36, l_line_dtl_rec.error_code,240);
2889 dbms_sql.define_column(l_dtl_qry_cur_id, 37, l_line_dtl_rec.error_explanation,240);
2890 dbms_sql.define_column(l_dtl_qry_cur_id, 38, l_line_dtl_rec.context,30);
2891 dbms_sql.define_column(l_dtl_qry_cur_id, 39, l_line_dtl_rec.attribute1, 150);
2892 dbms_sql.define_column(l_dtl_qry_cur_id, 40, l_line_dtl_rec.attribute2, 150);
2893 dbms_sql.define_column(l_dtl_qry_cur_id, 41, l_line_dtl_rec.attribute3, 150);
2894 dbms_sql.define_column(l_dtl_qry_cur_id, 42, l_line_dtl_rec.attribute4, 150);
2895 dbms_sql.define_column(l_dtl_qry_cur_id, 43, l_line_dtl_rec.attribute5, 150);
2896 dbms_sql.define_column(l_dtl_qry_cur_id, 44, l_line_dtl_rec.attribute6, 150);
2897 dbms_sql.define_column(l_dtl_qry_cur_id, 45, l_line_dtl_rec.attribute7, 150);
2898 dbms_sql.define_column(l_dtl_qry_cur_id, 46, l_line_dtl_rec.attribute8, 150);
2899 dbms_sql.define_column(l_dtl_qry_cur_id, 47, l_line_dtl_rec.attribute9, 150);
2900 dbms_sql.define_column(l_dtl_qry_cur_id, 48, l_line_dtl_rec.attribute10, 150);
2901 dbms_sql.define_column(l_dtl_qry_cur_id, 49, l_line_dtl_rec.attribute11, 150);
2902 dbms_sql.define_column(l_dtl_qry_cur_id, 50, l_line_dtl_rec.attribute12, 150);
2903 dbms_sql.define_column(l_dtl_qry_cur_id, 51, l_line_dtl_rec.attribute13, 150);
2904 dbms_sql.define_column(l_dtl_qry_cur_id, 52, l_line_dtl_rec.attribute14, 150);
2905 dbms_sql.define_column(l_dtl_qry_cur_id, 53, l_line_dtl_rec.attribute15, 150);
2906 dbms_sql.define_column(l_dtl_qry_cur_id, 54, l_line_dtl_rec.object_version_number);
2907 dbms_sql.define_column(l_dtl_qry_cur_id, 55, l_line_dtl_rec.source_txn_line_detail_id);
2908 dbms_sql.define_column(l_dtl_qry_cur_id, 56, l_line_dtl_rec.inv_mtl_transaction_id);
2909 dbms_sql.define_column(l_dtl_qry_cur_id, 57, l_line_dtl_rec.config_inst_hdr_id);
2910 dbms_sql.define_column(l_dtl_qry_cur_id, 58, l_line_dtl_rec.config_inst_rev_num);
2911 dbms_sql.define_column(l_dtl_qry_cur_id, 59, l_line_dtl_rec.config_inst_item_id);
2912 dbms_sql.define_column(l_dtl_qry_cur_id, 60, l_line_dtl_rec.target_commitment_date);
2913 dbms_sql.define_column(l_dtl_qry_cur_id, 61 , l_line_dtl_rec.instance_description,240);
2914 dbms_sql.define_column(l_dtl_qry_cur_id, 62 , l_line_dtl_rec.config_inst_baseline_rev_num);
2915 dbms_sql.define_column(l_dtl_qry_cur_id, 63, l_line_dtl_rec.reference_source_line_id);
2916 dbms_sql.define_column(l_dtl_qry_cur_id, 64, l_line_dtl_rec.install_location_type_code,60);
2917 dbms_sql.define_column(l_dtl_qry_cur_id, 65, l_line_dtl_rec.install_location_id);
2918 dbms_sql.define_column(l_dtl_qry_cur_id, 66, l_line_dtl_rec.cascade_owner_flag,1);
2919 dbms_sql.define_column(l_dtl_qry_cur_id, 67, l_line_dtl_rec.parent_instance_id);
2920 dbms_sql.define_column(l_dtl_qry_cur_id, 68, l_line_dtl_rec.assc_txn_line_detail_id);
2921 dbms_sql.define_column(l_dtl_qry_cur_id, 69, l_line_dtl_rec.overriding_csi_txn_id);
2922 dbms_sql.define_column(l_dtl_qry_cur_id, 70, l_line_dtl_rec.instance_status_id);
2923
2924 l_ind := 0;
2925
2926 l_processed_rows := dbms_sql.execute(l_dtl_qry_cur_id);
2927 LOOP
2928 exit when dbms_sql.fetch_rows(l_dtl_qry_cur_id) = 0;
2929
2930 l_ind := l_ind + 1;
2931
2932 dbms_sql.column_value(l_dtl_qry_cur_id, 1, x_line_dtls_tbl(l_ind).txn_line_detail_id);
2933 dbms_sql.column_value(l_dtl_qry_cur_id, 2, x_line_dtls_tbl(l_ind).transaction_line_id);
2934 dbms_sql.column_value(l_dtl_qry_cur_id, 3, x_line_dtls_tbl(l_ind).sub_type_id);
2935 dbms_sql.column_value(l_dtl_qry_cur_id, 4, x_line_dtls_tbl(l_ind).instance_exists_flag);
2936 dbms_sql.column_value(l_dtl_qry_cur_id, 5, x_line_dtls_tbl(l_ind).source_transaction_flag);
2937 dbms_sql.column_value(l_dtl_qry_cur_id, 6, x_line_dtls_tbl(l_ind).instance_id);
2938 dbms_sql.column_value(l_dtl_qry_cur_id, 7, x_line_dtls_tbl(l_ind).changed_instance_id);
2939 dbms_sql.column_value(l_dtl_qry_cur_id, 8, x_line_dtls_tbl(l_ind).csi_system_id);
2940 dbms_sql.column_value(l_dtl_qry_cur_id, 9, x_line_dtls_tbl(l_ind).inventory_item_id);
2941 dbms_sql.column_value(l_dtl_qry_cur_id, 10, x_line_dtls_tbl(l_ind).inventory_revision);
2942 dbms_sql.column_value(l_dtl_qry_cur_id, 11, x_line_dtls_tbl(l_ind).inv_organization_id);
2943 dbms_sql.column_value(l_dtl_qry_cur_id, 12, x_line_dtls_tbl(l_ind).item_condition_id);
2944 dbms_sql.column_value(l_dtl_qry_cur_id, 13, x_line_dtls_tbl(l_ind).instance_type_code);
2945 dbms_sql.column_value(l_dtl_qry_cur_id, 14, x_line_dtls_tbl(l_ind).quantity);
2946 dbms_sql.column_value(l_dtl_qry_cur_id, 15, x_line_dtls_tbl(l_ind).unit_of_measure);
2947 dbms_sql.column_value(l_dtl_qry_cur_id, 16, x_line_dtls_tbl(l_ind).qty_remaining);
2948 dbms_sql.column_value(l_dtl_qry_cur_id, 17, x_line_dtls_tbl(l_ind).serial_number);
2949 dbms_sql.column_value(l_dtl_qry_cur_id, 18, x_line_dtls_tbl(l_ind).mfg_serial_number_flag);
2950 dbms_sql.column_value(l_dtl_qry_cur_id, 19, x_line_dtls_tbl(l_ind).lot_number);
2951 dbms_sql.column_value(l_dtl_qry_cur_id, 20, x_line_dtls_tbl(l_ind).location_type_code);
2952 dbms_sql.column_value(l_dtl_qry_cur_id, 21, x_line_dtls_tbl(l_ind).location_id);
2953 dbms_sql.column_value(l_dtl_qry_cur_id, 22, x_line_dtls_tbl(l_ind).installation_date);
2954 dbms_sql.column_value(l_dtl_qry_cur_id, 23, x_line_dtls_tbl(l_ind).in_service_date);
2955 dbms_sql.column_value(l_dtl_qry_cur_id, 24, x_line_dtls_tbl(l_ind).external_reference);
2956 dbms_sql.column_value(l_dtl_qry_cur_id, 25, x_line_dtls_tbl(l_ind).transaction_system_id);
2957 dbms_sql.column_value(l_dtl_qry_cur_id, 26, x_line_dtls_tbl(l_ind).sellable_flag);
2958 dbms_sql.column_value(l_dtl_qry_cur_id, 27, x_line_dtls_tbl(l_ind).version_label);
2959 dbms_sql.column_value(l_dtl_qry_cur_id, 28, x_line_dtls_tbl(l_ind).return_by_date);
2960 dbms_sql.column_value(l_dtl_qry_cur_id, 29, x_line_dtls_tbl(l_ind).active_start_date);
2961 dbms_sql.column_value(l_dtl_qry_cur_id, 30, x_line_dtls_tbl(l_ind).active_end_date);
2962 dbms_sql.column_value(l_dtl_qry_cur_id, 31, x_line_dtls_tbl(l_ind).preserve_detail_flag);
2963 dbms_sql.column_value(l_dtl_qry_cur_id, 32, x_line_dtls_tbl(l_ind).reference_source_id);
2964 dbms_sql.column_value(l_dtl_qry_cur_id, 33, x_line_dtls_tbl(l_ind).reference_source_date);
2965 dbms_sql.column_value(l_dtl_qry_cur_id, 34, x_line_dtls_tbl(l_ind).csi_transaction_id);
2966 dbms_sql.column_value(l_dtl_qry_cur_id, 35, x_line_dtls_tbl(l_ind).processing_status);
2967 dbms_sql.column_value(l_dtl_qry_cur_id, 36, x_line_dtls_tbl(l_ind).error_code);
2968 dbms_sql.column_value(l_dtl_qry_cur_id, 37, x_line_dtls_tbl(l_ind).error_explanation);
2969 dbms_sql.column_value(l_dtl_qry_cur_id, 38, x_line_dtls_tbl(l_ind).context);
2970 dbms_sql.column_value(l_dtl_qry_cur_id, 39, x_line_dtls_tbl(l_ind).attribute1);
2971 dbms_sql.column_value(l_dtl_qry_cur_id, 40, x_line_dtls_tbl(l_ind).attribute2);
2972 dbms_sql.column_value(l_dtl_qry_cur_id, 41, x_line_dtls_tbl(l_ind).attribute3);
2973 dbms_sql.column_value(l_dtl_qry_cur_id, 42, x_line_dtls_tbl(l_ind).attribute4);
2974 dbms_sql.column_value(l_dtl_qry_cur_id, 43, x_line_dtls_tbl(l_ind).attribute5);
2975 dbms_sql.column_value(l_dtl_qry_cur_id, 44, x_line_dtls_tbl(l_ind).attribute6);
2976 dbms_sql.column_value(l_dtl_qry_cur_id, 45, x_line_dtls_tbl(l_ind).attribute7);
2977 dbms_sql.column_value(l_dtl_qry_cur_id, 46, x_line_dtls_tbl(l_ind).attribute8);
2978 dbms_sql.column_value(l_dtl_qry_cur_id, 47, x_line_dtls_tbl(l_ind).attribute9);
2979 dbms_sql.column_value(l_dtl_qry_cur_id, 48, x_line_dtls_tbl(l_ind).attribute10);
2980 dbms_sql.column_value(l_dtl_qry_cur_id, 49, x_line_dtls_tbl(l_ind).attribute11);
2981 dbms_sql.column_value(l_dtl_qry_cur_id, 50, x_line_dtls_tbl(l_ind).attribute12);
2982 dbms_sql.column_value(l_dtl_qry_cur_id, 51, x_line_dtls_tbl(l_ind).attribute13);
2983 dbms_sql.column_value(l_dtl_qry_cur_id, 52, x_line_dtls_tbl(l_ind).attribute14);
2984 dbms_sql.column_value(l_dtl_qry_cur_id, 53, x_line_dtls_tbl(l_ind).attribute15);
2985 dbms_sql.column_value(l_dtl_qry_cur_id, 54, x_line_dtls_tbl(l_ind).object_version_number);
2986 dbms_sql.column_value(l_dtl_qry_cur_id, 55, x_line_dtls_tbl(l_ind).source_txn_line_detail_id);
2987 dbms_sql.column_value(l_dtl_qry_cur_id, 56, x_line_dtls_tbl(l_ind).inv_mtl_transaction_id);
2988 dbms_sql.column_value(l_dtl_qry_cur_id, 57, x_line_dtls_tbl(l_ind).config_inst_hdr_id);
2989 dbms_sql.column_value(l_dtl_qry_cur_id, 58, x_line_dtls_tbl(l_ind).config_inst_rev_num);
2990 dbms_sql.column_value(l_dtl_qry_cur_id, 59, x_line_dtls_tbl(l_ind).config_inst_item_id);
2991 dbms_sql.column_value(l_dtl_qry_cur_id, 60, x_line_dtls_tbl(l_ind).target_commitment_date);
2992 dbms_sql.column_value(l_dtl_qry_cur_id, 61, x_line_dtls_tbl(l_ind).instance_description);
2993 dbms_sql.column_value(l_dtl_qry_cur_id, 62 , l_line_dtl_rec.config_inst_baseline_rev_num);
2994 dbms_sql.column_value(l_dtl_qry_cur_id, 63, x_line_dtls_tbl(l_ind).reference_source_line_id);
2995 dbms_sql.column_value(l_dtl_qry_cur_id, 64, x_line_dtls_tbl(l_ind).install_location_type_code);
2996 dbms_sql.column_value(l_dtl_qry_cur_id, 65, x_line_dtls_tbl(l_ind).install_location_id);
2997 dbms_sql.column_value(l_dtl_qry_cur_id, 66, x_line_dtls_tbl(l_ind).cascade_owner_flag);
2998 dbms_sql.column_value(l_dtl_qry_cur_id, 67, x_line_dtls_tbl(l_ind).parent_instance_id);
2999 dbms_sql.column_value(l_dtl_qry_cur_id, 68, x_line_dtls_tbl(l_ind).assc_txn_line_detail_id);
3000 dbms_sql.column_value(l_dtl_qry_cur_id, 69, x_line_dtls_tbl(l_ind).overriding_csi_txn_id);
3001 dbms_sql.column_value(l_dtl_qry_cur_id, 70, x_line_dtls_tbl(l_ind).instance_status_id);
3002 END LOOP;
3003
3004 dbms_sql.close_cursor(l_dtl_qry_cur_id);
3005
3006 EXCEPTION
3007
3008 WHEN fnd_api.g_exc_error THEN
3009
3010 IF dbms_sql.is_open(l_dtl_qry_cur_id) THEN
3011 dbms_sql.close_cursor(l_dtl_qry_cur_id);
3012 END IF;
3013
3014 x_return_status := fnd_api.g_ret_sts_error;
3015
3016 WHEN others THEN
3017
3018 IF dbms_sql.is_open(l_dtl_qry_cur_id) THEN
3019 dbms_sql.close_cursor(l_dtl_qry_cur_id);
3020 END IF;
3021
3022 debug('Error : '|| SQLERRM);
3023 x_return_status := fnd_api.g_ret_sts_unexp_error;
3024
3025 END get_dtls_dynamic;
3026
3027 PROCEDURE build_line_dtls_select(
3028 p_txn_line_detail_query_rec in csi_t_datastructures_grp.txn_line_detail_query_rec,
3029 x_dtl_select_stmt OUT NOCOPY varchar2,
3030 x_dtl_where_clause OUT NOCOPY varchar2,
3031 x_return_status OUT NOCOPY varchar2)
3032 IS
3033
3034 l_select_stmt varchar2(32767);
3035 l_where_clause varchar2(32767);
3036
3037 BEGIN
3038
3039 api_log('build_line_dtls_select');
3040
3041 x_return_status := fnd_api.g_ret_sts_success;
3042
3043 l_select_stmt :=
3044 'select txn_line_detail_id, transaction_line_id, sub_type_id, instance_exists_flag, '||
3045 ' source_transaction_flag, instance_id, changed_instance_id, '||
3046 ' csi_system_id, inventory_item_id, inventory_revision, '||
3047 ' inv_organization_id, item_condition_id, instance_type_code, '||
3048 ' quantity, unit_of_measure, qty_remaining, serial_number, '||
3049 ' mfg_serial_number_flag, lot_number, location_type_code, location_id, '||
3050 ' installation_date, in_service_date, external_reference, '||
3051 ' transaction_system_id, sellable_flag, version_label, return_by_date, '||
3052 ' active_start_date, active_end_date, preserve_detail_flag, reference_source_id, '||
3053 ' reference_source_date, csi_transaction_id, processing_status, error_code, '||
3054 ' error_explanation, context, attribute1, attribute2, attribute3, attribute4, '||
3055 ' attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, '||
3056 ' attribute11, attribute12, attribute13, attribute14, attribute15, '||
3057 ' object_version_number, source_txn_line_detail_id, inv_mtl_transaction_id '||
3058 ' , config_inst_hdr_id, config_inst_rev_num , config_inst_item_id , target_commitment_date, '||
3059 ' instance_description , config_inst_baseline_rev_num , reference_source_line_id, '||
3060 ' install_location_type_code,install_location_id, '|| ' cascade_owner_flag, '||
3061 ' parent_instance_id, assc_txn_line_detail_id, '||
3062 ' overriding_csi_txn_id, instance_status_id '||
3063 ' from csi_t_txn_line_details ';
3064
3065 l_where_clause := null;
3066
3067 IF nvl(p_txn_line_detail_query_rec.transaction_line_id, fnd_api.g_miss_num) <>
3068 fnd_api.g_miss_num
3069 THEN
3070 l_where_clause := l_where_clause||' transaction_line_id = :dtl_transaction_line_id and ';
3071 END IF;
3072
3073 IF nvl(p_txn_line_detail_query_rec.txn_line_detail_id, fnd_api.g_miss_num) <>
3074 fnd_api.g_miss_num
3075 THEN
3076 l_where_clause := l_where_clause||' txn_line_detail_id = :txn_line_detail_id and ';
3077 END IF;
3078
3079 IF nvl(p_txn_line_detail_query_rec.sub_type_id,fnd_api.g_miss_num) <>
3080 fnd_api.g_miss_num
3081 THEN
3082 l_where_clause := l_where_clause||' sub_type_id = :sub_type_id and ';
3083 END IF;
3084
3085 IF nvl(p_txn_line_detail_query_rec.instance_exists_flag, Fnd_api.g_miss_char) <>
3086 fnd_api.g_miss_char
3087 THEN
3088 l_where_clause := l_where_clause||' instance_exists_flag = :instance_exists_flag and ';
3089 END IF;
3090
3091 IF nvl(p_txn_line_detail_query_rec.instance_id, fnd_api.g_miss_num) <>
3092 fnd_api.g_miss_num
3093 THEN
3094 l_where_clause := l_where_clause||' instance_id = :instance_id and ';
3095 END IF;
3096
3097 IF nvl(p_txn_line_detail_query_rec.csi_transaction_id, fnd_api.g_miss_num) <>
3098 fnd_api.g_miss_num
3099 THEN
3100 l_where_clause := l_where_clause||' csi_transaction_id = :csi_transaction_id and ';
3101 END IF;
3102
3103 IF nvl(p_txn_line_detail_query_rec.source_transaction_flag, fnd_api.g_miss_char) <>
3104 fnd_api.g_miss_char
3105 THEN
3106 l_where_clause := l_where_clause||' source_transaction_flag = :source_transaction_flag and ';
3107 END IF;
3108
3109 IF nvl(p_txn_line_detail_query_rec.csi_system_id, fnd_api.g_miss_num) <>
3110 fnd_api.g_miss_num
3111 THEN
3112 l_where_clause := l_where_clause||' csi_system_id = :csi_system_id and ';
3113 END IF;
3114
3115 IF nvl(p_txn_line_detail_query_rec.transaction_system_id, fnd_api.g_miss_num) <>
3116 fnd_api.g_miss_num
3117 THEN
3118 l_where_clause := l_where_clause||' transaction_system_id = :transaction_system_id and ';
3119 END IF;
3120
3121 IF nvl(p_txn_line_detail_query_rec.inventory_item_id, fnd_api.g_miss_num) <>
3122 fnd_api.g_miss_num
3123 THEN
3124 l_where_clause := l_where_clause||' inventory_item_id = :inventory_item_id and ';
3125 END IF;
3126
3127 IF nvl(p_txn_line_detail_query_rec.inventory_revision, fnd_api.g_miss_char) <>
3128 fnd_api.g_miss_char
3129 THEN
3130 l_where_clause := l_where_clause||' inventory_revision = :inventory_revision and ';
3131 END IF;
3132
3133 IF nvl(p_txn_line_detail_query_rec.inv_organization_id, fnd_api.g_miss_num) <>
3134 fnd_api.g_miss_num
3135 THEN
3136 l_where_clause := l_where_clause||' inv_organization_id = :inv_organization_id and ';
3137 END IF;
3138
3139 IF nvl(p_txn_line_detail_query_rec.serial_number, fnd_api.g_miss_char) <>
3140 fnd_api.g_miss_char
3141 THEN
3142 l_where_clause := l_where_clause||' serial_number = :serial_number and ';
3143 END IF;
3144
3145 IF nvl(p_txn_line_detail_query_rec.mfg_serial_number_flag, fnd_api.g_miss_char) <>
3146 fnd_api.g_miss_char
3147 THEN
3148 l_where_clause := l_where_clause||' mfg_serial_number_flag = :mfg_serial_number_flag and ';
3149 END IF;
3150
3151 IF nvl(p_txn_line_detail_query_rec.lot_number, fnd_api.g_miss_char) <>
3152 fnd_api.g_miss_char
3153 THEN
3154 l_where_clause := l_where_clause||' lot_number = :lot_number and ';
3155 END IF;
3156
3157 IF nvl(p_txn_line_detail_query_rec.location_type_code, fnd_api.g_miss_char) <>
3158 fnd_api.g_miss_char
3159 THEN
3160 l_where_clause := l_where_clause||' location_type_code = :location_type_code and ';
3161 END IF;
3162
3163 IF nvl(p_txn_line_detail_query_rec.external_reference, fnd_api.g_miss_char) <>
3164 fnd_api.g_miss_char
3165 THEN
3166 l_where_clause := l_where_clause||' external_reference = :external_reference and ';
3167 END IF;
3168
3169 IF nvl(p_txn_line_detail_query_rec.processing_status, fnd_api.g_miss_char) <>
3170 fnd_api.g_miss_char
3171 THEN
3172 IF p_txn_line_detail_query_rec.processing_status = 'UNPROCESSED' THEN
3173
3174 l_where_clause := l_where_clause||
3175 ' nvl(processing_status,''SUBMIT'') <> ''PROCESSED'' and ';
3176
3177 ELSE
3178 l_where_clause := l_where_clause||' processing_status = '''||
3179 nvl(p_txn_line_detail_query_rec.processing_status,'SUBMIT')||''' and ';
3180 END IF;
3181 END IF;
3182
3183 IF nvl(p_txn_line_detail_query_rec.error_code, fnd_api.g_miss_char) <>
3184 fnd_api.g_miss_char
3185 THEN
3186 l_where_clause := l_where_clause||' error_code = :dtl_error_code and ';
3187 END IF;
3188
3189 IF nvl(p_txn_line_detail_query_rec.error_explanation, fnd_api.g_miss_char) <>
3190 fnd_api.g_miss_char
3191 THEN
3192 l_where_clause := l_where_clause||' error_explanation = :dtl_error_explanation and ';
3193 END IF;
3194
3195 IF nvl(p_txn_line_detail_query_rec.return_by_date, fnd_api.g_miss_date) <>
3196 fnd_api.g_miss_date
3197 THEN
3198 l_where_clause := l_where_clause||' return_by_date = '''||
3199 p_txn_line_detail_query_rec.return_by_date||''' and ';
3200 END IF;
3201
3202 l_where_clause := substr(l_where_clause,1,(instr(l_where_clause, ' and', -1)-1));
3203
3204 x_dtl_select_stmt := l_select_stmt;
3205 x_dtl_where_clause := l_where_clause;
3206
3207 debug(l_where_clause);
3208
3209 END build_line_dtls_select;
3210
3211 PROCEDURE build_txn_lines_select(
3212 p_txn_line_query_rec IN csi_t_datastructures_grp.txn_line_query_rec,
3213 x_lines_select_stmt OUT NOCOPY varchar2,
3214 x_lines_restrict OUT NOCOPY varchar2,
3215 x_return_status OUT NOCOPY varchar2)
3216 IS
3217
3218 l_select_stmt varchar2(32767);
3219 l_where_clause varchar2(32767);
3220
3221 BEGIN
3222
3223 x_return_status := fnd_api.g_ret_sts_success;
3224
3225 api_log('build_txn_lines_select');
3226
3227 x_lines_restrict := 'N';
3228
3229 l_select_stmt :=
3230 ' transaction_line_id in (select transaction_line_id from csi_t_transaction_lines ';
3231
3232 l_where_clause := null;
3233
3234 IF nvl(p_txn_line_query_rec.transaction_line_id,fnd_api.g_miss_num) <>
3235 fnd_api.g_miss_num THEN
3236 l_where_clause := l_where_clause||' transaction_line_id = :transaction_line_id and ';
3237 END IF;
3238
3239 IF nvl(p_txn_line_query_rec.source_txn_header_id,fnd_api.g_miss_num) <>
3240 fnd_api.g_miss_num THEN
3241 l_where_clause := l_where_clause||' source_txn_header_id = :source_txn_header_id and ';
3242 END IF;
3243
3244
3245 IF nvl(p_txn_line_query_rec.source_transaction_id,fnd_api.g_miss_num) <>
3246 fnd_api.g_miss_num THEN
3247 l_where_clause := l_where_clause||' source_transaction_id = :source_transaction_id and ';
3248 END IF;
3249
3250 IF nvl(p_txn_line_query_rec.source_transaction_table,fnd_api.g_miss_char) <>
3251 fnd_api.g_miss_char THEN
3252 l_where_clause := l_where_clause||' source_transaction_table = :source_transaction_table and ';
3253 END IF;
3254
3255 IF nvl(p_txn_line_query_rec.error_code,fnd_api.g_miss_char) <>
3256 fnd_api.g_miss_char THEN
3257 l_where_clause := l_where_clause||' error_code = :error_code and ';
3258 END IF;
3259
3260 IF nvl(p_txn_line_query_rec.processing_status,fnd_api.g_miss_char) <>
3261 fnd_api.g_miss_char THEN
3262 l_where_clause := l_where_clause||' processing_status = :processing_status and ';
3263 END IF;
3264
3265 IF nvl(p_txn_line_query_rec.config_session_hdr_id,fnd_api.g_miss_num) <>
3266 fnd_api.g_miss_num THEN
3267 l_where_clause := l_where_clause||' config_session_hdr_id = :config_session_hdr_id and ';
3268 END IF;
3269
3270 IF nvl(p_txn_line_query_rec.config_session_rev_num,fnd_api.g_miss_num) <>
3271 fnd_api.g_miss_num THEN
3272 l_where_clause := l_where_clause||' config_session_rev_num = :config_session_rev_num and ';
3273 END IF;
3274
3275 IF nvl(p_txn_line_query_rec.config_session_item_id,fnd_api.g_miss_num) <>
3276 fnd_api.g_miss_num THEN
3277 l_where_clause := l_where_clause||' config_session_item_id = :config_session_item_id and ';
3278 END IF;
3279
3280 IF l_where_clause is not null then
3281 x_lines_restrict := 'Y';
3282
3283 END IF;
3284
3285 l_where_clause := ' where '||substr(l_where_clause,1,(instr(l_where_clause,' and',-1) -1));
3286 debug(l_where_clause);
3287
3288 l_select_stmt := l_select_stmt||l_where_clause||')';
3289
3290 x_lines_select_stmt := l_select_stmt;
3291
3292 END build_txn_lines_select;
3293
3294
3295 PROCEDURE get_txn_line_dtls(
3296 p_txn_line_query_rec IN csi_t_datastructures_grp.txn_line_query_rec,
3297 p_txn_line_detail_query_rec IN csi_t_datastructures_grp.txn_line_detail_query_rec,
3298 x_txn_line_dtl_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
3299 x_return_status OUT NOCOPY varchar2)
3300 IS
3301
3302 l_dtl_select_stmt varchar2(32767);
3303 l_dtl_where_clause varchar2(32767);
3304 l_lines_restrict_clause varchar2(32767);
3305 l_lines_restrict varchar2(1);
3306 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
3307
3308 BEGIN
3309
3310 x_return_status := fnd_api.g_ret_sts_success;
3311
3312 api_log('get_txn_line_dtls');
3313
3314 -- apply txn_line_detail filter first and then the txn_ line_ filter
3315
3316 build_line_dtls_select(
3317 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
3318 x_dtl_select_stmt => l_dtl_select_stmt,
3319 x_dtl_where_clause => l_dtl_where_clause,
3320 x_return_status => l_return_status);
3321
3322 build_txn_lines_select(
3323 p_txn_line_query_rec => p_txn_line_query_rec,
3324 x_lines_select_stmt => l_lines_restrict_clause,
3325 x_lines_restrict => l_lines_restrict,
3326 x_return_status => l_return_status);
3327
3328
3329 IF l_dtl_where_clause is not null then
3330
3331 l_dtl_select_stmt := l_dtl_select_stmt||' where '||l_dtl_where_clause;
3332
3333 IF l_lines_restrict = 'Y' THEN
3334 l_dtl_select_stmt := l_dtl_select_stmt||' and '||l_lines_restrict_clause;
3335 END IF;
3336
3337 ELSE
3338 IF l_lines_restrict = 'Y' THEN
3339 l_dtl_select_stmt := l_dtl_select_stmt||' where '||l_lines_restrict_clause;
3340 END IF;
3341 END IF;
3342
3343 get_dtls_dynamic(
3344 p_txn_line_query_rec => p_txn_line_query_rec,
3345 p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
3346 p_dtl_select_stmt => l_dtl_select_stmt,
3347 x_line_dtls_tbl => x_txn_line_dtl_tbl,
3348 x_return_status => l_return_status);
3349
3350 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3351 raise fnd_api.g_exc_error;
3352 END IF;
3353
3354 debug('get_txn_line_dtls successful.');
3355
3356 EXCEPTION
3357 WHEN fnd_api.g_exc_error THEN
3358 x_return_status := fnd_api.g_ret_sts_error;
3359 END get_txn_line_dtls;
3360
3361 PROCEDURE delete_txn_line_dtls(
3362 p_api_version IN NUMBER,
3363 p_commit IN VARCHAR2 := fnd_api.g_false,
3364 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3365 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3366 p_txn_line_detail_ids_tbl IN csi_t_datastructures_grp.txn_line_detail_ids_tbl,
3367 x_return_status OUT NOCOPY VARCHAR2,
3368 x_msg_count OUT NOCOPY NUMBER,
3369 x_msg_data OUT NOCOPY VARCHAR2)
3370 IS
3371
3372 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_line_dtls';
3373 l_api_version CONSTANT NUMBER := 1.0;
3374 l_debug_level NUMBER;
3375
3376
3377 BEGIN
3378
3379 -- Standard Start of API savepoint
3380 SAVEPOINT delete_txn_line_dtls;
3381
3382 -- Initialize message list if p_init_msg_list is set to TRUE.
3383 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
3384 fnd_msg_pub.initialize;
3385 END IF;
3386
3387 -- Initialize API return status to success
3388 x_return_status := fnd_api.G_RET_STS_SUCCESS;
3389
3390 -- Standard call to check for call compatibility.
3391 IF NOT
3392
3393 fnd_api.Compatible_API_Call (
3394 p_current_version_number => l_api_version,
3395 p_caller_version_number => p_api_version,
3396 p_api_name => l_api_name,
3397 p_pkg_name => G_PKG_NAME) THEN
3398
3399 RAISE fnd_api.g_exc_unexpected_error;
3400
3401 END IF;
3402
3403 -- debug messages
3404 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
3405
3406 csi_t_gen_utility_pvt.dump_api_info(
3407 p_pkg_name => g_pkg_name,
3408 p_api_name => l_api_name);
3409
3410 debug(p_api_version||'-'||p_commit||'-'||p_validation_level||'-'||p_init_msg_list);
3411
3412 -- Main API code
3413 IF p_txn_line_detail_ids_tbl.COUNT > 0 THEN
3414 FOR l_ind in p_txn_line_detail_ids_tbl.FIRST..
3415 p_txn_line_detail_ids_tbl.LAST
3416 LOOP
3417
3418 csi_t_txn_line_details_pkg.delete_row(
3419 p_txn_line_detail_id => p_txn_line_detail_ids_tbl(l_ind).
3420 txn_line_detail_id);
3421
3422 END LOOP;
3423
3424 END IF;
3425
3426
3427 -- Standard check of p_commit.
3428 IF fnd_api.To_Boolean( p_commit ) THEN
3429 COMMIT WORK;
3430 END IF;
3431
3432 -- Standard call to get message count and if count is get message info.
3433 fnd_msg_pub.Count_And_Get(
3434 p_count => x_msg_count,
3435 p_data => x_msg_data);
3436
3437 EXCEPTION
3438 WHEN fnd_api.G_EXC_ERROR THEN
3439
3440 ROLLBACK TO delete_txn_line_dtls;
3441 x_return_status := fnd_api.g_ret_sts_error ;
3442 fnd_msg_pub.Count_And_Get (
3443 p_count => x_msg_count,
3444 p_data => x_msg_data);
3445
3446 WHEN fnd_api.g_exc_unexpected_error THEN
3447
3448 ROLLBACK TO delete_txn_line_dtls;
3449 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
3450
3451 fnd_msg_pub.Count_And_Get(
3452 p_count => x_msg_count,
3453 p_data => x_msg_data);
3454
3455 WHEN others THEN
3456
3457 rollback to delete_txn_line_dtls;
3458 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3459
3460 IF fnd_msg_pub.check_msg_level(
3461 p_message_level => fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3462
3463 fnd_msg_pub.add_exc_msg(
3464 p_pkg_name => g_pkg_name,
3465 p_procedure_name => l_api_name);
3466
3467 END IF;
3468
3469 fnd_msg_pub.Count_And_Get(
3470 p_count => x_msg_count,
3471 p_data => x_msg_data);
3472
3473 END delete_txn_line_dtls;
3474
3475 PROCEDURE update_txn_line(
3476 p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
3477 x_return_status OUT NOCOPY varchar2)
3478 IS
3479
3480
3481 CURSOR txn_line_cur (p_txn_line_id in number)is
3482 SELECT *
3483 FROM csi_t_transaction_lines
3484 WHERE transaction_line_id = p_txn_line_id;
3485
3486 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
3487 l_transaction_line_rec csi_t_datastructures_grp.txn_line_rec;
3488 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
3489 l_api_name varchar2(30) := 'update_txn_line';
3490 l_debug_level number;
3491
3492 BEGIN
3493
3494 x_return_status := fnd_api.g_ret_sts_success;
3495
3496 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
3497
3498 csi_t_gen_utility_pvt.dump_api_info(
3499 p_api_name => l_api_name,
3500 p_pkg_name => g_pkg_name);
3501
3502 IF l_debug_level > 1 then
3503 csi_t_gen_utility_pvt.dump_txn_line_rec(
3504 p_txn_line_rec => p_txn_line_rec);
3505 END IF;
3506
3507 -- adding this for transparency of transaction line id to callers.
3508
3509 l_txn_line_rec.transaction_line_id := p_txn_line_rec.transaction_line_id;
3510
3511 IF nvl(l_txn_line_rec.transaction_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3512
3513 IF nvl(p_txn_line_rec.source_transaction_table, fnd_api.g_miss_char) = fnd_api.g_miss_char
3514 OR
3515 nvl(p_txn_line_rec.source_transaction_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3516
3517 fnd_message.set_name('CSI','CSI_TXN_SRC_INFO_MISSING');
3518 fnd_message.set_token('SRC_NAME',p_txn_line_rec.source_transaction_table);
3519 fnd_message.set_token('SRC_ID',p_txn_line_rec.source_transaction_id);
3520 fnd_msg_pub.add;
3521 RAISE fnd_api.g_exc_error;
3522
3523 ELSE
3524
3525 BEGIN
3526
3527 SELECT transaction_line_id
3528 INTO l_txn_line_rec.transaction_line_id
3529 FROM csi_t_transaction_lines
3530 WHERE source_transaction_table = p_txn_line_rec.source_transaction_table
3531 AND source_transaction_id = p_txn_line_rec.source_transaction_id;
3532
3533 EXCEPTION
3534 WHEN no_data_found THEN
3535
3536 fnd_message.set_name('CSI','CSI_TXN_SOURCE_ID_INVALID');
3537 fnd_message.set_token('SRC_LINE_ID',p_txn_line_rec.source_transaction_id);
3538 fnd_message.set_token('SRC_NAME',p_txn_line_rec.source_transaction_table);
3539 fnd_msg_pub.add;
3540 RAISE fnd_api.g_exc_error;
3541 END;
3542
3543 END IF;
3544
3545 END IF;
3546
3547 csi_t_vldn_routines_pvt.check_reqd_param(
3548 p_value => l_txn_line_rec.transaction_line_id,
3549 p_param_name => 'p_txn_line_rec.transaction_line_id',
3550 p_api_name => 'update_txn_line_dtls');
3551
3552 -- validate txn_line_id
3553 csi_t_vldn_routines_pvt.validate_transaction_line_id(
3554 p_transaction_line_id => l_txn_line_rec.transaction_line_id,
3555 x_transaction_line_rec => l_transaction_line_rec, -- changed for Mass Update R12
3556 x_return_status => l_return_status);
3557
3558 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3559 fnd_message.set_name('CSI','CSI_TXN_LINE_ID_INVALID');
3560 fnd_message.set_token('TXN_LINE_ID', l_txn_line_rec.transaction_line_id);
3561 fnd_msg_pub.add;
3562 raise fnd_api.g_exc_error;
3563 END IF;
3564
3565 IF ( nvl(p_txn_line_rec.config_session_hdr_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
3566 OR nvl(p_txn_line_rec.config_session_rev_num, fnd_api.g_miss_num) <> fnd_api.g_miss_num
3567 OR nvl(p_txn_line_rec.config_session_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num )
3568 THEN
3569
3570 csi_t_gen_utility_pvt.add('Validating against CZ view ');
3571
3572 csi_t_vldn_routines_pvt.check_cz_session_keys
3573 (p_config_session_hdr_id => p_txn_line_rec.config_session_hdr_id,
3574 p_config_session_rev_num => p_txn_line_rec.config_session_rev_num,
3575 p_config_session_item_id => p_txn_line_rec.config_session_item_id,
3576 x_return_status => l_return_status);
3577
3578 IF l_return_status <> fnd_api.g_ret_sts_success
3579 THEN
3580 RAISE FND_API.g_exc_error;
3581 END IF;
3582
3583 END IF;
3584
3585 FOR l_cur_rec in txn_line_cur (l_txn_line_rec.transaction_line_id)
3586 LOOP
3587
3588 l_txn_line_rec.transaction_line_id := l_cur_rec.transaction_line_id;
3589
3590 l_txn_line_rec.source_transaction_type_id := l_cur_rec.source_transaction_type_id;
3591 l_txn_line_rec.source_transaction_table := l_cur_rec.source_transaction_table;
3592 l_txn_line_rec.source_txn_header_id := l_cur_rec.source_txn_header_id;
3593 l_txn_line_rec.source_transaction_id := l_cur_rec.source_transaction_id;
3594 l_txn_line_rec.config_session_hdr_id := l_cur_rec.config_session_hdr_id;
3595 l_txn_line_rec.config_session_rev_num := l_cur_rec.config_session_rev_num;
3596 l_txn_line_rec.config_session_item_id := l_cur_rec.config_session_item_id;
3597 l_txn_line_rec.config_valid_status := l_cur_rec.config_valid_status;
3598 l_txn_line_rec.source_transaction_status := l_cur_rec.source_transaction_status;
3599 l_txn_line_rec.error_code := p_txn_line_rec.error_code;
3600 l_txn_line_rec.error_explanation := p_txn_line_rec.error_explanation;
3601 l_txn_line_rec.processing_status := p_txn_line_rec.processing_status;
3602 l_txn_line_rec.context := p_txn_line_rec.context;
3603 l_txn_line_rec.attribute1 := p_txn_line_rec.attribute1;
3604 l_txn_line_rec.attribute2 := p_txn_line_rec.attribute2;
3605 l_txn_line_rec.attribute3 := p_txn_line_rec.attribute3;
3606 l_txn_line_rec.attribute4 := p_txn_line_rec.attribute4;
3607 l_txn_line_rec.attribute5 := p_txn_line_rec.attribute5;
3608 l_txn_line_rec.attribute6 := p_txn_line_rec.attribute6;
3609 l_txn_line_rec.attribute7 := p_txn_line_rec.attribute7;
3610 l_txn_line_rec.attribute8 := p_txn_line_rec.attribute8;
3611 l_txn_line_rec.attribute9 := p_txn_line_rec.attribute9;
3612 l_txn_line_rec.attribute10 := p_txn_line_rec.attribute10;
3613 l_txn_line_rec.attribute11 := p_txn_line_rec.attribute11;
3614 l_txn_line_rec.attribute12 := p_txn_line_rec.attribute12;
3615 l_txn_line_rec.attribute13 := p_txn_line_rec.attribute13;
3616 l_txn_line_rec.attribute14 := p_txn_line_rec.attribute14;
3617 l_txn_line_rec.attribute15 := p_txn_line_rec.attribute15;
3618 l_txn_line_rec.object_version_number := p_txn_line_rec.object_version_number;
3619
3620 csi_t_gen_utility_pvt.dump_api_info(
3621 p_api_name => 'update_row',
3622 p_pkg_name => 'csi_t_transaction_lines_pkg');
3623
3624 csi_t_transaction_lines_pkg.update_row(
3625 p_transaction_line_id => l_txn_line_rec.transaction_line_id,
3626 p_source_transaction_type_id => l_txn_line_rec.source_transaction_type_id,
3627 p_source_transaction_table => l_txn_line_rec.source_transaction_table,
3628 p_source_txn_header_id => l_txn_line_rec.source_txn_header_id,
3629 p_source_transaction_id => l_txn_line_rec.source_transaction_id,
3630 p_error_code => l_txn_line_rec.error_code,
3631 p_error_explanation => l_txn_line_rec.error_explanation,
3632 -- Added for CZ Integration (Begin)
3633 p_config_session_hdr_id => l_txn_line_rec.config_session_hdr_id ,
3634 p_config_session_rev_num => l_txn_line_rec.config_session_rev_num ,
3635 p_config_session_item_id => l_txn_line_rec.config_session_item_id ,
3636 p_config_valid_status => l_txn_line_rec.config_valid_status ,
3637 p_source_transaction_status => l_txn_line_rec.source_transaction_status ,
3638 -- Added for CZ Integration (End)
3639 p_processing_status => l_txn_line_rec.processing_status,
3640 p_attribute1 => l_txn_line_rec.attribute1,
3641 p_attribute2 => l_txn_line_rec.attribute2,
3642 p_attribute3 => l_txn_line_rec.attribute3,
3643 p_attribute4 => l_txn_line_rec.attribute4,
3644 p_attribute5 => l_txn_line_rec.attribute5,
3645 p_attribute6 => l_txn_line_rec.attribute6,
3646 p_attribute7 => l_txn_line_rec.attribute7,
3647 p_attribute8 => l_txn_line_rec.attribute8,
3648 p_attribute9 => l_txn_line_rec.attribute9,
3649 p_attribute10 => l_txn_line_rec.attribute10,
3650 p_attribute11 => l_txn_line_rec.attribute11,
3651 p_attribute12 => l_txn_line_rec.attribute12,
3652 p_attribute13 => l_txn_line_rec.attribute13,
3653 p_attribute14 => l_txn_line_rec.attribute14,
3654 p_attribute15 => l_txn_line_rec.attribute15,
3655 p_created_by => l_cur_rec.created_by,
3656 p_creation_date => l_cur_rec.creation_date,
3657 p_last_updated_by => fnd_global.user_id,
3658 p_last_update_date => sysdate,
3659 p_last_update_login => fnd_global.login_id,
3660 p_object_version_number => l_txn_line_rec.object_version_number,
3661 p_context => l_txn_line_rec.context);
3662
3663 END LOOP;
3664
3665 EXCEPTION
3666 WHEN fnd_api.g_exc_error THEN
3667 x_return_status := fnd_api.g_ret_sts_error;
3668 END update_txn_line;
3669
3670 END csi_t_txn_line_dtls_pvt;