“Invoice Price” is not calculated correctly on the Budget Balance tab.
- Article Type: General
- Product: Aleph
- Product Version: 20
Description:
“Invoice Price” is not calculated correctly on the Budget Balance tab.
Actual Results: Partial amount is displayed in the “Invoice Price” column on the Budget Balance tab.
Expected Results: “Invoice Price” should show a summary of the z601-local-price fields for the z601-type “INV”.
See attached scenario.
Resolution:
The problem is that this SQL is summing all of the z601's whereas, in fact, some of the z601's are credits. Count of debits only:
udm50@ALEPH20> select count (*) from z601 where Z601_REC_KEY like 'MO027-2011%' and Z601_TYPE='INV' and Z601_PAID='N' and z601_credit_debit = 'D';
**** Hit return to continue ****
COUNT(*)
----------
648
Count of credits only:
udm50@ALEPH20> select count (*) from z601 where Z601_REC_KEY like 'MO027-2011%' and Z601_TYPE='INV' and Z601_PAID='N' and z601_credit_debit = 'C';
**** Hit return to continue ****
COUNT(*)
----------
1
Sum with debits only:
udm50@ALEPH20> select sum(Z601_LOCAL_SUM) from z601 where Z601_REC_KEY like 'MO027-2011%' and Z601_TYPE='INV' and Z601_PAID='N' and z601_credit_debit = 'D';
**** Hit return to continue ****
SUM(Z601_LOCAL_SUM)
-------------------
3869046
Sum with credits only:
udm50@ALEPH20> select sum(Z601_LOCAL_SUM) from z601 where Z601_REC_KEY like 'MO027-2011%' and Z601_TYPE='INV' and Z601_PAID='N' and z601_credit_debit = 'C';
**** Hit return to continue ****
SUM(Z601_LOCAL_SUM)
-------------------
15965
The difference is exactly $3,853,081: 3869046 - 15965 = 3853081.
- Article last edited: 10/8/2013