**Problem:**
change of standard price on a product valued by lot 
and with standard price category does not work

**Steps to reproduce:**
- create a storable product tracked and valued by lot
- set category as standard price
- set a cost of 10 and save
- click on the quantity smart button and then "update quantity"
- add a quantity of 1 in a new lot
- on the product form, change the cost to 12 and save
- reload the page

**Current behavior:**
the cost is back to 10

**Expected behavior:**
it should stay 12

**Cause of the issue:**
when we change the standard_price of the product, 
_change_standard_price() is called from the write method
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/product.py#L293

Inside _change_standard_price(): 
step 1: a new product.value is created
step 2 : we set the standard_price of the lots to be the same 
as the one of the product
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/product.py#L319-L323

In the create method for product.value (step 1), we call _set_value() 
on the moves with a remaining quantity
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/product_value.py#L95
At the end of set_value we call _update_standard_price() on 
our product
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/stock_move.py#L337

Because the product is lot_valuated we update the standard_price 
based on the avg_cost of the product (this is needed because 
for instance if the prod is avco we can not simply use 
_run_average_batch as it is the case for non lot valuated avco 
product, because then the result won't be a weighted average of 
each lot, whereas avg_cost does take this into account) 
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/product.py#L633-L634

To compute the avg_cost, inside _compute_value(), we use 
the total value of each lot
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/product.py#L226

The lots total value is computed inside the _compute_value() 
method of stock.lot. 
In this method, because the product is valued by standard_price
we use the standard price of the lot
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/stock_lot.py#L40
But this value hasn't been updated yet (it will be at the time 
of step 2) so it's still the old value (10 in our case). 

So the avg_cost of the product will also be the old value and 
the standard price will be udpated back the old value

Then, at the end of _change_standard_price() (at the time of step 2)
the standard price of the lots are set based on the standard price 
of the product (so it stays the old value)

**fix:**
Inside _update_standard_price(), if the product is valued by standard 
price we do nothing 
https://github.com/odoo/odoo/blob/eaa6c4352aec2be8519360c282f3f6504a2f263c/addons/stock_account/models/product.py#L639-L640
We apply the same logic for the lot_valued product, if it's standard_price
there is nothing to update

opw-5949146

Forward-Port-Of: odoo/odoo#264991

### Issue before this commit:
In the previous implementation, several Italian taxes, specifically the 4% INPS and the 4% Pension Fund (F.Pens), were not correctly initialized. Although the relevant EDI data was present in the source CSV templates, it was missing from the actual tax records in the database.

### Steps to reproduce the issue:
1. Download Accounting and l10n_it_edi
2. Go to Accounting -> Taxes
3. Open 4% INPS tax and 4% F.Pens and go to Advanced Options tab and see that no Pension Fund Type is associated by default

### Cause of the issue:
While moving the witholding data from l10n_it_edi to l10n_it in this commit https://github.com/odoo/odoo/commit/40e09ca01242 the templates were not correcly rendered and set up.

### Reason to introduce the fix:
For a tax to be correctly recognized from the XML, it is essential that we have the corresponding tax already configured in Odoo, including the specific type. We should have at least these two taxes fully configured so the system can elaborate them correctly when imported from vendor bills.

opw-6093221

---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr

Forward-Port-Of: odoo/odoo#258947

When updating a mo, if the new component has a multilvl bom, it will only create a mo for the direct child and not the next

Steps to reproduce:
-------------------
* Create a products "Main", "Final", "Semi", "Raw"
* Create a Bom for "Final" with "Semi" as component
* Create a Bom for "Semi" with "Raw as component
* Add MTO on Final and Semi
* Create a MO for Main with no components and confirm it
* Add "Final" to the mo as component as save. -> The MO for "Final" is correctly created with "Semi" as component but there is no MO for "Semi" with "Raw" as component.

Observation:
-------------

