pt/pipekit
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
pipekit/config/modules/ocri.sql
Paul Trowbridge 890f10cbab Catch order status changes via an open-order hot set 
Hold/release rewrites OCRH.DCSTAT and DCHREA without touching any date
column, so no watermark could see it. A full-table row diff of LGDAT.OCRH
vs cms.ocrh (468,823 rows both sides) found 168 drifted rows, 0 missing,
0 extra -- and 159 of them changed with no date moving at all (checked
dcudat/dcodat/dccdat/dcmdat/dchdat/dcxdat/dcsdat/dcmxdt). 155 of the
drifts were DCSTAT, 4 DCHREA.

Every drifted row was still open (DCSTAT in A/N/H/B); none had reached
'C'. Only ~2,044 of 468,823 orders are open, so re-pulling the whole open
set each run is cheap and catches 100% of the observed drift. Applied to
ocrh and to the two modules that read its changed-set, ocri and ocrs.

Result: drift 168 -> 11 rows, DCSTAT 155 -> 3.

No invoice-driven branch: tested against LGDAT.OCRHT (CMS's header change
log, keyed DC3TMSP-leading via logical OCRHTX1) over the live watermark
window -- 1,107 headers changed, 65 missed by the date branches, 0 missed
by dates+open, and a 5- or 30-day invoiced-orders branch added exactly 0.
Closing an order writes dcudat/dccdat, so the date window covers the flip.

Also evaluated LGDAT.OCRIT (the order-line change log) as a replacement
for the open-order branch and rejected it: at the live 8-day watermark it
reports 0 changed orders not already caught, at ~12x the predicate cost,
and its lower row count means it declines to pull 18k lines the open set
would -- safe only if CMS's logging is complete, which is unverified.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
2026-08-11 11:02:57 -04:00

149 lines
4.7 KiB
SQL
Raw Permalink Blame History

