Results for “icl_id”

2 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGI_CIS_CERT_NI_NUMBERS_ALL is a transactional table in the IGI (Public Sector Financials International) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It stores National Insurance (NI) numbers associated with Construction Industry Scheme (CIS) withholding tax certificates. CIS is a United Kingdom statutory scheme requiring contractors to withhold tax from subcontractor payments unless a valid exemption certificate is presented; the NI number is the key identifier used to validate the subcontractor against HM Revenue & Customs records. This table therefore acts as the persistence layer for NI number data captured during certificate registration and verification.

The ETRM data vault classification is heuristic and satellite-leaning. In data vault terms, this object is best modeled as a satellite attached to a CIS certificate or contractor hub, since it holds descriptive attributes (the NI number and its audit columns) that change over time and depend on a parent business entity rather than acting as an independent hub. The self-referencing foreign key on ICL_ID, combined with its role in ETRM documentation, suggests the table also serves a structural or grouping function within the CIS certificate hierarchy.

Key Information Stored

The documented physical schema at 12.2.2 contains nine columns. The most significant are:

  • NI_NUMBER — the business attribute of primary interest: the National Insurance number recorded for the CIS certificate holder.
  • TAX_RATE_ID — associates the NI number entry with a withholding tax rate definition, linking the record to CIS tax treatment.
  • ORG_ID — the operating unit identifier, enforcing multi-org (MOAC) data segregation.
  • ROW_ID — the globally unique row identifier used across EBS tables for replication and auditing.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns tracking insert and update provenance.

ETRM documentation identifies the primary key as ICL_PK (ID) — the surrogate key used for row-level identification. The business-key candidate is the combination of NI_NUMBER with its parent certificate reference and ORG_ID; no separate unique index is documented in the supplied metadata, so the NI_NUMBER value alone should not be treated as a natural primary key. Note that the foreign key metadata documents ICL_ID referencing IGI_CIS_CERT_NI_NUMBERS_ALL itself and ROW_ID referencing CS_SYSTEMS_ALL_B_TEMP, indicating the ALL-suffixed table participates in both the CIS certificate model and the EBS multi-org/systems infrastructure.

Common Use Cases and Queries

Typical usage centers on CIS certificate reporting, subcontractor verification, and withholding tax audits. A representative query retrieving NI numbers for an operating unit is:

  • SELECT ni_number, tax_rate_id, org_id, last_update_date FROM igi.igi_cis_cert_ni_numbers_all WHERE org_id = :p_org_id;
  • Joining to CIS certificate headers on the certificate key to list all NI numbers per certificate.
  • Auditing changes to NI numbers by filtering on last_update_date and last_updated_by.
  • Reconciling NI numbers against withholding tax rates via TAX_RATE_ID.

Because the table is _ALL suffixed, reports must always filter by ORG_ID to respect operating unit security. Reporting tools such as Oracle BI Publisher or Discoverer typically source this table through the IGI CIS certificate views rather than querying it directly.

Related Objects

  • IGI_CIS_CERT_NI_NUMBERS_ALL (self-reference on ICL_ID) — the parent/child structural relationship within the CIS certificate NI number model.
  • CS_SYSTEMS_ALL_B_TEMP — referenced via ROW_ID for multi-org replication infrastructure.
  • IGI_CIS_CERTIFICATES and related CIS certificate header tables — the parent certificate entities that the NI numbers describe.
  • IGI withholding tax rate tables — joined through TAX_RATE_ID to resolve CIS deduction rates.
  • FND_ORG / HR_OPERATING_UNITS — resolves ORG_ID to the owning operating unit.
  • FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to application users for audit reporting.

These relationships confirm the table's position as a dependent satellite within the IGI CIS withholding tax certificate model rather than an independent transactional hub.