Create Trace IDs for Inventory

tim-wunder

All of our Fabricated parts have Trace Profiles set up. We are interested in creating Trace Profiles for all Purchased parts as well. We currently have about 8500 parts with inventory that do not have a trace profile configured.

Has anyone had luck with creating in batch trace profiles for parts with inventory on hand?

The Visual UI will not allow it. Typical practice would be to adjust out all inventory for a part, create the trace profile, then adjust the inventory back in and create the trace ID. Doing this with 8500 parts would be a bit problematic and a data entry nightmare. So I'm wondering if some T-SQL trickery could be used to create the necessary Trace ID and Trace Inventory Transactions that would satisfy Visual.

Our goal would ultimately be to make all parts traceable so that when parts are received into inventory, Visual will create a trace ID that would be tied to a specific PO receiver line. 

This would be for the purpose of calculating any associated tariff that might apply to the use of said purchased part. Since Receiver Lines have a country of origin, we could require that the receipt specify a county of origin. So we'd have a tie in from the trace ID to the county of origin specified on the receir line.

For those items that we currently have on hand in inventory, we'd assign a generic trace ID without tie-in to a PO Receipt. So we would be basically in the same place with those parts that we are now (no traceability to the country). But once the parts have the trace profile set up, future receipts will have that tie-in.  

chandler

There are a few tables involved in the process.  TRACE_PROFILE holds the actual trace profile for the Part ID, TRACE holds the current state of the traced part by trace id and the TRACE_INV_TRANS table holds all of the ins and outs.

Doing this once the part has inventory transactions will take some work.  Easiest way to do TRACE_PROFILE is to create a cartesian script that selects all of the part ids from part and the rest of the fields from a single record in TRACE_PROFILE so that all parts you are entering will be identical except for the part id. A select statement would look like this:

SELECT P.ID,T.NUMBERING_ID,T.APPLY_TO_REC,T.FIELD4,T.FIELD5,T.etc... <- this has to include all required fields from trace_profile
FROM TRACE_PROFILE T, PART P
WHERE T.PART_ID = 'KT01795-062'
AND P.ID IN (list of PART_IDs or select statement for parts)

Once that returns the correct info, you would do an INSERT INTO TRACE_PROFILE AS <select statement> 

After that, for each trace id, you have to create a TRACE record.  So assuming you have 3 receipts and each of them has their own trace, you would create 3 records in TRACE.  

INSERT INTO [dbo].[TRACE]
([PART_ID] ,[ID] ,[NUMBERING_ID]
,[OUT_QTY] ,[IN_QTY] ,[REPORTED_QTY]
,[ASSIGNED_QTY] ,[APROPERTY_1] ,[CREATE_DATE]
,[DISP_IN_QTY] ,[DISP_OUT_QTY] ,[UNAVAILABLE_QTY]
,[SITE_ID])
VALUES
('YOURPARTID','TRACEID1','MTS NUMBER',0,QTY,0,0,'APROPERTY_1','1ST RECEIPT DATE',0,0,QTY*2,'SITEID')
('YOURPARTID','TRACEID2','MTS NUMBER',0,QTY,0,0,'APROPERTY_1','1ST RECEIPT DATE',0,0,QTY*2,'SITEID'),
('YOURPARTID','TRACEID3','MTS NUMBER',0,QTY,0,0,'APROPERTY_1','1ST RECEIPT DATE',0,0,QTY*2,'SITEID')

TRACEID1 - TRACEID3 are the actual trace ids for the part_id and must not already exist in the trace table.

MTS NUMBER is the NUMBERING_ID used in the trace profile table for the part_id

QTY is the qty that was received initially for this line

For APROPERTY records, you may need to change the insert to include APROPERTY(1-5) and NPROPERTY(1-5)
but that you will need to look at your other trace records to see what those need to be

1ST RECEIPT DATE is the transaction date for the inventory transaction for this trace id

UNAVAILABLE_QTY (where I show QTY*2) doesn't really make any sense. It seems to be all over the place
so when I have done this before, I have just set it to whatever my qty is * 2. If you want to be sure you get
it right, do a part trace in visual and check the record to see what you get.

SITEID is obviously your site id

For TRACE_INV_TRANS, the insert will need a record for each inventory transaction for each trace id

So if TRACEID1 has 4 inventory transactions with 1 receipt of 18 and 3 issues of 6 the trace_inv_trans insert would look like this:

INSERT INTO TRACE_INV_TRANS VALUES
('YOURPARTID','TRACEID1','INVTRANSID1',18,'2025-01-01',0),
('YOURPARTID','TRACEID1','INVTRANSID2',-6,'2025-02-11',0),
('YOURPARTID','TRACEID1','INVTRANSID3',-6,'2025-03-05',0),
('YOURPARTID','TRACEID1','INVTRANSID4',-6,'2025-04-10',0)

INVTRANSID(#) is the TRANSACTION_ID from inventory_trans 

The date is the TRANSACTION_DATE from inventory_trans.

I would suggest creating am excel file to create all of the records so you can get the sequential trace ids and start very small with a couple part ids before you just got batch crazy on this one.

Good luck.  It can be done.