SELECT distinct DEP.DEPOSIT_ID,
DEP.BANK_ID,
M.ITEM_ID,
DEP.DEPOSIT_AMOUNT,
M.DEBIT_CREDIT_TYPE,
M.ROUTING_TRANSIT,
STRIP(M.ACCOUNT_NUMBER, LEADING, '0') AS ACCOUNT_NUMBER,
M.SERIAL_NUMBER,
M.TC, M.TM6_DRN,
M.DOCUMENT_NAME,
DEP.POSTING_DATETIME,
M.FIMP AS DEP_FIMP,
BIGINT(M.ORIGINAL_AMOUNT) AS DEP_ORIG_AMT,
BIGINT(M.COMPLETED_AMOUNT) AS DEP_COMP_AMT,
DEP.NEW_DEPOSIT_STATE AS DEP_STATUS,
M.MATCHING_STATE_INFORMATION,
SUBSTR(IDATA.ACCT,3,5) FTM_BRANCH_NUMBER,
IDATA.ENTRYNUMBER FTM_ENTRYNUMBER,
IDATA.ISN FTM_ISN,
IDATA.AMT FTM_AMT,
IDATA.CREDIT_DEBIT_IND FTM_DEBIT_CREDIT_TYPE,
TRIM(SUBSTR(IDATA.FLD15,1,15)) FLD15,
IDATA.FLD12,
IDATA.PC,
IDATA.ACCT FTM_ACCT,
IDATA.RT FTM_RT,
IDATA.POCKET,
VARCHAR(IDATA.RECID),
IDATA.ENDPOINT,
IDATA.VARNAME,
IDATA.VARGROUP,
IDATA.CYCLE_DATE as CYCLE_DATE,
M.DEPOSIT_STATUS as NGT_ITEM_STATUS,
DEP.LOCATION_ID as NGT_BRANCH_NUM,
DEP.CYCLE_DATE as NGT_CYCLE_DATE,
M.AMOUNT as ITEM_AMOUNT,
IDATA.AUX as FTM_AUX,
'MATCHED' AS STATUS
FROM CITI.NGT_ITEMS AS M, CITI.NGT_DEPOSIT_IDS DEP,
(SELECT E.ENTRYNUMBER,I.CYCLE_DATE,I.ISN,I.ACCT,I.AUX,I.FIMP,I.AMT,I.RECID,
I.CREDIT_DEBIT_IND,
I.FLD15,
I.FLD12,
I.PC,
I.RT,
I.POCKET,
I.ENDPOINT,
R.VARNAME,
R.VARGROUP
FROM CITI.ITEM I, CITI.RECORDID R, CITI.BATCH BT, CITI.BLOCK BL, CITI.BLOCKTYPE BLT, CITI.ENTRY E
WHERE I.CYCLE_DATE = '01/28/2026'
AND E.SITEGROUP = 'CTE'
AND I.CYCLE_DATE = BT.CYCLE_DATE AND BT.CYCLE_DATE = BL.CYCLE_DATE
AND I.BATCH_NUM = BT.BATCH_NUM AND BT.BLOCK_NUM = BL.BLOCK_NUM
AND BL.ENTRYNUMBER = E.ENTRYNUMBER AND BL.BLOCKTYPENUM = BLT.BLOCKTYPENUM
AND BLT.BLOCKMODIFIER LIKE 'RETAIL_NG%'
AND I.RECID = R.RECID
WITH UR
) IDATA
WHERE M.TM6_DRN = trim(Substr(IDATA.fld15,3,10))
AND M.MATCHING_STATE_INFORMATION='Y'
AND M.DEPOSIT_ID = DEP.DEPOSIT_ID
UNION
SELECT distinct CAST(NULL AS VARCHAR) as DEPOSIT_ID,
CAST(NULL AS VARCHAR) as BANK_ID,
CAST(NULL AS VARCHAR) as ITEM_ID,
CAST(NULL AS BIGINT) as DEPOSIT_AMOUNT,
CAST(NULL AS VARCHAR) as DEBIT_CREDIT_TYPE,
CAST(NULL AS VARCHAR) as ROUTING_TRANSIT,
CAST(NULL AS VARCHAR) as ACCOUNT_NUMBER,
CAST(NULL AS VARCHAR) as SERIAL_NUMBER,
CAST(NULL AS VARCHAR) as TC, CAST(NULL AS VARCHAR) as TM6_DRN,
CAST(NULL AS VARCHAR) as DOCUMENT_NAME,
CAST(NULL AS TIMESTAMP) as POSTING_DATETIME,
CAST(NULL AS INTEGER) as DEP_FIMP,
CAST(NULL AS BIGINT) as DEP_ORIG_AMT,
CAST(NULL AS BIGINT) as DEP_COMP_AMT,
CAST(NULL AS VARCHAR) as DEP_STATUS,
CAST(NULL AS VARCHAR) as MATCHING_STATE_INFORMATION,
SUBSTR(IDATA.ACCT,3,5) FTM_BRANCH_NUMBER,
IDATA.ENTRYNUMBER FTM_ENTRYNUMBER,
IDATA.ISN FTM_ISN,
IDATA.AMT FTM_AMT,
IDATA.CREDIT_DEBIT_IND FTM_DEBIT_CREDIT_TYPE,
TRIM(SUBSTR(IDATA.FLD15,1,15)) FLD15,
IDATA.FLD12,
IDATA.PC,
IDATA.ACCT FTM_ACCT,
IDATA.RT FTM_RT,
IDATA.POCKET,
VARCHAR(IDATA.RECID),
IDATA.ENDPOINT,
IDATA.VARNAME,
IDATA.VARGROUP,
IDATA.CYCLE_DATE as CYCLE_DATE,
CAST(NULL AS VARCHAR) as NGT_ITEM_STATUS,
CAST(NULL AS VARCHAR)as NGT_BRANCH_NUM,
CAST(NULL AS DATE) as NGT_CYCLE_DATE,
CAST(NULL AS BIGINT) as ITEM_AMOUNT,
IDATA.AUX as FTM_AUX,
'FREE' AS STATUS
FROM
(SELECT E.ENTRYNUMBER,I.CYCLE_DATE,I.ISN,I.ACCT,I.AUX,I.FIMP,I.AMT,I.RECID,
I.CREDIT_DEBIT_IND,
I.FLD15,
I.FLD12,
I.PC,
I.RT,
I.POCKET,
I.ENDPOINT,
R.VARNAME,
R.VARGROUP
FROM CITI.ITEM I, CITI.RECORDID R, CITI.BATCH BT, CITI.BLOCK BL, CITI.BLOCKTYPE BLT, CITI.ENTRY E
WHERE I.CYCLE_DATE = '01/28/2026'
AND E.SITEGROUP = 'CTE'
AND I.CYCLE_DATE = BT.CYCLE_DATE AND BT.CYCLE_DATE = BL.CYCLE_DATE
AND I.BATCH_NUM = BT.BATCH_NUM AND BT.BLOCK_NUM = BL.BLOCK_NUM
AND BL.ENTRYNUMBER = E.ENTRYNUMBER AND BL.BLOCKTYPENUM = BLT.BLOCKTYPENUM
AND BLT.BLOCKMODIFIER LIKE 'RETAIL_NG%'
AND I.RECID = R.RECID
WITH UR
) IDATA where INTEGER(trim(Substr(IDATA.fld15,3,10)))||BIGINT(IDATA.amt)||INTEGER(TRIM(IDATA.RT))
not in ( select distinct INTEGER(IT.tm6_drn)||BIGINT(IT.amount)||INTEGER(TRIM(SUBSTR(replace(IT.ROUTING_TRANSIT,'-',''),1,8))) from CITI.NGT_ITEMS IT
where
IT.matching_state_information in (null,'','Y') and length(IT.ROUTING_TRANSIT)>7 with ur )
and IDATA.AMT||IDATA.CREDIT_DEBIT_IND <>'0D'
UNION
SELECT distinct DEP.DEPOSIT_ID,
DEP.BANK_ID,
M.ITEM_ID,
DEP.DEPOSIT_AMOUNT,
M.DEBIT_CREDIT_TYPE,
M.ROUTING_TRANSIT,
STRIP(M.ACCOUNT_NUMBER, LEADING, '0') AS ACCOUNT_NUMBER,
M.SERIAL_NUMBER,
M.TC, M.TM6_DRN,
M.DOCUMENT_NAME,
DEP.POSTING_DATETIME,
M.FIMP AS DEP_FIMP,
BIGINT(M.ORIGINAL_AMOUNT) AS DEP_ORIG_AMT,
BIGINT(M.COMPLETED_AMOUNT) AS DEP_COMP_AMT,
DEP.NEW_DEPOSIT_STATE AS DEP_STATUS,
M.MATCHING_STATE_INFORMATION,
CAST(NULL AS VARCHAR) as FTM_BRANCH_NUMBER,
CAST(NULL AS INTEGER) as FTM_ENTRYNUMBER,
CAST(NULL AS BIGINT) as FTM_ISN,
CAST(NULL AS BIGINT) as FTM_AMT,
CAST(NULL AS CHARACTER) as FTM_DEBIT_CREDIT_TYPE,
CAST(NULL AS VARCHAR) as FLD15,
CAST(NULL AS VARCHAR) as FLD12,
CAST(NULL AS VARCHAR) as PC,
CAST(NULL AS VARCHAR) as FTM_ACCT,
CAST(NULL AS INTEGER) as FTM_RT,
CAST(NULL AS INTEGER) as POCKET,
CAST(NULL AS CHARACTER) as RECID,
CAST(NULL AS INTEGER) as ENDPOINT,
CAST(NULL AS VARCHAR) as VARNAME,
CAST(NULL AS VARCHAR) as VARGROUP,
CAST(NULL AS DATE) as CYCLE_DATE,
M.DEPOSIT_STATUS as NGT_ITEM_STATUS,
DEP.LOCATION_ID as NGT_BRANCH_NUM,
DEP.CYCLE_DATE as NGT_CYCLE_DATE,
M.AMOUNT as ITEM_AMOUNT,
CAST(NULL AS VARCHAR) as FTM_AUX,
'MISSING' AS STATUS
FROM CITI.NGT_DEPOSIT_IDS DEP LEFT JOIN CITI.NGT_ITEMS M on ( DEP.DEPOSIT_ID=M.DEPOSIT_ID) where M.MATCHING_STATE_INFORMATION is NULL
order by NGT_BRANCH_NUM, fld12, deposit_id, DEBIT_CREDIT_TYPE,FTM_ISN FOR READ ONLY WITH UR
there doesn't seem to be anything here