PiolotTH_ReportFrom/派诺报表SQL文件/订单未发货明细表插入数据SQL.sql

CREATE PROCEDURE GZTH_DDWFHMXB  
     @FSDate nvarchar(1000),  
     @FEDate nvarchar(1000),  
     @FSaleOrgId nvarchar(1000)  
AS  
BEGIN  
    DECLARE @ConvertedDate DATE;  
  
    SET @ConvertedDate = CONVERT(DATE, @FEDate, 120);  
    -- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϼ<EFBFBD>һ<EFBFBD><D2BB>  
    SET @ConvertedDate = DATEADD(DAY, 1, @ConvertedDate);  
  
    TRUNCATE TABLE GZ_DDWFHMX_TH  
  
    SELECT   
    A.FID AS FID,  
    AE.FENTRYID AS FENTRYID,  
    A.FApproveDate AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    B.FNAME AS '<EFBFBD>Ƶ<EFBFBD><EFBFBD><EFBFBD>',  
    ACG.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    G.FNAME AS 'ҵ<EFBFBD><EFBFBD>Ա',  
    A.F_contractnumber AS '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD>',  
    F.FNAME AS '<EFBFBD>ͻ<EFBFBD>',  
    H.FNAME AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD>',  
    WL.FNUMBER AS '<EFBFBD><EFBFBD><EFBFBD>ϱ<EFBFBD><EFBFBD><EFBFBD>',  
    WL_L.FNAME AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    WL_L.FSpecification AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͺ<EFBFBD>',  
    AE.FQTY AS  '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    AE_F.FTaxPrice AS '<EFBFBD><EFBFBD>˰<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    AE_F.FALLAMOUNT_LC AS '<EFBFBD><EFBFBD>˰<EFBFBD>ϼ<EFBFBD>',  
    HT_L.FDATAVALUE AS '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    A.F_Deliveryconditions AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'  
    INTO #TEMP1  
    FROM T_SAL_ORDER A  
    INNER JOIN T_SEC_USER B ON A.FCreatorId = B.FUSERID --<EFBFBD>û<EFBFBD><EFBFBD><EFBFBD>  
    INNER JOIN V_BD_SALESMAN_L G ON G.FID = A.FSALERID -- Ա<><D4B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>                                                      
    LEFT JOIN  ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACG ON ACG.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>=G.FNAME --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>  
    INNER JOIN T_BD_CUSTOMER_L F ON F.FCUSTID = A.FCustId --<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD>  
    INNER JOIN T_BAS_PREBDONE_L H ON H.FID = A.F_projectname --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD>Ʊ<EFBFBD>  
    INNER JOIN T_SAL_ORDERENTRY AE ON AE.FID = A.FID --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD>  
    INNER JOIN T_BD_MATERIAL WL ON WL.FMATERIALID = AE.FMaterialId --<EFBFBD><EFBFBD><EFBFBD>ϱ<EFBFBD>  
    INNER JOIN T_BD_MATERIAL_L WL_L ON WL_L.FMATERIALID = WL.FMATERIALID --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>_L<EFBFBD><EFBFBD>  
    INNER JOIN T_SAL_ORDERENTRY_F AE_F ON AE_F.FENTRYID = AE.FENTRYID --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϸ-<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>  
    INNER JOIN T_BAS_ASSISTANTDATAENTRY_L HT_L On A.F_Ordercategory = HT_L.FENTRYID --<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD>͸<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>_L<EFBFBD><EFBFBD>  
    WHERE A.FDocumentStatus = 'C'  
    AND (ISNULL(@FSaleOrgId,'') = '' OR A.FSaleOrgId = @FSaleOrgId)  
    AND (ISNULL(@FSDate,'') = '' OR A.FAPPROVEDATE >= @FSDate)  
    AND (ISNULL(@FEDate,'') = '' OR A.FAPPROVEDATE < @ConvertedDate)  
  
    INSERT INTO GZ_DDWFHMX_TH([FId],[FENTRYID],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD>Ƶ<EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[ҵ<EFBFBD><EFBFBD>Ա],[<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD>],[<EFBFBD>ͻ<EFBFBD>],[<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD>],  
    [<EFBFBD><EFBFBD><EFBFBD>ϱ<EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͺ<EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD>˰<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD>˰<EFBFBD>ϼ<EFBFBD>],[<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>])  
    SELECT * FROM #TEMP1  
  
  
    SELECT   
    E.FID AS FID,  
    D.FENTRYID AS FENTRYID,  
    SUM(B.FQTY) AS '<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',  
    SUM(BF.FAllAmount_LC) AS '<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'  
    INTO #TEMP2  
    FROM T_SAL_DELIVERYNOTICE A  
    INNER JOIN T_SAL_DELIVERYNOTICEENTRY B ON A.FID = B.FID  
    INNER JOIN T_SAL_DELIVERYNOTICEENTRY_F BF ON BF.FENTRYID = B.FENTRYID  
    INNER JOIN T_SAL_DELIVERYNOTICEENTRY_LK C ON B.FENTRYID = C.FENTRYID AND C.FSTABLENAME = 'T_SAL_ORDERENTRY'  
    INNEr JOIN T_SAL_ORDERENTRY D ON C.FSID = D.FENTRYID  
    INNER JOIN T_SAL_ORDER E ON D.FID = E.FID  
    WHERE   
    A.FDOCUMENTSTATUS = 'C'  
    AND (ISNULL(@FSaleOrgId,'') = '' OR E.FSaleOrgId = @FSaleOrgId)  
    AND(ISNULL(@FSDate,'') = '' OR E.FAPPROVEDATE >= @FSDate)  
    AND (ISNULL(@FEDate,'') = '' OR E.FAPPROVEDATE <= @ConvertedDate)  
    GROUP BY E.FID,D.FENTRYID  
  
    UPDATE A  
    SET A.[<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] = B.[<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],A.[<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] = B.[<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]  
    FROM GZ_DDWFHMX_TH A  
    INNER JOIN #TEMP2 B ON A.FId = B.FID AND A.FENTRYID = B.FENTRYID  
  
    UPDATE A  
    SET A.[δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] = ISNULL(A.[<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],0) - ISNULL(A.[<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],0),A.[δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] = B.FALLAMOUNT_LC - ISNULL(A.[<EFBFBD>ѷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],0)  
    FROM GZ_DDWFHMX_TH A  
    INNER JOIN T_SAL_ORDERENTRY_F B ON A.FENTRYID = B.FENTRYID  
  
    DROP TABLE  #TEMP1,#TEMP2  
END;