K3 cancelling Batch Management SQL scripts

/*

K3 cancel Batch Management SQL script (tested on K3 v10.4 in)

Cause: Because batch management is enabled at the beginning of the year, but problems are found during the subsequent return and re-processing process, and the process cannot be transferred, you must cancel the batch. Because a large amount of business data already exists, it is impossible to start again. Batch cancellation can only be performed through underlying statements.

Procedure:
Find four batch-related tables
They are: icinvinitial initialization data table icbal inventory balance table poinvbal managed balance table (ignore or ignore) icinvbal inventory balance table icinventory instant Inventory (records can be cleared, by checking inventory, data is automatically generated)

Handling principle: whether the same records exist after batch cancellation in the same warehouse, and if yes, merge

Some required fields: fitemid Product internal code fstockid warehouse internal code

These three fields are the fields to be cleared: fbatchno batch number fkfperiod shelf life fkfdate production/purchase date

Because our company's set of books also enable the shelf life, so the initial merged records are not in place, the shelf life of fkfperiod data will be cleared in the future, the second merge, pass.

The following is a script to cancel a batch (no processing duration). Copy the script to the query analyzer and execute it.
*/

 

-- 1. initialize the table
-- Transfers data summary to a temporary table
Select fbrno, fperiod, fstockid, fitemid, convert (varchar (200), '') as fbatchno, sum (fbegqty) as fbegqty, sum (freceive) as freceive, sum (fsend) as fsend,
Sum (fytdreceive) as fytdreceive, sum (fytdsend) as fytdsend, sum (fendqty) as fendqty, sum (fbegbal) as fbegbal,
Sum (fdebit) as fdebit, sum (fcredit) as fcredit, sum (fytddebit) as fytddebit, sum (fytdcredit) as fytdcredit, sum (fendbal) as fendbal,
Sum (fbegdiff) as fbegdiff, sum (freceivediff) as freceivediff, sum (fsenddiff) as fsenddiff, fbillinterid, funitid,
Sum (fauxbegqty) as fauxbegqty, sum (fytdbegqty) as fytdbegqty, sum (fytdauxbegqty) as fytdauxbegqty, sum (fytdauxreceive) as fytdauxreceive,
Sum (fytdauxsend) as fytdauxsend, sum (fytdbegbal) as fytdbegbal, sum (fytdbegdiff) as fytdbegdiff, sum (fytdreceivediff) as fytdreceivediff,
Sum (fytdsenddiff) as fytdsenddiff, fkfdate, fkfperiod, fspid, sum (fsecbegqty) as fsecbegqty, sum (fsecreceive) as fsecreceive,
Sum (fsecsend) as fsecsend, sum (fsecytdreceive) as fsecytdreceive, sum (fsecytdsend) as fsecytdsend, sum (fsecendqty) as fsecendqty,
Fsecunitid, fstockindate, fauxpropid, 0 as fseccoefficient, sum (fauxunitbegqty) as fauxunitbegqty, sum (fytdauxunitbegqty) as fytdauxunitbegqty,
Sum (fytdauxunitsend) as fytdauxunitsend, sum (fytdauxunitreceive) as fytdauxunitreceive, fsnlistid
Into # tempicinvinitial
From icinvinitial group by fbrno, fperiod, fstockid, fitemid, fbillinterid, funitid, fkfdate, fkfperiod, fspid, fsecunitid, fstockindate, fauxpropid, fsnlistid
-- Clear the table
Truncate table icinvinitial
-- Switch back data
Insert into icinvinitial select * from # tempicinvinitial
-- Auxiliary Conversion Rate
Update icinvinitial set fseccoefficient = case when fsecbegqty <> 0 then fbegqty/fsecbegqty else 0 end
-- Delete a temporary table
Drop table # tempicinvinitial

