details: /erp/devel/pi/rev/d08feaca26c7
changeset: 8195:d08feaca26c7
user: Sathiyan Sivaprakasam <sathiyan.sivaprakasam <at> openbravo.com>
date: Sat Aug 21 18:35:56 2010 +0530
summary: Fixes Issue 14234 : Grouping in Journal is not correct
diffstat:
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql |
34 +++------
1 files changed, 13 insertions(+), 21 deletions(-)
diffs (100 lines):
diff -r 53317f50c6f9 -r d08feaca26c7
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
---
a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
Fri Aug 20 17:54:55 2010 +0200
+++
b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
Sat Aug 21 18:35:56 2010 +0530
@@ -30,10 +30,7 @@
<![CDATA[
SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID,
AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
(CASE FACTACCTTYPE WHEN 'O' THEN 1 WHEN 'N' THEN 2 WHEN 'R' THEN 3 ELSE
4 END) AS FACTACCTTYPE2,
- (CASE WHEN AMTACCTDR=0 THEN NULL WHEN ABS(AMTACCTDR)>=ABS(AMTACCTCR)
THEN
- (CASE WHEN SIGN(AMTACCTDR)=SIGN(AMTACCTCR) THEN AMTACCTDR- AMTACCTCR
ELSE SIGN(AMTACCTDR)*(ABS(AMTACCTDR)- ABS(AMTACCTCR)) END) ELSE NULL END) AS
AMTACCTDR,
- (CASE WHEN AMTACCTCR=0 THEN NULL WHEN ABS(AMTACCTCR)>ABS(AMTACCTDR)
THEN
- (CASE WHEN SIGN(AMTACCTCR)=SIGN(AMTACCTDR) THEN AMTACCTCR-AMTACCTDR ELSE
SIGN(AMTACCTCR)*(ABS(AMTACCTCR)- ABS(AMTACCTDR)) END) ELSE NULL END) AS
AMTACCTCR,
+ (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE
AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR,
'' AS GROUPEDLINES
FROM
(SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME,
F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
@@ -47,7 +44,8 @@
AND F.FactAcctType IN ('C','N','O','R')
AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT,
F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID,
- F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE) AA
+ F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
+ (CASE F.AMTACCTDR WHEN 0 THEN 1 ELSE 2 END)) AA
ORDER BY SCHEMA_NAME, DATEACCT, FACTACCTTYPE2, IDENTIFIER, SEQNO
]]></Sql>
<Field name="rownum" value="count"/>
@@ -71,10 +69,7 @@
FROM
(
SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID,
AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
- (CASE WHEN AMTACCTDR=0 THEN NULL WHEN ABS(AMTACCTDR)>=ABS(AMTACCTCR)
THEN
- (CASE WHEN SIGN(AMTACCTDR)=SIGN(AMTACCTCR) THEN AMTACCTDR- AMTACCTCR
ELSE SIGN(AMTACCTDR)*(ABS(AMTACCTDR)- ABS(AMTACCTCR)) END) ELSE NULL END) AS
AMTACCTDR,
- (CASE WHEN AMTACCTCR=0 THEN NULL WHEN ABS(AMTACCTCR)>ABS(AMTACCTDR)
THEN
- (CASE WHEN SIGN(AMTACCTCR)=SIGN(AMTACCTDR) THEN AMTACCTCR-AMTACCTDR
ELSE SIGN(AMTACCTCR)*(ABS(AMTACCTCR)- ABS(AMTACCTDR)) END) ELSE NULL END) AS
AMTACCTCR
+ (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR,
(CASE AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
FROM
(SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME,
F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID,
F.AD_TABLE_ID, F.DOCBASETYPE,
@@ -87,7 +82,8 @@
AND F.FactAcctType IN ('C','N','O','R')
AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT,
F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID,
- F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID) AA
+ F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,
+ (CASE F.AMTACCTDR WHEN 0 THEN 1 ELSE 2 END)) AA
ORDER BY SCHEMA_NAME, DATEACCT, IDENTIFIER, SEQNO
) BB
GROUP BY IDENTIFIER, SCHEMA_NAME, DATEACCT
@@ -122,7 +118,7 @@
AND F.AD_ORG_ID IN('1')
AND 1=1
AND f.AD_ORG_ID IN('2')
- AND F.FactAcctType IN ('C','N','O','R')
+ AND F.FactAcctType IN ('C','N','O','R')
AND (CASE (SELECT MAX(ISSOTRX) FROM C_DOCTYPE D
WHERE D.DOCBASETYPE = F.DOCBASETYPE) WHEN 'N' THEN
COALESCE(T.PO_WINDOW_ID, T.AD_WINDOW_ID) ELSE T.AD_WINDOW_ID END) =
W.AD_WINDOW_ID
GROUP BY F.DATEACCT, F.FACT_ACCT_GROUP_ID) AA
@@ -154,10 +150,7 @@
<![CDATA[
SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID,
AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
(CASE FACTACCTTYPE WHEN 'O' THEN 1 WHEN 'N' THEN 2 WHEN 'R' THEN 3 ELSE
4 END) AS FACTACCTTYPE2,
- (CASE WHEN AMTACCTDR=0 THEN NULL WHEN ABS(AMTACCTDR)>=ABS(AMTACCTCR)
THEN
- (CASE WHEN SIGN(AMTACCTDR)=SIGN(AMTACCTCR) THEN AMTACCTDR- AMTACCTCR
ELSE SIGN(AMTACCTDR)*(ABS(AMTACCTDR)- ABS(AMTACCTCR)) END) ELSE NULL END) AS
AMTACCTDR,
- (CASE WHEN AMTACCTCR=0 THEN NULL WHEN ABS(AMTACCTCR)>ABS(AMTACCTDR)
THEN
- (CASE WHEN SIGN(AMTACCTCR)=SIGN(AMTACCTDR) THEN AMTACCTCR-AMTACCTDR ELSE
SIGN(AMTACCTCR)*(ABS(AMTACCTCR)- ABS(AMTACCTDR)) END) ELSE NULL END) AS
AMTACCTCR
+ (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE
AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
FROM
(SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME,
F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID,
F.AD_TABLE_ID, F.DOCBASETYPE,
@@ -168,7 +161,8 @@
AND 1=1
AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT,
F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID,
- F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE) AA
+ F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
+ (CASE F.AMTACCTDR WHEN 0 THEN 1 ELSE 2 END)) AA
ORDER BY SCHEMA_NAME, DATEACCT, FACTACCTTYPE2, IDENTIFIER, SEQNO
]]></Sql>
<Field name="rownum" value="count"/>
@@ -213,10 +207,7 @@
<![CDATA[
SELECT SCHEMA_ID, SCHEMA_NAME, IDENTIFIER, DATEACCT, VALUE, NAME, ID,
AD_TABLE_ID, DOCBASETYPE, SEQNO, '' AS TOTAL, '' AS DESCRIPTION,
(CASE FACTACCTTYPE WHEN 'O' THEN 1 WHEN 'N' THEN 2 WHEN 'R' THEN 3 ELSE
4 END) AS FACTACCTTYPE2,
- (CASE WHEN AMTACCTDR=0 THEN NULL WHEN ABS(AMTACCTDR)>=ABS(AMTACCTCR)
THEN
- (CASE WHEN SIGN(AMTACCTDR)=SIGN(AMTACCTCR) THEN AMTACCTDR- AMTACCTCR
ELSE SIGN(AMTACCTDR)*(ABS(AMTACCTDR)- ABS(AMTACCTCR)) END) ELSE NULL END) AS
AMTACCTDR,
- (CASE WHEN AMTACCTCR=0 THEN NULL WHEN ABS(AMTACCTCR)>ABS(AMTACCTDR)
THEN
- (CASE WHEN SIGN(AMTACCTCR)=SIGN(AMTACCTDR) THEN AMTACCTCR-AMTACCTDR ELSE
SIGN(AMTACCTCR)*(ABS(AMTACCTCR)- ABS(AMTACCTDR)) END) ELSE NULL END) AS
AMTACCTCR
+ (CASE AMTACCTDR WHEN 0 THEN NULL ELSE AMTACCTDR END) AS AMTACCTDR, (CASE
AMTACCTCR WHEN 0 THEN NULL ELSE AMTACCTCR END) AS AMTACCTCR
FROM
(SELECT F.C_ACCTSCHEMA_ID AS SCHEMA_ID, SC.NAME AS SCHEMA_NAME,
F.FACT_ACCT_GROUP_ID AS IDENTIFIER, F.DATEACCT,
F.ACCTVALUE AS VALUE, F.ACCTDESCRIPTION AS NAME,F.RECORD_ID AS ID,
F.AD_TABLE_ID, F.DOCBASETYPE,
@@ -227,7 +218,8 @@
AND f.C_ACCTSCHEMA_ID = SC.C_ACCTSCHEMA_ID
AND F.Fact_Acct_Group_ID = ?
GROUP BY f.C_ACCTSCHEMA_ID, SC.NAME, F.AD_TABLE_ID, F.DATEACCT,
F.ACCTDESCRIPTION, F.ACCTVALUE, F.DOCBASETYPE, F.RECORD_ID,
- F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE) AA
+ F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
+ (CASE F.AMTACCTDR WHEN 0 THEN 1 ELSE 2 END)) AA
ORDER BY SCHEMA_NAME, DATEACCT, FACTACCTTYPE2, IDENTIFIER, SEQNO
]]></Sql>
<Field name="rownum" value="count"/>
------------------------------------------------------------------------------
This SF.net email is sponsored by
Make an app they can't live without
Enter the BlackBerry Developer Challenge
http://p.sf.net/sfu/RIM-dev2dev
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
