先查兩個版面欄位各自的資料來源.看是否運算式列錯1.在Invoice主檔版面
d_invoicetop3 的Invoice_amount,查其資料來源SQL,是一個複合式Select ,如下列所式
(select sum(invoice_item.num*order_item.price) from order_item,invoice_item where invoice_item.invoice_no=invoice.invoice_no
and invoice_item.orderno=order_item.orderno and invoice_item.scseq=order_item.seq) as invoice_amount
這個當成Invoice Amount,但只算到內含所有訂單明細的合計金額,未含到加減項金額.
2.在Invoice一覽表版面
d_invoice_list 中查其資料來源SQL,看到先計算訂單金額 totmoney,再用訂單金額totmoney減去
invoice主檔的加減項金額 discount_addition,賦予欄位名稱 shouldmon,並以 Amount抬頭出現在版面,totmoney則Order Amount抬頭出現在版面.
內含所有訂單明細的總金額
(select sum(invoice_item.num*order_item.price) from order_item,invoice_item where invoice_item.invoice_no=invoice.invoice_no
and invoice_item.orderno=order_item.orderno and invoice_item.scseq=order_item.seq) as totmoney
訂單總金額 減去 加減項金額 =Invoice Amount
(totmoney
- invoice.discount_addition) as shouldmon
一覽表把訂單總餘Order Amount,與加減項總額 invoice.discount_addition分開列.並不像明細版面將這兩項金額加總列出 Invoice Amount
修正SQL計算式基本上算出訂單金額部分是一樣的Select 子句,但差異在加減項金額上面,Invoice amount需含訂單加減項金額,但應是加或減需要檢查之
根據客戶意思修正如下
一覽表版面 d_invoice_list 的shouldmon(版面抬頭為Amount)應改成 (totmoney
+ invoice.discount_addition) as shouldmon
而明細版面d_invoicetop3應改
(select sum(invoice_item.num*order_item.price) from order_item,invoice_item where invoice_item.invoice_no=invoice.invoice_no
and invoice_item.orderno=order_item.orderno and invoice_item.scseq=order_item.seq)
+invoice.discount_addition as invoice_amount
加減項金額被納入計算式,其值是存在 Table Invoice中,所以其正或負必須由寫入該欄位的機制明確負責.
但目前系統是開放由會計自行輸入在Invoice主檔中,而因加減項明細與各項金額是訂單內含的項目,設計上應該是由訂單主動回寫過來才對.
需客戶再討論這點的適當性.
版面 d_invoicetop3如下圖,在右邊偏下可看到 Invoice Amount 欄位