--source-side extract for cms.terms (pipekit CMS module)
--CMS terms live in the generic CODE table (A2 = 'NN'); the structured attributes are
--packed into the fixed-width CHAR column A215 at fixed byte offsets. Parse them HERE on
--DB2 where A215 is intact -- do NOT sync raw A215 and parse in Postgres: the transfer
--strips leading spaces on discount-bearing rows, which shifts every offset.
--offsets verified against the working query in sql/db2/CMS/AR/terms/Terms Codes.sql
--REPLACE(...,' ','0') guards each numeric field: unpopulated term types leave those
--positions blank, and a bare INT()/FLOAT() over a space would fail the whole extract.
SELECT
     LTRIM(RTRIM(A9)) AS term
    ,LTRIM(RTRIM(A30)) AS descr
    --discount % offered (e.g. 1.00 = 1%); A215 pos 102-105, scaled by 10000
    ,ROUND(FLOAT(REPLACE(SUBSTR(A215,102,4),' ','0')) / FLOAT(10000),2) AS disc_pct
    --days within which the discount may be taken; A215 pos 84-86
    ,INT(REPLACE(SUBSTR(A215,84,3),' ','0')) AS disc_days
    --net pay days; A215 pos 64-66
    ,INT(REPLACE(SUBSTR(A215,64,3),' ','0')) AS pay_days
    --pay percent (rarely used split-term %); A215 pos 10-12
    ,FLOAT(REPLACE(SUBSTR(A215,10,3),' ','0')) AS pay_perc
    --fixed pay date (seasonal/dated terms): A215 pos 28-33 packed MMDDYY -> YYYY-MM-DD
    --emitted as ISO text (null when the date field is zero) so a malformed packed value
    --cannot fail the extract; cast to date in Postgres on read
    ,CASE
        WHEN INT(REPLACE(SUBSTR(A215,28,6),' ','0')) = 0 THEN CAST(NULL AS CHAR(10))
        ELSE '20' || SUBSTR(A215,32,2) || '-' || SUBSTR(A215,28,2) || '-' || SUBSTR(A215,30,2)
     END AS pay_date
FROM
    LGDAT.CODE
WHERE
    A2 = 'NN'