---
# System prepended metadata

title: E28
tags: [已處理]

---

# E28
###### tags: `已處理`
## 問題敘述
N110-26契約第4批已完成驗收，請款單資料產生(MR30409)出現「該標項、批次
尚未完成驗收手續」，無法辦理請款手續，敬請查修。(三區物料課反映)
![](https://i.imgur.com/yHdJSH5.png)

[E28](https://docs.google.com/document/d/1hdbGk5BuwvChER8lDuw7r4mRqFvrgzdt/edit?usp=sharing&ouid=107825791787894292764&rtpof=true&sd=true)
![](https://i.imgur.com/I2AOSgu.png)
:::spoiler
## 解析
![](https://i.imgur.com/zqHYgTa.png)
https://10.100.50.20:9080/mr/mr30302_lis.jsp?s_BNOH_CSEQ=26&e_BNOH_CHSEQ=&s_BNOH_SNO=&s_BNOH_CHYY=&s_BNOH_SNAME=&s_BNOH_MKYY=&s_BNOH_ANO=03&s_BNOH_CHSEQ=&s_BNOH_STATUS=&s_BNOH_CYY=110&e_BNOH_MKSEQ=&e_BNOH_CSEQ=26&s_BNOH_MKSEQ=

![](https://i.imgur.com/01PIhER.png)
https://10.100.50.20:9080/mr/mr30409_man.jsp?PNOH_ANO=03&PNOH_CSEQ1=03&PNOH_ANO1=03&PNOH_CYY=110&s_PNOH_DEL=&s_PNOH_PMYY=111&PNOH_CSEQ=26&PNOH_PMYY=111&PNOH_PMSEQ=108&s_PNOH_PMDATE=&s_PNOH_ANO=03&PNOH_CCODE=N&s_PNOH_PMSEQ=108&PNOH_PMKIND=P&PNOH_SEQ=1&PNOH_DEL=0

![](https://i.imgur.com/9ZIYTD5.png)
https://10.100.50.20:9080/mr/mr30409_man.jsp?ccsForm=MAUR_PNOM&PNOH_PMSEQ=108&s_PNOH_PMYY=111&PNOH_CSEQ=26&PNOH_PMYY=111&PNOH_ANO1=03&PNOH_DEL=0&PNOH_SEQ=1&PNOH_ANO=03&s_PNOH_DEL=&PNOH_CCODE=N&PNOH_CSEQ1=03&PNOH_CYY=110&s_PNOH_ANO=03&s_PNOH_PMDATE=&PNOH_PMKIND=P&s_PNOH_PMSEQ=108

![](https://i.imgur.com/gCgu5HF.png)

https://10.100.50.20:9080/mr/mr30302_man.jsp?s_BNOH_MKYY=&s_BNOH_CYY=110&s_BNOH_MKSEQ=&s_BNOH_STATUS=&s_BNOH_CHSEQ=&s_BNOH_SNAME=&BNOH_CHDATE=1110704&e_BNOH_MKSEQ=&BNOH_CHSEQ=7899&BNOH_CHYY=111&e_BNOH_CSEQ=26&s_BNOH_CHYY=&e_BNOH_CHSEQ=&s_BNOH_CSEQ=26&BNOH_CHKIND=B&BNOH_ANO=03&s_BNOH_ANO=03&s_BNOH_SNO=

![](https://i.imgur.com/mezIcVw.png)

https://10.100.50.20:9080/mr/mr30409_lis.jsp?s_PNOH_PMYY=111&s_PNOH_DEL=&s_PNOH_ANO=03&s_PNOH_PMDATE=&s_PNOH_PMSEQ=108
:::
## 問題單內容
*  原始程式碼位置
*  目的程式碼位置
*  問題解析
*  處理方法
*  變更後測試執行結果

## 討論
先定位該錯誤訊息拋出的位置再開始解析原因
mr30409_manHandlers.jsp
![](https://i.imgur.com/Aguvfdn.png)

先把那一段計算數量的 sql 兜出來
理論上要什麼數字、實際上是什麼數字
分析造成這兩個落差的原因
修正
提示：可以直接打開 CCLogger 去查看 DBTools.dLookUp 這個動作留下的 sql 語法
你會需要這樣推導
```java
// line 952 //項次起訖為空
pnom_item_count = Utils.convertToLong(DBTools.dLookUp("NVL(COUNT(*), 0)", tableName, whereCondition2, connectionName)).intValue();
// line 948 //項次起訖不為空
pnom_item_count = Integer.parseInt(pnom_item_e) - Integer.parseInt(pnom_item_s) + 1;

// line 865-871
//項次_起[PNOM_ITEM_S]
Control cntrl_pnom_item_s = (Control)rowData.get("PNOM_ITEM_S");
String pnom_item_s = Utils.convertToString(cntrl_pnom_item_s.getValue());
//項次_迄[PNOM_ITEM_E]
Control cntrl_pnom_item_e = (Control)rowData.get("PNOM_ITEM_E");
String pnom_item_e = Utils.convertToString(cntrl_pnom_item_e.getValue());
```
我有去撈該筆資料的table & sql
## Mr30409
```sql
SELECT * FROM MAUR_PNOH 
WHERE 
PNOH_PMYY=111 
AND  PNOH_DEL= 0
AND  PNOH_ANO=03 
--AND  PNOH_PMDATE= 
AND  PNOH_PMSEQ=108
--CDVD_ANO='0D' AND CDVD_CCODE='N' AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D' 
;
```
![](https://i.imgur.com/aEihQxz.png)

## Mr30409
```sql
SELECT * FROM MAUR_BNOH
WHERE 
BNOH_CYY=110 
AND BNOH_CHDATE=1110704 
AND BNOH_CHSEQ=7899 
AND BNOH_CHYY=111 
AND  BNOH_CSEQ=26 
AND BNOH_CHKIND = 'B' 
AND BNOH_ANO = '03' 
```
![](https://i.imgur.com/b5pB339.png)

> 你先跟著程式邏輯走，把完整的 sql 組合出來
> 例如說我需要請款種類 PNOM_TYPE 去判斷sql 是要從 line 907-914 或是 line 916-921 來，條件要加 line 936 或是  938

好~

```sql
-- 已完成交貨手續的數量
-- 查詢一次，然後再把 AND BNOH_CHDATE IS NOT NULL 註解掉看看有什麼差別
SELECT NVL(COUNT(*), 0) 
FROM MAUR_BNOH, MAUR_BNOB
WHERE 
	(BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE) AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ) AND (BNOH_CSEQ1 = BNOB_CSEQ1) 
	AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ 
	AND BNOH_DEL = '0' AND BNOH_STATUS = '1' 
	AND BNOB_ANO1 = :PNOH_ANO1 AND BNOB_CCODE = :PNOH_CCODE AND BNOB_CYY = :PNOH_CYY AND BNOB_CSEQ = :PNOH_CSEQ AND BNOB_CSEQ1 = :PNOH_CSEQ1 
	AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM =:PNOM_PITEM
	AND BNOH_CHDATE IS NOT NULL

-- pnom_item_count =
-- 查這個的值
SELECT NVL(COUNT(*), 0) 
FROM  MAUR_BNOH, MAUR_BNOB
WHERE (BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE) AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ) AND (BNOH_CSEQ1 = BNOB_CSEQ1) AND BNOH_DEL = '0' 
AND BNOB_ANO1 = :PNOH_ANO1 AND BNOB_CCODE = :PNOH_CCODE AND BNOB_CYY = :PNOH_CYY AND BNOB_CSEQ = :PNOH_CSEQ AND BNOB_CSEQ1 = :PNOH_CSEQ1 AND BNOB_DEL = '0' AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM = :PNOM_PITEM
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ 
```
![](https://i.imgur.com/EYeAP1h.png)
我剛有拿掉過 但結果蠻怪的
阿不是拉 PNOM那些我要帶多少XDD

那要去CCloger撈 這筆資料的參數    ㄇ?
好 我去撈一下    
辛苦ㄌ
> 多一個; 修正再丟
> 你把參數值都要填上
> 你要從 30409 查 你看 xml 下面的 select 語法
![](https://i.imgur.com/VVrWk0v.png)


最快的就是從 logger 直接拿他查的語法
不用 logger 就是要照條件推導
我不在現場只能做苦工

mr30409_manHandlers.jsp上 900行那邊 放ccLoger 紀錄嗎?

> 有些 sql 他原本就會 log 出來，沒有的話你也可以放
> 我記得 DBLookup  的都會 log

有 我找到ㄌ
![](https://i.imgur.com/iJKN16O.png)

```sql
SELECT NVL(COUNT(*), 0) 
FROM  MAUR_BNOH, MAUR_BNOB
WHERE (BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE) 
AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ) 
AND (BNOH_CSEQ1 = BNOB_CSEQ1) AND BNOH_DEL = '0' 
AND BNOH_DEL = '0' AND BNOH_STATUS = '1' 
AND BNOB_ANO1 = '03' AND BNOB_CCODE = 'N'
AND BNOB_CYY = 110 AND BNOB_CSEQ = 26 AND BNOB_CSEQ1 = 03
AND BNOB_DEL = '0' 
--AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM = :PNOM_PITEM
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ 
```
![](https://i.imgur.com/aI3XBE1.png)
(兩段結果 都是 33)
(select* 時有找到 7899的資料)

table MAUR_PNOM  沒資料
```sql
SELECT *
FROM MAUR_PNOM 
WHERE PNOM_ANO = '03' 
AND PNOM_KIND = 'P' 
AND PNOM_PMYY = 111 ㄎ
--AND PNOM_PMSEQ = 108 
--AND PNOM_DEL
```
(沒有108)
![](https://i.imgur.com/ewRznL0.png)

但table MAUR_PNOH  有資料
```sql
SELECT * 
FROM MAUR_PNOH 
WHERE PNOH_ANO like '03%' AND PNOH_ANO = '03' 
AND PNOH_PMKIND = 'P' AND PNOH_PMYY = 111 
AND PNOH_PMSEQ = 108 
```
![](https://i.imgur.com/aUGpIJU.png)

> AND BNOB_BITEM = :PNOM_BITEM AND BNOB_PITEM = :PNOM_PITEM
> 不能註解，你看 jsp 可以知道分別代表標項、批次
> 要帶入 1 , 4  查

這個數字是12
```sql
SELECT*
--NVL(COUNT(*), 0) 
FROM  MAUR_BNOH, MAUR_BNOB
WHERE (BNOH_ANO1 = BNOB_ANO1) AND (BNOH_CCODE = BNOB_CCODE) 
AND (BNOH_CYY = BNOB_CYY) AND (BNOH_CSEQ = BNOB_CSEQ) 
AND (BNOH_CSEQ1 = BNOB_CSEQ1) AND BNOH_DEL = '0' 
AND BNOH_DEL = '0' AND BNOH_STATUS = '1' 
AND BNOB_ANO1 = '03' AND BNOB_CCODE = 'N'
AND BNOB_CYY = 110 AND BNOB_CSEQ = 26 AND BNOB_CSEQ1 = 03
AND BNOB_DEL = '0' 
AND BNOB_BITEM = 1 AND BNOB_PITEM = 4
AND BNOH_ANO=BNOB_ANO AND BNOH_CHKIND = BNOB_CHKIND AND BNOH_CHYY =BNOB_CHYY AND BNOH_CHSEQ =BNOB_CHSEQ 
```
![](https://i.imgur.com/ei3mzNW.png)
   
   ```sql
   DISABLE TRIGGER ALL ON MAUR_PNOM;
UPDATE MAUR_PNOM SET PNOM_PITEM= 4 
WHERE PNOM_ANO = '03' AND PNOM_ANO = '03' 
--AND PNOM_PMKIND = 'P' 
AND PNOM_PMYY = 111 
AND PNOM_PMSEQ = 108 ;
ENABLE TRIGGER ALL ON MAUR_PNOM; 

SELECT * FROM MAUR_PNOM 
WHERE PNOM_ANO = '03' AND PNOM_ANO = '03' 
--AND PNOM_PMKIND = 'P' AND PNOM_PMYY = 111 
--WHERE PNOM_ANO like '03' 
----AND PNOM_KIND = 'P' 
AND PNOM_PMYY = 111 AND PNOM_PMSEQ = 108 
----AND PNOM_DEL = '0'  
ORDER BY PNOM_SEQ;
   ```
7/21
已修正
![](https://i.imgur.com/PKvg3EM.png)