-- 2. Balance Table
-- Transfers data summary to a temporary table
Select fbrno, fyear, fperiod, fitemid, convert (varchar (200), '') as fbatchno, sum (fbegqty) as fbegqty, sum (freceive) as freceive, sum (fsend) as fsend,
Sum (fytdreceive) as fytdreceive, sum (fytdsend) as fytdsend, sum (fendqty) as fendqty, sum (fbegbal) as fbegbal,
Sum (fdebit) as fdebit, sum (fcredit) as fcredit, sum (fytddebit) as fytddebit, sum (fytdcredit) as fytdcredit, sum (fendbal) as fendbal,
Sum (fbegdiff) as fbegdiff, sum (freceivediff) as freceivediff, sum (fsenddiff) as fsenddiff, sum (fenddiff) as fenddiff,
Fbillinterid, fentryid, fstockgroupid, sum (fytdreceivediff) as fytdreceivediff, sum (fytdsenddiff) as fytdsenddiff,
Sum (fsecbegqty) as fsecbegqty, sum (fsecreceive) as fsecreceive, sum (fsecsend) as fsecsend, sum (fsecytdreceive) as fsecytdreceive,
Sum (fsecytdsend) as fsecytdsend, sum (fsecendqty) as fsecendqty, fstockindate, fauxpropid
Into # tempicbal
From icbal group by fbrno, fyear, fperiod, fitemid, fbillinterid, fentryid, fstockgroupid, fstockindate, fauxpropid
-- Clear the table
Truncate table icbal
-- Switch back data
Insert into icbal select * from # tempicbal
-- Delete a temporary table
Drop table # tempicbal

-- 3. Inventory balance table
-- Transfers data summary to a temporary table
Select fbrno, fyear, fperiod, fstockid, fitemid, convert (varchar (200), '') as fbatchno, sum (fbegqty) as fbegqty, sum (freceive) as freceive, sum (fsend) as fsend,
Sum (fytdreceive) as fytdreceive, sum (fytdsend) as fytdsend, sum (fendqty) as fendqty, sum (fbegbal) as fbegbal,
Sum (fdebit) as fdebit, sum (fcredit) as fcredit, sum (fytddebit) as fytddebit, sum (fytdcredit) as fytdcredit, sum (fendbal) as fendbal,
Sum (fbegdiff) as fbegdiff, sum (freceivediff) as freceivediff, sum (fsenddiff) as fsenddiff, sum (fenddiff) as fenddiff, fbillinterid, fstockplaceid,
Fkfperiod, fkfdate, sum (percentage) as percentage, sum (fytdsenddiff) as fytdsenddiff, sum (fsecbegqty) as fsecbegqty, sum (fsecreceive) as fsecreceive,
Sum (fsecsend) as fsecsend, sum (fsecytdreceive) as fsecytdreceive, sum (fsecytdsend) as fsecytdsend, sum (fsecendqty) as fsecendqty,
Fauxpropid, fstockindate
Into # tempicinvbal
From icinvbal group by fbrno, fyear, fperiod, fstockid, fitemid, fbillinterid, fstockplaceid, fkfperiod, fkfdate, fauxpropid, fstockindate
-- Clear the table
Truncate table icinvbal
-- Switch back data
Insert into icinvbal select * from # tempicinvbal
-- Delete a temporary table
Drop table # tempicinvbal

----------- ==============================
-- The managed balance table does not need to be processed
-- Transfers data summary to a temporary table
Select fbrno, fyear, fperiod, fstockid, fitemid, convert (varchar (200), '') as fbatchno, sum (fbegqty) as fbegqty,
Sum (freceive) as freceive, sum (fsend) as fsend,
Sum (fytdreceive) as fytdreceive, sum (fytdsend) as fytdsend, sum (fendqty) as fendqty, sum (fbegbal) as fbegbal,
Sum (fdebit) as fdebit, sum (fcredit) as fcredit, sum (fytddebit) as fytddebit, sum (fytdcredit) as fytdcredit,
Sum (fendbal) as fendbal,
Sum (fbegdiff) as fbegdiff, sum (freceivediff) as freceivediff, sum (fsenddiff) as fsenddiff, sum (fenddiff) as fenddiff, fbillinterid,
Fkfperiod, fkfdate, sum (fspid) fspid, sum (fdcspid) fdcspid, sum (fsecbegqty) as fsecbegqty, sum (fsecreceive) as fsecreceive,
Sum (fsecsend) as fsecsend, sum (fsecytdreceive) as fsecytdreceive, sum (fsecytdsend) as fsecytdsend,
Sum (fsecendqty) as fsecendqty,
Fauxpropid
Into # temppoinvbal
From poinvbal group by fbrno, fyear, fperiod, fstockid, fitemid, fbillinterid, fkfperiod, fkfdate, fauxpropid
-- Clear the table
Truncate table poinvbal
-- Switch back data
Insert into poinvbal select * from # temppoinvbal
-- Delete a temporary table
Drop table # temppoinvbal
-- Insert into poinvbal select * From poinvbalback
---

