Appeon Rounding Issue? PB 12.5

[Former Member]

Greetings All,

I had a client that uses our Appeon web application complain about an error (kindly see screenshot).

The system checks to make sure that the total of completed charges is either the same or less than what was quoted.

I experienced the same problem in the 32-bit version of the application and changed all my cost elements from decimal to decimal{2}.  Now it works fine in the 32-bit version. 

But I am still getting the same error after doing a full rebuild and full deployment in Appeon.

Can someone kindly point me in the right direction?

Thank you!!

Paul

[Former Member · Accepted solution]

Hi Paul;

   Did you set the decimal size & precision in the Appeon AEM to match the newer PowerBuilder's precision to 28 digits?

FYI:  AEM => Application => PB Features => Precision => <YourAppName>

Regards .... Chris

[Former Member · Accepted solution]

Thanks Chris,

I just tried it now and redeployed.  Same issue error.

I am not sure why higher precision would help.  The 15 digits is more than enough to cover the lat/longs that I use.

Anyway, it seems like an odd error.

Thanks for your help!!

Paul

[Former Member · Accepted solution]

what is the build number of appeon?

I have seen and reported decimal issues in the past with them, i believe they were all fixed.

how exactly is the calculation being done?  if the sum is in the datawindow, then you will have decimal issues even in regular PB since it is a double.  the format for the subtotal may do the rounding for them, but for comparison they should do a getitemnumber of the sum value and then round it.

[Former Member · Accepted solution]

Hi Mike;

  That could certainly be Paul's problem if he is doing a GetItemNumber vs a GetItemDecimal. That means that he is not using the correct data type in his DBMS columns.

Regards ... Chris

[Former Member · Accepted solution]

Actually, I am using neither getitemnumber or getitemdecimal to populate the vertical datawindow.

I have a function that builds the sql and then I do setsqlselect and retrieve it.

When I log into AEM, I see this:  Appeon for PowerBuilder 2015 Build 0328.00

sql = "SELECT 'Base Rate: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.basic_rate_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Gratuity: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.tips_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Tolls: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.tolls_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Parking: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.parking_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//FUEL //per Tim in OCT 2015 NO AFFILIATE CAN CHARGE FUEL

if fuel_hide = 'N' then

  sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Fuel: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.fuel_surcharge_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

end if

//STC

if stc_hide = 'N' then

  sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'STC: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.stc_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

end if

//MEET/GREET

if meet_greet_hide = 'N' then

  sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Meet/Greet: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.m_g_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

end if

//ELMN/HOLIDAY

//For EMLN and Holiday charge, Aleph will have to add them together before they populate the _dr rate elements in the operator table

if emln_hide = 'N' then

  sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'EMLN/Holiday: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.ot_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

end if

//AIRPORT FEE

if airport_fee_hide = 'N' then

  sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Airport Fee: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.airport_fee_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

end if

//'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '"'  + &

//STOPS FEES

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Stops Charges: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.stops_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//WAIT CHARGES

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Wait Charges: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.wt_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//MISC CHARGES

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Misc. Charges: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.misc_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//DISCOUNT

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Discount: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.discount_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//WC

//need to check if trip is in Manhattan

//if trim(pu_city) = 'New York' AND trim(dest_county) = 'New York' then

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'NYC Workers Comp: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.wc_charges_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//end if

//SALES TAX

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Tax: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.sales_tax_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

//TOTAL

sql = sql + '~r~n' + '~r~n' + 'UNION ALL' + &

'~r~n' + '~r~n' + '' + &

'~r~n' + '~r~n' + "SELECT 'Total: ' as DESCRIPTION," + &

'~r~n' + '~r~n' + 'operator_archive.price_est_dr as AMOUNT' + &

'~r~n' + '~r~n' + '    FROM operator_archive' + &

'~r~n' + '~r~n' + '   WHERE operator_archive.confirmation_no = ~'' + confo + '~''  + &

'~r~n' + '~r~n' + ''

  sql = sql + ";"

  return SQL

[Former Member · Accepted solution]

Are the numerical columns of data type Decimal though?

[Former Member · Accepted solution]

your attached screen shot showed an error message that the total didnt match.  how are you getting the data to do the comparison

[Former Member · Accepted solution]

Hi M,

I am using a function to check the totals below.  Where you see "string(abs(net_dr - total_check))" is where the odd amount shows seemingly to be to some exponential power.

Paul

string returnvalue

long rows

long i

string description2

decimal{2} amount

rows = w_trip_closeout_ii.dw_vc_charges.rowcount()

