[Home] [Help]
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,
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,
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,
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
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:
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
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
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(
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,
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(
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,
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);
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;
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.');
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:
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:
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(
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,
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');
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;
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 .');
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:
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
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
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:
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 .');
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:
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:
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 .');
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:
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:
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
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
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:
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:
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:
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(
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;
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:
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:
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(
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;
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
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(
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:
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:
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),
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:
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
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;
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;
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(
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',
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:
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:
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;
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:
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
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:
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');
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
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
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
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
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,
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
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
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(
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,
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:
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:
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';
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;
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 :=
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;
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;
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
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)
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;
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 :=
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;
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 :=
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 :=
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;
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 (
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;
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:
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);
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:
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,
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,
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,
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;
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
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:
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
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;
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:
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:
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;
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
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:
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 ');
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
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));
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
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,
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);
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:
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
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
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';
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';
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';
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',
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);
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;
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;
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:
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:
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
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:
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:
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:
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:
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:
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:
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:
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:
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:
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',
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:
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
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:
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:
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:
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:
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;
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(
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 */
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(
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',
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:
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:
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(
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,
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;
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:
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;
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;
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 :=
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;
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:
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:
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;
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:
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:
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
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;
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
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
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;
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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,
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
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
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:
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)
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
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;
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
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 )
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: )
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
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);
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
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
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
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:
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;
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;
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 )
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
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
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
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;
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
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
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;
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;
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;
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:
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
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);
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;
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;
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:
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;
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:
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);
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;
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;
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:
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:
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;
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.');
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:
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:
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.');
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;
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:
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.
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 (
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:
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:
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:
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
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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);
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;
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);
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;
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;
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:
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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);
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;
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(
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');
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);
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);
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;
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(
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, '||
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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;
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:
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:
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||
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;
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:
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;
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:
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 ';
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;
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';
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:
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) <>
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:
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:
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:
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) <>
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:
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) <>
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:
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) <>
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:
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) <>
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:
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) <>
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:
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) <>
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:
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
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;
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
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.');
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:
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(
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,
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,
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,
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)
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
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:
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
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
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.
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 (
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:
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:
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:
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
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(
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
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');
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);
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
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:
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;
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
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:
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 ');
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:
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:
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:
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;
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;