304 lines
18 KiB
Transact-SQL
304 lines
18 KiB
Transact-SQL
--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' --<2D><>ˮ<EFBFBD>ţ<EFBFBD><C5A3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Զ<EFBFBD><D4B6><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>'--<2D><><EFBFBD><EFBFBD>
|
||
,c.FNAME as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>λ' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-<2D>ͻ<EFBFBD>
|
||
,K.FDATAVALUE AS '<EFBFBD><EFBFBD>˾<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'--<2D><><EFBFBD><EFBFBD><EFBFBD>ڿͻ<DABF><CDBB><EFBFBD><EFBFBD><EFBFBD>-<2D><>˾<EFBFBD><CBBE><EFBFBD><EFBFBD>
|
||
,P.FDATAVALUE AS '<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D>ͻ<EFBFBD>-<2D>Զ<EFBFBD><D4B6><EFBFBD>-<2D>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD>ü<EFBFBD><C3BC><EFBFBD><EFBFBD><EFBFBD>#
|
||
,A.F_contractnumber AS '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'--<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-ֽ<>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>
|
||
, G.FNAME as '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-<2D><>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>
|
||
,x.FNAME as '<EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'--<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D>տ<EFBFBD><D5BF>ƻ<EFBFBD>-<2D>տ<EFBFBD><D5BF><EFBFBD><EFBFBD><EFBFBD>+<2B><><EFBFBD><EFBFBD>+<2B><><EFBFBD>족+Ӧ<>ձ<EFBFBD><D5B1><EFBFBD>(%),<2C><><EFBFBD><EFBFBD>֮<EFBFBD><D6AE><EFBFBD><EFBFBD>Ҫ<EFBFBD>á<EFBFBD>,<2C><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϊ<EFBFBD>վ<EFBFBD>Ĭ<EFBFBD><C4AC>Ϊ0<CEAA><30>
|
||
,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>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>-<2D><>˰<EFBFBD>ϼƱ<CFBC>λ<EFBFBD>ң<EFBFBD><D2A3><EFBFBD><EFBFBD>շ<EFBFBD>̯<EFBFBD><CCAF><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
||
,convert( nvarchar(10), year(A.FDATE)) AS 'ǩ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-<2D><><EFBFBD><EFBFBD>-<2D><>Ӧ<EFBFBD><D3A6><EFBFBD>·<EFBFBD>
|
||
,CONVERT(VARCHAR(10), A.FDATE,23) AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-<2D><><EFBFBD><EFBFBD>
|
||
,Q.<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-ֽ<>ʺ<EFBFBD>ͬ<EFBFBD>Ź<EFBFBD><C5B9><EFBFBD><EFBFBD>ķ<EFBFBD><C4B7><EFBFBD>֪ͨ<CDA8><D6AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><D6B5><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>=<3D><><EFBFBD><EFBFBD>֪ͨ<CDA8><D6AA>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>
|
||
,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>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-ֽ<>ʺ<EFBFBD>ͬ<EFBFBD>Ź<EFBFBD><C5B9><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>յ<EFBFBD><D5B5><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><D6B5><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>=<3D><>Ʊֽ<C6B1>ʺ<EFBFBD>ͬ<EFBFBD>š<EFBFBD>
|
||
--<2D>û<EFBFBD><C3BB><EFBFBD>Ҫȡ<D2AA><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֿ<EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD>ڵ<EFBFBD><DAB5><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><D6B5><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD>Ʊ<EFBFBD>Գ<EFBFBD>ʱ<EFBFBD><CAB1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD>ȫ<EFBFBD><C8AB><EFBFBD><EFBFBD><EFBFBD>꣬
|
||
--<2D><><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD>
|
||
--1<><31><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><>ͬ<EFBFBD><CDAC><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<><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><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>'--<2D><><EFBFBD><EFBFBD>֪ͨ<CDA8><D6AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EEA3AC>Ҫ<EFBFBD><D2AA>ҵ<EFBFBD><D2B5>Ա<EFBFBD><D4B1>̯<EFBFBD><CCAF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>֣<EFBFBD><D6A3><EFBFBD><EFBFBD>ݹ<EFBFBD><DDB9><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>=<3D><><EFBFBD><EFBFBD>֪ͨ<CDA8><D6AA>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>
|
||
,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>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-ֽ<>ʺ<EFBFBD>ͬ<EFBFBD>Ź<EFBFBD><C5B9><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˻<EFBFBD><CBBB><EFBFBD>-<2D>˻<EFBFBD><CBBB><EFBFBD><EFBFBD><EFBFBD>[<5B>˻<EFBFBD><CBBB><EFBFBD><EFBFBD><EFBFBD>Ҫ<EFBFBD><D2AA><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ<EFBFBD><CDAC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]<5D><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>=<3D>˻<EFBFBD><CBBB><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>
|
||
,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>(<28><>Ʊ<EFBFBD><C6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>)] END,0) ELSE NULL END
|
||
AS '<EFBFBD>ۼƿ<EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --1<><31><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><>ͬ<EFBFBD><CDAC><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>' --<2D><><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>' --<2D><><EFBFBD>ڶ<EFBFBD><DAB6><EFBFBD><EFBFBD>ܶ<EFBFBD>-<2D>˻<EFBFBD><CBBB><EFBFBD><EFBFBD><EFBFBD>൱<EFBFBD>ڼ<EFBFBD><DABC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EEA3AC><EFBFBD><EFBFBD><EFBFBD>ٲ<EFBFBD><D9B2><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ҫ<EFBFBD><D2AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD><EFBFBD>¼<EFBFBD><C2BC><EFBFBD><EFBFBD>һ<EFBFBD>У<EFBFBD>-<2D>տ<EFBFBD><D5BF><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>' --<2D><><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>-<2D><><EFBFBD><EFBFBD>֪ͨ<CDA8><D6AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EEA3AC>Ҫ<EFBFBD><D2AA>ҵ<EFBFBD><D2B5>Ա<EFBFBD><D4B1>̯<EFBFBD><CCAF><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>֣<EFBFBD><D6A3><EFBFBD><EFBFBD>ݹ<EFBFBD><DDB9><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>=<3D><><EFBFBD><EFBFBD>֪ͨ<CDA8><D6AA>ֽ<EFBFBD>ʺ<EFBFBD>ͬ<EFBFBD><CDAC>
|
||
,convert(nvarchar(10),dateadd(day,isnull(w.f_day,0) ,W.F_PMSDate),23) AS 'Ԥ<EFBFBD>Ƶ<EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD><EFBFBD>ƻ<EFBFBD>-PMS<4D><53><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>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS 'ʵ<EFBFBD><EFBFBD><EFBFBD>ѳ<EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD>տ<EFBFBD><EFBFBD>ϼ<EFBFBD>'--<2D><><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>' --<2D><><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>' --<2D><><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>' --<2D><><EFBFBD><EFBFBD>
|
||
,a.F_XMJDD AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD>۽<EFBFBD><DBBD><EFBFBD>
|
||
,convert(nvarchar(10),a.F_XMTSSJ,23) AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --ȡ<><C8A1>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>ʱ<EFBFBD><CAB1>
|
||
,convert(nvarchar(10),a.F_XMYSSJ,23) AS '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱ<EFBFBD><EFBFBD>' --ȡ<><C8A1>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>ʱ<EFBFBD><CAB1>
|
||
,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>' --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D>տ<EFBFBD><D5BF>ƻ<EFBFBD>-<2D>տ<EFBFBD><D5BF><EFBFBD><EFBFBD><EFBFBD><EFBFBD>к<EFBFBD><D0BA><EFBFBD><EFBFBD>ʱ<EFBFBD><CAB1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӧ<EFBFBD><D3A6>Ӧ<EFBFBD>ս<EFBFBD><D5BD><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>' --<2D><><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>' --<2D><><EFBFBD><EFBFBD>
|
||
,w.FSEQ as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
||
--,w.FRECADVANCERATE as '<27>տ<EFBFBD><D5BF><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>' --<2D><><EFBFBD><EFBFBD>
|
||
,convert(nvarchar(100), '') AS '<EFBFBD>ߵ<EFBFBD>ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,convert(nvarchar(100), '') AS 'ʮ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʮ<EFBFBD>˸<EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,convert(nvarchar(100), '') AS 'ʮ<EFBFBD>Ÿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,convert(nvarchar(100), '') AS 'һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><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>' --<2D><><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>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD>-<2D><><EFBFBD><EFBFBD>ͷ-<2D><><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>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>÷<EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS 'ȡֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,case when FIsBadDebt = 1 then '<EFBFBD><EFBFBD>' else '<EFBFBD><EFBFBD>' end AS '<EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD>ϵ<EFBFBD><CFB5>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD>
|
||
,CASE WHEN AB.F_PAPERCONTRACT IS NULL THEN '<EFBFBD><EFBFBD>' else '<EFBFBD><EFBFBD>' end AS '<EFBFBD>Ƿ<EFBFBD>Ԥ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --ȡ<><C8A1><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD>ϵ<EFBFBD><CFB5>Ƿ<EFBFBD>Ԥ<EFBFBD><D4A4><EFBFBD><EFBFBD>
|
||
,'' AS 'Ԥ<EFBFBD>ƻؿ<EFBFBD>ʱ<EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS 'Ԥ<EFBFBD>ƻؿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>E
|
||
,'' AS '<EFBFBD><EFBFBD><EFBFBD>۷<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,'' AS '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ӫר<EFBFBD><EFBFBD>Э<EFBFBD><EFBFBD><EFBFBD>տ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<2D><><EFBFBD><EFBFBD>
|
||
,B.FDATAVALUE as '<EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
||
INTO #TEMP1
|
||
FROM T_SAL_ORDER A --<2D><><EFBFBD>۶<EFBFBD><DBB6><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 --<2D><><EFBFBD>۶<EFBFBD><DBB6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
||
INNER JOIN T_BD_CUSTOMER_L C ON C.FCUSTID= A.FCUSTID AND C.FLOCALEID= 2052 --<2D>ͻ<EFBFBD><CDBB><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 --<2D><>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD>֪ͨ<CDA8><D6AA><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ķ<EFBFBD><C4B7><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 --<2D><>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD>˻<EFBFBD><CBBB><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
|
||
--<2D><><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
|
||
|
||
--<2D><><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,--δ<><CEB4><EFBFBD><EFBFBD><EFBFBD>ܶ<EFBFBD>
|
||
null,null,null,null,null,'','','','','',--<2D><>Ŀ<EFBFBD><C4BF><EFBFBD><EFBFBD>ʱ<EFBFBD><CAB1>
|
||
SUM(<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD>),SUM(t1.<EFBFBD>ʱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>),null,'',null,'',null,--<2D><EFBFBD><D7B6><EFBFBD><EFBFBD><EFBFBD>
|
||
null,null,'','','','','','','','',null,'','',--<2D><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
||
'','','','','','','','','','','','','','','','',--<2D>Ƿ<EFBFBD>Ԥ<EFBFBD><D4A4><EFBFBD><EFBFBD>
|
||
null,null,'','','','',0 Sort
|
||
FROM #TEMP1 t1) t1
|
||
order by Sort desc
|
||
|
||
SELECT * FROM #TEMP2 order by <EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><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
|
||
|