有分支的总明细账

CREATE PROCEDURE “MTC_Print_F_GL_Month_Detail”(
IN FromDate DATETIME,
IN BPLID INT
)
LANGUAGE SQLSCRIPT
AS
BEGIN


DECLARE StratDate DATETIME; ———开始日期
DECLARE EndDate DATETIME; ———截止日期



SELECT YEAR(to_date(FromDate))||’.01.01’ INTO StratDate FROM dummy;
SELECT YEAR(to_date(FromDate))||’.12.31’ INTO EndDate FROM dummy;

TEMP=SELECT U0.”NType”,U0.”RefDate”,U0.”Memo”,U0.”AcctCode”,U0.”AcctName”
,U0.”U_Number”,U0.”AcctName1”,U0.”SupplCode”
,U0.”Debit”,U0.”Credit”,U0.”Balance”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM (
SELECT 1 AS “NType”,T0.”RefDate”,T0.”Memo”,T2.”AcctCode”,T2.”AcctName”
,T0.”U_Number”,T0.”SupplCode”
,T1.”ContraAct”||’:’||T3.”AcctName” AS “AcctName1”
,T1.”Debit”, T1.”Credit”,T1.”Debit”-T1.”Credit” AS “Balance”
,T1.”BPLId”,T1.”BPLName”,T4.”AcctName” AS “FatherName”
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.”TransId”=T1.”TransId”
LEFT JOIN OACT T2 ON T2.”AcctCode”=T1.”Account”
LEFT JOIN OACT T3 ON T3.”AcctCode”=T1.”ContraAct”
LEFT JOIN OACT T4 ON T4.”AcctCode”=LEFT(T2.”AcctCode”,4)
WHERE 1=1
AND T0.”RefDate” BETWEEN :StratDate AND :EndDate
AND T1.”BPLId”=:BPLId

UNION ALL ——本月合计
SELECT 2 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年’||MONTH(T3.”T_RefDate”)||’月合计’ AS “Memo”
,T2.”AcctCode”,T2.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年’||MONTH(T3.”T_RefDate”)||’月合计’ AS “AcctName1”
,SUM(T1.”Debit”),SUM(T1.”Credit”),SUM(T1.”Debit”-T1.”Credit”) AS “Balance”
,T1.”BPLId”,T1.”BPLName”,T4.”AcctName” AS “FatherName”
FROM OJDT T0
JOIN JDT1 T1 ON T0.”TransId”=T1.”TransId”
JOIN OACT T2 ON T2.”AcctCode”=T1.”Account”
JOIN OFPR T3 ON LEFT(T0.”RefDate”,7)=T3.”Code”
LEFT JOIN OACT T4 ON T4.”AcctCode”=LEFT(T2.”AcctCode”,4)
WHERE 1=1
AND T0.”RefDate” BETWEEN :StratDate AND :EndDate
AND T1.”BPLId”=:BPLId
GROUP BY T3.”T_RefDate”,T2.”AcctCode”,T2.”AcctName”,T1.”BPLId”,T1.”BPLName”,T4.”AcctName”
) U0
ORDER BY 4,1,2
;

SELECT U0.”NType”,U0.”RefDate”,U0.”Memo”,U0.”AcctCode”,U0.”AcctName”
,U0.”U_Number”,U0.”SupplCode”,U0.”AcctName1”
,U0.”Debit”,U0.”Credit”,U0.”Balance”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM (
SELECT U0.”NType”,U0.”RefDate”,U0.”Memo”,U0.”AcctCode”,U0.”AcctName”
,U0.”U_Number”,U0.”SupplCode”,U0.”AcctName1”
,U0.”Debit”,U0.”Credit”,U0.”Balance”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年1月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年1月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-01’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-01’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年2月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年2月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-02’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-02’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年3月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年3月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-03’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-03’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年4月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年4月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-04’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-04’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年5月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年5月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-05’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-05’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年6月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年6月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-06’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-06’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年7月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年7月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-07’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-07’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年8月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年8月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-08’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-08’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年9月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年9月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-09’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-09’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年10月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年10月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-10’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-10’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年11月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年11月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-11’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-11’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
UNION ALL
SELECT 3 AS “NType”,T3.”T_RefDate”,YEAR(T3.”T_RefDate”)||’年12月累计’ AS “Memo”
,U0.”AcctCode”,U0.”AcctName”
,’’ AS “U_Number”,’’ AS “SupplCode”
,YEAR(T3.”T_RefDate”)||’年12月累计’ AS “AcctName1”
,SUM(U0.”Debit”),SUM(U0.”Credit”),SUM(U0.”Debit”-U0.”Credit”) AS “Balance”
,U0.”BPLId”,U0.”BPLName”,U0.”FatherName”
FROM :TEMP U0
JOIN OFPR T3 ON YEAR(:StratDate)||’-12’=T3.”Code”
WHERE U0.”NType”=2
AND LEFT(U0.”RefDate”,7)<=YEAR(:StratDate)||’-12’
GROUP BY T3.”T_RefDate”,U0.”AcctCode”,U0.”AcctName”,U0.”BPLId”,U0.”BPLName” ,U0.”FatherName”
)U0
WHERE U0.”RefDate” BETWEEN :StratDate AND :EndDate
ORDER BY U0.”AcctCode”,U0.”RefDate”,U0.”NType”
;

END
—CALL “MTC_Report_F_GL_Month”(‘20200101’,’20200831’)