DBA merging creditors

[neil.interactit]

Hi guys,

We have a client that merged business operations many years ago and 2 Jiwas were combined. It now turns out that a handful of creditors remained duplicated with one accounts team using one version and the other account team using the other ... which is causing problems!

In each case the pair of records both refer to the same creditor and the client can see no audit reason not to combine these to a single record.

Are there any concerns or gotchas with this?

My plan would be to find every CreditorID FK (excepting those associated with the core CR_Main record - ie. on the actual creditor maintenance screen itself) and replace CreditorID BBBBBBBBBBB with AAAAAAAAAAA.

Yours tentitavely,
Neil.

[Mike.Sheen]

My plan would be to find every CreditorID FK (excepting those associated with the core CR_Main record - ie. on the actual creditor maintenance screen itself) and replace CreditorID BBBBBBBBBBB with AAAAAAAAAAA.

That should work - but you'll need to run usp_JIWA_Creditor_Recalc_Balances after you've done the transactions if you had any unaged transactions which were moved otherwise their balances will be wrong.

[neil.interactit]

Perfect, thanks.

[neil.interactit]

Hi Mike,

Would you mind casting your eye over and sanity checking the following?

This is the proposed split between leave alone and update IDs.

OTHER CREDITOR (CreditorID left alone as this is the duplicate and its children)

CR_Main.CreditorID
CR_CustomSettingValues.CreditorID
CR_Warehouse.CreditorID ??
CN_Main.CreditorID
CR_Notes.CreditorID **
CR_Documents.CreditorID **

TRANSACTIONAL (CreditorID updated to reference the 'main' creditor instead of the 'other' creditor)

BR_DirectCreditorPayments.CreditorID
BR_DirectCreditorReceipts.CreditorID
CB_BatchTranLines.DebtorCreditorID (WHERE DebtorCreditor IS CREDITOR)
CR_AllocationBatch.CreditorID
CR_BatchTranLines.AccountID
CR_Purchases.CreditorID
CR_Trans.CreditorID
CR_TransAlloc.CreditorID
IN_Creditor.CreditorID
IN_LogicalOrder.CentralWarehouseID ??
PI_Main.CreditorID
PO_BatchPurchaseOrders.CR_Main_RecID
PO_Main.CentralWarehouseID
PO_Main.CreditorID
RA_CreditMain.CreditorID
RA_RequestMain.CreditorID
RA_ShipMain.CreditorID
RE_Main.CreditorID
SH_BookInLines.CreditorID
SH_Costs.CreditorID
SH_Invoices.CreditorID
SH_Lines.CreditorID
SH_Main.ShippingAgent
SH_Voti.CreditorID
SY_RecentlyViewed.Document_RecID (also update also Document_DisplayText)
TD_Main.Source_RecID

** Even though an 'other' child, planning to update to reference the 'main' creditor instead, to effectively move these children to the 'main' record
?? Unsure about these

Cheers,
Neil.

[Mike.Sheen]

Would you mind casting your eye over and sanity checking the following?

Without obtaining the database you are wanting to do this on and actually doing it myself, I cannot tell you if there any problems with it or not, I'm afraid.

Copy the database, and run your merge scripts on the copy, inspect some of the merged creditors to see if they look sensible. Try to create and complete a PO -> GRN -> PI cycle for one of the merged creditors - check for the invoice showing up against the right creditor. Do the same for any other critical flows they perform - supplier returns, shipments and so on.

Right now, even if an obscene amount of money was offered for me to do it, I would have to decline as my dance card is full.

[neil.interactit]

No probs, thanks.

And here was me, thinking of brushing up on my tango!

[neil.interactit]

Hi Mike,

After prolonged and extensive testing we rolled this out ... only to get caught out around the edges. The client isn't overly concerned as there is no impact on their operations, but wants it resolved to finish the data cleansing.

The merge process (included) updating IN_Creditor.CreditorID from @mergeFromId to @mergeToId.

However SELECT IN_Creditor_RecID FROM IN_SupplierWarehouse WHERE InventoryID = 'ID' now returns differing IN_Creditor_RecIDs that point to the same CreditorID, and this shows as duplicate supplier entries on the Inventory Item / Supply tab.

So I wanted to sanity check with you the plan to:

For each IN_SupplierWarehouse duplicate pair:

Code: Select all

UPDATE PO_Lines.IN_SupplierWarehouse_RecID to the one associated with the correct IN_Creditor assocated with @mergeTo(Creditor)Id
UPDATE SH_Lines.IN_SupplierWarehouse_RecID to the one associated with the correct IN_Creditor assocated with @mergeTo(Creditor)Id
UPDATE SH_Lines.IN_Creditor_RecID  to the one associated with @mergeTo(Creditor)Id
(We don't have any IN_SupplierWarehouseCustomValues in play)
DELETE the IN_SupplierWarehouse  associated with the IN_Creditor assocated with @mergeFrom(Creditor)Id
DELETE the IN_Creditor assocated with @mergeFrom(Creditor)Id


Does this sound reasonable?

Cheers,
Neil