When updating de MO, it will write the new SM (Final) to the production, and we will call ```_autoconfirm_production``` with ```no_procurement```: https://github.com/odoo/odoo/blob/6fb69b5640743d3bc7bb52c73cb27da428f2451c/addons/mrp/models/mrp_production.py#L1051 Where we will directly confirm the sm (```_action_confirm```).

From the SM ```_action_confirm``` we will create and run a procurement (manufacture in our case).
From the manufacture we will create the new move line for Semi and go through ```action_confirm``` on the manufacturing order:
https://github.com/odoo/odoo/blob/9ef76a4d6010191ab7ab1a0d1085972901280dda/addons/mrp/models/stock_rule.py#L116-L118
In the MO ```action_confirm```, we will confirm the move and should create new procurement for the moves that need them, but, since in our case we have ```no_procurement``` in the context, we will set ```create_proc``` to false:
https://github.com/odoo/odoo/blob/9ef76a4d6010191ab7ab1a0d1085972901280dda/addons/mrp/models/mrp_production.py#L1635
Since ```create_proc``` is false we will not create a procurement for those move lines:
https://github.com/odoo/odoo/blob/6fb69b5640743d3bc7bb52c73cb27da428f2451c/addons/stock/models/stock_move.py#L1557-L1558
https://github.com/odoo/odoo/blob/6fb69b5640743d3bc7bb52c73cb27da428f2451c/addons/stock/models/stock_move.py#L1571-L1580

opw-6005675

Forward-Port-Of: odoo/odoo#258153

Issue
-----
`REF` field of Fedex labels is missing.

Steps to reproduce
-----
- Setup Fedex
- Create a product (set weight)
- Create a delivery for the product
	- Set carrier as Fedex
- Validate delivery
- Opend the label

> REF field is empty

Cause
-----
When filling the `CustomerReferences`, we only specify the SO

https://github.com/odoo/enterprise/blob/aae680f5b86fa87193ba6616e8431eed985b2ee7/delivery_fedex_rest/models/fedex_request.py#L309-L313

The `REF` field is populated using `CUSTOMER_REFERENCE` references, which is not present in this case.

Excerpt of the API DOC
-----
```
"CustomerReference": {
	"type": "object",
	"properties": {
		"customerReferenceType": {
			"type": "string",
			"description": [...],
			"example": "DEPARTMENT_NUMBER",
			"enum": [
				"CUSTOMER_REFERENCE",
				"DEPARTMENT_NUMBER",
				"INVOICE_NUMBER",
				"P_O_NUMBER",
				"INTRACOUNTRY_REGULATORY_REFERENCE",
				"RMA_ASSOCIATION"
			]
		},
		"value": {
			"type": "string",
			"description": [...],
			"example": "3686"
		}
	}
},
```

[...] replaces long description strings, refer to API for full documentation.

Result after fix
-----
<img width="477" height="738" alt="image" src="https://github.com/user-attachments/assets/0d3a0786-5b7d-41cc-8548-2dc7b0f379ab" />

-----
Ticket:
opw-6101620

Forward-Port-Of: odoo/enterprise#116870

**Steps to reproduce:**
- Create a company in Belgium and set the fiscal localisation accordingly.
- In the same company, create a fiscal position in Switzerland, set the foreign tax ID and then generate the taxes for it.
- Install the module account_edi_ubl_cii.
- Create and invoice for a belgian customer, with one product line having a 0% tax.
- Export the invoice as XML.
- Go to taxes, filter by purchase, and make sure that the 0% switzerland tax has a higher sequence than the belgian 0% tax.
- Import the previous invoice XML as a vendor bill.

**Issue:**
After importing the bill, the switzerland tax is used even though the fiscal localisation is belgian, which is wrong as it violates the constraint _validate_taxes_country

**Solution:**
Added a more selective domain to _import_fill_invoice_line_taxes

opw-5467936
---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr

Forward-Port-Of: odoo/odoo#265200
Forward-Port-Of: odoo/odoo#255848

