DeReconcile Inventory

These queries can be used to undo an inventory reconciliation or a whole group of reconciliation sessions. If you dereconcile one session, all sessions after that must be dereconciled.

Review Sessions:

select SESSION_No,trx_date from artprtrx
where trx_type='B' AND TRX_DATE >'6/1'
GROUP  BY SESSION_NO,TRX_DATE

Export Session List - good for creating where clause:

select SESSION_No from artprtrx
where trx_type='B' AND TRX_DATE >'6/1'
GROUP  BY SESSION_NO,TRX_DATE

List GL accounts (this can be exported):

select * from glttrx 
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)

Update the Reconciled flag in ARTPRTRX (Needed for deletion)

update artprtrx
set reconciled_flag='Y'
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
AND RECONCILED_FLAG<>'Y'

Delete the B records created by the Reconciliations.

DELETE from artprtrx
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
AND TRX_TYPE='B'

Clear the Reconciliation fields from the transactions (revert standard transactions back to an unreconciled state)

UPDATE artprtrx
SET RECONCILED_FLAG='N'
, RECON_SESSION_NO=NULL, RECON_TRANS_NO=NULL
where RECON_SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)

Delete related G/L:

DELETE from GLTTRX 
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
AND SOURCE_APP='R'

Copy & Paste into IB Console - Enter in Session Numbers

/*Update the Reconciled flag in ARTPRTRX (Needed for deletion)*/
update artprtrx
set reconciled_flag='Y'
where SESSION_NO = :"Recon Session"
AND RECONCILED_FLAG<>'Y'; /* Test for Y is not needed - but faster */

/*Delete the B records created by the Reconciliations.*/
DELETE from artprtrx
where SESSION_NO = :"Recon Session"
AND TRX_TYPE IN ('C','L','B'); /* Extra protection - do not need to test for B */

/*Clear the Reconciliation fields from the transactions (revert standard transactions back to an unreconciled state)*/
UPDATE artprtrx
SET RECONCILED_FLAG='N', RECON_SESSION_NO=NULL, RECON_TRANS_NO=NULL
where RECON_SESSION_NO = :"Recon Session";

/*Delete related G/L:*/
DELETE from GLTTRX 
where SESSION_NO = :"Recon Session"
AND SOURCE_APP='R';  /* Extra security - not needed to test for R */