[Home] [Help]
415: --Cursor to check if code assignment is already exist for party.
416: CURSOR get_code_assignments(p_party_id NUMBER) IS
417: SELECT 'X'
418: FROM hz_code_assignments
419: WHERE owner_table_name = 'HZ_PARTIES'
420: AND owner_table_id = p_party_id
421: AND class_category = 'POS_CLASSIFICATION'
422: AND class_code = 'PROCUREMENT_ORGANIZATION'
423: AND status = 'A'
517: --Need to create relationship between Vendor and Party.
518: --Relationship type define as 'POS_VENDOR_PARTY', Subject as Party and
519: --Object as Vendor, since relationship code used is 'PARTY_OF_VENDOR'
520: l_party_rel_rec.subject_id := l_party_id;
521: l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
522: l_party_rel_rec.subject_type := 'ORGANIZATION';
523:
524: l_party_rel_rec.object_id := p_vendor_id;
525: l_party_rel_rec.object_table_name := 'PO_VENDORS';
568: IF (l_tmp IS NULL ) THEN
569: --No code assignment exists, create for party.
570: --These input values are standard for Vendor and Party as define
571: --by PO receiving team.
572: l_code_assignment_rec.owner_table_name := 'HZ_PARTIES';
573: l_code_assignment_rec.owner_table_id := l_party_id;
574: l_code_assignment_rec.class_category := 'POS_CLASSIFICATION';
575: l_code_assignment_rec.class_code := 'PROCUREMENT_ORGANIZATION';
576: l_code_assignment_rec.primary_flag := 'Y';
1031: --is same as when user is created.
1032: CURSOR get_pos_user IS
1033: SELECT 1
1034: FROM hz_relationships h2,
1035: hz_parties hp,
1036: fnd_user fu
1037: WHERE h2.subject_type = 'ORGANIZATION'
1038: and h2.object_type = 'PERSON'
1039: and h2.relationship_type = 'POS_EMPLOYMENT'
1037: WHERE h2.subject_type = 'ORGANIZATION'
1038: and h2.object_type = 'PERSON'
1039: and h2.relationship_type = 'POS_EMPLOYMENT'
1040: and h2.relationship_code = 'EMPLOYER_OF'
1041: and h2.subject_table_name = 'HZ_PARTIES'
1042: and h2.object_table_name = 'HZ_PARTIES'
1043: and h2.status = 'A'
1044: and h2.start_date <= sysdate
1045: and h2.end_date >= sysdate
1038: and h2.object_type = 'PERSON'
1039: and h2.relationship_type = 'POS_EMPLOYMENT'
1040: and h2.relationship_code = 'EMPLOYER_OF'
1041: and h2.subject_table_name = 'HZ_PARTIES'
1042: and h2.object_table_name = 'HZ_PARTIES'
1043: and h2.status = 'A'
1044: and h2.start_date <= sysdate
1045: and h2.end_date >= sysdate
1046: and h2.object_id = fu.person_party_id -- IB-PHASE-2 Vendor merge
1049: and fu.user_id = p_user_id
1050: and h2.subject_id IN
1051: (select owner_table_id
1052: from hz_code_assignments
1053: where owner_table_name='HZ_PARTIES'
1054: and status = 'A'
1055: and class_category ='POS_PARTICIPANT_TYPE'
1056: and class_code='VENDOR');
1057: */
1060: -- R12 Bug 4911516
1061: --
1062: CURSOR get_pos_user IS
1063: SELECT 1
1064: FROM pos_supplier_users_v pos, hz_parties hz
1065: WHERE pos.user_id = p_user_id
1066: AND pos.vendor_party_id = hz.party_id
1067: AND hz.party_name = p_supplier_name;
1068: --
1481:
1482: --Phone Number not is mandatory parameter for creating phone contact,
1483: --call api create only if it is not null.
1484: IF (p_phone IS NOT NULL) THEN
1485: l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
1486: l_contact_points_rec_type.owner_table_id := p_owner_table_id;
1487: l_contact_points_rec_type.primary_flag := 'Y';
1488: l_contact_points_rec_type.status := 'A';
1489: l_contact_points_rec_type.created_by_module := C_CREATED_BY_MODULE;
1606:
1607: --Cursor to get object_version_number for party
1608: CURSOR Get_Object_Version_Number(p_person_party_id NUMBER) IS
1609: select object_version_number
1610: from hz_parties
1611: where party_id = p_person_party_id;
1612:
1613: --Cursor to get object_version_number for party contact.
1614: CURSOR Get_Cont_Point_Version(p_contact_point_id NUMBER) IS
1938: x_num_errors => l_num_errors);
1939:
1940: -- Create Org Contact to related person to party.
1941: l_ocon_rec.party_rel_rec.subject_id := l_person_party_id;
1942: l_ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1943: l_ocon_rec.party_rel_rec.subject_type := 'PERSON';
1944: l_ocon_rec.party_rel_rec.object_id := p_party_id;
1945: l_ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1946: l_ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1941: l_ocon_rec.party_rel_rec.subject_id := l_person_party_id;
1942: l_ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1943: l_ocon_rec.party_rel_rec.subject_type := 'PERSON';
1944: l_ocon_rec.party_rel_rec.object_id := p_party_id;
1945: l_ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1946: l_ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1947: l_ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
1948: l_ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
1949: l_ocon_rec.party_rel_rec.status := 'A';
1980: x_num_errors => l_num_errors);
1981:
1982:
1983: -- Create a EMAIL contact point, this related email to party.
1984: l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
1985: l_contact_points_rec_type.owner_table_id := l_party_id;
1986: l_contact_points_rec_type.primary_flag := 'Y';
1987: l_contact_points_rec_type.status := 'A';
1988: l_contact_points_rec_type.created_by_module := C_CREATED_BY_MODULE;
2124: email_record.owner_table_id,
2125: hrel.relationship_id, -- IB-Phase-2
2126: hrel.end_date end_date -- IB-Phase-2
2127: from hz_party_sites hps,
2128: hz_parties contact_person,
2129: hz_org_contacts supplier_contact,
2130: hz_contact_points phone_record,
2131: hz_contact_points email_record,
2132: hz_relationships hrel
2130: hz_contact_points phone_record,
2131: hz_contact_points email_record,
2132: hz_relationships hrel
2133: where hrel.subject_id = contact_person.party_id
2134: and hrel.subject_table_name = 'HZ_PARTIES'
2135: and hrel.subject_type = 'PERSON'
2136: and hrel.object_id = hps.party_id
2137: and hrel.object_table_name = 'HZ_PARTIES'
2138: and hrel.object_type = 'ORGANIZATION'
2133: where hrel.subject_id = contact_person.party_id
2134: and hrel.subject_table_name = 'HZ_PARTIES'
2135: and hrel.subject_type = 'PERSON'
2136: and hrel.object_id = hps.party_id
2137: and hrel.object_table_name = 'HZ_PARTIES'
2138: and hrel.object_type = 'ORGANIZATION'
2139: and hrel.relationship_code = 'CONTACT_OF'
2140: and hrel.directional_flag = 'F'
2141: and supplier_contact.party_relationship_id =hrel.relationship_id
2139: and hrel.relationship_code = 'CONTACT_OF'
2140: and hrel.directional_flag = 'F'
2141: and supplier_contact.party_relationship_id =hrel.relationship_id
2142: and supplier_contact.party_site_id = hps.party_site_id
2143: and phone_record.owner_table_name(+) = 'HZ_PARTIES'
2144: and phone_record.owner_table_id(+) = hrel.party_id
2145: and phone_record.contact_point_type(+) = 'PHONE'
2146: and email_record.owner_table_name = 'HZ_PARTIES'
2147: and email_record.owner_table_id = hrel.party_id
2142: and supplier_contact.party_site_id = hps.party_site_id
2143: and phone_record.owner_table_name(+) = 'HZ_PARTIES'
2144: and phone_record.owner_table_id(+) = hrel.party_id
2145: and phone_record.contact_point_type(+) = 'PHONE'
2146: and email_record.owner_table_name = 'HZ_PARTIES'
2147: and email_record.owner_table_id = hrel.party_id
2148: and email_record.contact_point_type = 'EMAIL'
2149: and hps.party_site_id =p_party_site_id
2150: and hps.party_id = p_party_id;