AP 1099 Payments – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Payables · all Payables reports
Overview
1099 Payments report showing payments made to 1099 reportable suppliers. This is the Blitz Report equivalent of the Oracle standard 1099 Payments Report (APXTRRVT).
The report lists suppliers from the 1099 tape data (vendors processed for 1099 reporting) along with their payment totals within the specified date range.
Key features:
- Uses ap_1099_tape_data to filter vendors processed for 1099 reporting
- Validates against Tax Reporting Entity and balancing segments
- Calculates payment amounts using Oracle AP_UTILITIES_PKG.Net_Invoice_Amount
- Determines tax reporting site (site with tax_reporting_site_flag=Y or first alphabetical site)
- Handles void checks by excluding payments voided within the date range
- Supports Summary (totals by supplier/type) and Detail (individual checks/invoices) modes
- MISC4 (Backup Withholding) amounts tracked separately as Withheld Amount
- Distribution Total shows gross amounts before withholding adjustments
- Payment Amount shows net reportable amounts (with MISC4 as negative)
- Query Driver parameter controls balancing segment matching (INV=invoice distribution, PAY=bank cash account)
- Employee vendors use national_identifier from per_all_people_f for tax ID
- Tax ID cleanup removes dashes, spaces, and treats 000000000 as blank
When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CGST Rate, SGST Rate, IGST Rate, GST Supplier Registration No, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code.
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
Reporting Option, From Payment Date, To Payment Date, Tax Reporting Entity, Supplier Name, Income Tax Region, Federal Reportable Only, Query Driver
Oracle EBS Tables Used
ap_invoice_payments_all, ap_reporting_entities_all, ap_supplier_sites_all, ap_1099_tape_data
Report Categories
Related Reports
AP Invoices and Lines, AP Suppliers, AP Invoices and Lines 11i, AP Supplier Upload, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Analysis
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 | AP 1099 Payments 28-Dec-2025 142614.xlsx |
| Blitz Report™ XML Import | AP_1099_Payments.xml |
| SQL Source | AP 1099 Payments.sql |
| Full Report on Enginatics | www.enginatics.com/reports/ap-1099-payments/ |
Case Study & Technical Analysis: AP 1099 Payments
Executive Summary
The AP 1099 Payments report lists the payments made to 1099 reportable suppliers within a payment date range, grouped by supplier, tax reporting site, tax ID and 1099 income tax type. It is the Blitz Report equivalent of the Oracle standard 1099 Payments Report (APXTRRVT). Accounts payable staff and tax reporting teams use it to reconcile the amounts paid to each 1099 vendor before the year-end 1099 filing, and to separate gross reportable amounts from backup withholding. The report runs in Excel through Blitz Report.
Business Challenge
- Year-end 1099 reconciliation: Tax teams must establish, for each reportable supplier, the amount paid in the calendar year and the 1099 type it belongs to, which the standard payment history does not present in one view.
- Backup withholding: Amounts withheld under the MISC4 type must be reported separately from the reportable payment, and the net effect must be visible for each supplier.
- Multi-site vendors: A supplier can have several supplier sites, and tax reporting must be attributed to a single tax reporting site within the correct Tax Reporting Entity.
The Solution
- 1099 vendor scope: Suppliers are restricted to those present in
AP_1099_TAPE_DATA, the vendors processed for 1099 reporting, and matched to the Tax Reporting Entity and its balancing segment lines. - Prorated amount calculation: Each invoice distribution amount is prorated by the payment amount divided by the net invoice amount from
AP_UTILITIES_PKG.NET_INVOICE_AMOUNT, so partial payments yield the correct reportable portion. - Backup withholding split: Distributions typed MISC4 populate a separate Withheld Amount column, and their payment amount is sign-flipped so that Payment Amount reflects the net figure.
- Voided checks excluded: Payments whose check was voided inside the date window are removed, so voids do not inflate the totals.
Technical Architecture (High Level)
Primary Tables
AP_INVOICE_PAYMENTS_ALL: the payment-to-invoice link, supplying the payment amount and accounting date used for the date window.AP_INVOICE_DISTRIBUTIONS_ALL: the distribution lines that carry the 1099 type (TYPE_1099) and the distribution amount.AP_INVOICES_ALL: invoice header data, including the cancelled amount.AP_CHECKS_ALL: the check number, date and void date.AP_SUPPLIERSandAP_SUPPLIER_SITES_ALL: supplier identity, 1099 identifiers and the tax reporting site.AP_REPORTING_ENTITIES_ALLandAP_REPORTING_ENTITY_LINES_ALL: Tax Reporting Entity definitions and their balancing segment values.AP_1099_TAPE_DATA: the vendors processed for 1099 reporting.CE_BANK_ACCT_USES_ALLandCE_BANK_ACCOUNTS: the bank account used for the payment.GL_CODE_COMBINATIONSandGL_LEDGERS: balancing segment matching and the ledger.PER_ALL_PEOPLE_F: the employee record that supplies the national identifier for employee vendors.
Logical Relationships
AP_REPORTING_ENTITIES_ALLjoins toAP_REPORTING_ENTITY_LINES_ALLon the tax entity, then to the operating unit and ledger.- The balancing segment is matched to the distribution code combination (INV) or to the bank account asset code combination (PAY).
AP_SUPPLIERSjoins to the 1099 vendor list and to the single selected row fromAP_SUPPLIER_SITES_ALL.- Invoices join to their distributions and payments, and each payment joins to
AP_CHECKS_ALLand then to the bank account. - Detail rows are aggregated by supplier, supplier number, tax reporting site, tax ID, income tax type and region.
Parameters & Filtering
- Reporting Option: selects Summary or Detail output, which adds or removes the check and invoice columns.
- From Payment Date and To Payment Date: bound the payment accounting date, and decide which distributions are prorated.
- Tax Reporting Entity: limits the balancing segments and operating units reported.
- Federal Reportable Only: when set to Y, keeps only suppliers flagged as federal reportable.
- Query Driver: INV matches the balancing segment through the invoice distribution code combination, while PAY matches it through the bank account code combination.
Performance & Optimization
- The 1099 vendor restriction is an
INsubquery onAP_1099_TAPE_DATA, so only 1099 vendors enter the payment join. - Tax reporting site selection uses an analytic
ROW_NUMBERover the supplier site table, which avoids a correlated lookup per payment row. - Outer joins to
PER_ALL_PEOPLE_Fare restricted to the current effective row, so each supplier keeps one employee record.
FAQ
Q: How is backup withholding reported in the AP 1099 Payments report? A: Distributions typed MISC4 populate the Withheld Amount column as a positive value, and their payment amount is negated, so Payment Amount shows the net reportable figure.
Q: Why does a voided check not appear in the 1099 payment totals?
A: Payments whose check void date falls within the From and To Payment Date range are excluded by the condition on AP_CHECKS_ALL.VOID_DATE.
Q: How does the report choose the tax reporting site for a supplier? A: It ranks the supplier’s sites with an analytic function, placing the site flagged as tax reporting site first and otherwise the alphabetically first site, and keeps only the top row.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH