Search Results get_org_contacts

Overview

APPS.JTF_TERR_LOOKUP_PUB is a public PL/SQL API belonging to the Oracle E-Business Suite Territory Management (Joint Task Force / CRM) schema. Its stated purpose, per the package header comments, is to provide a public interface for retrieving winning territories or the resources assigned to those territories. The package is classified as PUB in the ETRM repository, meaning it is an officially supported extension point intended for direct invocation by customers and integrators, unlike the private or internal packages that surround it. The package is owned by APPS and relies on the standard APPS synonym layer to reach the underlying TCA (Trading Community Architecture) tables.

The package originated in November 2000 and was subsequently enhanced through 2001. Notable milestones recorded in the header history include the introduction of a generic table-of-records architecture (September 2001), the addition of p_source_id and p_trans_id parameters to GET_WINNERS (October 2001), the extension of get_org_contacts with additional parameters, and the broadening of org_name_rec_type to carry area_code, employees_total, category_code, and sic_code. Because it is published, modifications have historically preserved backward compatibility for existing callers.

Key Procedures and Functions

ETRM documents two callable members for this package:

• GET_WINNERS — Resolves territory assignment requests and returns the winning territory or territories for a given input. It accepts a generic assignment request record (based on trans_rec_type), and supports source and transaction identifiers through p_source_id and p_trans_id, which were added to support transactional lookups. This is the primary entry point used when a caller needs to know which territory "wins" for a lead, opportunity, or similar CRM entity.
• GET_ORG_CONTACTS — Returns contact information associated with an organization. This is the procedure referenced by the user's search term "get_org_contacts". Its output record type (org_name_rec_type) was extended to include organization-level attributes such as area code, total employee count, category code, and SIC code, in addition to contact details. This makes it useful for populating organization and contact poplists or display blocks in CRM forms.

Both procedures operate through table-of-records parameters rather than single scalar values, reflecting the generic bulk architecture adopted in 2001. Consult the package specification for exact parameter signatures.

Tables Accessed

The package references the following objects through APPS synonyms:

• HZ_PARTIES — the master party table; supplies organization names and party identifiers.
• HZ_PARTY_SITES — links parties to sites, used to correlate contacts and organizations.
• HZ_LOCATIONS — address and location details for party sites.
• HZ_CONTACT_POINTS — contact mechanisms (phone, email, fax) associated with parties, central to GET_ORG_CONTACTS output.
• DUAL — utility selection.
• PLITBLM — the PL/SQL table-to-bulk-message utility used to convert table-of-records parameters into bulk operations.

Usage Notes

JTF_TERR_LOOKUP_PUB is invoked from CRM forms, concurrent programs, and custom extensions that need territory resolution or organization contact retrieval without reimplementing the territory engine. It is referenced by two other packages in the ETRM repository, indicating it acts as a shared service layer. Typical invocations occur during lead or opportunity processing, where winning territory results drive assignment and routing, and in organization display logic where contact and firmographic data must be assembled. Because it is a PUB API, custom code should call it directly rather than querying the underlying HZ tables, ensuring that territory rules, security, and future schema changes remain encapsulated.