A
AMV90
en
JD Edwards sql

20 queries SQL esenciales para JD Edwards

Las consultas SQL más útiles para diagnosticar, auditar y extraer datos de JD Edwards EnterpriseOne. Copia, pega y adapta.

18 de junio de 2026 · 7 min de lectura

Por qué SQL directo en JDE

A veces el One View Report no es suficiente. Necesitas cruzar tablas, diagnosticar un dato corrupto o extraer un reporte rápido para el equipo funcional. Estas 20 queries cubren los escenarios más comunes.

Importante: Ejecuta estas queries en un entorno de solo lectura o en una réplica. Nunca hagas UPDATE o DELETE directamente en producción sin un DBA supervisando.

Address Book

1. Buscar un registro por nombre o número

SELECT
    ABAN8   AS address_number,
    ABALPH  AS name,
    ABAT1   AS search_type,
    ABAC01  AS category_01,
    ABTAX   AS tax_id
FROM F0101
WHERE ABAN8 = 1001
   OR ABALPH LIKE '%ACME%'
ORDER BY ABAN8;

2. Direcciones completas de un Address Book

SELECT
    a.ABAN8   AS address_number,
    a.ABALPH  AS name,
    b.ALADD1  AS address_line_1,
    b.ALADD2  AS address_line_2,
    b.ALCTY1  AS city,
    b.ALADDS  AS state,
    b.ALADDZ  AS zip,
    b.ALCTR   AS country
FROM F0101 a
JOIN F0116 b ON a.ABAN8 = b.ALAN8
WHERE b.ALEFTB = (
    SELECT MAX(ALEFTB)
    FROM F0116
    WHERE ALAN8 = b.ALAN8
)
ORDER BY a.ABAN8;

Accounts Payable

3. Vouchers abiertos (sin pagar)

SELECT
    RPAN8   AS supplier,
    RPDOC   AS voucher,
    RPDCT   AS doc_type,
    RPAG    AS gross_amount,
    RPAAP   AS open_amount,
    RPDDJ   AS due_date,
    RPPOST  AS posted
FROM F0411
WHERE RPAAP <> 0
  AND RPPOST = 'D'
ORDER BY RPDDJ;

4. Aging de proveedores a una fecha

SELECT
    RPAN8   AS supplier,
    ab.ABALPH AS name,
    SUM(CASE WHEN RPDDJ >= :fecha THEN RPAAP ELSE 0 END) AS not_due,
    SUM(CASE WHEN RPDDJ < :fecha AND RPDDJ >= :fecha - 30 THEN RPAAP ELSE 0 END) AS days_1_30,
    SUM(CASE WHEN RPDDJ < :fecha - 30 AND RPDDJ >= :fecha - 60 THEN RPAAP ELSE 0 END) AS days_31_60,
    SUM(CASE WHEN RPDDJ < :fecha - 60 THEN RPAAP ELSE 0 END) AS over_60,
    SUM(RPAAP) AS total_open
FROM F0411
JOIN F0101 ab ON RPAN8 = ab.ABAN8
WHERE RPAAP <> 0
GROUP BY RPAN8, ab.ABALPH
ORDER BY total_open DESC;

5. Historial de pagos de un proveedor

SELECT
    RNDOC   AS payment_doc,
    RNDCT   AS doc_type,
    RNCKNU  AS check_number,
    RNAG    AS amount,
    RNDGJ   AS gl_date,
    RNDMTJ  AS maturity_date
FROM F0413
WHERE RNAN8 = :supplier_number
ORDER BY RNDGJ DESC;

Accounts Receivable

6. Facturas abiertas por cliente

SELECT
    Rpan8   AS customer,
    RPDOC   AS invoice,
    RPDCT   AS doc_type,
    RPAG    AS original_amount,
    RPAAP   AS open_amount,
    RPDDJ   AS due_date
FROM F03B11
WHERE RPAAP <> 0
  AND RPAN8 = :customer_number
ORDER BY RPDDJ;

7. Recibos aplicados a una factura

SELECT
    RZDOC   AS receipt_doc,
    RZDCT   AS receipt_type,
    RZAG    AS applied_amount,
    RZCKNU  AS check_number,
    RZDGJ   AS gl_date
FROM F03B14
WHERE RZDOCM = :invoice_number
  AND RZDCTM = 'RI'
ORDER BY RZDGJ;

General Ledger

8. Balance de una cuenta a una fecha

SELECT
    GBOBJ   AS object,
    GBSUB   AS subsidiary,
    GBLT    AS ledger_type,
    GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 +
    GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12 AS ytd_balance
FROM F0902
WHERE GBCO   = '00001'
  AND GBOBJ  = '1110'
  AND GBLT   = 'AA'
  AND GBFY   = 26
ORDER BY GBSUB;

9. Detalle de journal entries por cuenta y periodo

SELECT
    GLOBJ   AS object,
    GLSUB   AS subsidiary,
    GLDOC   AS document,
    GLDCT   AS doc_type,
    GLLT    AS ledger,
    GLAA    AS amount,
    GLEXR   AS explanation,
    GLDGJ   AS gl_date,
    GLUSER  AS user_id
FROM F0911
WHERE GLCO  = '00001'
  AND GLOBJ = '1110'
  AND GLFY  = 26
  AND GLPN  = 6
ORDER BY GLDGJ, GLDOC;

Inventory

10. Stock actual por almacén

SELECT
    LIITM   AS item_number,
    LILOCN  AS location,
    LILOTN  AS lot,
    LIMCU   AS branch,
    LIPQOH  AS qty_on_hand,
    LIHCOM  AS qty_committed,
    LIPQOH - LIHCOM AS qty_available
