Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
No join predicate has been detected <<
That is fine; you will get a CROSS JOIN or Cartesian product. But this is usually an error known as a Cartesian explosion.
What little posted is a total mess of vague generic data elements names with irregular capitalization to make it a bitch to read.
There is no such crap as generic id in RDBMS! But old COBOL and Autocoder programmers use it for IDENTITY to get a physical rerecord number replacement.
I also see that you put the search conditions in redundant parentheses, so you must be an old Assembly language programmer. No SQL programmer writes code like that.
There is no “category_id” in RDBMS; an attribute can be “product_category” or “product_id”, but not a hybrid of two attribute properties! This was the first week of any class on data modeling!
But it gets worse. Your “V_Product_category_new” looks like the “V_” means VIEW, but we do not put meta-data in a table name. The postfix “new” looks like a role this view plays in the data model. But that would be a prefix in ISO-11179 naming conventions. That would get us “New_Product_Categories” as a table. But since a category is an attribute, would be a simple look up table or a column constraint
“CHECK (product_category IN (..))” in a valid schema design.
The rest of this awful. I will guess that your “language” and “country” are supposed to be the ISO Standard language_code and country_code. But who knows? I see that when you use it as a parmeter, it is NVARCHAR(2) in the improperly named “PROCEDURE dbo.SP_Product_Invoice”, so perhaps I guess right. But wait! The ISO country code is fixed length and uses only the ISO Latin subset that all the UNICODE languages have to support. Why did you waste space and throw away data integrity? Do you know that the “sp_” prefix on names means in T-SQL? Google it! What does this procedure do? Where is the verb? Print? Update? Delete?
I often think that there is a “screwup_” procedure in accounting systems:confused:
Finally, we do not math on product or order identifiers, so they are never numeric. The products ought to use a GRIN, EAN, UPC or other industry standard.
The function "PROBE" on column [dev_shayana].[dbo].[Order_Detail].[order_id] may be causing a table scan <<
Where is that function? Why do you have only one order detail? Oh, more 1970's COBOL programming with singular file names.
SQL: (huge mess probably) <<
No, it is certain. Might be time to update the resume and leave the sinking ship 
Sorry.