DeReconcile Inventory

2023-06-27T16:45:30Z

Mbradley:

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:
<PRE>
select SESSION_No,trx_date from artprtrx
where trx_type='B' AND TRX_DATE >'6/1'
GROUP BY SESSION_NO,TRX_DATE
</PRE>

Export Session List - good for creating where clause:
<PRE>
select SESSION_No from artprtrx
where trx_type='B' AND TRX_DATE >'6/1'
GROUP BY SESSION_NO,TRX_DATE
</PRE>

List GL accounts (this can be exported):
<PRE>
select * from glttrx
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
</PRE>

Update the Reconciled flag in ARTPRTRX (Needed for deletion)
<PRE>
update artprtrx
set reconciled_flag='Y'
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
AND RECONCILED_FLAG<>'Y'
</PRE>

Delete the B records created by the Reconciliations.
<PRE>
DELETE from artprtrx
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
AND TRX_TYPE='B'
</PRE>

Clear the Reconciliation fields from the transactions (revert standard transactions back to an unreconciled state)
<PRE>
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)
</PRE>

Delete related G/L:
<PRE>
DELETE from GLTTRX
where SESSION_NO in
(317609,318526,319938,320229,320288,320295,
320431,320500,320517)
AND SOURCE_APP='R'
</PRE>

Copy & Paste into IB Console - Enter in Session Numbers
<PRE>
/*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 */
</PRE>

KeystoneIntranet - User contributions [en]

DeReconcile Inventory