WITH changed AS (
SELECT dcord# FROM lgdat.ocrh WHERE dcudat BETWEEN '{ocri_wm}' AND CURRENT_DATE
UNION
SELECT dcord# FROM lgdat.ocrh WHERE dcodat BETWEEN '{ocri_wm}' AND CURRENT_DATE
UNION
SELECT dcord# FROM lgdat.ocrh WHERE dccdat BETWEEN '{ocri_wm}' AND CURRENT_DATE
UNION
-- Hold / release and other status flips rewrite DCSTAT and DCHREA and
-- move no date column, so no watermark can see them. Only ~2k orders are
-- open, so re-pulling the whole open set every run is cheap.
-- Checked 2026-08-11 against LGDAT.OCRIT (CMS's own order-line change
-- log): OCRIT reports 0 changed orders that are not already caught here,
-- at ~12x the predicate cost -- this branch is a proven superset.
SELECT dcord# FROM lgdat.ocrh WHERE RTRIM(DCSTAT) <> 'C'
)
SELECT
"DDORD#" AS "ddord#",
"DDITM#" AS "dditm#",
"DDDES#" AS "dddes#",
DDQTOI AS ddqtoi,
DDQTBI AS ddqtbi,
DDQTSI AS ddqtsi,
RTRIM(DDUNIT) AS ddunit,
DDUNPR AS ddunpr,
DDTOTI AS ddtoti,
RTRIM(DDSLMN) AS ddslmn,
DDCPCT AS ddcpct,
DDCRAT AS ddcrat,
RTRIM(DDAIND) AS ddaind,
RTRIM(DDPTEX) AS ddptex,
RTRIM(DDFTCD) AS ddftcd,
CASE WHEN DDRDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDRDAT END AS ddrdat,
RTRIM(DDMAJS) AS ddmajs,
RTRIM(DDMINS) AS ddmins,
RTRIM(DDITST) AS dditst,
RTRIM(DDPART) AS ddpart,
RTRIM(DDGLC) AS ddglc,
RTRIM(DDCRRS) AS ddcrrs,
CASE WHEN DDSDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDSDAT END AS ddsdat,
RTRIM(DDTXGR) AS ddtxgr,
RTRIM(DDTXRT) AS ddtxrt,
RTRIM(DDNU07) AS ddnu07,
RTRIM(DDTMPS) AS ddtmps,
DDTEMP AS ddtemp,
DDQTOO AS ddqtoo,
DDQTBO AS ddqtbo,
DDQTSO AS ddqtso,
RTRIM(DDORUN) AS ddorun,
DDQTSP AS ddqtsp,
RTRIM(DDPRUN) AS ddprun,
DDTOTB AS ddtotb,
DDTDIS AS ddtdis,
DDTTAX AS ddttax,
DDTFRT AS ddtfrt,
DDTWGT AS ddtwgt,
RTRIM(DDTWUN) AS ddtwun,
DDTVOL AS ddtvol,
RTRIM(DDTVUN) AS ddtvun,
CASE WHEN DDODAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDODAT END AS ddodat,
RTRIM(DDSTKL) AS ddstkl,
RTRIM(DDSTOR) AS ddstor,
CASE WHEN DDDDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDDDAT END AS ddddat,
CASE WHEN DDFDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDFDAT END AS ddfdat,
CASE WHEN DDHDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDHDAT END AS ddhdat,
CASE WHEN DDQDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDQDAT END AS ddqdat,
CASE WHEN DDCDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDCDAT END AS ddcdat,
DDOLST AS ddolst,
DDCTMS AS ddctms,
RTRIM(DDCUSR) AS ddcusr,
RTRIM(DDDBUY) AS dddbuy,
"DDREQ#" AS "ddreq#",
RTRIM(DDPJNM) AS ddpjnm,
RTRIM(DDFUT1) AS ddfut1,
RTRIM(DDFUT2) AS ddfut2,
RTRIM(DDFUT3) AS ddfut3,
RTRIM(DDFUT4) AS ddfut4,
RTRIM(DDFUT5) AS ddfut5,
RTRIM(DDFUT6) AS ddfut6,
DDFUT7 AS ddfut7,
DDFUT8 AS ddfut8,
RTRIM(DDFUT9) AS ddfut9,
RTRIM(DDFUTA) AS ddfuta,
RTRIM(DDFUTB) AS ddfutb,
RTRIM(DDFUTC) AS ddfutc,
RTRIM(DDFUTD) AS ddfutd,
DDWTCH AS ddwtch,
DDWTCI AS ddwtci,
DDLDSQ AS ddldsq,
RTRIM("DDRAN#") AS "ddran#",
RTRIM(DDCTMZ) AS ddctmz,
RTRIM(DDOTMZ) AS ddotmz,
RTRIM(DDRTMZ) AS ddrtmz,
RTRIM(DDQTMZ) AS ddqtmz,
RTRIM(DDSTMZ) AS ddstmz,
DDRTME AS ddrtme,
DDQTME AS ddqtme,
DDSTME AS ddstme,
CASE WHEN DDRXDT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDRXDT END AS ddrxdt,
DDRXTM AS ddrxtm,
CASE WHEN DDQXDT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DDQXDT END AS ddqxdt,
DDQXTM AS ddqxtm,
RTRIM(DDPTYP) AS ddptyp,
RTRIM(DDAWLS) AS ddawls,
DDPOIT AS ddpoit,
RTRIM(DDSRVC) AS ddsrvc,
RTRIM(DDPRCL) AS ddprcl,
RTRIM(DDAVAC) AS ddavac,
RTRIM(DDDBAC) AS dddbac,
RTRIM(DDSCDP) AS ddscdp,
RTRIM(DDSOCP) AS ddsocp,
RTRIM(DDCSPI) AS ddcspi,
RTRIM(DDSCPI) AS ddscpi,
RTRIM(DDSCOR) AS ddscor,
RTRIM(DDSCIM) AS ddscim,
RTRIM("DDREV#") AS "ddrev#",
RTRIM(DDCSPA) AS ddcspa,
DDOITM AS ddoitm,
RTRIM(DDFROM) AS ddfrom,
RTRIM(DDMMEL) AS ddmmel,
RTRIM(DDFEAT) AS ddfeat,
RTRIM(DDOPTN) AS ddoptn,
RTRIM(DDFUT10) AS ddfut10,
RTRIM(DDFUT11) AS ddfut11,
RTRIM(DDFUT12) AS ddfut12,
RTRIM(DDFUT13) AS ddfut13,
RTRIM(DDFUT14) AS ddfut14,
RTRIM(DDFUT15) AS ddfut15,
RTRIM(DDFUT16) AS ddfut16,
RTRIM(DDFUT17) AS ddfut17,
DDFUT18 AS ddfut18,
DDFUT19 AS ddfut19,
DDFUT20 AS ddfut20,
RTRIM(DDCONPT) AS ddconpt,
DDUFRT AS ddufrt,
DDHFEP AS ddhfep,
RTRIM(DDOGLDC) AS ddogldc,
RTRIM(DDDRSF) AS dddrsf,
RTRIM(DDODBUY) AS ddodbuy,
RTRIM(DDATSPG) AS ddatspg,
RTRIM(DDWCOD) AS ddwcod,
DD3PNM AS dd3pnm,
DDWEOR AS ddweor,
DDWEOI AS ddweoi,
RTRIM(DDAGRM) AS ddagrm,
DDRNDR AS ddrndr,
DDRNDI AS ddrndi
FROM LGDAT.OCRI i
INNER JOIN changed c ON c.dcord# = i.ddord#
Reference in New Issue View Git Blame Copy Permalink