INVOICE 檔- 加減項錯誤-急急急

INVOICE 檔- 加減項錯誤,
invoice 錯誤

先查兩個版面欄位各自的資料來源.看是否運算式列錯

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 欄位