22,209
社区成员
发帖
与我相关
我的任务
分享
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: 服务维修用件明细
-- =============================================
ALTER PROCEDURE [dbo].[up_Service_TSI_Part_Detail]
@service_report_no NVARCHAR(20),
@service_order_no NVARCHAR(20),
@borrow_no NVARCHAR(20),
@dealer_id INT,
--@product_id INT,
@model_id INT,
@serial_no NVARCHAR(20),
@part_no NVARCHAR(20),
@part_no_new NVARCHAR(20),
@create_date_start NVARCHAR(10),
@create_date_end NVARCHAR(10),
@borrow_part_seq_id INT,
@userId INT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY A.create_date DESC ) AS row_num ,
A.service_report_id ,
A.service_report_no ,
A.create_date ,
B.service_order_id ,
B.service_order_no ,
C.borrow_part_seq_id ,
C.borrow_no ,
-- B.product_id ,
-- B.product_name,
B.model_id ,
B.model_name ,
B.serial_no ,
B.dealer_id ,
B.dealer_name,
B.cust_name ,
B.service_start_date ,
B.service_end_date ,
C.part_no ,
C.part_name ,
C.supplier_id ,
D.supplier_no AS supplier_name ,
C.steel_grade_no ,
C.qty ,
C.part_no_new ,
C.part_name_new ,
C.supplier_id_new ,
E.supplier_no AS supplier_name_new ,
C.steel_grade_no_new ,
C.locale_dispose_qty ,
C.return_qty ,
C.scrap_value_qty ,
C.return_place_name ,
C.remark ,
C.borrow_part_seq_id
FROM dbo.Service_Tsi_Report A
INNER JOIN dbo.View_Service_Order B ON A.service_order_id = B.service_order_id
INNER JOIN dbo.View_Service_TSI_Report_Part_Detail C ON A.service_report_id = C.service_report_id
LEFT JOIN dbo.Comm_Supplier D ON C.supplier_id=D.supplier_id
LEFT JOIN dbo.Comm_Supplier E ON C.supplier_id_new=E.supplier_id
WHERE A.service_report_no LIKE ISNULL(@service_report_no,'')+'%' AND B.service_order_no LIKE ISNULL(@service_order_no,'')+'%'
AND C.borrow_no LIKE ISNULL(@borrow_no,'')+'%' AND (B.dealer_id = @dealer_id OR ISNULL(@dealer_id,0)=0)
-- AND (B.product_id =@product_id OR ISNULL(@product_id,0)=0)
AND (B.model_id=@model_id OR ISNULL(@model_id,0)=0)
AND B.serial_no LIKE ISNULL(@serial_no,'')+'%' AND C.part_no LIKE ISNULL(@part_no,'')+'%'
AND C.part_no_new LIKE ISNULL(@part_no_new,'')+'%'
AND CONVERT(NVARCHAR(10),A.create_date,120) BETWEEN @create_date_start AND @create_date_end
AND (C.borrow_part_seq_id=@borrow_part_seq_id OR ISNULL(@borrow_part_seq_id,0)=0)
AND EXISTS(SELECT 1 FROM dbo.Comm_System_User_Dealer WHERE user_id=@userId AND dealer_id=A.dealer_id)
ORDER BY A.create_date DESC
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO