Search Results p_where_clause

Overview

The package body APPS.IGI_CIS2007_IGIPPADR_PKG supports the United Kingdom Construction Industry Scheme (CIS) reporting functionality delivered within Oracle E-Business Suite releases 12.1.1 and 12.2.2. Specifically, it underpins the "IGIPPA" (CIS Payment and Deduction) report, a statutory output that contractors must produce to summarise payments made to subcontractors and the deductions applied against those payments. The package is registered under the APPS schema and is classified as an "OTHER" API, indicating that it is not a public, supported application programming interface but rather an internal implementation unit driven by the Oracle Reports runtime engine.

The package operates as a Reports PL/SQL library attached to the IGIPPA report. Its central responsibility is the dynamic construction of the SQL fragments that the report uses to retrieve payment, deduction, and supplier data. The user-visible parameter p_where_clause referenced in the source excerpt is a Reports bind parameter that this package populates at runtime, allowing the report query to be assembled conditionally based on user selections rather than being hard-coded.

Key Procedures and Functions

• BEFOREREPORT — A standard Oracle Reports formula function invoked before the report query executes. It performs the core logic of the package: inspecting the reporting parameters (total type, period, zero-deduction flag, supplier range) and concatenating the appropriate SELECT, FROM, and WHERE fragments into the report's dynamic query parameters, including p_where_clause.
• GET_PRINT_TYPE — Returns a value that governs the format or classification of output produced by the report, supporting the differing presentation required for the various CIS report variants.
• GET_ORG_NAME — Retrieves the name of the organisation (operating unit or legal entity) for which the CIS return is being produced, used to populate the report header.
• GET_PERIOD_END_DATE — Returns the end date of the reporting period, enabling the report to label the return with the correct statutory period boundaries.

The p_where_clause construction logic branches on P_TOTAL_TYPE, building either a lines-level predicate or a payment-level predicate, and conditionally appends supplier range filters and an ORDER BY clause.

Tables Accessed

The documented table reference for this package is AP_OTHER_PERIODS, accessed via its APPS synonym. This table supplies the non-standard or user-defined accounting period definitions that the CIS reporting logic uses to resolve period boundaries and validity, complementing the standard AP_PERIODS data. The source excerpt additionally shows the dynamic query joining HR_LOCATIONS_V, AP_INVOICES, and the CIS payment table IGI_CIS_MTH_RET_PAY_H, reflecting the operational tables read during report execution.

Usage Notes

This package is not intended for direct invocation by custom code. It is executed implicitly whenever the IGIPPA CIS report is run from the Oracle EBS concurrent manager or the relevant reporting responsibility. Administrators should be aware that the dynamic SQL assembled through p_where_clause uses string concatenation of parameter values, so parameter validation and access control are governed by the report definition itself. Because the package is classified as an internal "OTHER" API, Oracle does not guarantee backward compatibility of its internal signatures across patch levels; customisations should avoid referencing its procedures directly and should instead rely on the supported report interface. The object is not referenced by any other packaged program, confirming its role as a terminal, report-facing implementation unit.