Description of the issue/feature this PR addresses:

Searching for records without a binary attachment (e.g., `('binary_field', '=', False)`) previously generated a query using `NOT IN (SELECT res_id FROM ir_attachment...)`. On databases with a large `ir_attachment` table, materializing this entire list of IDs causes a significant performance bottleneck.

Replacing NOT IN with a NOT EXISTS allows PostgreSQL to short-circuit the evaluation as soon as it find a matching document, drastically reducing query execution time.

Current behavior before PR:

Searching for a "false-ish" binary with attachment generates a query with a `NOT IN`, slow when `ir_attachment` is large.

```python
>>> env["ir.ui.menu"].search([("web_icon_data", "!=", False)])
2026-03-06 15:59:51,326 516177 DEBUG odoo19 odoo.sql_db: [1.076 ms] query: SELECT "ir_ui_menu"."id" FROM "ir_ui_menu" WHERE ("ir_ui_menu"."active" IS TRUE AND "ir_ui_menu"."id" IN (SELECT res_id FROM ir_attachment WHERE res_model = 'ir.ui.menu' AND res_field = 'web_icon_data')) ORDER BY "ir_ui_menu"."sequence"  , "ir_ui_menu"."id"   
ir.ui.menu(15, 1, 16)
>>> env["ir.ui.menu"].search([("web_icon_data", "=", False)])
2026-03-06 15:59:54,439 516177 DEBUG odoo19 odoo.sql_db: [0.665 ms] query: SELECT "ir_ui_menu"."id" FROM "ir_ui_menu" WHERE ("ir_ui_menu"."active" IS TRUE AND "ir_ui_menu"."id" NOT IN (SELECT res_id FROM ir_attachment WHERE res_model = 'ir.ui.menu' AND res_field = 'web_icon_data')) ORDER BY "ir_ui_menu"."sequence"  , "ir_ui_menu"."id"   
ir.ui.menu(62, 68, 3, 10, 43, 59, 4, 28, 44, 65, 6, 7, 29, 41, 45, 61, 66, 5, 18, 30, 31, 48, 49, 60, 69, 70, 9, 11, 12, 17, 19, 20, 21, 22, 23, 24, 25, 26, 27, 32, 33, 34, 36, 37, 38, 39, 40, 42, 46, 47, 52, 54, 56, 57, 58, 63, 71, 73, 74, 76, 78, 79, 80, 81, 50, 64, 51, 72, 75, 77, 35, 14, 13, 53, 2, 55, 67, 8)
```


Desired behavior after PR is merged:

Searching for a "false-ish" binary with attachment generates a query with a `NOT EXISTS`

```python
>>> env["ir.ui.menu"].search([("web_icon_data", "!=", False)])
2026-03-06 15:59:04,847 513555 DEBUG odoo19 odoo.sql_db: [0.945 ms] query: SELECT "ir_ui_menu"."id" FROM "ir_ui_menu" WHERE ("ir_ui_menu"."active" IS TRUE AND "ir_ui_menu"."id" IN (SELECT res_id FROM ir_attachment WHERE res_model = 'ir.ui.menu' AND res_field = 'web_icon_data')) ORDER BY "ir_ui_menu"."sequence"  , "ir_ui_menu"."id"   
ir.ui.menu(15, 1, 16)
>>> env["ir.ui.menu"].search([("web_icon_data", "=", False)])
2026-03-06 15:59:08,323 513555 DEBUG odoo19 odoo.sql_db: [0.628 ms] query: SELECT "ir_ui_menu"."id" FROM "ir_ui_menu" WHERE ("ir_ui_menu"."active" IS TRUE AND NOT EXISTS (SELECT 1 FROM ir_attachment WHERE res_model = 'ir.ui.menu' AND res_field = 'web_icon_data' AND res_id = "ir_ui_menu"."id")) ORDER BY "ir_ui_menu"."sequence"  , "ir_ui_menu"."id"   
ir.ui.menu(62, 68, 3, 10, 43, 59, 4, 28, 44, 65, 6, 7, 29, 41, 45, 61, 66, 5, 18, 30, 31, 48, 49, 60, 69, 70, 9, 11, 12, 17, 19, 20, 21, 22, 23, 24, 25, 26, 27, 32, 33, 34, 36, 37, 38, 39, 40, 42, 46, 47, 52, 54, 56, 57, 58, 63, 71, 73, 74, 76, 78, 79, 80, 81, 50, 64, 51, 72, 75, 77, 35, 14, 13, 53, 2, 55, 67, 8)
```



