WITH X (CURRENCY_CODE, AR_CONTROL_ACCT, CLIENT_ID, BILL_NUMBER) AS
(
SELECT
A.CURRENCY_CODE,
A.AR_CONTROL_ACCT,
A.CLIENT_ID,
A.BILL_NUMBER
FROM
AR_TRANSACTIONS_ALL A
WHERE
A.SOURCE_AUDIT <> 0
AND A.TX_POSTED = 'True'
AND A.TX_DATE <= TIMESTAMP('{0}', '23.59.59')
GROUP BY
A.CURRENCY_CODE, A.AR_CONTROL_ACCT, A.CLIENT_ID, A.BILL_NUMBER
HAVING
ROUND(SUM(A.TX_AMT),2) <> 0
)
SELECT
1 RECTYPE,
A.CLIENT_ID,
C.NAME,
A.CURRENCY_CODE,
(SELECT NAME FROM UNIT WHERE UNIT_TYPE = 'Y' AND UNIT_SYMBOL =
A.CURRENCY_CODE FETCH FIRST ROW ONLY) CURRENCY_NAME,
A.AR_CONTROL_ACCT,
A.BILL_NUMBER,
(SELECT DATE(AGING_DATE) FROM AR_SUM_ALL WHERE CLIENT_ID = A.CLIENT_ID
AND BILL_NUMBER = A.BILL_NUMBER FETCH FIRST ROW ONLY) AGING_DATE,
A.TX_TYPE,
DATE(A.TX_DATE) TX_DATE,
A.TX_AMT,
USER USER_ID,
A.COMPANY_ID,
P.NAME COMPANY_NAME,
C.ALT_CONTACT BILL_NAME,
C.ALT_BUSINESS_PHONE BILL_PHONE,
(C.NAME || ' - ' || A.CLIENT_ID) CLIENT_GROUP
FROM
X, COMPANY_INFO P, CLIENT_ALL C, AR_TRANSACTIONS_ALL A
WHERE
P.COMPANY_INFO_ID = A.COMPANY_ID
AND C.CLIENT_ID = A.CLIENT_ID
AND X.CURRENCY_CODE = A.CURRENCY_CODE
AND X.AR_CONTROL_ACCT = A.AR_CONTROL_ACCT
AND X.CLIENT_ID = A.CLIENT_ID
AND X.BILL_NUMBER = A.BILL_NUMBER
AND A.TX_POSTED = 'True'
AND A.CLIENT_ID = '{1}'
AND A.TX_DATE <= TIMESTAMP('{0}', '23.59.59')
There is a snippet of the query its a bit longer than that a few unions and
some case statements . Johann and Manuele that thought never crossed my
mind I'll give that a try and get back with the result. Thank You ahead of
time.
--
This message is for named person(s) only. It may contain confidential
and/or legally privileged information. No confidentiality or privilege is
waived or lost should mis-transmission occur. If you receive this message
in error, delete it (and all copies) and notify the sender. You must not,
directly or indirectly,use, disclose, distribute, print, or copy any part
of this message if you are not the intended recipient. GAD GROUP
TECHNOLOGY, INC. reserves the right to monitor all e-mail communications
through its networks.
Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to
state them to be the views of any such entity.
This e-mail has been virus and content scanned by GAD GROUP TECHNOLOGY, INC.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
