Search Results extract_data

Overview

The package body APPS.IGI_CIS_CI36_DATA_PKG is a server-side PL/SQL component within the Oracle E-Business Suite (EBS) Financials product family, specifically associated with the IGI (Oracle Financials for the United Kingdom / CIS) localization module. Its primary purpose is to support the Construction Industry Scheme (CIS) statutory reporting framework used in the United Kingdom, where contractors must report payments made to subcontractors to HM Revenue & Customs. The internal identifier "CI36" corresponds to the Inland Revenue CIS form referenced as CIS36, which summarizes payments and tax deductions made to subcontractors under the CIS scheme.

The package encapsulates the data extraction logic required to assemble the reporting dataset — calculating tax treatment rates per vendor and vendor site, joining invoice, payment, and withholding tax information, and staging the results into an extraction table for downstream report generation. It also contains a standardized debug logging utility layered on top of FND_LOG, reflecting the diagnostics convention used across IGI packages.

Key Procedures and Functions

The ETRM documentation for this object identifies a single public entry point:

• EXTRACT_DATA — The main data extraction routine. It accepts the report identity, the operating unit, a low and high date boundary, and a vendor identifier. Its purpose is to select CIS-relevant payment and withholding data for the supplied reporting window and vendor scope, resolve the applicable CIS tax rate information per vendor and vendor site as of the relevant check date, and populate the CIS extraction table so that the report generator can produce the CIS36 output. The routine is documented as an API-style procedure that returns an error buffer and a numeric return code, following the concurrent program execution convention. Parameters are not enumerated here beyond their documented functional roles.

Supporting internal logic includes the cursor c_cis_payments, which reads the pre-staged payment data from IGI_CIS_CI36_PAYMENTS, and the parameterized cursor c_cis_awt_tax_rates, which resolves certificate type, certificate number, comments, and NI number for a given vendor, vendor site, and check date from IGI_CIS_AWT_TAX_RATES. The package body also defines a private DEBUG procedure for conditional logging controlled by the FND_LOG runtime level.

Tables Accessed

The documented table references expose the data lineage of the extraction process:

• IGI_CIS_CI36_PAYMENTS — The staging source for CIS payment and withholding lines, including invoice, check, vendor, certificate, and NI number attributes.
• IGI_CIS_CI36_EXTRACT — The output table into which the extraction process writes the assembled CIS36 reporting dataset.
• AP_INVOICES and AP_INVOICE_DISTRIBUTIONS — Oracle Payables invoice and distribution records that provide the underlying supplier liability and payment basis.
• AP_CHECKS — Payment documents supplying check number, check date, and payment amounts used for CIS period reporting.
• AP_AWT_GROUPS — Withholding tax group definitions required to identify and interpret the CIS withholding treatment applied to qualifying payments.

Access is performed through APPS synonyms, consistent with standard EBS package compilation against the APPS schema.

Usage Notes

Given its signature returning Errbuf and Retcode, EXTRACT_DATA is characteristically invoked from an Oracle EBS concurrent program, typically via a PL/SQL concurrent program stub or a host of the CIS36 reporting concurrent executable. It may also be called from a report grouping that first populates the extraction table and then runs the BI Publisher or Oracle Reports output. The routine is not documented as being referenced by other packages, meaning it is generally a top-level entry point rather than a shared utility. Environments upgrading to 12.2.2 should note the historical fix recorded in the source header regarding NVL handling of icatr1.end_date, consistent with the package having been maintained across 11i and R12 code lines. Database privileges, concurrent manager configuration, and the operating unit context must be established by the caller before invocation.