59 lines
4.0 KiB
MySQL
59 lines
4.0 KiB
MySQL
|
CREATE PROCEDURE GZ_KNBGHT_LTH
|
|||
|
|
@FSaleOrgIdName varchar(1000),
|
|||
|
|
@BGFStartDate varchar(1000),
|
|||
|
|
@BGFEndDate varchar(1000)
|
|||
|
|
AS
|
|||
|
|
BEGIN
|
|||
|
|
DECLARE @FSaleOrgId nvarchar(1000);
|
|||
|
|
|
|||
|
|
SELECT @FSaleOrgId = FORGID
|
|||
|
|
FROM T_ORG_ORGANIZATIONS_L WHERE
|
|||
|
|
FNAME = @FSaleOrgIdName
|
|||
|
|
|
|||
|
|
SELECT
|
|||
|
|
A.FID
|
|||
|
|
,FORMAT(A.FChangeDate,'yyyy-MM-dd') AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,FORMAT(A.FChangeDate,'MM') AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>·<EFBFBD>'
|
|||
|
|
,FORMAT(A.FDATE,'yyyy') AS 'ԭ<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,A.F_contractnumber AS '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,KH_L.FNAME AS '<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,GC_L.FNAME AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,CASE WHEN ISNULL(O.FNAME,'')='' THEN XS_L.FNAME
|
|||
|
|
ELSE O.FNAME END AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա'
|
|||
|
|
,CAST(A.F_Amountstar AS DECIMAL(20, 2)) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ǰ<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,CAST(AN.FBillAllAmount_LC AS DECIMAL(20, 2)) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,CAST(AN.FBillAllAmount_LC - A.F_Amountstar AS DECIMAL(20, 2)) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,A.FChangeReason AS '<EFBFBD><EFBFBD>ע'
|
|||
|
|
,TU.FNAME AS '֪ͨ<EFBFBD><EFBFBD>Ա'
|
|||
|
|
,CASE WHEN ISNULL(O.FNAME,'')='' THEN '' ELSE CONCAT( CAST ( N.FSHARERATE AS REAL),'%') END AS '<EFBFBD><EFBFBD>̯<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,CASE WHEN ISNULL(O.FNAME,'')='' THEN ACL.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> ELSE ACO.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> END AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
,HT_L.FDATAVALUE AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
INTO #TEMP1
|
|||
|
|
FROM T_SAL_ORDER A
|
|||
|
|
LEFT JOIN T_SAL_ORDERFIN AN ON A.FID = AN.FID --<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>
|
|||
|
|
LEFT JOIN T_BAS_PREBDONE GC ON A.F_projectname = GC.FID
|
|||
|
|
LEFT JOIN T_BAS_PREBDONE_L GC_L ON GC_L.FID = GC.FID --<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>
|
|||
|
|
LEFT JOIN T_BD_CUSTOMER KH ON A.FCUSTID = KH.FCUSTID
|
|||
|
|
LEFT JOIN T_BD_CUSTOMER_L KH_L ON KH_L.FCUSTID = KH.FCUSTID --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ģ<EFBFBD>ͣ<EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
LEFT JOIN V_BD_SALESMAN XS ON XS.fid = A.FSalerId
|
|||
|
|
LEFT JOIN V_BD_SALESMAN_L XS_L ON XS_L.fid = XS.fid --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ģ<EFBFBD>ͣ<EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
INNER JOIN T_SEC_USER TU ON A.FChangerId = TU.FUSERID -- <20>û<EFBFBD><C3BB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,<2C><>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
left JOIN T_PerformanceSharing M ON M.FSOURCEBILLNO = A.FBILLNO AND M.FDOCUMENTSTATUS = 'C'
|
|||
|
|
left JOIN T_PerformanceSharingEntry N ON N.FID=M.FID --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>̯<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD>̯<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
LEFT JOIN V_BD_SALESMAN_L O ON O.fid= N.FSALEID --<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>
|
|||
|
|
LEFT JOIN ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACO ON ACO.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>=O.FNAME AND ISNULL(ACO.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,'') != ''
|
|||
|
|
LEFT JOIN ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACL ON ACL.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>=XS_L.FNAME AND ISNULL(ACL.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,'') != ''
|
|||
|
|
LEFT JOIN T_BAS_ASSISTANTDATAENTRY HT ON HT.FENTRYID = A.F_Ordercategory
|
|||
|
|
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L HT_L ON HT_L.FENTRYID = HT.FENTRYID
|
|||
|
|
WHERE
|
|||
|
|
(@FSaleOrgIdName = '' OR A.FSALEORGID = @FSaleOrgId)
|
|||
|
|
AND (ISNULL(@BGFStartDate,'') = '' OR FORMAT(A.FChangeDate,'yyyy-MM-dd') >= @BGFStartDate)
|
|||
|
|
AND (ISNULL(@BGFEndDate,'') = '' OR FORMAT(A.FChangeDate,'yyyy-MM-dd') <= @BGFEndDate)
|
|||
|
|
--AND A.FChangeDate IS NOT NULL
|
|||
|
|
AND FORMAT(A.FDATE,'yyyy') < FORMAT(A.FChangeDate,'yyyy')
|
|||
|
|
AND TU.FNAME NOT LIKE 'ERP%' AND TU.FNAME NOT LIKE '13570668909'
|
|||
|
|
|
|||
|
|
|
|||
|
|
SELECT * FROM #TEMP1
|
|||
|
|
DROP TABLE #TEMP1
|
|||
|
|
END;
|