if rows > 0 then

  for i = 1 to rows

  description2 = trim(w_trip_closeout_ii.dw_vc_charges.getitemstring(i, "closeout_item_description_long_1111111111"))

  amount = w_trip_closeout_ii.dw_vc_charges.getitemdecimal(i, "amount")

  if description2 = "Base Rate:" then

  basic_rate_dr = round(amount,2)

  end if

  if description2 = "Gratuity:" then

  tips_charges_dr = round(amount,2)

  end if

  if description2 = "Tolls:" then

  tolls_charges_dr = round(amount,2)

  end if

  if description2 = "Parking:" then

  parking_charges_dr = round(amount,2)

  end if

  if description2 = "Fuel:" then

  fuel_surcharge_dr = round(amount,2)

  end if

  if description2 = "STC:" then

  stc_charges_dr = round(amount,2)

  end if

  if description2 = "Meet/Greet:" then

  m_g_charges_dr = round(amount,2)

  end if

  if description2 = "EMLN/Holiday:" then

  ot_charges_dr = round(amount,2)

  end if

  if description2 = "Airport Fee:" then

  miscellaneous_dr = round(amount,2)

  end if

  if description2 = "Stops Charges:" then

  stops_charges_dr = round(amount,2)

  end if

  if description2 = "Wait Charges:" then

  wt_charges_dr = round(amount,2)

  end if

  if description2 = "Misc. Charges:" then

  miscellaneous_dr = round(amount,2)

  end if

  if description2 = "Discount:" then

  discount_dr = round(amount,2)

  end if

  if description2 = "NYC Workers Comp:" then

  wc_charges_dr = round(amount,2)

  end if

  if description2 = "Tax:" then

  sales_tax_dr = round(amount,2)

  end if

  if description2 = "Total:" then

  net_dr = round(amount,2)

  end if

  next

end if

if isnull(basic_rate) then

  basic_rate_dr = 0

end if

if isnull(parking) then 

  parking_charges_dr = 0

end if

if isnull(m_g_charges) then 

  m_g_charges_dr = 0

end if

if isnull(ot_charges) then

  ot_charges_dr = 0

end if

if isnull(stc) then 

  stc_charges_dr = 0

end if

if isnull(fuel) then 

  fuel_surcharge_dr = 0

end if

if isnull(airport_fee) then

  airport_fee_dr = 0

end if

if isnull(stops_charge) then

  stops_charges_dr = 0

end if

if isnull(wait_charge) then

  wt_charges_dr = 0

end if

if isnull(tolls) then 

  tolls_charges_dr = 0

end if

if isnull(misc_charge) then

  miscellaneous_dr = 0

end if

if isnull(tip_charge) then 

  tips_charges_dr = 0

end if

if isnull(emln_holiday) then

  ot_charges_dr = 0

end if

if isnull(discount) then

  discount_dr = 0

end if

if isnull(subtotal) then

  subtotal = 0

end if

if isnull(nyc_wc_fee) then

  wc_charges_dr = 0

end if

if isnull(sales_tax) then

  sales_tax_dr = 0

end if

if isnull(price_est) then

  net_dr = 0

end if

decimal{2} total_check

total_check = round(basic_rate_dr + tips_charges_dr + tolls_charges_dr + parking_charges_dr + fuel_surcharge_dr + stc_charges_dr + m_g_charges_dr + ot_charges_dr + airport_fee_dr + stops_charges_dr + wt_charges_dr + miscellaneous_dr - discount_dr + wc_charges_dr + sales_tax_dr,2)

if total_check <> net_dr then

  messagebox("Error!","Sorry, the total of the price elements do not match the total of " + string(net_dr) + " entered. The difference is " + string(abs(net_dr - total_check)) + ".  Please adjust the price elements or the total to match.  Alternatively, you can request a trip closeout override.")

  returnvalue =  "NOMATCH"

elseif net_dr = 0 then

  messagebox("Error!","Sorry, the total price cannot be zero.")

  returnvalue =  "NOMATCH"

elseif basic_rate_dr = 0 then

  messagebox("Error!","Sorry, the base rate cannot be zero.")

  returnvalue =  "NOMATCH"

else

  returnvalue = "MATCH"

end if

return returnvalue

[Former Member · Accepted solution]

Hi Chris,

The vertical datawindow item for 'amount' has the following format defined:

#,##0.00

Paul

[Former Member · Accepted solution]

Hi Paul;

  I was talking about the data type - not the Edit/Display mask format.

Regards ... Chris

[Former Member · Accepted solution]

Hi Paul,

Are all your decimals defined as decimal{2}  including net_dr?

Lars

[Former Member · Accepted solution]

Thanks, Chris...

Well, they look and feel like decimal, but here is the sql in design mode (same for both datawindows)...

SELECT 'CLOSEOUT_ITEM_DESCRIPTION_LONG_1111111111' as CLOSEOUT_ITEM_DESCRIPTION_LONG_1111111111,

voucher_current.basic_rate_dr as AMOUNT

    FROM voucher_current 

   WHERE voucher_current.confirmation_no = '1100329204'

[Former Member · Accepted solution]

decimal{2} amount

if you do a getitemdecimal/number into a decimal{2} the value is truncated.

get rid of the {2}, but make sure you round to 2 decimal places (which you are)

[Former Member · Accepted solution]

Thanks M.

This was not working before I added the {2} to the variable definition.

But what exactly is getting truncated?  Just the decimal?

And I am still rounding after get the values.

In that case is the {2} still throwing me off?

thanks for your help!!

Paul