-- 4. ******************** real-time inventory tables
-- Transfers data summary to a temporary table
Select fbrno, fitemid, convert (varchar (200), '') as fbatchno, fstockid, sum (fqty) as fqty, sum (fbal) as fbal,
Fstockplaceid, fkfperiod, fkfdate, sum (fqtylock) as fqtylock, fauxpropid, sum (fsecqty) as fsecqty
Into # tempicinventory
From icinventory group by fbrno, fitemid, fstockid, fstockplaceid, fkfperiod, fkfdate, fauxpropid
-- Clear the table
Truncate table icinventory
-- Switch back data
Insert into icinventory select * from # tempicinventory
-- Delete a temporary table
Drop table # tempicinventory

-- The Managed Inventory table virtual warehouse real-time inventory table does not need to be processed
-- Transfers data summary to a temporary table
Select fbrno, fitemid, convert (varchar (200), '') as fbatchno, fstockid, sum (fqty) as fqty, sum (fbal) as fbal,
Fstockplaceid, fkfperiod, fkfdate, fstocktypeid, fauxpropid, sum (fsecqty) as fsecqty
Into # temppoinventory
From poinventory group by fbrno, fitemid, fstockid, fstockplaceid, fkfperiod, fkfdate, fstocktypeid, fauxpropid
-- Clear the table
Truncate table poinventory
-- Switch back data
Insert into poinventory select * from # temppoinventory
-- Delete a temporary table
Drop table # temppoinventory

 

-- Clear material batch attribute item table
Update t_icitem set fbatchmanager = 0 where fbatchmanager = 1

 

 

 

-------- The following code is not executed to handle batch issues. If not, you can execute the following code:
-- Check the backup table without data
-- Business Document Table
Declare @ strsql varchar (2000)
Declare @ tablename varchar (100)
Declare @ fieldname varchar (100)
Declare cur_table cursor
For select distinct TT. fentrytable, Tf. ffieldname from ictransactiontype TT
Inner join (select distinct FID, ffieldname from ictemplateentry where fheadcaption like 'batch % ') TF
On TT. ftemplateid = TF. FID
Open cur_table
 
Fetch next from cur_table into @ tablename, @ fieldname
While @ fetch_status = 0
Begin
Select @ strsql = 'alter table' + @ tablename + 'Disable trigger all' + char (13)
Select @ strsql = @ strsql + 'update' + @ tablename + 'set' + @ fieldname + '= ''' + char (13)
Select @ strsql = @ strsql + 'alter table' + @ tablename + 'Enable trigger all'
Exec (@ strsql)
Fetch next from cur_table into @ tablename, @ fieldname
End
Close cur_table
Deallocate cur_table
Declare cur_table cursor
For select distinct TT. fheadtable, Tf. ffieldname from ictransactiontype TT
Inner join (select distinct FID, ffieldname from ictemplate where fcaption like 'batch % ') TF
On TT. ftemplateid = TF. FID
Open cur_table
 
Fetch next from cur_table into @ tablename, @ fieldname
While @ fetch_status = 0
Begin
Select @ strsql = 'alter table' + @ tablename + 'Disable trigger all' + char (13)
Select @ strsql = @ strsql + 'update' + @ tablename + 'set' + @ fieldname + '= ''' + char (13)
Select @ strsql = @ strsql + 'alter table' + @ tablename + 'Enable trigger all'
Exec (@ strsql)
Fetch next from cur_table into @ tablename, @ fieldname
End
Close cur_table
Deallocate cur_table