DBA merging creditors  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

DBA merging creditors

Postby neil.interactit » Mon Mar 25, 2024 4:51 pm

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.
neil.interactit
Kohai
Kohai
 
Posts: 232
Joined: Wed Dec 03, 2014 2:36 pm
Topics Solved: 6

Re: DBA merging creditors

Postby Mike.Sheen » Tue Mar 26, 2024 2:31 pm

neil.interactit wrote: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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2485
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 769

Re: DBA merging creditors

Postby neil.interactit » Thu Mar 28, 2024 1:33 pm

Perfect, thanks.
neil.interactit
Kohai
Kohai
 
Posts: 232
Joined: Wed Dec 03, 2014 2:36 pm
Topics Solved: 6

Re: DBA merging creditors

Postby neil.interactit » Thu Apr 04, 2024 1:34 pm

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.
neil.interactit
Kohai
Kohai
 
Posts: 232
Joined: Wed Dec 03, 2014 2:36 pm
Topics Solved: 6

Re: DBA merging creditors  Topic is solved

Postby Mike.Sheen » Thu Apr 04, 2024 6:29 pm

neil.interactit wrote: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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2485
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 769

Re: DBA merging creditors

Postby neil.interactit » Tue Apr 16, 2024 2:57 pm

No probs, thanks.

And here was me, thinking of brushing up on my tango!
neil.interactit
Kohai
Kohai
 
Posts: 232
Joined: Wed Dec 03, 2014 2:36 pm
Topics Solved: 6

Re: DBA merging creditors

Postby neil.interactit » Tue May 28, 2024 6:38 pm

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
neil.interactit
Kohai
Kohai
 
Posts: 232
Joined: Wed Dec 03, 2014 2:36 pm
Topics Solved: 6


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest