Search Results nih_subdivision_code

Overview

IGW.IGW_ORG_DETAILS is a transactional data table within the Oracle E-Business Suite Grants Management (IGW) schema. It stores supplementary organizational and applicant-specific attributes that are not held on the core HR organization definition. The table captures the details required for sponsored research administration, including signing and administrative officials, entity and taxpayer identifiers, DUNS numbers, and various assurance and institutional profile codes associated with grant applicants and applicant units.

In Oracle EBS 12.1.1 and 12.2.2, the table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its design follows the standard EBS pattern of a foreign-key-to-surrogate-key relationship, where ORGANIZATION_ID references HR_ALL_ORGANIZATION_UNITS. Based on the mined foreign key structure, the heuristic Data Vault classification for this object is satellite-leaning: it functions as a dependent attribute table describing a single business entity (the organization) rather than acting as a hub or link. This classification should be treated as a modeling suggestion only.

Key Information Stored

The table contains 42 documented columns. The most business-significant attributes are listed below.

• ORGANIZATION_ID — Number(15). The primary key of the table, established by the IGW_ORG_DETAILS_PK constraint. It is also the column defined on the unique index IGW_ORG_DETAILS_U1, making it both the surrogate primary key and the business-key candidate. It is a foreign key to HR_ALL_ORGANIZATION_UNITS.
• SIGNING_OFFICIAL_ID — Number(15). Identifier for the official authorized to sign on behalf of the applicant organization.
• ADMIN_OFFICIAL_ID — Number(15). Identifier for the administrative official responsible for the applicant organization's sponsored programs.
• EIN — Varchar2(20). Employer Identification Number.
• TIN — Varchar2(20). Taxpayer Identification Number.
• DUNS_NUMBER and DUNS4_NUMBER — Varchar2(20). Data Universal Numbering System identifiers, with the four-digit suffix variant.
• DODAAC_NUMBER and CAGE_NUMBER — Varchar2(20). Department of Defense Activity Address Code and Commercial and Government Entity identifiers.
• ORG_IPF_CODE — Varchar2(20). The organization's Institutional Profile Code.
• NSF_INSTITUTIONAL_CODE and NIH_SUBDIVISION_CODE — Varchar2(20). Sponsor-specific institutional identifiers.
• AGREEMENT_DATE, RATE_ESTABLISHED_DATE, NEGOTIATING_AGREEMENT_WITH, and RATE_ESTABLISHED_WITH — Dates and regional office references governing negotiated rate agreements.
• CONGRESS_DISTRICT — Varchar2(50). Congressional district of the organization.
• HUMAN_EXEMPTION_NUMBER, HUMAN_ASSURANCE_NUMBER, and ANIMAL_ASSURANCE_NUMBER — Varchar2(20). Compliance assurance identifiers for human and animal research subjects.

Standard EBS audit columns also apply: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and fifteen ATTRIBUTE and ATTRIBUTE_CATEGORY descriptive flexfield columns.

Common Use Cases and Queries

Typical scenarios include reporting the signing and administrative officials for a grantee organization, validating DUNS or EIN data during proposal submission, and coordinating negotiated rate agreements by regional office.

A basic lookup joining the organization to its details:

• SELECT o.organization_id, o.name, d.signing_official_id, d.ein, d.duns_number, d.cage_number FROM igw.igw_org_details d, hr_all_organization_units o WHERE d.organization_id = o.organization_id;
• Reporting organizations whose rate agreement date falls within a period: SELECT organization_id, rate_established_date, rate_established_with FROM igw.igw_org_details WHERE rate_established_date BETWEEN :p_start AND :p_end;
• Validating DUNS coverage: SELECT organization_id FROM igw.igw_org_details WHERE duns_number IS NULL OR duns4_number IS NULL;

Because IGW_ORG_DETAILS_U1 is a unique index on ORGANIZATION_ID, queries filtering on that column can use an index unique scan, which supports efficient point lookups during application processing.

Related Objects

The most significant related objects are the parent organization definition and associated grant applicant and official records. The documented foreign key constraint links this table to HR_ALL_ORGANIZATION_UNITS on ORGANIZATION_ID.

• HR.HR_ALL_ORGANIZATION_UNITS — Parent object supplying the organization definition; joined on ORGANIZATION_ID.
• IGW.IGW_ORG_DETAILS_U1 — Unique index on ORGANIZATION_ID in APPS_TS_TX_IDX; identifies the business key candidate.
• IGW.IGW_ORG_DETAILS_PK — Primary key constraint on ORGANIZATION_ID.
• PER_PERIODS_OF_SERVICE / PER_ALL_PEOPLE_F — Common source for resolving SIGNING_OFFICIAL_ID and ADMIN_OFFICIAL_ID to person records.
• IGW grant applicant and proposal tables — Other IGW schema objects that reference the organization through ORGANIZATION_ID.

Overall, IGW_ORG_DETAILS serves as the satellite attribute store for organization-level applicant data, tightly coupled to the HR organization hub on which its primary key depends.