BIOVIA CISPro

Technical Note T27-2022

May 2022

Virtual Containers lost from database if viewed and saved in a box larger than 100 cells

Program

BIOVIA CISPro 2019, 2020 and 2021 (all service packs and hot fixes)

Background

Issue CIS-74307 is a known defect in versions earlier than CISPro 2022 whereby only 100 virtual containers can be viewed in a single freezer box.  However, if there are more virtual containers assigned to the freezer box and the contents are modified and saved, then the non-visible virtual containers are deleted from the CISPro database.  Modifications which can cause this issue on boxes larger than 100 cells includes move, rearrange and transfer.  This issue is resolved in CISPro 2022 since CIS-74307 is fixed in this version, however data lost in versions prior to CISPro 2022 would still be lost on upgrading to CISPro 2022.

To identify if this issue has occurred on your system, the following SQL can be run directly on the CISPro schema or from within a Report.  This SQL shows any Receipt Lots with missing Freezer Box Inventory and the actual number of missing Virtual Containers:

--SQL to find Receipt Lots with missing Assign Inventory count:
with
    ac as --actual count per Receipt Lot
        (select actualcount, nodeid as ReceiptLot_ID from receiptlot where actualcount is not null),
    vc as --n virtual container per Receipt Lot   
        (select nvl(sum(container_count),0) as n, receiptlot_id
        from lot_holders
        where container_count > 0
            and isinitial = 0
        group by receiptlot_id),
    cc as --n containers per Receipt Lot
        (select nvl(count(barcode),0) as n, receiptlot_id
        from container
        group by receiptlot_id)
select
    ac.actualcount - nvl(vc.n,0) - nvl(cc.n,0) - nvl(lh.container_count,0) as Num_Missing,
    rl.ReceiptLotNo
from
    ac
    left outer join vc on (ac.ReceiptLot_ID = vc.ReceiptLot_ID)
    left outer join cc on (ac.ReceiptLot_ID = cc.ReceiptLot_ID)
    join receiptlot rl on (ac.ReceiptLot_ID = rl.nodeid)
    left outer join lot_holders lh on (ac.ReceiptLot_ID = lh.ReceiptLot_ID and lh.isinitial = 1)
where
    ac.actualcount - nvl(vc.n,0) - nvl(cc.n,0) - nvl(lh.container_count,0) > 0

Resolution

This issue is resolved in BIOVIA CISPro 2022.

If the above SQL identifies missing Freezer Box content, then please contact BIOVIA CISPro Support for assistance to resolve this issue.

How to contact BIOVIA Support

If you have any questions, please contact BIOVIA Support.