---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr

Forward-Port-Of: odoo/odoo#252525

Before this commit, finding a bom for a recordset of `products` involved looping over all the boms and it will loop over all the `product_variant_ids` of `bom.product_tmpl_id` if the bom's `product_id` is NULL. This approach might loop over variants which we are not trying to find a bom for. In additon to that, due to the fact that multiple boms might have the same `product_tmpl_id`, this approach might consider the same variants in the inner loop redundantly even though we matched the variant with a bom in a previous itration.

Worst case, this might result in a time complexity of $O(N * M)$ where N is the number of boms and M is the number of variants.

To improve the performance, I only considered the variants given in the paramater `products` and in addition to that, I created a new dictionary mapping a `product_tmpl_id` to its bom if the bom doesn't have a variant set. By doing this, I can loop over the `products` given and if it doesn't have a bom set then it will be set to the one its template had taken from the previos loop.

In a method call with the following constraints
    - **2** products the method was finding a bom for
    - The 2 products had the same template and the template contained **550** active variants
    - The boms were only related to the template rather than the variants themselves.

| Input Size | Before | After |
| :--- | :--- | :--- |
| 100 | 0.78s | 0.03s |
| 1000 | 8.53s | 0.11s |
| 10000 | 80.99s | 0.73s |

---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr

Forward-Port-Of: odoo/odoo#247465

### Issue before this commit:
When importing an XML file containing multiple business documents (multiple bodies with a single header), the system correctly split the file into separate attachments but failed to process any document beyond the first one.

### Steps to reproduce the issue:
1. Download Accounting and l10n_it
2. Go to Vendor -> Bills
3. Try to upload a xml with multiple bodies and one header
4. See only the first bill is correctly imported

### Cause of the issue:
The splitting logic renamed subsequent attachments with numeric suffixes but then this function incorrectly checked the name of the document.
https://github.com/odoo/odoo/blob/29805eec2b70144edf9441cffe7e69e39fd4ba0e/addons/l10n_it_edi/models/account_move.py#L297-L305
We can not rely only on the name of the document but we need to check also its content.
Refer to the rules for the name of the attachments: https://www.fatturapa.gov.it/export/documenti/Specifiche-tecniche-relative-al-Sistema-di-Interscambio-versione-1.8.4.pdf

In summary what we need in the document (page 9): 

> The unique progressive of the file is represented by an alphanumeric string up to 5 characters long and with allowed values. [az], [AZ], [0-9]. The unique progressive of the file has the sole purpose of differentiating the name of the files transmitted to the Interchange System by the same entity; it does not necessarily have to follow a strict progressive nature and may also present different numbering styles.

### Reason to introduce the fix:
This fix ensures that the function not only checks the name but also the content to be sure that the xml or p7m file contains a valid structure to be registered.

