Results for “certificate_number”

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

Overview

The JL_CL_AP_AWT_CERT table resides in the JL schema and belongs to the Latin America Localizations product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores Chilean withholding certificate information generated for accounts payable transactions subject to Chilean tax withholding rules. In Chilean localization, suppliers of certain services are subject to a withholding regime (retención) that obligates the paying legal entity to issue an official withholding certificate documenting the gross service amount, the participation or retention amount, and the tax withheld. This table acts as the persistent registry of those certificates, linking each certificate to the legal entity, operating unit, supplier, and price index context under which the withholding was calculated.

From a modeling perspective, the metadata characterizes this object heuristically as a standalone structure. In Data Vault terms, it behaves primarily as a satellite carrying descriptive and measurable withholding attributes keyed by a surrogate CERTIFICATE_ID, with a single documented outbound relationship to FV_LEGAL_ENTITIES functioning as a reference link. No dependent child tables are documented, reinforcing its standalone classification.

Key Information Stored

The table contains 20 documented columns. The following are the most significant:

• CERTIFICATE_ID — Surrogate primary key (JL_CL_AP_AWT_CERT_PK), also exposed through the unique index JL_CL_AP_AWT_CERT_U1, making it the sole documented business-key candidate.
• CERTIFICATE_NUMBER and CERTIFICATE_YEAR — The human-readable certificate identifier and the fiscal year to which the withholding applies.
• LEGAL_ENTITY_ID — Foreign key to FV_LEGAL_ENTITIES, identifying the issuing legal entity.
• SET_OF_BOOKS_ID and ORG_ID — The ledger and operating unit context, standard Multi-Org Access Control (MOAC) columns.
• VENDOR_ID — The supplier to whom the certificate is issued.
• PRICE_INDEX_ID — Reference to the Chilean price index (UF, IPC, or similar) used in indexed withholding calculations.
• SERVICE_AMOUNT, PARTICIPATION_AMOUNT, and TAX_AMOUNT — The monetary basis: gross service value, retention/participation basis, and tax withheld.
• PARTICIPATION_FLAG — Indicates whether the certificate exercises the participation (retención) option.
• TAXPAYER_ID_NUMBER — The Chilean RUT (Rol Único Tributario) of the taxpayer.
• STATUS, REPRINT_DATE — Certificate lifecycle state and reprint tracking.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.

Common Use Cases and Queries

Typical reporting includes periodic withholding certificate listings, supplier tax summaries, and legal-entity-level retention reconciliations. A frequent pattern joins the certificate to its legal entity and supplier context:

• Listing certificates by year and status for a given legal entity: SELECT CERTIFICATE_NUMBER, TAX_AMOUNT FROM JL.JL_CL_AP_AWT_CERT WHERE CERTIFICATE_YEAR = :year AND LEGAL_ENTITY_ID = :le_id AND STATUS = 'ISSUED'.
• Aggregating withheld tax by supplier for annual Chilean tax reporting: SELECT VENDOR_ID, SUM(TAX_AMOUNT) FROM JL.JL_CL_AP_AWT_CERT GROUP BY VENDOR_ID.
• Reconciling retained amounts against the service and participation bases when PARTICIPATION_FLAG = 'Y'.
• Identifying certificates requiring reprint by inspecting REPRINT_DATE.

Because ORG_ID is present, queries should observe MOAC filtering, and joins to SET_OF_BOOKS_ID should align with the ledger of the operating unit.

Related Objects

The following objects are most significant in relation to this table:

• FV_LEGAL_ENTITIES — Referenced via LEGAL_ENTITY_ID; the only documented foreign key.
• AP_SUPPLIERS / PO_VENDORS — Supplier master resolved through VENDOR_ID.
• GL_SETS_OF_BOOKS — Ledger context via SET_OF_BOOKS_ID.
• HR_OPERATING_UNITS / GL_ACCESS_SET — Operating unit context via ORG_ID.
• JL_CL_AP_AWT_CERT_PK / JL_CL_AP_AWT_CERT_U1 — Primary key constraint and unique index enforcing CERTIFICATE_ID.
• JL_CL_AP_AWT_CALC / associated JL withholding calculation tables — Upstream localization logic that populates certificate records.