Purpose
Re-balance the JDE F4229 “Sales As-Of” file based on actual details in the F42119 Sales Detail History file. This should be used to maintain reporting accuracy if you have directly modified Sales Detail History in F42119.
About
JDE maintains a Sales History Summary in the file F4229 that is used to help increase reporting performance. Any modifications to F42119 (Insert, Update, Delete) will cause inconsistencies in the F4229 file that will lead to inaccurate sales reporting.
This SQL script looks at JDE data to re-build the F4229 based on actual values in the F42119. It uses the F0008 to determine the fiscal period for each Sales Detail record.
Usage
- Set the WHERE Clause to limit which Doc Type(s) should be included for rebuild. Set the same selection criteria in both PARAMETERs 1 & 2.
NOTE: You can remove both WHERE clauses to perform a complete rebuild of F4229. - Adjust the schema prefix “CRPDTA.” to suite your local environment.
- Execute the query, which will rebuild all F4229 records that match the Doc Type set in Step 1.
- *PROOF MODE* Comment out DELETE and INSERT INTO lines and then execute the query to produce a read-only output. This can be used to proof & compare against existing data.
Warnings
- This query WILL ERASE existing records in the F4229 file before they are rebuilt. Back up your F4229 before running this query.
- This query DOES NOT CALCULATE for Fiscal Periods 13 and 14. My employer simply doesn’t use those. It should be easy enough for you to add them in if needed.
- This query should be run as a TRANSACTION. Remember to COMMIT your changes, or you risk locking the database and/or losing your changes.
- This query makes permanent alterations to important business data. Please test this in DEV first and use proper promotion techniques before applying in Production.
Query
BEGIN TRANSACTION;
-- ** PARAMETER 1 ** WHERE Clause to limit operation to specific doc types. In this example, we will operate on 'SH' Doc Types ONLY!
-- ** CAUTION: You *MUST* Duplicate this data selection at PARAMETER 2 below!!! If you want to rebuild *ALL* F4229, remove the "Where" clause altogether from both parameters
-- Step 1.
-- Delete existing records from F4229 for 'SH' Doc Types. These records will be rebuilt in the next step.
DELETE FROM CRPDTA.F4229 WHERE SSDCTO = 'SH'; -- DISABLE THIS LINE & FOLLOWING 'INSERT' STATEMENT FOR READ-ONLY USAGE
Step 2.
-- Recompute & insert F4229 records for 'SH' Doc Types. This should be run at the same time as Step 1.
WITH SalesPeriod AS (
select *,
CASE
WHEN SDIVD <= CDD01J THEN '1'
WHEN SDIVD <= CDD02J THEN '2'
WHEN SDIVD <= CDD03J THEN '3'
WHEN SDIVD <= CDD04J THEN '4'
WHEN SDIVD <= CDD05J THEN '5'
WHEN SDIVD <= CDD06J THEN '6'
WHEN SDIVD <= CDD07J THEN '7'
WHEN SDIVD <= CDD08J THEN '8'
WHEN SDIVD <= CDD09J THEN '9'
WHEN SDIVD <= CDD10J THEN '10'
WHEN SDIVD <= CDD11J THEN '11'
WHEN SDIVD <= CDD12J THEN '12'
WHEN SDIVD <= CDD13J THEN '13'
WHEN SDIVD <= CDD14J THEN '14'
ELSE '??'
END AS Period
from crpdta.f42119
inner join crpdta.f0008 on SDIVD >= CDDFYJ AND SDIVD < CDDFYJ + 366
WHERE SDDCTO = 'SH' -- ** PARAMETER 2: What DocTypes should be recalculated MAKE SURE THIS MATCHES THE DATA SELECTION DEFINED ABOVE IN PARAMETER 1!!
),
Rebalance AS (
SELECT
SDDCTO, SDLNTY, SDAN8, SDITM, SDMCU, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, CDFY,
SUM(SDUORG) as Qty_YTD,
SUM(SDAEXP) as Dollars_YTD,
SUM(SDECST) as Cost_YTD,
SUM(CASE Period WHEN '1' THEN SDUORG ELSE 0 END) as QTY_1,
SUM(CASE Period WHEN '1' THEN SDAEXP ELSE 0 END) as Dollars_1,
SUM(CASE Period WHEN '1' THEN SDECST ELSE 0 END) as Cost_1,
SUM(CASE Period WHEN '2' THEN SDUORG ELSE 0 END) as QTY_2,
SUM(CASE Period WHEN '2' THEN SDAEXP ELSE 0 END) as Dollars_2,
SUM(CASE Period WHEN '2' THEN SDECST ELSE 0 END) as Cost_2,
SUM(CASE Period WHEN '3' THEN SDUORG ELSE 0 END) as QTY_3,
SUM(CASE Period WHEN '3' THEN SDAEXP ELSE 0 END) as Dollars_3,
SUM(CASE Period WHEN '3' THEN SDECST ELSE 0 END) as Cost_3,
SUM(CASE Period WHEN '4' THEN SDUORG ELSE 0 END) as QTY_4,
SUM(CASE Period WHEN '4' THEN SDAEXP ELSE 0 END) as Dollars_4,
SUM(CASE Period WHEN '4' THEN SDECST ELSE 0 END) as Cost_4,
SUM(CASE Period WHEN '5' THEN SDUORG ELSE 0 END) as QTY_5,
SUM(CASE Period WHEN '5' THEN SDAEXP ELSE 0 END) as Dollars_5,
SUM(CASE Period WHEN '5' THEN SDECST ELSE 0 END) as Cost_5,
SUM(CASE Period WHEN '6' THEN SDUORG ELSE 0 END) as QTY_6,
SUM(CASE Period WHEN '6' THEN SDAEXP ELSE 0 END) as Dollars_6,
SUM(CASE Period WHEN '6' THEN SDECST ELSE 0 END) as Cost_6,
SUM(CASE Period WHEN '7' THEN SDUORG ELSE 0 END) as QTY_7,
SUM(CASE Period WHEN '7' THEN SDAEXP ELSE 0 END) as Dollars_7,
SUM(CASE Period WHEN '7' THEN SDECST ELSE 0 END) as Cost_7,
SUM(CASE Period WHEN '8' THEN SDUORG ELSE 0 END) as QTY_8,
SUM(CASE Period WHEN '8' THEN SDAEXP ELSE 0 END) as Dollars_8,
SUM(CASE Period WHEN '8' THEN SDECST ELSE 0 END) as Cost_8,
SUM(CASE Period WHEN '9' THEN SDUORG ELSE 0 END) as QTY_9,
SUM(CASE Period WHEN '9' THEN SDAEXP ELSE 0 END) as Dollars_9,
SUM(CASE Period WHEN '9' THEN SDECST ELSE 0 END) as Cost_9,
SUM(CASE Period WHEN '10' THEN SDUORG ELSE 0 END) as QTY_10,
SUM(CASE Period WHEN '10' THEN SDAEXP ELSE 0 END) as Dollars_10,
SUM(CASE Period WHEN '10' THEN SDECST ELSE 0 END) as Cost_10,
SUM(CASE Period WHEN '11' THEN SDUORG ELSE 0 END) as QTY_11,
SUM(CASE Period WHEN '11' THEN SDAEXP ELSE 0 END) as Dollars_11,
SUM(CASE Period WHEN '11' THEN SDECST ELSE 0 END) as Cost_11,
SUM(CASE Period WHEN '12' THEN SDUORG ELSE 0 END) as QTY_12,
SUM(CASE Period WHEN '12' THEN SDAEXP ELSE 0 END) as Dollars_12,
SUM(CASE Period WHEN '12' THEN SDECST ELSE 0 END) as Cost_12,
MAX(SDIVD) as MaxInvoiceDate
FROM SalesPeriod
GROUP BY SDDCTO, SDLNTY, SDAN8, SDITM, SDMCU, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, CDFY
),
NewRecords AS (
SELECT
SDDCTO,
SDLNTY,
SDAN8,
SDITM,
IMLITM as SDLITM,
IMAITM as SDAITM,
SDMCU,
SDSRP1,
SDSRP2,
SDSRP3,
SDSRP4,
SDSRP5,
20 as CTRY, -- CAUTION: This query is only good for fiscal years 2000-2099
CDFY as FY,
'' as FQ,
Dollars_YTD,
Cost_YTD,
Dollars_YTD as [Dollars_YTD2],
Cost_YTD as [Cost_YTD2],
0 as PYES,
0 as PYEC,
QTY_1,
QTY_2,
QTY_3,
QTY_4,
QTY_5,
QTY_6,
QTY_7,
QTY_8,
QTY_9,
QTY_10,
QTY_11,
QTY_12,
0 as QTY_13,
0 as QTY_14,
Cost_1 as CI01,
Cost_2 as CI02,
Cost_3 as CI03,
Cost_4 as CI04,
Cost_5 as CI05,
Cost_6 as CI06,
Cost_7 as CI07,
Cost_8 as CI08,
Cost_9 as CI09,
Cost_10 as CI10,
Cost_11 as CI11,
Cost_12 as CI12,
0 as CI13,
0 as CI14,
Dollars_1,
Dollars_2,
Dollars_3,
Dollars_4,
Dollars_5,
Dollars_6,
Dollars_7,
Dollars_8,
Dollars_9,
Dollars_10,
Dollars_11,
Dollars_12,
0 as Dollars_13,
0 as Dollars_14,
MaxInvoiceDate
FROM Rebalance LEFT JOIN CRPDTA.F4101 ON SDITM = IMITM
)
INSERT INTO CRPDTA.F4229 -- Insert newly calculated records (DISABLE THIS LINE & THE TOP 'DELETE' STATEMENT FOR READ-ONLY USAGE)
SELECT * FROM NewRecords; -- Select newly calculated records
COMMIT;