[Home] [Help]
1352: SAVEPOINT map_pub;
1353:
1354: --MOSR Changes
1355: --If p_inactivate_flag is 'Y' then in activate Source System mapping
1356: -- for HZ_PARTIES, HZ_PARTY_SITES and HZ_CONTACT_POINTS
1357: --bug 3107162: Passed local variable l_inactivate flag instead of p_inactivate_flag
1358: --bug 4287144: pass enquiry duns
1359: check_mosr_mapping (
1360: lpad(to_char(nvl(l_interface_rec.enquiry_duns,l_interface_rec.duns_number)),9,'0'),
1661: l_party_rel_rec.relationship_code := 'HEADQUARTERS_OF';
1662: l_party_rel_rec.actual_content_source := l_interface_rec.content_source_type;
1663: l_party_rel_rec.subject_type := 'ORGANIZATION';
1664: l_party_rel_rec.object_type := 'ORGANIZATION';
1665: l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
1666: l_party_rel_rec.object_table_name := 'HZ_PARTIES';
1667: l_party_rel_rec.created_by_module := 'TCA_DNB_MAPPING';
1668:
1669: store_party_rel(
1662: l_party_rel_rec.actual_content_source := l_interface_rec.content_source_type;
1663: l_party_rel_rec.subject_type := 'ORGANIZATION';
1664: l_party_rel_rec.object_type := 'ORGANIZATION';
1665: l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
1666: l_party_rel_rec.object_table_name := 'HZ_PARTIES';
1667: l_party_rel_rec.created_by_module := 'TCA_DNB_MAPPING';
1668:
1669: store_party_rel(
1670: l_party_rel_rec,
1870: FROM -- hz_organization_profiles org_pro,
1871: hz_orig_sys_references hosr
1872: WHERE -- nvl(org_pro.party_id, '-999') = hosr.owner_table_id AND
1873: hosr.orig_system = 'DNB'
1874: AND hosr.owner_table_name = 'HZ_PARTIES'
1875: AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
1876: AND hosr.status='A'
1877: AND hosr.orig_system_reference = p_duns_number_c
1878: AND hosr.party_id <> nvl(p_party_id,-1);
1917: AND hosr.orig_system_reference like p_duns_number_c || '%'
1918: AND hosr.party_id <> nvl(p_party_id,-1);
1919: --4227564
1920: BEGIN
1921: --Inactivate HZ_PARTIES records
1922: BEGIN
1923: OPEN c_check_party_mapping;
1924:
1925: FETCH c_check_party_mapping INTO l_orig_system_ref_id, l_object_version_number, l_start_date_active,
2085: hz_orig_sys_references hosr
2086: WHERE
2087: --nvl(org_pro.party_id, '-999') = hosr.owner_table_id AND
2088: hosr.orig_system = 'DNB'
2089: AND hosr.owner_table_name = 'HZ_PARTIES'
2090: -- AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
2091: AND hosr.status='A'
2092: AND hosr.orig_system_reference <> p_duns_number_c
2093: AND hosr.owner_table_id = p_organization_rec.party_rec.party_id;
2137: BEGIN
2138: --bug 4287144
2139: x_return_status := FND_API.G_RET_STS_SUCCESS;
2140:
2141: --Inactivate HZ_PARTIES records
2142: BEGIN
2143: p_duns_number_c := p_organization_rec.duns_number_c;
2144:
2145: OPEN c_check_party_mapping;
2377: BEGIN
2378: SELECT hosr.orig_system_ref_id INTO l_orig_sys_ref_id
2379: FROM hz_orig_sys_references hosr
2380: WHERE hosr.orig_system = 'DNB'
2381: AND hosr.owner_table_name = 'HZ_PARTIES'
2382: AND hosr.status='A'
2383: AND hosr.owner_table_id = l_organization_rec.party_rec.party_id;
2384: EXCEPTION
2385: WHEN NO_DATA_FOUND THEN
2385: WHEN NO_DATA_FOUND THEN
2386:
2387: l_orig_sys_reference_rec.orig_system := 'DNB';
2388: l_orig_sys_reference_rec.orig_system_reference := l_organization_rec.duns_number_c;
2389: l_orig_sys_reference_rec.owner_table_name := 'HZ_PARTIES';
2390: l_orig_sys_reference_rec.owner_table_id := l_organization_rec.party_rec.party_id;
2391: l_orig_sys_reference_rec.start_date_active := sysdate;
2392: l_orig_sys_reference_rec.party_id := l_organization_rec.party_rec.party_id;
2393: l_orig_sys_reference_rec.status := 'A';
3235: l_exist VARCHAR2(1);
3236: l_mosr_mapping_exist VARCHAR2(1);
3237: l_ue_exist VARCHAR2(1);
3238: l_profile_obsolete_col VARCHAR2(1);
3239: l_party_id HZ_PARTIES.PARTY_ID%TYPE;
3240: l_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
3241:
3242: CURSOR check_party_mosr_mapping (p_orig_system IN VARCHAR2,
3243: p_orig_system_ref IN VARCHAR2,
3236: l_mosr_mapping_exist VARCHAR2(1);
3237: l_ue_exist VARCHAR2(1);
3238: l_profile_obsolete_col VARCHAR2(1);
3239: l_party_id HZ_PARTIES.PARTY_ID%TYPE;
3240: l_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
3241:
3242: CURSOR check_party_mosr_mapping (p_orig_system IN VARCHAR2,
3243: p_orig_system_ref IN VARCHAR2,
3244: p_owner_table_name IN VARCHAR2)
3318: END IF;
3319:
3320: p_organization_rec.application_id := 222;
3321: p_organization_rec.created_by_module := 'TCA_DNB_MAPPING';
3322: OPEN check_party_mosr_mapping ( 'DNB', p_organization_rec.DUNS_NUMBER_C, 'HZ_PARTIES');
3323: FETCH check_party_mosr_mapping into l_mosr_mapping_exist;
3324: IF l_mosr_mapping_exist = 'Y' THEN
3325: p_organization_rec.party_rec.orig_system := NULL;
3326: ELSE
3383:
3384: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_ORG: party exists, org profile exists ');
3385:
3386: SELECT object_version_number INTO l_object_version_number
3387: FROM hz_parties
3388: WHERE party_id= p_organization_rec.party_rec.party_id;
3389: p_organization_rec.party_rec.orig_system := NULL;
3390: --p_organization_rec.party_rec.orig_system_reference := NULL;
3391: p_organization_rec.created_by_module := NULL;
3501: --
3502: -- store classification
3503: --
3504: p_code_assignment_rec.code_assignment_id := null;
3505: p_code_assignment_rec.owner_table_name := 'HZ_PARTIES';
3506: p_code_assignment_rec.owner_table_id := p_interface_rec.party_id;
3507: p_code_assignment_rec.class_category := p_interface_rec.sic_code_type;
3508: --bug 4169070
3509: -- p_code_assignment_rec.content_source_type := NVL( p_interface_rec.content_source_type, HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE);
4455:
4456: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_CONTACT_POINT (+) ');
4457:
4458: p_contact_points_rec.contact_point_type := 'PHONE';
4459: p_contact_points_rec.owner_table_name := 'HZ_PARTIES';
4460: p_contact_points_rec.contact_point_id := NULL; --Reset it.
4461:
4462: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_CONTACT_POINT: actual_content_source = ' || p_contact_points_rec.actual_content_source);
4463:
4487: FROM
4488: ( SELECT contact_point_id, object_version_number, orig_system_reference,rank() over (
4489: partition by owner_table_id order by last_update_date desc,object_version_number desc,contact_point_id desc) r
4490: FROM hz_contact_points hcp
4491: WHERE owner_table_name = 'HZ_PARTIES'
4492: AND owner_table_id = p_contact_points_rec.owner_table_id
4493: AND contact_point_type = 'PHONE'
4494: AND phone_line_type = p_phone_rec.PHONE_LINE_TYPE
4495: AND actual_content_source = p_contact_points_rec.actual_content_source
6101: WHERE org.duns_number_c = lpad(to_char(p_related_duns_rec.DUNS_NUMBER),9,'0')
6102: AND org.actual_content_source = p_related_duns_rec.content_source_type
6103: AND org.effective_end_date is NULL
6104: AND org.party_id = ssm.owner_table_id
6105: AND ssm.owner_table_name = 'HZ_PARTIES'
6106: AND ssm.orig_system = 'DNB'
6107: AND ssm.status = 'A'
6108: AND rownum = 1;
6109:
6121: l_organization_profile_id NUMBER;
6122: l_party_id NUMBER;
6123: l_profile VARCHAR2(1);
6124: l_last_update_date1 DATE;
6125: x_party_id HZ_PARTIES.PARTY_ID%TYPE;
6126: x_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
6127:
6128:
6129: BEGIN
6122: l_party_id NUMBER;
6123: l_profile VARCHAR2(1);
6124: l_last_update_date1 DATE;
6125: x_party_id HZ_PARTIES.PARTY_ID%TYPE;
6126: x_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
6127:
6128:
6129: BEGIN
6130:
6138: l_party_rel_rec.relationship_type := p_related_duns_rec.RELATIONSHIP_TYPE;
6139: l_party_rel_rec.relationship_code := p_related_duns_rec.RELATIONSHIP_CODE;
6140: l_party_rel_rec.subject_type := 'ORGANIZATION';
6141: l_party_rel_rec.object_type := 'ORGANIZATION';
6142: l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
6143: l_party_rel_rec.object_table_name := 'HZ_PARTIES';
6144: l_party_rel_rec.created_by_module := 'TCA_DNB_MAPPING';
6145:
6146: l_contact_points_rec.actual_content_source := p_related_duns_rec.content_source_type;
6139: l_party_rel_rec.relationship_code := p_related_duns_rec.RELATIONSHIP_CODE;
6140: l_party_rel_rec.subject_type := 'ORGANIZATION';
6141: l_party_rel_rec.object_type := 'ORGANIZATION';
6142: l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
6143: l_party_rel_rec.object_table_name := 'HZ_PARTIES';
6144: l_party_rel_rec.created_by_module := 'TCA_DNB_MAPPING';
6145:
6146: l_contact_points_rec.actual_content_source := p_related_duns_rec.content_source_type;
6147: l_contact_points_rec.created_by_module := 'TCA_DNB_MAPPING';
6388: AND rel.relationship_type = p_party_rel_rec.relationship_type
6389: AND rel.actual_content_source = p_party_rel_rec.actual_content_source
6390: AND rel.start_date <= sysdate
6391: AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
6392: AND rel.subject_table_name = 'HZ_PARTIES'
6393: AND rel.object_table_name = 'HZ_PARTIES';
6394: -- AND rel.DIRECTIONAL_FLAG = 'F';
6395:
6396: l_msg_count NUMBER;
6389: AND rel.actual_content_source = p_party_rel_rec.actual_content_source
6390: AND rel.start_date <= sysdate
6391: AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
6392: AND rel.subject_table_name = 'HZ_PARTIES'
6393: AND rel.object_table_name = 'HZ_PARTIES';
6394: -- AND rel.DIRECTIONAL_FLAG = 'F';
6395:
6396: l_msg_count NUMBER;
6397: l_msg_data VARCHAR2(2000);
6399: l_object_version_number2 NUMBER;
6400:
6401: x_relationship_id NUMBER;
6402: x_party_id NUMBER;
6403: x_party_number hz_parties.party_number%TYPE;
6404:
6405: l_profile VARCHAR2(1);
6406:
6407: TYPE l_old_rel_id_table IS TABLE OF hz_relationships.relationship_id%TYPE
6437: AND rel.relationship_type = l_rel_type
6438: AND rel.actual_content_source = p_party_rel_rec.actual_content_source
6439: AND rel.start_date <= sysdate
6440: AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
6441: AND rel.subject_table_name = 'HZ_PARTIES'
6442: AND rel.object_table_name = 'HZ_PARTIES';
6443:
6444:
6445: BEGIN
6438: AND rel.actual_content_source = p_party_rel_rec.actual_content_source
6439: AND rel.start_date <= sysdate
6440: AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
6441: AND rel.subject_table_name = 'HZ_PARTIES'
6442: AND rel.object_table_name = 'HZ_PARTIES';
6443:
6444:
6445: BEGIN
6446:
6471:
6472: SELECT object_version_number INTO l_obj_ver_no
6473: FROM hz_relationships
6474: WHERE relationship_id = l_rel_id
6475: AND subject_table_name = 'HZ_PARTIES'
6476: AND object_table_name = 'HZ_PARTIES'
6477: AND DIRECTIONAL_FLAG = 'F';
6478:
6479:
6472: SELECT object_version_number INTO l_obj_ver_no
6473: FROM hz_relationships
6474: WHERE relationship_id = l_rel_id
6475: AND subject_table_name = 'HZ_PARTIES'
6476: AND object_table_name = 'HZ_PARTIES'
6477: AND DIRECTIONAL_FLAG = 'F';
6478:
6479:
6480: l_party_rel_rec.actual_content_source := p_party_rel_rec.actual_content_source;
6546: --End date old party rel.
6547: SELECT object_version_number INTO l_object_version_number1
6548: FROM hz_relationships
6549: WHERE relationship_id = l_old_rel_ids(i)
6550: AND subject_table_name = 'HZ_PARTIES'
6551: AND object_table_name = 'HZ_PARTIES'
6552: AND DIRECTIONAL_FLAG = 'F';
6553:
6554: --do not need to pass in LUD of parties as party is not updated.
6547: SELECT object_version_number INTO l_object_version_number1
6548: FROM hz_relationships
6549: WHERE relationship_id = l_old_rel_ids(i)
6550: AND subject_table_name = 'HZ_PARTIES'
6551: AND object_table_name = 'HZ_PARTIES'
6552: AND DIRECTIONAL_FLAG = 'F';
6553:
6554: --do not need to pass in LUD of parties as party is not updated.
6555: