Why does SQL Server say it can't convert varchar to numeric?
A conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. As to whether or not this actually happens depends on the order of evaluation in the execution plan.

Below is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated:

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'  


The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed:

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)


Although the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. but it would be better to fix the data model such as to ensure only like or compatible data types are compared.