[Home] [Help]
81: x_return_status IN OUT NOCOPY VARCHAR2)
82: RETURN VARCHAR2;
83:
84: PROCEDURE setup_dnb_data(
85: p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
86: x_to_id IN OUT NOCOPY NUMBER,
87: p_batch_party_id IN NUMBER
88: );
89:
500: FND_MSG_PUB.initialize;
501: init_globals;
502:
503: --Initialize API return status to success.
504: l_glob_return_status := FND_API.G_RET_STS_SUCCESS;
505: l_return_status := FND_API.G_RET_STS_SUCCESS;
506:
507:
508: open get_batch_party_id_csr;
501: init_globals;
502:
503: --Initialize API return status to success.
504: l_glob_return_status := FND_API.G_RET_STS_SUCCESS;
505: l_return_status := FND_API.G_RET_STS_SUCCESS;
506:
507:
508: open get_batch_party_id_csr;
509: fetch get_batch_party_id_csr into l_batch_pid;
516: ELSE
517: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
518: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
519: FND_MSG_PUB.ADD;
520: RAISE FND_API.G_EXC_ERROR;
521: END IF;
522:
523: -- Open the batch cursor and fetch batch details
524: OPEN c_batch(l_batch_id);
527: IF (c_batch%NOTFOUND) THEN
528: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
529: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
530: FND_MSG_PUB.ADD;
531: RAISE FND_API.G_EXC_ERROR;
532: END IF;
533:
534: -- If batch already complete error out
535: IF (l_batch_status = 'COMPLETE') THEN
535: IF (l_batch_status = 'COMPLETE') THEN
536: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_COMPLETE');
537: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
538: FND_MSG_PUB.ADD;
539: RAISE FND_API.G_EXC_ERROR;
540: END IF;
541: CLOSE c_batch;
542:
543: -- bug 4865280 :check for internal external party types. Veto merge if both party types are present in batch
551: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_INTERNAL_PARTY_IND');
552: FND_MESSAGE.SET_TOKEN('PARTY_INT', l_int_party);
553: FND_MESSAGE.SET_TOKEN('PARTY_EXT', l_ext_party);
554: FND_MSG_PUB.ADD;
555: RAISE FND_API.G_EXC_ERROR;
556: END IF;
557:
558: HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
559: p_dup_set_id => null,
557:
558: HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
559: p_dup_set_id => null,
560: p_merge_batch_id => l_batch_id,
561: p_init_msg_list => FND_API.G_FALSE,
562: p_reject_req_flag => 'Y',
563: x_return_status => l_ret_status,
564: x_msg_count => l_msg_count,
565: x_msg_data => l_msg_data);
565: x_msg_data => l_msg_data);
566:
567: /* error messages have been pushed into message stack in above procedure */
568: IF l_ret_status = 'E' THEN
569: RAISE FND_API.G_EXC_ERROR;
570: ELSIF l_ret_status = 'U' THEN
571: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572: END IF;
573:
567: /* error messages have been pushed into message stack in above procedure */
568: IF l_ret_status = 'E' THEN
569: RAISE FND_API.G_EXC_ERROR;
570: ELSIF l_ret_status = 'U' THEN
571: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572: END IF;
573:
574: -- check party merge DSS - update party privilege.
575: hz_dup_pvt.party_merge_dss_check(p_merge_batch_id => l_batch_id,
579: x_msg_data => l_msg_data);
580:
581: /* error messages have been pushed into message stack in above procedure */
582: IF l_ret_status = 'E' THEN
583: RAISE FND_API.G_EXC_ERROR;
584: ELSIF l_ret_status = 'U' THEN
585: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586: END IF;
587:
581: /* error messages have been pushed into message stack in above procedure */
582: IF l_ret_status = 'E' THEN
583: RAISE FND_API.G_EXC_ERROR;
584: ELSIF l_ret_status = 'U' THEN
585: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586: END IF;
587:
588: -- Disable DSS in party merge for sub entities. Will enabled it at the end of the merge.
589: l_dss_orig_prof_val := NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N');
654:
655: -- Lock HZ_MERGE_BATCH, HZ_MERGE_PARTIES and HZ_MERGE_PARTY_DETAILS records
656: log ('.... Locking batch for execution');
657: lock_batch(p_batch_id, l_return_status);
658: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659: ROLLBACK to batch_merge;
660: RAISE FND_API.G_EXC_ERROR;
661: END IF;
662:
656: log ('.... Locking batch for execution');
657: lock_batch(p_batch_id, l_return_status);
658: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659: ROLLBACK to batch_merge;
660: RAISE FND_API.G_EXC_ERROR;
661: END IF;
662:
663:
664: -----Pre-Merge for all the distict merge_to parties where the parties are
708: LOOP
709: FND_MSG_PUB.initialize;
710:
711: -- Initialize return status and error buffer
712: l_return_status := FND_API.G_RET_STS_SUCCESS;
713: l_error_msg := '';
714:
715: -- Fetch the merge party details
716: FETCH c_batch_details INTO l_batch_party_id, l_merge_type, l_from_party_id,
732: IF c_dict_id%NOTFOUND THEN
733: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
734: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
735: FND_MSG_PUB.ADD;
736: RAISE FND_API.G_EXC_ERROR;
737: END IF;
738: CLOSE c_dict_id;
739:
740: log('');
743: g_num_sub_entities :=-1;
744:
745: --4307667
746: IF (HZ_PARTY_USG_ASSIGNMENT_PVT.allow_party_merge('T',l_from_party_id,l_to_party_id,l_msg_count,l_error_msg) <> 'Y') THEN
747: l_return_status := FND_API.G_RET_STS_ERROR;
748: raise FND_API.G_EXC_ERROR;
749: END IF;
750:
751: -- Call the recursive merge procedure performing this merge
744:
745: --4307667
746: IF (HZ_PARTY_USG_ASSIGNMENT_PVT.allow_party_merge('T',l_from_party_id,l_to_party_id,l_msg_count,l_error_msg) <> 'Y') THEN
747: l_return_status := FND_API.G_RET_STS_ERROR;
748: raise FND_API.G_EXC_ERROR;
749: END IF;
750:
751: -- Call the recursive merge procedure performing this merge
752: do_merge(p_batch_party_id =>l_batch_party_id,
773: IF c_dict_id%NOTFOUND THEN
774: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
775: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
776: FND_MSG_PUB.ADD;
777: RAISE FND_API.G_EXC_ERROR;
778: END IF;
779: CLOSE c_dict_id;
780:
781: -- Loop through the merge party sites
808: x_error_msg =>l_error_msg,
809: x_return_status =>l_return_status);
810:
811: END IF;
812: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
813: END LOOP;
814: CLOSE c_batch_party_sites;
815:
816: --if the prev. merge was success only then do the next step
813: END LOOP;
814: CLOSE c_batch_party_sites;
815:
816: --if the prev. merge was success only then do the next step
817: if l_return_status = FND_API.G_RET_STS_SUCCESS then
818: OPEN c_dict_id(l_rule_set_name,'HZ_CONTACT_POINTS');
819: FETCH c_dict_id INTO l_dict_id;
820: IF c_dict_id%NOTFOUND THEN
821: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
820: IF c_dict_id%NOTFOUND THEN
821: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
822: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_CONTACT_POINTS');
823: FND_MSG_PUB.ADD;
824: RAISE FND_API.G_EXC_ERROR;
825: END IF;
826: CLOSE c_dict_id;
827:
828: -- Loop through the merge contact points
857: x_error_msg =>l_error_msg,
858: x_return_status =>l_return_status);
859:
860: END IF;
861: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
862: END LOOP;
863: CLOSE c_batch_contact_points;
864:
865: end if; --l_return status
866:
867:
868:
869: ---if the prev. merge was success only then do the next step
870: if l_return_status = FND_API.G_RET_STS_SUCCESS then
871: OPEN c_dict_id(l_rule_set_name,'HZ_PARTY_RELATIONSHIPS');
872: FETCH c_dict_id INTO l_dict_id;
873: IF c_dict_id%NOTFOUND THEN
874: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
873: IF c_dict_id%NOTFOUND THEN
874: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
875: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_RELATIONSHIPS');
876: FND_MSG_PUB.ADD;
877: RAISE FND_API.G_EXC_ERROR;
878: END IF;
879: CLOSE c_dict_id;
880:
881: -- Loop through the merge relationships
933: x_error_msg =>l_error_msg,
934: x_return_status =>l_return_status);
935:
936: END IF;
937: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
938: END LOOP;
939: CLOSE c_batch_relationships;
940: end if; --l_return_status
941:
941:
942: ELSE --type of merge
943: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_TYPE');
944: FND_MSG_PUB.ADD;
945: RAISE FND_API.G_EXC_ERROR;
946: END IF; --type of merge
947:
948: -- If the party was successfully merged, update merge status to 'DONE'
949: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
945: RAISE FND_API.G_EXC_ERROR;
946: END IF; --type of merge
947:
948: -- If the party was successfully merged, update merge status to 'DONE'
949: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
950:
951: store_merge_history(null,null,null,null,null,null,null,null,null,'Y');
952: -- If delete not vetoed, perform delete
953: IF g_merge_delete_flag = 'Y' THEN
985: END LOOP;
986: CLOSE c_batch_details;
987:
988: if l_pre_merge_type <> 'SAME_PARTY_MERGE' AND
989: l_return_status = FND_API.G_RET_STS_SUCCESS then
990:
991: /* Merge all to records that are getting merged into the transferred from records */
992: FOR TO_RECORDS IN (
993: SELECT mp.batch_party_id, merge_from_entity_id, merge_to_entity_id, ENTITY_NAME
1007: p_preview=>p_preview,
1008: p_log_padding=>' ',
1009: x_error_msg=>l_error_msg,
1010: x_return_status=>l_return_status);
1011: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
1012: END LOOP;
1013:
1014: IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1015: BEGIN
1010: x_return_status=>l_return_status);
1011: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
1012: END LOOP;
1013:
1014: IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1015: BEGIN
1016:
1017: SELECT 1 INTO l_tmp
1018: FROM HZ_MERGE_ENTITY_ATTRIBUTES
1033: l_party_type,
1034: l_return_status);
1035:
1036: ---Bug 2723616 raise the message passed by profile attr API
1037: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1038: l_msg_data := logerror;
1039: END IF;
1040:
1041:
1044: NULL;
1045: WHEN OTHERS THEN
1046: FND_FILE.put_line(FND_FILE.log,'l_return_status ' || l_return_status);
1047: FND_FILE.put_line(FND_FILE.log,'Error ' || SQLERRM);
1048: l_return_status:= FND_API.G_RET_STS_ERROR;
1049: END;
1050: END IF;
1051: END IF;
1052:
1050: END IF;
1051: END IF;
1052:
1053: --4114041
1054: IF (l_glob_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1055: l_glob_return_status := l_return_status;
1056: END IF;
1057:
1058: --If merge mode is not preview
1056: END IF;
1057:
1058: --If merge mode is not preview
1059: IF p_preview <> 'Y'AND
1060: l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1061: null;
1062: -- COMMIT; Should not commit here, need to run batch merge procs after this. 4634891
1063: ELSE
1064: ROLLBACK to merge_group;
1080: FETCH batch_merge_procedures INTO l_merge_dict_id, l_sub_entity_name, l_proc_name;
1081: EXIT WHEN batch_merge_procedures%NOTFOUND;
1082: exec_merge(l_sub_entity_name,l_proc_name,p_batch_id,g_request_id,l_return_status);
1083:
1084: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1085: outandlog('Executed batch merge procedure '||l_proc_name||' successfully');
1086: ELSE
1087: l_glob_return_status := l_return_status;
1088: ROLLBACK TO batch_merge;
1113:
1114: END IF;
1115:
1116: -- Check if the whole batch was successfully executed
1117: IF l_glob_return_status = FND_API.G_RET_STS_SUCCESS THEN
1118: outandlog('.... Merge batch successfully executed');
1119:
1120: -- If not preview mode update batch status to COMPLETE
1121: IF p_preview <> 'Y' then
1247: --4634891
1248:
1249:
1250: EXCEPTION
1251: WHEN FND_API.G_EXC_ERROR THEN
1252: --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1253: IF p_preview = 'Y' AND l_batch_merge_spt THEN
1254: ROLLBACK to batch_merge;
1255: ELSE
1271: -1,-1, -1,null,null,
1272: -1, 'Error', errbuf);
1273: store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1274: -- FND_FILE.close;
1275: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1276: --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1277: IF p_preview = 'Y' AND l_batch_merge_spt THEN
1278: ROLLBACK to batch_merge;
1279: ELSE
1353: IF c_dict_id%NOTFOUND THEN
1354: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1355: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
1356: FND_MSG_PUB.ADD;
1357: RAISE FND_API.G_EXC_ERROR;
1358: END IF;
1359: CLOSE c_dict_id;
1360:
1361: log('');
1480: IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1481: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1482: FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
1483: FND_MSG_PUB.ADD;
1484: RAISE FND_API.G_EXC_ERROR;
1485: END IF;
1486: CLOSE c_dict_details;
1487:
1488:
1487:
1488:
1489: l_from_rec_desc := get_record_desc(p_from_id,p_entity_name, l_pk_column_name,
1490: l_desc_column_name, x_return_status);
1491: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1492:
1493: -- Write a log message
1494: log(p_log_padding|| l_from_rec_desc, FALSE);
1495:
1505: p_batch_party_id,
1506: x_return_status);
1507:
1508: -- Check if the merge procedure returned an error
1509: IF (x_return_status = FND_API.G_RET_STS_SUCCESS OR
1510: x_return_status = 'N') THEN
1511: -- Log the merged records
1512:
1513: -- If the to_id is different from from_id .. then the operation performed
1511: -- Log the merged records
1512:
1513: -- If the to_id is different from from_id .. then the operation performed
1514: -- is a merge
1515: IF ((p_to_id IS NOT NULL AND p_to_id <> FND_API.G_MISS_NUM)
1516: AND p_to_id <> p_from_id) THEN
1517: -- Store in the history and log
1518:
1519: IF (p_to_id <> 0) THEN
1524: ELSE
1525: l_op := 'Merge';
1526: END IF;
1527:
1528: x_return_status := FND_API.G_RET_STS_SUCCESS;
1529:
1530: l_to_rec_desc := get_record_desc(p_to_id,p_entity_name, l_pk_column_name,
1531: l_desc_column_name, x_return_status);
1532: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1528: x_return_status := FND_API.G_RET_STS_SUCCESS;
1529:
1530: l_to_rec_desc := get_record_desc(p_to_id,p_entity_name, l_pk_column_name,
1531: l_desc_column_name, x_return_status);
1532: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1533:
1534: IF p_entity_name = 'HZ_PARTY_SITES' AND l_mand <> 'C' THEN
1535: BEGIN
1536: SELECT mandatory_merge INTO l_mand
1688: l_merge_dict_id, 'Error', x_error_msg);
1689: log('Error Entity Name: '||l_sub_entity_name);
1690: log('Error FK Column: '||l_sub_fk_column_name);
1691:
1692: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1693: RETURN;
1694: END IF;
1695:
1696: --log('fk data type: '||l_sub_fk_column_type);
1770: -1,p_from_id, p_to_id,null,null,
1771: l_merge_dict_id, 'Error', x_error_msg);
1772: log('Error Entity Name: '||l_sub_entity_name);
1773: log('Error FK Column: '||l_sub_fk_column_name);
1774: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1775: RETURN;
1776: END IF;
1777: END;
1778:
1788: IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1789: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1790: FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1791: FND_MSG_PUB.ADD;
1792: RAISE FND_API.G_EXC_ERROR;
1793: END IF;
1794: CLOSE c_dict_details;
1795: log(p_log_padding || ' '|| l_desc);
1796:
1809: l_desc_column_name_b, x_return_status);
1810: log(p_log_padding || ' ' || l_from_rec_desc, FALSE);
1811: END IF;
1812:
1813: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1814:
1815: IF rownumber=1 OR l_bulk_flag IS NULL OR l_bulk_flag <> 'Y' THEN
1816: -- Execute the merge procedure for the entity
1817: exec_merge_r(l_sub_entity_name,
1826: x_return_status);
1827: END IF;
1828:
1829: -- Check if the merge procedure returned an error
1830: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1831: IF p_from_id <> p_to_id THEN
1832: store_merge_history(p_batch_party_id,-1,
1833: -1,p_from_id, p_to_id,l_from_rec_desc, null, p_dict_id,
1834: 'Transfer');
1907: IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1908: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1909: FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1910: FND_MSG_PUB.ADD;
1911: RAISE FND_API.G_EXC_ERROR;
1912: END IF;
1913: CLOSE c_dict_details;
1914: END IF;
1915:
1912: END IF;
1913: CLOSE c_dict_details;
1914: END IF;
1915:
1916: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1917: IF rownumber=1 THEN
1918:
1919: -- Execute the merge procedure for the entity
1920: exec_merge(l_sub_entity_name,
1930: log(p_log_padding || ' Bulk merge completed. Transferred '|| l_fk_column_name_b ||' from '|| p_from_id ||' to '||p_to_id);
1931: END IF;
1932:
1933: -- Check if the merge procedure returned an error
1934: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1935: -- Write to log file
1936: store_merge_history(p_batch_party_id, l_sub_pk_value ,
1937: null,p_from_id, p_to_id,null,
1938: null, l_merge_dict_id, 'Transfer');
1999: x_error_msg => x_error_msg,
2000: x_return_status => x_return_status);
2001:
2002: -- If not successful abort merge and return
2003: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2004: RETURN;
2005: END IF;
2006: END LOOP;
2007: END IF;
2028: -1,p_from_id, p_to_id,null,null,
2029: l_merge_dict_id, 'Error', x_error_msg);
2030: log('Error Entity Name: '||l_sub_entity_name);
2031: log('Error FK Column: '||l_sub_fk_column_name);
2032: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2033: END do_merge;
2034:
2035: /*------------------------------------------------------------------------------
2036: | Procedure to lock HZ_MERGE_BATCH, HZ_MERGE_PARTIES and HZ_MERGE_PARTY_DETAILS
2060: WHEN OTHERS THEN
2061: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_BATCH_LOCK_ERROR');
2062: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2063: FND_MSG_PUB.ADD;
2064: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2065: END lock_batch;
2066:
2067: /*---------------------------------------------------------------------------------------
2068: | Procedure to recursively lock all records for entities defined in the merge dictionary
2123: lockstr:=null;
2124: ELSIF SQLCODE=-942 THEN
2125: lockstr:=null;
2126: ELSE
2127: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2128: END IF;
2129: END;
2130:
2131: IF lockstr IS NULL THEN
2176: lock_records(l_sub_ent_name, l_sub_pkcol, l_sub_fkcol, '(' ||lockstr
2177: ||')', l_sub_joincl,p_rule_set_name, x_return_status);
2178: END IF;
2179:
2180: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2181: RETURN;
2182: END IF;
2183:
2184: END LOOP;
2188: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_LOCK_ERROR');
2189: FND_MESSAGE.SET_TOKEN('ENTITY', p_entity_name);
2190: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM || ' : ' || lockstr || ' subent ' || l_sub_ent_name);
2191: FND_MSG_PUB.ADD;
2192: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193: END lock_records;
2194:
2195: /*------------------------------------------------------------------------------
2196: | Procedure to delete the merged records for a batch party
2250: WHEN OTHERS THEN
2251: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_DELETE_ERROR');
2252: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2253: FND_MSG_PUB.ADD;
2254: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2255: END delete_merged_records;
2256:
2257: /*-------------------------------------------------------------------
2258: | Function to fetch a record description
2297: FND_MESSAGE.SET_NAME('AR', 'HZ_REC_DESC_ERROR');
2298: FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
2299: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2300: FND_MSG_PUB.ADD;
2301: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2302: RETURN NULL;
2303: END get_record_desc;
2304:
2305: /*------------------------------------------------------------------------------
2334: FND_MESSAGE.SET_NAME('AR', 'HZ_REC_DESC_ERROR');
2335: FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
2336: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2337: FND_MSG_PUB.ADD;
2338: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2339: RETURN NULL;
2340: END get_record_desc_r;
2341:
2342:
2361: r NUMBER;
2362:
2363: BEGIN
2364:
2365: l_return_status := FND_API.G_RET_STS_SUCCESS;
2366: IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2367: IF g_cur_proc_name IS NOT NULL THEN
2368: dbms_sql.close_cursor(g_proc_cursor);
2369: END IF;
2394:
2395: x_return_status := l_return_status;
2396:
2397: /*
2398: l_return_status := FND_API.G_RET_STS_SUCCESS;
2399: -- Create a dynamic SQL block to execute the merge procedure
2400: plsql_block := 'BEGIN '||
2401: p_proc_name||'(:p_entity_name, :from_id,'||
2402: ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2422: log('');
2423: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2424: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2425: FND_MSG_PUB.ADD;
2426: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2427: END exec_merge;
2428:
2429: /*-----------------------------------------------------------------
2430: | Procedure to execute the merge procedure using Dynamic SQL
2447: r NUMBER;
2448:
2449: BEGIN
2450:
2451: l_return_status := FND_API.G_RET_STS_SUCCESS;
2452: IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2453: IF g_cur_proc_name IS NOT NULL THEN
2454: dbms_sql.close_cursor(g_proc_cursor);
2455: END IF;
2480:
2481: x_return_status := l_return_status;
2482:
2483: /*
2484: l_return_status := FND_API.G_RET_STS_SUCCESS;
2485: -- Create a dynamic SQL block to execute the merge procedure
2486: plsql_block := 'BEGIN '||
2487: p_proc_name||'(:p_entity_name, :from_id,'||
2488: ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2508: log('');
2509: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2510: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2511: FND_MSG_PUB.ADD;
2512: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2513: END exec_merge_r;
2514:
2515: --bug 4634891 created overloaded procedure exec_merge
2516: /*------------------------------------------------------------------
2529: r NUMBER;
2530:
2531: BEGIN
2532:
2533: l_return_status := FND_API.G_RET_STS_SUCCESS;
2534: IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2535: IF g_cur_proc_name IS NOT NULL THEN
2536: dbms_sql.close_cursor(g_proc_cursor);
2537: END IF;
2565: log('');
2566: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2567: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2568: FND_MSG_PUB.ADD;
2569: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2570: END exec_merge;
2571:
2572: /*------------------------------------------------------------------------
2573: | Procedure to store merge history record
2637: END IF;
2638:
2639: H_Counter := H_Counter+1;
2640: H_batch_party_id(H_Counter) := p_batch_party_id;
2641: IF p_from_id=FND_API.G_MISS_NUM THEN
2642: H_from_id(H_Counter) := null;
2643: ELSE
2644: H_from_id(H_Counter) := p_from_id;
2645: END IF;
2642: H_from_id(H_Counter) := null;
2643: ELSE
2644: H_from_id(H_Counter) := p_from_id;
2645: END IF;
2646: IF p_to_id=FND_API.G_MISS_NUM THEN
2647: H_to_id(H_Counter) := null;
2648: ELSE
2649: H_to_id(H_Counter) := p_to_id;
2650: END IF;
2647: H_to_id(H_Counter) := null;
2648: ELSE
2649: H_to_id(H_Counter) := p_to_id;
2650: END IF;
2651: IF p_from_fk_id=FND_API.G_MISS_NUM THEN
2652: H_from_fk_id(H_Counter) := null;
2653: ELSE
2654: H_from_fk_id(H_Counter) := p_from_fk_id;
2655: END IF;
2652: H_from_fk_id(H_Counter) := null;
2653: ELSE
2654: H_from_fk_id(H_Counter) := p_from_fk_id;
2655: END IF;
2656: IF p_to_fk_id=FND_API.G_MISS_NUM THEN
2657: H_to_fk_id(H_Counter) := null;
2658: ELSE
2659: H_to_fk_id(H_Counter) := p_to_fk_id;
2660: END IF;
2667: WHEN OTHERS THEN
2668: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2669: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2670: FND_MSG_PUB.ADD;
2671: RAISE FND_API.G_EXC_ERROR;
2672: END store_merge_history;
2673:
2674: /*---------------------------------------------------------------------------
2675: | Procedure to store the merge procedure in the log table.
2738: END IF;
2739:
2740: I_Counter := I_Counter+1;
2741: I_batch_party_id(I_Counter) := p_batch_party_id;
2742: IF p_from_id=FND_API.G_MISS_NUM THEN
2743: I_from_id(I_Counter) := null;
2744: ELSE
2745: I_from_id(I_Counter) := p_from_id;
2746: END IF;
2743: I_from_id(I_Counter) := null;
2744: ELSE
2745: I_from_id(I_Counter) := p_from_id;
2746: END IF;
2747: IF p_to_id=FND_API.G_MISS_NUM THEN
2748: I_to_id(I_Counter) := null;
2749: ELSE
2750: I_to_id(I_Counter) := p_to_id;
2751: END IF;
2748: I_to_id(I_Counter) := null;
2749: ELSE
2750: I_to_id(I_Counter) := p_to_id;
2751: END IF;
2752: IF p_from_fk_id=FND_API.G_MISS_NUM THEN
2753: I_from_fk_id(I_Counter) := null;
2754: ELSE
2755: I_from_fk_id(I_Counter) := p_from_fk_id;
2756: END IF;
2753: I_from_fk_id(I_Counter) := null;
2754: ELSE
2755: I_from_fk_id(I_Counter) := p_from_fk_id;
2756: END IF;
2757: IF p_to_fk_id=FND_API.G_MISS_NUM THEN
2758: I_to_fk_id(I_Counter) := null;
2759: ELSE
2760: I_to_fk_id(I_Counter) := p_to_fk_id;
2761: END IF;
2772: ROLLBACK; -- bug 3947633
2773: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2774: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2775: FND_MSG_PUB.ADD;
2776: RAISE FND_API.G_EXC_ERROR;
2777: END store_merge_log;
2778:
2779: /*---------------------------------------------------------------------------
2780: | Procedure to write a message to the out NOCOPY file
2923: IF (pre_batch%NOTFOUND) THEN
2924: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
2925: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
2926: FND_MSG_PUB.ADD;
2927: RAISE FND_API.G_EXC_ERROR;
2928: END IF;
2929: CLOSE pre_batch;
2930:
2931: --Initialise the log variables
2937:
2938: ----------Pre merge for HZ_PARTY_SITES ---------------
2939: OPEN party_sites_for_pre_merge(p_to_party_id , p_batch_id);
2940: LOOP
2941: l_return_status := FND_API.G_RET_STS_SUCCESS;
2942: FETCH party_sites_for_pre_merge INTO l_merge_to_entity_id, l_from_party_id, l_batch_party_id;
2943: EXIT WHEN party_sites_for_pre_merge%NOTFOUND;
2944: l_new_party_site_id := l_merge_to_entity_id;
2945: IF l_merge_to_entity_id IS NOT NULL THEN
2964: x_return_status => l_return_status);
2965:
2966:
2967: -- Check if the merge procedure returned an error
2968: IF (l_return_status = FND_API.G_RET_STS_SUCCESS OR
2969: l_return_status = 'N' ) THEN
2970: IF l_new_party_site_id <> 0 THEN
2971:
2972: -- Write to log file
2975: log('Pre Merge: '|| l_from_rec_desc ||'discarded (DNB data) ');
2976: END IF;
2977:
2978: ELSE
2979: RAISE FND_API.G_EXC_ERROR;
2980: END IF;
2981:
2982: -- set the party sites status to A so that it is picked in merge that follows
2983: --the hz_merge_pkg.do_party_site_merge sets it to 'M' in pre-merge
3011:
3012: END IF;
3013: END LOOP;
3014:
3015: l_return_status := FND_API.G_RET_STS_SUCCESS;
3016: ----------Pre merge for HZ_RELATIONSHIPS ---------------
3017:
3018: --Initialise the log variables
3019: l_from_rec_desc := null;
3067:
3068: l_from_rec_desc := hz_merge_util.get_party_reln_description(l_merge_to_entity_id);
3069:
3070: -- Check if the merge procedure returned an error
3071: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3072: -- Write to log file
3073: store_merge_history(l_batch_party_id, l_merge_to_entity_id ,
3074: l_merge_to_entity_id,l_from_party_id, p_to_party_id,l_from_rec_desc,
3075: null, l_merge_dict_id, 'Transfer');
3085: store_merge_log(l_batch_party_id, l_merge_to_entity_id,
3086: l_merge_to_entity_id,l_from_party_id, p_to_party_id,
3087: l_from_rec_desc,null,
3088: l_merge_dict_id, 'Error', logerror);
3089: RAISE FND_API.G_EXC_ERROR;
3090: END IF;
3091: END IF; --l_proc_name
3092: END IF; --l_merge_to_entity_id
3093:
3114: BEGIN
3115: FND_MSG_PUB.Reset;
3116:
3117: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3118: l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3119: END LOOP;
3120: IF (SQLERRM IS NOT NULL) THEN
3121: l_msg_data := l_msg_data || SQLERRM;
3122: END IF;
3138:
3139: PROCEDURE get_merge_to_record_id
3140: (
3141: p_api_version IN NUMBER,
3142: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
3143: p_record_id IN NUMBER,
3144: p_entity_name IN VARCHAR2,
3145: x_is_merged OUT NOCOPY VARCHAR2,
3146: x_merge_to_record_id OUT NOCOPY NUMBER,
3164:
3165: BEGIN
3166:
3167: --Standard call to check for call compatibility.
3168: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3169: l_api_name, G_PKG_NAME) THEN
3170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3171: END IF;
3172:
3166:
3167: --Standard call to check for call compatibility.
3168: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3169: l_api_name, G_PKG_NAME) THEN
3170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3171: END IF;
3172:
3173: --Initialize message list if p_init_msg_list is set to TRUE.
3174: IF FND_API.to_Boolean(p_init_msg_list) THEN
3170: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3171: END IF;
3172:
3173: --Initialize message list if p_init_msg_list is set to TRUE.
3174: IF FND_API.to_Boolean(p_init_msg_list) THEN
3175: FND_MSG_PUB.initialize;
3176: END IF;
3177:
3178: --Initialize API return status to success.
3175: FND_MSG_PUB.initialize;
3176: END IF;
3177:
3178: --Initialize API return status to success.
3179: x_return_status := FND_API.G_RET_STS_SUCCESS;
3180:
3181: l_merge_to_id := p_record_id;
3182:
3183: LOOP
3190:
3191: IF l_merge_to_id <> p_record_id THEN
3192: x_merge_to_record_id := l_merge_to_id;
3193: x_merge_to_record_desc := l_merge_to_desc;
3194: x_is_merged := FND_API.G_TRUE;
3195: ELSE
3196: x_is_merged := FND_API.G_FALSE;
3197: END IF;
3198:
3192: x_merge_to_record_id := l_merge_to_id;
3193: x_merge_to_record_desc := l_merge_to_desc;
3194: x_is_merged := FND_API.G_TRUE;
3195: ELSE
3196: x_is_merged := FND_API.G_FALSE;
3197: END IF;
3198:
3199: EXCEPTION
3200: WHEN OTHERS THEN
3197: END IF;
3198:
3199: EXCEPTION
3200: WHEN OTHERS THEN
3201: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3202:
3203: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3204: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3205: FND_MSG_PUB.ADD;
3204: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3205: FND_MSG_PUB.ADD;
3206:
3207: FND_MSG_PUB.Count_And_Get(
3208: p_encoded => FND_API.G_FALSE,
3209: p_count => x_msg_count,
3210: p_data => x_msg_data);
3211: END get_merge_to_record_id;
3212:
3245: WHEN OTHERS THEN
3246: /* FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3247: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3248: FND_MSG_PUB.ADD;
3249: RAISE FND_API.G_EXC_ERROR;*/
3250: RETURN NULL;
3251: END;
3252:
3253: /*-----------------------------------------------------------------------
3287: WHEN OTHERS THEN
3288: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3289: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3290: FND_MSG_PUB.ADD;
3291: RAISE FND_API.G_EXC_ERROR;
3292: END get_col_type;
3293:
3294:
3295: /*-----------------------------------------------------------------------
3298:
3299: PROCEDURE check_party_in_merge_batch
3300: (
3301: p_api_version IN NUMBER,
3302: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
3303: p_party_id IN NUMBER,
3304: x_in_merge OUT NOCOPY VARCHAR2,
3305: x_batch_id OUT NOCOPY NUMBER,
3306: x_batch_name OUT NOCOPY VARCHAR2,
3329:
3330: BEGIN
3331:
3332: --Standard call to check for call compatibility.
3333: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3334: l_api_name, G_PKG_NAME) THEN
3335: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3336: END IF;
3337:
3331:
3332: --Standard call to check for call compatibility.
3333: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3334: l_api_name, G_PKG_NAME) THEN
3335: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3336: END IF;
3337:
3338: --Initialize message list if p_init_msg_list is set to TRUE.
3339: IF FND_API.to_Boolean(p_init_msg_list) THEN
3335: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3336: END IF;
3337:
3338: --Initialize message list if p_init_msg_list is set to TRUE.
3339: IF FND_API.to_Boolean(p_init_msg_list) THEN
3340: FND_MSG_PUB.initialize;
3341: END IF;
3342:
3343: --Initialize API return status to success.
3340: FND_MSG_PUB.initialize;
3341: END IF;
3342:
3343: --Initialize API return status to success.
3344: x_return_status := FND_API.G_RET_STS_SUCCESS;
3345: x_in_merge := FND_API.G_FALSE;
3346:
3347: OPEN c_merge_batch;
3348: FETCH c_merge_batch INTO l_batch_id, l_batch_name, l_batch_created_by,
3341: END IF;
3342:
3343: --Initialize API return status to success.
3344: x_return_status := FND_API.G_RET_STS_SUCCESS;
3345: x_in_merge := FND_API.G_FALSE;
3346:
3347: OPEN c_merge_batch;
3348: FETCH c_merge_batch INTO l_batch_id, l_batch_name, l_batch_created_by,
3349: l_batch_created_on;
3351: x_batch_id := l_batch_id;
3352: x_batch_name := l_batch_name;
3353: x_batch_created_by := l_batch_created_by;
3354: x_batch_creation_date := l_batch_created_on;
3355: x_in_merge := FND_API.G_TRUE;
3356: END IF;
3357:
3358: EXCEPTION
3359: WHEN OTHERS THEN
3356: END IF;
3357:
3358: EXCEPTION
3359: WHEN OTHERS THEN
3360: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3361:
3362: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3363: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3364: FND_MSG_PUB.ADD;
3369: END check_party_in_merge_batch;
3370:
3371:
3372: PROCEDURE setup_dnb_data(
3373: p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
3374: x_to_id IN OUT NOCOPY NUMBER,
3375: p_batch_party_id IN NUMBER
3376: ) IS
3377:
3505: -- Handle DNB data in HZ_ORGANIZATION_PROFILES
3506:
3507: IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
3508: HZ_PARTY_V2PUB.get_organization_rec(
3509: FND_API.G_FALSE,
3510: p_from_id,
3511: 'DNB',
3512: l_organization_rec,
3513: l_return_status,
3513: l_return_status,
3514: l_msg_count,
3515: l_msg_data);
3516:
3517: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3518: IF l_msg_data IS NULL THEN
3519: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3520: l_msg_data := l_msg_data ||
3521: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3517: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3518: IF l_msg_data IS NULL THEN
3519: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3520: l_msg_data := l_msg_data ||
3521: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3522: END LOOP;
3523: END IF;
3524: RETURN;
3525: END IF;
3533: l_organization_rec.application_id := NULL;
3534: l_organization_rec.party_rec.party_number := NULL;
3535: l_organization_rec.party_rec.orig_system_reference := NULL;
3536: HZ_PARTY_V2PUB.update_organization(
3537: FND_API.G_FALSE,
3538: l_organization_rec,
3539: l_obj_version_number,
3540: l_to_profile_id,
3541: l_return_status,
3541: l_return_status,
3542: l_msg_count,
3543: l_msg_data);
3544:
3545: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3546: IF l_msg_data IS NULL THEN
3547: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3548: l_msg_data := l_msg_data ||
3549: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3545: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3546: IF l_msg_data IS NULL THEN
3547: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3548: l_msg_data := l_msg_data ||
3549: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3550: END LOOP;
3551: END IF;
3552: FND_FILE.put_line (FND_FILE.log,'Warning .. Error updating Org Profile of x_to_id ' || l_msg_data);
3553: -- Bug Fix : 3116262.
3551: END IF;
3552: FND_FILE.put_line (FND_FILE.log,'Warning .. Error updating Org Profile of x_to_id ' || l_msg_data);
3553: -- Bug Fix : 3116262.
3554: --RETURN;
3555: RAISE FND_API.G_EXC_ERROR;
3556: END IF;
3557:
3558: END IF;
3559: