pt/pipekit
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
pipekit/config/modules/ocrh.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

191 lines
6.2 KiB
SQL
Raw Permalink Blame History

SELECT
"DCORD#" AS "dcord#",
CASE WHEN DCODAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCODAT END AS dcodat,
RTRIM(DCOTYP) AS dcotyp,
RTRIM(DCIUSE) AS dciuse,
RTRIM(DCBCUS) AS dcbcus,
RTRIM(DCBNAM) AS dcbnam,
RTRIM(DCBAD1) AS dcbad1,
RTRIM(DCBATT) AS dcbatt,
RTRIM(DCBPHO) AS dcbpho,
RTRIM(DCTRCD) AS dctrcd,
RTRIM(DCNU28) AS dcnu28,
RTRIM(DCSHVI) AS dcshvi,
RTRIM(DCSCUS) AS dcscus,
RTRIM(DCSNAM) AS dcsnam,
RTRIM(DCSAD1) AS dcsad1,
RTRIM(DCSAD2) AS dcsad2,
RTRIM(DCSAD3) AS dcsad3,
RTRIM(DCSAD4) AS dcsad4,
RTRIM(DCSAD5) AS dcsad5,
RTRIM(DCSAD6) AS dcsad6,
RTRIM(DCSAD7) AS dcsad7,
RTRIM(DCSAD8) AS dcsad8,
RTRIM(DCSAD9) AS dcsad9,
RTRIM(DCSAD10) AS dcsad10,
RTRIM(DCSPOS) AS dcspos,
RTRIM(DCPO) AS dcpo,
RTRIM(DCQUOT) AS dcquot,
RTRIM(DCCURR) AS dccurr,
CASE WHEN DCMDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCMDAT END AS dcmdat,
RTRIM(DCSLMN) AS dcslmn,
DCCPCT AS dccpct,
DCCRAT AS dccrat,
RTRIM(DCRES) AS dcres,
RTRIM(DCFSTL) AS dcfstl,
RTRIM(DCPSTL) AS dcpstl,
RTRIM(DCPPCL) AS dcppcl,
RTRIM(DCGDIS) AS dcgdis,
RTRIM(DCGDSF) AS dcgdsf,
RTRIM(DCSTAT) AS dcstat,
RTRIM(DCWOPR) AS dcwopr,
"DCNDL#" AS "dcndl#",
DCTOTO AS dctoto,
DCTDIS AS dctdis,
DCTTAX AS dcttax,
DCTFRT AS dctfrt,
DCTWGT AS dctwgt,
RTRIM(DCTWUN) AS dctwun,
DCTVOL AS dctvol,
RTRIM(DCTVUN) AS dctvun,
RTRIM(DCAVPR) AS dcavpr,
CASE WHEN DCUDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCUDAT END AS dcudat,
RTRIM(DCUSRE) AS dcusre,
RTRIM(DCUSRC) AS dcusrc,
"DCINV#" AS "dcinv#",
DCOBAL AS dcobal,
RTRIM(DCARBK) AS dcarbk,
RTRIM(DCSTKL) AS dcstkl,
DCRFNO AS dcrfno,
CASE WHEN DCXDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCXDAT END AS dcxdat,
RTRIM(DCTERR) AS dcterr,
RTRIM(DCSLIP) AS dcslip,
RTRIM(DCCARC) AS dccarc,
RTRIM(DCSERC) AS dcserc,
CASE WHEN DCSDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCSDAT END AS dcsdat,
CASE WHEN DCHDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCHDAT END AS dchdat,
CASE WHEN DCDDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCDDAT END AS dcddat,
CASE WHEN DCFDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCFDAT END AS dcfdat,
CASE WHEN DCRDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCRDAT END AS dcrdat,
CASE WHEN DCQDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCQDAT END AS dcqdat,
CASE WHEN DCCDAT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCCDAT END AS dccdat,
RTRIM(DCHREA) AS dchrea,
RTRIM(DCBOFG) AS dcbofg,
RTRIM(DCAPLG) AS dcaplg,
RTRIM(DCFOBC) AS dcfobc,
RTRIM(DCSCOM) AS dcscom,
RTRIM(DCOSRC) AS dcosrc,
RTRIM(DCOFXD) AS dcofxd,
RTRIM(DCSLCS) AS dcslcs,
RTRIM(DCCRTY) AS dccrty,
RTRIM(DCCRDT) AS dccrdt,
RTRIM("DCCRD#") AS "dccrd#",
RTRIM(DCEXPR) AS dcexpr,
RTRIM(DCHODR) AS dchodr,
DCCRNM AS dccrnm,
RTRIM(DCSHBF) AS dcshbf,
RTRIM(DCCLEN) AS dcclen,
DDFTTX AS ddfttx,
RTRIM(DCFUT1) AS dcfut1,
RTRIM(DCFUT2) AS dcfut2,
RTRIM(DCFLG1) AS dcflg1,
RTRIM(DCFLG2) AS dcflg2,
RTRIM(DCREFN) AS dcrefn,
RTRIM(DCPLNT) AS dcplnt,
RTRIM(DCFUT3) AS dcfut3,
RTRIM(DCFUT4) AS dcfut4,
RTRIM(DCFUT5) AS dcfut5,
RTRIM(DCFUT6) AS dcfut6,
RTRIM(DCFUT7) AS dcfut7,
RTRIM(DCFUT8) AS dcfut8,
RTRIM(DCFUT9) AS dcfut9,
CASE WHEN DCDAT1 IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCDAT1 END AS dcdat1,
CASE WHEN DCDAT2 IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCDAT2 END AS dcdat2,
RTRIM(DCFLG3) AS dcflg3,
RTRIM(DCFLG4) AS dcflg4,
RTRIM(DCFLG5) AS dcflg5,
RTRIM(DCFLG6) AS dcflg6,
RTRIM(DCFLG7) AS dcflg7,
DCFUT10 AS dcfut10,
DCFUT11 AS dcfut11,
DCFUT12 AS dcfut12,
DCFUT13 AS dcfut13,
DCFUT14 AS dcfut14,
DCFUT15 AS dcfut15,
DCFUT16 AS dcfut16,
DCFUT17 AS dcfut17,
DCFUT18 AS dcfut18,
DCFUT19 AS dcfut19,
DCFUT20 AS dcfut20,
RTRIM(DCDEPT) AS dcdept,
RTRIM(DCPROM) AS dcprom,
RTRIM(DCCLMO) AS dcclmo,
RTRIM(DCTTMZ) AS dcttmz,
RTRIM(DCCTMZ) AS dcctmz,
RTRIM(DCOTMZ) AS dcotmz,
RTRIM(DCMTMZ) AS dcmtmz,
RTRIM(DCQTMZ) AS dcqtmz,
RTRIM(DCSTMZ) AS dcstmz,
DCMTME AS dcmtme,
CASE WHEN DCMXDT IN (DATE('0001-01-01'), DATE('9999-12-31')) THEN NULL ELSE DCMXDT END AS dcmxdt,
DCMXTM AS dcmxtm,
RTRIM(DCR870) AS dcr870,
RTRIM(DCS870) AS dcs870,
RTRIM(DCFCHG) AS dcfchg,
RTRIM(DCSTTP) AS dcsttp,
RTRIM(DCSCNS) AS dcscns,
RTRIM(DCOTMC) AS dcotmc,
RTRIM(DCOFOM) AS dcofom,
RTRIM(DCOFTM) AS dcoftm,
RTRIM(DCAPPTM) AS dcapptm,
RTRIM(DCEXPO) AS dcexpo,
RTRIM(DCSDLV) AS dcsdlv,
RTRIM(DCSPIC) AS dcspic,
RTRIM(DCFRTC) AS dcfrtc,
RTRIM(DCCPLT) AS dccplt,
RTRIM(DCSDBC) AS dcsdbc,
RTRIM(DCSDBS) AS dcsdbs,
RTRIM(DCSDSC) AS dcsdsc,
RTRIM(DCSDSS) AS dcsdss,
RTRIM(DCSDCR) AS dcsdcr,
RTRIM(DCSCPO) AS dcscpo,
RTRIM(DCSCOR) AS dcscor,
RTRIM(DCFROM) AS dcfrom,
RTRIM(DCSCTY) AS dcscty,
RTRIM(DCSPOV) AS dcspov,
RTRIM(DCSCRY) AS dcscry,
RTRIM(DCFUT21) AS dcfut21,
RTRIM(DCFUT22) AS dcfut22,
RTRIM(DCFUT23) AS dcfut23,
RTRIM(DCFUT24) AS dcfut24,
RTRIM(DCFUT25) AS dcfut25,
RTRIM(DCFUT26) AS dcfut26,
RTRIM(DCSLSCH) AS dcslsch,
RTRIM(DCARBOI) AS dcarboi,
RTRIM(DCBBORT) AS dcbbort,
RTRIM(DCPBBOR) AS dcpbbor,
RTRIM(DCSCPHN) AS dcscphn,
RTRIM(DC4473PN) AS dc4473pn,
RTRIM(DCSCONT) AS dcscont,
DCADRSN AS dcadrsn,
DCHFEE AS dchfee,
DCHFEETX AS dchfeetx,
RTRIM(DCAFAX) AS dcafax,
RTRIM(DCAEMAIL) AS dcaemail,
RTRIM(DCFOID) AS dcfoid
FROM LGDAT.OCRH
WHERE
(dcudat BETWEEN '{ocrh_wm}' AND CURRENT_DATE)
OR (dcodat BETWEEN '{ocrh_wm}' AND CURRENT_DATE)
OR (dccdat BETWEEN '{ocrh_wm}' AND CURRENT_DATE)
-- Hold / release and other status flips rewrite DCSTAT and DCHREA and
-- touch no date column at all, so no watermark can see them. Open orders
-- are ~2k rows out of 469k -- cheap to re-pull in full on every run.
OR RTRIM(DCSTAT) <> 'C'
-- No invoice-driven branch here on purpose: measured 2026-08-11 against
-- LGDAT.OCRHT (CMS's header change log) over the live watermark window,
-- the two predicates above already catch 100% of changed headers, 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 to 'C'.
Reference in New Issue View Git Blame Copy Permalink