OKS Service Contract Line Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

This upload maintains the lines of existing Oracle Service Contracts through the standard Service Contracts public APIs: it updates header and line level values, adds new service and usage lines, and adds covered product or counter sublines.

Each row is one contract line or subline. Rows identified by the Line Number column update that line; sublines use the parent.child format, for example 1.2 for the second covered product of service line 1. Rows without a Line Number create a new line: leave Parent Line Number empty and enter Line Type, Item and the line dates for a top line, or enter a Parent Line Number, the Covered Product and the covered dates for a subline. A subline can reference a top line created by an earlier row of the same file. Header level values repeat on every row of a contract and are applied once.

Usage lines need a Usage Type, and a Usage Period for all types except Negotiated per Period. Actual per Period and Actual by Quantity lines are invoiced in arrears, which the upload sets automatically, and their sublines need a Default Quantity and a Base Reading.

Prices are written as entered. Changing the negotiated amount or unit price does not reprice the line through the price list and does not regenerate an existing billing schedule, and lines cannot be removed by this upload.

Updates are validated by the Service Contracts APIs, so changes not allowed for the current contract or line status are rejected with the corresponding API message.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Operating Unit, Contract Number, Contract Status, Line Status

Oracle EBS Tables Used

hz_cust_acct_sites_all, hz_cust_accounts, okc_k_party_roles_b, hz_parties, okc_line_styles_v, mtl_system_items_vl, okc_k_items, okc_k_lines_v, mtl_units_of_measure_vl, csi_item_instances, csi_counters_vl, okc_k_headers_all_b, okc_k_headers_tl, oks_k_headers_b, oks_billing_profiles_v, hr_all_organization_units_vl, okc_statuses_v, hz_cust_site_uses_all, okc_k_lines_b, oks_k_lines_b, mo_glob_org_access_tmp

Report Categories

Enginatics, R12 only, Upload

AR Transactions and Lines 11i, OKL Termination Quotes, CSI Customer Products Summary, GL Account Distribution Analysis, GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), CSI Counter Readings

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import OKS_Service_Contract_Line_Upload.xml
SQL Source OKS Service Contract Line Upload.sql
Full Report on Enginatics www.enginatics.com/reports/oks-service-contract-line-upload/

Case Study & Technical Analysis: OKS Service Contract Line Upload

Executive Summary

This upload maintains the lines of existing Oracle Service Contracts through the standard Service Contracts public APIs. It updates header and line values, adds service and usage lines, and adds covered product or counter sublines to a contract. Service contract administrators and service finance teams use it to extend or correct large contracts, for example when many covered products are added to a maintenance agreement or when usage lines are set up for counter-based billing. The template runs in Excel through Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. OKC_K_HEADERS_ALL_B joins to OKC_K_HEADERS_TL on id, restricted to the session language, and to OKS_K_HEADERS_B on the chr id.
  2. OKC_K_HEADERS_ALL_B joins to OKC_K_LINES_B on the dnz chr id, restricted to service line styles.
  3. Each line joins to its parent line through the cle id, and to OKS_K_LINES_B for the usage period.
  4. Each line joins to OKC_K_ITEMS for the covered product or counter, which resolves to CSI_ITEM_INSTANCES or CSI_COUNTERS_VL by the object type.
  5. The bill-to and ship-to sites join to HZ_CUST_SITE_USES_ALL, and the account numbers come from HZ_CUST_ACCOUNTS on the header and line customer account ids, with a fallback through HZ_CUST_ACCT_SITES_ALL when the account is not set.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I add a covered product to an existing service line? A: Enter a parent line number in the form parent.child, with the covered product and covered dates, and leave the line number empty.

Q: Why is my usage line rejected? A: Usage lines need a usage type, and a usage period for every type except negotiated per period.

Q: Can this upload remove a line? A: No. The upload does not remove lines.

© Enginatics GmbH