[Home] [Help]
112: px_inst_pa_rec IN OUT NOCOPY inst_pa_rec,
113: x_sub_type_rec OUT NOCOPY csi_txn_sub_types%rowtype,
114: x_return_status OUT NOCOPY varchar2)
115: IS
116: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
117: BEGIN
118: api_log('get_instance_pa_dtls');
119: x_return_status := fnd_api.g_ret_sts_success;
120: BEGIN
115: IS
116: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
117: BEGIN
118: api_log('get_instance_pa_dtls');
119: x_return_status := fnd_api.g_ret_sts_success;
120: BEGIN
121: SELECT party_id ,
122: instance_party_id,
123: object_version_number
144: px_inst_pa_rec.acct_rltnshp_code := 'OWNER';
145: EXCEPTION
146: WHEN no_data_found THEN
147: --## to seed some error message appropriately
148: l_return_status := fnd_api.g_ret_sts_error;
149: RAISE fnd_api.g_exc_error;
150: END;
151:
152: get_sub_type_rec(
145: EXCEPTION
146: WHEN no_data_found THEN
147: --## to seed some error message appropriately
148: l_return_status := fnd_api.g_ret_sts_error;
149: RAISE fnd_api.g_exc_error;
150: END;
151:
152: get_sub_type_rec(
153: p_transaction_type_id => p_transaction_type_id,
154: p_sub_type_id => p_sub_type_id,
155: x_sub_type_rec => x_sub_type_rec,
156: x_return_status => l_return_status);
157:
158: IF l_return_status <> fnd_api.g_ret_sts_success THEN
159: raise fnd_api.g_exc_error;
160: END IF;
161:
162: debug(' Instance ID : '||px_inst_pa_rec.instance_id);
155: x_sub_type_rec => x_sub_type_rec,
156: x_return_status => l_return_status);
157:
158: IF l_return_status <> fnd_api.g_ret_sts_success THEN
159: raise fnd_api.g_exc_error;
160: END IF;
161:
162: debug(' Instance ID : '||px_inst_pa_rec.instance_id);
163: debug(' Internal Party ID : '||px_inst_pa_rec.internal_party_id);
177: fnd_message.set_token('INSTANCE_ID', px_inst_pa_rec.instance_id );
178: fnd_message.set_token('OLD_PARTY_ID', px_inst_pa_rec.party_id );
179: fnd_message.set_token('NEW_PARTY_ID', px_inst_pa_rec.src_txn_party_id );
180: fnd_msg_pub.add;
181: l_return_status := fnd_api.g_ret_sts_error;
182: RAISE fnd_api.g_exc_error;
183: END IF;
184:
185: EXCEPTION
178: fnd_message.set_token('OLD_PARTY_ID', px_inst_pa_rec.party_id );
179: fnd_message.set_token('NEW_PARTY_ID', px_inst_pa_rec.src_txn_party_id );
180: fnd_msg_pub.add;
181: l_return_status := fnd_api.g_ret_sts_error;
182: RAISE fnd_api.g_exc_error;
183: END IF;
184:
185: EXCEPTION
186: WHEN fnd_api.g_exc_error THEN
182: RAISE fnd_api.g_exc_error;
183: END IF;
184:
185: EXCEPTION
186: WHEN fnd_api.g_exc_error THEN
187: x_return_status := l_return_status;
188: END get_instance_pa_dtls;
189:
190: PROCEDURE get_dflt_sub_type_id(
195: BEGIN
196:
197: api_log('get_dflt_sub_type_id');
198:
199: x_return_status := fnd_api.g_ret_sts_success;
200:
201: SELECT sub_type_id
202: INTO x_sub_type_id
203: FROM csi_source_ib_types -- SQL repository changes.
210: WHEN no_data_found THEN
211: fnd_message.set_name('CSI', 'CSI_DFLT_SUB_TYPE_MISSING');
212: fnd_message.set_token('TXN_TYPE_ID', p_transaction_type_id);
213: fnd_msg_pub.add;
214: x_return_status := fnd_api.g_ret_sts_error;
215: WHEN too_many_rows THEN
216: fnd_message.set_name('CSI', 'CSI_MANY_DFLT_SUB_TYPES');
217: fnd_message.set_token('TXN_TYPE_ID', p_transaction_type_id);
218: fnd_msg_pub.add;
215: WHEN too_many_rows THEN
216: fnd_message.set_name('CSI', 'CSI_MANY_DFLT_SUB_TYPES');
217: fnd_message.set_token('TXN_TYPE_ID', p_transaction_type_id);
218: fnd_msg_pub.add;
219: x_return_status := fnd_api.g_ret_sts_error;
220:
221: END get_dflt_sub_type_id;
222:
223:
230: BEGIN
231:
232: api_log('get_sub_type_rec');
233:
234: x_return_status := fnd_api.g_ret_sts_success;
235:
236: SELECT *
237: INTO x_sub_type_rec
238: FROM csi_txn_sub_types
253: fnd_message.set_name('CSI', 'CSI_INT_SUB_TYPE_REC_MISSING');
254: fnd_message.set_token('SUB_TYPE_ID', p_sub_type_id);
255: fnd_message.set_token('TRANSACTION_TYPE_ID', p_transaction_type_id);
256: fnd_msg_pub.add;
257: x_return_status := fnd_api.g_ret_sts_error;
258: END get_sub_type_rec;
259:
260: PROCEDURE get_item_control_rec(
261: p_mtl_txn_id IN number,
268:
269: BEGIN
270:
271: api_log('get_item_control_rec');
272: x_return_status := fnd_api.g_ret_sts_success;
273: BEGIN
274:
275: SELECT inventory_item_id,
276: organization_id
283: WHEN no_data_found THEN
284: fnd_message.set_name('CSI','CSI_NO_INVENTORY_RECORDS');
285: fnd_message.set_token('MTL_TRANSACTION_ID',p_mtl_txn_id);
286: fnd_msg_pub.add;
287: RAISE fnd_api.g_exc_error;
288: END;
289:
290: BEGIN
291: SELECT serial_number_control_code,
310: fnd_message.set_name('CSI', 'CSI_INT_ITEM_ID_MISSING');
311: fnd_message.set_token('INVENTORY_ITEM_ID', l_item_control_rec.inventory_item_id);
312: fnd_message.set_token('INV_ORGANZATION_ID', l_item_control_rec.organization_id);
313: fnd_msg_pub.add;
314: RAISE fnd_api.g_exc_error;
315: END;
316:
317: BEGIN
318: SELECT distinct serial_number_control_code
327: fnd_message.set_name('CSI', 'CSI_INT_ITEM_ID_MISSING');
328: fnd_message.set_token('INVENTORY_ITEM_ID', l_item_control_rec.inventory_item_id);
329: fnd_message.set_token('INV_ORGANZATION_ID', l_item_control_rec.organization_id);
330: fnd_msg_pub.add;
331: RAISE fnd_api.g_exc_error;
332: END;
333:
334: dump_item_control_rec(
335: p_item_control_rec => l_item_control_rec);
336:
337: x_item_control_rec := l_item_control_rec;
338:
339: EXCEPTION
340: WHEN fnd_api.g_exc_error THEN
341: x_return_status := fnd_api.g_ret_sts_error;
342:
343: END get_item_control_rec;
344:
337: x_item_control_rec := l_item_control_rec;
338:
339: EXCEPTION
340: WHEN fnd_api.g_exc_error THEN
341: x_return_status := fnd_api.g_ret_sts_error;
342:
343: END get_item_control_rec;
344:
345:
357: l_rma_line_id number;
358:
359: BEGIN
360:
361: x_return_status := fnd_api.g_ret_sts_success;
362:
363: api_log('get_partner_order_info');
364:
365: SELECT trx_source_line_id
392:
393: IF x_partner_order_rec.END_CUSTOMER_ID is null Then
394: fnd_message.set_name('CSI','CSI_PARTNER_VAL_MISSING');
395: fnd_msg_pub.add;
396: raise fnd_api.g_exc_error;
397: ELSE
398: BEGIN
399: SELECT party_id,
400: status
406: WHEN no_data_found THEN
407: fnd_message.set_name('CSI','CSI_INT_INV_CUST_ACCT_ID');
408: fnd_message.set_token('CUST_ACCOUNT_ID', x_partner_order_rec.END_CUSTOMER_ID);
409: fnd_msg_pub.add;
410: RAISE fnd_api.g_exc_error;
411: END;
412:
413: IF l_party_id = -1 THEN
414: raise fnd_api.g_exc_error;
410: RAISE fnd_api.g_exc_error;
411: END;
412:
413: IF l_party_id = -1 THEN
414: raise fnd_api.g_exc_error;
415: END IF;
416:
417: x_end_cust_party_id := l_party_id;
418:
427:
428: END IF;
429:
430: EXCEPTION
431: WHEN fnd_api.g_exc_error THEN
432: x_return_status := fnd_api.g_ret_sts_error;
433:
434: END get_partner_order_info;
435:
428: END IF;
429:
430: EXCEPTION
431: WHEN fnd_api.g_exc_error THEN
432: x_return_status := fnd_api.g_ret_sts_error;
433:
434: END get_partner_order_info;
435:
436:
445: l_cust_acct_site_use_id number;
446:
447: BEGIN
448:
449: x_return_status := fnd_api.g_ret_sts_success;
450:
451: api_log('get_src_order_info');
452:
453: x_src_order_rec.transaction_id := p_mtl_txn_id;
456: INTO x_src_order_rec.rma_line_id
457: FROM mtl_material_transactions
458: WHERE transaction_id = p_mtl_txn_id;
459:
460: SELECT nvl(reference_line_id , fnd_api.g_miss_num)
461: INTO x_src_order_rec.original_order_line_id
462: FROM oe_order_lines_all
463: WHERE line_id = x_src_order_rec.rma_line_id;
464:
499: fnd_message.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
500: fnd_message.set_token('SITE_USE_ID',l_cust_acct_site_use_id);
501: fnd_message.set_token('SITE_USE_CODE','SHIP_TO');
502: fnd_msg_pub.add;
503: raise fnd_api.g_exc_error;
504: END;
505: ELSE -- null for both ship and Invoice to org in RMA's - Self bug. Raise the error much before rather than in the API. shegde
506: fnd_message.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
507: fnd_message.set_token('SITE_USE_ID',l_cust_acct_site_use_id);
506: fnd_message.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
507: fnd_message.set_token('SITE_USE_ID',l_cust_acct_site_use_id);
508: fnd_message.set_token('SITE_USE_CODE','INVOICE_TO');
509: fnd_msg_pub.add;
510: raise fnd_api.g_exc_error;
511: END IF;
512: EXCEPTION
513: WHEN no_data_found THEN
514: null;
514: null;
515: END;
516: END IF;
517: EXCEPTION
518: WHEN fnd_api.g_exc_error THEN
519: x_return_status := fnd_api.g_ret_sts_error;
520: END get_src_order_info;
521:
522: PROCEDURE get_mtl_txn_recs(
515: END;
516: END IF;
517: EXCEPTION
518: WHEN fnd_api.g_exc_error THEN
519: x_return_status := fnd_api.g_ret_sts_error;
520: END get_src_order_info;
521:
522: PROCEDURE get_mtl_txn_recs(
523: p_mtl_txn_id IN number,
527: x_src_order_rec OUT NOCOPY source_order_rec,
528: x_return_status OUT NOCOPY varchar2)
529: IS
530:
531: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
532: l_debug_level number;
533:
534: l_s_ind binary_integer := 0;
535: l_d_ind binary_integer := 0;
630: AND haou.organization_id = mmt.organization_id;
631:
632: BEGIN
633:
634: x_return_status := fnd_api.g_ret_sts_success;
635:
636: api_log('get_mtl_txn_recs');
637:
638: l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
641: p_mtl_txn_id => p_mtl_txn_id,
642: x_src_order_rec => l_src_order_rec,
643: x_return_status => l_return_status);
644:
645: IF l_return_status <> fnd_api.g_ret_sts_success THEN
646: RAISE fnd_api.g_exc_error;
647: END IF;
648:
649: x_src_order_rec := l_src_order_rec;
642: x_src_order_rec => l_src_order_rec,
643: x_return_status => l_return_status);
644:
645: IF l_return_status <> fnd_api.g_ret_sts_success THEN
646: RAISE fnd_api.g_exc_error;
647: END IF;
648:
649: x_src_order_rec := l_src_order_rec;
650:
652: p_mtl_txn_id => p_mtl_txn_id,
653: x_item_control_rec => l_item_control_rec,
654: x_return_status => l_return_status);
655:
656: IF l_return_status <> fnd_api.g_ret_sts_success THEN
657: RAISE fnd_api.g_exc_error;
658: END IF;
659:
660: IF (l_item_control_rec.serial_control_code in (2, 5, 6)
653: x_item_control_rec => l_item_control_rec,
654: x_return_status => l_return_status);
655:
656: IF l_return_status <> fnd_api.g_ret_sts_success THEN
657: RAISE fnd_api.g_exc_error;
658: END IF;
659:
660: IF (l_item_control_rec.serial_control_code in (2, 5, 6)
661: AND
813: IF l_s_ind = 0 then
814: fnd_message.set_name('CSI','CSI_NO_INVENTORY_RECORDS');
815: fnd_message.set_token('MTL_TRANSACTION_ID',p_mtl_txn_id);
816: fnd_msg_pub.add;
817: RAISE fnd_api.g_exc_error;
818: END IF;
819:
820: debug('Mtl Transaction Recs Count(Source) : '||l_src_mtl_txn_tbl.COUNT);
821: debug('Mtl Transaction Recs Count(Destination) : '||l_dest_mtl_txn_tbl.COUNT);
839: x_dest_mtl_txn_tbl := l_dest_mtl_txn_tbl;
840:
841: EXCEPTION
842:
843: WHEN fnd_api.g_exc_error THEN
844: x_return_status := fnd_api.g_ret_sts_error;
845:
846: WHEN others THEN
847:
840:
841: EXCEPTION
842:
843: WHEN fnd_api.g_exc_error THEN
844: x_return_status := fnd_api.g_ret_sts_error;
845:
846: WHEN others THEN
847:
848: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
849: fnd_message.set_token('API_NAME','get_mtl_txn_recs');
850: fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 240));
851: fnd_msg_pub.add;
852:
853: x_return_status := fnd_api.g_ret_sts_unexp_error;
854:
855: END get_mtl_txn_recs;
856:
857: PROCEDURE build_instance_query_rec(
867: l_party_query_rec csi_datastructures_pub.party_query_rec;
868:
869: BEGIN
870:
871: x_return_status := fnd_api.g_ret_sts_success;
872:
873: api_log('build_instance_query_rec');
874:
875: l_inst_query_rec.inventory_item_id := p_mtl_txn_rec.inventory_item_id;
899: debug(' party_id :'||l_party_query_rec.party_id);
900: debug(' relationship_type_code :'||l_party_query_rec.relationship_type_code);
901:
902: EXCEPTION
903: WHEN fnd_api.g_exc_error THEN
904: x_return_status := fnd_api.g_ret_sts_error;
905: END build_instance_query_rec;
906:
907:
900: debug(' relationship_type_code :'||l_party_query_rec.relationship_type_code);
901:
902: EXCEPTION
903: WHEN fnd_api.g_exc_error THEN
904: x_return_status := fnd_api.g_ret_sts_error;
905: END build_instance_query_rec;
906:
907:
908: PROCEDURE identify_source_instance(
914: l_party_query_rec csi_datastructures_pub.party_query_rec;
915: l_pty_acct_query_rec csi_datastructures_pub.party_account_query_rec;
916: l_instance_header_tbl csi_datastructures_pub.instance_header_tbl;
917: l_assm_qty number;
918: l_qry_exp_inst varchar2(1) := fnd_api.g_false;
919: l_wip_entity_type number;
920:
921: l_msg_count number;
922: l_msg_data varchar2(2000);
919: l_wip_entity_type number;
920:
921: l_msg_count number;
922: l_msg_data varchar2(2000);
923: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
924: BEGIN
925:
926: api_log('identify_source_instance');
927:
924: BEGIN
925:
926: api_log('identify_source_instance');
927:
928: x_return_status := fnd_api.g_ret_sts_success;
929:
930: build_instance_query_rec(
931: p_mtl_txn_rec => px_mtl_txn_rec,
932: x_instance_query_rec => l_instance_query_rec,
933: x_party_query_rec => l_party_query_rec,
934: x_pty_acct_query_rec => l_pty_acct_query_rec,
935: x_return_status => l_return_status);
936:
937: IF l_return_status <> fnd_api.g_ret_sts_success THEN
938: RAISE fnd_api.g_exc_error;
939: END IF;
940:
941: -- RMA fulfillment ER, get expired instances also. Instance id is mandatory in TD entered for RMA fl.
934: x_pty_acct_query_rec => l_pty_acct_query_rec,
935: x_return_status => l_return_status);
936:
937: IF l_return_status <> fnd_api.g_ret_sts_success THEN
938: RAISE fnd_api.g_exc_error;
939: END IF;
940:
941: -- RMA fulfillment ER, get expired instances also. Instance id is mandatory in TD entered for RMA fl.
942: -- This fix 2733128 (11.5.8) is already taken care of in 11.5.9
940:
941: -- RMA fulfillment ER, get expired instances also. Instance id is mandatory in TD entered for RMA fl.
942: -- This fix 2733128 (11.5.8) is already taken care of in 11.5.9
943: /*
944: IF nvl(px_mtl_txn_rec.instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
945: l_qry_exp_inst := fnd_api.g_true;
946: END IF;
947:
948: debug(' Bring expired instances Flag :'||l_qry_exp_inst);
941: -- RMA fulfillment ER, get expired instances also. Instance id is mandatory in TD entered for RMA fl.
942: -- This fix 2733128 (11.5.8) is already taken care of in 11.5.9
943: /*
944: IF nvl(px_mtl_txn_rec.instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
945: l_qry_exp_inst := fnd_api.g_true;
946: END IF;
947:
948: debug(' Bring expired instances Flag :'||l_qry_exp_inst);
949: */ -- shegde removing this check since referencing an instance should not be a pre-requisite.
953: p_pkg_name => 'csi_item_instance_pub');
954:
955: csi_item_instance_pub.get_item_instances(
956: p_api_version => 1.0,
957: p_commit => fnd_api.g_false,
958: p_init_msg_list => fnd_api.g_true,
959: p_validation_level => fnd_api.g_valid_level_full,
960: p_instance_query_rec => l_instance_query_rec,
961: p_party_query_rec => l_party_query_rec,
954:
955: csi_item_instance_pub.get_item_instances(
956: p_api_version => 1.0,
957: p_commit => fnd_api.g_false,
958: p_init_msg_list => fnd_api.g_true,
959: p_validation_level => fnd_api.g_valid_level_full,
960: p_instance_query_rec => l_instance_query_rec,
961: p_party_query_rec => l_party_query_rec,
962: p_account_query_rec => l_pty_acct_query_rec,
955: csi_item_instance_pub.get_item_instances(
956: p_api_version => 1.0,
957: p_commit => fnd_api.g_false,
958: p_init_msg_list => fnd_api.g_true,
959: p_validation_level => fnd_api.g_valid_level_full,
960: p_instance_query_rec => l_instance_query_rec,
961: p_party_query_rec => l_party_query_rec,
962: p_account_query_rec => l_pty_acct_query_rec,
963: p_transaction_id => NULL,
960: p_instance_query_rec => l_instance_query_rec,
961: p_party_query_rec => l_party_query_rec,
962: p_account_query_rec => l_pty_acct_query_rec,
963: p_transaction_id => NULL,
964: p_resolve_id_columns => fnd_api.g_false,
965: p_active_instance_only => l_qry_exp_inst,
966: x_instance_header_tbl => l_instance_header_tbl,
967: x_return_status => l_return_status,
968: x_msg_count => l_msg_count,
967: x_return_status => l_return_status,
968: x_msg_count => l_msg_count,
969: x_msg_data => l_msg_data );
970:
971: IF l_return_status <> fnd_api.g_ret_sts_success THEN
972: RAISE fnd_api.g_exc_error;
973: END IF;
974:
975: IF l_instance_header_tbl.COUNT > 0 THEN
968: x_msg_count => l_msg_count,
969: x_msg_data => l_msg_data );
970:
971: IF l_return_status <> fnd_api.g_ret_sts_success THEN
972: RAISE fnd_api.g_exc_error;
973: END IF;
974:
975: IF l_instance_header_tbl.COUNT > 0 THEN
976: IF l_instance_header_tbl.COUNT = 1 THEN
1009: debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1010: fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1011: fnd_message.set_token('LOC_TYPE_CODE', l_instance_header_tbl(1).location_type_code);
1012: fnd_msg_pub.add;
1013: RAISE fnd_api.g_exc_error;
1014: END IF;
1015:
1016: Exception when others then
1017: fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1016: Exception when others then
1017: fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1018: fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1019: fnd_msg_pub.add;
1020: raise fnd_api.g_exc_error;
1021: End;
1022: */
1023: ELSE
1024: debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1024: debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1025: fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1026: fnd_message.set_token('LOC_TYPE_CODE', l_instance_header_tbl(1).location_type_code);
1027: fnd_msg_pub.add;
1028: RAISE fnd_api.g_exc_error;
1029: END IF;
1030: -- fix for Bug 2733128.
1031: ELSE
1032: IF ( l_instance_header_tbl(1).location_type_code = 'INVENTORY'
1030: -- fix for Bug 2733128.
1031: ELSE
1032: IF ( l_instance_header_tbl(1).location_type_code = 'INVENTORY'
1033: AND
1034: NVL(l_instance_header_tbl(1).active_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date
1035: )
1036: THEN
1037: debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1038: fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1038: fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1039: fnd_message.set_token('LOC_TYPE_CODE', l_instance_header_tbl(1).location_type_code);
1040: fnd_message.set_token('INV_ORG_ID',l_instance_query_rec.inv_organization_id);
1041: fnd_msg_pub.add;
1042: raise fnd_api.g_exc_error;
1043: END IF;
1044: END IF;
1045: ELSE
1046: debug('Multiple Source Instances Found.');
1047: fnd_message.set_name('CSI', 'CSI_TXN_MULT_INST_FOUND');
1048: fnd_message.set_token('INV_ITEM_ID',l_instance_query_rec.inventory_item_id);
1049: fnd_message.set_token('INV_ORG_ID',l_instance_query_rec.inv_organization_id);
1050: fnd_msg_pub.add;
1051: raise fnd_api.g_exc_error;
1052: END IF;
1053: ELSE
1054: debug('RMA Processor could not find the source instance.');
1055: px_mtl_txn_rec.instance_id := fnd_api.g_miss_num;
1051: raise fnd_api.g_exc_error;
1052: END IF;
1053: ELSE
1054: debug('RMA Processor could not find the source instance.');
1055: px_mtl_txn_rec.instance_id := fnd_api.g_miss_num;
1056: END IF;
1057: EXCEPTION
1058: WHEN fnd_api.g_exc_error THEN
1059: x_return_status := fnd_api.g_ret_sts_error;
1054: debug('RMA Processor could not find the source instance.');
1055: px_mtl_txn_rec.instance_id := fnd_api.g_miss_num;
1056: END IF;
1057: EXCEPTION
1058: WHEN fnd_api.g_exc_error THEN
1059: x_return_status := fnd_api.g_ret_sts_error;
1060: END identify_source_instance;
1061:
1062:
1055: px_mtl_txn_rec.instance_id := fnd_api.g_miss_num;
1056: END IF;
1057: EXCEPTION
1058: WHEN fnd_api.g_exc_error THEN
1059: x_return_status := fnd_api.g_ret_sts_error;
1060: END identify_source_instance;
1061:
1062:
1063: PROCEDURE identify_source_instances(
1064: px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
1065: p_item_control_rec IN item_control_rec, -- Added for Multi WIP Job ER
1066: x_return_status OUT NOCOPY varchar2)
1067: IS
1068: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1069: BEGIN
1070:
1071: x_return_status := fnd_api.g_ret_sts_success;
1072:
1067: IS
1068: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1069: BEGIN
1070:
1071: x_return_status := fnd_api.g_ret_sts_success;
1072:
1073: api_log('identify_source_instances');
1074:
1075: IF px_mtl_txn_tbl.COUNT > 0 THEN
1080: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1081: p_item_control_rec => p_item_control_rec,
1082: x_return_status => l_return_status);
1083:
1084: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1085: raise fnd_api.g_exc_error;
1086: END IF;
1087:
1088: END LOOP;
1081: p_item_control_rec => p_item_control_rec,
1082: x_return_status => l_return_status);
1083:
1084: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1085: raise fnd_api.g_exc_error;
1086: END IF;
1087:
1088: END LOOP;
1089: END IF;
1088: END LOOP;
1089: END IF;
1090:
1091: EXCEPTION
1092: WHEN fnd_api.g_exc_error THEN
1093: x_return_status := fnd_api.g_ret_sts_error;
1094: END identify_source_instances;
1095:
1096: PROCEDURE get_master_organization_id(
1089: END IF;
1090:
1091: EXCEPTION
1092: WHEN fnd_api.g_exc_error THEN
1093: x_return_status := fnd_api.g_ret_sts_error;
1094: END identify_source_instances;
1095:
1096: PROCEDURE get_master_organization_id(
1097: p_organization_id IN number,
1100: IS
1101: l_master_organization_id number;
1102: BEGIN
1103: api_log('get_master_organization_id');
1104: x_return_status := fnd_api.g_ret_sts_success;
1105: SELECT master_organization_id
1106: INTO l_master_organization_id
1107: FROM mtl_parameters
1108: WHERE organization_id = p_organization_id;
1167: l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
1168:
1169:
1170: -- misc variables
1171: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1172: l_msg_count number;
1173: l_msg_data varchar2(2000);
1174: l_error_message varchar2(2000);
1175:
1179: l_ea_ind binary_integer;
1180:
1181: BEGIN
1182:
1183: x_return_status := fnd_api.g_ret_sts_success;
1184:
1185: api_log('build_process_tables_TD');
1186:
1187: get_dflt_sub_type_id(
1188: p_transaction_type_id => l_transaction_type_id,
1189: x_sub_type_id => l_txn_sub_type_id,
1190: x_return_status => l_return_status);
1191:
1192: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1193: raise fnd_api.g_exc_error;
1194: END IF;
1195:
1196: l_pty_ind := 1;
1189: x_sub_type_id => l_txn_sub_type_id,
1190: x_return_status => l_return_status);
1191:
1192: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1193: raise fnd_api.g_exc_error;
1194: END IF;
1195:
1196: l_pty_ind := 1;
1197: l_pa_ind := 1;
1211: x_return_status => l_return_status);
1212: END IF;
1213:
1214: -- get_txn_line_details
1215: IF nvl(p_mtl_txn_tbl(l_ind).txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1216: THEN
1217:
1218: l_txn_line_detail_query_rec.txn_line_detail_id := p_mtl_txn_tbl(l_ind).txn_line_detail_id;
1219: l_txn_line_detail_query_rec.processing_status := 'IN_PROCESS'; -- added for bug 3094905
1219: l_txn_line_detail_query_rec.processing_status := 'IN_PROCESS'; -- added for bug 3094905
1220:
1221: csi_t_txn_details_grp.get_transaction_details(
1222: p_api_version => 1.0,
1223: p_commit => fnd_api.g_false,
1224: p_init_msg_list => fnd_api.g_true,
1225: p_validation_level => fnd_api.g_valid_level_full,
1226: p_txn_line_query_rec => l_txn_line_query_rec,
1227: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1220:
1221: csi_t_txn_details_grp.get_transaction_details(
1222: p_api_version => 1.0,
1223: p_commit => fnd_api.g_false,
1224: p_init_msg_list => fnd_api.g_true,
1225: p_validation_level => fnd_api.g_valid_level_full,
1226: p_txn_line_query_rec => l_txn_line_query_rec,
1227: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1228: x_txn_line_detail_tbl => l_line_dtl_tbl,
1221: csi_t_txn_details_grp.get_transaction_details(
1222: p_api_version => 1.0,
1223: p_commit => fnd_api.g_false,
1224: p_init_msg_list => fnd_api.g_true,
1225: p_validation_level => fnd_api.g_valid_level_full,
1226: p_txn_line_query_rec => l_txn_line_query_rec,
1227: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1228: x_txn_line_detail_tbl => l_line_dtl_tbl,
1229: p_get_parties_flag => fnd_api.g_true,
1225: p_validation_level => fnd_api.g_valid_level_full,
1226: p_txn_line_query_rec => l_txn_line_query_rec,
1227: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1228: x_txn_line_detail_tbl => l_line_dtl_tbl,
1229: p_get_parties_flag => fnd_api.g_true,
1230: x_txn_party_detail_tbl => l_pty_dtl_tbl,
1231: p_get_pty_accts_flag => fnd_api.g_true,
1232: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
1233: p_get_ii_rltns_flag => fnd_api.g_false,
1227: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1228: x_txn_line_detail_tbl => l_line_dtl_tbl,
1229: p_get_parties_flag => fnd_api.g_true,
1230: x_txn_party_detail_tbl => l_pty_dtl_tbl,
1231: p_get_pty_accts_flag => fnd_api.g_true,
1232: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
1233: p_get_ii_rltns_flag => fnd_api.g_false,
1234: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
1235: p_get_org_assgns_flag => fnd_api.g_true,
1229: p_get_parties_flag => fnd_api.g_true,
1230: x_txn_party_detail_tbl => l_pty_dtl_tbl,
1231: p_get_pty_accts_flag => fnd_api.g_true,
1232: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
1233: p_get_ii_rltns_flag => fnd_api.g_false,
1234: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
1235: p_get_org_assgns_flag => fnd_api.g_true,
1236: x_txn_org_assgn_tbl => l_org_assgn_tbl,
1237: p_get_ext_attrib_vals_flag => fnd_api.g_true,
1231: p_get_pty_accts_flag => fnd_api.g_true,
1232: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
1233: p_get_ii_rltns_flag => fnd_api.g_false,
1234: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
1235: p_get_org_assgns_flag => fnd_api.g_true,
1236: x_txn_org_assgn_tbl => l_org_assgn_tbl,
1237: p_get_ext_attrib_vals_flag => fnd_api.g_true,
1238: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
1239: p_get_csi_attribs_flag => fnd_api.g_false,
1233: p_get_ii_rltns_flag => fnd_api.g_false,
1234: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
1235: p_get_org_assgns_flag => fnd_api.g_true,
1236: x_txn_org_assgn_tbl => l_org_assgn_tbl,
1237: p_get_ext_attrib_vals_flag => fnd_api.g_true,
1238: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
1239: p_get_csi_attribs_flag => fnd_api.g_false,
1240: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
1241: p_get_csi_iea_values_flag => fnd_api.g_false,
1235: p_get_org_assgns_flag => fnd_api.g_true,
1236: x_txn_org_assgn_tbl => l_org_assgn_tbl,
1237: p_get_ext_attrib_vals_flag => fnd_api.g_true,
1238: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
1239: p_get_csi_attribs_flag => fnd_api.g_false,
1240: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
1241: p_get_csi_iea_values_flag => fnd_api.g_false,
1242: x_csi_iea_values_tbl => l_csi_eav_tbl,
1243: p_get_txn_systems_flag => fnd_api.g_false,
1237: p_get_ext_attrib_vals_flag => fnd_api.g_true,
1238: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
1239: p_get_csi_attribs_flag => fnd_api.g_false,
1240: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
1241: p_get_csi_iea_values_flag => fnd_api.g_false,
1242: x_csi_iea_values_tbl => l_csi_eav_tbl,
1243: p_get_txn_systems_flag => fnd_api.g_false,
1244: x_txn_systems_tbl => l_txn_systems_tbl,
1245: x_return_status => l_return_status,
1239: p_get_csi_attribs_flag => fnd_api.g_false,
1240: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
1241: p_get_csi_iea_values_flag => fnd_api.g_false,
1242: x_csi_iea_values_tbl => l_csi_eav_tbl,
1243: p_get_txn_systems_flag => fnd_api.g_false,
1244: x_txn_systems_tbl => l_txn_systems_tbl,
1245: x_return_status => l_return_status,
1246: x_msg_count => l_msg_count,
1247: x_msg_data => l_msg_data);
1245: x_return_status => l_return_status,
1246: x_msg_count => l_msg_count,
1247: x_msg_data => l_msg_data);
1248:
1249: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1250: debug('Error getting the transaction details info for the subject id');
1251: RAISE fnd_api.g_exc_error;
1252: END IF;
1253:
1247: x_msg_data => l_msg_data);
1248:
1249: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1250: debug('Error getting the transaction details info for the subject id');
1251: RAISE fnd_api.g_exc_error;
1252: END IF;
1253:
1254: END IF;
1255:
1274: l_txn_rec.transaction_date := sysdate;
1275: l_txn_rec.transaction_type_id := l_transaction_type_id;
1276: l_txn_rec.txn_sub_type_id := p_mtl_txn_tbl(l_ind).sub_type_id;
1277:
1278: IF nvl(l_txn_rec.txn_sub_type_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1279: l_txn_rec.txn_sub_type_id := l_txn_sub_type_id;
1280: END IF;
1281:
1282: get_sub_type_rec(
1284: p_sub_type_id => l_txn_rec.txn_sub_type_id,
1285: x_sub_type_rec => l_sub_type_rec,
1286: x_return_status => l_return_status);
1287:
1288: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1289: raise fnd_api.g_exc_error;
1290: END IF;
1291:
1292: l_txn_rec.source_header_ref := l_rma_order_rec.source_header_ref;
1285: x_sub_type_rec => l_sub_type_rec,
1286: x_return_status => l_return_status);
1287:
1288: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1289: raise fnd_api.g_exc_error;
1290: END IF;
1291:
1292: l_txn_rec.source_header_ref := l_rma_order_rec.source_header_ref;
1293: l_txn_rec.source_header_ref_id := l_rma_order_rec.source_header_id;
1305: l_instances_tbl(l_ind).actual_return_date := p_mtl_txn_tbl(l_ind).transaction_date;
1306: l_instances_tbl(l_ind).return_by_date := null;
1307: /* this is because we query txn details by txn_line_detail_id */
1308: IF l_line_dtl_tbl.COUNT = 1
1309: AND nvl(p_mtl_txn_tbl(l_ind).txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1310: THEN
1311: l_instances_tbl(l_ind).instance_id := l_line_dtl_tbl(1).instance_id;
1312: END IF;
1313:
1310: THEN
1311: l_instances_tbl(l_ind).instance_id := l_line_dtl_tbl(1).instance_id;
1312: END IF;
1313:
1314: IF nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1315: l_instances_tbl(l_ind).instance_id := p_mtl_txn_tbl(l_ind).instance_id;
1316: END IF;
1317:
1318: l_instances_tbl(l_ind).mtl_txn_creation_date := nvl(p_mtl_txn_tbl(l_ind).mtl_txn_creation_date,sysdate);--bug4026148
1317:
1318: l_instances_tbl(l_ind).mtl_txn_creation_date := nvl(p_mtl_txn_tbl(l_ind).mtl_txn_creation_date,sysdate);--bug4026148
1319: l_instances_tbl(l_ind).quantity := p_mtl_txn_tbl(l_ind).instance_quantity;
1320:
1321: IF nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1322:
1323: BEGIN
1324:
1325: SELECT quantity
1342: debug('This Line had No Receiving Node in the RMA fulfillment earlier. Special Processing...');
1343:
1344: l_instances_tbl(l_ind).active_end_date := NULL; -- this is done in process txn also
1345: IF p_item_control_rec.serial_control_code = 1 THEN -- pass g_miss so that process txn API does not double update source instance
1346: l_instances_tbl(l_ind).instance_id := fnd_api.g_miss_num;
1347: END IF;
1348: ELSE
1349: debug('sorry!!. This Line had A Receiving Node on the RMA Order earlier. Normal Processing...');
1350: IF l_instance_quantity < l_instances_tbl(l_ind).quantity THEN
1351:
1352: fnd_message.set_name('CSI', 'CSI_INT_QTY_CHK_FAILED');
1353: fnd_message.set_token('INSTANCE_ID', l_instances_tbl(l_ind).instance_id);
1354: fnd_msg_pub.add;
1355: RAISE fnd_api.g_exc_error;
1356:
1357: END IF;
1358: END IF;
1359: ELSIF l_instance_quantity < l_instances_tbl(l_ind).quantity THEN
1362:
1363: fnd_message.set_name('CSI', 'CSI_INT_QTY_CHK_FAILED');
1364: fnd_message.set_token('INSTANCE_ID', l_instances_tbl(l_ind).instance_id);
1365: fnd_msg_pub.add;
1366: RAISE fnd_api.g_exc_error;
1367:
1368: END IF;
1369:
1370: EXCEPTION
1377: px_mtl_txn_rec => l_mtl_txn_rec ,
1378: p_item_control_rec => p_item_control_rec,
1379: x_return_status => l_return_status);
1380:
1381: IF l_mtl_txn_rec.instance_id <> fnd_api.g_miss_num THEN
1382: l_instances_tbl(l_ind).instance_id := l_mtl_txn_rec.instance_id;
1383: END IF;
1384: END IF;
1385:
1391:
1392: l_instances_tbl(l_ind).last_oe_rma_line_id := p_mtl_txn_tbl(l_ind).oe_line_id;
1393: l_instances_tbl(l_ind).object_version_number := 1.0;
1394:
1395: IF nvl(p_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1396: l_instances_tbl(l_ind).mfg_serial_number_flag := 'N';
1397: ELSE
1398: l_instances_tbl(l_ind).mfg_serial_number_flag := 'Y';
1399: END IF;
1467: --
1468: --
1469:
1470: -- check_and_break relation
1471: IF (nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1472: AND
1473: l_instances_tbl(l_ind).mfg_serial_number_flag = 'Y')
1474: OR
1475: -- for non serial configured item break it from the ato model
1472: AND
1473: l_instances_tbl(l_ind).mfg_serial_number_flag = 'Y')
1474: OR
1475: -- for non serial configured item break it from the ato model
1476: nvl(p_item_control_rec.model_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1477: THEN
1478:
1479: -- Moved the routine to process txn pvt to avoid circular dependancy
1480: -- introduced in that routine for bug 2373109 and also to not load rma receipt for
1484: p_instance_id => l_instances_tbl(l_ind).instance_id,
1485: p_csi_txn_rec => l_txn_rec,
1486: x_return_status => l_return_status);
1487:
1488: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1489: RAISE fnd_api.g_exc_error;
1490: END IF;
1491:
1492: END IF;
1485: p_csi_txn_rec => l_txn_rec,
1486: x_return_status => l_return_status);
1487:
1488: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1489: RAISE fnd_api.g_exc_error;
1490: END IF;
1491:
1492: END IF;
1493:
1532: l_internal_party_id number;
1533: l_master_organization_id number;
1534: l_rma_order_rec mtl_trx_type;
1535:
1536: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1537: l_error_message varchar2(2000);
1538:
1539: l_txn_rec csi_datastructures_pub.transaction_rec;
1540: l_instances_tbl csi_process_txn_grp.txn_instances_tbl;
1549:
1550:
1551: BEGIN
1552:
1553: x_return_status := fnd_api.g_ret_sts_success;
1554:
1555: api_log('build_process_tables_NOTD');
1556:
1557: get_dflt_sub_type_id(
1558: p_transaction_type_id => l_transaction_type_id,
1559: x_sub_type_id => l_txn_sub_type_id,
1560: x_return_status => l_return_status);
1561:
1562: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1563: raise fnd_api.g_exc_error;
1564: END IF;
1565:
1566: IF p_mtl_txn_tbl.COUNT > 0 THEN
1559: x_sub_type_id => l_txn_sub_type_id,
1560: x_return_status => l_return_status);
1561:
1562: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1563: raise fnd_api.g_exc_error;
1564: END IF;
1565:
1566: IF p_mtl_txn_tbl.COUNT > 0 THEN
1567: FOR l_ind IN p_mtl_txn_tbl.FIRST .. p_mtl_txn_tbl.LAST
1600: p_sub_type_id => l_txn_rec.txn_sub_type_id,
1601: x_sub_type_rec => l_sub_type_rec,
1602: x_return_status => l_return_status);
1603:
1604: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1605: raise fnd_api.g_exc_error;
1606: END IF;
1607:
1608: l_txn_rec.source_header_ref := l_rma_order_rec.source_header_ref;
1601: x_sub_type_rec => l_sub_type_rec,
1602: x_return_status => l_return_status);
1603:
1604: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1605: raise fnd_api.g_exc_error;
1606: END IF;
1607:
1608: l_txn_rec.source_header_ref := l_rma_order_rec.source_header_ref;
1609: l_txn_rec.source_header_ref_id := l_rma_order_rec.source_header_id;
1629: l_instances_tbl(l_ind).lot_number := p_mtl_txn_tbl(l_ind).lot_number; -- added Self Bug shegde.
1630: --l_instances_tbl(l_ind).location_type_code := 'HZ_PARTY_SITES';
1631: --l_instances_tbl(l_ind).instance_usage_code := 'IN_INVENTORY';
1632: l_instances_tbl(l_ind).mtl_txn_creation_date := nvl(p_mtl_txn_tbl(l_ind).mtl_txn_creation_date,sysdate);--bug4026148
1633: IF nvl(p_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1634: l_instances_tbl(l_ind).mfg_serial_number_flag := 'N';
1635: ELSE
1636: l_instances_tbl(l_ind).mfg_serial_number_flag := 'Y';
1637: END IF;
1638:
1639: l_instances_tbl(l_ind).unit_of_measure := p_mtl_txn_tbl(l_ind).primary_uom_code;
1640:
1641: -- check_and_break relation
1642: IF (nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1643: AND
1644: l_instances_tbl(l_ind).mfg_serial_number_flag = 'Y' )
1645: OR
1646: -- for non serial configured item break it from the ato model
1643: AND
1644: l_instances_tbl(l_ind).mfg_serial_number_flag = 'Y' )
1645: OR
1646: -- for non serial configured item break it from the ato model
1647: nvl(p_item_control_rec.model_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1648: THEN
1649:
1650: -- Moved the routine to process txn pvt to avoid circular dependancy
1651: -- introduced in that routine for bug 2373109 and also to not load rma
1655: p_instance_id => l_instances_tbl(l_ind).instance_id,
1656: p_csi_txn_rec => l_txn_rec,
1657: x_return_status => l_return_status);
1658:
1659: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1660: RAISE fnd_api.g_exc_error;
1661: END IF;
1662:
1663: END IF;
1656: p_csi_txn_rec => l_txn_rec,
1657: x_return_status => l_return_status);
1658:
1659: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1660: RAISE fnd_api.g_exc_error;
1661: END IF;
1662:
1663: END IF;
1664: END LOOP;
1691: x_match_basis OUT NOCOPY varchar2,
1692: x_return_status OUT NOCOPY varchar2)
1693: IS
1694: l_mtl_txn_rec mtl_txn_rec;
1695: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1696: l_inst_qty number;
1697: i_index number := 0;
1698: l_found varchar2(1) := 'N';
1699: BEGIN
1697: i_index number := 0;
1698: l_found varchar2(1) := 'N';
1699: BEGIN
1700:
1701: x_return_status := fnd_api.g_ret_sts_success;
1702:
1703: api_log('match_mtl_txn_for_txn_dtl');
1704:
1705: x_match_flag := 'N';
1709: IF px_mtl_txn_tbl.COUNT > 0 THEN
1710: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1711: LOOP
1712:
1713: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1714: AND
1715: nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1716: THEN
1717:
1711: LOOP
1712:
1713: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1714: AND
1715: nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1716: THEN
1717:
1718: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1719:
1716: THEN
1717:
1718: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1719:
1720: IF (nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1721: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char ) )
1722: AND
1723: (nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1724: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) )
1717:
1718: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1719:
1720: IF (nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1721: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char ) )
1722: AND
1723: (nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1724: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) )
1725: THEN
1719:
1720: IF (nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1721: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char ) )
1722: AND
1723: (nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1724: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) )
1725: THEN
1726: x_match_flag := 'Y';
1727: x_match_basis := 'ALL_ATTRIBUTES';
1720: IF (nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1721: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char ) )
1722: AND
1723: (nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1724: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) )
1725: THEN
1726: x_match_flag := 'Y';
1727: x_match_basis := 'ALL_ATTRIBUTES';
1728: px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1740:
1741: /* try matching with serial number alone */
1742: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1743: LOOP
1744: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char
1745: AND
1746: nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1747: THEN
1748: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1742: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1743: LOOP
1744: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char
1745: AND
1746: nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1747: THEN
1748: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1749: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1750: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char )
1745: AND
1746: nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1747: THEN
1748: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1749: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1750: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char )
1751: THEN
1752: x_match_flag := 'Y';
1753: x_match_basis := 'SRL_ATTRIBUTE';
1746: nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1747: THEN
1748: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1749: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1750: nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char )
1751: THEN
1752: x_match_flag := 'Y';
1753: x_match_basis := 'SRL_ATTRIBUTE';
1754: px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1754: px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1755: px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1756: px_mtl_txn_tbl(l_ind).sub_type_id := px_txn_dtl_rec.sub_type_id;
1757: l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1758: IF nvl(px_txn_dtl_rec.instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1759: debug('Entered txn line detail has null Instance_ID. Trying to identify one.');
1760:
1761: identify_source_instance(
1762: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1762: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1763: p_item_control_rec => p_item_control_rec,
1764: x_return_status => l_return_status);
1765:
1766: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1767: raise fnd_api.g_exc_error;
1768: END IF;
1769: ELSE
1770: px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1763: p_item_control_rec => p_item_control_rec,
1764: x_return_status => l_return_status);
1765:
1766: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1767: raise fnd_api.g_exc_error;
1768: END IF;
1769: ELSE
1770: px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1771: END IF;
1780:
1781: /* try matching with lot number alone */
1782: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1783: LOOP
1784: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char AND
1785: ( nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char OR -- Added for bug 4244887
1786: nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) <> fnd_api.g_miss_char) THEN -- Added for bug 4244887
1787: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1788: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1781: /* try matching with lot number alone */
1782: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1783: LOOP
1784: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char AND
1785: ( nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char OR -- Added for bug 4244887
1786: nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) <> fnd_api.g_miss_char) THEN -- Added for bug 4244887
1787: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1788: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1789: AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1782: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1783: LOOP
1784: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char AND
1785: ( nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char OR -- Added for bug 4244887
1786: nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) <> fnd_api.g_miss_char) THEN -- Added for bug 4244887
1787: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1788: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1789: AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1790: THEN
1783: LOOP
1784: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char AND
1785: ( nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char OR -- Added for bug 4244887
1786: nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) <> fnd_api.g_miss_char) THEN -- Added for bug 4244887
1787: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1788: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1789: AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1790: THEN
1791: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1784: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char AND
1785: ( nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char OR -- Added for bug 4244887
1786: nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) <> fnd_api.g_miss_char) THEN -- Added for bug 4244887
1787: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1788: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1789: AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1790: THEN
1791: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1792: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1788: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1789: AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1790: THEN
1791: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1792: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1793: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char )
1794: THEN
1795: x_match_flag := 'Y';
1796: x_match_basis := 'LOT_ATTRIBUTE';
1789: AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1790: THEN
1791: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1792: IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1793: nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char )
1794: THEN
1795: x_match_flag := 'Y';
1796: x_match_basis := 'LOT_ATTRIBUTE';
1797: IF px_mtl_txn_tbl(l_ind).transaction_quantity >= p_match_qty THEN
1805: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1806: p_item_control_rec => p_item_control_rec,
1807: x_return_status => l_return_status);
1808:
1809: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1810: raise fnd_api.g_exc_error;
1811: END IF;
1812: l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1813: --end of fix for bug5159276
1806: p_item_control_rec => p_item_control_rec,
1807: x_return_status => l_return_status);
1808:
1809: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1810: raise fnd_api.g_exc_error;
1811: END IF;
1812: l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1813: --end of fix for bug5159276
1814: exit;
1825: l_found := 'N';
1826:
1827: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1828: LOOP
1829: IF nvl(px_txn_dtl_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1830: IF nvl(p_item_control_rec.mult_srl_control_flag, fnd_api.g_miss_char) = 'Y' THEN
1831: IF p_item_control_rec.serial_control_code <> 1 THEN
1832: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1833: debug('Serial control codes are different. Non serial item instances referenced');
1826:
1827: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1828: LOOP
1829: IF nvl(px_txn_dtl_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1830: IF nvl(p_item_control_rec.mult_srl_control_flag, fnd_api.g_miss_char) = 'Y' THEN
1831: IF p_item_control_rec.serial_control_code <> 1 THEN
1832: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1833: debug('Serial control codes are different. Non serial item instances referenced');
1834: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1828: LOOP
1829: IF nvl(px_txn_dtl_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1830: IF nvl(p_item_control_rec.mult_srl_control_flag, fnd_api.g_miss_char) = 'Y' THEN
1831: IF p_item_control_rec.serial_control_code <> 1 THEN
1832: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1833: debug('Serial control codes are different. Non serial item instances referenced');
1834: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1835: i_index := NVL(px_tld_inst_tbl.LAST,0) + 1 ;
1836: IF px_tld_inst_tbl.count > 0 THEN
1854: END IF;
1855: END IF;
1856: ELSE
1857: -- Non serialized
1858: IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1859: THEN
1860: debug('Serial control codes are different. Serialized item instances referenced');
1861: debug('Transaction Details count: '||px_tld_inst_tbl.count);
1862: IF px_tld_inst_tbl.count > 0 THEN
1890: END IF;
1891: END IF;
1892: END IF;
1893: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1894: IF nvl(abs(px_txn_dtl_rec.quantity), fnd_api.g_miss_num ) =
1895: nvl(px_mtl_txn_tbl(l_ind).instance_quantity , fnd_api.g_miss_num ) AND
1896: nvl(px_mtl_txn_tbl(l_ind).serial_number,fnd_api.g_miss_char)= fnd_api.g_miss_char THEN -- Added For BUG 4244887
1897:
1898: px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1891: END IF;
1892: END IF;
1893: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1894: IF nvl(abs(px_txn_dtl_rec.quantity), fnd_api.g_miss_num ) =
1895: nvl(px_mtl_txn_tbl(l_ind).instance_quantity , fnd_api.g_miss_num ) AND
1896: nvl(px_mtl_txn_tbl(l_ind).serial_number,fnd_api.g_miss_char)= fnd_api.g_miss_char THEN -- Added For BUG 4244887
1897:
1898: px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1899: px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1892: END IF;
1893: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1894: IF nvl(abs(px_txn_dtl_rec.quantity), fnd_api.g_miss_num ) =
1895: nvl(px_mtl_txn_tbl(l_ind).instance_quantity , fnd_api.g_miss_num ) AND
1896: nvl(px_mtl_txn_tbl(l_ind).serial_number,fnd_api.g_miss_char)= fnd_api.g_miss_char THEN -- Added For BUG 4244887
1897:
1898: px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1899: px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1900: px_mtl_txn_tbl(l_ind).sub_type_id := px_txn_dtl_rec.sub_type_id;
1905: identify_source_instance(
1906: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1907: p_item_control_rec => p_item_control_rec,
1908: x_return_status => l_return_status);
1909: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1910: px_mtl_txn_tbl(l_ind).verified_flag := 'N';
1911: x_match_flag := 'N';
1912: l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1913: ELSE
1914: x_match_flag := 'Y';
1915: x_match_basis := 'MTL_ATTRIBUTE';
1916: px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1917: END IF;
1918: IF px_mtl_txn_tbl(l_ind).instance_id = fnd_api.g_miss_num THEN
1919: px_mtl_txn_tbl(l_ind).instance_id := NULL;
1920: END IF;
1921: ELSIF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1922: x_match_flag := 'Y';
1917: END IF;
1918: IF px_mtl_txn_tbl(l_ind).instance_id = fnd_api.g_miss_num THEN
1919: px_mtl_txn_tbl(l_ind).instance_id := NULL;
1920: END IF;
1921: ELSIF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1922: x_match_flag := 'Y';
1923: x_match_basis := 'QTY_ATTRIBUTE';
1924: IF px_mtl_txn_tbl(l_ind).transaction_quantity >= p_match_qty THEN
1925: px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1948:
1949: /*-- End: Added filer conditions for bug 4006563 --*/
1950:
1951: -- px_txn_dtl_rec.quantity := l_mtl_txn_rec.instance_quantity;
1952: IF l_mtl_txn_rec.instance_id <> fnd_api.g_miss_num THEN
1953: IF p_item_control_rec.serial_control_code = 1 THEN -- non serilized / lot
1954: Begin
1955: Select quantity
1956: into l_inst_qty
1961: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1962: fnd_message.set_token('API_NAME','match_mtl_txn_for_txn_dtl');
1963: fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 240));
1964: fnd_msg_pub.add;
1965: raise fnd_api.g_exc_error;
1966: End;
1967:
1968: IF p_item_control_rec.lot_control_code = 2 THEN
1969: IF l_inst_qty < l_mtl_txn_rec.instance_quantity THEN -- self bug
2002: debug('Could not match the entered installation details with the material txn info.');
2003: END IF;
2004:
2005: EXCEPTION
2006: WHEN fnd_api.g_exc_error THEN
2007: x_return_status := fnd_api.g_ret_sts_error;
2008: END match_mtl_txn_for_txn_dtl;
2009:
2010:
2003: END IF;
2004:
2005: EXCEPTION
2006: WHEN fnd_api.g_exc_error THEN
2007: x_return_status := fnd_api.g_ret_sts_error;
2008: END match_mtl_txn_for_txn_dtl;
2009:
2010:
2011: PROCEDURE split_mtl_txn_tbl(
2015: x_return_status OUT NOCOPY varchar2)
2016: IS
2017: l_new_quantity number;
2018: l_new_ind binary_integer := 0;
2019: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2020: BEGIN
2021:
2022: api_log('split_mtl_txn_tbl');
2023:
2030: LOOP
2031: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
2032: l_new_quantity := px_mtl_txn_tbl(l_ind).instance_quantity -
2033: abs(px_line_dtl_tbl(l_t_ind).quantity);
2034: IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num)
2035: = fnd_api.g_miss_num
2036: AND ( p_item_control_rec.serial_control_code <> 1) THEN
2037:
2038: identify_source_instance(
2031: IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
2032: l_new_quantity := px_mtl_txn_tbl(l_ind).instance_quantity -
2033: abs(px_line_dtl_tbl(l_t_ind).quantity);
2034: IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num)
2035: = fnd_api.g_miss_num
2036: AND ( p_item_control_rec.serial_control_code <> 1) THEN
2037:
2038: identify_source_instance(
2039: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
2039: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
2040: p_item_control_rec => p_item_control_rec,
2041: x_return_status => l_return_status);
2042: END IF;
2043: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2044: raise fnd_api.g_exc_error;
2045: END IF;
2046: -- px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2047: IF p_item_control_rec.serial_control_code = 1 THEN
2040: p_item_control_rec => p_item_control_rec,
2041: x_return_status => l_return_status);
2042: END IF;
2043: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2044: raise fnd_api.g_exc_error;
2045: END IF;
2046: -- px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2047: IF p_item_control_rec.serial_control_code = 1 THEN
2048: IF p_item_control_rec.lot_control_code <> 1 THEN
2045: END IF;
2046: -- px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2047: IF p_item_control_rec.serial_control_code = 1 THEN
2048: IF p_item_control_rec.lot_control_code <> 1 THEN
2049: IF nvl(px_line_dtl_tbl(l_t_ind).lot_number, fnd_api.g_miss_char )
2050: = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2051: px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2052: ELSE
2053: l_return_status := fnd_api.g_ret_sts_error;
2046: -- px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2047: IF p_item_control_rec.serial_control_code = 1 THEN
2048: IF p_item_control_rec.lot_control_code <> 1 THEN
2049: IF nvl(px_line_dtl_tbl(l_t_ind).lot_number, fnd_api.g_miss_char )
2050: = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2051: px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2052: ELSE
2053: l_return_status := fnd_api.g_ret_sts_error;
2054: debug('Lot number referenced on the transaction line detail is different from the one being received..');
2049: IF nvl(px_line_dtl_tbl(l_t_ind).lot_number, fnd_api.g_miss_char )
2050: = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2051: px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2052: ELSE
2053: l_return_status := fnd_api.g_ret_sts_error;
2054: debug('Lot number referenced on the transaction line detail is different from the one being received..');
2055: fnd_message.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
2056: fnd_message.set_token('PARAM','Lot Number');
2057: fnd_message.set_token('VALUE',px_line_dtl_tbl(l_t_ind).lot_number);
2056: fnd_message.set_token('PARAM','Lot Number');
2057: fnd_message.set_token('VALUE',px_line_dtl_tbl(l_t_ind).lot_number);
2058: fnd_message.set_token('REASON','The Lot number and/or instance referenced on the transaction details is different from the one received. Pl. correct it and reprocess the error');
2059: fnd_msg_pub.add;
2060: raise fnd_api.g_exc_error;
2061: END IF;
2062: ELSE
2063: px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2064: px_line_dtl_tbl(l_t_ind).processing_status := 'IN_PROCESS';
2081: END IF;
2082: END LOOP;
2083: END IF;
2084: Exception
2085: when fnd_api.g_exc_error then
2086: debug('fnd_api.g_exc_error raised in split_mtl_txn_tbl');
2087: x_return_status := l_return_status;
2088: END split_mtl_txn_tbl;
2089:
2082: END LOOP;
2083: END IF;
2084: Exception
2085: when fnd_api.g_exc_error then
2086: debug('fnd_api.g_exc_error raised in split_mtl_txn_tbl');
2087: x_return_status := l_return_status;
2088: END split_mtl_txn_tbl;
2089:
2090: PROCEDURE sync_txn_dtls_and_mtl_txn(
2114: l_u_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
2115:
2116: u_td_ind binary_integer;
2117:
2118: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2119: l_msg_count number;
2120: l_msg_data varchar2(2000);
2121: l_new_ind binary_integer := 0;
2122: l_tld_inst_tbl tld_inst_tbl;
2123: l_txn_sub_type_id number; -- Added for bug 4244887
2124:
2125: BEGIN
2126:
2127: x_return_status := fnd_api.g_ret_sts_success;
2128:
2129: api_log('sync_txn_dtls_and_mtl_txn');
2130:
2131: l_mtl_txn_tbl := px_mtl_txn_tbl;
2150: FOR l_td_ind IN px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
2151: LOOP
2152: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2153: LOOP
2154: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
2155: IF px_mtl_txn_tbl(l_ind).verified_flag = 'N' THEN
2156: IF nvl(px_line_dtl_tbl(l_td_ind).lot_number, fnd_api.g_miss_char )
2157: = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2158: IF px_mtl_txn_tbl(l_ind).instance_quantity > abs(px_line_dtl_tbl(l_td_ind).quantity) THEN
2152: FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2153: LOOP
2154: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
2155: IF px_mtl_txn_tbl(l_ind).verified_flag = 'N' THEN
2156: IF nvl(px_line_dtl_tbl(l_td_ind).lot_number, fnd_api.g_miss_char )
2157: = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2158: IF px_mtl_txn_tbl(l_ind).instance_quantity > abs(px_line_dtl_tbl(l_td_ind).quantity) THEN
2159: l_new_ind := px_mtl_txn_tbl.count + 1;
2160: px_mtl_txn_tbl(l_new_ind) := px_mtl_txn_tbl(l_ind);
2153: LOOP
2154: IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
2155: IF px_mtl_txn_tbl(l_ind).verified_flag = 'N' THEN
2156: IF nvl(px_line_dtl_tbl(l_td_ind).lot_number, fnd_api.g_miss_char )
2157: = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2158: IF px_mtl_txn_tbl(l_ind).instance_quantity > abs(px_line_dtl_tbl(l_td_ind).quantity) THEN
2159: l_new_ind := px_mtl_txn_tbl.count + 1;
2160: px_mtl_txn_tbl(l_new_ind) := px_mtl_txn_tbl(l_ind);
2161: px_mtl_txn_tbl(l_new_ind).instance_quantity :=
2192: x_match_flag => l_match_flag,
2193: x_match_basis => l_match_basis,
2194: x_return_status => l_return_status);
2195:
2196: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2197: debug('Errors while matching txn del with mtl txn.');
2198: RAISE fnd_api.g_exc_error;
2199: END IF;
2200:
2194: x_return_status => l_return_status);
2195:
2196: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2197: debug('Errors while matching txn del with mtl txn.');
2198: RAISE fnd_api.g_exc_error;
2199: END IF;
2200:
2201: END IF;
2202:
2229: IF px_mtl_txn_tbl.COUNT > 0 THEN
2230: FOR m_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2231: LOOP
2232: /*-- Added for bug 4244887 --*/ -- Assigning sub-type to mtl_txn table if sub-type is null
2233: IF nvl(px_mtl_txn_tbl(m_ind).sub_type_id,fnd_api.g_miss_num) = fnd_api.g_miss_num AND
2234: l_txn_sub_type_id IS NOT NULL THEN
2235: px_mtl_txn_tbl(m_ind).sub_type_id := l_txn_sub_type_id;
2236: END IF;
2237: /*-- End: Added for bug 4244887 --*/
2256: l_u_txn_line_rec.transaction_line_id := px_line_dtl_tbl(1).transaction_line_id;
2257:
2258: csi_t_txn_details_grp.update_txn_line_dtls(
2259: p_api_version => 1.0,
2260: p_commit => fnd_api.g_false,
2261: p_init_msg_list => fnd_api.g_true,
2262: p_validation_level => fnd_api.g_valid_level_full,
2263: p_txn_line_rec => l_u_txn_line_rec,
2264: p_txn_line_detail_tbl => l_u_line_dtl_tbl,
2257:
2258: csi_t_txn_details_grp.update_txn_line_dtls(
2259: p_api_version => 1.0,
2260: p_commit => fnd_api.g_false,
2261: p_init_msg_list => fnd_api.g_true,
2262: p_validation_level => fnd_api.g_valid_level_full,
2263: p_txn_line_rec => l_u_txn_line_rec,
2264: p_txn_line_detail_tbl => l_u_line_dtl_tbl,
2265: px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
2258: csi_t_txn_details_grp.update_txn_line_dtls(
2259: p_api_version => 1.0,
2260: p_commit => fnd_api.g_false,
2261: p_init_msg_list => fnd_api.g_true,
2262: p_validation_level => fnd_api.g_valid_level_full,
2263: p_txn_line_rec => l_u_txn_line_rec,
2264: p_txn_line_detail_tbl => l_u_line_dtl_tbl,
2265: px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
2266: px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
2270: x_return_status => l_return_status,
2271: x_msg_count => l_msg_count,
2272: x_msg_data => l_msg_data);
2273:
2274: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2275: debug('Update txn line dtls failed while matching txn del with mtl txn.');
2276: RAISE fnd_api.g_exc_error;
2277: END IF;
2278:
2272: x_msg_data => l_msg_data);
2273:
2274: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2275: debug('Update txn line dtls failed while matching txn del with mtl txn.');
2276: RAISE fnd_api.g_exc_error;
2277: END IF;
2278:
2279: ELSE
2280:
2290: px_mtl_txn_tbl => px_mtl_txn_tbl,
2291: p_item_control_rec => p_item_control_rec,
2292: x_return_status => l_return_status);
2293:
2294: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2295: raise fnd_api.g_exc_error;
2296: END IF;
2297: -- END IF;
2298:
2291: p_item_control_rec => p_item_control_rec,
2292: x_return_status => l_return_status);
2293:
2294: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2295: raise fnd_api.g_exc_error;
2296: END IF;
2297: -- END IF;
2298:
2299: /*
2312: px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
2313: p_item_control_rec => p_item_control_rec,
2314: x_return_status => l_return_status);
2315:
2316: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2317: raise fnd_api.g_exc_error;
2318: END IF;
2319: IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2320: AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(l_ind).instance_id
2313: p_item_control_rec => p_item_control_rec,
2314: x_return_status => l_return_status);
2315:
2316: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2317: raise fnd_api.g_exc_error;
2318: END IF;
2319: IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2320: AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(l_ind).instance_id
2321: THEN
2315:
2316: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2317: raise fnd_api.g_exc_error;
2318: END IF;
2319: IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2320: AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(l_ind).instance_id
2321: THEN
2322: px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(l_ind).instance_id;
2323: ELSE
2326: for bug 4570399*/
2327: px_line_dtl_tbl(1).processing_status:='IN_PROCESS';
2328:
2329: --fix for bug 5898987
2330: IF px_mtl_txn_tbl(1).instance_id <> fnd_api.g_miss_num THEN
2331: px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(1).instance_id;
2332: ELSE
2333: px_line_dtl_tbl(1).instance_id := NULL;
2334: END IF;
2354: px_mtl_txn_rec => px_mtl_txn_tbl(1),
2355: p_item_control_rec => p_item_control_rec,
2356: x_return_status => l_return_status);
2357:
2358: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2359: raise fnd_api.g_exc_error;
2360: END IF;
2361: IF nvl(px_mtl_txn_tbl(1).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2362: AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(1).instance_id
2355: p_item_control_rec => p_item_control_rec,
2356: x_return_status => l_return_status);
2357:
2358: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2359: raise fnd_api.g_exc_error;
2360: END IF;
2361: IF nvl(px_mtl_txn_tbl(1).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2362: AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(1).instance_id
2363: THEN
2357:
2358: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2359: raise fnd_api.g_exc_error;
2360: END IF;
2361: IF nvl(px_mtl_txn_tbl(1).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2362: AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(1).instance_id
2363: THEN
2364: px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(1).instance_id;
2365:
2379: ELSIF p_item_control_rec.serial_control_code = 1 THEN -- qty mismatch + txn dtl qty also mismatch with the total mmt qty!!!
2380: debug('The combination of Transaction Details entered could not be matched with the multiple material transaction records.');
2381: /* serialized items should be processed no matter what txn details
2382: are entered. hence handled separately at the end */
2383: l_return_status := fnd_api.g_ret_sts_error;
2384: RAISE fnd_api.g_exc_error;
2385: END IF;
2386: END IF;
2387: ELSE -- qty mismatch with txn dtl qty - Error!!
2380: debug('The combination of Transaction Details entered could not be matched with the multiple material transaction records.');
2381: /* serialized items should be processed no matter what txn details
2382: are entered. hence handled separately at the end */
2383: l_return_status := fnd_api.g_ret_sts_error;
2384: RAISE fnd_api.g_exc_error;
2385: END IF;
2386: END IF;
2387: ELSE -- qty mismatch with txn dtl qty - Error!!
2388: debug('Transaction Details quantity does not be match with the material transaction');
2394: FOR m IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2395: LOOP
2396: IF ( px_mtl_txn_tbl(m).verified_flag <> 'S' and
2397: px_line_dtl_tbl(t).instance_id is not null) THEN
2398: IF nvl(px_line_dtl_tbl(t).lot_number, fnd_api.g_miss_char )
2399: = nvl(px_mtl_txn_tbl(m).lot_number , fnd_api.g_miss_char ) THEN
2400: px_line_dtl_tbl(t).quantity := -1*(px_mtl_txn_tbl(m).lot_primary_quantity);
2401: px_mtl_txn_tbl(m).verified_flag := 'S';
2402: px_mtl_txn_tbl(m).instance_quantity := abs(px_line_dtl_tbl(t).quantity);
2395: LOOP
2396: IF ( px_mtl_txn_tbl(m).verified_flag <> 'S' and
2397: px_line_dtl_tbl(t).instance_id is not null) THEN
2398: IF nvl(px_line_dtl_tbl(t).lot_number, fnd_api.g_miss_char )
2399: = nvl(px_mtl_txn_tbl(m).lot_number , fnd_api.g_miss_char ) THEN
2400: px_line_dtl_tbl(t).quantity := -1*(px_mtl_txn_tbl(m).lot_primary_quantity);
2401: px_mtl_txn_tbl(m).verified_flag := 'S';
2402: px_mtl_txn_tbl(m).instance_quantity := abs(px_line_dtl_tbl(t).quantity);
2403: px_mtl_txn_tbl(m).instance_id := px_line_dtl_tbl(t).instance_id;
2411: END LOOP;
2412: END LOOP; -- match_flag = 'Y'
2413: END IF;
2414: IF l_match_flag <> 'Y' THEN
2415: l_return_status := fnd_api.g_ret_sts_error;
2416: RAISE fnd_api.g_exc_error;
2417: END IF;
2418: ELSIF p_item_control_rec.serial_control_code = 1 THEN
2419: /* serialized items should be processed no matter what txn details
2412: END LOOP; -- match_flag = 'Y'
2413: END IF;
2414: IF l_match_flag <> 'Y' THEN
2415: l_return_status := fnd_api.g_ret_sts_error;
2416: RAISE fnd_api.g_exc_error;
2417: END IF;
2418: ELSIF p_item_control_rec.serial_control_code = 1 THEN
2419: /* serialized items should be processed no matter what txn details
2420: are entered. hence handled separately - build_process..*/
2418: ELSIF p_item_control_rec.serial_control_code = 1 THEN
2419: /* serialized items should be processed no matter what txn details
2420: are entered. hence handled separately - build_process..*/
2421: /*ported for bug 3686818-Check For Partial receipt on Non-Serialized Item */
2422: IF nvl(p_item_control_rec.mult_srl_control_flag,fnd_api.g_miss_char) = 'Y' THEN
2423: IF x_tld_inst_tbl.count > 0 THEN
2424: null;-- process these in the next routine
2425: END IF;
2426: ELSIF px_mtl_txn_tbl.COUNT = 1 AND px_line_dtl_tbl.COUNT = 1 THEN
2424: null;-- process these in the next routine
2425: END IF;
2426: ELSIF px_mtl_txn_tbl.COUNT = 1 AND px_line_dtl_tbl.COUNT = 1 THEN
2427: IF ( px_line_dtl_tbl(1).instance_id IS NOT NULL
2428: AND nvl(px_line_dtl_tbl(1).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char) THEN
2429: px_line_dtl_tbl(1).quantity := -1*(px_mtl_txn_tbl(1).instance_quantity);
2430: px_mtl_txn_tbl(1).verified_flag := 'S';
2431: px_mtl_txn_tbl(1).instance_quantity := abs(px_line_dtl_tbl(1).quantity);
2432: px_mtl_txn_tbl(1).instance_id := px_line_dtl_tbl(1).instance_id;
2435: l_match_flag := 'Y';
2436: debug('match basis: '||'NS_ATTRIBUTE');
2437: END IF;
2438: ELSE -- modified for bug 3644297. non serial qty matches need to error out...
2439: l_return_status := fnd_api.g_ret_sts_error;
2440: RAISE fnd_api.g_exc_error;
2441: END IF;
2442:
2443: --Fix for bug 4125459:To take sub transaction type from txn details in the case of partial receipt
2436: debug('match basis: '||'NS_ATTRIBUTE');
2437: END IF;
2438: ELSE -- modified for bug 3644297. non serial qty matches need to error out...
2439: l_return_status := fnd_api.g_ret_sts_error;
2440: RAISE fnd_api.g_exc_error;
2441: END IF;
2442:
2443: --Fix for bug 4125459:To take sub transaction type from txn details in the case of partial receipt
2444: ELSIF p_item_control_rec.serial_control_code <> 1 THEN
2453: l_u_txn_line_rec.transaction_line_id := px_line_dtl_tbl(1).transaction_line_id;
2454:
2455: csi_t_txn_details_grp.update_txn_line_dtls(
2456: p_api_version => 1.0,
2457: p_commit => fnd_api.g_false,
2458: p_init_msg_list => fnd_api.g_true,
2459: p_validation_level => fnd_api.g_valid_level_full,
2460: p_txn_line_rec => l_u_txn_line_rec,
2461: p_txn_line_detail_tbl => px_line_dtl_tbl,
2454:
2455: csi_t_txn_details_grp.update_txn_line_dtls(
2456: p_api_version => 1.0,
2457: p_commit => fnd_api.g_false,
2458: p_init_msg_list => fnd_api.g_true,
2459: p_validation_level => fnd_api.g_valid_level_full,
2460: p_txn_line_rec => l_u_txn_line_rec,
2461: p_txn_line_detail_tbl => px_line_dtl_tbl,
2462: px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
2455: csi_t_txn_details_grp.update_txn_line_dtls(
2456: p_api_version => 1.0,
2457: p_commit => fnd_api.g_false,
2458: p_init_msg_list => fnd_api.g_true,
2459: p_validation_level => fnd_api.g_valid_level_full,
2460: p_txn_line_rec => l_u_txn_line_rec,
2461: p_txn_line_detail_tbl => px_line_dtl_tbl,
2462: px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
2463: px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
2467: x_return_status => l_return_status,
2468: x_msg_count => l_msg_count,
2469: x_msg_data => l_msg_data);
2470:
2471: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2472: debug('Update txn line dtls failed while matching txn del with mtl txn.');
2473: RAISE fnd_api.g_exc_error;
2474: END IF;
2475:
2469: x_msg_data => l_msg_data);
2470:
2471: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2472: debug('Update txn line dtls failed while matching txn del with mtl txn.');
2473: RAISE fnd_api.g_exc_error;
2474: END IF;
2475:
2476: END IF;
2477: END IF;
2475:
2476: END IF;
2477: END IF;
2478: Exception
2479: when fnd_api.g_exc_error then
2480: debug('fnd_api.g_exc_error raised in sync_txn_dtls_and_mtl_txn');
2481: x_return_status := l_return_status;
2482: when others then
2483: debug('when others raised in sync_txn_dtls_and_mtl_txn');
2476: END IF;
2477: END IF;
2478: Exception
2479: when fnd_api.g_exc_error then
2480: debug('fnd_api.g_exc_error raised in sync_txn_dtls_and_mtl_txn');
2481: x_return_status := l_return_status;
2482: when others then
2483: debug('when others raised in sync_txn_dtls_and_mtl_txn');
2484: x_return_status := fnd_api.g_ret_sts_unexp_error;
2480: debug('fnd_api.g_exc_error raised in sync_txn_dtls_and_mtl_txn');
2481: x_return_status := l_return_status;
2482: when others then
2483: debug('when others raised in sync_txn_dtls_and_mtl_txn');
2484: x_return_status := fnd_api.g_ret_sts_unexp_error;
2485: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2486: fnd_message.set_token('API_NAME','sync_txn_dtls_and_mtl_txn');
2487: fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 240));
2488: fnd_msg_pub.add;
2495: px_trx_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec)
2496: IS
2497:
2498: l_api_name varchar2(30) := 'rma_receipt';
2499: l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2500: l_msg_count number;
2501: l_msg_data varchar2(2000);
2502:
2503: l_rma_order_rec mtl_trx_type;
2578:
2579: -- process_transaction variables
2580:
2581: l_api_version NUMBER := 1.0;
2582: l_commit VARCHAR2(1) := fnd_api.g_false;
2583: l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
2584: l_validation_level NUMBER := fnd_api.g_valid_level_full;
2585: l_validate_only_flag VARCHAR2(1) := fnd_api.g_false;
2586: l_in_out_flag VARCHAR2(30) := 'IN';
2579: -- process_transaction variables
2580:
2581: l_api_version NUMBER := 1.0;
2582: l_commit VARCHAR2(1) := fnd_api.g_false;
2583: l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
2584: l_validation_level NUMBER := fnd_api.g_valid_level_full;
2585: l_validate_only_flag VARCHAR2(1) := fnd_api.g_false;
2586: l_in_out_flag VARCHAR2(30) := 'IN';
2587:
2580:
2581: l_api_version NUMBER := 1.0;
2582: l_commit VARCHAR2(1) := fnd_api.g_false;
2583: l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
2584: l_validation_level NUMBER := fnd_api.g_valid_level_full;
2585: l_validate_only_flag VARCHAR2(1) := fnd_api.g_false;
2586: l_in_out_flag VARCHAR2(30) := 'IN';
2587:
2588: l_txn_rec csi_datastructures_pub.transaction_rec;
2581: l_api_version NUMBER := 1.0;
2582: l_commit VARCHAR2(1) := fnd_api.g_false;
2583: l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
2584: l_validation_level NUMBER := fnd_api.g_valid_level_full;
2585: l_validate_only_flag VARCHAR2(1) := fnd_api.g_false;
2586: l_in_out_flag VARCHAR2(30) := 'IN';
2587:
2588: l_txn_rec csi_datastructures_pub.transaction_rec;
2589: l_instances_tbl csi_process_txn_grp.txn_instances_tbl;
2646: savepoint rma_receipt;
2647:
2648: fnd_msg_pub.initialize;
2649:
2650: x_return_status := fnd_api.g_ret_sts_success;
2651:
2652: csi_t_gen_utility_pvt.build_file_name(
2653: p_file_segment1 => 'csirmarc',
2654: p_file_segment2 => p_mtl_txn_id);
2693: x_item_control_rec => l_item_control_rec,
2694: x_src_order_rec => l_src_order_rec,
2695: x_return_status => l_return_status);
2696:
2697: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2698: debug('get_mtl_txn_recs Failed.');
2699: RAISE fnd_api.g_exc_error;
2700: END IF;
2701:
2695: x_return_status => l_return_status);
2696:
2697: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2698: debug('get_mtl_txn_recs Failed.');
2699: RAISE fnd_api.g_exc_error;
2700: END IF;
2701:
2702: /* start of ER 2646086 + RMA for Repair with different party */
2703: /* Get the value for the source of truth flag */
2731: l_txn_line_detail_query_rec.source_transaction_flag := 'Y';
2732:
2733: csi_t_txn_details_grp.get_transaction_details(
2734: p_api_version => 1.0,
2735: p_commit => fnd_api.g_false,
2736: p_init_msg_list => fnd_api.g_true,
2737: p_validation_level => fnd_api.g_valid_level_full,
2738: p_txn_line_query_rec => l_txn_line_query_rec,
2739: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2732:
2733: csi_t_txn_details_grp.get_transaction_details(
2734: p_api_version => 1.0,
2735: p_commit => fnd_api.g_false,
2736: p_init_msg_list => fnd_api.g_true,
2737: p_validation_level => fnd_api.g_valid_level_full,
2738: p_txn_line_query_rec => l_txn_line_query_rec,
2739: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2740: x_txn_line_detail_tbl => l_line_dtl_tbl,
2733: csi_t_txn_details_grp.get_transaction_details(
2734: p_api_version => 1.0,
2735: p_commit => fnd_api.g_false,
2736: p_init_msg_list => fnd_api.g_true,
2737: p_validation_level => fnd_api.g_valid_level_full,
2738: p_txn_line_query_rec => l_txn_line_query_rec,
2739: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2740: x_txn_line_detail_tbl => l_line_dtl_tbl,
2741: p_get_parties_flag => fnd_api.g_false,
2737: p_validation_level => fnd_api.g_valid_level_full,
2738: p_txn_line_query_rec => l_txn_line_query_rec,
2739: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2740: x_txn_line_detail_tbl => l_line_dtl_tbl,
2741: p_get_parties_flag => fnd_api.g_false,
2742: x_txn_party_detail_tbl => l_pty_dtl_tbl,
2743: p_get_pty_accts_flag => fnd_api.g_false,
2744: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2745: p_get_ii_rltns_flag => fnd_api.g_false,
2739: p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2740: x_txn_line_detail_tbl => l_line_dtl_tbl,
2741: p_get_parties_flag => fnd_api.g_false,
2742: x_txn_party_detail_tbl => l_pty_dtl_tbl,
2743: p_get_pty_accts_flag => fnd_api.g_false,
2744: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2745: p_get_ii_rltns_flag => fnd_api.g_false,
2746: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2747: p_get_org_assgns_flag => fnd_api.g_false,
2741: p_get_parties_flag => fnd_api.g_false,
2742: x_txn_party_detail_tbl => l_pty_dtl_tbl,
2743: p_get_pty_accts_flag => fnd_api.g_false,
2744: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2745: p_get_ii_rltns_flag => fnd_api.g_false,
2746: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2747: p_get_org_assgns_flag => fnd_api.g_false,
2748: x_txn_org_assgn_tbl => l_org_assgn_tbl,
2749: p_get_ext_attrib_vals_flag => fnd_api.g_false,
2743: p_get_pty_accts_flag => fnd_api.g_false,
2744: x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2745: p_get_ii_rltns_flag => fnd_api.g_false,
2746: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2747: p_get_org_assgns_flag => fnd_api.g_false,
2748: x_txn_org_assgn_tbl => l_org_assgn_tbl,
2749: p_get_ext_attrib_vals_flag => fnd_api.g_false,
2750: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
2751: p_get_csi_attribs_flag => fnd_api.g_false,
2745: p_get_ii_rltns_flag => fnd_api.g_false,
2746: x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2747: p_get_org_assgns_flag => fnd_api.g_false,
2748: x_txn_org_assgn_tbl => l_org_assgn_tbl,
2749: p_get_ext_attrib_vals_flag => fnd_api.g_false,
2750: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
2751: p_get_csi_attribs_flag => fnd_api.g_false,
2752: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
2753: p_get_csi_iea_values_flag => fnd_api.g_false,
2747: p_get_org_assgns_flag => fnd_api.g_false,
2748: x_txn_org_assgn_tbl => l_org_assgn_tbl,
2749: p_get_ext_attrib_vals_flag => fnd_api.g_false,
2750: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
2751: p_get_csi_attribs_flag => fnd_api.g_false,
2752: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
2753: p_get_csi_iea_values_flag => fnd_api.g_false,
2754: x_csi_iea_values_tbl => l_csi_eav_tbl,
2755: p_get_txn_systems_flag => fnd_api.g_false,
2749: p_get_ext_attrib_vals_flag => fnd_api.g_false,
2750: x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
2751: p_get_csi_attribs_flag => fnd_api.g_false,
2752: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
2753: p_get_csi_iea_values_flag => fnd_api.g_false,
2754: x_csi_iea_values_tbl => l_csi_eav_tbl,
2755: p_get_txn_systems_flag => fnd_api.g_false,
2756: x_txn_systems_tbl => l_txn_systems_tbl,
2757: x_return_status => l_return_status,
2751: p_get_csi_attribs_flag => fnd_api.g_false,
2752: x_csi_ext_attribs_tbl => l_csi_ea_tbl,
2753: p_get_csi_iea_values_flag => fnd_api.g_false,
2754: x_csi_iea_values_tbl => l_csi_eav_tbl,
2755: p_get_txn_systems_flag => fnd_api.g_false,
2756: x_txn_systems_tbl => l_txn_systems_tbl,
2757: x_return_status => l_return_status,
2758: x_msg_count => l_msg_count,
2759: x_msg_data => l_msg_data);
2757: x_return_status => l_return_status,
2758: x_msg_count => l_msg_count,
2759: x_msg_data => l_msg_data);
2760:
2761: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2762: debug('Error getting transaction details for RMA Receipt to IB Interface.');
2763: raise fnd_api.g_exc_error;
2764: END IF;
2765:
2759: x_msg_data => l_msg_data);
2760:
2761: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2762: debug('Error getting transaction details for RMA Receipt to IB Interface.');
2763: raise fnd_api.g_exc_error;
2764: END IF;
2765:
2766: /* check if instance reference is specified */
2767:
2773: /* bug 2291543. added the serial code check here to allow the serial
2774: installation details without instance reference
2775: */
2776: IF l_item_control_rec.serial_control_code = 1 THEN
2777: IF nvl(l_line_dtl_tbl(l_ind).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2778: debug('No instance reference in Txn Details for non serial item.');
2779: fnd_message.set_name('CSI', 'CSI_INST_REF_NOT_ENTERED');
2780: fnd_msg_pub.add;
2781: RAISE fnd_api.g_exc_error;
2777: IF nvl(l_line_dtl_tbl(l_ind).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2778: debug('No instance reference in Txn Details for non serial item.');
2779: fnd_message.set_name('CSI', 'CSI_INST_REF_NOT_ENTERED');
2780: fnd_msg_pub.add;
2781: RAISE fnd_api.g_exc_error;
2782: END IF;
2783: END IF;
2784:
2785: END LOOP;
2792: x_tld_inst_tbl => l_tld_inst_tbl,
2793: p_item_control_rec => l_item_control_rec,
2794: x_return_status => l_return_status);
2795:
2796: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2797: fnd_message.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
2798: fnd_message.set_token('PARAM','Item attributes');
2799: fnd_message.set_token('VALUE','Lot / Serial');
2800: fnd_message.set_token('REASON','The transaction details entered do not match the inventory material transaction for one or more of the attributes. Pl. correct it and reprocess the error');
2798: fnd_message.set_token('PARAM','Item attributes');
2799: fnd_message.set_token('VALUE','Lot / Serial');
2800: fnd_message.set_token('REASON','The transaction details entered do not match the inventory material transaction for one or more of the attributes. Pl. correct it and reprocess the error');
2801: fnd_msg_pub.add;
2802: RAISE fnd_api.g_exc_error;
2803: END IF;
2804:
2805: build_process_tables_TD(
2806: p_mtl_txn_tbl => l_src_mtl_txn_tbl,
2817: x_ii_relationships_tbl => l_ii_relationships_tbl,
2818: x_dest_location_rec => l_dest_location_rec,
2819: x_return_status => l_return_status);
2820:
2821: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2822: RAISE fnd_api.g_exc_error;
2823: END IF;
2824:
2825: IF l_tld_inst_tbl.count > 0 THEN
2818: x_dest_location_rec => l_dest_location_rec,
2819: x_return_status => l_return_status);
2820:
2821: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2822: RAISE fnd_api.g_exc_error;
2823: END IF;
2824:
2825: IF l_tld_inst_tbl.count > 0 THEN
2826: debug('Instance updates to be processed for Multiple serial control codes.. '||l_tld_inst_tbl.count);
2847: END IF;
2848: For i in 1 .. l_i_ind Loop
2849: -- this is done to ensure that the number of srl item instances updated are limited
2850: --to only the total transacted quantity for nsrl items
2851: IF nvl(l_tld_inst_tbl(i).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2852: AND nvl(l_tld_inst_tbl(i).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
2853: THEN
2854: l_inst_pa_rec.instance_id := l_tld_inst_tbl(i).instance_id;
2855: get_instance_pa_dtls(
2848: For i in 1 .. l_i_ind Loop
2849: -- this is done to ensure that the number of srl item instances updated are limited
2850: --to only the total transacted quantity for nsrl items
2851: IF nvl(l_tld_inst_tbl(i).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2852: AND nvl(l_tld_inst_tbl(i).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
2853: THEN
2854: l_inst_pa_rec.instance_id := l_tld_inst_tbl(i).instance_id;
2855: get_instance_pa_dtls(
2856: p_transaction_type_id => l_txn_rec.transaction_type_id,
2858: px_inst_pa_rec => l_inst_pa_rec,
2859: x_sub_type_rec => l_sub_type_rec,
2860: x_return_status => l_return_status);
2861:
2862: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2863: RAISE fnd_api.g_exc_error;
2864: END IF;
2865: Begin
2866: SELECT object_version_number, active_end_date
2859: x_sub_type_rec => l_sub_type_rec,
2860: x_return_status => l_return_status);
2861:
2862: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2863: RAISE fnd_api.g_exc_error;
2864: END IF;
2865: Begin
2866: SELECT object_version_number, active_end_date
2867: INTO l_obj_ver_num, l_end_date
2905: IF l_pi_ind > 0 THEN
2906: l_count := 0;
2907: For k in l_instances_tbl.first .. l_instances_tbl.last Loop
2908: --Only loop through till for the actual qty received...
2909: IF nvl(l_instances_tbl(k).instance_id,fnd_api.g_miss_num)
2910: = nvl(l_tld_inst_tbl(i).instance_id,fnd_api.g_miss_num) THEN
2911: l_instances_tbl(k).instance_id := fnd_api.g_miss_num;
2912: l_instances_tbl(k).serial_number := fnd_api.g_miss_char;
2913: l_count := l_count + 1;
2906: l_count := 0;
2907: For k in l_instances_tbl.first .. l_instances_tbl.last Loop
2908: --Only loop through till for the actual qty received...
2909: IF nvl(l_instances_tbl(k).instance_id,fnd_api.g_miss_num)
2910: = nvl(l_tld_inst_tbl(i).instance_id,fnd_api.g_miss_num) THEN
2911: l_instances_tbl(k).instance_id := fnd_api.g_miss_num;
2912: l_instances_tbl(k).serial_number := fnd_api.g_miss_char;
2913: l_count := l_count + 1;
2914: IF l_count >= l_pi_ind THEN
2907: For k in l_instances_tbl.first .. l_instances_tbl.last Loop
2908: --Only loop through till for the actual qty received...
2909: IF nvl(l_instances_tbl(k).instance_id,fnd_api.g_miss_num)
2910: = nvl(l_tld_inst_tbl(i).instance_id,fnd_api.g_miss_num) THEN
2911: l_instances_tbl(k).instance_id := fnd_api.g_miss_num;
2912: l_instances_tbl(k).serial_number := fnd_api.g_miss_char;
2913: l_count := l_count + 1;
2914: IF l_count >= l_pi_ind THEN
2915: exit;
2908: --Only loop through till for the actual qty received...
2909: IF nvl(l_instances_tbl(k).instance_id,fnd_api.g_miss_num)
2910: = nvl(l_tld_inst_tbl(i).instance_id,fnd_api.g_miss_num) THEN
2911: l_instances_tbl(k).instance_id := fnd_api.g_miss_num;
2912: l_instances_tbl(k).serial_number := fnd_api.g_miss_char;
2913: l_count := l_count + 1;
2914: IF l_count >= l_pi_ind THEN
2915: exit;
2916: END IF;
2923: ELSIF l_item_control_rec.serial_control_code <> 1 THEN
2924: u_ind := 0 ;
2925: l_nsrl_qty := 0;
2926: For j in 1 .. l_instances_tbl.count Loop
2927: IF nvl(l_instances_tbl(j).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2928: l_nsrl_qty := l_nsrl_qty + 1;
2929: END IF;
2930: End Loop;
2931: l_rem_qty := l_nsrl_qty;
2930: End Loop;
2931: l_rem_qty := l_nsrl_qty;
2932:
2933: For l in 1 .. l_instances_tbl.count Loop
2934: IF nvl(l_instances_tbl(l).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2935: For m in l_tld_inst_tbl.first .. l_tld_inst_tbl.last Loop
2936: IF nvl(l_tld_inst_tbl(m).processed_flag, 'N') = 'N' THEN
2937: u_ind := u_ind + 1;
2938: IF abs(l_tld_inst_tbl(m).quantity) >= l_rem_qty THEN
2971: p_csi_instance_rec => l_split_nsrc_inst_rec);
2972:
2973: csi_item_instance_pvt.split_item_instance (
2974: p_api_version => 1.0,
2975: p_commit => fnd_api.g_false,
2976: p_init_msg_list => fnd_api.g_true,
2977: p_validation_level => fnd_api.g_valid_level_full,
2978: p_source_instance_rec => l_split_nsrc_inst_rec,
2979: p_quantity1 => l_quantity1 - l_quantity2 ,
2972:
2973: csi_item_instance_pvt.split_item_instance (
2974: p_api_version => 1.0,
2975: p_commit => fnd_api.g_false,
2976: p_init_msg_list => fnd_api.g_true,
2977: p_validation_level => fnd_api.g_valid_level_full,
2978: p_source_instance_rec => l_split_nsrc_inst_rec,
2979: p_quantity1 => l_quantity1 - l_quantity2 ,
2980: p_quantity2 => l_quantity2,
2973: csi_item_instance_pvt.split_item_instance (
2974: p_api_version => 1.0,
2975: p_commit => fnd_api.g_false,
2976: p_init_msg_list => fnd_api.g_true,
2977: p_validation_level => fnd_api.g_valid_level_full,
2978: p_source_instance_rec => l_split_nsrc_inst_rec,
2979: p_quantity1 => l_quantity1 - l_quantity2 ,
2980: p_quantity2 => l_quantity2,
2981: p_copy_ext_attribs => fnd_api.g_true,
2977: p_validation_level => fnd_api.g_valid_level_full,
2978: p_source_instance_rec => l_split_nsrc_inst_rec,
2979: p_quantity1 => l_quantity1 - l_quantity2 ,
2980: p_quantity2 => l_quantity2,
2981: p_copy_ext_attribs => fnd_api.g_true,
2982: p_copy_org_assignments => fnd_api.g_true,
2983: p_copy_parties => fnd_api.g_true,
2984: p_copy_accounts => fnd_api.g_true,
2985: p_copy_asset_assignments => fnd_api.g_true,
2978: p_source_instance_rec => l_split_nsrc_inst_rec,
2979: p_quantity1 => l_quantity1 - l_quantity2 ,
2980: p_quantity2 => l_quantity2,
2981: p_copy_ext_attribs => fnd_api.g_true,
2982: p_copy_org_assignments => fnd_api.g_true,
2983: p_copy_parties => fnd_api.g_true,
2984: p_copy_accounts => fnd_api.g_true,
2985: p_copy_asset_assignments => fnd_api.g_true,
2986: p_copy_pricing_attribs => fnd_api.g_true,
2979: p_quantity1 => l_quantity1 - l_quantity2 ,
2980: p_quantity2 => l_quantity2,
2981: p_copy_ext_attribs => fnd_api.g_true,
2982: p_copy_org_assignments => fnd_api.g_true,
2983: p_copy_parties => fnd_api.g_true,
2984: p_copy_accounts => fnd_api.g_true,
2985: p_copy_asset_assignments => fnd_api.g_true,
2986: p_copy_pricing_attribs => fnd_api.g_true,
2987: p_txn_rec => l_txn_rec,
2980: p_quantity2 => l_quantity2,
2981: p_copy_ext_attribs => fnd_api.g_true,
2982: p_copy_org_assignments => fnd_api.g_true,
2983: p_copy_parties => fnd_api.g_true,
2984: p_copy_accounts => fnd_api.g_true,
2985: p_copy_asset_assignments => fnd_api.g_true,
2986: p_copy_pricing_attribs => fnd_api.g_true,
2987: p_txn_rec => l_txn_rec,
2988: x_new_instance_rec => l_new_nsrc_inst_rec,
2981: p_copy_ext_attribs => fnd_api.g_true,
2982: p_copy_org_assignments => fnd_api.g_true,
2983: p_copy_parties => fnd_api.g_true,
2984: p_copy_accounts => fnd_api.g_true,
2985: p_copy_asset_assignments => fnd_api.g_true,
2986: p_copy_pricing_attribs => fnd_api.g_true,
2987: p_txn_rec => l_txn_rec,
2988: x_new_instance_rec => l_new_nsrc_inst_rec,
2989: x_return_status => l_return_status,
2982: p_copy_org_assignments => fnd_api.g_true,
2983: p_copy_parties => fnd_api.g_true,
2984: p_copy_accounts => fnd_api.g_true,
2985: p_copy_asset_assignments => fnd_api.g_true,
2986: p_copy_pricing_attribs => fnd_api.g_true,
2987: p_txn_rec => l_txn_rec,
2988: x_new_instance_rec => l_new_nsrc_inst_rec,
2989: x_return_status => l_return_status,
2990: x_msg_count => l_msg_count,
2989: x_return_status => l_return_status,
2990: x_msg_count => l_msg_count,
2991: x_msg_data => l_msg_data);
2992:
2993: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2994: debug('csi_item_instance_pvt.split_item_instance raised errors');
2995: raise fnd_api.g_exc_error;
2996: END IF;
2997:
2991: x_msg_data => l_msg_data);
2992:
2993: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2994: debug('csi_item_instance_pvt.split_item_instance raised errors');
2995: raise fnd_api.g_exc_error;
2996: END IF;
2997:
2998: debug('New Instance ID: '||l_new_nsrc_inst_rec.instance_id
2999: ||' New Instance Qty.: '||l_new_nsrc_inst_rec.quantity);
3048: p_csi_instance_rec => l_u_instance_rec);
3049:
3050: csi_item_instance_pub.update_item_instance(
3051: p_api_version => 1.0,
3052: p_commit => fnd_api.g_false,
3053: p_init_msg_list => fnd_api.g_true,
3054: p_validation_level => fnd_api.g_valid_level_full,
3055: p_instance_rec => l_u_instance_rec,
3056: p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3049:
3050: csi_item_instance_pub.update_item_instance(
3051: p_api_version => 1.0,
3052: p_commit => fnd_api.g_false,
3053: p_init_msg_list => fnd_api.g_true,
3054: p_validation_level => fnd_api.g_valid_level_full,
3055: p_instance_rec => l_u_instance_rec,
3056: p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3057: p_party_tbl => l_u_party_tbl,
3050: csi_item_instance_pub.update_item_instance(
3051: p_api_version => 1.0,
3052: p_commit => fnd_api.g_false,
3053: p_init_msg_list => fnd_api.g_true,
3054: p_validation_level => fnd_api.g_valid_level_full,
3055: p_instance_rec => l_u_instance_rec,
3056: p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3057: p_party_tbl => l_u_party_tbl,
3058: p_account_tbl => l_u_party_acct_tbl,
3064: x_return_status => l_return_status,
3065: x_msg_count => l_msg_count,
3066: x_msg_data => l_msg_data );
3067:
3068: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3069: RAISE fnd_api.g_exc_error;
3070: END IF;
3071: End Loop;
3072: END IF;
3065: x_msg_count => l_msg_count,
3066: x_msg_data => l_msg_data );
3067:
3068: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3069: RAISE fnd_api.g_exc_error;
3070: END IF;
3071: End Loop;
3072: END IF;
3073: END IF;
3078: IF l_item_control_rec.serial_control_code = 1 THEN
3079: debug('NON Serialized item and installation details not entered.');
3080: fnd_message.set_name('CSI', 'CSI_INST_DTLS_NOT_ENTERED');
3081: fnd_msg_pub.add;
3082: RAISE fnd_api.g_exc_error;
3083: ELSE
3084: -- serialized item, so figure out the owner's instance
3085: identify_source_instances(
3086: px_mtl_txn_tbl => l_src_mtl_txn_tbl,
3086: px_mtl_txn_tbl => l_src_mtl_txn_tbl,
3087: p_item_control_rec => l_item_control_rec,
3088: x_return_status => l_return_status);
3089:
3090: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3091: RAISE fnd_api.g_exc_error;
3092: END IF;
3093: END IF;
3094:
3087: p_item_control_rec => l_item_control_rec,
3088: x_return_status => l_return_status);
3089:
3090: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3091: RAISE fnd_api.g_exc_error;
3092: END IF;
3093: END IF;
3094:
3095: -- build the process transaction pl/sql tables using the mtl txn info
3107: x_ii_relationships_tbl => l_ii_relationships_tbl,
3108: x_dest_location_rec => l_dest_location_rec,
3109: x_return_status => l_return_status);
3110:
3111: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3112: RAISE fnd_api.g_exc_error;
3113: END IF;
3114:
3115: END IF;
3108: x_dest_location_rec => l_dest_location_rec,
3109: x_return_status => l_return_status);
3110:
3111: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3112: RAISE fnd_api.g_exc_error;
3113: END IF;
3114:
3115: END IF;
3116:
3119: p_sub_type_id => l_txn_rec.txn_sub_type_id,
3120: x_sub_type_rec => l_sub_type_rec,
3121: x_return_status => l_return_status);
3122:
3123: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3124: raise fnd_api.g_exc_error;
3125: END IF;
3126:
3127: /* start of ER 2646086 + RMA for Repair with different party */
3120: x_sub_type_rec => l_sub_type_rec,
3121: x_return_status => l_return_status);
3122:
3123: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3124: raise fnd_api.g_exc_error;
3125: END IF;
3126:
3127: /* start of ER 2646086 + RMA for Repair with different party */
3128: /* Get the value for the source of truth flag */
3150: /* serialized instances. */
3151:
3152: debug('Check if owner needs to be overridden. Return from a different guy.');
3153:
3154: IF nvl(l_instances_tbl(i_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3155: THEN
3156:
3157: l_error_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3158:
3189: END;
3190: EXCEPTION
3191: WHEN no_data_found THEN
3192: --## to seed some error message appropriately
3193: RAISE fnd_api.g_exc_error;
3194: END;
3195:
3196: debug(' Instance ID :'||l_instances_tbl(i_ind).instance_id);
3197: debug(' Internal Party ID :'||l_internal_party_id);
3214: fnd_message.set_token('INSTANCE_ID', l_instances_tbl(i_ind).instance_id );
3215: fnd_message.set_token('OLD_PARTY_ID', l_cur_owner_party_id );
3216: fnd_message.set_token('NEW_PARTY_ID', l_src_order_rec.party_id );
3217: fnd_msg_pub.add;
3218: RAISE fnd_api.g_exc_error;
3219: END IF;
3220:
3221: /* end of ER 2646086 + RMA for Repair with different party */
3222:
3291: l_upd_parties_tbl(1).party_source_table := 'HZ_PARTIES';
3292: -- l_upd_parties_tbl(1).party_id := l_src_order_rec.party_id;
3293: l_upd_parties_tbl(1).relationship_type_code := 'OWNER';
3294: l_upd_parties_tbl(1).contact_flag := 'N';
3295: l_upd_parties_tbl(1).call_contracts := fnd_api.g_false;
3296:
3297: l_upd_pty_accts_tbl(1).ip_account_id := l_owner_acct_ipa_id;
3298: l_upd_pty_accts_tbl(1).instance_party_id := l_owner_pty_ip_id;
3299: l_upd_pty_accts_tbl(1).object_version_number:= l_owner_acct_obj_ver_num;
3299: l_upd_pty_accts_tbl(1).object_version_number:= l_owner_acct_obj_ver_num;
3300: l_upd_pty_accts_tbl(1).parent_tbl_index := 1;
3301: --l_upd_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3302: l_upd_pty_accts_tbl(1).relationship_type_code := 'OWNER';
3303: l_upd_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
3304:
3305: /* Commented the call as part of fix for Bug 2733128. Added call to Update_Item_Instance instead
3306: csi_t_gen_utility_pvt.dump_api_info(
3307: p_pkg_name => 'csi_party_relationships_pub',
3308: p_api_name => 'update_inst_party_relationship');
3309:
3310: csi_party_relationships_pub.update_inst_party_relationship (
3311: p_api_version => 1.0,
3312: p_commit => fnd_api.g_false,
3313: p_init_msg_list => fnd_api.g_true,
3314: p_validation_level => fnd_api.g_valid_level_full,
3315: p_party_tbl => l_upd_parties_tbl,
3316: p_party_account_tbl => l_upd_pty_accts_tbl,
3309:
3310: csi_party_relationships_pub.update_inst_party_relationship (
3311: p_api_version => 1.0,
3312: p_commit => fnd_api.g_false,
3313: p_init_msg_list => fnd_api.g_true,
3314: p_validation_level => fnd_api.g_valid_level_full,
3315: p_party_tbl => l_upd_parties_tbl,
3316: p_party_account_tbl => l_upd_pty_accts_tbl,
3317: p_txn_rec => l_txn_rec,
3310: csi_party_relationships_pub.update_inst_party_relationship (
3311: p_api_version => 1.0,
3312: p_commit => fnd_api.g_false,
3313: p_init_msg_list => fnd_api.g_true,
3314: p_validation_level => fnd_api.g_valid_level_full,
3315: p_party_tbl => l_upd_parties_tbl,
3316: p_party_account_tbl => l_upd_pty_accts_tbl,
3317: p_txn_rec => l_txn_rec,
3318: x_return_status => l_return_status,
3325: p_api_name => 'update_item_instance');
3326:
3327: csi_item_instance_pub.update_item_instance(
3328: p_api_version => 1.0,
3329: p_commit => fnd_api.g_false,
3330: p_init_msg_list => fnd_api.g_true,
3331: p_validation_level => fnd_api.g_valid_level_full,
3332: p_instance_rec => l_chg_instance_rec,
3333: p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
3326:
3327: csi_item_instance_pub.update_item_instance(
3328: p_api_version => 1.0,
3329: p_commit => fnd_api.g_false,
3330: p_init_msg_list => fnd_api.g_true,
3331: p_validation_level => fnd_api.g_valid_level_full,
3332: p_instance_rec => l_chg_instance_rec,
3333: p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
3334: p_party_tbl => l_upd_parties_tbl,
3327: csi_item_instance_pub.update_item_instance(
3328: p_api_version => 1.0,
3329: p_commit => fnd_api.g_false,
3330: p_init_msg_list => fnd_api.g_true,
3331: p_validation_level => fnd_api.g_valid_level_full,
3332: p_instance_rec => l_chg_instance_rec,
3333: p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
3334: p_party_tbl => l_upd_parties_tbl,
3335: p_account_tbl => l_upd_pty_accts_tbl,
3341: x_return_status => l_return_status,
3342: x_msg_count => l_msg_count,
3343: x_msg_data => l_msg_data);
3344:
3345: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3346: RAISE fnd_api.g_exc_error;
3347: END IF;
3348:
3349: debug('Ownership Changed Successfully.');
3342: x_msg_count => l_msg_count,
3343: x_msg_data => l_msg_data);
3344:
3345: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3346: RAISE fnd_api.g_exc_error;
3347: END IF;
3348:
3349: debug('Ownership Changed Successfully.');
3350:
3386: px_mtl_txn_rec => l_mtl_txn_rec,
3387: p_item_control_rec => l_item_control_rec,
3388: x_return_status => l_return_status);
3389:
3390: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3391: RAISE fnd_api.g_exc_error;
3392: END IF;
3393:
3394: IF l_mtl_txn_rec.instance_id = fnd_api.g_miss_num THEN
3387: p_item_control_rec => l_item_control_rec,
3388: x_return_status => l_return_status);
3389:
3390: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3391: RAISE fnd_api.g_exc_error;
3392: END IF;
3393:
3394: IF l_mtl_txn_rec.instance_id = fnd_api.g_miss_num THEN
3395:
3390: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3391: RAISE fnd_api.g_exc_error;
3392: END IF;
3393:
3394: IF l_mtl_txn_rec.instance_id = fnd_api.g_miss_num THEN
3395:
3396: debug('Create a source serialized instance. Looks like first time IB creation.');
3397:
3398: l_crt_instance_rec.instance_id := fnd_api.g_miss_num;
3394: IF l_mtl_txn_rec.instance_id = fnd_api.g_miss_num THEN
3395:
3396: debug('Create a source serialized instance. Looks like first time IB creation.');
3397:
3398: l_crt_instance_rec.instance_id := fnd_api.g_miss_num;
3399: l_crt_instance_rec.instance_number := fnd_api.g_miss_char;
3400: l_crt_instance_rec.inventory_item_id := l_instances_tbl(i_ind).inventory_item_id;
3401: l_crt_instance_rec.inventory_revision := l_instances_tbl(i_ind).inventory_revision;
3402: l_crt_instance_rec.serial_number := l_instances_tbl(i_ind).serial_number;
3395:
3396: debug('Create a source serialized instance. Looks like first time IB creation.');
3397:
3398: l_crt_instance_rec.instance_id := fnd_api.g_miss_num;
3399: l_crt_instance_rec.instance_number := fnd_api.g_miss_char;
3400: l_crt_instance_rec.inventory_item_id := l_instances_tbl(i_ind).inventory_item_id;
3401: l_crt_instance_rec.inventory_revision := l_instances_tbl(i_ind).inventory_revision;
3402: l_crt_instance_rec.serial_number := l_instances_tbl(i_ind).serial_number;
3403: l_crt_instance_rec.mfg_serial_number_flag := 'Y';
3412: l_crt_instance_rec.last_oe_rma_line_id := l_instances_tbl(i_ind).last_oe_rma_line_id;
3413: l_crt_instance_rec.customer_view_flag := 'N';
3414: l_crt_instance_rec.merchant_view_flag := 'Y';
3415: l_crt_instance_rec.object_version_number := 1;
3416: l_crt_instance_rec.call_contracts := fnd_api.g_false;
3417: --fix for bug5086652
3418: l_crt_instance_rec.active_start_date := l_instances_tbl(i_ind).mtl_txn_creation_date;
3419:
3420: l_crt_parties_tbl(1).instance_party_id := fnd_api.g_miss_num;
3416: l_crt_instance_rec.call_contracts := fnd_api.g_false;
3417: --fix for bug5086652
3418: l_crt_instance_rec.active_start_date := l_instances_tbl(i_ind).mtl_txn_creation_date;
3419:
3420: l_crt_parties_tbl(1).instance_party_id := fnd_api.g_miss_num;
3421: l_crt_parties_tbl(1).party_source_table := 'HZ_PARTIES';
3422: l_crt_parties_tbl(1).party_id := l_src_order_rec.party_id;
3423: l_crt_parties_tbl(1).relationship_type_code:= 'OWNER';
3424: l_crt_parties_tbl(1).contact_flag := 'N';
3421: l_crt_parties_tbl(1).party_source_table := 'HZ_PARTIES';
3422: l_crt_parties_tbl(1).party_id := l_src_order_rec.party_id;
3423: l_crt_parties_tbl(1).relationship_type_code:= 'OWNER';
3424: l_crt_parties_tbl(1).contact_flag := 'N';
3425: l_crt_parties_tbl(1).call_contracts := fnd_api.g_false;
3426:
3427: l_crt_pty_accts_tbl(1).ip_account_id := fnd_api.g_miss_num;
3428: l_crt_pty_accts_tbl(1).parent_tbl_index := 1;
3429: l_crt_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3423: l_crt_parties_tbl(1).relationship_type_code:= 'OWNER';
3424: l_crt_parties_tbl(1).contact_flag := 'N';
3425: l_crt_parties_tbl(1).call_contracts := fnd_api.g_false;
3426:
3427: l_crt_pty_accts_tbl(1).ip_account_id := fnd_api.g_miss_num;
3428: l_crt_pty_accts_tbl(1).parent_tbl_index := 1;
3429: l_crt_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3430: l_crt_pty_accts_tbl(1).relationship_type_code := 'OWNER';
3431: l_crt_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
3427: l_crt_pty_accts_tbl(1).ip_account_id := fnd_api.g_miss_num;
3428: l_crt_pty_accts_tbl(1).parent_tbl_index := 1;
3429: l_crt_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3430: l_crt_pty_accts_tbl(1).relationship_type_code := 'OWNER';
3431: l_crt_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
3432:
3433: csi_t_gen_utility_pvt.dump_csi_instance_rec(
3434: p_csi_instance_rec => l_crt_instance_rec);
3435:
3438: p_api_name => 'create_item_instance');
3439:
3440: csi_item_instance_pub.create_item_instance(
3441: p_api_version => 1.0,
3442: p_commit => fnd_api.g_false,
3443: p_init_msg_list => fnd_api.g_true,
3444: p_validation_level => fnd_api.g_valid_level_full,
3445: p_instance_rec => l_crt_instance_rec,
3446: p_party_tbl => l_crt_parties_tbl,
3439:
3440: csi_item_instance_pub.create_item_instance(
3441: p_api_version => 1.0,
3442: p_commit => fnd_api.g_false,
3443: p_init_msg_list => fnd_api.g_true,
3444: p_validation_level => fnd_api.g_valid_level_full,
3445: p_instance_rec => l_crt_instance_rec,
3446: p_party_tbl => l_crt_parties_tbl,
3447: p_account_tbl => l_crt_pty_accts_tbl,
3440: csi_item_instance_pub.create_item_instance(
3441: p_api_version => 1.0,
3442: p_commit => fnd_api.g_false,
3443: p_init_msg_list => fnd_api.g_true,
3444: p_validation_level => fnd_api.g_valid_level_full,
3445: p_instance_rec => l_crt_instance_rec,
3446: p_party_tbl => l_crt_parties_tbl,
3447: p_account_tbl => l_crt_pty_accts_tbl,
3448: p_org_assignments_tbl => l_crt_org_units_tbl,
3453: x_return_status => l_return_status,
3454: x_msg_count => l_msg_count,
3455: x_msg_data => l_msg_data );
3456:
3457: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3458: raise fnd_api.g_exc_error;
3459: END IF;
3460:
3461: l_instances_tbl(i_ind).instance_id := l_crt_instance_rec.instance_id;
3454: x_msg_count => l_msg_count,
3455: x_msg_data => l_msg_data );
3456:
3457: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3458: raise fnd_api.g_exc_error;
3459: END IF;
3460:
3461: l_instances_tbl(i_ind).instance_id := l_crt_instance_rec.instance_id;
3462:
3486: l_i_parties_tbl(i_p_ind).contact_flag := 'N';
3487:
3488: END IF;
3489:
3490: END IF; -- instance_id = fnd_api.g_miss_num
3491:
3492: ELSE -- Non_Serialized Item in INV , serial code = 1
3493: IF nvl(l_instances_tbl(i_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3494: /* Split the instance at source for Non serialized items */
3489:
3490: END IF; -- instance_id = fnd_api.g_miss_num
3491:
3492: ELSE -- Non_Serialized Item in INV , serial code = 1
3493: IF nvl(l_instances_tbl(i_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3494: /* Split the instance at source for Non serialized items */
3495: THEN
3496:
3497: BEGIN
3504:
3505: EXCEPTION
3506: WHEN no_data_found THEN
3507: debug('Failed to retrieve Instance data');
3508: raise fnd_api.g_exc_error;
3509: END;
3510:
3511: IF l_quantity2 > l_instances_tbl(i_ind).quantity THEN -- inst qty > txn qty
3512: IF (l_active_end_date is NOT NULL) AND (l_active_end_date <= sysdate)
3515: -- Contracts shouldn't be called while unexpiring
3516:
3517: csi_process_txn_pvt.unexpire_instance(
3518: p_instance_id => l_instances_tbl(i_ind).instance_id,
3519: p_call_contracts => fnd_api.g_false,
3520: p_transaction_rec => l_txn_rec,
3521: x_return_status => l_return_status);
3522:
3523: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3519: p_call_contracts => fnd_api.g_false,
3520: p_transaction_rec => l_txn_rec,
3521: x_return_status => l_return_status);
3522:
3523: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3524: RAISE fnd_api.g_exc_error;
3525: END IF;
3526: END IF;
3527:
3520: p_transaction_rec => l_txn_rec,
3521: x_return_status => l_return_status);
3522:
3523: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3524: RAISE fnd_api.g_exc_error;
3525: END IF;
3526: END IF;
3527:
3528: l_quantity1 := l_quantity2 - l_instances_tbl(i_ind).quantity ;
3539: p_api_name => 'split_item_instance');
3540:
3541: csi_item_instance_pvt.split_item_instance (
3542: p_api_version => 1.0,
3543: p_commit => fnd_api.g_false,
3544: p_init_msg_list => fnd_api.g_true,
3545: p_validation_level => fnd_api.g_valid_level_full,
3546: p_source_instance_rec => l_split_src_inst_rec,
3547: p_quantity1 => l_quantity1,
3540:
3541: csi_item_instance_pvt.split_item_instance (
3542: p_api_version => 1.0,
3543: p_commit => fnd_api.g_false,
3544: p_init_msg_list => fnd_api.g_true,
3545: p_validation_level => fnd_api.g_valid_level_full,
3546: p_source_instance_rec => l_split_src_inst_rec,
3547: p_quantity1 => l_quantity1,
3548: p_quantity2 => l_quantity2,
3541: csi_item_instance_pvt.split_item_instance (
3542: p_api_version => 1.0,
3543: p_commit => fnd_api.g_false,
3544: p_init_msg_list => fnd_api.g_true,
3545: p_validation_level => fnd_api.g_valid_level_full,
3546: p_source_instance_rec => l_split_src_inst_rec,
3547: p_quantity1 => l_quantity1,
3548: p_quantity2 => l_quantity2,
3549: p_copy_ext_attribs => fnd_api.g_true,
3545: p_validation_level => fnd_api.g_valid_level_full,
3546: p_source_instance_rec => l_split_src_inst_rec,
3547: p_quantity1 => l_quantity1,
3548: p_quantity2 => l_quantity2,
3549: p_copy_ext_attribs => fnd_api.g_true,
3550: p_copy_org_assignments => fnd_api.g_true,
3551: p_copy_parties => fnd_api.g_true,
3552: p_copy_accounts => fnd_api.g_true,
3553: p_copy_asset_assignments => fnd_api.g_true,
3546: p_source_instance_rec => l_split_src_inst_rec,
3547: p_quantity1 => l_quantity1,
3548: p_quantity2 => l_quantity2,
3549: p_copy_ext_attribs => fnd_api.g_true,
3550: p_copy_org_assignments => fnd_api.g_true,
3551: p_copy_parties => fnd_api.g_true,
3552: p_copy_accounts => fnd_api.g_true,
3553: p_copy_asset_assignments => fnd_api.g_true,
3554: p_copy_pricing_attribs => fnd_api.g_true,
3547: p_quantity1 => l_quantity1,
3548: p_quantity2 => l_quantity2,
3549: p_copy_ext_attribs => fnd_api.g_true,
3550: p_copy_org_assignments => fnd_api.g_true,
3551: p_copy_parties => fnd_api.g_true,
3552: p_copy_accounts => fnd_api.g_true,
3553: p_copy_asset_assignments => fnd_api.g_true,
3554: p_copy_pricing_attribs => fnd_api.g_true,
3555: p_txn_rec => l_txn_rec,
3548: p_quantity2 => l_quantity2,
3549: p_copy_ext_attribs => fnd_api.g_true,
3550: p_copy_org_assignments => fnd_api.g_true,
3551: p_copy_parties => fnd_api.g_true,
3552: p_copy_accounts => fnd_api.g_true,
3553: p_copy_asset_assignments => fnd_api.g_true,
3554: p_copy_pricing_attribs => fnd_api.g_true,
3555: p_txn_rec => l_txn_rec,
3556: x_new_instance_rec => l_split_new_inst_rec,
3549: p_copy_ext_attribs => fnd_api.g_true,
3550: p_copy_org_assignments => fnd_api.g_true,
3551: p_copy_parties => fnd_api.g_true,
3552: p_copy_accounts => fnd_api.g_true,
3553: p_copy_asset_assignments => fnd_api.g_true,
3554: p_copy_pricing_attribs => fnd_api.g_true,
3555: p_txn_rec => l_txn_rec,
3556: x_new_instance_rec => l_split_new_inst_rec,
3557: x_return_status => l_return_status,
3550: p_copy_org_assignments => fnd_api.g_true,
3551: p_copy_parties => fnd_api.g_true,
3552: p_copy_accounts => fnd_api.g_true,
3553: p_copy_asset_assignments => fnd_api.g_true,
3554: p_copy_pricing_attribs => fnd_api.g_true,
3555: p_txn_rec => l_txn_rec,
3556: x_new_instance_rec => l_split_new_inst_rec,
3557: x_return_status => l_return_status,
3558: x_msg_count => l_msg_count,
3557: x_return_status => l_return_status,
3558: x_msg_count => l_msg_count,
3559: x_msg_data => l_msg_data);
3560:
3561: IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
3562: debug('csi_item_instance_pvt.split_item_instance raised errors');
3563: raise fnd_api.g_exc_error;
3564: END IF;
3565:
3559: x_msg_data => l_msg_data);
3560:
3561: IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
3562: debug('csi_item_instance_pvt.split_item_instance raised errors');
3563: raise fnd_api.g_exc_error;
3564: END IF;
3565:
3566: IF (l_active_end_date is NOT NULL) AND (l_active_end_date <= sysdate)
3567: THEN -- expire the instance back.
3571: FROM csi_item_instances
3572: WHERE instance_id = l_instances_tbl(i_ind).instance_id;
3573:
3574: l_exp_instance_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3575: l_exp_instance_rec.call_contracts := fnd_api.g_false;
3576: l_exp_instance_rec.active_end_date := sysdate;
3577: l_exp_instance_rec.last_oe_rma_line_id := l_txn_rec.source_line_ref_id;
3578:
3579: csi_t_gen_utility_pvt.dump_api_info(
3584: p_csi_instance_rec => l_exp_instance_rec);
3585:
3586: csi_item_instance_pub.update_item_instance(
3587: p_api_version => 1.0,
3588: p_commit => fnd_api.g_false,
3589: p_init_msg_list => fnd_api.g_true,
3590: p_validation_level => fnd_api.g_valid_level_full,
3591: p_instance_rec => l_exp_instance_rec,
3592: p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3585:
3586: csi_item_instance_pub.update_item_instance(
3587: p_api_version => 1.0,
3588: p_commit => fnd_api.g_false,
3589: p_init_msg_list => fnd_api.g_true,
3590: p_validation_level => fnd_api.g_valid_level_full,
3591: p_instance_rec => l_exp_instance_rec,
3592: p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3593: p_party_tbl => l_u_party_tbl,
3586: csi_item_instance_pub.update_item_instance(
3587: p_api_version => 1.0,
3588: p_commit => fnd_api.g_false,
3589: p_init_msg_list => fnd_api.g_true,
3590: p_validation_level => fnd_api.g_valid_level_full,
3591: p_instance_rec => l_exp_instance_rec,
3592: p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3593: p_party_tbl => l_u_party_tbl,
3594: p_account_tbl => l_u_party_acct_tbl,
3600: x_return_status => l_return_status,
3601: x_msg_count => l_msg_count,
3602: x_msg_data => l_msg_data );
3603:
3604: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3605: RAISE fnd_api.g_exc_error;
3606: END IF;
3607: END IF;
3608:
3601: x_msg_count => l_msg_count,
3602: x_msg_data => l_msg_data );
3603:
3604: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3605: RAISE fnd_api.g_exc_error;
3606: END IF;
3607: END IF;
3608:
3609: l_instances_tbl(i_ind).instance_id := l_split_new_inst_rec.instance_id ;
3667: x_return_status => l_return_status,
3668: x_msg_count => l_msg_count,
3669: x_msg_data => l_msg_data );
3670:
3671: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3672: debug('Process transaction routine failed.');
3673: RAISE fnd_api.g_exc_error;
3674: END IF;
3675:
3669: x_msg_data => l_msg_data );
3670:
3671: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3672: debug('Process transaction routine failed.');
3673: RAISE fnd_api.g_exc_error;
3674: END IF;
3675:
3676: --update the csi txn line details with the processed status
3677:
3723: p_txn_line_rec => l_split_txn_line_rec);
3724: EXCEPTION
3725: WHEN TOO_MANY_ROWS THEN
3726: debug('Multiple RMA split lines found in OM for the RMA Order.');
3727: RAISE fnd_api.g_exc_error;
3728: l_partial_receipt := FALSE;
3729: l_td_found := FALSE;
3730: WHEN OTHERS THEN
3731: l_partial_receipt := FALSE;
3734:
3735: IF l_partial_receipt AND NOT l_td_found THEN
3736: csi_t_txn_details_grp.split_transaction_details(
3737: p_api_version => 1.0,
3738: p_commit => fnd_api.g_false,
3739: p_init_msg_list => fnd_api.g_true,
3740: p_validation_level => fnd_api.g_valid_level_full,
3741: p_src_txn_line_rec => l_txn_line_rec,
3742: px_split_txn_line_rec => l_split_txn_line_rec,
3735: IF l_partial_receipt AND NOT l_td_found THEN
3736: csi_t_txn_details_grp.split_transaction_details(
3737: p_api_version => 1.0,
3738: p_commit => fnd_api.g_false,
3739: p_init_msg_list => fnd_api.g_true,
3740: p_validation_level => fnd_api.g_valid_level_full,
3741: p_src_txn_line_rec => l_txn_line_rec,
3742: px_split_txn_line_rec => l_split_txn_line_rec,
3743: px_line_dtl_tbl => l_line_dtl_tbl,
3736: csi_t_txn_details_grp.split_transaction_details(
3737: p_api_version => 1.0,
3738: p_commit => fnd_api.g_false,
3739: p_init_msg_list => fnd_api.g_true,
3740: p_validation_level => fnd_api.g_valid_level_full,
3741: p_src_txn_line_rec => l_txn_line_rec,
3742: px_split_txn_line_rec => l_split_txn_line_rec,
3743: px_line_dtl_tbl => l_line_dtl_tbl,
3744: x_pty_dtl_tbl => l_pty_dtl_tbl,
3749: x_return_status => l_return_status,
3750: x_msg_count => l_msg_count,
3751: x_msg_data => l_msg_data);
3752:
3753: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3754: debug(l_msg_data);
3755: Debug(' Failed to post Transaction Details to split RMA Line for partial RMA Cases.');
3756: ELSE
3757: Debug(' Transaction Details succefully posted to split RMA Line.');
3759: END IF;
3760: END IF;
3761:
3762: EXCEPTION
3763: WHEN fnd_api.g_exc_error THEN
3764:
3765: rollback to rma_receipt;
3766:
3767: x_return_status := fnd_api.g_ret_sts_error;
3763: WHEN fnd_api.g_exc_error THEN
3764:
3765: rollback to rma_receipt;
3766:
3767: x_return_status := fnd_api.g_ret_sts_error;
3768: l_error_rec.error_text := csi_t_gen_utility_pvt.dump_error_stack;
3769: debug('Error:(E) '||l_error_rec.error_text);
3770: px_trx_error_rec := l_error_rec;
3771:
3771:
3772: WHEN others THEN
3773: rollback to rma_receipt;
3774:
3775: x_return_status := fnd_api.g_ret_sts_unexp_error;
3776: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3777: fnd_message.set_token('API_NAME',l_api_name);
3778: fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 540));
3779: fnd_msg_pub.add;
3791: x_return_status OUT NOCOPY varchar2)
3792: IS
3793: BEGIN
3794:
3795: x_return_status := fnd_api.g_ret_sts_success;
3796:
3797: BEGIN
3798:
3799: SELECT transaction_id,
3811: WHEN no_data_found THEN
3812: fnd_message.set_name('CSI', 'CSI_INT_MTL_TXN_ID_INVALID');
3813: fnd_message.set_token('MTL_TXN_ID', p_transaction_id);
3814: fnd_msg_pub.add;
3815: RAISE fnd_api.g_exc_error;
3816: END;
3817:
3818: BEGIN
3819:
3835: null;
3836: END;
3837:
3838: EXCEPTION
3839: WHEN fnd_api.g_exc_error THEN
3840: x_error_message := fnd_msg_pub.get;
3841: x_return_status := fnd_api.g_ret_sts_error;
3842: END get_rma_info;
3843:
3837:
3838: EXCEPTION
3839: WHEN fnd_api.g_exc_error THEN
3840: x_error_message := fnd_msg_pub.get;
3841: x_return_status := fnd_api.g_ret_sts_error;
3842: END get_rma_info;
3843:
3844:
3845: PROCEDURE decode_message (
3850: x_return_status OUT NOCOPY VARCHAR2)
3851: IS
3852:
3853: l_api_name VARCHAR2(100):= 'csi_wip_trxs_pkg.decode_message';
3854: l_fnd_unexpected VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
3855: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3856: l_mtl_txn_id number;
3857:
3858: BEGIN
3851: IS
3852:
3853: l_api_name VARCHAR2(100):= 'csi_wip_trxs_pkg.decode_message';
3854: l_fnd_unexpected VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
3855: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3856: l_mtl_txn_id number;
3857:
3858: BEGIN
3859:
3858: BEGIN
3859:
3860: xnp_xml_utils.decode(P_Msg_Text, 'MTL_TRANSACTION_ID', l_mtl_txn_id);
3861:
3862: IF nvl(l_mtl_txn_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3863:
3864: fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
3865: fnd_message.set_token('MESSAGE',
3866: 'xnp_xml_utils.decode failed for '||p_msg_header.message_id);
3865: fnd_message.set_token('MESSAGE',
3866: 'xnp_xml_utils.decode failed for '||p_msg_header.message_id);
3867: fnd_msg_pub.add;
3868:
3869: RAISE fnd_api.g_exc_error;
3870: END IF;
3871:
3872: get_rma_info(
3873: p_transaction_id => l_mtl_txn_id,
3874: x_mtl_trx_type => x_mtl_trx_rec,
3875: x_error_message => x_error_message,
3876: x_return_status => l_return_status);
3877:
3878: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3879: RAISE fnd_api.g_exc_error;
3880: END IF;
3881:
3882: EXCEPTION
3875: x_error_message => x_error_message,
3876: x_return_status => l_return_status);
3877:
3878: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3879: RAISE fnd_api.g_exc_error;
3880: END IF;
3881:
3882: EXCEPTION
3883:
3880: END IF;
3881:
3882: EXCEPTION
3883:
3884: WHEN fnd_api.g_exc_error THEN
3885: x_error_message := csi_t_gen_utility_pvt.dump_error_stack;
3886: x_return_status := fnd_api.g_ret_sts_error;
3887:
3888: WHEN others THEN
3882: EXCEPTION
3883:
3884: WHEN fnd_api.g_exc_error THEN
3885: x_error_message := csi_t_gen_utility_pvt.dump_error_stack;
3886: x_return_status := fnd_api.g_ret_sts_error;
3887:
3888: WHEN others THEN
3889: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3890: fnd_message.set_token('API_NAME',l_api_name);