Search Results clets_id

Overview

OKL_OR_SUBSIDY_REFUNDS is an APPS-owned reporting view within the Oracle Lease and Finance Management (OKL) module, available in Oracle E-Business Suite 12.1.1 and 12.2.2. It consolidates subsidy refund payment information by joining contract line, party role, and payment detail records against vendor and lookup reference data. The view exposes the payment profile attached to a subsidy-bearing contract line so that refunds owed to or from a vendor can be reported without navigating the normalized OLTP tables directly.

Because it resolves foreign keys into descriptive names, the view is suited to operational reporting, reconciliation, and outbound integration. Payment method and pay group codes are translated through AP and PO lookup views, the vendor name is drawn from PO_VENDORS, the pay site is resolved through PO_VENDOR_SITES_ALL, and the payment term description is retrieved from AP_TERMS_TL using the session language established by FND_GLOBAL. This makes the view particularly useful when users search on a descriptive attribute such as payment_term_name rather than the underlying surrogate identifier.

The view is defined with outer joins (the (+) operator) on the payment detail, vendor, vendor site, term, and lookup relationships. Consequently, a contract line party role without a corresponding OKL_PARTY_PAYMENT_DTLS row still appears in the result set, with the payment-related columns returned as null.

Underlying Base Objects

The view is constructed over the following documented base objects:

• OKC_K_LINES_B and OKL_K_LINES — contract line definitions, joined on line ID (CLETS.ID = KLETS.ID), with OKL_K_LINES linked to the subsidy.
• OKL_SUBSIDIES_B — the subsidy master, supplying SUBSIDY_ID and SUBSIDY_NAME.
• OKC_K_PARTY_ROLES_B — the party role on the contract line, supplying CPL_ID, the vendor reference (OBJECT1_ID1), and DNZ_CHR_ID.
• OKL_PARTY_PAYMENT_DTLS — the payment detail record, exposed through ID, OBJECT_VERSION_NUMBER, PAY_SITE_ID, PAYMENT_TERM_ID, PAYMENT_METHOD_CODE, and PAY_GROUP_CODE.
• PO_VENDORS and PO_VENDOR_SITES_ALL — vendor and vendor site descriptions (VENDOR_NAME, PAY_SITE_NAME).
• AP_TERMS_TL — the translated payment term name, restricted to the current language.
• AP_LOOKUP_CODES and PO_LOOKUP_CODES — lookup views resolving payment method and pay group codes into displayed field values.

The dependency on AP_TERMS_TL is central to the object's behavior under multilingual deployments, since the language predicate is evaluated per session.

Key Columns

• CPL_ID, CLETS_ID, DNZ_CHR_ID, SUBSIDY_ID, SUBSIDY_NAME — identify the contract line, contract, and subsidy driving the refund.
• ID, OBJECT_VERSION_NUMBER, PYD_CPL_ID, OBJECT1_ID1 — the payment detail identifier, row version for concurrent update control, and the party role linkage.
• VENDOR_ID, VENDOR_NAME, PAY_SITE_ID, PAY_SITE_NAME — the payee and its remit-to site.
• PAYMENT_TERM_ID, PAYMENT_TERM_NAME — the payment term identifier and its translated description, the attribute most frequently searched.
• PAYMENT_METHOD_CODE, PAYMENT_METHOD_NAME — the payment instrument code and its lookup display value.
• PAY_GROUP_CODE, PAY_GROUP_NAME — the pay group code and its lookup display value.

Common Use Cases and Queries

Typical scenarios include subsidy refund reporting, vendor payment profile verification, and reconciliation of payment terms across subsidy contracts.

• List refunds by payment term name.
• Review payment method and pay group distribution across subsidies.
• Identify lines where payment details are missing (outer-join nulls).

SELECT subsidy_name, vendor_name, pay_site_name,
       payment_term_name, payment_method_name, pay_group_name
FROM   apps.okl_or_subsidy_refunds
WHERE  payment_term_name = :p_term_name;

SELECT subsidy_id, subsidy_name, COUNT(*) refund_lines
FROM   apps.okl_or_subsidy_refunds
WHERE  payment_term_id IS NULL
GROUP  BY subsidy_id, subsidy_name;