Search Results jl_cl_ap_awt_cert_pk

Overview

The JL_CL_AP_AWT_CERT table is a core data object within the Oracle E-Business Suite (EBS) Latin America Localizations module, specifically designed for the Chilean market. It serves as the primary repository for storing detailed withholding tax certificate information, a critical legal requirement for business transactions in Chile. This table supports the generation, tracking, and reporting of official withholding certificates, which document tax deductions made on payments to suppliers. Its role is integral to ensuring compliance with Chilean tax regulations within the Accounts Payable (AP) process, enabling organizations to maintain accurate fiscal records and fulfill statutory reporting obligations.

Key Information Stored

Based on the provided metadata, the table's primary key is the CERTIFICATE_ID column, which uniquely identifies each withholding certificate record. While a full column list is not provided in the excerpt, standard data stored in such a table typically includes certificate numbers, issuance dates, fiscal periods, supplier details, total payment amounts, calculated withholding tax amounts, tax authority identifiers, and certificate status (e.g., Generated, Printed, Annulled). It also links to underlying invoice payment transactions, ensuring a complete audit trail from the original invoice to the formal tax certificate.

Common Use Cases and Queries

The primary use case is the generation and management of official withholding certificates for suppliers. This is often triggered during the payment process or as a periodic batch operation. Common reporting needs include listing all certificates issued within a date range for a specific supplier or tax ID, reconciling total withholdings per fiscal period, and identifying certificates pending issuance. A typical query pattern would join this table to supplier (POZ_SUPPLIERS) and invoice (AP_INVOICES_ALL) tables to produce a detailed report.

SELECT cert.certificate_id,
       cert.certificate_number,
       cert.issue_date,
       sup.vendor_name,
       inv.invoice_num,
       cert.withholding_amount
FROM   jl_cl_ap_awt_cert cert,
       ap_suppliers sup,
       ap_invoices_all inv
WHERE  cert.vendor_id = sup.vendor_id
AND    cert.invoice_id = inv.invoice_id
AND    cert.issue_date BETWEEN :p_start_date AND :p_end_date;

Related Objects

The table's relationships are defined by its primary key and foreign key constraints. The documented primary key constraint JL_CL_AP_AWT_CERT_PK on CERTIFICATE_ID ensures uniqueness. This key is likely referenced by other localization objects, such as detail tables for certificate lines or print log tables. While specific foreign keys are not listed, this table typically has foreign key relationships to core EBS tables, including AP_INVOICES_ALL (via INVOICE_ID) and POZ_SUPPLIERS (via VENDOR_ID), to anchor the certificate to the original transaction and supplier. It is also commonly referenced by Chilean localization reports and interfaces for tax authority submissions.