2025-03-14 10:00:24 +08:00
|
|
|
|
ALTER VIEW [dbo].[V_SXJD_Table1]
|
|
|
|
|
|
AS
|
|
|
|
|
|
Select * From
|
|
|
|
|
|
(Select R1.FSBILLID,STRING_AGG(R1.FNAME,',') as 'FName',SUM(R1.FALLAMOUNT_LC) as 'FALLAMOUNT_LC',SUM(R1.FPriceUnitQty) as 'FPriceUnitQty',
|
|
|
|
|
|
STRING_AGG(CONVERT(nvarchar,R1.FDATE,23),',') as 'FDate' From (SELECT W2.FSBILLID,W1.FNAME,SUM(W1.FALLAMOUNT_LC) as 'FALLAMOUNT_LC',
|
|
|
|
|
|
SUM(W1.FPriceUnitQty) as 'FPriceUnitQty',STRING_AGG(CONVERT(nvarchar,W1.FDATE,23),',') as 'FDate' FROM (Select q2.FALLAMOUNT_LC,
|
|
|
|
|
|
q2.FENTRYID,q1.FDATE,q3.FNAME,q2.FPriceUnitQty From T_SAL_DELIVERYNOTICE q1 JOIN T_SAL_DELIVERYNOTICEENTRY_F q2 on q1.FID = q2.FID JOIN
|
|
|
|
|
|
T_SEC_USER q3 on q1.FCreatorId = q3.FUSERID Where q1.FDocumentStatus = 'C' AND q3.FForbidStatus = 'A' AND q1.FCancelStatus = 'A')
|
|
|
|
|
|
W1 JOIN T_SAL_DELIVERYNOTICEENTRY_LK W2 on W1.FENTRYID = W2.FENTRYID Where W2.FSTABLENAME = 'T_SAL_ORDERENTRY'
|
|
|
|
|
|
GROUP BY W2.FSBILLID,W1.FNAME) R1 GROUP BY R1.FSBILLID) A;
|
|
|
|
|
|
go
|
2025-03-25 15:06:34 +08:00
|
|
|
|
--ALTER VIEW [dbo].[V_SXJD_Table2]
|
|
|
|
|
|
--AS
|
|
|
|
|
|
--Select a5.FID,SUM(a2.FRealQty) as 'FRealQty',SUM(FALLAMOUNT_LC) as 'FALLAMOUNT_LC',STRING_AGG(CONVERT(nvarchar,a3.FDATE,23),',') as 'FDate',
|
|
|
|
|
|
--STRING_AGG(a4.FNAME,',')as 'FNAME',SUM(a1.FSIGNQTY) as 'FSIGNQTY' From T_SAL_OUTSTOCKENTRY_R a1 JOIN T_SAL_OUTSTOCKENTRY a2
|
|
|
|
|
|
--on a1.FENTRYID = a2.FENTRYID JOIN T_SAL_OUTSTOCKENTRY_F aa on aa.FENTRYID = a2.FENTRYID
|
|
|
|
|
|
--JOIN T_SAL_OUTSTOCK a3 on a3.FID = a1.FID JOIN T_SEC_USER a4 on a4.FUSERID = a3.FCREATORID
|
|
|
|
|
|
--JOIN T_SAL_ORDERENTRY a5 on a5.FENTRYID = a1.FSOENTRYID Where a3.FDOCUMENTSTATUS = 'C' GROUP BY a5.FID
|
|
|
|
|
|
--GO
|
|
|
|
|
|
-----2025-3-05--
|
2025-03-14 10:00:24 +08:00
|
|
|
|
ALTER VIEW [dbo].[V_SXJD_Table2]
|
|
|
|
|
|
AS
|
2025-03-25 15:06:34 +08:00
|
|
|
|
SELECT a5.FID,SUM(a2.FRealQty) AS 'FRealQty',SUM(FALLAMOUNT_LC) AS 'FALLAMOUNT_LC',
|
|
|
|
|
|
(
|
|
|
|
|
|
SELECT STRING_AGG(aa4.fname,',') AS fname FROM (
|
|
|
|
|
|
SELECT DISTINCT fname FROM T_SEC_USER a4
|
|
|
|
|
|
INNER JOIN T_SAL_OUTSTOCK a3 on a4.FUSERID = a3.FCREATORID
|
|
|
|
|
|
INNER JOIN T_SAL_OUTSTOCKENTRY_R aa1 ON a3.FID=aa1.fid
|
|
|
|
|
|
INNER JOIN T_SAL_ORDERENTRY aa5 ON aa5.FENTRYID = aa1.FSOENTRYID
|
|
|
|
|
|
WHERE aa5.FID=a5.FID ) aa4
|
|
|
|
|
|
|
|
|
|
|
|
) AS 'FNAME'
|
|
|
|
|
|
,(SELECT STRING_AGG(CONVERT(NVARCHAR,aaa3.FDATE,23),',') FROM
|
|
|
|
|
|
(SELECT DISTINCT CONVERT(NVARCHAR,aa3.FDATE,23) AS FDATE FROM
|
|
|
|
|
|
T_SAL_OUTSTOCK aa3
|
|
|
|
|
|
INNER JOIN T_SAL_OUTSTOCKENTRY_R aa1 ON aa3.FID=aa1.fid
|
|
|
|
|
|
INNER JOIN T_SAL_ORDERENTRY aa5 ON aa5.FENTRYID = aa1.FSOENTRYID
|
|
|
|
|
|
WHERE aa5.FID=a5.FID ) aaa3 ) AS 'FDate'
|
|
|
|
|
|
,SUM(a1.FSIGNQTY) AS 'FSIGNQTY' FROM T_SAL_OUTSTOCKENTRY_R a1 JOIN T_SAL_OUTSTOCKENTRY a2
|
|
|
|
|
|
ON a1.FENTRYID = a2.FENTRYID JOIN T_SAL_OUTSTOCKENTRY_F aa ON aa.FENTRYID = a2.FENTRYID
|
|
|
|
|
|
JOIN T_SAL_OUTSTOCK a3 ON a3.FID = a1.FID
|
|
|
|
|
|
JOIN T_SAL_ORDERENTRY a5 ON a5.FENTRYID = a1.FSOENTRYID WHERE a3.FDOCUMENTSTATUS = 'C' GROUP BY a5.FID
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
2025-03-14 10:00:24 +08:00
|
|
|
|
ALTER VIEW [dbo].[V_SXJD_Table3]
|
|
|
|
|
|
AS
|
|
|
|
|
|
Select a3.FID,SUM(a1.FREALQTY)'FREALQTY',SUM(a2.FALLAMOUNT_LC)'FALLAMOUNT_LC',STRING_AGG(a5.FNAME,',')as 'FNAME',
|
|
|
|
|
|
STRING_AGG((CONVERT(nvarchar,a4.FDATE,23)),',')as 'FDate' From T_SAL_RETURNSTOCKENTRY a1 JOIN T_SAL_RETURNSTOCKENTRY_F a2
|
|
|
|
|
|
on a1.FENTRYID = a2.FENTRYID JOIN T_SAL_ORDERENTRY a3 on a3.FENTRYID = a1.FSOENTRYID JOIN T_SAL_RETURNSTOCK a4
|
|
|
|
|
|
on a4.FID = a2.FID JOIN T_SEC_USER a5 on a5.FUSERID = a4.FCREATORID GROUP BY a3.FID
|
|
|
|
|
|
go
|
|
|
|
|
|
ALTER VIEW [dbo].[V_SXJD_Table4]
|
|
|
|
|
|
AS
|
|
|
|
|
|
Select a1.FORDERNUMBER,MAX(a2.F_INVOICENUMBER)as 'F_INVOICENUMBER',STRING_AGG((CONVERT(nvarchar,a2.FDATE,23)),',')'FDate',STRING_AGG(a3.FNAME,',')as 'FNAME',
|
|
|
|
|
|
SUM(a1.FOPENQTY)'FOPENQTY',SUM(a1.FALLAMOUNT)'FALLAMOUNT' From T_AR_RECEIVABLEENTRY a1 JOIN T_AR_RECEIVABLE a2
|
|
|
|
|
|
on a1.FID = a2.FID JOIN T_SEC_USER a3 on a3.FUSERID = a2.FCREATORID Where ISNULL(a1.FORDERNUMBER,'') != ''
|
|
|
|
|
|
GROUP BY a1.FORDERNUMBER
|
|
|
|
|
|
go
|
|
|
|
|
|
ALTER VIEW [dbo].[V_SXJD_Table5]
|
|
|
|
|
|
AS
|
|
|
|
|
|
Select * From
|
|
|
|
|
|
(Select a1.FID,a2.FBILLNO,a3.FNAME,STRING_AGG((CONVERT(nvarchar,a1.FDATE,23)),',')as 'FDate',SUM(a1.FREALRECAMOUNT) as 'FREALRECAMOUNT'
|
|
|
|
|
|
From T_AR_RECEIVEBILL a1 JOIN T_SAL_ORDER a2 on a1.F_CONTRACTNUMBER = a2.F_CONTRACTNUMBER LEFT JOIN
|
|
|
|
|
|
T_SEC_USER a3 on a1.FCreatorId = a3.FUSERID Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.FID,a2.FBILLNO,a3.FNAME) A;
|
|
|
|
|
|
go -- <20>˿<EFBFBD><CBBF><EFBFBD>ͼ
|
|
|
|
|
|
ALTER VIEW [dbo].[V_SXJD_Table6]
|
|
|
|
|
|
AS
|
|
|
|
|
|
Select a1.F_VRYF_TEXT_83G,STRING_AGG(a3.FNAME,',')as 'FNAME',STRING_AGG((CONVERT(nvarchar,a1.FDATE,23)),',')as 'FDate',SUM(a1.FREALREFUNDAMOUNT) as 'FREALREFUNDAMOUNT'
|
|
|
|
|
|
From T_AR_REFUNDBILL a1 JOIN T_SAL_ORDER a2 on a2.F_CONTRACTNUMBER = a1.F_VRYF_TEXT_83G JOIn T_SEC_USER a3 on a3.FUSERID = a1.FCREATERID
|
|
|
|
|
|
Where a1.FDOCUMENTSTATUS = 'C' GROUP BY a1.F_VRYF_TEXT_83G
|
|
|
|
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Select a5.FID,SUM(a2.FRealQty) as 'FRealQty',SUM(FALLAMOUNT_LC) as 'FALLAMOUNT_LC',STRING_AGG(CONVERT(nvarchar,a3.FDATE,23),',') as 'FDate',
|
|
|
|
|
|
STRING_AGG(a4.FNAME,',')as 'FNAME',SUM(a1.FSIGNQTY) as 'FSIGNQTY' From T_SAL_OUTSTOCKENTRY_R a1 JOIN T_SAL_OUTSTOCKENTRY a2
|
|
|
|
|
|
on a1.FENTRYID = a2.FENTRYID JOIN T_SAL_OUTSTOCKENTRY_F aa on aa.FENTRYID = a2.FENTRYID
|
|
|
|
|
|
JOIN T_SAL_OUTSTOCK a3 on a3.FID = a1.FID JOIN T_SEC_USER a4 on a4.FUSERID = a3.FCREATORID
|
|
|
|
|
|
JOIN T_SAL_ORDERENTRY a5 on a5.FENTRYID = a1.FSOENTRYID Where a3.FDOCUMENTSTATUS = 'C' GROUP BY a5.FID
|
|
|
|
|
|
|
|
|
|
|
|
|
