[Home] [Help]
588: l_sql_err_message VARCHAR2(2000);
589:
590: CURSOR c_contact IS
591: SELECT oc.org_contact_id
592: FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc
593: WHERE pr.relationship_id = oc.party_relationship_id
594: AND pr.subject_id = p_party_id
595: AND pr.subject_table_name = 'HZ_PARTIES'
596: AND pr.object_table_name = 'HZ_PARTIES'
669: END;
670: IF l_party_type = 'PARTY_RELATIONSHIP' THEN
671: BEGIN
672: SELECT r.object_id, org_contact_id INTO l_party_id1,l_org_contact_id
673: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
674: WHERE r.party_id = l_party_id
675: AND r.relationship_id = oc.party_relationship_id
676: AND r.directional_flag='F'
677: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
720: END IF;
721: ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN
722: IF (p_create_upd <> 'D') THEN
723: SELECT r.object_id INTO l_party_id
724: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
725: WHERE oc.org_contact_id = p_org_contact_id
726: AND oc.party_relationship_id = r.relationship_id
727: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
728: AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
731: AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
732: AND (r.status is null OR r.status = 'A' or r.status = 'I') ;
733: ELSIF (p_create_upd = 'D') THEN
734: SELECT r.object_id INTO l_party_id
735: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
736: WHERE oc.org_contact_id = p_org_contact_id
737: AND oc.party_relationship_id = r.relationship_id
738: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
739: AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
800:
801: IF l_party_type = 'PARTY_RELATIONSHIP' THEN
802: BEGIN
803: SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
804: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
805: WHERE r.party_id = l_party_id1
806: AND r.relationship_id = oc.party_relationship_id
807: AND r.directional_flag='F'
808: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
826: l_org_contact_id:=NULL;
827: ELSE
828: BEGIN
829: SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
830: FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
831: WHERE r.party_id = l_ot_id
832: AND r.relationship_id = oc.party_relationship_id
833: AND r.directional_flag='F'
834: AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1510: AND contact_point_id = cp_id;
1511:
1512: CURSOR c_cp_org_contact (cp_id NUMBER) IS
1513: SELECT oc.org_contact_id
1514: FROM HZ_CONTACT_POINTS cp, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
1515: WHERE owner_table_name = 'HZ_PARTIES'
1516: AND contact_point_id = cp_id
1517: AND rl.PARTY_ID = cp.owner_table_id
1518: AND oc.party_relationship_id = rl.relationship_id
1521: AND rl.DIRECTIONAL_FLAG = 'F';
1522:
1523: CURSOR c_ps_org_contact (ps_id NUMBER) IS
1524: SELECT oc.org_contact_id
1525: FROM HZ_PARTY_SITES ps, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
1526: WHERE ps.party_site_id = ps_id
1527: AND rl.PARTY_ID = ps.party_id
1528: AND oc.party_relationship_id = rl.relationship_id
1529: AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1580: IF l_party_type = 'ORGANIZATION' OR l_party_type = 'PERSON' THEN
1581: l_party_id := l_ot_id;
1582: ELSIF l_party_type = 'PARTY_RELATIONSHIP' THEN
1583: SELECT relationship_id, object_id INTO l_pr_id, l_party_id
1584: FROM hz_relationships --bug 4500011 replaced hz_party_relationships with hz_relationships
1585: WHERE party_id = l_ot_id
1586: AND subject_table_name = 'HZ_PARTIES'
1587: AND object_table_name = 'HZ_PARTIES'
1588: AND directional_flag = 'F';
1661: IF p_from_fk_id = p_to_fk_id THEN
1662: l_org_contact_id := p_from_id;
1663:
1664: SELECT pr.party_id, pr.object_id INTO l_rel_party_id, l_party_id
1665: FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc --bug 4500011 replaced hz_party_relationships with hz_relationships
1666: WHERE oc.org_contact_id = l_org_contact_id
1667: AND pr.relationship_id = oc.party_relationship_id
1668: AND pr.subject_table_name = 'HZ_PARTIES'
1669: AND pr.object_table_name = 'HZ_PARTIES'