INL Duty and Tariff Analysis – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Oracle Landed Cost Management · all Oracle Landed Cost Management reports

Overview

Duty, other charges and taxes per landed cost shipment line, with tariff code, country of origin and supplier, to analyse duty rates and the landed cost uplift on the item value.

The charge types counted as duty are selected in the Duty Charge Type parameter, all other charges are Other Charges. Amounts are the current landed cost allocations in functional currency, Duty Estimated the first calculation. LCM allocates a charge to the shipment lines by the charge type’s allocation basis, so an actual duty invoice matched to the shipment is spread over its lines, and the duty rate per line reflects that allocation rather than the customs entry line.

Oracle has no standard HTS field: the tariff code is the item’s category in the Tariff Code Category Set, an item category set holding the HTS codes.

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

Operating Unit, Organization Code, Shipment Date From, Shipment Date To, Supplier, Item, Country of Origin, Duty Charge Type, Tariff Code Category Set, Tariff Code

Oracle EBS Tables Used

fnd_territories_vl, mtl_category_sets_vl, mtl_item_categories, mtl_categories_kfv, inl_matches, inl_charge_lines, inl_ship_headers_all, inl_allocations, inl_ship_lines_all, pon_cost_factors_vl, org_access_view, inl_ship_line_groups, hr_all_organization_units_vl, mtl_parameters, org_organization_definitions, gl_ledgers, mtl_system_items_vl, ap_suppliers, po_line_locations_all, po_lines_all, po_headers_all, rcv_shipment_lines, rcv_shipment_headers

Report Categories

Enginatics, R12 only

INL Tariff Exposure on Open POs, INL Landed Cost Estimated vs Actual, INL Pending Actual Charges, INL Landed Unit Cost Trend, INL Landed Cost Adjustments, CAC Interface Error Summary, PO Headers and Lines

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 INL Duty and Tariff Analysis 10-Oct-2026 151355.xlsx
Blitz Report™ XML Import INL_Duty_and_Tariff_Analysis.xml
SQL Source INL Duty and Tariff Analysis.sql
Full Report on Enginatics www.enginatics.com/reports/inl-duty-and-tariff-analysis/

Case Study & Technical Analysis: INL Duty and Tariff Analysis

Executive Summary

The INL Duty and Tariff Analysis report shows duty, other charges and taxes for each landed cost shipment line, together with the tariff code, country of origin and supplier. It supports the analysis of duty rates and of the landed cost uplift on the item value, comparing the estimated and current duty amounts. Import compliance and cost accounting teams in Oracle Landed Cost Management (LCM) use it to check how duty has been allocated to receipts, to find lines with a high duty rate or uplift, and to review the tariff classification behind the duty. The report runs in Excel via Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. The inner block x groups the allocations by shipment header, shipment line and component type (item, charge or tax), using the allocation’s adjustment number.
  2. Each charge component is flagged as matched when a row exists in INL_MATCHES for the same shipment line and charge line type.
  3. The block z sums the item value, estimated duty, current duty, other charges and taxes per shipment line.
  4. z joins to INL_SHIP_HEADERS_ALL, INL_SHIP_LINES_ALL and INL_SHIP_LINE_GROUPS, then to the organization, ledger and item tables.
  5. Purchase order and receipt details are outer joined through the shipment line source type and RCV_SHIPMENT_LINES.LCM_SHIPMENT_LINE_ID.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does the duty rate on a line differ from the customs entry? A: LCM allocates each charge to shipment lines by the charge type’s allocation basis, so the duty per line reflects that allocation rather than the customs entry line.

Q: Where does the tariff code come from? A: Oracle has no standard HTS field, so the tariff code is the item’s category in the Tariff Code Category Set, which must be named as a parameter.

Q: What does Duty Variance show? A: It is the current duty less the estimated duty from the first calculation, so a positive value means the duty has increased since the estimate.

© Enginatics GmbH