SQL Query to export to excel basic information from JDE Sales Orders history file

Demographics: Oracle ERP1 / JDEdwards / Peoplesoft ERP0 / B7334 Module: Sales Orders

Credits; Sergio Vargas PEOPLECNC.COM

Keywords: How Extract Sales Orders information / History Sales Order export to excel / Extract sales order to text or excel file / JDE / JDEdwards

Exploring the JDEdwards Sales Reports those are limited to some few. Sales Managers usually request about information to be manipulated in excel worksheets.

The SQL will inquiry JDEdward Sales Order History Table F42119

Query conditions:

Query includes inventory items only. Freight lines will be excluded.
Query select sales order after the Sales Update program had been executed: last status activity rule is greater or equal 620 – Sales Update
Query converts JDE Julian dates to friendly dates in format ‘YYYY-MM-DD’
Basic Sales Order data included: Sell to, Ship to, Item, Sales reporting codes, Dates, Location, Lot, Cost, Profit and others fields.

Asumptions:
Sales update activity rule = ‘620’
Query had been tested in Oracle database 8.1.4. SQL might require minor adjustments.

How to use the SQl query script:

1. You might connect to a Oracle Database using sql*plus to execute the query
2. Also you can use WINsql that provides a free license to query your database.

Sample output file

Download

Download the query sql file now at: Sales order inquiry SQL query

License: This is free to use query, freely distributed.
Credit: Sergio Vargas from PEOPLECNC.COM
Help available to implement this script from PEOPLECNC.COM, contact now.

Disclaimer: THIS PUBLICATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. Use this query at your own risk

The query SQl script

SELECT F42119.SDDOCO as sales_order, F42119.SDLNID/1000 as line, F42119.SDMCU AS BRANCH_PLANT, F42119.SDAN8 AS AB_SELLTO, F0101.ABALPH AS SELL_TO, F42119.SDSHAN AS AB_SHIPTO, F0101_1.ABALPH as SHIP_TO, 19 + substr( F42119.SDTRDJ, 1, 1 ) || substr(F42119.SDTRDJ, 2,2) || '-' || to_char( to_date( substr( F42119.SDTRDJ, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDTRDJ, 4, 3 ) - 1 , 'DDD') , 'DD' ) AS transaction_date, 19 + substr( F42119.SDDRQJ, 1, 1 ) || substr(F42119.SDDRQJ, 2,2) || '-' || to_char( to_date( substr( F42119.SDDRQJ, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDDRQJ, 4, 3 ) - 1 , 'DDD') , 'DD' ) as requested_date, 19 + substr( F42119.SDPDDJ, 1, 1 ) || substr(F42119.SDPDDJ, 2,2) || '-' || to_char( to_date( substr( F42119.SDPDDJ, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDPDDJ, 4, 3 ) - 1 , 'DDD') , 'DD' ) AS promised_date , 19 + substr( F42119.SDADDJ, 1, 1 ) || substr(F42119.SDADDJ, 2,2) || '-' || to_char( to_date( substr( F42119.SDADDJ, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDADDJ, 4, 3 ) - 1 , 'DDD') , 'DD' ) AS actual_delivery, 19 + substr( F42119.SDIVD, 1, 1 ) || substr(F42119.SDIVD, 2,2) || '-' || to_char( to_date( substr( F42119.SDIVD, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDIVD, 4, 3 ) - 1 , 'DDD') , 'DD' ) AS invoice_date, 19 + substr( F42119.SDDGL, 1, 1 ) || substr(F42119.SDDGL, 2,2) || '-' || to_char( to_date( substr( F42119.SDDGL, 4, 3 ) , 'DDD') , 'MM' ) || '-' || to_char( to_date( substr( F42119.SDDGL, 4, 3 ) - 1 , 'DDD') , 'DD' ) AS gl_date, F42119.SDLITM as item_number, F42119.SDITM as short_item_number, F42119.SDLOCN as location, F42119.SDLOTN as lot_number, F42119.SDDSC1 as description, F42119.SDSRP1 as sales_reporting_1, F42119.SDSRP2 as sales_reporting_2, F42119.SDSRP3 as sales_reporting_3, F42119.SDSRP4 as sales_reporting_4, F42119.SDSRP5 as sales_reporting_5, F42119.SDUOM1 as unit_of_measure_primary, F42119.SDPQOR/10000 as order_quantity_primary, F42119.SDAEXP/100 as order_amount, F42119.SDECST/100 as extended_cost, (F42119.SDAEXP/100)-(F42119.SDECST/100) as order_profit, (((F42119.SDAEXP/100)-(F42119.SDECST/100))*100)/(F42119.SDAEXP/100) as profit_percent FROM EXPDDTA.F0101 F0101, EXPDDTA.F0101 F0101_1, EXPDDTA.F42119 F42119 WHERE F0101.ABAN8 = F42119.SDAN8 AND F42119.SDSHAN = F0101_1.ABAN8 AND ((F42119.SDLTTR>='620') AND (F42119.SDITM0)) ORDER BY F42119.SDDOCO