[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:
523: FND_MSG_PUB.initialize;
524: init_globals;
525:
526: --Initialize API return status to success.
527: l_glob_return_status := FND_API.G_RET_STS_SUCCESS;
528: l_return_status := FND_API.G_RET_STS_SUCCESS;
529:
530:
531: open get_batch_party_id_csr;
524: init_globals;
525:
526: --Initialize API return status to success.
527: l_glob_return_status := FND_API.G_RET_STS_SUCCESS;
528: l_return_status := FND_API.G_RET_STS_SUCCESS;
529:
530:
531: open get_batch_party_id_csr;
532: fetch get_batch_party_id_csr into l_batch_pid;
539: ELSE
540: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
541: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
542: FND_MSG_PUB.ADD;
543: RAISE FND_API.G_EXC_ERROR;
544: END IF;
545:
546: -- Open the batch cursor and fetch batch details
547: OPEN c_batch(l_batch_id);
550: IF (c_batch%NOTFOUND) THEN
551: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
552: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
553: FND_MSG_PUB.ADD;
554: RAISE FND_API.G_EXC_ERROR;
555: END IF;
556:
557: -- If batch already complete error out
558: IF (l_batch_status = 'COMPLETE') THEN
558: IF (l_batch_status = 'COMPLETE') THEN
559: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_COMPLETE');
560: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
561: FND_MSG_PUB.ADD;
562: RAISE FND_API.G_EXC_ERROR;
563: END IF;
564: CLOSE c_batch;
565:
566: -- bug 4865280 :check for internal external party types. Veto merge if both party types are present in batch
574: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_INTERNAL_PARTY_IND');
575: FND_MESSAGE.SET_TOKEN('PARTY_INT', l_int_party);
576: FND_MESSAGE.SET_TOKEN('PARTY_EXT', l_ext_party);
577: FND_MSG_PUB.ADD;
578: RAISE FND_API.G_EXC_ERROR;
579: END IF;
580:
581: HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
582: p_dup_set_id => null,
580:
581: HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
582: p_dup_set_id => null,
583: p_merge_batch_id => l_batch_id,
584: p_init_msg_list => FND_API.G_FALSE,
585: p_reject_req_flag => 'Y',
586: x_return_status => l_ret_status,
587: x_msg_count => l_msg_count,
588: x_msg_data => l_msg_data);
588: x_msg_data => l_msg_data);
589:
590: /* error messages have been pushed into message stack in above procedure */
591: IF l_ret_status = 'E' THEN
592: RAISE FND_API.G_EXC_ERROR;
593: ELSIF l_ret_status = 'U' THEN
594: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595: END IF;
596:
590: /* error messages have been pushed into message stack in above procedure */
591: IF l_ret_status = 'E' THEN
592: RAISE FND_API.G_EXC_ERROR;
593: ELSIF l_ret_status = 'U' THEN
594: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595: END IF;
596:
597: -- check party merge DSS - update party privilege.
598: hz_dup_pvt.party_merge_dss_check(p_merge_batch_id => l_batch_id,
602: x_msg_data => l_msg_data);
603:
604: /* error messages have been pushed into message stack in above procedure */
605: IF l_ret_status = 'E' THEN
606: RAISE FND_API.G_EXC_ERROR;
607: ELSIF l_ret_status = 'U' THEN
608: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609: END IF;
610:
604: /* error messages have been pushed into message stack in above procedure */
605: IF l_ret_status = 'E' THEN
606: RAISE FND_API.G_EXC_ERROR;
607: ELSIF l_ret_status = 'U' THEN
608: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609: END IF;
610:
611: -- Disable DSS in party merge for sub entities. Will enabled it at the end of the merge.
612: l_dss_orig_prof_val := NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N');
677:
678: -- Lock HZ_MERGE_BATCH, HZ_MERGE_PARTIES and HZ_MERGE_PARTY_DETAILS records
679: log ('.... Locking batch for execution');
680: lock_batch(p_batch_id, l_return_status);
681: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
682: ROLLBACK to batch_merge;
683: RAISE FND_API.G_EXC_ERROR;
684: END IF;
685:
679: log ('.... Locking batch for execution');
680: lock_batch(p_batch_id, l_return_status);
681: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
682: ROLLBACK to batch_merge;
683: RAISE FND_API.G_EXC_ERROR;
684: END IF;
685:
686:
687: -----Pre-Merge for all the distict merge_to parties where the parties are
744: LOOP
745: FND_MSG_PUB.initialize;
746:
747: -- Initialize return status and error buffer
748: l_return_status := FND_API.G_RET_STS_SUCCESS;
749: l_error_msg := '';
750: g_merge_reason_code := NULL;
751:
752: -- Fetch the merge party details
769: IF c_dict_id%NOTFOUND THEN
770: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
771: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
772: FND_MSG_PUB.ADD;
773: RAISE FND_API.G_EXC_ERROR;
774: END IF;
775: CLOSE c_dict_id;
776:
777: log('');
780: g_num_sub_entities :=-1;
781:
782: --4307667
783: 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
784: l_return_status := FND_API.G_RET_STS_ERROR;
785: raise FND_API.G_EXC_ERROR;
786: END IF;
787:
788: -- Call the recursive merge procedure performing this merge
781:
782: --4307667
783: 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
784: l_return_status := FND_API.G_RET_STS_ERROR;
785: raise FND_API.G_EXC_ERROR;
786: END IF;
787:
788: -- Call the recursive merge procedure performing this merge
789: do_merge(p_batch_party_id =>l_batch_party_id,
810: IF c_dict_id%NOTFOUND THEN
811: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
812: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
813: FND_MSG_PUB.ADD;
814: RAISE FND_API.G_EXC_ERROR;
815: END IF;
816: CLOSE c_dict_id;
817:
818: -- Loop through the merge party sites
845: x_error_msg =>l_error_msg,
846: x_return_status =>l_return_status);
847:
848: END IF;
849: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
850: END LOOP;
851: CLOSE c_batch_party_sites;
852:
853: --if the prev. merge was success only then do the next step
850: END LOOP;
851: CLOSE c_batch_party_sites;
852:
853: --if the prev. merge was success only then do the next step
854: if l_return_status = FND_API.G_RET_STS_SUCCESS then
855: OPEN c_dict_id(l_rule_set_name,'HZ_CONTACT_POINTS');
856: FETCH c_dict_id INTO l_dict_id;
857: IF c_dict_id%NOTFOUND THEN
858: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
857: IF c_dict_id%NOTFOUND THEN
858: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
859: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_CONTACT_POINTS');
860: FND_MSG_PUB.ADD;
861: RAISE FND_API.G_EXC_ERROR;
862: END IF;
863: CLOSE c_dict_id;
864:
865: -- Loop through the merge contact points
894: x_error_msg =>l_error_msg,
895: x_return_status =>l_return_status);
896:
897: END IF;
898: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
899: END LOOP;
900: CLOSE c_batch_contact_points;
901:
902: end if; --l_return status
903:
904:
905:
906: ---if the prev. merge was success only then do the next step
907: if l_return_status = FND_API.G_RET_STS_SUCCESS then
908: OPEN c_dict_id(l_rule_set_name,'HZ_PARTY_RELATIONSHIPS');
909: FETCH c_dict_id INTO l_dict_id;
910: IF c_dict_id%NOTFOUND THEN
911: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
910: IF c_dict_id%NOTFOUND THEN
911: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
912: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_RELATIONSHIPS');
913: FND_MSG_PUB.ADD;
914: RAISE FND_API.G_EXC_ERROR;
915: END IF;
916: CLOSE c_dict_id;
917:
918: -- Loop through the merge relationships
970: x_error_msg =>l_error_msg,
971: x_return_status =>l_return_status);
972:
973: END IF;
974: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
975: END LOOP;
976: CLOSE c_batch_relationships;
977: end if; --l_return_status
978:
978:
979: ELSE --type of merge
980: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_TYPE');
981: FND_MSG_PUB.ADD;
982: RAISE FND_API.G_EXC_ERROR;
983: END IF; --type of merge
984:
985: -- If the party was successfully merged, update merge status to 'DONE'
986: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
982: RAISE FND_API.G_EXC_ERROR;
983: END IF; --type of merge
984:
985: -- If the party was successfully merged, update merge status to 'DONE'
986: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
987:
988: store_merge_history(null,null,null,null,null,null,null,null,null,'Y');
989: -- If delete not vetoed, perform delete
990: IF g_merge_delete_flag = 'Y' THEN
1022: END LOOP;
1023: CLOSE c_batch_details;
1024:
1025: if l_pre_merge_type <> 'SAME_PARTY_MERGE' AND
1026: l_return_status = FND_API.G_RET_STS_SUCCESS then
1027:
1028: /* Merge all to records that are getting merged into the transferred from records */
1029: FOR TO_RECORDS IN (
1030: SELECT mp.batch_party_id, merge_from_entity_id, merge_to_entity_id, ENTITY_NAME
1044: p_preview=>p_preview,
1045: p_log_padding=>' ',
1046: x_error_msg=>l_error_msg,
1047: x_return_status=>l_return_status);
1048: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
1049: END LOOP;
1050:
1051: IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1052: BEGIN
1047: x_return_status=>l_return_status);
1048: EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
1049: END LOOP;
1050:
1051: IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1052: BEGIN
1053:
1054: SELECT 1 INTO l_tmp
1055: FROM HZ_MERGE_ENTITY_ATTRIBUTES
1070: l_party_type,
1071: l_return_status);
1072:
1073: ---Bug 2723616 raise the message passed by profile attr API
1074: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1075: l_msg_data := logerror;
1076: END IF;
1077:
1078:
1081: NULL;
1082: WHEN OTHERS THEN
1083: FND_FILE.put_line(FND_FILE.log,'l_return_status ' || l_return_status);
1084: FND_FILE.put_line(FND_FILE.log,'Error ' || SQLERRM);
1085: l_return_status:= FND_API.G_RET_STS_ERROR;
1086: END;
1087: END IF;
1088: END IF;
1089:
1087: END IF;
1088: END IF;
1089:
1090: --4114041
1091: IF (l_glob_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1092: l_glob_return_status := l_return_status;
1093: END IF;
1094:
1095: --If merge mode is not preview
1093: END IF;
1094:
1095: --If merge mode is not preview
1096: IF p_preview <> 'Y'AND
1097: l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1098: null;
1099: -- COMMIT; Should not commit here, need to run batch merge procs after this. 4634891
1100: ELSE
1101: ROLLBACK to merge_group;
1117: FETCH batch_merge_procedures INTO l_merge_dict_id, l_sub_entity_name, l_proc_name;
1118: EXIT WHEN batch_merge_procedures%NOTFOUND;
1119: exec_merge(l_sub_entity_name,l_proc_name,p_batch_id,g_request_id,l_return_status);
1120:
1121: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1122: outandlog('Executed batch merge procedure '||l_proc_name||' successfully');
1123: ELSE
1124: l_glob_return_status := l_return_status;
1125: ROLLBACK TO batch_merge;
1150:
1151: END IF;
1152:
1153: -- Check if the whole batch was successfully executed
1154: IF l_glob_return_status = FND_API.G_RET_STS_SUCCESS THEN
1155: outandlog('.... Merge batch successfully executed');
1156:
1157: -- If not preview mode update batch status to COMPLETE
1158: IF p_preview <> 'Y' then
1284: --4634891
1285:
1286:
1287: EXCEPTION
1288: WHEN FND_API.G_EXC_ERROR THEN
1289: --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1290: IF p_preview = 'Y' AND l_batch_merge_spt THEN
1291: ROLLBACK to batch_merge;
1292: ELSE
1309: -1,-1, -1,null,null,
1310: -1, 'Error', errbuf);
1311: store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1312: -- FND_FILE.close;
1313: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314: --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1315: IF p_preview = 'Y' AND l_batch_merge_spt THEN
1316: ROLLBACK to batch_merge;
1317: ELSE
1393: IF c_dict_id%NOTFOUND THEN
1394: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1395: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
1396: FND_MSG_PUB.ADD;
1397: RAISE FND_API.G_EXC_ERROR;
1398: END IF;
1399: CLOSE c_dict_id;
1400:
1401: log('');
1520: IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1521: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1522: FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
1523: FND_MSG_PUB.ADD;
1524: RAISE FND_API.G_EXC_ERROR;
1525: END IF;
1526: CLOSE c_dict_details;
1527:
1528:
1527:
1528:
1529: l_from_rec_desc := get_record_desc(p_from_id,p_entity_name, l_pk_column_name,
1530: l_desc_column_name, x_return_status);
1531: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1532:
1533: -- Write a log message
1534: log(p_log_padding|| l_from_rec_desc, FALSE);
1535:
1545: p_batch_party_id,
1546: x_return_status);
1547:
1548: -- Check if the merge procedure returned an error
1549: IF (x_return_status = FND_API.G_RET_STS_SUCCESS OR
1550: x_return_status = 'N') THEN
1551: -- Log the merged records
1552:
1553: -- If the to_id is different from from_id .. then the operation performed
1551: -- Log the merged records
1552:
1553: -- If the to_id is different from from_id .. then the operation performed
1554: -- is a merge
1555: IF ((p_to_id IS NOT NULL AND p_to_id <> FND_API.G_MISS_NUM)
1556: AND p_to_id <> p_from_id) THEN
1557: -- Store in the history and log
1558:
1559: IF (p_to_id <> 0) THEN
1564: ELSE
1565: l_op := 'Merge';
1566: END IF;
1567:
1568: x_return_status := FND_API.G_RET_STS_SUCCESS;
1569:
1570: l_to_rec_desc := get_record_desc(p_to_id,p_entity_name, l_pk_column_name,
1571: l_desc_column_name, x_return_status);
1572: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1568: x_return_status := FND_API.G_RET_STS_SUCCESS;
1569:
1570: l_to_rec_desc := get_record_desc(p_to_id,p_entity_name, l_pk_column_name,
1571: l_desc_column_name, x_return_status);
1572: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1573:
1574: IF p_entity_name = 'HZ_PARTY_SITES' AND l_mand <> 'C' THEN
1575: BEGIN
1576: SELECT mandatory_merge INTO l_mand
1728: l_merge_dict_id, 'Error', x_error_msg);
1729: log('Error Entity Name: '||l_sub_entity_name);
1730: log('Error FK Column: '||l_sub_fk_column_name);
1731:
1732: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1733: RETURN;
1734: END IF;
1735:
1736: --log('fk data type: '||l_sub_fk_column_type);
1810: -1,p_from_id, p_to_id,null,null,
1811: l_merge_dict_id, 'Error', x_error_msg);
1812: log('Error Entity Name: '||l_sub_entity_name);
1813: log('Error FK Column: '||l_sub_fk_column_name);
1814: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1815: RETURN;
1816: END IF;
1817: END;
1818:
1828: IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1829: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1830: FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1831: FND_MSG_PUB.ADD;
1832: RAISE FND_API.G_EXC_ERROR;
1833: END IF;
1834: CLOSE c_dict_details;
1835: log(p_log_padding || ' '|| l_desc);
1836:
1849: l_desc_column_name_b, x_return_status);
1850: log(p_log_padding || ' ' || l_from_rec_desc, FALSE);
1851: END IF;
1852:
1853: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1854:
1855: IF rownumber=1 OR l_bulk_flag IS NULL OR l_bulk_flag <> 'Y' THEN
1856: -- Execute the merge procedure for the entity
1857: exec_merge_r(l_sub_entity_name,
1866: x_return_status);
1867: END IF;
1868:
1869: -- Check if the merge procedure returned an error
1870: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1871: IF p_from_id <> p_to_id THEN
1872: store_merge_history(p_batch_party_id,-1,
1873: -1,p_from_id, p_to_id,l_from_rec_desc, null, p_dict_id,
1874: 'Transfer');
1947: IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1948: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1949: FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1950: FND_MSG_PUB.ADD;
1951: RAISE FND_API.G_EXC_ERROR;
1952: END IF;
1953: CLOSE c_dict_details;
1954: END IF;
1955:
1952: END IF;
1953: CLOSE c_dict_details;
1954: END IF;
1955:
1956: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1957: IF rownumber=1 THEN
1958:
1959: -- Execute the merge procedure for the entity
1960: exec_merge(l_sub_entity_name,
1970: log(p_log_padding || ' Bulk merge completed. Transferred '|| l_fk_column_name_b ||' from '|| p_from_id ||' to '||p_to_id);
1971: END IF;
1972:
1973: -- Check if the merge procedure returned an error
1974: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1975: -- Write to log file
1976: store_merge_history(p_batch_party_id, l_sub_pk_value ,
1977: null,p_from_id, p_to_id,null,
1978: null, l_merge_dict_id, 'Transfer');
2039: x_error_msg => x_error_msg,
2040: x_return_status => x_return_status);
2041:
2042: -- If not successful abort merge and return
2043: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2044: RETURN;
2045: END IF;
2046: END LOOP;
2047: END IF;
2068: -1,p_from_id, p_to_id,null,null,
2069: l_merge_dict_id, 'Error', x_error_msg);
2070: log('Error Entity Name: '||l_sub_entity_name);
2071: log('Error FK Column: '||l_sub_fk_column_name);
2072: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2073: END do_merge;
2074:
2075: /*------------------------------------------------------------------------------
2076: | Procedure to lock HZ_MERGE_BATCH, HZ_MERGE_PARTIES and HZ_MERGE_PARTY_DETAILS
2100: WHEN OTHERS THEN
2101: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_BATCH_LOCK_ERROR');
2102: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2103: FND_MSG_PUB.ADD;
2104: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2105: END lock_batch;
2106:
2107: /*---------------------------------------------------------------------------------------
2108: | Procedure to recursively lock all records for entities defined in the merge dictionary
2163: lockstr:=null;
2164: ELSIF SQLCODE=-942 THEN
2165: lockstr:=null;
2166: ELSE
2167: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2168: END IF;
2169: END;
2170:
2171: IF lockstr IS NULL THEN
2216: lock_records(l_sub_ent_name, l_sub_pkcol, l_sub_fkcol, '(' ||lockstr
2217: ||')', l_sub_joincl,p_rule_set_name, x_return_status);
2218: END IF;
2219:
2220: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2221: RETURN;
2222: END IF;
2223:
2224: END LOOP;
2228: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_LOCK_ERROR');
2229: FND_MESSAGE.SET_TOKEN('ENTITY', p_entity_name);
2230: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM || ' : ' || lockstr || ' subent ' || l_sub_ent_name);
2231: FND_MSG_PUB.ADD;
2232: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2233: END lock_records;
2234:
2235: /*------------------------------------------------------------------------------
2236: | Procedure to delete the merged records for a batch party
2290: WHEN OTHERS THEN
2291: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_DELETE_ERROR');
2292: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2293: FND_MSG_PUB.ADD;
2294: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2295: END delete_merged_records;
2296:
2297: /*-------------------------------------------------------------------
2298: | Function to fetch a record description
2337: FND_MESSAGE.SET_NAME('AR', 'HZ_REC_DESC_ERROR');
2338: FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
2339: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2340: FND_MSG_PUB.ADD;
2341: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2342: RETURN NULL;
2343: END get_record_desc;
2344:
2345: /*------------------------------------------------------------------------------
2374: FND_MESSAGE.SET_NAME('AR', 'HZ_REC_DESC_ERROR');
2375: FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
2376: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2377: FND_MSG_PUB.ADD;
2378: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2379: RETURN NULL;
2380: END get_record_desc_r;
2381:
2382:
2401: r NUMBER;
2402:
2403: BEGIN
2404:
2405: l_return_status := FND_API.G_RET_STS_SUCCESS;
2406: IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2407: IF g_cur_proc_name IS NOT NULL THEN
2408: dbms_sql.close_cursor(g_proc_cursor);
2409: END IF;
2434:
2435: x_return_status := l_return_status;
2436:
2437: /*
2438: l_return_status := FND_API.G_RET_STS_SUCCESS;
2439: -- Create a dynamic SQL block to execute the merge procedure
2440: plsql_block := 'BEGIN '||
2441: p_proc_name||'(:p_entity_name, :from_id,'||
2442: ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2462: log('');
2463: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2464: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2465: FND_MSG_PUB.ADD;
2466: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2467: END exec_merge;
2468:
2469: /*-----------------------------------------------------------------
2470: | Procedure to execute the merge procedure using Dynamic SQL
2487: r NUMBER;
2488:
2489: BEGIN
2490:
2491: l_return_status := FND_API.G_RET_STS_SUCCESS;
2492: IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2493: IF g_cur_proc_name IS NOT NULL THEN
2494: dbms_sql.close_cursor(g_proc_cursor);
2495: END IF;
2520:
2521: x_return_status := l_return_status;
2522:
2523: /*
2524: l_return_status := FND_API.G_RET_STS_SUCCESS;
2525: -- Create a dynamic SQL block to execute the merge procedure
2526: plsql_block := 'BEGIN '||
2527: p_proc_name||'(:p_entity_name, :from_id,'||
2528: ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2548: log('');
2549: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2550: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2551: FND_MSG_PUB.ADD;
2552: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2553: END exec_merge_r;
2554:
2555: --bug 4634891 created overloaded procedure exec_merge
2556: /*------------------------------------------------------------------
2569: r NUMBER;
2570:
2571: BEGIN
2572:
2573: l_return_status := FND_API.G_RET_STS_SUCCESS;
2574: IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2575: IF g_cur_proc_name IS NOT NULL THEN
2576: dbms_sql.close_cursor(g_proc_cursor);
2577: END IF;
2605: log('');
2606: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2607: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2608: FND_MSG_PUB.ADD;
2609: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2610: END exec_merge;
2611:
2612: /*------------------------------------------------------------------------
2613: | Procedure to store merge history record
2677: END IF;
2678:
2679: H_Counter := H_Counter+1;
2680: H_batch_party_id(H_Counter) := p_batch_party_id;
2681: IF p_from_id=FND_API.G_MISS_NUM THEN
2682: H_from_id(H_Counter) := null;
2683: ELSE
2684: H_from_id(H_Counter) := p_from_id;
2685: END IF;
2682: H_from_id(H_Counter) := null;
2683: ELSE
2684: H_from_id(H_Counter) := p_from_id;
2685: END IF;
2686: IF p_to_id=FND_API.G_MISS_NUM THEN
2687: H_to_id(H_Counter) := null;
2688: ELSE
2689: H_to_id(H_Counter) := p_to_id;
2690: END IF;
2687: H_to_id(H_Counter) := null;
2688: ELSE
2689: H_to_id(H_Counter) := p_to_id;
2690: END IF;
2691: IF p_from_fk_id=FND_API.G_MISS_NUM THEN
2692: H_from_fk_id(H_Counter) := null;
2693: ELSE
2694: H_from_fk_id(H_Counter) := p_from_fk_id;
2695: END IF;
2692: H_from_fk_id(H_Counter) := null;
2693: ELSE
2694: H_from_fk_id(H_Counter) := p_from_fk_id;
2695: END IF;
2696: IF p_to_fk_id=FND_API.G_MISS_NUM THEN
2697: H_to_fk_id(H_Counter) := null;
2698: ELSE
2699: H_to_fk_id(H_Counter) := p_to_fk_id;
2700: END IF;
2707: WHEN OTHERS THEN
2708: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2709: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2710: FND_MSG_PUB.ADD;
2711: RAISE FND_API.G_EXC_ERROR;
2712: END store_merge_history;
2713:
2714: /*---------------------------------------------------------------------------
2715: | Procedure to store the merge procedure in the log table.
2778: END IF;
2779:
2780: I_Counter := I_Counter+1;
2781: I_batch_party_id(I_Counter) := p_batch_party_id;
2782: IF p_from_id=FND_API.G_MISS_NUM THEN
2783: I_from_id(I_Counter) := null;
2784: ELSE
2785: I_from_id(I_Counter) := p_from_id;
2786: END IF;
2783: I_from_id(I_Counter) := null;
2784: ELSE
2785: I_from_id(I_Counter) := p_from_id;
2786: END IF;
2787: IF p_to_id=FND_API.G_MISS_NUM THEN
2788: I_to_id(I_Counter) := null;
2789: ELSE
2790: I_to_id(I_Counter) := p_to_id;
2791: END IF;
2788: I_to_id(I_Counter) := null;
2789: ELSE
2790: I_to_id(I_Counter) := p_to_id;
2791: END IF;
2792: IF p_from_fk_id=FND_API.G_MISS_NUM THEN
2793: I_from_fk_id(I_Counter) := null;
2794: ELSE
2795: I_from_fk_id(I_Counter) := p_from_fk_id;
2796: END IF;
2793: I_from_fk_id(I_Counter) := null;
2794: ELSE
2795: I_from_fk_id(I_Counter) := p_from_fk_id;
2796: END IF;
2797: IF p_to_fk_id=FND_API.G_MISS_NUM THEN
2798: I_to_fk_id(I_Counter) := null;
2799: ELSE
2800: I_to_fk_id(I_Counter) := p_to_fk_id;
2801: END IF;
2812: ROLLBACK; -- bug 3947633
2813: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2814: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2815: FND_MSG_PUB.ADD;
2816: RAISE FND_API.G_EXC_ERROR;
2817: END store_merge_log;
2818:
2819: /*---------------------------------------------------------------------------
2820: | Procedure to write a message to the out NOCOPY file
2963: IF (pre_batch%NOTFOUND) THEN
2964: FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
2965: FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
2966: FND_MSG_PUB.ADD;
2967: RAISE FND_API.G_EXC_ERROR;
2968: END IF;
2969: CLOSE pre_batch;
2970:
2971: --Initialise the log variables
2977:
2978: ----------Pre merge for HZ_PARTY_SITES ---------------
2979: OPEN party_sites_for_pre_merge(p_to_party_id , p_batch_id);
2980: LOOP
2981: l_return_status := FND_API.G_RET_STS_SUCCESS;
2982: FETCH party_sites_for_pre_merge INTO l_merge_to_entity_id, l_from_party_id, l_batch_party_id;
2983: EXIT WHEN party_sites_for_pre_merge%NOTFOUND;
2984: l_new_party_site_id := l_merge_to_entity_id;
2985: IF l_merge_to_entity_id IS NOT NULL THEN
3004: x_return_status => l_return_status);
3005:
3006:
3007: -- Check if the merge procedure returned an error
3008: IF (l_return_status = FND_API.G_RET_STS_SUCCESS OR
3009: l_return_status = 'N' ) THEN
3010: IF l_new_party_site_id <> 0 THEN
3011:
3012: -- Write to log file
3015: log('Pre Merge: '|| l_from_rec_desc ||'discarded (DNB data) ');
3016: END IF;
3017:
3018: ELSE
3019: RAISE FND_API.G_EXC_ERROR;
3020: END IF;
3021:
3022: -- set the party sites status to A so that it is picked in merge that follows
3023: --the hz_merge_pkg.do_party_site_merge sets it to 'M' in pre-merge
3051:
3052: END IF;
3053: END LOOP;
3054:
3055: l_return_status := FND_API.G_RET_STS_SUCCESS;
3056: ----------Pre merge for HZ_RELATIONSHIPS ---------------
3057:
3058: --Initialise the log variables
3059: l_from_rec_desc := null;
3107:
3108: l_from_rec_desc := hz_merge_util.get_party_reln_description(l_merge_to_entity_id);
3109:
3110: -- Check if the merge procedure returned an error
3111: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3112: -- Write to log file
3113: store_merge_history(l_batch_party_id, l_merge_to_entity_id ,
3114: l_merge_to_entity_id,l_from_party_id, p_to_party_id,l_from_rec_desc,
3115: null, l_merge_dict_id, 'Transfer');
3125: store_merge_log(l_batch_party_id, l_merge_to_entity_id,
3126: l_merge_to_entity_id,l_from_party_id, p_to_party_id,
3127: l_from_rec_desc,null,
3128: l_merge_dict_id, 'Error', logerror);
3129: RAISE FND_API.G_EXC_ERROR;
3130: END IF;
3131: END IF; --l_proc_name
3132: END IF; --l_merge_to_entity_id
3133:
3154: BEGIN
3155: FND_MSG_PUB.Reset;
3156:
3157: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3158: l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3159: END LOOP;
3160: IF (SQLERRM IS NOT NULL) THEN
3161: l_msg_data := l_msg_data || SQLERRM;
3162: END IF;
3178:
3179: PROCEDURE get_merge_to_record_id
3180: (
3181: p_api_version IN NUMBER,
3182: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
3183: p_record_id IN NUMBER,
3184: p_entity_name IN VARCHAR2,
3185: x_is_merged OUT NOCOPY VARCHAR2,
3186: x_merge_to_record_id OUT NOCOPY NUMBER,
3204:
3205: BEGIN
3206:
3207: --Standard call to check for call compatibility.
3208: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3209: l_api_name, G_PKG_NAME) THEN
3210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3211: END IF;
3212:
3206:
3207: --Standard call to check for call compatibility.
3208: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3209: l_api_name, G_PKG_NAME) THEN
3210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3211: END IF;
3212:
3213: --Initialize message list if p_init_msg_list is set to TRUE.
3214: IF FND_API.to_Boolean(p_init_msg_list) THEN
3210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3211: END IF;
3212:
3213: --Initialize message list if p_init_msg_list is set to TRUE.
3214: IF FND_API.to_Boolean(p_init_msg_list) THEN
3215: FND_MSG_PUB.initialize;
3216: END IF;
3217:
3218: --Initialize API return status to success.
3215: FND_MSG_PUB.initialize;
3216: END IF;
3217:
3218: --Initialize API return status to success.
3219: x_return_status := FND_API.G_RET_STS_SUCCESS;
3220:
3221: l_merge_to_id := p_record_id;
3222:
3223: LOOP
3230:
3231: IF l_merge_to_id <> p_record_id THEN
3232: x_merge_to_record_id := l_merge_to_id;
3233: x_merge_to_record_desc := l_merge_to_desc;
3234: x_is_merged := FND_API.G_TRUE;
3235: ELSE
3236: x_is_merged := FND_API.G_FALSE;
3237: END IF;
3238:
3232: x_merge_to_record_id := l_merge_to_id;
3233: x_merge_to_record_desc := l_merge_to_desc;
3234: x_is_merged := FND_API.G_TRUE;
3235: ELSE
3236: x_is_merged := FND_API.G_FALSE;
3237: END IF;
3238:
3239: EXCEPTION
3240: WHEN OTHERS THEN
3237: END IF;
3238:
3239: EXCEPTION
3240: WHEN OTHERS THEN
3241: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3242:
3243: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3244: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3245: FND_MSG_PUB.ADD;
3244: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3245: FND_MSG_PUB.ADD;
3246:
3247: FND_MSG_PUB.Count_And_Get(
3248: p_encoded => FND_API.G_FALSE,
3249: p_count => x_msg_count,
3250: p_data => x_msg_data);
3251: END get_merge_to_record_id;
3252:
3285: WHEN OTHERS THEN
3286: /* FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3287: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3288: FND_MSG_PUB.ADD;
3289: RAISE FND_API.G_EXC_ERROR;*/
3290: RETURN NULL;
3291: END;
3292:
3293: /*-----------------------------------------------------------------------
3327: WHEN OTHERS THEN
3328: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3329: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3330: FND_MSG_PUB.ADD;
3331: RAISE FND_API.G_EXC_ERROR;
3332: END get_col_type;
3333:
3334:
3335: /*-----------------------------------------------------------------------
3338:
3339: PROCEDURE check_party_in_merge_batch
3340: (
3341: p_api_version IN NUMBER,
3342: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
3343: p_party_id IN NUMBER,
3344: x_in_merge OUT NOCOPY VARCHAR2,
3345: x_batch_id OUT NOCOPY NUMBER,
3346: x_batch_name OUT NOCOPY VARCHAR2,
3369:
3370: BEGIN
3371:
3372: --Standard call to check for call compatibility.
3373: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3374: l_api_name, G_PKG_NAME) THEN
3375: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3376: END IF;
3377:
3371:
3372: --Standard call to check for call compatibility.
3373: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3374: l_api_name, G_PKG_NAME) THEN
3375: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3376: END IF;
3377:
3378: --Initialize message list if p_init_msg_list is set to TRUE.
3379: IF FND_API.to_Boolean(p_init_msg_list) THEN
3375: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3376: END IF;
3377:
3378: --Initialize message list if p_init_msg_list is set to TRUE.
3379: IF FND_API.to_Boolean(p_init_msg_list) THEN
3380: FND_MSG_PUB.initialize;
3381: END IF;
3382:
3383: --Initialize API return status to success.
3380: FND_MSG_PUB.initialize;
3381: END IF;
3382:
3383: --Initialize API return status to success.
3384: x_return_status := FND_API.G_RET_STS_SUCCESS;
3385: x_in_merge := FND_API.G_FALSE;
3386:
3387: OPEN c_merge_batch;
3388: FETCH c_merge_batch INTO l_batch_id, l_batch_name, l_batch_created_by,
3381: END IF;
3382:
3383: --Initialize API return status to success.
3384: x_return_status := FND_API.G_RET_STS_SUCCESS;
3385: x_in_merge := FND_API.G_FALSE;
3386:
3387: OPEN c_merge_batch;
3388: FETCH c_merge_batch INTO l_batch_id, l_batch_name, l_batch_created_by,
3389: l_batch_created_on;
3391: x_batch_id := l_batch_id;
3392: x_batch_name := l_batch_name;
3393: x_batch_created_by := l_batch_created_by;
3394: x_batch_creation_date := l_batch_created_on;
3395: x_in_merge := FND_API.G_TRUE;
3396: END IF;
3397:
3398: EXCEPTION
3399: WHEN OTHERS THEN
3396: END IF;
3397:
3398: EXCEPTION
3399: WHEN OTHERS THEN
3400: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3401:
3402: FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3403: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3404: FND_MSG_PUB.ADD;
3409: END check_party_in_merge_batch;
3410:
3411:
3412: PROCEDURE setup_dnb_data(
3413: p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
3414: x_to_id IN OUT NOCOPY NUMBER,
3415: p_batch_party_id IN NUMBER
3416: ) IS
3417:
3528: -- Handle DNB data in HZ_ORGANIZATION_PROFILES
3529:
3530: IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
3531: HZ_PARTY_V2PUB.get_organization_rec(
3532: FND_API.G_FALSE,
3533: p_from_id,
3534: 'DNB',
3535: l_organization_rec,
3536: l_return_status,
3536: l_return_status,
3537: l_msg_count,
3538: l_msg_data);
3539:
3540: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3541: IF l_msg_data IS NULL THEN
3542: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3543: l_msg_data := l_msg_data ||
3544: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3540: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3541: IF l_msg_data IS NULL THEN
3542: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3543: l_msg_data := l_msg_data ||
3544: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3545: END LOOP;
3546: END IF;
3547: RETURN;
3548: END IF;
3556: l_organization_rec.application_id := NULL;
3557: l_organization_rec.party_rec.party_number := NULL;
3558: l_organization_rec.party_rec.orig_system_reference := NULL;
3559: HZ_PARTY_V2PUB.update_organization(
3560: FND_API.G_FALSE,
3561: l_organization_rec,
3562: l_obj_version_number,
3563: l_to_profile_id,
3564: l_return_status,
3564: l_return_status,
3565: l_msg_count,
3566: l_msg_data);
3567:
3568: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3569: IF l_msg_data IS NULL THEN
3570: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3571: l_msg_data := l_msg_data ||
3572: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3568: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3569: IF l_msg_data IS NULL THEN
3570: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3571: l_msg_data := l_msg_data ||
3572: FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3573: END LOOP;
3574: END IF;
3575: FND_FILE.put_line (FND_FILE.log,'Warning .. Error updating Org Profile of x_to_id ' || l_msg_data);
3576: -- Bug Fix : 3116262.
3574: END IF;
3575: FND_FILE.put_line (FND_FILE.log,'Warning .. Error updating Org Profile of x_to_id ' || l_msg_data);
3576: -- Bug Fix : 3116262.
3577: --RETURN;
3578: RAISE FND_API.G_EXC_ERROR;
3579: END IF;
3580:
3581: END IF;
3582:
3689:
3690: END IF;
3691:
3692: HZ_HIERARCHY_PUB.update_link(
3693: p_init_msg_list => FND_API.G_FALSE,
3694: p_hierarchy_node_rec => l_hierarchy_rec,
3695: x_return_status => l_return_status,
3696: x_msg_count => l_msg_count,
3697: x_msg_data => l_msg_data