/*dialect*/
SELECT * FROM
(
select
CASE WHEN '' = '销售组' THEN to_char(SALEGROUP)
WHEN '' = '业务员' THEN to_char(xsy)
WHEN '' = '客户类型' THEN to_char(KHLB)
END AS FZTJ,
KHLB,
xsy,
SJBM,
"上级客户",
FNUMBER,
"二级客户",
-- NVL(A2.FJSAMOUNT,0) AS "期初应收金额应收单",
"期初应收金额出库单",
"期初实收金额",
-- (NVL(A2.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初结欠余额应收单",
"期初结欠余额出库单",
"发货数量",
"发货金额",
"本期实收金额",
"授信限额",
-- NVL(B1.QCKPJEYS,0) AS "期初未开票应收",
"期初未开票出库",
-- "本期应收金额",
"本期已开票",
yfje,
BQHZ,
QCHZ,
"期初应收金额出库单"+"发货金额"-"本期实收金额"-nvl(BQHZ,0)-nvl(QCHZ,0) as "期末余额"
--Y1.QMHZ,
from (
SELECT
A1.xsy,
A1.SALEGROUP,
A1.FParentID,
CASE WHEN A1.SJBM IS NULL THEN A1.FNUMBER ELSE A1.SJBM END AS SJBM,
CASE WHEN A1.SJKH IS NULL THEN A1.FNAME_L2 ELSE A1.SJKH END AS "上级客户",
-- A1.SJBM,
-- A1.SJKH AS "上级客户",
A1.FID,
A1.FNUMBER,
A1.FNAME_L2 AS "二级客户",
A1.KHLB,
-- NVL(A2.FJSAMOUNT,0) AS "期初应收金额应收单",
NVL(A22.FJSAMOUNT,0) AS "期初应收金额出库单",
NVL(A3.FActualAMOUNT,0) AS "期初实收金额",
-- (NVL(A2.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初结欠余额应收单",
(NVL(A22.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初结欠余额出库单",
NVL(A4.FQTY,0) AS "发货数量",
NVL(A4.HSJE,0) AS "发货金额",
NVL(A9.FActualAMOUNT,0) AS "本期实收金额",
NVL(A10.xye,0) AS "授信限额",
-- NVL(B1.QCKPJEYS,0) AS "期初未开票应收",
NVL(BB1.QCKPJECK,0) AS "期初未开票出库",
NVL(AA.BQYSAMOUNT,0) AS "本期应收金额",
NVL(B2.BQKPJE,0) AS "本期已开票",
yf.yfje,
Y.BQHZ,Y2.QCHZ
--Y1.QMHZ,--期初未开票+本期应收-本期开票=期末未开票
FROM
(
--客户
select distinct AA.FID,AA.FNUMBER,AA.FNAME_L2,AA.FParentID,
CASE WHEN BB.FID IS NULL THEN AA.FID ELSE BB.FID END AS SJFID,
BB.FNUMBER AS SJBM,BB.FNAME_L2 AS SJKH,b.xsy,e.fname_l2 as SALEGROUP,
Q.DW AS KHLB
from T_BD_Customer AA
left join khlb Q on Q.FID = AA.FID
--left join T_BD_CSSPGroup Q ON Q.FID =AA.FBrowseGroupID
LEFT JOIN (select AA.FID,AA.FNUMBER,AA.FNAME_L2
from T_BD_Customer AA where AA.FControlUnitID= '00000000-0000-0000-0000-000000000000CCE7AED4') BB ON BB.FID = AA.FParentID
left join (
SELECT a.*,c.fname_l2 as xsy FROM T_BD_CustomerSaleInfo a
left join T_BD_CustomerSaler b on a.fid = b.FCustomerSaleID
left join T_BD_Person c on c.fid = b.FPersonID
where a.FSaleOrgID = 'sREAAAAAB9LM567U'
) b on b.FCustomerID = AA.fid
left join T_BD_SALEGROUP e on e.fid = b.CFSALEGROUPID
where
( b.CFSalegroupID in ( '') OR null IS NULL ) and
( b.FSaleOrgID= 'sREAAAAAB9LM567U' ) and
AA.FControlUnitID='00000000-0000-0000-0000-000000000000CCE7AED4'
) A1
LEFT JOIN (
SELECT
FAcctCussentID,0 AS SL,SUM(BQHZ) AS BQHZ
FROM
(
---本期的应收冲应付---select
b.FAcctCussID as FAcctCussentID,
sum(b.FThisVerificateAmtLoc) as BQHZ
from T_AR_VerificationBill a
inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
where
( b.FCurrencyID = '' OR null IS NULL )
and b.FBillSubType = 2
AND to_char(a.FBizDate,'YYYY-MM-DD')>= '2016-04-01'
and to_char(a.FBizDate,'YYYY-MM-DD')<= '2017-04-03'
and a.FVerificationType in (103,109)
and a.FControlUnitID = 'sREAAAAAB9LM567U'
group by b.FAcctCussID
)
GROUP BY FAcctCussentID
) Y ON A1.FID =Y.FAcctCussentID/*LEFT JOIN (
--坏账损失
select T1.FAcctCussentID,0 AS SL,
SUM(T1.FBadAmountsLocal)-SUM(T1.FTakeBackAmountsLocal) AS QMHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') <='2017-04-03'
AND (T3.FNUMBER ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
) Y1 ON A1.FID =Y1.FAcctCussentID*/LEFT JOIN (
SELECT
FAcctCussentID,0 AS SL,sum(QCHZ) as QCHZ
FROM (
--坏账损失
select T1.FAcctCussentID,
SUM(T1.FBadAmountsLocal)-SUM(T1.FTakeBackAmountsLocal) AS QCHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') >= '2016-04-01'
and TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') <= '2017-04-03'
AND (T3.FID ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
---期初的应收冲应付---
--union all
--select
-- b.FAcctCussID,
-- sum(b.FThisVerificateAmtLoc) as FThisVerificateAmtLoc
--from T_AR_VerificationBill a
--inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
--where
-- ( b.FCurrencyID = '' OR null IS NULL )
--and b.FBillSubType = 2
--AND to_char(a.FBizDate,'YYYY-MM-DD') < '2016-04-01'
--and a.FVerificationType in (103,109)
--and a.FControlUnitID = 'sREAAAAAB9LM567U'
--group by b.FAcctCussID
)
group by FAcctCussentID) Y2 ON A1.FID =Y2.FAcctCussentID
LEFT JOIN
(
--起初应收余额(应收单)select T11.FAsstActID,SUM(T11.FJSAMOUNT) AS FJSAMOUNT from(
select FAsstActID,FRecievePayAmountLocal AS FJSAMOUNT
from T_AR_OtherBill_Define
where
(fyear < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') or
fyear = to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') and fmonth < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'MM'))
and FCompanyID = 'sREAAAAAB9LM567U'union allSELECT T1.FAsstActID,
T2.FRecievePayAmountLocal AS FJSAMOUNT
FROM T_AR_OtherBill T1
INNER JOIN T_AR_OtherBillentry T2 ON T1.FID=T2.FParentID where ( T1.FBillStatus =3 )
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01' )
AND (to_char(T1.FBizDate,'YYYY-MM-DD') >= to_char(add_months(last_day(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'))+1,-1),'yyyy-MM-dd') )
AND ( T1.FCompanyID = 'sREAAAAAB9LM567U' )
) T11
GROUP BY T11.FAsstActID) A2 ON A1.FID = A2.FAsstActIDLEFT JOIN
(
--起初应收余额(出库单)
--1、其他应收单+销售出库单
select
FAsstActID,
SUM(FJSAMOUNT) AS FJSAMOUNT
from (SELECT FAsstActID,SUM(FAmountLocal) AS FJSAMOUNT
FROM OtherBill_Define
WHERE FCompanyID = 'sREAAAAAB9LM567U'
GROUP BY FAsstActIDunion all---深圳的初始化销售出库单SELECT T1.FCustomerID as FAsstActID,
SUM(T2.FBalanceCustomerID) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where ( T1.FBaseStatus = 4 )
and NVL(T1.FIsInitBill,0) = 1
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
AND T1.FStorageOrgUnitID <> 'sREAAAAACBDM567U'
AND T1.FStorageOrgUnitID <> 'sREAAAAACE7M567U'
AND T1.FStorageOrgUnitID = 'sREAAAAACUbM567U'
GROUP BY T1.FCustomerID
union all---上海的初始化销售出库单SELECT T1.FCustomerID as FAsstActID,
SUM(T2.FBalanceCustomerID) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where ( T1.FBaseStatus = 4 )
and NVL(T1.FIsInitBill,0) = 1
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
AND T1.FStorageOrgUnitID <> 'sREAAAAACBDM567U'
AND T1.FStorageOrgUnitID <> 'sREAAAAACE7M567U'
AND T1.FStorageOrgUnitID = 'sREAAAAACIzM567U'
GROUP BY T1.FCustomerIDunion allselect FBalanceCustomerID as FAsstActID,SUM(FLocalAmount) AS FJSAMOUNT
from T_IM_SaleIssueBill_Define
where
(fyear < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') or
fyear = to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') and fmonth < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'MM'))
and NVL(fisinitbill,0) = 0
and
( FStorageOrgUnitID = 'sREAAAAAB9LM567U')
GROUP BY FBalanceCustomerIDunion all
--坏账损失
select T1.FAcctCussentID,
SUM(-T1.FBadAmountsLocal)-SUM(-T1.FTakeBackAmountsLocal) AS QCHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') < '2016-04-01'
AND (T3.FID ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
union all
select
b.FAcctCussID,
sum(-b.FThisVerificateAmtLoc) as FThisVerificateAmtLoc
from T_AR_VerificationBill a
inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
where
b.FBillSubType = 2
AND to_char(a.FBizDate,'YYYY-MM-DD') < '2016-04-01'
and a.FVerificationType in (103,109)
and a.FControlUnitID = 'sREAAAAAB9LM567U'
group by b.FAcctCussID
union allSELECT T2.FBalanceCustomerID as FAsstActID,
SUM(T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND (to_char(T1.FBizDate,'YYYY-MM-DD') >= to_char(add_months(last_day(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'))+1,-1),'yyyy-MM-dd') )
AND ( T1.FBaseStatus = 4 )
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and NVL(T1.fisinitbill,0) = 0
GROUP BY T2.FBalanceCustomerID--预返剔除(电器)
--初始化应收单+期初出库单
union all
SELECT T1.FAsstActID,
SUM(-T2.FRecievePayAmountLocal) AS FJSAMOUNT
FROM T_AR_OtherBill T1
INNER JOIN T_AR_OtherBillentry T2 ON T1.FID=T2.FParentID
where ( T1.FBillStatus =3 )
AND T2.FMaterialID = 'sREAAAJFyVdECefw'
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01' )
AND ( T1.FCompanyID = 'sREAAAAAB9LM567U' )
AND T1.FIsInitializeBill = 1
GROUP BY T1.FAsstActIDunion all
--剔除预返
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(-T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FBaseStatus = 4 )
AND T2.FMaterialID = 'sREAAAJFyVdECefw'
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and NVL(T1.fisinitbill,0) = 0
GROUP BY T2.FBalanceCustomerIDunion all
--电器期初发出商品
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FBaseStatus = 4 )
AND ( T1.FStorageOrgUnitID = 'sREAAADZGt7M567U')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and T1.fisinitbill = 1
GROUP BY T2.FBalanceCustomerIDunion all
--固定资产清理
select
A.FAsstActID,
SUM(B.FRecievePayAmountLocal) AS FAmountLocal
from T_AR_OtherBill A
INNER JOIN T_AR_OtherBillentry B ON A.FID = B.FPARENTID
WHERE B.FExpenseItemID = 'bjWzSkEJABjgU38AAAEAGHjkvJQ='
AND A.FCompanyID = 'sREAAAAAB9LM567U'
AND to_char(A.FBizDate,'YYYY-MM-DD') < '2016-04-01'
GROUP BY A.FAsstActID
)
group by FAsstActID
) A22 ON A1.FID = A22.FAsstActIDLEFT JOIN
(select T11.FAsstActID,SUM(T11.BQYSAMOUNT) AS BQYSAMOUNT from(
select FAsstActID,FRecievePayAmountLocal AS BQYSAMOUNT
from T_AR_Ot
SELECT * FROM
(
select
CASE WHEN '' = '销售组' THEN to_char(SALEGROUP)
WHEN '' = '业务员' THEN to_char(xsy)
WHEN '' = '客户类型' THEN to_char(KHLB)
END AS FZTJ,
KHLB,
xsy,
SJBM,
"上级客户",
FNUMBER,
"二级客户",
-- NVL(A2.FJSAMOUNT,0) AS "期初应收金额应收单",
"期初应收金额出库单",
"期初实收金额",
-- (NVL(A2.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初结欠余额应收单",
"期初结欠余额出库单",
"发货数量",
"发货金额",
"本期实收金额",
"授信限额",
-- NVL(B1.QCKPJEYS,0) AS "期初未开票应收",
"期初未开票出库",
-- "本期应收金额",
"本期已开票",
yfje,
BQHZ,
QCHZ,
"期初应收金额出库单"+"发货金额"-"本期实收金额"-nvl(BQHZ,0)-nvl(QCHZ,0) as "期末余额"
--Y1.QMHZ,
from (
SELECT
A1.xsy,
A1.SALEGROUP,
A1.FParentID,
CASE WHEN A1.SJBM IS NULL THEN A1.FNUMBER ELSE A1.SJBM END AS SJBM,
CASE WHEN A1.SJKH IS NULL THEN A1.FNAME_L2 ELSE A1.SJKH END AS "上级客户",
-- A1.SJBM,
-- A1.SJKH AS "上级客户",
A1.FID,
A1.FNUMBER,
A1.FNAME_L2 AS "二级客户",
A1.KHLB,
-- NVL(A2.FJSAMOUNT,0) AS "期初应收金额应收单",
NVL(A22.FJSAMOUNT,0) AS "期初应收金额出库单",
NVL(A3.FActualAMOUNT,0) AS "期初实收金额",
-- (NVL(A2.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初结欠余额应收单",
(NVL(A22.FJSAMOUNT,0) - NVL(A3.FActualAMOUNT,0)) as "期初结欠余额出库单",
NVL(A4.FQTY,0) AS "发货数量",
NVL(A4.HSJE,0) AS "发货金额",
NVL(A9.FActualAMOUNT,0) AS "本期实收金额",
NVL(A10.xye,0) AS "授信限额",
-- NVL(B1.QCKPJEYS,0) AS "期初未开票应收",
NVL(BB1.QCKPJECK,0) AS "期初未开票出库",
NVL(AA.BQYSAMOUNT,0) AS "本期应收金额",
NVL(B2.BQKPJE,0) AS "本期已开票",
yf.yfje,
Y.BQHZ,Y2.QCHZ
--Y1.QMHZ,--期初未开票+本期应收-本期开票=期末未开票
FROM
(
--客户
select distinct AA.FID,AA.FNUMBER,AA.FNAME_L2,AA.FParentID,
CASE WHEN BB.FID IS NULL THEN AA.FID ELSE BB.FID END AS SJFID,
BB.FNUMBER AS SJBM,BB.FNAME_L2 AS SJKH,b.xsy,e.fname_l2 as SALEGROUP,
Q.DW AS KHLB
from T_BD_Customer AA
left join khlb Q on Q.FID = AA.FID
--left join T_BD_CSSPGroup Q ON Q.FID =AA.FBrowseGroupID
LEFT JOIN (select AA.FID,AA.FNUMBER,AA.FNAME_L2
from T_BD_Customer AA where AA.FControlUnitID= '00000000-0000-0000-0000-000000000000CCE7AED4') BB ON BB.FID = AA.FParentID
left join (
SELECT a.*,c.fname_l2 as xsy FROM T_BD_CustomerSaleInfo a
left join T_BD_CustomerSaler b on a.fid = b.FCustomerSaleID
left join T_BD_Person c on c.fid = b.FPersonID
where a.FSaleOrgID = 'sREAAAAAB9LM567U'
) b on b.FCustomerID = AA.fid
left join T_BD_SALEGROUP e on e.fid = b.CFSALEGROUPID
where
( b.CFSalegroupID in ( '') OR null IS NULL ) and
( b.FSaleOrgID= 'sREAAAAAB9LM567U' ) and
AA.FControlUnitID='00000000-0000-0000-0000-000000000000CCE7AED4'
) A1
LEFT JOIN (
SELECT
FAcctCussentID,0 AS SL,SUM(BQHZ) AS BQHZ
FROM
(
---本期的应收冲应付---select
b.FAcctCussID as FAcctCussentID,
sum(b.FThisVerificateAmtLoc) as BQHZ
from T_AR_VerificationBill a
inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
where
( b.FCurrencyID = '' OR null IS NULL )
and b.FBillSubType = 2
AND to_char(a.FBizDate,'YYYY-MM-DD')>= '2016-04-01'
and to_char(a.FBizDate,'YYYY-MM-DD')<= '2017-04-03'
and a.FVerificationType in (103,109)
and a.FControlUnitID = 'sREAAAAAB9LM567U'
group by b.FAcctCussID
)
GROUP BY FAcctCussentID
) Y ON A1.FID =Y.FAcctCussentID/*LEFT JOIN (
--坏账损失
select T1.FAcctCussentID,0 AS SL,
SUM(T1.FBadAmountsLocal)-SUM(T1.FTakeBackAmountsLocal) AS QMHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') <='2017-04-03'
AND (T3.FNUMBER ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
) Y1 ON A1.FID =Y1.FAcctCussentID*/LEFT JOIN (
SELECT
FAcctCussentID,0 AS SL,sum(QCHZ) as QCHZ
FROM (
--坏账损失
select T1.FAcctCussentID,
SUM(T1.FBadAmountsLocal)-SUM(T1.FTakeBackAmountsLocal) AS QCHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') >= '2016-04-01'
and TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') <= '2017-04-03'
AND (T3.FID ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
---期初的应收冲应付---
--union all
--select
-- b.FAcctCussID,
-- sum(b.FThisVerificateAmtLoc) as FThisVerificateAmtLoc
--from T_AR_VerificationBill a
--inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
--where
-- ( b.FCurrencyID = '' OR null IS NULL )
--and b.FBillSubType = 2
--AND to_char(a.FBizDate,'YYYY-MM-DD') < '2016-04-01'
--and a.FVerificationType in (103,109)
--and a.FControlUnitID = 'sREAAAAAB9LM567U'
--group by b.FAcctCussID
)
group by FAcctCussentID) Y2 ON A1.FID =Y2.FAcctCussentID
LEFT JOIN
(
--起初应收余额(应收单)select T11.FAsstActID,SUM(T11.FJSAMOUNT) AS FJSAMOUNT from(
select FAsstActID,FRecievePayAmountLocal AS FJSAMOUNT
from T_AR_OtherBill_Define
where
(fyear < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') or
fyear = to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') and fmonth < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'MM'))
and FCompanyID = 'sREAAAAAB9LM567U'union allSELECT T1.FAsstActID,
T2.FRecievePayAmountLocal AS FJSAMOUNT
FROM T_AR_OtherBill T1
INNER JOIN T_AR_OtherBillentry T2 ON T1.FID=T2.FParentID where ( T1.FBillStatus =3 )
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01' )
AND (to_char(T1.FBizDate,'YYYY-MM-DD') >= to_char(add_months(last_day(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'))+1,-1),'yyyy-MM-dd') )
AND ( T1.FCompanyID = 'sREAAAAAB9LM567U' )
) T11
GROUP BY T11.FAsstActID) A2 ON A1.FID = A2.FAsstActIDLEFT JOIN
(
--起初应收余额(出库单)
--1、其他应收单+销售出库单
select
FAsstActID,
SUM(FJSAMOUNT) AS FJSAMOUNT
from (SELECT FAsstActID,SUM(FAmountLocal) AS FJSAMOUNT
FROM OtherBill_Define
WHERE FCompanyID = 'sREAAAAAB9LM567U'
GROUP BY FAsstActIDunion all---深圳的初始化销售出库单SELECT T1.FCustomerID as FAsstActID,
SUM(T2.FBalanceCustomerID) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where ( T1.FBaseStatus = 4 )
and NVL(T1.FIsInitBill,0) = 1
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
AND T1.FStorageOrgUnitID <> 'sREAAAAACBDM567U'
AND T1.FStorageOrgUnitID <> 'sREAAAAACE7M567U'
AND T1.FStorageOrgUnitID = 'sREAAAAACUbM567U'
GROUP BY T1.FCustomerID
union all---上海的初始化销售出库单SELECT T1.FCustomerID as FAsstActID,
SUM(T2.FBalanceCustomerID) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where ( T1.FBaseStatus = 4 )
and NVL(T1.FIsInitBill,0) = 1
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
AND T1.FStorageOrgUnitID <> 'sREAAAAACBDM567U'
AND T1.FStorageOrgUnitID <> 'sREAAAAACE7M567U'
AND T1.FStorageOrgUnitID = 'sREAAAAACIzM567U'
GROUP BY T1.FCustomerIDunion allselect FBalanceCustomerID as FAsstActID,SUM(FLocalAmount) AS FJSAMOUNT
from T_IM_SaleIssueBill_Define
where
(fyear < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') or
fyear = to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'YYYY') and fmonth < to_char(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'),'MM'))
and NVL(fisinitbill,0) = 0
and
( FStorageOrgUnitID = 'sREAAAAAB9LM567U')
GROUP BY FBalanceCustomerIDunion all
--坏账损失
select T1.FAcctCussentID,
SUM(-T1.FBadAmountsLocal)-SUM(-T1.FTakeBackAmountsLocal) AS QCHZ
from T_AR_BadAcct T1
INNER JOIN T_ORG_Company T3 ON T1.FCompanyID=T3.FID
where TO_CHAR(T1.FBadAcctDate,'YYYY-MM-DD') < '2016-04-01'
AND (T3.FID ='sREAAAAAB9LM567U' )
GROUP BY T1.FAcctCussentID
union all
select
b.FAcctCussID,
sum(-b.FThisVerificateAmtLoc) as FThisVerificateAmtLoc
from T_AR_VerificationBill a
inner join T_AR_VerificationBillentry b on a.fid = b.fparentid
where
b.FBillSubType = 2
AND to_char(a.FBizDate,'YYYY-MM-DD') < '2016-04-01'
and a.FVerificationType in (103,109)
and a.FControlUnitID = 'sREAAAAAB9LM567U'
group by b.FAcctCussID
union allSELECT T2.FBalanceCustomerID as FAsstActID,
SUM(T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND (to_char(T1.FBizDate,'YYYY-MM-DD') >= to_char(add_months(last_day(to_date('2016-04-01', 'yyyy-mm-dd HH24:mi:ss'))+1,-1),'yyyy-MM-dd') )
AND ( T1.FBaseStatus = 4 )
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and NVL(T1.fisinitbill,0) = 0
GROUP BY T2.FBalanceCustomerID--预返剔除(电器)
--初始化应收单+期初出库单
union all
SELECT T1.FAsstActID,
SUM(-T2.FRecievePayAmountLocal) AS FJSAMOUNT
FROM T_AR_OtherBill T1
INNER JOIN T_AR_OtherBillentry T2 ON T1.FID=T2.FParentID
where ( T1.FBillStatus =3 )
AND T2.FMaterialID = 'sREAAAJFyVdECefw'
AND (to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01' )
AND ( T1.FCompanyID = 'sREAAAAAB9LM567U' )
AND T1.FIsInitializeBill = 1
GROUP BY T1.FAsstActIDunion all
--剔除预返
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(-T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FBaseStatus = 4 )
AND T2.FMaterialID = 'sREAAAJFyVdECefw'
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and NVL(T1.fisinitbill,0) = 0
GROUP BY T2.FBalanceCustomerIDunion all
--电器期初发出商品
SELECT T2.FBalanceCustomerID as FAsstActID,
SUM(T2.FLocalAmount) AS FJSAMOUNT
FROM T_IM_SaleIssueBill T1
INNER JOIN T_IM_SaleIssueEntry T2 ON T1.FID=T2.FParentID
where
(to_char(T1.FBizDate,'YYYY-MM-DD') < '2016-04-01')
AND ( T1.FBaseStatus = 4 )
AND ( T1.FStorageOrgUnitID = 'sREAAADZGt7M567U')
AND ( T1.FStorageOrgUnitID = 'sREAAAAAB9LM567U')
and T1.fisinitbill = 1
GROUP BY T2.FBalanceCustomerIDunion all
--固定资产清理
select
A.FAsstActID,
SUM(B.FRecievePayAmountLocal) AS FAmountLocal
from T_AR_OtherBill A
INNER JOIN T_AR_OtherBillentry B ON A.FID = B.FPARENTID
WHERE B.FExpenseItemID = 'bjWzSkEJABjgU38AAAEAGHjkvJQ='
AND A.FCompanyID = 'sREAAAAAB9LM567U'
AND to_char(A.FBizDate,'YYYY-MM-DD') < '2016-04-01'
GROUP BY A.FAsstActID
)
group by FAsstActID
) A22 ON A1.FID = A22.FAsstActIDLEFT JOIN
(select T11.FAsstActID,SUM(T11.BQYSAMOUNT) AS BQYSAMOUNT from(
select FAsstActID,FRecievePayAmountLocal AS BQYSAMOUNT
from T_AR_Ot
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货