Spilt of the invoices due to more than 15K trades

Split invoice:
 In HSBC Brokerage, invoices involve two types of data: bankside and broker-side. Broker-side data, typically provided as an Excel file, includes details like Invoice Ref, Trade Data, Value Date, and more. When the number of trades exceeds 20,000, splitting the invoice into parts reduces settlement time and prevents Merit Performance Issues. Without splitting, large invoices may cause slowness and delays in the settlement process.

• Whenever Support team receives a request to Spilt of the invoices due to more than 15,000 trades as shown in below screenshot. We need to follow the below process to Split the Invoice into 2 or 3 based on Operations team requirement

• What is an Invoice – In HSBC Brokerage there will be two types of data, one is bank side and the other is Broker side data. The bank side data will be available with the operations team and the broker side data will be provided by their broker to the Operations team, which contains an excel with trades inside it with columns like Invoice Ref, Trade Data, Value Data, Pay Date, CCY etc.
• Why do we need to Split it - Once the Operations team have the data from their broker and requests to settle any invoice, then they will settle the invoice. In few scenarios the no. of trades will be more than 15,000. where it will consume more time to settle the invoice and may cause Merit Performance Issue, to consume less time, we need to Split the Invoice in 2 or 3 based on the Ops requirement
• What is the impact if we do not Split it - If we don’t Split the invoice in to 2 or more if it has trades more than 15,000, it may lead to issues like performance issues, Slowness Issue, Frozen and may consume much time to settle the Invoice than the usual time

Steps to Split the Invoice

• Firstly, check for the Original and New Invoice Keys in the Requesters email. If the invoice keys are not provided, please get it from requester. The Invoice keys will be like as shown below

Original Invoice - CFEUR24281ELC103-10_ATLANTIS

New invoice 1 - CFEUR24281ELC103-10_CP1

New invoice 2 - CFEUR24281ELC103-10_ATLCP1

• Login into the production server and open SQL and write the below Query to know the count of the original Invoice key.

select count(*) from MRT_SNCLAIMSTRADES WHERE MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLANTIS'

Total no of records – 44003

• Distribute the count equally in all the 2 or 3 invoices (Can vary depending on the total number of trades on the invoice. In the current example we are considering 3 invoices)

• Now run the below query in SQL to Split the Invoice In first new Invoice key provided by user

• Query to transfer trades to first invoice - CFEUR24281ELC103-10_CP1

if exists (select 1 from MRT_SNCLAIMSTRADES where MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLANTIS')

BEGIN

UPDATE MRT_SNCLAIMSTRADES

SET MRTINVKEY='CFEUR24281ELC103-10_CP1',MRTSUBACNO= 'CP1'

WHERE MRT_KEY IN(SELECT TOP 15000 MRT_KEY from  MRT_SNCLAIMSTRADES WHERE MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLANTIS')

END

• Query to transfer trades to second invoice - CFEUR24281ELC103-10_ATLCP1

if exists (select 1 from MRT_SNCLAIMSTRADES where MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLANTIS')

BEGIN

UPDATE MRT_SNCLAIMSTRADES

SET MRTINVKEY='CFEUR24281ELC103-10_ATLCP1',MRTSUBACNO='ATLCP1'

WHERE MRT_KEY IN(SELECT TOP 15000 MRT_KEY from  MRT_SNCLAIMSTRADES WHERE MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLANTIS')

END

• Once the queries are run, Check the count using below mentioned queries.

• Query to cross check count

select count(*) from MRT_SNCLAIMSTRADES WHERE MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLANTIS'

select count(*) from MRT_SNCLAIMSTRADES WHERE MRTPRODTYPE='ETD' AND MRTINVKEY=' CFEUR24281ELC103-10_CP1'

select count(*) from MRT_SNCLAIMSTRADES WHERE MRTPRODTYPE='ETD' AND MRTINVKEY='CFEUR24281ELC103-10_ATLCP1'

• Login to website and search the invoices in All invoices tab. In our example, we must search 3 invoices.

• Flag the invoices and right click to select “Mark for Recalc”. Wait for some time, monitor the ‘’lines’’ column to check whether the trades got moved or not.

\

• Once it is updated send mail to Operations team to check from their side.

• Once the Operations team confirms that the Original invoice has been Splitted, Please procced to close the case