FROM F41021
WHERE LIMCU = :branch_plant
  AND LIPQOH <> 0
ORDER BY LIITM, LILOCN;

11. Movimientos de inventario (cardex)

SELECT
    ILITM   AS item_number,
    ILDCT   AS doc_type,
    ILDOC   AS document,
    ILTQTY  AS quantity,
    ILUNCS  AS unit_cost,
    ILTRDT  AS transaction_date,
    ILUSER  AS user_id,
    ILMCU   AS branch
FROM F4111
WHERE ILITM = :item_number
  AND ILMCU = :branch_plant
ORDER BY ILTRDT DESC
FETCH FIRST 50 ROWS ONLY;

Purchase Orders

12. Órdenes de compra abiertas

SELECT
    PDDOCO  AS order_number,
    PDAN8   AS supplier,
    PDLNID  AS line,
    PDLITM  AS item,
    PDUORG  AS qty_ordered,
    PDUREC  AS qty_received,
    PDUORG - PDUREC AS qty_pending,
    PDAEXP  AS extended_amount,
    PDDRQJ  AS requested_date
FROM F4311
WHERE PDNXTR < '999'
  AND PDNXTR >= '400'
  AND PDUORG - PDUREC > 0
ORDER BY PDDRQJ;

Sales Orders

13. Pedidos de venta abiertos por cliente

SELECT
    SDDOCO  AS order_number,
    SDLNID  AS line,
    SDLITM  AS item,
    SDAN8   AS customer,
    SDUORG  AS qty_ordered,
    SDSOQS  AS qty_shipped,
    SDUORG - SDSOQS AS qty_backorder,
    SDAEXP  AS extended_amount,
    SDDRQJ  AS requested_date
FROM F4211
WHERE SDNXTR < '999'
  AND SDAN8 = :customer_number
  AND SDUORG - SDSOQS > 0
ORDER BY SDDRQJ;

Seguridad y auditoría

14. Usuarios activos en el sistema

SELECT
    SUSER   AS user_id,
    SUSESSION AS session_id,
    SUENV   AS environment,
    SUJOBNAM AS job_name,
    SUEVDT  AS login_date,
    SUEVTM  AS login_time
FROM F9312
WHERE SUEVDT >= :today
ORDER BY SUEVDT DESC, SUEVTM DESC;

15. Seguridad de aplicaciones por usuario

SELECT
    SSOBNM  AS application,
    SSUSER  AS user_id,
    SSAR    AS action_code,
    SSFUF   AS function_code
FROM F00950
WHERE SSUSER = :user_id
ORDER BY SSOBNM;

16. Audit trail — quién modificó un registro

SELECT
    ALUSER  AS user_id,
    AESSION AS session,
    ALACT   AS action,
    ALKEY   AS record_key,
    ALUPMJ  AS date_modified,
    ALUPMT  AS time_modified
FROM F9500041
WHERE ALKEY LIKE '%' || :address_number || '%'
ORDER BY ALUPMJ DESC, ALUPMT DESC;

UDC y configuración

17. Valores de un User Defined Code

SELECT
    DRSY    AS product_code,
    DRRT    AS udc_type,
    DRKY    AS code,
    DRDL01  AS description,
    DRDL02  AS description_2,
    DRSPHD  AS special_handling
FROM F0005
WHERE DRSY = '01'
  AND DRRT = 'ST'
ORDER BY DRKY;

18. Datos del Next Number

SELECT
    NNSY    AS system_code,
    NNN001  AS next_number_01,
    NNN002  AS next_number_02,
    NNN003  AS next_number_03
FROM F0002
WHERE NNSY = '04'
  AND NNCO = '00001';

Utilidades

19. Buscar en qué tabla está un campo

SELECT
    FRDTAI  AS data_item,
    FRTBNM  AS table_name,
    FRCLAS  AS alias,
    FRDTAT  AS data_type,
    FRDTAW  AS size
FROM F9210
WHERE FRCLAS = 'AN8'
ORDER BY FRTBNM;

20. Data Dictionary — definición de un campo

SELECT
    FRDTAI  AS data_item,
    FRDTAT  AS data_type,
    FRDTAW  AS size,
    FRDTAS  AS decimals,
    FRROWT  AS row_description,
    FRCOLT  AS column_title,
    FRDESY  AS product_code
FROM F9210
WHERE FRDTAI = 'AN8';

Tips para trabajar con SQL en JDE

  1. Julian dates: JDE almacena fechas en formato julián (CYYDDD). Para convertir:
-- Julian a fecha normal (Oracle)
SELECT TO_DATE(TO_CHAR(1900 + FLOOR(RPDGJ / 1000)) ||
       LPAD(MOD(RPDGJ, 1000), 3, '0'), 'YYYYDDD') AS fecha
FROM F0411
WHERE RPDGJ > 0;
  1. Montos: Los campos de monto suelen estar almacenados sin decimales. Divide entre 100 si la moneda tiene 2 decimales.
SELECT RPAG / 100 AS amount_with_decimals
FROM F0411;
  1. Espacios en blanco: JDE rellena con espacios. Usa TRIM():
SELECT TRIM(ABALPH) AS clean_name
FROM F0101
WHERE TRIM(ABALPH) LIKE '%ACME%';
  1. Nombres de tabla: Siempre empiezan con F seguido del número de sistema. F0101 = Address Book, F0411 = AP Ledger, F0911 = GL Transactions.

Conclusión

Estas queries son un punto de partida. Adáptalas a tu esquema específico — los nombres de compañía, path codes y customizaciones varían en cada instalación. Guarda las que uses frecuentemente en un snippet manager y compártelas con tu equipo.

sql oracle consultas datos