Ticket [link](https://www.odoo.com/odoo/project.task/6072258)
opw-6072258

Forward-Port-Of: odoo/odoo#265079
Forward-Port-Of: odoo/odoo#259887

Before this commit, the "Allow full order validation" were partially ignored in the Barcode app when used for Manufacturing Orders, and the "Mandatory scan" settings didn't work very well.

For example, setting the scan of lot/serial as mandatory didn't prevent the user to set automatically a SN on consummed component by generating a lot/serial on the produced product or by clicking on "Produce All" button.

This commit adds some conditions to avoid to update barcode lines in case they should depending of the config.

This commit also fixes a related issue where the MRP operation type's config wasn't used at all when a MO is created directly from the Barcode app.
As the config is get from the MO's picking type and no MO exists when a new one is created from the Barcode app, there is no MO's config returned in the data send by the server.
To fix that, the config is now updated clientside when the data are fetched after a save.

[Task-5420762](https://www.odoo.com/odoo/project/966/tasks/4655907/project.task/5420762)
[opw-5223507](https://www.odoo.com/odoo/project/49/tasks/5223507)

Forward-Port-Of: odoo/enterprise#113318

### Issue:
When importing EDI documents such as Peppol files, Swiss VAT numbers are often provided in a flat format (e.g., CHE530781296TVA), while existing Odoo partners usually store them in the official formatted version (e.g., CHE-530.781.296 TVA)

This mismatch prevents proper partner matching and may create duplicate partners during import

### Cause:
`_retrieve_partner` lacks Swiss-specific VAT normalization logic in `_import_retrieve_customer_from_vat()`
As a result, the matching process fails to:
- match formatted and unformatted Swiss VAT numbers
- properly handle language suffixes such as `TVA`, `MWST`, or `IVA`

If `base_vat` is installed, and the imported XML VAT is `CHE530781296TVA`, a new partner will be created with the structure format  `CHE-530.781.296 TVA`
As the match won't be made new partner will be created at each import

### Steps to reproduce:
- Install `account`
- Create a Vendor (Name: Test CH Vendor, Country: Switzerland, Tax ID: CHE-530.781.296 TVA)
- Import the bill [CH_bill_to_import.xml](https://github.com/user-attachments/files/27202997/CH_bill_to_import.xml) from the ticket 

Before the fix, the existing partner is not matched and a duplicate partner is created

opw-6072239

Forward-Port-Of: odoo/odoo#262011

__
## Short functional explanation of the error
While investigating for bug reported on ticket 6036064, I found this other bug. It only occurs when hr_attendance and
hr_work_entry_attendance are both installed.
When setting an attendance for an employee that has a check-in date but no check-out date, and running the scheduled action `Automatically check-out employees`, an `expected singleton` error occurs.

## Reproduction Steps
1. Install hr_work_entry_attendance.
2. Create an Employee. In the Payroll tab, set a start date for the contract. In the Settings tab, make sure their timezone is set to Brussels, and set the Overtime Ruleset field to Default Ruleset.
3. In Settings, check the Automatic Check-out box.
4. Go to Attendances. Create an attendance for the employee you just created. Set a Check-in date to 8 am on April 17th, for example, and leave the check-out field empty.
5. Open Scheduled Actions. Search the action Automatically check-out employees and click Run Manually.

### Expected behavior
The attendance check-out should be set at the end of April 17th.

### Unexpected behavior
An error occurs:
`Expected singleton: hr.attendance.overtime.line(39, 40)`

## Origin of the issue
When the attendance goes over several days, we set the check-out date to:
https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L618 This is a Naive date. However, it will later be considered as a UTC date. Because the employee's timezone is Brussels, this time will be transformed to 2 am next day when we retrieve attendance intervals. This will result in the creation of overtime entries for both days, causing the Expected Singleton error.
https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L687 https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L667-L672 In our case, `self.check_in` = April 17th at 06:00:00 and `self.check_out` = April 17th at 23:59:59. Converted, we will obtain April 17th at 08:00:00 and April 18th at 1:59:59.
Because of that, at the return:
https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L706-L709 We will return a dict containing 2 intervals: one for 17th April and one for 18th April.
We will then create overtime entries with such attendances: https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L333 leading to the creation of 2 different overtimes for the same attendance.
So, when we retrieve the overtime for that attendance: https://github.com/odoo/enterprise/blob/2d2056766441157dc45ebc37b677841c44e5c513/hr_work_entry_attendance/models/hr_version.py#L185, We get the 2. Thus when trying to access their status with: https://github.com/odoo/enterprise/blob/2d2056766441157dc45ebc37b677841c44e5c513/hr_work_entry_attendance/models/hr_version.py#L191 An Expected Singleton Occurs.
__
opw-6036064

Forward-Port-Of: odoo/odoo#262257

__
## Short functional explanation of the error
While investigating for bug reported on ticket 6036064, I found this other bug. It only occurs when hr_attendance and
hr_work_entry_attendance are both installed.
When setting an attendance for an employee that has a check-in date but no check-out date, and running the scheduled action `Automatically check-out employees`, an `expected singleton` error occurs.

## Reproduction Steps
1. Install hr_work_entry_attendance.
2. Create an Employee. In the Payroll tab, set a start date for the contract. In the Settings tab, make sure their timezone is set to Brussels, and set the Overtime Ruleset field to Default Ruleset.
3. In Settings, check the Automatic Check-out box.
4. Go to Attendances. Create an attendance for the employee you just created. Set a Check-in date to 8 am on April 17th, for example, and leave the check-out field empty.
5. Open Scheduled Actions. Search the action Automatically check-out employees and click Run Manually.

### Expected behavior
The attendance check-out should be set at the end of April 17th.

### Unexpected behavior
An error occurs:
`Expected singleton: hr.attendance.overtime.line(39, 40)`

## Origin of the issue
When the attendance goes over several days, we set the check-out date to:
https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L618 This is a Naive date. However, it will later be considered as a UTC date. Because the employee's timezone is Brussels, this time will be transformed to 2 am next day when we retrieve attendance intervals. This will result in the creation of overtime entries for both days, causing the Expected Singleton error.
https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L687 https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L667-L672 In our case, `self.check_in` = April 17th at 06:02:00 and `self.check_out` = April 17th at 23:59:59. Converted, we will obtain April 17th at 08:02:00 and April 18th at 1:59:59.
Because of that, at the return:
https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L706 We will return a dict containing 2 intervals: one for 17th April and one for 18th April.
We will then create overtime entries with such attendances: https://github.com/odoo/odoo/blob/b293ce50e0fc9355ffd233557f079916b514eac7/addons/hr_attendance/models/hr_attendance.py#L333 leading to the creation of 2 different overtimes for the same attendance.
So, when we retrieve the overtime for that attendance: https://github.com/odoo/enterprise/blob/2d2056766441157dc45ebc37b677841c44e5c513/hr_work_entry_attendance/models/hr_version.py#L185, We get the 2. Thus when trying to access their status with: https://github.com/odoo/enterprise/blob/2d2056766441157dc45ebc37b677841c44e5c513/hr_work_entry_attendance/models/hr_version.py#L191 An Expected Singleton Occurs.
__
opw-6036064

Forward-Port-Of: odoo/enterprise#115828

### Description of the issue/feature this PR addresses:

Some stock queries determine whether a location belongs to the subtree of a set of locations by checking the parent_path prefix against candidate parent locations. This is done using a correlated EXISTS subquery with a LIKE parent.parent_path || '%' condition.

When the list of candidate locations becomes large (for example tens or hundreds of thousands of ids), this approach causes extremely poor performance because the database must repeatedly compare hierarchical path strings for every candidate row.

This PR improves the performance of this ancestry check by replacing the string prefix comparison with a direct check on the ancestor ids contained in parent_path.

### Current behavior before PR:

The query determines whether a location belongs to the subtree of one of the provided locations using:

location.parent_path LIKE parent.parent_path || '%'

For each row, PostgreSQL must evaluate a correlated subquery against all candidate parent locations. Because this relies on string prefix comparisons on parent_path, when the location list is large, this results in extremely slow queries.

### Desired behavior after PR is merged:

Instead of performing string prefix comparisons, the query extracts the ancestor ids directly from parent_path.

The path is:

1. Trimmed to remove leading and trailing /
2. Split into an array of ancestor ids
3. Expanded using unnest
4. Checked for intersection with the provided location ids

This converts the ancestry check from repeated string comparisons into a simple integer membership check.

### Benchmarks

Comparing performance of old subquery:

```
SELECT stock_location_inner.id 
FROM stock_location AS stock_location_inner 
WHERE EXISTS (
    SELECT 1
    FROM stock_location parent
	WHERE parent.id IN (long list)
		AND stock_location_inner.parent_path LIKE parent.parent_path || '%%'
);
```

to new one:

```
SELECT stock_location_inner.id 
FROM stock_location AS stock_location_inner 
WHERE EXISTS (
    SELECT 1
    FROM unnest(
        string_to_array(trim(both '/' FROM stock_location_inner.parent_path), '/')::int[]
    ) AS path_id(id)
	WHERE path_id.id IN (long list)
);
```

Depending on the number of elements in 'long list'

| # of elements | Before | After |
| --- |---|---|
| 130,000 | 21min | 0.8sec |  
| 10,000 | 95sec | 0.5sec |  
| 1,000 | 10.5sec | 0.5sec |  

In practice, on the reference ticket this causes the "Validate" button on a stock picking to go from timing out to taking 8 seconds. 

### Reference

opw-5932436

---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr

Forward-Port-Of: odoo/odoo#254245

Customers placing an order without logging in and with the "Pick up in store" delivery method are not notified when the delivery is confirmed

1. Install eCommerce and Sales
2. Go to Settings > Website > Delivery and enable "Click & Collect"
3. Go to Settings > Inventory > Shipping and enable "Email Confirmation"
4. Go to Website > Configuration > Payment Providers and Install Demo
5. Go to Website > Configuration > Delivery Methods and open "Pick up in store", set YourCompany as warehouse and publish it
6. Go to Sales > Products, open product "Office Lamp", click on "Update Quantity" in the status bar and add 5 units
7. Log out
8. Go to the shop, add product "Office Lamp" to the cart and checkout
9. Fill in the address form and continue checkout
10. Select "Pick up in store" as delivery method and select a location
11. Confirm the order and pay with Demo
12. As user Mitchell Admin, go to Sales, remove the default filter and open the newly created sale order
13. Open the related delivery with the smart button and validate it
14. No delivery order confirmation was sent to the customer (check emails)

Issue:
Confirming an order with a "Pick up in store" delivery method replaces the `partner_shipping_id` of the sale order with an archived partner
https://github.com/odoo/odoo/blob/d73e5662a0af7c549008661f743ba5d51f765339/addons/delivery/models/sale_order.py#L178-L192
which updates the `partner_id` of the related `stock.picking` with the archived partner
https://github.com/odoo/odoo/blob/d73e5662a0af7c549008661f743ba5d51f765339/addons/sale_stock/models/sale_order.py#L130-L132
This will unsubscribe the old `partner_id` on the `stock.picking` and try to subscribe the archived partner
https://github.com/odoo/odoo/blob/d73e5662a0af7c549008661f743ba5d51f765339/addons/stock/models/stock_picking.py#L1120-L1125
Because the partner we want to subscribe is archived, he will be filtered out and the subscribe action will have no effect, preventing him from receiving the delivery confirmation
https://github.com/odoo/odoo/blob/d73e5662a0af7c549008661f743ba5d51f765339/addons/mail/models/mail_thread.py#L4367-L4369

Solution:
Subscribe the parent of the archived partner when we write a `partner_id` on pickings with "in_store" `delivery_type`. This ensures the unarchived partner is subscribed to the picking allowing him to receive the mail confirmation.

opw-6095396

Forward-Port-Of: odoo/odoo#265392
Forward-Port-Of: odoo/odoo#263005