帳款處理

(1) 銷貨金額計算

搭配 SQL 專案代號 PRJ-0003，SQL 指令如下：

Lock Tables spbill write, splist write, cuquoat write, txrate write;

Update spbill Join splist On spbill.SP_Blno = splist.SP_Blno

Join cuquoat On spbill.CU_No = cuquoat.CU_No And splist.PD_No = cuquoat.PD_No

Join txrate On cuquoat.IC_Tax = txrate.IC_Tax

Set splist.SP_Amt = splist.SP_Qty* If(cuquoat.IC_Tax="T", cuquoat.UN_Price/(1+txRate.TX_Rate), cuquoat.UN_Price), splist.TX_Amt = splist.SP_Qty * If(cuquoat.IC_Tax="T",

cuquoat.UN_Price/(1+txRate.TX_Rate), cuquoat.UN_Price) * txRate.TX_Rate;

Unlock Tables;

註 1：報價檔若含稅者，將統一轉為不含稅，然後以外加方式處理貨款。稅率在 txrate 資料表內定義。

註 2：指令內之 If(cuquoat.IC_Tax="T", cuquoat.UN_Price/(1+txRate.TX_Rate)，其 中 IF 為 MySQL 專用函數，詳細用法請參看 MySQL 專用函數說明。

(2) 客戶退貨金額計算

搭配 SQL 專案代號 PRJ-0004，SQL 指令如下：

Lock Tables bkspbl write, bksplog write,cuquoat write, txrate write; ↑H

Update bkspbl Join bksplog On bkspbl.BK_Blno=bksplog.BK_Blno Join cuquoat On bkspbl.CU_No=cuquoat.CU_No And

bksplog.PD_No=cuquoat.PD_No

Join txrate On cuquoat.IC_Tax = txrate.IC_Tax

Set bksplog.BK_Amt= bksplog.BK_Qty* If(cuquoat.IC_Tax="T", cuquoat.UN_Price/(1+txRate.TX_Rate),

cuquoat.UN_Price), bksplog.TX_Amt=bksplog.BK_Qty * If(cuquoat.IC_Tax="T", cuquoat.UN_Price/(1+txRate.TX_Rate), cuquoat.UN_Price) * txRate.TX_Rate;

Unlock Tables;

(3) 進貨金額計算

搭配 SQL 專案代號 PRJ-0005，SQL 指令如下：

Lock Tables icbill write, iclist write, icquoat write, txrate write;

Update icbill Join iclist On icbill.SP_Blno=iclist.SP_Blno

Join icquoat On icbill.CU_No = icquoat.CU_No And iclist.PD_No=icquoat.PD_No Join txrate On icquoat.IC_Tax = txrate.IC_Tax

Set iclist.SP_Amt = iclist.SP_Qty* If(icquoat.IC_Tax="T",

icquoat.UN_Price/(1+txRate.TX_Rate),icquoat.UN_Price), iclist.TX_Amt = iclist.SP_Qty * If(icquoat.IC_Tax="T", icquoat.UN_Price /(1+txRate.TX_Rate), icquoat.UN_Price)* txRate.TX_Rate;

Unlock Tables;

(4) 進貨退回金額計算

搭配 SQL 專案代號 PRJ-0006，SQL 指令如下：

Lock Tables bkicbl write, bkiclog write,icquoat write, txrate write;

Update bkicbl Join bkiclog On bkicbl.BK_Blno=bkiclog.BK_Blno Join icquoat On bkicbl.CU_No = icquoat.CU_No And bkiclog.PD_No =

icquoat.PD_No

Join txrate On icquoat.IC_Tax = txrate.IC_Tax

Set bkiclog.BK_Amt = bkiclog.BK_Qty* If(icquoat.IC_Tax="T",

icquoat.UN_Price/(1+txRate.TX_Rate), icquoat.UN_Price),bkiclog.TX_Amt = bkiclog.BK_Qty * If(icquoat.IC_Tax="T",icquoat.UN_Price/(1+txRate.TX_Rate), icquoat.UN_Price)* txRate.TX_Rate;

Unlock Tables;

(5) 出貨轉應收帳 ↑H

搭配 SQL 專案代號 PRJ-0007，SQL 指令如下：

BackupTBL SQL From splist;

BackupTBL SQL From rcpay;

Create Temporary Table updTable

Select spbill.CU_No, Sum(splist.SP_Amt + splist.TX_Amt) AS Amount,

splist.TR_Note From spbill Join splist Where spbill.SP_Blno = splist.SP_Blno And splist.TR_Note<>"T" Group By spbill.CU_No;

Lock Tables updTable write, spbill write, splist write,rcpay Write, rcpay As objTable Write;

Insert Into rcpay (CU_No) Select CU_No From updTable

Where (CU_No) Not In (Select CU_No From rcpay As objTable);

Update rcpay, updTable

Set rcpay.CR_Spamt = rcpay.CR_Spamt + updTable.Amount Where rcpay.CU_No=

updTable.CU_No;

Update splist Set splist.TR_Note ="T" Where splist.TR_Note<>"T";

Unlock Tables;

Drop Temporary Table updTable;

(6) 收款轉應收帳

搭配 SQL 專案代號 PRJ-0008，SQL 指令如下：

BackupTBL SQL From rcdtl ; BackupTBL SQL From rcpay;

Create Temporary Table updTable

Select rcdtl.CU_No, Sum(rcdtl.CS_Amt) As Csamt, Sum(rcdtl.RD_Amt) As RdAmt, rcdtl.TR_Note From rcdtl Where rcdtl.TR_Note<>"T" Group By rcdtl.CU_No;

Lock Tables updTable write, rcdtl write,rcpay Write;

Update rcpay, updTable

Set rcpay.CR_Csamt = rcpay.CR_Csamt + updTable.Csamt,rcpay.CR_Rdamt = rcpay.CR_Rdamt + updTable.Rdamt Where rcpay.CU_No = updTable.CU_No;

Update rcdtl Set rcdtl.TR_Note="T" Where rcdtl.TR_Note<>"T";

Unlock Tables;

Drop Temporary Table updTable;

(7) 銷貨退回轉應收帳 ↑H

搭配 SQL 專案代號 PRJ-0009，SQL 指令如下：

BackupTBL SQL From bksplog;

BackupTBL SQL From rcpay;

Create Temporary Table updTable

Select bkspbl.CU_No, Sum(bksplog.BK_Amt + bksplog.TX_Amt) AS BkAmt, bksplog.TR_Note From bkspbl

Join bksplog Where bkspbl.BK_Blno = bksplog.BK_Blno And bksplog.TR_Note<>"T" Group By bkspbl.CU_No;

Lock Tables updTable write, bkspbl write, bksplog write, rcpay Write;

Update rcpay, updTable

Set rcpay.CR_Bkamt = rcpay.CR_Bkamt + updTable.Bkamt Where rcpay.CU_No = updTable.CU_No;

Update bksplog Set bksplog.TR_Note="T" Where bksplog.TR_Note<>"T";

Unlock Tables;

Drop Temporary Table updTable;

(8) 進貨轉應付帳

搭配 SQL 專案代號 PRJ-0010，SQL 指令如下：

BackupTBL SQL From iclist;

BackupTBL SQL From rppay;

Create Temporary Table updTable

Select icbill.CU_No, Sum(iclist.SP_Amt + iclist.TX_Amt) AS Amount, iclist.TR_Note From icbill

Join iclist Where icbill.SP_Blno = iclist.SP_Blno And iclist.TR_Note<>"T"

Group By icbill.CU_No;

Lock Tables updTable write, icbill write, iclist write,rppay Write, rppay As objTable Write;

Insert Into rppay (CU_No) Select CU_No From updTable

Where (CU_No) Not In (Select CU_No From rppay As objTable);

Update rppay, updTable

Set rppay.CR_Spamt = rppay.CR_Spamt + updTable.Amount Where rppay.CU_No

= updTable.CU_No;

Update iclist Set iclist.TR_Note ="T" Where iclist.TR_Note<>"T"; ↑H

Unlock Tables;

Drop Temporary Table updTable;

(9) 付款轉應付帳

搭配 SQL 專案代號 PRJ-0011，SQL 指令如下：

BackupTBL SQL From padtl;

BackupTBL SQL From rppay;

Create Temporary Table updTable

Select padtl.CU_No, Sum(padtl.PA_Amt) As Paamt, Sum(padtl.RD_Amt) As RdAmt, padtl.TR_Note From padtl Where padtl.TR_Note<>"T" Group By padtl.CU_No;

Lock Tables updTable write, padtl write,rppay Write;

Update rppay, updTable

Set rppay.CR_Csamt = rppay.CR_Csamt + updTable.Paamt,rppay.CR_Rdamt = rppay.CR_Rdamt + updTable.Rdamt Where rppay.CU_No = updTable.CU_No;

Update padtl Set padtl.TR_Note="T" Where padtl.TR_Note<>"T";

Update rppay Set CR_Upamt = PR_Upamt + CR_Spamt- CR_Bkamt- CR_Csamt- CR_Rdamt;

Unlock Tables;

Drop Temporary Table updTable;

(10). 進貨退回轉應付帳

搭配 SQL 專案代號 PRJ-0012，SQL 指令如下：

BackupTBL SQL From bkiclog;

BackupTBL SQL From rppay;

Create Temporary Table updTable

Select bkicbl.CU_No, Sum(bkiclog.BK_Amt + bkiclog.TX_Amt) AS BkAmt, bkiclog.TR_Note From bkicbl

Join bkiclog Where bkicbl.BK_Blno = bkiclog.BK_Blno And bkiclog.TR_Note<>"T" Group By bkicbl.CU_No;

Lock Tables updTable write, bkicbl write, bkiclog write, rppay Write;

Update rppay, updTable

Set rppay.CR_Bkamt = rppay.CR_Bkamt + updTable.Bkamt

Where rppay.CU_No = updTable.CU_No; ↑H

Update bkiclog Set bkiclog.TR_Note="T" Where bkiclog.TR_Note<>"T";

Unlock Tables;

Drop Temporary Table updTable;

(11)累計應收整理

搭配 SQL 專案代號 SQL-0034，SQL 指令如下：

Update rcpay Set rcpay.CR_Upamt = rcpay.PR_Upamt +r cpay.CR_Spamt –r cpay.CR_Bkam t- rcpay.CR_Csamt - rcpay.CR_Rdamt;

(12)累計應付整理

搭配 SQL 專案代號 SQL-0035，SQL 指令如下：

Update rppay Set rppay.CR_Upamt = rppay.PR_Upamt + rppay.CR_Spamt - rppay.CR_Bkamt - rppay.CR_Csamt - rppay.CR_Rdamt;

17.8 月結

(1) 銷貨訂單月結：交貨完畢或已結案部份

搭配 SQL 專案代號 PRJ-0020，SQL 指令如下：

BackupTBL SQL From sporder;

BackupTBL SQL From spodlst;

Lock Tables sporder Write, spodlst Write;

Delete sporder, spodlst From sporder

Join spodlst On sporder.OD_Blno = spodlst.OD_Blno

And (spodlst.SP_Qty >=spodlst.OD_Qty Or spodlst.CL_Note="T");

Delete From sporder Where sporder.OD_Blno Not In (Select OD_Blno From spodlst);

Unlock Tables;

(2) 出貨檔月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0021，SQL 指令如下：

BackupTBL SQL From spbill;

BackupTBL SQL From splist;

Lock Tables spbill Write, splist Write;

Delete spbill, splist From spbill ↑H

Join splist On sp bill.SP_Blno = splist.SP_Blno And splist.TR_Note="T";

Delete From spbill Where spbill.SP_Blno Not In (Select SP_Blno From splist);

Unlock Tables;

(3) 銷貨退回月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0022，SQL 指令如下：

BackupTBL SQL From bkspbl;

BackupTBL SQL From bksplog;

Lock Tables bkspbl Write, bksplog Write;

Delete bkspbl, bksplog From bkspbl

Join bksplog On bkspbl.BK_Blno = bksplog.BK_Blno And bksplog.TR_Note="T";

Delete From bkspbl Where bkspbl.BK_Blno Not In (Select BK_Blno From bksplog);

Unlock Tables;

(4) 請購訂單月結：交貨完畢或已結案部份

搭配 SQL 專案代號 PRJ-0023，SQL 指令如下：

BackupTBL SQL From icorder;

BackupTBL SQL From icodlst;

Lock Tables icorder Write, icodlst Write;

Delete icorder, icodlst From icorder

Join icodlst On icorder.OD_Blno =icodlst.OD_Blno

Where (icodlst.IC_Qty >=OD_Qty Or icodlst.CL_Note="T");

Delete From icorder Where icorder.OD_Blno Not In (Select OD_Blno From icodlst);

Unlock Tables;

(5) 進貨檔月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0024，SQL 指令如下：

BackupTBL SQL From icbill;

BackupTBL SQL From iclist;

Lock Tables icbill Write, iclist Write;

Delete icbill, iclist From icbill

Join iclist On icbill.SP_Blno = iclist.SP_Blno And iclist.TR_Note="T";

Delete From icbill Where (icbill.SP_Blno) Not In (Select SP_Blno From iclist); ↑H

Unlock Tables;

(6) 進貨退回月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0025，SQL 指令如下：

BackupTBL SQL From bkicbl;

BackupTBL SQL From bkiclog;

Lock Tables bkicbl Write, bkiclog Write;

Delete bkicbl,bkiclog From bkicbl

Join bkiclog On bkicbl.BK_Blno = bkiclog.BK_Blno And bkiclog.TR_Note="T";

Delete From bkicbl Where bkicbl.BK_Blno Not In (Select BK_Blno From bkiclog);

Unlock Tables;

(7) 庫存異動檔月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0026，SQL 指令如下：

BackupTBL SQL From iostock;

Lock Tables iostock Write;

Delete From iostock Where iostock.ST_Note="T";

Unlock Tables;

(8) 收款檔月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0027，SQL 指令如下：

BackupTBL SQL From rcdtl;

Lock Tables rcdtl Write;

Delete From rcdtl Where rcdtl.TR_Note="T";

Unlock Tables;

(9) 付款檔月結：已轉帳部份

搭配 SQL 專案代號 PRJ-0028，SQL 指令如下：

BackupTBL SQL From padtl;

Lock Tables padtl Write;

Delete From padtl Where padtl.TR_Note="T";

Unlock Tables;

(10) 應收帳款月底沖轉 ↑H

搭配 SQL 專案代號 PRJ-0029，SQL 指令如下：

BackupTBL SQL From rcpay;

Lock Tables rcpay Write;

Update rcpay Set

rcpay.CR_Upamt=rcpay.PR_Upamt+rcpay.CR_Spamt-rcpay.CR_Bkamt- rcpay.CR_Csamt-rcpay.CR_Rdamt,

rcpay.PR_Upamt = rcpay.CR_Upamt,

rcpay.CR_Spamt = 0, rcpay.CR_Bkamt = 0, rcpay.CR_Csamt= 0, rcpay.CR_Rdamt = 0;

Unlock Tables;

(11) 應付帳款月底沖轉

搭配 SQL 專案代號 PRJ-0030，SQL 指令如下：

BackupTBL SQL From rppay;

Lock Tables rppay Write;

Update rppay Set

rppay.CR_Upamt=rppay.PR_Upamt+rppay.CR_Spamt-rppay.CR_Bkamt-rppay.CR _Csamt-rppay.CR_Rdamt,

rppay.PR_Upamt = rppay.CR_Upamt,

rppay.CR_Spamt = 0, rppay.CR_Bkamt = 0, rppay.CR_Csamt= 0, rppay.CR_Rdamt = 0;

Unlock Tables;

在文檔中 第十七章 進銷存管理系統 17.1 (頁 25-33)