审计用（截止到某一日期的应收明细直接SQL报表）
SELECT T.FNAME AS [单据类型],
F.FNAME AS 业务组织,
E.FNUMBER AS 客户编码,
E.FNAME AS 客户名称,
A.单据日期,
A.单据编号,
A.单据行号,
C.FNUMBER AS 物料编码,
C.FNAME AS 物料名称,
C.FSPECIFICATION AS 规格型号,
D.FNAME AS 单位,
A.应发数量,
A.实发数量,
A.批号文件,
A.计价数量,
A.税率,
A.[税额（本位币）],
A.[金额（本位币）],
A.[价税合计（本位币）],
A.[总成本（本位币）],
A.赠品,
B.单据日期 AS 应收单单据日期,
ISNULL(B.应收单计价数量, 0) AS 应收单计价数量,
ISNULL(B.[应收单不含税金额(本位币)], 0) AS [应收单不含税金额(本位币)],
ISNULL(B.[应收单价税合计(本位币)], 0) AS [应收单价税合计(本位币)],
ISNULL(B.[应收单成本金额], 0) AS [应收单成本金额]
FROM (
SELECT 0 AS SORT,
CK0.FBILLTYPEID AS [单据类型],
CK0.FBILLNO AS [单据编号],
CK0.FID AS [单据内码],
CK0.FSALEORGID AS [业务组织内码],
CONVERT(VARCHAR(10), CK0.FDATE, 120) AS [单据日期],
CK0.FCUSTOMERID AS [客户内码],
CK1.FENTRYID AS [分录内码],
CK1.FSEQ AS [单据行号],
CK1.FMATERIALID AS [物料内码],
CK1.FUNITID AS [计量单位],
CK1.FMUSTQTY AS [应发数量],
CK1.FREALQTY AS [实发数量],
CK1.FLOT_TEXT AS [批号文件],
CK3.FPRICEUNITQTY AS [计价数量],
CK3.FTAXRATE AS [税率],
CK3.FTAXAMOUNT_LC AS [税额（本位币）],
CK3.FAMOUNT_LC AS [金额（本位币）],
CK3.FALLAMOUNT_LC AS [价税合计（本位币）],
CK3.FCOSTAMOUNT_LC AS [总成本（本位币）],
CK3.FISFREE AS [赠品]
FROM T_SAL_OUTSTOCK CK0
LEFT JOIN T_SAL_OUTSTOCKENTRY CK1
ON CK1.FID = CK0.FID
LEFT JOIN T_SAL_OUTSTOCKENTRY_F CK3
ON CK1.FENTRYID = CK3.FENTRYID
WHERE CK0.FCANCELSTATUS = 'A'
UNION ALL
SELECT 1 AS SORT,
FBILLTYPEID AS [单据类型],
TH0.FBILLNO AS [单据编号],
TH0.FID AS [单据内码],
TH0.FSALEORGID AS [业务组织内码],
CONVERT(VARCHAR(10), TH0.FDATE, 120) AS [单据日期],
TH0.FRETCUSTID AS [客户内码],
TH1.FENTRYID AS [分录内码],
TH1.FSEQ AS [单据行号],
TH1.FMATERIALID AS [物料内码],
TH1.FUNITID AS [计量单位],
TH1.FMUSTQTY AS [应退数量],
TH1.FREALQTY AS [实退数量],
TH1.FLOT_TEXT AS [批号文件],
TH3.FPRICEUNITQTY AS [计价数量],
TH3.FTAXRATE AS [税率],
TH3.FTAXAMOUNT_LC AS [税额（本位币）],
TH3.FAMOUNT_LC AS [金额（本位币）],
TH3.FALLAMOUNT_LC AS [价税合计（本位币）],
TH3.FCOSTAMOUNT_LC AS [总成本（本位币）],
TH3.FISFREE AS [赠品]
FROM T_SAL_RETURNSTOCK TH0
LEFT JOIN T_SAL_RETURNSTOCKENTRY TH1
ON TH1.FID = TH0.FID
LEFT JOIN T_SAL_RETURNSTOCKENTRY_F TH3
ON ( TH1.FID = TH3.FID
AND TH1.FENTRYID = TH3.FENTRYID)
LEFT JOIN T_SAL_RETURNSTOCKENTRY_LK TH4
ON TH1.FENTRYID = TH4.FENTRYID
WHERE TH0.FDOCUMENTSTATUS <> 'A'
UNION ALL
SELECT 3 AS SORT,
QCCK0.FBILLTYPEID AS [单据类型],
QCCK0.FBILLNO AS [单据编号],
QCCK0.FID AS [单据内码],
QCCK0.FSALEORGID AS [业务组织内码],
CONVERT(VARCHAR(10), QCCK0.FDATE, 120) AS [单据日期],
QCCK0.FCUSTOMERID AS [客户内码],
QCCK1.FENTRYID AS [分录内码],
QCCK1.FSEQ AS [单据行号],
QCCK1.FMATERIALID AS [物料内码],
QCCK1.FUNITID AS [计量单位],
QCCK1.FMUSTQTY AS [应发数量],
QCCK1.FREALQTY AS [实发数量],
QCCK1.FLOT_TEXT AS [批号文件],
QCCK3.FPRICEUNITQTY AS [计价数量],
QCCK3.FTAXRATE AS [税率],
QCCK3.FTAXAMOUNT_LC AS [税额（本位币）],
QCCK3.FAMOUNT_LC AS [金额（本位币）],
QCCK3.FALLAMOUNT_LC AS [价税合计（本位币）],
QCCK3.FCOSTAMOUNT_LC AS [总成本（本位币）],
QCCK3.FISFREE AS [赠品]
FROM T_SAL_INITOUTSTOCK QCCK0
LEFT JOIN T_SAL_INITOUTSTOCKENTRY QCCK1
ON QCCK0.FID = QCCK1.FID
LEFT JOIN T_SAL_INITOUTSTOCKENTRY_F QCCK3
ON ( QCCK1.FID = QCCK3.FID
AND QCCK1.FENTRYID = QCCK3.FENTRYID)
WHERE QCCK0.FBUSINESSTYPE = 'OUTSTOCK'
UNION ALL
SELECT 4 AS SORT,
QCCK0.FBILLTYPEID AS [单据类型],
QCCK0.FBILLNO AS [单据编号],
QCCK0.FID AS [单据内码],
QCCK0.FSALEORGID AS [业务组织内码],
CONVERT(VARCHAR(10), QCCK0.FDATE, 120) AS [单据日期],
QCCK0.FCUSTOMERID AS [客户内码],
QCCK1.FENTRYID AS [分录内码],
QCCK1.FSEQ AS [单据行号],
QCCK1.FMATERIALID AS [物料内码],
QCCK1.FUNITID AS [计量单位],
QCCK1.FMUSTQTY AS [应发数量],
QCCK1.FREALQTY AS [实发数量],
QCCK1.FLOT_TEXT AS [批号文件],
QCCK3.FPRICEUNITQTY AS [计价数量],
QCCK3.FTAXRATE AS [税率],
QCCK3.FTAXAMOUNT_LC AS [税额（本位币）],
QCCK3.FAMOUNT_LC AS [金额（本位币）],
QCCK3.FALLAMOUNT_LC AS [价税合计（本位币）],
QCCK3.FCOSTAMOUNT_LC AS [总成本（本位币）],
QCCK3.FISFREE AS [赠品]
FROM T_SAL_INITOUTSTOCK QCCK0
LEFT JOIN T_SAL_INITOUTSTOCKENTRY QCCK1
ON QCCK0.FID = QCCK1.FID
LEFT JOIN T_SAL_INITOUTSTOCKENTRY_F QCCK3
ON ( QCCK1.FID = QCCK3.FID
AND QCCK1.FENTRYID = QCCK3.FENTRYID)
WHERE QCCK0.FBUSINESSTYPE = 'RETURN') A
LEFT JOIN (
SELECT YSD.FMATERIALID,
YSD.FSBILLID AS FSBILLID,
YSD.FSALEORGID AS FSALEORGID,
YSD.FSID AS FSID,
CONVERT(VARCHAR(10), YSD.FDATE, 120) AS [单据日期],
SUM(YSD.FPRICEQTY) AS [应收单计价数量],
SUM(YSD.FNOTAXAMOUNT) AS [应收单不含税金额(本位币)],
SUM(YSD.FALLAMOUNT) AS [应收单价税合计(本位币)],
SUM
审计用（截止到某一日期的应收明细直接SQL报表）
SELECT T.FNAME AS [单据类型],F.FNAME AS 业务组织,E.FNUMBER AS 客户编码,E.FNAME AS 客户名称,A.单据日期,A.单据编号,A.单据行...
点击下载文档
本文2024-09-16 18:58:12发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-25566.html
相关文章
您需要登录后才可以发表评论， 登录登录 或者 注册
最新文档
热门文章
阅读排行
确认删除?