[Home] [Help]
338: p_source_party_id IN NUMBER,
339: p_destination_party_id IN NUMBER
340: );
341:
342: ----BugNo:1695595.Added private procedure to update denormalized columns in hz_parties.
343:
344: PROCEDURE do_denormalize_contact_point (
345: p_party_id IN NUMBER,
346: p_contact_point_type IN VARCHAR2,
412:
413: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
414: p_to_fk_id, p_par_entity_name,
415: 'HZ_MERGE_PKG.party_merge',
416: 'HZ_PARTIES',NULL,
417: 'PARTY_ID',NULL,x_return_status);
418:
419: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
420: do_party_merge(p_from_id, l_to_id,p_from_fk_id, p_to_fk_id,
454:
455: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
456: p_to_fk_id, p_par_entity_name,
457: 'HZ_MERGE_PKG.org_profile_merge',
458: 'HZ_ORGANIZATION_PROFILES','HZ_PARTIES',
459: 'ORGANIZATION_PROFILE_ID', 'PARTY_ID',x_return_status);
460:
461: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
462: do_org_profile_merge(p_from_id, l_to_id,p_from_fk_id, p_to_fk_id,
496:
497: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
498: p_to_fk_id, p_par_entity_name,
499: 'HZ_MERGE_PKG.party_relationship_merge',
500: 'HZ_PARTY_RELATIONSHIPS','HZ_PARTIES',
501: 'PARTY_RELATIONSHIP_ID', 'PARTY_ID',x_return_status);
502:
503: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
504: do_party_relationship_merge(p_from_id, l_to_id,p_from_fk_id,p_to_fk_id,
538:
539: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
540: p_to_fk_id, p_par_entity_name,
541: 'HZ_MERGE_PKG.per_profile_merge',
542: 'HZ_PERSON_PROFILES','HZ_PARTIES',
543: 'PERSON_PROFILE_ID', 'PARTY_ID',x_return_status);
544:
545: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
546: do_per_profile_merge(p_from_id, l_to_id,p_from_fk_id,p_to_fk_id,
582:
583: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
584: p_to_fk_id, p_par_entity_name,
585: 'HZ_MERGE_PKG.party_usage_merge',
586: 'HZ_PARTY_USG_ASSIGNMENTS','HZ_PARTIES',
587: 'PARTY_USG_ASSIGNMENT_ID', 'PARTY_ID',x_return_status);
588:
589: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
590: do_party_usage_merge(p_from_id,l_to_id, p_from_fk_id,
625:
626: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
627: p_to_fk_id, p_par_entity_name,
628: 'HZ_MERGE_PKG.party_reln_subject_merge',
629: 'HZ_PARTY_RELATIONSHIPS','HZ_PARTIES',
630: 'PARTY_RELATIONSHIP_ID', 'PARTY_ID',x_return_status);
631:
632: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
633: do_party_reln_subj_merge(p_from_id,l_to_id, p_from_fk_id,
684:
685: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
686: p_to_fk_id, p_par_entity_name,
687: 'HZ_MERGE_PKG.party_reln_object_merge',
688: 'HZ_PARTY_RELATIONSHIPS','HZ_PARTIES',
689: 'PARTY_RELATIONSHIP_ID', 'PARTY_ID',x_return_status);
690:
691: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
692: do_party_reln_obj_merge(p_from_id,l_to_id, p_from_fk_id,
874: IF p_from_fk_id <> FND_API.G_MISS_NUM THEN
875: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
876: p_to_fk_id, p_par_entity_name,
877: 'HZ_MERGE_PKG.party_site_merge',
878: 'HZ_PARTY_SITES','HZ_PARTIES',
879: 'PARTY_SITE_ID', 'PARTY_ID',x_return_status);
880: ELSE
881: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
882: p_to_fk_id, p_par_entity_name,
953:
954: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
955: p_to_fk_id, p_par_entity_name,
956: 'HZ_MERGE_PKG.cust_account_merge',
957: 'HZ_CUST_ACCOUNTS','HZ_PARTIES',
958: 'CUST_ACCOUNT_ID', 'PARTY_ID',x_return_status);
959: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
960: l_dup_exists := HZ_MERGE_DUP_CHECK.check_cust_account_dup(
961: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
1001:
1002: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1003: p_to_fk_id, p_par_entity_name,
1004: 'HZ_MERGE_PKG.cust_account_merge',
1005: 'HZ_CUST_ACCOUNTS','HZ_PARTIES',
1006: 'CUST_ACCOUNT_ID', 'SELLING_PARTY_ID',x_return_status);
1007:
1008: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
1009: UPDATE HZ_CUST_ACCOUNTS
1053:
1054: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1055: p_to_fk_id, p_par_entity_name,
1056: 'HZ_MERGE_PKG.cust_account_role_merge',
1057: 'HZ_CUST_ACCOUNT_ROLES','HZ_PARTIES',
1058: 'CUST_ACCOUNT_ROLE_ID', 'PARTY_ID',x_return_status);
1059: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1060: l_dup_exists := HZ_MERGE_DUP_CHECK.check_cust_account_role_dup(
1061: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
1178:
1179: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1180: p_to_fk_id, p_par_entity_name,
1181: 'HZ_MERGE_PKG.financial_profile_merge',
1182: 'HZ_FINANCIAL_PROFILE','HZ_PARTIES',
1183: 'FINANCIAL_PROFILE_ID', 'PARTY_ID',x_return_status);
1184:
1185: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1186: l_dup_exists := HZ_MERGE_DUP_CHECK.check_financial_profile_dup(
1257:
1258: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1259: p_to_fk_id, p_par_entity_name,
1260: 'HZ_MERGE_PKG.contact_point_merge',
1261: 'HZ_CONTACT_POINTS','HZ_PARTIES',
1262: 'CONTACT_POINT_ID', 'PARTY_ID',x_return_status);
1263:
1264: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1265: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_point_dup(
1263:
1264: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1265: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_point_dup(
1266: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
1267: 'HZ_PARTIES',x_return_status);
1268: END IF;
1269:
1270: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
1271: do_contact_point_transfer(p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
1350:
1351: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1352: p_to_fk_id, p_par_entity_name,
1353: 'HZ_MERGE_PKG.references_merge',
1354: 'HZ_REFERENCES','HZ_PARTIES',
1355: 'REFERENCE_ID', 'PARTY_ID',x_return_status);
1356:
1357: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1358: l_dup_exists := HZ_MERGE_DUP_CHECK.check_references_dup(
1430:
1431: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1432: p_to_fk_id, p_par_entity_name,
1433: 'HZ_MERGE_PKG.certification_merge',
1434: 'HZ_CERTIFICATIONS','HZ_PARTIES',
1435: 'CERTIFICATION_ID', 'PARTY_ID',x_return_status);
1436:
1437: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1438: l_dup_exists := HZ_MERGE_DUP_CHECK.check_certification_dup(
1479:
1480: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1481: p_to_fk_id, p_par_entity_name,
1482: 'HZ_MERGE_PKG.credit_ratings_merge',
1483: 'HZ_CREDIT_RATINGS','HZ_PARTIES',
1484: 'CREDIT_RATING_ID', 'PARTY_ID',x_return_status);
1485:
1486: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1487: l_dup_exists := HZ_MERGE_DUP_CHECK.check_credit_ratings_dup(
1529:
1530: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1531: p_to_fk_id, p_par_entity_name,
1532: 'HZ_MERGE_PKG.security_issued_merge',
1533: 'HZ_SECURITY_ISSUED','HZ_PARTIES',
1534: 'SECURITY_ISSUED_ID', 'PARTY_ID',x_return_status);
1535:
1536: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1537:
1579:
1580: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1581: p_to_fk_id, p_par_entity_name,
1582: 'HZ_MERGE_PKG.financial_reports_merge',
1583: 'HZ_FINANCIAL_REPORTS','HZ_PARTIES',
1584: 'FINANCIAL_REPORT_ID', 'PARTY_ID',x_return_status);
1585:
1586: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1587: l_dup_exists := HZ_MERGE_DUP_CHECK.check_financial_reports_dup(
1629:
1630: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1631: p_to_fk_id, p_par_entity_name,
1632: 'HZ_MERGE_PKG.org_indicators_merge',
1633: 'HZ_ORGANIZATION_INDICATORS','HZ_PARTIES',
1634: 'ORGANIZATION_INDICATOR_ID', 'PARTY_ID',x_return_status);
1635:
1636: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1637: l_dup_exists := HZ_MERGE_DUP_CHECK.check_org_indicators_dup(
1678:
1679: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1680: p_to_fk_id, p_par_entity_name,
1681: 'HZ_MERGE_PKG.org_ind_reference_merge',
1682: 'HZ_INDUSTRIAL_REFERENCE','HZ_PARTIES',
1683: 'INDUSTRY_REFERENCE_ID', 'PARTY_ID',x_return_status);
1684:
1685: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1686: l_dup_exists := HZ_MERGE_DUP_CHECK.check_ind_reference_dup(
1728:
1729: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1730: p_to_fk_id, p_par_entity_name,
1731: 'HZ_MERGE_PKG.per_interest_merge',
1732: 'HZ_PERSON_INTEREST','HZ_PARTIES',
1733: 'PERSON_INTEREST_ID', 'PARTY_ID',x_return_status);
1734:
1735: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1736: l_dup_exists := HZ_MERGE_DUP_CHECK.check_per_interest_dup(
1777:
1778: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1779: p_to_fk_id, p_par_entity_name,
1780: 'HZ_MERGE_PKG.citizenship_merge',
1781: 'HZ_CITIZENSHIP','HZ_PARTIES',
1782: 'CITIZENSHIP_ID', 'PARTY_ID',x_return_status);
1783:
1784: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1785: l_dup_exists := HZ_MERGE_DUP_CHECK.check_citizenship_dup(
1826:
1827: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1828: p_to_fk_id, p_par_entity_name,
1829: 'HZ_MERGE_PKG.education_merge',
1830: 'HZ_EDUCATION','HZ_PARTIES',
1831: 'EDUCATION_ID', 'PARTY_ID',x_return_status);
1832:
1833: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1834: l_dup_exists := HZ_MERGE_DUP_CHECK.check_education_dup(
1875:
1876: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1877: p_to_fk_id, p_par_entity_name,
1878: 'HZ_MERGE_PKG.education_school_merge',
1879: 'HZ_EDUCATION','HZ_PARTIES',
1880: 'EDUCATION_ID', 'SCHOOL_PARTY_ID',x_return_status);
1881:
1882: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1883: l_dup_exists := HZ_MERGE_DUP_CHECK.check_education_dup(
1924:
1925: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1926: p_to_fk_id, p_par_entity_name,
1927: 'HZ_MERGE_PKG.emp_history_merge',
1928: 'HZ_EMPLOYMENT_HISTORY','HZ_PARTIES',
1929: 'EMPLOYMENT_HISTORY_ID', 'PARTY_ID',x_return_status);
1930:
1931: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1932: l_dup_exists := HZ_MERGE_DUP_CHECK.check_emp_history_dup(
1973:
1974: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1975: p_to_fk_id, p_par_entity_name,
1976: 'HZ_MERGE_PKG.emp_history_employed_merge',
1977: 'HZ_EMPLOYMENT_HISTORY','HZ_PARTIES',
1978: 'EMPLOYMENT_HISTORY_ID','EMPLOYED_BY_PARTY_ID',x_return_status);
1979:
1980: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1981: l_dup_exists := HZ_MERGE_DUP_CHECK.check_emp_history_dup(
2071:
2072: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2073: p_to_fk_id, p_par_entity_name,
2074: 'HZ_MERGE_PKG.code_assignment_merge',
2075: 'HZ_CODE_ASSIGNMENTS','HZ_PARTIES',
2076: 'CODE_ASSIGNMENT_ID', 'PARTY_ID',x_return_status);
2077:
2078: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2079: l_dup_exists := HZ_MERGE_DUP_CHECK.check_code_assignment_dup(
2077:
2078: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2079: l_dup_exists := HZ_MERGE_DUP_CHECK.check_code_assignment_dup(
2080: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2081: 'HZ_PARTIES', x_return_status);
2082: END IF;
2083:
2084: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2085: do_code_assignment_transfer(p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2267:
2268: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2269: p_to_fk_id, p_par_entity_name,
2270: 'HZ_MERGE_PKG.per_languages_merge',
2271: 'HZ_PERSON_LANGUAGE','HZ_PARTIES',
2272: 'LANGUAGE_USE_REFERENCE_ID', 'PARTY_ID',x_return_status);
2273:
2274: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2275: l_dup_exists := HZ_MERGE_DUP_CHECK.check_languages_dup(
2459:
2460: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2461: p_to_fk_id, p_par_entity_name,
2462: 'HZ_MERGE_PKG.contact_preference_merge',
2463: 'HZ_CONTACT_PREFERENCES','HZ_PARTIES',
2464: 'CONTACT_PREFERENCE_ID', 'PARTY_ID',x_return_status);
2465:
2466: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2467: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_preference_dup(
2465:
2466: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2467: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_preference_dup(
2468: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2469: 'HZ_PARTIES',x_return_status);
2470: END IF;
2471:
2472: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2473: do_contact_pref_transfer(p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2666:
2667: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2668: p_to_fk_id, p_par_entity_name,
2669: 'HZ_MERGE_PKG.displayed_duns_merge',
2670: 'HZ_ORGANIZATION_PROFILES','HZ_PARTIES',
2671: 'ORGANIZATION_PROFILE_ID', 'PARTY_ID',x_return_status);
2672:
2673: do_displayed_duns_merge(p_from_id, l_to_id, p_from_fk_id, p_to_fk_id,
2674: x_return_status);
2997: SELECT contact_point_type,primary_flag, url, email_address,
2998: contact_point_purpose,phone_line_type, phone_country_code,
2999: phone_area_code,phone_number,phone_extension ,primary_by_purpose
3000: FROM HZ_CONTACT_POINTS
3001: WHERE owner_table_name = 'HZ_PARTIES'
3002: AND contact_point_id = p_from_id
3003: AND rownum=1;
3004:
3005: ----------------
3062: INTO l_exists
3063: FROM HZ_CONTACT_POINTS
3064: WHERE PRIMARY_BY_PURPOSE = 'Y'
3065: AND CONTACT_POINT_PURPOSE = l_contact_point_purpose
3066: AND OWNER_TABLE_NAME = 'HZ_PARTIES'
3067: AND CONTACT_POINT_TYPE = l_contact_point_type
3068: AND OWNER_TABLE_ID = p_to_fk_id
3069: AND ROWNUM = 1;
3070: EXCEPTION
3078: SELECT 'Exists'
3079: INTO l_exists
3080: FROM HZ_CONTACT_POINTS
3081: WHERE primary_flag = 'Y'
3082: AND OWNER_TABLE_NAME = 'HZ_PARTIES'
3083: AND CONTACT_POINT_TYPE= l_contact_point_type
3084: AND OWNER_TABLE_ID = p_to_fk_id
3085: AND ROWNUM = 1;
3086: EXCEPTION
4061: AND OC.PARTY_RELATIONSHIP_ID = PR.RELATIONSHIP_ID
4062: AND PR.OBJECT_ID = PR2.OBJECT_ID
4063: AND PR2.RELATIONSHIP_ID = OC2.PARTY_RELATIONSHIP_ID
4064: AND OC2.ORG_CONTACT_ID = p_to_fk_id
4065: AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4066: AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4067: AND PR.DIRECTIONAL_FLAG = 'F'
4068: AND PR2.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4069: AND PR2.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4062: AND PR.OBJECT_ID = PR2.OBJECT_ID
4063: AND PR2.RELATIONSHIP_ID = OC2.PARTY_RELATIONSHIP_ID
4064: AND OC2.ORG_CONTACT_ID = p_to_fk_id
4065: AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4066: AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4067: AND PR.DIRECTIONAL_FLAG = 'F'
4068: AND PR2.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4069: AND PR2.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4070: AND PR2.DIRECTIONAL_FLAG = 'F'
4064: AND OC2.ORG_CONTACT_ID = p_to_fk_id
4065: AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4066: AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4067: AND PR.DIRECTIONAL_FLAG = 'F'
4068: AND PR2.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4069: AND PR2.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4070: AND PR2.DIRECTIONAL_FLAG = 'F'
4071: AND ROWNUM = 1;
4072: EXCEPTION
4065: AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4066: AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4067: AND PR.DIRECTIONAL_FLAG = 'F'
4068: AND PR2.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4069: AND PR2.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4070: AND PR2.DIRECTIONAL_FLAG = 'F'
4071: AND ROWNUM = 1;
4072: EXCEPTION
4073: WHEN NO_DATA_FOUND THEN
4211: SELECT 'Exists'
4212: INTO l_exists
4213: FROM HZ_CODE_ASSIGNMENTS
4214: WHERE primary_flag = 'Y'
4215: AND OWNER_TABLE_NAME = 'HZ_PARTIES'
4216: AND OWNER_TABLE_ID = p_to_fk_id
4217: AND CLASS_CATEGORY = l_class_category
4218: AND CONTENT_SOURCE_TYPE = l_cont_src
4219: AND ROWNUM = 1;
4239:
4240: --bug 4582789
4241:
4242: IF l_primary_flag = 'Y' AND l_class_category = 'CUSTOMER_CATEGORY' THEN
4243: UPDATE hz_parties
4244: SET category_code = l_class_code,
4245: last_update_date = hz_utility_pub.last_update_date,
4246: last_updated_by = hz_utility_pub.user_id,
4247: last_update_login = hz_utility_pub.last_update_login
5105: l_object_type HZ_RELATIONSHIPS.OBJECT_TYPE%TYPE;
5106: l_self_rel_flag VARCHAR2(1);
5107:
5108: l_rel_party_id HZ_RELATIONSHIPS.PARTY_ID%TYPE;
5109: l_new_obj_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5110: l_subject_name HZ_PARTIES.PARTY_NAME%TYPE;
5111: l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
5112: from_cont_source HZ_RELATIONSHIPS.ACTUAL_CONTENT_SOURCE%TYPE; --5404244
5113: from_cont_source_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
5106: l_self_rel_flag VARCHAR2(1);
5107:
5108: l_rel_party_id HZ_RELATIONSHIPS.PARTY_ID%TYPE;
5109: l_new_obj_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5110: l_subject_name HZ_PARTIES.PARTY_NAME%TYPE;
5111: l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
5112: from_cont_source HZ_RELATIONSHIPS.ACTUAL_CONTENT_SOURCE%TYPE; --5404244
5113: from_cont_source_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
5114:
5107:
5108: l_rel_party_id HZ_RELATIONSHIPS.PARTY_ID%TYPE;
5109: l_new_obj_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5110: l_subject_name HZ_PARTIES.PARTY_NAME%TYPE;
5111: l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
5112: from_cont_source HZ_RELATIONSHIPS.ACTUAL_CONTENT_SOURCE%TYPE; --5404244
5113: from_cont_source_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
5114:
5115: --6696774 Start
5289: if l_rel_party_id is not null then
5290:
5291: select sub.party_name , obj.party_name , rel.party_number
5292: into l_subject_name , l_new_obj_party_name , l_rel_party_number
5293: from hz_parties sub , hz_parties obj , hz_parties rel
5294: where sub.party_id = l_subject_id
5295: and obj.party_id = p_to_fk_id
5296: and rel.party_id = l_rel_party_id;
5297:
5294: where sub.party_id = l_subject_id
5295: and obj.party_id = p_to_fk_id
5296: and rel.party_id = l_rel_party_id;
5297:
5298: UPDATE HZ_PARTIES
5299: SET PARTY_NAME = SUBSTRB(l_subject_name || '-' ||
5300: l_new_obj_party_name || '-' ||
5301: l_rel_party_number, 1, 360)
5302: WHERE party_id = l_rel_party_id;
5332: program_update_date = sysdate
5333: WHERE relationship_id = p_from_id;
5334: ---Start of Bug:3880218----
5335: IF l_rel_party_id is not null THEN
5336: UPDATE HZ_PARTIES
5337: SET STATUS = 'I',
5338: last_update_date = hz_utility_pub.last_update_date,
5339: last_updated_by = hz_utility_pub.user_id,
5340: last_update_login = hz_utility_pub.last_update_login,
5355: ----End of DlProject Phase2--------------------
5356: ELSE
5357: --bug 4867151--start
5358: IF l_rel_party_id is not null THEN
5359: UPDATE HZ_PARTIES
5360: SET STATUS = 'M',
5361: last_update_date = hz_utility_pub.last_update_date,
5362: last_updated_by = hz_utility_pub.user_id,
5363: last_update_login = hz_utility_pub.last_update_login,
5497: l_object_type HZ_RELATIONSHIPS.OBJECT_TYPE%TYPE;
5498: l_self_rel_flag VARCHAR2(1);
5499:
5500: l_rel_party_id HZ_RELATIONSHIPS.PARTY_ID%TYPE;
5501: l_new_subj_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5502: l_object_name HZ_PARTIES.PARTY_NAME%TYPE;
5503: l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
5504: from_cont_source HZ_RELATIONSHIPS.ACTUAL_CONTENT_SOURCE%TYPE; --5404244
5505: from_cont_source_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
5498: l_self_rel_flag VARCHAR2(1);
5499:
5500: l_rel_party_id HZ_RELATIONSHIPS.PARTY_ID%TYPE;
5501: l_new_subj_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5502: l_object_name HZ_PARTIES.PARTY_NAME%TYPE;
5503: l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
5504: from_cont_source HZ_RELATIONSHIPS.ACTUAL_CONTENT_SOURCE%TYPE; --5404244
5505: from_cont_source_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
5506:
5499:
5500: l_rel_party_id HZ_RELATIONSHIPS.PARTY_ID%TYPE;
5501: l_new_subj_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5502: l_object_name HZ_PARTIES.PARTY_NAME%TYPE;
5503: l_rel_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
5504: from_cont_source HZ_RELATIONSHIPS.ACTUAL_CONTENT_SOURCE%TYPE; --5404244
5505: from_cont_source_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
5506:
5507: --6696774 start
5686: if l_rel_party_id is not null then
5687:
5688: select sub.party_name , obj.party_name , rel.party_number
5689: into l_new_subj_party_name , l_object_name , l_rel_party_number
5690: from hz_parties sub , hz_parties obj , hz_parties rel
5691: where sub.party_id = p_to_fk_id
5692: and obj.party_id = l_object_id
5693: and rel.party_id = l_rel_party_id;
5694:
5691: where sub.party_id = p_to_fk_id
5692: and obj.party_id = l_object_id
5693: and rel.party_id = l_rel_party_id;
5694:
5695: UPDATE HZ_PARTIES
5696: SET PARTY_NAME = SUBSTRB(l_new_subj_party_name || '-' ||
5697: l_object_name || '-' ||
5698: l_rel_party_number, 1, 360)
5699: WHERE party_id = l_rel_party_id;
5732: WHERE relationship_id = p_from_id;
5733:
5734: ---Start of Bug:3880218----
5735: IF l_rel_party_id is not null THEN
5736: UPDATE HZ_PARTIES
5737: SET STATUS = 'I',
5738: last_update_date = hz_utility_pub.last_update_date,
5739: last_updated_by = hz_utility_pub.user_id,
5740: last_update_login = hz_utility_pub.last_update_login,
5755: ----End of DlProject Phase2--------------------
5756: ---bug 4867151 start
5757: ELSE
5758: IF l_rel_party_id is not null THEN
5759: UPDATE HZ_PARTIES
5760: SET STATUS = 'M',
5761: last_update_date = hz_utility_pub.last_update_date,
5762: last_updated_by = hz_utility_pub.user_id,
5763: last_update_login = hz_utility_pub.last_update_login,
5832: CURSOR c_merge_to_party_reln IS
5833: SELECT relationship_id
5834: FROM HZ_RELATIONSHIPS --4500011
5835: WHERE party_id = p_to_fk_id
5836: AND subject_table_name = 'HZ_PARTIES'
5837: AND object_table_name = 'HZ_PARTIES'
5838: AND directional_flag = 'F';
5839:
5840: l_to_preln_id NUMBER;
5833: SELECT relationship_id
5834: FROM HZ_RELATIONSHIPS --4500011
5835: WHERE party_id = p_to_fk_id
5836: AND subject_table_name = 'HZ_PARTIES'
5837: AND object_table_name = 'HZ_PARTIES'
5838: AND directional_flag = 'F';
5839:
5840: l_to_preln_id NUMBER;
5841:
6147:
6148: CURSOR c_from_party_type IS
6149: SELECT party_type
6150: FROM HZ_PARTY_SITES ps,
6151: HZ_PARTIES p
6152: WHERE ps.party_id = p.party_id
6153: AND ps.party_site_id = p_from_id;
6154:
6155: l_from_last_upd_date DATE;
6191: --l_loc_id HZ_LOC_ASSIGNMENTS.LOC_ID%TYPE; bug 4569674
6192: --l_org_id HZ_LOC_ASSIGNMENTS.ORG_ID%TYPE;
6193: l_from_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6194: l_to_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6195: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6196: to_party_loc_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6197: map_ps_id HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
6198: BEGIN
6199:
6288: UPDATE HZ_PARTY_SITES
6289: SET location_id = l_to_location_id
6290: WHERE party_site_id IN (
6291: SELECT psr.party_site_id
6292: FROM hz_parties p,
6293: hz_party_sites psr,
6294: hz_party_sites pso,
6295: hz_relationships r
6296: WHERE pso.party_site_id = p_from_id
6312:
6313: IF (l_from_party_id = l_to_party_id )THEN
6314: IF(l_ident_flag = 'Y') THEN
6315: IF l_cont_source_to = 'USER_ENTERED' THEN
6316: UPDATE hz_parties
6317: SET country = l_country,
6318: address1 = l_address1,
6319: address2 = l_address2,
6320: address3 = l_address3,
6338: identifying_address_flag = 'N'
6339: WHERE party_site_id = p_from_id;
6340:
6341: END IF;
6342: UPDATE hz_parties
6343: SET country = l_country,
6344: address1 = l_address1,
6345: address2 = l_address2,
6346: address3 = l_address3,
6594: ) IS
6595:
6596: CURSOR c_party_type(cp_party_id NUMBER) IS
6597: SELECT party_type
6598: FROM HZ_PARTIES
6599: WHERE party_id = cp_party_id;
6600:
6601: CURSOR c_duns IS
6602: SELECT duns_number_c, last_update_date, organization_profile_id
6620: WHERE content_source_type = 'DNB'
6621: AND subject_id = p_from_id
6622: AND object_id = x_to_id
6623: AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
6624: AND subject_table_name = 'HZ_PARTIES'
6625: AND object_table_name = 'HZ_PARTIES'
6626: AND directional_flag = 'F';
6627:
6628: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6621: AND subject_id = p_from_id
6622: AND object_id = x_to_id
6623: AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
6624: AND subject_table_name = 'HZ_PARTIES'
6625: AND object_table_name = 'HZ_PARTIES'
6626: AND directional_flag = 'F';
6627:
6628: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6629: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6624: AND subject_table_name = 'HZ_PARTIES'
6625: AND object_table_name = 'HZ_PARTIES'
6626: AND directional_flag = 'F';
6627:
6628: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6629: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6630: l_from_duns_number VARCHAR2(255);
6631: l_to_duns_number VARCHAR2(255);
6632: l_temp NUMBER;
6625: AND object_table_name = 'HZ_PARTIES'
6626: AND directional_flag = 'F';
6627:
6628: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6629: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6630: l_from_duns_number VARCHAR2(255);
6631: l_to_duns_number VARCHAR2(255);
6632: l_temp NUMBER;
6633:
6674: RETURN;
6675: END IF;
6676:
6677: -- Update and set status to merged
6678: UPDATE HZ_PARTIES
6679: SET
6680: STATUS = 'M',
6681: last_update_date = hz_utility_pub.last_update_date,
6682: last_updated_by = hz_utility_pub.user_id,
6695: p_init_msg_list => FND_API.G_FALSE,
6696: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6697: p_old_owner_table_id => p_from_id,
6698: p_new_owner_table_id => x_to_id,
6699: p_owner_table_name =>'HZ_PARTIES',
6700: p_orig_system => null,
6701: p_orig_system_reference => null,
6702: p_reason_code => 'MERGED',
6703: x_return_status => x_return_status,
6805: --
6806: -- PRIVATE PROCEDURE do_denormalize_contact_point
6807: --
6808: -- DESCRIPTION
6809: -- Private procedure to denormalize some type of contact point to hz_parties.
6810: --
6811: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
6812: --
6813: -- ARGUMENTS
6846: ) IS
6847: BEGIN
6848:
6849: IF p_contact_point_type = 'WEB' THEN
6850: UPDATE hz_parties
6851: SET url = p_url,
6852: last_update_date = hz_utility_v2pub.last_update_date,
6853: last_updated_by = hz_utility_v2pub.last_updated_by,
6854: last_update_login = hz_utility_v2pub.last_update_login,
6857: program_id = hz_utility_v2pub.program_id,
6858: program_update_date = sysdate
6859: WHERE party_id = p_party_id;
6860: ELSIF p_contact_point_type = 'EMAIL' THEN
6861: UPDATE hz_parties
6862: SET email_address = p_email_address,
6863: last_update_date = hz_utility_v2pub.last_update_date,
6864: last_updated_by = hz_utility_v2pub.last_updated_by,
6865: last_update_login = hz_utility_v2pub.last_update_login,
6868: program_id = hz_utility_v2pub.program_id,
6869: program_update_date = sysdate
6870: WHERE party_id = p_party_id;
6871: ELSIF p_contact_point_type = 'PHONE' THEN
6872: UPDATE hz_parties
6873: SET primary_phone_contact_pt_id = p_phone_contact_pt_id,
6874: primary_phone_purpose = p_phone_purpose,
6875: primary_phone_line_type = p_phone_line_type,
6876: primary_phone_country_code = p_phone_country_code,