TianHua/Piolot_RepotForm_PeiHao
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Piolot_RepotForm_PeiHao/动态表单（应收款明细表）.sql

304 lines
18 KiB
MySQL
Raw Permalink Normal View History

0
2025-03-14 10:00:24 +08:00
--EXEC [GetReceiveData2] '','2025-01-01','2025-01-31';
--ALTER
ALTER Proc [dbo].[GetReceiveData2]
(
@FSaleOrgId nvarchar(1000),
@FStartDate nvarchar(1000),
@FEndDate nvarchar(1000)
)
as
SELECT W.*,x.FNAME into #RecCond FROM T_SAL_ORDERPLAN W
INNER JOIN T_BD_RecCondition_L X on X.FID = W.F_RecConditionId
SELECT D.FID,sum(b.fqty) AS 'FQTY',sum(E.fallamount) AS 'FAMOUNT'
INTO #SAL
FROM T_SAL_DELIVERYNOTICE A
INNER JOIN T_SAL_DELIVERYNOTICEENTRY B ON A.FID = B.FID
INNER JOIN T_SAL_DELIVERYNOTICEENTRY_F E ON E.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_DELIVERYNOTICEENTRY_LK C ON C.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_ORDERENTRY D ON D.FID = C.FSBILLID AND D.FENTRYID = C.FSID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY D.FID
SELECT D.FID,sum(b.fqty) AS 'FQTY',sum(E.fallamount) AS 'FAMOUNT'
INTO #RETURN
FROM T_SAL_RETURNNOTICE A
INNER JOIN T_SAL_RETURNNOTICEENTRY B ON A.FID = B.FID
INNER JOIN T_SAL_RETURNNOTICEENTRY_F E ON E.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_RETURNNOTICEENTRY_LK C ON C.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_ORDERENTRY D ON D.FID = C.FSBILLID AND D.FENTRYID = C.FSID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY D.FID
SELECT F.FID,sum(b.FREALQTY) AS 'FQTY',sum(G.FALLAMOUNT_LC) AS 'FAMOUNT'
INTO #OUTSTOCK
FROM T_SAL_OUTSTOCK A
INNER JOIN T_SAL_OUTSTOCKENTRY B ON A.FID = B.FID
INNER JOIN T_SAL_OUTSTOCKENTRY_F G ON G.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_OUTSTOCKENTRY_LK C ON C.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_DELIVERYNOTICEENTRY D ON D.FID = C.FSBILLID AND D.FENTRYID = C.FSID
INNER JOIN T_SAL_DELIVERYNOTICEENTRY_LK E ON E.FENTRYID = D.FENTRYID
INNER JOIN T_SAL_ORDERENTRY F ON F.FID = E.FSBILLID AND F.FENTRYID = E.FSID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY F.FID
SELECT F.FID,sum(b.FREALQTY) AS 'FQTY',sum(G.FALLAMOUNT_LC) AS 'FAMOUNT'
INTO #RETURNSTOCK
FROM T_SAL_RETURNSTOCK A
INNER JOIN T_SAL_RETURNSTOCKENTRY B ON A.FID = B.FID
INNER JOIN T_SAL_RETURNSTOCKENTRY_F G ON G.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_RETURNSTOCKENTRY_LK C ON C.FENTRYID = B.FENTRYID
INNER JOIN T_SAL_RETURNNOTICEENTRY D ON D.FID = C.FSBILLID AND D.FENTRYID = C.FSID
INNER JOIN T_SAL_RETURNNOTICEENTRY_LK E ON E.FENTRYID = D.FENTRYID
INNER JOIN T_SAL_ORDERENTRY F ON F.FID = E.FSBILLID AND F.FENTRYID = E.FSID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY F.FID
SELECT b.FORDERENTRYID as 'FENTRYID',sum(b.FPriceQty) AS 'FQTY',sum(b.FALLAMOUNT) AS 'FAMOUNT'
INTO #RECE
FROM t_AR_receivable A
INNER JOIN t_AR_receivableENTRY B ON A.FID = B.FID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY b.FORDERENTRYID
SELECT
A.FBILLNO,
A.FID ,
--B.FENTRYID,
RANK()OVER(PARTITION BY A.FID ORDER BY N.FENTRYID ) AS '<EFBFBD><EFBFBD>̯<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',
RANK()OVER(PARTITION BY A.FID ORDER BY N.FENTRYID DESC ) AS '<EFBFBD><EFBFBD>̯<EFBFBD><EFBFBD><EFBFBD><EFBFBD>1',
RANK()OVER(ORDER BY A.F_contractnumber) AS 'F_001' --<EFBFBD><EFBFBD>ˮ<EFBFBD>ţ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Զ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,CASE WHEN ISNULL(O.FNAME,'') = '' THEN L.FNAME
ELSE O.FNAME END as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա'
,CASE WHEN ISNULL(O.FNAME,'') = '' THEN AC.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ELSE ACC.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> END as 'SBU'
,CASE WHEN ISNULL(O.FNAME,'') = '' THEN AC.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ELSE ACC.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> END as '<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,c.FNAME as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>λ' --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͷ-<EFBFBD>ͻ<EFBFBD>
,K.FDATAVALUE AS '<EFBFBD><EFBFBD>˾<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ڿͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD>˾<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,P.FDATAVALUE AS '<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>#
,A.F_contractnumber AS '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'--<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͷ-ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
, G.FNAME as '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͷ-<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,x.FNAME as '<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>Ϊ0<EFBFBD><EFBFBD>
,n.FSHARERATE as 'Ӧ<EFBFBD>ձ<EFBFBD><EFBFBD><EFBFBD>'
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN (F.FBILLALLAMOUNT_LC * isnull(n.FSHARERATE,100) /100) ELSE NULL END as '<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>
,convert( nvarchar(10), year(A.FDATE)) AS 'ǩ<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>
,CONVERT(VARCHAR(10), A.FDATE,23) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͷ-<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,Q.<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> AS '<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>
,CASE WHEN U.FDATAVALUE LIKE 'W%' THEN T.<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> ELSE T.û<EFBFBD>к<EFBFBD>ɫ<EFBFBD>Ŀ<EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> END AS '<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><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><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--1<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>#="W",<2C><><EFBFBD>еķ<D0B5>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>㣨<EFBFBD>ų<EFBFBD><C5B3><EFBFBD><EFBFBD>ּ<EFBFBD><D6BC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֳ<EFBFBD><D6B3><EFBFBD><EFBFBD><EFBFBD>ȫ<EFBFBD><C8AB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD>Ʊ<EFBFBD><C6B1>
--2<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>#<23><>"W",<2C><><EFBFBD>еķ<D0B5>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>㣨<EFBFBD>ų<EFBFBD><C5B3><EFBFBD><EFBFBD>ּ<EFBFBD><D6BC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֳ<EFBFBD><D6B3><EFBFBD><EFBFBD><EFBFBD>ȫ<EFBFBD><C8AB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>=<3D><><EFBFBD>ǡ<EFBFBD><C7A1><EFBFBD>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN cast( round( ISNULL(Q.<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0) * ISNULL(N.FSHARERATE,100)/100,2) as real) ELSE NULL END
as '<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>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN cast( round( ISNULL(v.<EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0) * ISNULL(N.FSHARERATE,100)/100,2) as real) ELSE NULL END
AS '<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><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>=<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><EFBFBD>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN ISNULL( CASE WHEN U.FDATAVALUE LIKE 'W%' THEN AD.<EFBFBD><EFBFBD>Ʊ<EFBFBD>ܽ<EFBFBD><EFBFBD><EFBFBD> ELSE AD.[<EFBFBD><EFBFBD>Ʊ<EFBFBD>ܽ<EFBFBD><EFBFBD><EFBFBD>(<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>)] END,0) ELSE NULL END
AS '<EFBFBD>ۼƿ<EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --1<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>#="W",<2C><><EFBFBD>еķ<D0B5>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD>ϼ<EFBFBD> 2<><32><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><>ͬ<EFBFBD><CDAC><EFBFBD><EFBFBD>#<23><>"W",<2C><><EFBFBD>еķ<D0B5>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>㣬<EFBFBD>ҡ<EFBFBD><D2A1>Ƿ<EFBFBD><C7B7><EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>=<3D><><EFBFBD>ǡ<EFBFBD><C7A1><EFBFBD><EFBFBD><EFBFBD>Ҫ<EFBFBD><D2AA>ҵ<EFBFBD><D2B5>Ա<EFBFBD><D4B1>̯<EFBFBD><CCAF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN cast(round( isnull(ae.<EFBFBD>ۼ<EFBFBD><EFBFBD>տ<EFBFBD>,0) * isnull(n.FSHARERATE,100) /100,2) as real) ELSE NULL END
AS '<EFBFBD>ۼƵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN cast(round( (F.FBILLALLAMOUNT_LC - (isnull(ae.<EFBFBD>ۼ<EFBFBD><EFBFBD>տ<EFBFBD>,0))) * isnull(n.FSHARERATE,100) /100,2) as real) ELSE NULL END
AS 'Ӧ<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><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¼<EFBFBD><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD>У<EFBFBD>-<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,cast( round( J.FBILLALLAMOUNT_LC- ISNULL(Q.<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0) * ISNULL(N.FSHARERATE,100)/100,2) as real) AS 'δ<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>
,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23) AS 'Ԥ<EFBFBD>Ƶ<EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ƻ<EFBFBD>-PMS<EFBFBD><EFBFBD><EFBFBD><EFBFBD>#
,case when convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23) > Getdate() AND W.FRECADVANCEAMOUNT > W.FRECAMOUNT then W.FRECADVANCEAMOUNT - W.FRECAMOUNT ELSE 0 END AS 'δ<EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS 'ʵ<EFBFBD><EFBFBD><EFBFBD>ѳ<EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD>ϼ<EFBFBD>'--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --
,case when convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23) <= Getdate() AND W.FRECADVANCEAMOUNT > W.FRECAMOUNT then W.FRECADVANCEAMOUNT - W.FRECAMOUNT ELSE 0 END as '<EFBFBD><EFBFBD><EFBFBD>ڽ<EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,case when isnull(ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) ,0)>=6 then 'Ӧ<EFBFBD>տ<EFBFBD>ר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>' else O.FNAME end AS '<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,CASE WHEN A.F_XMJD <> '' THEN A.F_XMJD WHEN ISNULL(Q.<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0) = 0 THEN 'δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' WHEN F.FBILLALLAMOUNT_LC = ISNULL(Q.<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0) THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' WHEN F.FBILLALLAMOUNT_LC > ISNULL(Q.<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0) then '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' END AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD>׶<EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,a.F_XMJDD AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>-<EFBFBD><EFBFBD><EFBFBD>۽<EFBFBD><EFBFBD><EFBFBD>
,convert(nvarchar(10),a.F_XMTSSJ,23) AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --ȡ<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
,convert(nvarchar(10),a.F_XMYSSJ,23) AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --ȡ<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN CAST ( y.FRECADVANCEAMOUNT AS REAL ) ELSE NULL END
AS '<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>
,CASE WHEN ROW_NUMBER() OVER (PARTITION BY A.FId,n.FSaleId ORDER BY n.FSaleId) = 1
THEN CAST ( y.F_DAY as REAL ) ELSE NULL END
AS '<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --
,convert(nvarchar(10),dateadd(day,isnull(y.f_day,0) ,y.F_PMSDate),23) AS '<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --
,case when a.F_YYS = 0 then '<EFBFBD><EFBFBD>' else '<EFBFBD><EFBFBD>' end as '<EFBFBD><EFBFBD>ǰ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
, ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,case when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) <= 6 AND ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >=1 then 'һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) <= 12 AND ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >=7 then '<EFBFBD>ߵ<EFBFBD>ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) <= 18 AND ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >=13 then 'ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʮ<EFBFBD>˸<EFBFBD><EFBFBD><EFBFBD>'
when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >= 19 then 'ʮ<EFBFBD>Ÿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' end
AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׶<EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,w.FSEQ as '<EFBFBD>׶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
--,w.FRECADVANCERATE as '<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
,SUM(w.FRECADVANCERATE)OVER(PARTITION BY A.FID,N.FENTRYID ORDER BY W.FSEQ) as '<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
,case when F.FBILLALLAMOUNT_LC - ISNULL(v.<EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0)= 0 THEN 0 ELSE round(( isnull(ae.<EFBFBD>ۼ<EFBFBD><EFBFBD>տ<EFBFBD>,0) - ISNULL(v.<EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0))/( F.FBILLALLAMOUNT_LC - ISNULL(v.<EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,0)) ,2) END '<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
,convert(nvarchar(100), '') AS 'һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,convert(nvarchar(100), '') AS '<EFBFBD>ߵ<EFBFBD>ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,convert(nvarchar(100), '') AS 'ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʮ<EFBFBD>˸<EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,convert(nvarchar(100), '') AS 'ʮ<EFBFBD>Ÿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,convert(nvarchar(100), '') AS 'һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,case when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) <= 6 AND ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >=1 then '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) <= 12 AND ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >=7 then '<EFBFBD>߿'
when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) <= 18 AND ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >=13 then '<EFBFBD><EFBFBD>ʦ<EFBFBD><EFBFBD>'
when ROUND( DATEDIFF(DAY,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23),Getdate()) / 30,0) >= 19 then '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' else '' end
AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,CASE WHEN S.FCAPTION = '<EFBFBD>DZ<EFBFBD>' THEN 'Ԫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' ELSE s.FCAPTION END AS '<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>#<23><><EFBFBD><EFBFBD>ֵΪ<D6B5>DZ<EFBFBD>ʱ<EFBFBD><CAB1><EFBFBD>ij<EFBFBD>Ԫ<EFBFBD><D4AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϊϵͳ
,CASE WHEN S.FCAPTION = '<EFBFBD>DZ<EFBFBD>' AND a.F_XMYSSJ <> '' then '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>꣬<EFBFBD>ز<EFBFBD><EFBFBD>ֿ<EFBFBD>'
WHEN S.FCAPTION = '<EFBFBD>DZ<EFBFBD>' AND a.F_XMYSSJ = '' then '<EFBFBD><EFBFBD>Ŀδ<EFBFBD>깤<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ'
WHEN S.FCAPTION <> '<EFBFBD>DZ<EFBFBD>' AND y.FID is not null then '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
WHEN S.FCAPTION <> '<EFBFBD>DZ<EFBFBD>' AND z.FID is not null then 'Э<EFBFBD>鸶<EFBFBD><EFBFBD>'
ELSE 'Ԥ<EFBFBD>տ<EFBFBD>+<2B><><EFBFBD><EFBFBD>/<2F><><EFBFBD>Կ<EFBFBD>+<2B>ʱ<EFBFBD><CAB1><EFBFBD>' END AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0)+180 ,W.F_PMSDate),23) AS 'תר<EFBFBD><EFBFBD>С<EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>'
,case when convert(nvarchar(10),dateadd(day,isnull(w.f_day,0)+180 ,W.F_PMSDate),23) is null then '<EFBFBD><EFBFBD>' else '<EFBFBD><EFBFBD>' end AS '<EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ת<EFBFBD><EFBFBD>'
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','' AS 'ԭSBU','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','' AS 'ԭ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>ʽ','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ؿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','' AS '<EFBFBD>ؿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>黹<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>÷<EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS 'ȡֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,case when FIsBadDebt = 1 then '<EFBFBD><EFBFBD>' else '<EFBFBD><EFBFBD>' end AS '<EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϵ<EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,CASE WHEN AB.F_PAPERCONTRACT IS NULL THEN '<EFBFBD><EFBFBD>' else '<EFBFBD><EFBFBD>' end AS '<EFBFBD>Ƿ<EFBFBD>Ԥ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϵ<EFBFBD><EFBFBD>Ƿ<EFBFBD>Ԥ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS 'Ԥ<EFBFBD>ƻؿ<EFBFBD>ʱ<EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS 'Ԥ<EFBFBD>ƻؿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>E
,'' AS '<EFBFBD><EFBFBD><EFBFBD>۷<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӫר<EFBFBD><EFBFBD>Э<EFBFBD><EFBFBD><EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
,B.FDATAVALUE as '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
INTO #TEMP1
FROM T_SAL_ORDER A --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͷ
LEFT JOIN
(Select a2.FENTRYID,a2.FDATAVALUE From T_BAS_ASSISTANTDATAENTRY a1 JOIN T_BAS_ASSISTANTDATAENTRY_L a2 on a1.FENTRYID = a2.FENTRYID
Where a1.FID = '670cc977263353') B on A.F_ORDERCATEGORY = B.FENTRYID
--INNER JOIN T_SAL_orderentry b on a.FID = B.FID --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
INNER JOIN T_BD_CUSTOMER_L C ON C.FCUSTID= A.FCUSTID AND C.FLOCALEID= 2052 --<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
INNER JOIN T_SAL_ORDERFIN F ON F.FID = A.FID
INNER JOIN T_BD_CUSTOMER I ON I .FCUSTID = A.FCUSTID
INNER JOIN T_SAL_ORDERFIN J ON J.FID= A.FID
LEFT JOIN T_BAS_PREBDONE_L G ON G.FID = A.F_projectname AND G.FLocaleID =2052
LEFT JOIN T_BAS_PREBDTHREE H ON H.FID = A.F_BON
lEFT JOIN V_ASSISTANTDATA K ON K.FENTRYID = I.FCompanyNature
lEFT JOIN V_BD_SALESMAN_L L ON L.fid = A.FSALERID
left JOIN T_PerformanceSharing M ON M.FSOURCEBILLNO = A.FBILLNO
left JOIN T_PerformanceSharingEntry N ON N.FID=M.FID
LEFT JOIN V_BD_SALESMAN_L O ON O.fid= N.FSALEID
LEFT JOIN V_ASSISTANTDATA P ON P.FENTRYID = I.F_CREDITCLASSIFICATION
LEFT JOIN
(
SELECT F_Papercontract,MAX(CONVERT(NVARCHAR(10), A.FDATE,23)) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',SUM(B.FBILLALLAMOUNT_LC) AS '<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' FROM T_SAL_DELIVERYNOTICE A
INNER JOIN T_SAL_DELIVERYNOTICEFIN B ON A.FID =B.FID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY A.F_Papercontract
) Q ON Q.F_PAPERCONTRACT = A.F_contractnumber --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>֪ͨ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ķ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
LEFT JOIN T_META_FORMENUMITEM R ON r.FVALUE = A.F_OrderType AND R.FID = 'bc2c1d77-0f4b-41d2-b61b-e29554c570d4'
LEFT JOIN T_META_FORMENUMITEM_L S ON S.FENUMID = R.FENUMID AND S.FLOCALEID = 2052
LEFT JOIN
(
SELECT F_Paper ,CONVERT(NVARCHAR(10), MAX(FDATE),23) AS '<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',CONVERT(NVARCHAR(10),
MAX(FDATE),23) AS 'û<EFBFBD>к<EFBFBD>ɫ<EFBFBD>Ŀ<EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
FROM t_AR_receivable A
INNER JOIN T_AR_RECEIVABLEENTRY B ON A.FID = B.FID
WHERE A.FDOCUMENTSTATUS = 'C' AND A.FCancelStatus = 'A'
GROUP BY F_Paper
) T ON T.F_PAPER = A.F_contractnumber
LEFT JOIN V_ASSISTANTDATA U ON U.FENTRYID = A.F_Ordercategory
LEFT JOIN
(
SELECT F_contractnumber,MAX(CONVERT(NVARCHAR(10), A.FDATE,23)) AS '<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',SUM(B.FBILLALLAMOUNT_LC) AS '<EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' FROM T_SAL_RETURNSTOCK A
INNER JOIN T_SAL_RETURNSTOCKFIN B ON A.FID =B.FID
WHERE A.FDOCUMENTSTATUS= 'C'
GROUP BY A.F_contractnumber
) V ON V.F_contractnumber = A.F_contractnumber --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD>
LEFT JOIN ERPTOHR.HYHRV3.dbo.v_erp_empinfo AC ON AC.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>=L.FNAME
LEFT JOIN ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACC ON ACC.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>=O.FNAME
LEFT JOIN T_SAL_ORDERPLAN W ON W.FID= A.FID
LEFT JOIN T_BD_RecCondition_L X on X.FID = W.F_RecConditionId
LEFT JOIN #RecCond Y ON Y.FID =A.FID AND Y.FNAME LIKE '%<25>ʱ<EFBFBD><CAB1><EFBFBD>%'
LEFT JOIN (select distinct fid from #RecCond Z where Z.FNAME LIKE '%<25>%') z on z.fid =a.fid
LEFT JOIN (select distinct fid from #RecCond AA where AA.FNAME LIKE '%Э<><D0AD>%') AA on AA.fid =a.fid
LEFT JOIN (SELECT distinct F_Papercontract FROM T_SAL_DELIVERYNOTICE WHERE F_SFYFH ='<EFBFBD><EFBFBD>') AB ON AB.F_PAPERCONTRACT = A.F_contractnumber
LEFT JOIN
(
SELECT
F_Papercontract,CAST( sum(b.FALLAMOUNT) AS REAL) AS '<EFBFBD><EFBFBD>Ʊ<EFBFBD>ܽ<EFBFBD><EFBFBD><EFBFBD>',
CAST (sum(CASE WHEN F_Papercontract = 'Y' THEN b.FALLAMOUNT ELSE 0 END ) AS REAL) AS '<EFBFBD><EFBFBD>Ʊ<EFBFBD>ܽ<EFBFBD><EFBFBD><EFBFBD>(<28><>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>)'
FROM t_AR_receivable A
INNER JOIN T_AR_RECEIVABLEFIN B ON A.FID = B.FID
GROUP BY F_Papercontract
) AD ON AD.F_PAPERCONTRACT = A.F_contractnumber
LEFT JOIN
(
SELECT a.F_Papercontract,
avg(d.FCURWRITTENOFFAMOUNT) as '<EFBFBD>ۼ<EFBFBD><EFBFBD>տ<EFBFBD>'
FROM t_AR_receivable A
INNER JOIN T_AR_RECEIVABLEENTRY E ON E.FID =A.FID
INNER JOIN T_AR_RECMacthLogENTRY B ON A.FBILLNO = B.FSRCBILLNO
INNER JOIN T_AR_RECMacthLog C ON C.FID = B.FID
INNER JOIN T_AR_RECMacthLogENTRY D ON D.FID = B.FID
AND D.FSOURCETYPE = '36cf265bd8c3452194ed9c83ec5e73d2'
WHERE
1=1
group by a.F_Papercontract
) AE ON AE.F_Papercontract = A.F_contractnumber
WHERE 1=1 AND A.FDOCUMENTSTATUS = 'C'
and (@FSaleOrgId = '' or A.FSALEORGID = @FSaleOrgId)
AND (@FEndDate = '' or A.FDATE<=@FEndDate)
AND (@FStartDate = '' or A.FDATE>=@FStartDate)
ORDER BY A.F_contractnumber
--AND A.FBILLNO = 'PL-XSDD207241200017'
--AND a.FBILLNO = 'PL-XSDD20241200058'
--where a.fid = 100130
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
UPDATE #TEMP1 SET
[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] = '0' ,[<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׶<EFBFBD>]=''
WHERE <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><=<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>*100
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
--SELECT *
UPDATE #TEMP1
SET [һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]= case when [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] <> 0 AND [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׶<EFBFBD>]='һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' AND <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> then CONVERT(nvarchar(10), round( <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,2)-<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>*100) else '' end,
[<EFBFBD>ߵ<EFBFBD>ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]= case when [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] <> 0 AND [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׶<EFBFBD>]='<EFBFBD>ߵ<EFBFBD>ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' AND <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> then CONVERT(nvarchar(10), round( <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,2)-<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>*100) else '' end,
[ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʮ<EFBFBD>˸<EFBFBD><EFBFBD><EFBFBD>]=case when [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] <> 0 AND [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׶<EFBFBD>]='ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʮ<EFBFBD>˸<EFBFBD><EFBFBD><EFBFBD>' AND <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> then CONVERT(nvarchar(10), round( <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,2)-<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>*100) else '' end,
[ʮ<EFBFBD>Ÿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]=case when [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] <> 0 AND [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׶<EFBFBD>]='ʮ<EFBFBD>Ÿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' AND <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> then CONVERT(nvarchar(10), round( <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,2)-<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> *100) else '' end,
[һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]=case when [<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] <> 0 AND ROUND([<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] / 30,0) >= 13 AND <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> then CONVERT(nvarchar(10), round( <EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,2)-<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>*100) else '' end
--FROM #TEMP1 WHERE Ԥ<>Ƶ<EFBFBD><C6B5><EFBFBD>ʱ<EFBFBD><CAB1><Getdate()
Select * INTO #TEMP2 From
(SELECT *,1 Sort FROM #TEMP1
UNION ALL
SELECT
'','',null,null,0,'<EFBFBD>ϼƣ<EFBFBD>','','','','','','','','',null,SUM(t1.<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>),--16
'',null,null,null,SUM(t1.<EFBFBD>ۼƷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>),SUM(t1.<EFBFBD>ۼ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>),SUM(t1.<EFBFBD>ۼƿ<EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>),SUM(t1.<EFBFBD>ۼƵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>),SUM(t1.Ӧ<EFBFBD>տ<EFBFBD><EFBFBD>ϼ<EFBFBD>),null,--δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>
null,null,null,null,null,'','','','','',--<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>
SUM(<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD>),SUM(t1.<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>),null,'',null,'',null,--<EFBFBD>׶<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
null,null,'','','','','','','','',null,'','',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
'','','','','','','','','','','','','','','','',--<EFBFBD>Ƿ<EFBFBD>Ԥ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
null,null,'','','','',0 Sort
FROM #TEMP1 t1) t1
order by Sort desc
SELECT * FROM #TEMP2 order by <20><>ͬ<EFBFBD><CDAC><EFBFBD><EFBFBD>,Sort;
DROP TABLE #SAL
DROP TABLE #OUTSTOCK
DROP TABLE #RETURN
DROP TABLE #RETURNSTOCK
DROP TABLE #RECE
DROP TABLE #RecCond
DROP TABLE #TEMP1
DROP TABLE #TEMP2
Reference in New Issue Copy Permalink