string(14788) " IF OBJECT_ID('tempdb..#tmp_stock') IS NOT NULL DROP TABLE #tmp_stock CREATE TABLE #tmp_stock ( [row_no] [bigint] NULL, [Stock_Id] [int] NOT NULL, [Auction_Country_Id] [int] NULL, [M3] [numeric](13, 3) NULL, [Bodystyle_Id] [int] NOT NULL, [Altered_Stock_Status_Id] [int] NOT NULL, [Altered_Nd_Status_Id] [int] NOT NULL, [inspectionPassed] [bit] NULL, [fixed_destination_country_id] [int] NULL, [inspectionNg] [bit] NULL ) INSERT INTO #tmp_stock SELECT row_no = ROW_NUMBER() OVER ( ORDER BY CASE WHEN s.Is_ND = 1 THEN CASE WHEN s.NdCountryId = 95 AND s.ND_Status_Id = 1 THEN 2 ELSE s.ND_Status_Id END ELSE CASE WHEN hs.stock_id IS NULL AND s.country_hold_flg = 1 AND s.Stock_Status_Id = 1 THEN 2 ELSE s.Stock_Status_Id END END, CASE WHEN COALESCE(s.Photos, 0) = 0 THEN 1 ELSE 0 END, CASE WHEN s.fixed_destination_country_id IS NULL AND ( (s.Is_ND = 1 AND s.ndCountryId = 85) OR s.For_Country_Id IN (7,15,53,202) ) THEN 0 ELSE 1 END,s.SalesKbn, s.IsDealerStock, CASE WHEN s.is_sbt_auction = 1 THEN s.auc_current_price_$ WHEN ing.Stock_Id IS NOT NULL THEN 99999999 ELSE CASE WHEN price.roro_usd IS NOT NULL THEN price.roro_usd ELSE CASE WHEN s.IsDealerStock = 1 THEN s.FOB_Latest_$ ELSE 99999999 END END END ASC, s.Purchase_Date, s.RND_Key) ,s.Stock_Id ,s.Auction_Country_Id ,s.M3 ,s.Bodystyle_Id ,CASE WHEN hs.stock_id IS NULL AND s.country_hold_flg = 1 AND s.Stock_Status_Id = 1 THEN 2 ELSE s.Stock_Status_Id END AS Altered_Stock_Status_Id ,CASE WHEN s.NdCountryId = 95 AND s.ND_Status_Id = 1 THEN 2 ELSE s.ND_Status_Id END AS Altered_ND_Status_Id ,s.is_inspection_passed AS inspectionPassed ,s.fixed_destination_country_id ,CASE WHEN ing.Stock_Id IS NOT NULL THEN 1 ELSE 0 END AS inspectionNg FROM [SCM].[dbo].[Inv_Stock_Web] s WITH(NOLOCK) LEFT JOIN [WEB].[dbo].[t_dest_base_price] price WITH (NOLOCK) ON s.Stock_Id = price.stock_id AND price.dest_country_id = 0 AND price.dest_port_id = 399 LEFT JOIN [WEB].[dbo].[t_inspection_ng] ing WITH(NOLOCK) ON s.Stock_Id = ing.stock_id AND ing.country_id = 0 LEFT JOIN [WEB].[dbo].[t_country_holding_stocks] hs WITH(NOLOCK) ON s.Stock_Id = hs.stock_id AND hs.holding_country = 0 WHERE s.Auction_Country_Id IN (111) AND ISNULL(s.NdCountryId, 0) NOT IN (93, 137) AND s.DealerBelongCode <> 10280799 SELECT t_stock.* ,stock.RND_Key ,COALESCE(stock.KM1, 0) * 1000 AS KM ,stock.mileage_type ,stock.For_Country_Id ,stock.For_Country_Name ,stock.Attr_Make_Name + ' ' + stock.Attr_Car_Name AS ItemName ,stock.Auction_Country_Name AS AuctionCountry ,CASE stock.Is_LHD WHEN 1 THEN 'LHD' ELSE 'RHD' END AS Handle ,CASE WHEN CHARINDEX('-', stock.Chassis_No) > 0 THEN COALESCE(SUBSTRING(stock.Chassis_No, 1, CHARINDEX('-', stock.Chassis_No)-1), '') ELSE SUBSTRING(stock.Chassis_No,1,6) END AS Chassis_No ,CASE WHEN (stock.Ship_Invoice_No IS NULL AND stock.ND_Status_Id = 1) THEN 1 ELSE t_stock.Altered_Stock_Status_Id END AS Stock_Status_Id ,CASE WHEN stock.Is_Paid = 0 AND stock.Is_Ship_OK = 0 THEN 0 ELSE 1 END AS Full_Chassis_No ,stock.Attr_BodyStyle AS BodyStyle ,stock.Attr_Accessory_Code AS Accessory_Code ,stock.Attr_Drive AS Drive ,stock.Attr_Accessory_Description AS Accessory_Description ,stock.displacement AS CC ,CASE WHEN stock.Grade IS NULL THEN '' WHEN RTRIM(LTRIM(stock.Grade)) = '' THEN '' ELSE stock.Grade END AS Grade ,COALESCE(REPLACE(REPLACE(stock.Condition,'accident',''),'no accident',''), '') AS Auction_Grade ,COALESCE(stock.Port_Name, '') AS Port_Name ,stock.Attr_Fuel AS Fuel ,stock.Fuel_Id ,CASE WHEN stock.Registration_Year = 0 THEN NULL ELSE stock.Registration_Year END AS Registration_Year ,stock.Registration_Month ,CASE WHEN stock.Registration_Year = 0 THEN '' ELSE CAST(stock.Registration_Year AS varchar(4)) + CASE WHEN stock.Registration_Month IS NOT NULL AND stock.Registration_Month <> 0 THEN '/' + CAST(stock.Registration_Month as varchar(2)) ELSE '' END END AS registrationYearMonthforDisplay ,stock.Manufacture_Year ,stock.Attr_Transmission AS Transmission ,stock.Attr_Color AS Color ,stock.Engine_Type ,scm.dbo.convert_disp_model(stock.Model) AS Model ,stock.Seat ,stock.Clearance_Sale_Status_Id as SaleId ,stock.Attr_Make_Name AS Make_Name ,stock.Attr_Car_Name AS Car_Name ,stock.Purchase_Date ,stock.Door ,stock.BidUserCode ,stock.Is_ND ,stock.IsSpecialPurpose ,stock.IsGuaranteedCar ,stock.Max_Load ,stock.Options ,stock.bp_sell_price_kbn ,stock.is_leave_port ,CASE WHEN stock.Is_ND = 1 AND stock.NdCountryId = 93 THEN 'JPY' ELSE _currency.currency_iso END AS Currency_Code ,CASE WHEN stock.Is_ND = 1 AND stock.NdCountryId = 93 THEN FOB_Price_Y ELSE FOB_Price_$ END AS OriginalPrice ,stock.comment ,t_stock.Altered_ND_Status_Id AS ND_Status_Id ,CASE WHEN (stock.Is_In_Yard = 0 OR stock.Photos < 7) AND stock.Is_ND <> 1 AND stock.IsDealerStock <> 1 AND stock.Auction_Country_Id = 36 THEN 0 ELSE 1 END AS displayPhotoFlg ,FOB_Latest_Y AS FOB_Latest_Y ,FOB_Latest_$ AS FOB_Latest_$ ,FOB_Latest_GBP AS FOB_Latest_GBP ,FOB_Latest_EUR AS FOB_Latest_EUR ,CASE WHEN stock.Is_ND = 1 AND stock.NdCountryId = 93 THEN CASE WHEN price.roro_jpy IS NOT NULL THEN CASE WHEN stock.FOB_Price_Y > price.roro_jpy THEN CAST(ABS(((stock.FOB_Price_Y - price.roro_jpy) / stock.FOB_Price_Y) * 100) AS NUMERIC(18,2)) ELSE 0 END ELSE 0 END ELSE CASE WHEN price.roro_usd IS NOT NULL THEN CASE WHEN stock.FOB_Price_$ > price.roro_usd THEN CAST(ABS(((stock.FOB_Price_$ - price.roro_usd) / stock.FOB_Price_$) * 100) AS NUMERIC(18,2)) ELSE 0 END ELSE CASE WHEN stock.IsDealerStock = 1 AND stock.FOB_Difference_$ > 0 THEN CAST(ABS((stock.FOB_Difference_$ / (stock.FOB_Highest_$) * 100)) AS NUMERIC(18,2)) ELSE 0 END END END AS Saving ,CASE WHEN stock.Is_ND = 1 AND stock.NdCountryId = 93 THEN CASE WHEN price.roro_jpy IS NOT NULL THEN price.roro_jpy ELSE 0 END ELSE CASE WHEN price.roro_usd IS NOT NULL THEN price.roro_usd ELSE CASE WHEN stock.IsDealerStock = 1 THEN stock.FOB_Latest_$ ELSE 0 END END END AS CurrentPrice ,0 AS CollectOnly ,CASE WHEN stock.IsDealerStock = 1 THEN 'https://srv1.sbtjapan.com/dealercarphoto/' + stock.RND_Key + '/thumbnail/f.jpg' ELSE 'https://srv1.sbtjapan.com/photo/' + SUBSTRING(stock.RND_Key, 2, 1) + '0000/' + SUBSTRING(stock.RND_Key, 3, 1) + '000/' + stock.RND_Key + '/thumbnail/f.jpg' END AS ItemImageUrl ,stock.DealerBelongCode as BelongCode ,stock.IsDealerStock ,stock.SalesKbn ,stock.CurrentFlg ,stock.CloseSchedule ,stock.StartJpy ,stock.distance_kbn ,stock.is_sbt_auction ,stock.vr_flg ,ts.cTime as TimeKey ,stock.high_trading_car_flg ,saleCountry.t_sale_stock_id AS country_sale_id , 0 AS Notification_flg /* ,CASE WHEN 0 = 1 AND 0 = 4 AND t_stock.Bodystyle_Id = 9 THEN 800 WHEN 0 = 1 THEN COALESCE(scp.Inspection_Charge, 0) ELSE 0 END AS Inspection ,CASE WHEN 0 = 0 THEN 0 WHEN 0 = 0 THEN 0 ELSE scc.Vanning_Charge END AS Vanning , AS Insurance ,CASE WHEN 1 = 0 THEN ' FOB ' ELSE ' CFR ' END + CASE WHEN 0 != 0 AND scc.Vanning_Charge > 0 THEN ' +Vanning' ELSE '' END + CASE WHEN > 0 AND != 4 THEN ' +Protection program' ELSE '' END + CASE WHEN > 0 AND = 4 THEN ' +Insurance' ELSE '' END + CASE WHEN 0 != 0 THEN ' +Inspect' ELSE '' END AS Charges_Type ,COALESCE(stock.FOB_Latest_$, 0) + COALESCE(nd.freight, 0) + COALESCE(nd.inspection, 0) + COALESCE(nd.[insurance], 0) + COALESCE(nd.[vanning], 0) + COALESCE(nd.other_charges, 0) AS Total_Amount ,COALESCE(nd.inspection, 0) + COALESCE(nd.[insurance], 0) + COALESCE(nd.[vanning], 0) + COALESCE(nd.other_charges, 0) AS nd_other_cost */ ,(SELECT MAX(row_no) FROM #tmp_stock) AS Record_Count ,(SELECT MAX(row_no) FROM #tmp_stock) / 25 AS Page_Count ,stock.NdCountryId AS ndCustomerCountryId ,nd.dest_port_name AS Destination_Port_Name ,nd.dest_country_name AS Destination_Country_Name ,nd.dest_port_id AS Destination_Port ,nd.schedule_id AS Schedule_Id ,nd.ship_id AS Ship_Id ,nd.ship_name AS Ship_Name ,COALESCE(nd.other_charges, 0) AS Other_Charges ,ship_date AS Ship_Date ,ship_arrival_date AS Ship_Arrival_Date ,CASE WHEN ir.country_id IS NOT NULL THEN ir.country_id ELSE 0 END AS inspectionRecommendCountry ,sbt_auc.result_status AS sbt_auction_result_status ,sbt_auc.start_date AS sbt_auction_start_date ,sbt_auc.close_date AS sbt_auction_close_date ,sbt_auc.currency_code AS sbt_auction_currency ,sbt_auc.bid_unit AS sbt_auction_bid_unit ,sbt_auc.start_price AS sbt_auction_start_price ,sbt_auc.decision_price AS sbt_auction_decision_price ,CASE WHEN sbt_auc.current_price IS NULL THEN sbt_auc.start_price ELSE sbt_auc.current_price END AS sbt_auction_current_price FROM #tmp_stock t_stock LEFT JOIN [WEB].[dbo].[T015_photoOptimizeLog] ts WITH(NOLOCK) ON t_stock.Stock_Id = ts.stockId LEFT JOIN [WEB].[dbo].[m_currency] _currency WITH(NOLOCK) ON _currency.currency_id = 2 LEFT JOIN [WEB].[dbo].[t_sbt_auction] sbt_auc WITH(NOLOCK) ON t_stock.Stock_Id = sbt_auc.stock_id LEFT JOIN [WEB].[dbo].[t_sale_stock] saleCountry WITH(NOLOCK) ON saleCountry.stock_id = t_stock.Stock_Id AND saleCountry.country_id = 85 /* LEFT JOIN [SAM].[dbo].[Set_Country_Charge] scp WITH(NOLOCK) ON scp.Destination_Country_Id = 0 AND scp.Country_Id = t_stock.Auction_Country_Id AND scp.Destination_Country_Id != t_stock.Auction_Country_Id LEFT JOIN [SAM].[dbo].[Set_Country_Charge] scc WITH(NOLOCK) ON scc.Destination_Country_Id = 0 AND scc.Country_Id = t_stock.Auction_Country_Id AND scc.Destination_Country_Id != t_stock.Auction_Country_Id */ LEFT JOIN [SCM].[dbo].[Inv_Stock_Web] stock WITH(NOLOCK) ON t_stock.Stock_Id = stock.Stock_Id LEFT JOIN [WEB].[dbo].[t_nd_stock_info] nd WITH(NOLOCK) ON t_stock.Stock_Id = nd.stock_Id LEFT JOIN [WEB].[dbo].[t_dest_base_price] price WITH (NOLOCK) ON t_stock.Stock_Id = price.stock_id AND price.dest_country_id = CASE WHEN stock.is_nd = 1 AND stock.ndCountryId <> 7 THEN stock.ndCountryId ELSE 0 END AND price.dest_port_id = CASE WHEN stock.is_nd = 1 AND stock.ndCountryId <> 7 THEN nd.dest_port_id ELSE 399 END LEFT JOIN [WEB].[dbo].[t_inspection_recommend] ir WITH(NOLOCK) ON t_stock.Stock_Id = ir.stock_id AND ir.country_id = 0 WHERE row_no BETWEEN 1 AND 25 ORDER BY row_no DROP TABLE #tmp_stock " Японские Подержанные Авто Доступные По Всему Миру - SBT JAPAN
Back to Top