details: https://code.openbravo.com/erp/stable/2.50/rev/3dfbee5689ff
changeset: 9600:3dfbee5689ff
user: Jon Alegría <jon.alegria <at> openbravo.com>
date: Fri Jul 29 13:49:22 2011 +0200
summary: Fixes issue 0017768: General ledge report is not showing correctly the
information
diffstat:
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql | 24 ++++++---
1 files changed, 15 insertions(+), 9 deletions(-)
diffs (72 lines):
diff -r e8b3cacc2fa1 -r 3dfbee5689ff src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Fri Jul 29 13:03:50 2011 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Fri Jul 29 13:49:22 2011 +0200
@@ -35,7 +35,8 @@
0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL,
0 AS FINALDEBIT, 0 AS FINALCREDIT, 0 AS FINALTOTAL,
TO_CHAR(DATEACCT,'J') AS DATEACCTNUMBER,
- ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname
+ ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname,
+ CASE WHEN FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACTACCTTYPE = 'C' THEN 4 END) END) END) END AS FACTACCTTYPE
FROM
(SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME,
DATEACCT, AMTACCTDR, AMTACCTCR,
@@ -50,7 +51,7 @@
WHEN 'Product' THEN (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end)
WHEN 'Project' THEN (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end)
ELSE '' END AS groupbyname,
- CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT
+ CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT, FACT_ACCT.FACTACCTTYPE
FROM FACT_ACCT
LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
@@ -76,12 +77,16 @@
AND 2=2
AND 3=3) D
WHERE 6=6
- GROUP BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
+ GROUP BY groupbyname, groupbyid, VALUE, NAME, ID,
+ CASE WHEN FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACTACCTTYPE = 'C' THEN 4 END) END) END) END,
+ DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0
- ORDER BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
+ ORDER BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT,
+ CASE WHEN FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACTACCTTYPE = 'C' THEN 4 END) END) END) END
+ , FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
) C
) B WHERE 1=1
- ) A ORDER BY groupbyname, groupbyid, VALUE, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
+ ) A ORDER BY groupbyname, groupbyid, VALUE, DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT
]]></Sql>
<Field name="rownum" value="count"/>
<Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
@@ -126,14 +131,15 @@
SELECT VALUE, NAME, DATEACCT,
SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR,
FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname,
- DESCRIPTION
+ DESCRIPTION, FACTACCTTYPE
FROM
(SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME,
DATEACCT, AMTACCTDR, AMTACCTCR,
FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION,
c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid,
(case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) as bpname, (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) as pdname, (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) as pjname,
- CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT
+ CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT,
+ CASE WHEN FACT_ACCT.FACTACCTTYPE = 'O' THEN 1 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'N' THEN 2 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'R' THEN 3 ELSE (CASE WHEN FACT_ACCT.FACTACCTTYPE = 'C' THEN 4 END) END) END) END AS FACTACCTTYPE
FROM FACT_ACCT
LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
@@ -159,9 +165,9 @@
AND 2=2
AND 3=3) D
WHERE 6=6
- GROUP BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT
+ GROUP BY VALUE, NAME, ID, FACTACCTTYPE, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT
HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0
- ORDER BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT
+ ORDER BY VALUE, NAME, ID, DATEACCT, FACTACCTTYPE, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT
]]></Sql>
<Field name="rownum" value="count"/>
<Parameter name="adLanguage"/>
|