[Home] [Help]
205: extension in VARCHAR2 ) IS
206: SELECT 'Y' FROM hz_contact_points
207: WHERE contact_point_type = 'PHONE'
208: AND phone_line_type = 'GEN'
209: AND owner_table_name = 'HZ_PARTIES'
210: AND owner_table_id = party_id_in
211: AND phone_number = phone_number
212: AND NVL(phone_country_code,'x') = NVL(country_code,'x')
213: AND NVL(phone_area_code,'x') = NVL(area_code,'x')
217: CURSOR email_exists (email_address_in VARCHAR2) IS
218: SELECT owner_table_id
219: FROM hz_contact_points
220: WHERE contact_point_type = 'EMAIL'
221: AND owner_table_name = 'HZ_PARTIES'
222: AND upper(email_address) = upper(email_address_in);
223:
224: cursor c_person_exists(id_in IN NUMBER) is
225: select 'Y' from hz_parties
221: AND owner_table_name = 'HZ_PARTIES'
222: AND upper(email_address) = upper(email_address_in);
223:
224: cursor c_person_exists(id_in IN NUMBER) is
225: select 'Y' from hz_parties
226: where customer_key = l_party_key
227: and party_type = 'PERSON'
228: and party_id = id_in;
229:
437: CURSOR phone_exists (x_hz_party_id number) IS
438: SELECT 'Y' FROM hz_contact_points
439: WHERE contact_point_type = 'PHONE'
440: AND phone_line_type = 'GEN'
441: AND owner_table_name = 'HZ_PARTIES'
442: AND owner_table_id = x_hz_party_id
443: AND phone_number = x_phone_number
444: AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
445: AND NVL(phone_area_code,'x') = NVL(x_phone_area_code,'x')
448:
449: CURSOR email_exists (x_hz_party_id number) IS
450: SELECT 'Y' FROM hz_contact_points
451: WHERE contact_point_type = 'EMAIL'
452: AND owner_table_name = 'HZ_PARTIES'
453: AND owner_table_id = x_hz_party_id
454: AND email_address = x_email_address;
455: /*
456: party_rec hz_party_pub.party_rec_type;
632: x_party_number := null;
633: if x_generate_party_number = 'N' then
634: select hz_party_number_s.nextval into x_party_number from dual;
635: end if;
636: select hz_parties_s.nextval into x_org_party_id from dual;
637: org_rec.party_rec.party_number := x_party_number;
638: org_rec.party_rec.party_id := x_org_party_id;
639: org_rec.ceo_name := p_rec.ceo_name;
640: org_rec.curr_fy_potential_revenue := p_rec.current_fy_potential_rev;
739: if person_rec.person_first_name is not null then
740: if x_generate_party_number = 'N' then
741: select hz_party_number_s.nextval into x_party_number from dual;
742: end if;
743: select hz_parties_s.nextval into x_per_party_id from dual;
744: person_rec.party_rec.party_number := x_party_number;
745: person_rec.party_rec.party_id := x_per_party_id;
746: x_return_status := null;
747: x_msg_count := 0;
1169: x_msg_data := null;
1170: cpoint_rec.contact_point_id := x_contact_point_id;
1171: cpoint_rec.contact_point_type := 'PHONE';
1172: cpoint_rec.status := 'A';
1173: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1174: cpoint_rec.owner_table_id := x_party_rel_party_id;
1175: -- cpoint_rec.primary_flag := 'Y';
1176: cpoint_rec.orig_system_reference := x_contact_point_id;
1177: cpoint_rec.created_by_module := 'AMS_EVENT';
1237: x_msg_data := null;
1238: cpoint_rec.contact_point_id := x_contact_point_id;
1239: cpoint_rec.contact_point_type := 'EMAIL';
1240: cpoint_rec.status := 'A';
1241: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1242: cpoint_rec.owner_table_id := x_party_rel_party_id;
1243: -- cpoint_rec.primary_flag := 'Y';
1244: cpoint_rec.orig_system_reference := x_contact_point_id;
1245: cpoint_rec.created_by_module := 'AMS_EVENT';
1397: if x_per_party_id is null then
1398: if x_generate_party_number = 'N' then
1399: select hz_party_number_s.nextval into x_party_number from dual;
1400: end if;
1401: select hz_parties_s.nextval into x_per_party_id from dual;
1402:
1403: person_rec.party_rec.party_number := x_party_number;
1404: person_rec.party_rec.party_id := x_per_party_id;
1405: x_return_status := null;
1664: x_msg_data := null;
1665: cpoint_rec.contact_point_id := x_contact_point_id;
1666: cpoint_rec.contact_point_type := 'PHONE';
1667: cpoint_rec.status := 'A';
1668: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1669: cpoint_rec.owner_table_id := x_per_party_id;
1670: -- cpoint_rec.primary_flag := 'Y';
1671: cpoint_rec.orig_system_reference := x_contact_point_id;
1672: cpoint_rec.created_by_module := 'AMS_EVENT';
1735: x_msg_data := null;
1736: cpoint_rec.contact_point_id := x_contact_point_id;
1737: cpoint_rec.contact_point_type := 'EMAIL';
1738: cpoint_rec.status := 'A';
1739: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1740: cpoint_rec.owner_table_id := x_per_party_id;
1741: -- cpoint_rec.primary_flag := 'Y';
1742: cpoint_rec.orig_system_reference := x_contact_point_id;
1743: cpoint_rec.created_by_module := 'AMS_EVENT';
1840:
1841: cursor c_email_address is
1842: select max(p.party_id)
1843: from hz_contact_points cp,
1844: hz_parties p
1845: where p.customer_key = l_party_key
1846: and p.party_type = 'PERSON'
1847: and cp.owner_table_id = p.party_id
1848: and cp.owner_table_name = 'HZ_PARTIES'
1844: hz_parties p
1845: where p.customer_key = l_party_key
1846: and p.party_type = 'PERSON'
1847: and cp.owner_table_id = p.party_id
1848: and cp.owner_table_name = 'HZ_PARTIES'
1849: and cp.email_address = p_email_address
1850: and cp.primary_flag = 'Y';
1851:
1852: cursor c_ph_number is
1851:
1852: cursor c_ph_number is
1853: select max(p.party_id)
1854: from hz_contact_points cp,
1855: hz_parties p
1856: where p.customer_key = l_party_key
1857: and p.party_type = 'PERSON'
1858: and cp.owner_table_id = p.party_id
1859: and cp.owner_table_name = 'HZ_PARTIES'
1855: hz_parties p
1856: where p.customer_key = l_party_key
1857: and p.party_type = 'PERSON'
1858: and cp.owner_table_id = p.party_id
1859: and cp.owner_table_name = 'HZ_PARTIES'
1860: and cp.primary_flag = 'Y'
1861: and cp.phone_number = p_ph_number
1862: and nvl(cp.phone_country_code, nvl(p_ph_country_code, 'x')) = nvl(p_ph_country_code, 'x')
1863: and nvl(cp.phone_area_code, nvl(p_ph_area_code, 'x')) = nvl(p_ph_area_code, 'x');
1865: cursor c_address_country is
1866: select max(psite.party_id)
1867: from hz_party_sites psite,
1868: hz_locations loc,
1869: hz_parties party
1870: where psite.location_id = loc.location_id
1871: and loc.address1 = p_address1
1872: and loc.country = p_country
1873: and party.customer_key = l_party_key
1874: and psite.party_id = party.party_id;
1875:
1876: cursor c_person_exists is
1877: select 'Y'
1878: from hz_parties
1879: where customer_key = l_party_key
1880: and party_type = 'PERSON';
1881:
1882: begin
1980: L_API_NAME CONSTANT VARCHAR2(30) := ' party_echeck';
1981:
1982: cursor c_address_country is
1983: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
1984: hz_parties party
1985: where psite.location_id = loc.location_id
1986: and loc.address1 = p_address1
1987: and loc.country = p_country
1988: and party.customer_key = l_party_key
1989: and psite.party_id = party.party_id;
1990:
1991: cursor c_country is
1992: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
1993: hz_parties party
1994: where psite.location_id = loc.location_id
1995: and loc.country = p_country
1996: and party.customer_key = l_party_key
1997: and psite.party_id = party.party_id;
1996: and party.customer_key = l_party_key
1997: and psite.party_id = party.party_id;
1998:
1999: cursor c_customer_exists is
2000: select 'Y' from hz_parties
2001: where customer_key = l_party_key
2002: and party_type = 'ORGANIZATION';
2003:
2004: cursor c_person_exists is
2001: where customer_key = l_party_key
2002: and party_type = 'ORGANIZATION';
2003:
2004: cursor c_person_exists is
2005: select 'Y' from hz_parties
2006: where customer_key = l_party_key
2007: and party_type = 'PERSON';
2008: begin
2009:
2163: l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
2164:
2165:
2166: cursor c_customer_exists is
2167: select 'Y' from hz_parties
2168: where customer_key = l_party_key
2169: and party_type = 'PERSON';
2170:
2171: cursor c_cont_email is
2169: and party_type = 'PERSON';
2170:
2171: cursor c_cont_email is
2172: select max(per.party_id) from
2173: hz_parties org,
2174: hz_parties per,
2175: hz_relationships rel,
2176: hz_contact_points cpoint
2177: where org.party_id = p_org_party_id
2170:
2171: cursor c_cont_email is
2172: select max(per.party_id) from
2173: hz_parties org,
2174: hz_parties per,
2175: hz_relationships rel,
2176: hz_contact_points cpoint
2177: where org.party_id = p_org_party_id
2178: and org.party_type = 'ORGANIZATION'
2175: hz_relationships rel,
2176: hz_contact_points cpoint
2177: where org.party_id = p_org_party_id
2178: and org.party_type = 'ORGANIZATION'
2179: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2180: and rel.SUBJECT_TYPE = 'PERSON'
2181: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2182: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2183: and rel.OBJECT_ID = org.party_id
2177: where org.party_id = p_org_party_id
2178: and org.party_type = 'ORGANIZATION'
2179: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2180: and rel.SUBJECT_TYPE = 'PERSON'
2181: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2182: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2183: and rel.OBJECT_ID = org.party_id
2184: and rel.SUBJECT_ID = per.PARTY_ID
2185: and per.customer_key = l_party_key
2183: and rel.OBJECT_ID = org.party_id
2184: and rel.SUBJECT_ID = per.PARTY_ID
2185: and per.customer_key = l_party_key
2186: and cpoint.owner_table_id = rel.party_id
2187: and cpoint.owner_table_name = 'HZ_PARTIES'
2188: and cpoint.contact_point_type = 'EMAIL'
2189: and cpoint.email_address = p_email_address
2190: and cpoint.status = 'A';
2191:
2191:
2192:
2193: cursor c_cont_email_phone is
2194: select max(per.party_id) from
2195: hz_parties org,
2196: hz_parties per,
2197: hz_relationships rel,
2198: hz_contact_points cpoint,
2199: hz_contact_points cpoint1
2192:
2193: cursor c_cont_email_phone is
2194: select max(per.party_id) from
2195: hz_parties org,
2196: hz_parties per,
2197: hz_relationships rel,
2198: hz_contact_points cpoint,
2199: hz_contact_points cpoint1
2200: where org.party_id = p_org_party_id
2198: hz_contact_points cpoint,
2199: hz_contact_points cpoint1
2200: where org.party_id = p_org_party_id
2201: and org.party_type = 'ORGANIZATION'
2202: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2203: and rel.SUBJECT_TYPE = 'PERSON'
2204: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2205: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2206: and rel.OBJECT_ID = org.party_id
2200: where org.party_id = p_org_party_id
2201: and org.party_type = 'ORGANIZATION'
2202: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2203: and rel.SUBJECT_TYPE = 'PERSON'
2204: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2205: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2206: and rel.OBJECT_ID = org.party_id
2207: and rel.SUBJECT_ID = per.PARTY_ID
2208: and per.customer_key = l_party_key
2206: and rel.OBJECT_ID = org.party_id
2207: and rel.SUBJECT_ID = per.PARTY_ID
2208: and per.customer_key = l_party_key
2209: and cpoint.owner_table_id = rel.party_id
2210: and cpoint.owner_table_name = 'HZ_PARTIES'
2211: and cpoint.contact_point_type = 'EMAIL'
2212: and cpoint.email_address = p_email_address
2213: and cpoint.status = 'A'
2214: and cpoint1.owner_table_id = rel.party_id
2211: and cpoint.contact_point_type = 'EMAIL'
2212: and cpoint.email_address = p_email_address
2213: and cpoint.status = 'A'
2214: and cpoint1.owner_table_id = rel.party_id
2215: and cpoint1.owner_table_name = 'HZ_PARTIES'
2216: and cpoint1.contact_point_type = 'PHONE'
2217: and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
2218: p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
2219: and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
2221:
2222:
2223: cursor c_cont_phone is
2224: select max(per.party_id) from
2225: hz_parties org,
2226: hz_parties per,
2227: hz_relationships rel,
2228: hz_contact_points cpoint
2229: where org.party_id = p_org_party_id
2222:
2223: cursor c_cont_phone is
2224: select max(per.party_id) from
2225: hz_parties org,
2226: hz_parties per,
2227: hz_relationships rel,
2228: hz_contact_points cpoint
2229: where org.party_id = p_org_party_id
2230: and org.party_type = 'ORGANIZATION'
2227: hz_relationships rel,
2228: hz_contact_points cpoint
2229: where org.party_id = p_org_party_id
2230: and org.party_type = 'ORGANIZATION'
2231: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2232: and rel.SUBJECT_TYPE = 'PERSON'
2233: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2234: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2235: and rel.OBJECT_ID = org.party_id
2229: where org.party_id = p_org_party_id
2230: and org.party_type = 'ORGANIZATION'
2231: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2232: and rel.SUBJECT_TYPE = 'PERSON'
2233: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2234: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
2235: and rel.OBJECT_ID = org.party_id
2236: and rel.SUBJECT_ID = per.PARTY_ID
2237: and per.customer_key = l_party_key
2235: and rel.OBJECT_ID = org.party_id
2236: and rel.SUBJECT_ID = per.PARTY_ID
2237: and per.customer_key = l_party_key
2238: and cpoint.owner_table_id = rel.party_id
2239: and cpoint.owner_table_name = 'HZ_PARTIES'
2240: and cpoint.contact_point_type = 'PHONE'
2241: and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
2242: p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
2243: and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)