# E26
###### tags: `已處理`

## 問題敘述
本處契約:110-6-0D
本案契約總量為34,800 M，
現總已通知交貨6批，合計量為29,400 M
故本案契約應剩5,400M 惟系統顯示該合約剩餘量惟600M

[E26](https://docs.google.com/document/d/1J9jqHusCUmnUGCpvm3UNtfYaQ7i1Hxxs/edit?usp=sharing&ouid=107825791787894292764&rtpof=true&sd=true)
:::spoiler
:::

## 解析

Mr30109
Mr30114
程式呼叫順序：

mr30109_sel.xml 呼叫 mr30109_prt
mr30109_prt 呼叫 mr30109 class
mr30109 execout getData
查看 ccloger 取得 Sql
Oracle 資料 與列印的資料一樣 (排除列印功能出錯的可能性)

mr30102 是有資料的?!
見鬼了.... 怎麼撈都沒有

SQL 應該沒錯 但資料撈不出來 (修改資料 或 修改 sql)
資料庫的資料與列印的結果一致


## 問題單內容
* 原始程式碼位置
* 目的程式碼位置
* 問題解析
* 處理方法
* 變更後測試執行結果

---

# 討論
:::spoiler

> 根據使用者反映的問題，似乎應該先檢查 mr30409_man ?

那是E28的 複製過來忘了改 sorry 😅
E26 題目
本處契約:110-6-0D
本案契約總量為34,800 M，
現總已通知交貨6批，合計量為29,400 M
故本案契約應剩5,400M 惟系統顯示該合約剩餘量惟600M

> 先檢查所謂系統顯示的畫面在哪裡，截圖

> 在mr30102 然後 這是mr30102 的sql 結果

系統顯示該合約剩餘量惟600M 在MR30114
可是問題不是計算邏輯...
是撈不出第二批次的資料(資料少一筆)
修改附加條件?
使用者交貨依據是mr30102的交貨通知維護
(有8筆 第一筆作廢 剩7筆)

> 使用者是在系統上看到，還是在報表內看到的
> 看到**系統顯示該合約剩餘量惟600M**是在哪個欄位，需要先知道他的計算邏輯才會知道差值從哪裡來，所以需要去比較這兩個：
> - **系統顯示該合約剩餘量惟600M**的計算邏輯
> - 交貨總量的計算邏輯
>
> 再進一步去判斷哪個是對的，這個 case 來說使用者應該是對的。然後按照對的邏輯去修改錯的。

> 所以目前的問題應該是這樣：交貨單(MAUR_TNOB)有資料，但是交貨狀況表(MAUR_CDVD)沒有資料。那要嘗試找出 MAUR_TNOB和MAUR_CDVD之間的連結，麻煩你先翻一下這兩個表的 Trigger 和交貨單產生/確定的程式

好 (Trigger 在xml? java? )
交貨單產生/確定的程式 在sql 裡面嗎~

> Trigger 在資料庫觸發程式找
> 交貨單產生/確定的程式 從系統邏輯找頁面再對回 jsp

收到~
為什麼是MAUR_TNOB 不是 MAUR_TNOH 阿? (交貨單 sql不是MAUR_TNOH嗎XD)
以及想詢問 MAUR_TNOB MAUR_CDVD 命名有什麼邏輯比較好猜測嗎~~

> TNOB (BODY)是交貨明細， TNOH (HEAD)是交貨單主檔，看總交貨金額要看明細加總，但是如果你看到主檔有那就是明細加總填過去的，方便撈資料而已。
> MAUR 契約相關 MAST 倉儲相關
> 除此之外沒有什麼命名邏輯

那 TNOH TNOB CDVD 都是什麼簡寫阿....
(還有 MAUR_CNOB,MAUR_CDVD,MAUR_ANOB,MAUR_ANOH ....

> 我猜 T transation 之類的 NO 就不知道了
> 一般就是看到 T H 就是交貨單頭，哪個英文字母代表什麼必要時要記一下
> 天曉得當初系統設計在想什麼...

辛苦ㄌQAQ....
~~看來要多吃葉黃素QAQ...~~

找不太出來 MAUR_TNOB和MAUR_CDVD之間的連結
但是 [MR30102](https://docs.google.com/document/d/1Ef6RalTrahv_K8hQ_ex4D9fEkxjr_Jcw/edit)
是會連動到 MAUR_CDVD的
同時我也查到命名ㄌ
MAUR_ANOB:[30114](https://docs.google.com/document/d/1I7DbywLzdxH62414Ic9u-BSO75kTZRUG/edit)
CDVD: [30109](https://docs.google.com/document/d/1FGG8DfxlpElK83xnnXwd3ThYKI1OyZpM/edit)
契約餘額（CNOB_AMT減CNOB_MAMT）
(話說 駐點的電腦不能用git 對嗎~)

> 兩年前可以，但後來資安規定更嚴格，不太曉得目前的情況

我找到 update & insert 的[Triger](https://github.com/weacloud/QA/tree/main/E26/%E5%8F%83%E8%80%83%E8%B3%87%E6%96%99)

實際可再通知剩量（CNOB_QTY減CNOB_MQTY）
公式檢查也是對的

猜測可能是觸發程式出問題(沒有觸發到)
導致只有一個table的資料是對的

> 直接以補上資料的方式處理
XDDDDD 是可以這樣的嗎XDDD
但我覺得很可以XDD
那我直接在 select下面 insert 嗎?

確認 MR30114 會用到以下 table MAUR_CNOH,MAUR_CNOB,MAUR_ANOB,MAUR_ANOH
複製出上面資料
準備 insert 到
table
MAUR_CNOH,
MAUR_CNOB,
MAUR_ANOB,
MAUR_ANOH
而
MR30102 會用到的是 MAUR_TNOH 故 理論上不會受影響
將直接 insert 同時 commit

我 選取了MAUR_CNOH 其中一項的全部內容 並要直接insert 到資料庫中
但報錯
無法複製內容 並 insert 抱歉 我好廢...

> 直接貼 sql 上來

insert into MAUR_CNOH (col1, col2, col3...)
SELECT 
MAUR_CNOH.CNOH_TRAN,
MAUR_CNOH.CNOH_AMT0,
MAUR_CNOH.CNOH_CSEQ1,
MAUR_CNOH.CNOH_BNO1,
MAUR_CNOH.CNOH_BNO2,
MAUR_CNOH.CNOH_BNO3,
MAUR_CNOH.CNOH_BNO4,
MAUR_CNOH.CNOH_BNO5,
MAUR_CNOH.CNOH_KIND3,
MAUR_CNOH.CNOH_KIND4,
MAUR_CNOH.CNOH_CKIND,
MAUR_CNOH.CNOH_CDATE1,
MAUR_CNOH.CNOH_CDATE2,
MAUR_CNOH.CNOH_NODATE,
MAUR_CNOH.CNOH_NONO,
MAUR_CNOH.CNOH_CDATE31,
MAUR_CNOH.CNOH_NO31,
MAUR_CNOH.CNOH_CDATE32,
MAUR_CNOH.CNOH_NO32,
MAUR_CNOH.CNOH_CDATE33,
MAUR_CNOH.CNOH_NO33,
MAUR_CNOH.CNOH_RATE,
MAUR_CNOH.CNOH_UDEPT,
MAUR_CNOH.CNOH_COMUDEPT,
MAUR_CNOH.CNOH_SKIND,
MAUR_CNOH.CNOH_NO,
MAUR_CNOH.CNOH_SPLACE,
MAUR_CNOH.CNOH_CHKIND,
MAUR_CNOH.CNOH_RATE1,
MAUR_CNOH.CNOH_AMT,
MAUR_CNOH.CNOH_AMT1,
MAUR_CNOH.CNOH_MAMT,
MAUR_CNOH.CNOH_SAMT,
MAUR_CNOH.CNOH_CHAMT,
MAUR_CNOH.CNOH_AMT2,
MAUR_CNOH.CNOH_IAMT,
MAUR_CNOH.CNOH_DAMT,
MAUR_CNOH.CNOH_KIND8,
MAUR_CNOH.CNOH_KIND9,
MAUR_CNOH.CNOH_SNO,
MAUR_CNOH.CNOH_END,
MAUR_CNOH.CNOH_EDATE,
MAUR_CNOH.CNOH_USERID,
MAUR_CNOH.CNOH_WDATE,
MAUR_CNOH.CNOH_WTIME,
MAUR_CNOH.CNOH_UPUSER,
MAUR_CNOH.CNOH_UPDATE,
MAUR_CNOH.CNOH_UPTIME,
MAUR_CNOH.CNOH_BITEM,
MAUR_CNOH.CNOH_WAMT,
MAUR_CNOH.CNOH_WARRANTY,
MAUR_CNOH.CNOH_WADATE,
MAUR_CNOH.CNOH_ENDDOC,
MAUR_CNOH.CNOH_ENDDATE,
MAUR_CNOH.CNOH_DISCHDOC,
MAUR_CNOH.CNOH_DISCHDATE,
MAUR_CNOH.CNOH_ARGDOC,
MAUR_CNOH.CNOH_ARGDATE,
MAUR_CNOH.CNOH_REDOC,
MAUR_CNOH.CNOH_REDATE,
MAUR_CNOH.CNOH_ANO,
MAUR_CNOH.CNOH_COMANO,
MAUR_CNOH.CNOH_CCODE,
MAUR_CNOH.CNOH_CYY,
MAUR_CNOH.CNOH_CSEQ,
CNOH_CDATE2,CNOH_SNO,
ANOH_TYPE,ANOB_MNAME,ANOB_SCALE,CDVD_BITEM,CDVD_PITEM,CDVD_ITEM,CDVD_MNO,NVL(CDVD_CQTY,0) 
AS CDVD_CQTY ,NVL(CDVD_CAMT,0) AS CDVD_CAMT,NVL(CDVD_MQTY,0) AS CDVD_MQTY ,NVL(CDVD_MAMT,0) 
AS CDVD_MAMT,NVL(CDVD_SQTY,0) AS  CDVD_SQTY ,NVL(CDVD_SAMT,0) AS CDVD_SAMT ,NVL(CDVD_CHQTY,0) 
AS CDVD_CHQTY ,NVL(CDVD_CHAMT,0) AS CDVD_CHAMT   
FROM MAUR_CNOH,MAUR_CNOB,MAUR_CDVD,MAUR_ANOB,MAUR_ANOH 
WHERE (MAUR_CNOH.CNOH_ANO = MAUR_CNOB.CNOB_ANO) 
AND (MAUR_CNOH.CNOH_CCODE = MAUR_CNOB.CNOB_CCODE) 
AND (MAUR_CNOH.CNOH_CYY = MAUR_CNOB.CNOB_CYY) 
AND (MAUR_CNOH.CNOH_CSEQ = MAUR_CNOB.CNOB_CSEQ) 
AND (MAUR_CNOH.CNOH_CSEQ1 = MAUR_CNOB.CNOB_CSEQ1) 
AND(MAUR_CNOB.CNOB_ANO = MAUR_CDVD.CDVD_ANO) AND (MAUR_CNOB.CNOB_CCODE = MAUR_CDVD.CDVD_CCODE) 
AND (MAUR_CNOB.CNOB_CYY = MAUR_CDVD.CDVD_CYY) AND (MAUR_CNOB.CNOB_CSEQ = MAUR_CDVD.CDVD_CSEQ) 
AND (MAUR_CNOB.CNOB_CSEQ1 = MAUR_CDVD.CDVD_CSEQ1) AND (MAUR_CNOB.CNOB_ITEM = MAUR_CDVD.CDVD_ITEM) 
AND (MAUR_CNOB.CNOB_BITEM = MAUR_CDVD.CDVD_BITEM) AND (MAUR_CNOB.CNOB_ANO = MAUR_ANOB.ANOB_ANO) 
AND (MAUR_CNOB.CNOB_PCODE = MAUR_ANOB.ANOB_PCODE) AND (MAUR_CNOB.CNOB_PYY = MAUR_ANOB.ANOB_PYY) 
AND (MAUR_CNOB.CNOB_PSEQ = MAUR_ANOB.ANOB_PSEQ) AND (MAUR_CNOB.CNOB_SEQ = MAUR_ANOB.ANOB_SEQ) 
AND (MAUR_ANOB.ANOB_ANO = MAUR_ANOH.ANOH_ANO) AND (MAUR_ANOB.ANOB_PCODE = MAUR_ANOH.ANOH_PCODE) 
AND (MAUR_ANOB.ANOB_PYY = MAUR_ANOH.ANOH_PYY) AND (MAUR_ANOB.ANOB_PSEQ = MAUR_ANOH.ANOH_PSEQ)    
AND CDVD_ANO='0D' AND CDVD_CCODE='N' AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D'  
AND CDVD_PITEM='3';

insert into MAUR_CNOH(
CNOH_TRAN,
CNOH_AMT0,
CNOH_CSEQ1,
CNOH_BNO1,
CNOH_BNO2,
CNOH_BNO3,
CNOH_BNO4,
CNOH_BNO5,
CNOH_KIND3,
CNOH_KIND4,
CNOH_CKIND,
CNOH_CDATE1,
CNOH_CDATE2,
CNOH_NODATE,
CNOH_NONO,
CNOH_CDATE31,
CNOH_NO31,
CNOH_CDATE32,
CNOH_NO32,
CNOH_CDATE33,
CNOH_NO33,
CNOH_RATE,
CNOH_UDEPT,
CNOH_COMUDEPT,
CNOH_SKIND,
CNOH_NO,
CNOH_SPLACE,
CNOH_CHKIND,
CNOH_RATE1,
CNOH_AMT,
CNOH_AMT1,
CNOH_MAMT,
CNOH_SAMT,
CNOH_CHAMT,
CNOH_AMT2,
CNOH_IAMT,
CNOH_DAMT,
CNOH_KIND8,
CNOH_KIND9,
CNOH_SNO,
CNOH_END,
CNOH_EDATE,
CNOH_USERID,
CNOH_WDATE,
CNOH_WTIME,
CNOH_UPUSER,
CNOH_UPDATE,
CNOH_UPTIME,
CNOH_BITEM,
CNOH_WAMT,
CNOH_WARRANTY,
CNOH_WADATE,
CNOH_ENDDOC,
CNOH_ENDDATE,
CNOH_DISCHDOC,
CNOH_DISCHDATE,
CNOH_ARGDOC,
CNOH_ARGDATE,
CNOH_REDOC,
CNOH_REDATE,
CNOH_ANO,
CNOH_COMANO,
CNOH_CCODE,
CNOH_CYY,
CNOH_CSEQ
)
SELECT 
MAUR_CNOH.CNOH_TRAN,
MAUR_CNOH.CNOH_AMT0,
MAUR_CNOH.CNOH_CSEQ1,
MAUR_CNOH.CNOH_BNO1,
MAUR_CNOH.CNOH_BNO2,
MAUR_CNOH.CNOH_BNO3,
MAUR_CNOH.CNOH_BNO4,
MAUR_CNOH.CNOH_BNO5,
MAUR_CNOH.CNOH_KIND3,
MAUR_CNOH.CNOH_KIND4,
MAUR_CNOH.CNOH_CKIND,
MAUR_CNOH.CNOH_CDATE1,
MAUR_CNOH.CNOH_CDATE2,
MAUR_CNOH.CNOH_NODATE,
MAUR_CNOH.CNOH_NONO,
MAUR_CNOH.CNOH_CDATE31,
MAUR_CNOH.CNOH_NO31,
MAUR_CNOH.CNOH_CDATE32,
MAUR_CNOH.CNOH_NO32,
MAUR_CNOH.CNOH_CDATE33,
MAUR_CNOH.CNOH_NO33,
MAUR_CNOH.CNOH_RATE,
MAUR_CNOH.CNOH_UDEPT,
MAUR_CNOH.CNOH_COMUDEPT,
MAUR_CNOH.CNOH_SKIND,
MAUR_CNOH.CNOH_NO,
MAUR_CNOH.CNOH_SPLACE,
MAUR_CNOH.CNOH_CHKIND,
MAUR_CNOH.CNOH_RATE1,
MAUR_CNOH.CNOH_AMT,
MAUR_CNOH.CNOH_AMT1,
MAUR_CNOH.CNOH_MAMT,
MAUR_CNOH.CNOH_SAMT,
MAUR_CNOH.CNOH_CHAMT,
MAUR_CNOH.CNOH_AMT2,
MAUR_CNOH.CNOH_IAMT,
MAUR_CNOH.CNOH_DAMT,
MAUR_CNOH.CNOH_KIND8,
MAUR_CNOH.CNOH_KIND9,
MAUR_CNOH.CNOH_SNO,
MAUR_CNOH.CNOH_END,
MAUR_CNOH.CNOH_EDATE,
MAUR_CNOH.CNOH_USERID,
MAUR_CNOH.CNOH_WDATE,
MAUR_CNOH.CNOH_WTIME,
MAUR_CNOH.CNOH_UPUSER,
MAUR_CNOH.CNOH_UPDATE,
MAUR_CNOH.CNOH_UPTIME,
MAUR_CNOH.CNOH_BITEM,
MAUR_CNOH.CNOH_WAMT,
MAUR_CNOH.CNOH_WARRANTY,
MAUR_CNOH.CNOH_WADATE,
MAUR_CNOH.CNOH_ENDDOC,
MAUR_CNOH.CNOH_ENDDATE,
MAUR_CNOH.CNOH_DISCHDOC,
MAUR_CNOH.CNOH_DISCHDATE,
MAUR_CNOH.CNOH_ARGDOC,
MAUR_CNOH.CNOH_ARGDATE,
MAUR_CNOH.CNOH_REDOC,
MAUR_CNOH.CNOH_REDATE,
MAUR_CNOH.CNOH_ANO,
MAUR_CNOH.CNOH_COMANO,
MAUR_CNOH.CNOH_CCODE,
MAUR_CNOH.CNOH_CYY,
MAUR_CNOH.CNOH_CSEQ
--CNOH_CDATE2,CNOH_SNO
--ANOH_TYPE,ANOB_MNAME,ANOB_SCALE,CDVD_BITEM,CDVD_PITEM,CDVD_ITEM,CDVD_MNO,NVL(CDVD_CQTY,0) 
--AS CDVD_CQTY ,NVL(CDVD_CAMT,0) AS CDVD_CAMT,NVL(CDVD_MQTY,0) AS CDVD_MQTY ,NVL(CDVD_MAMT,0) 
--AS CDVD_MAMT,NVL(CDVD_SQTY,0) AS  CDVD_SQTY ,NVL(CDVD_SAMT,0) AS CDVD_SAMT ,NVL(CDVD_CHQTY,0) 
--AS CDVD_CHQTY ,NVL(CDVD_CHAMT,0) AS CDVD_CHAMT   
FROM MAUR_CNOH,MAUR_CNOB,MAUR_CDVD,MAUR_ANOB,MAUR_ANOH 
WHERE (MAUR_CNOH.CNOH_ANO = MAUR_CNOB.CNOB_ANO) 
AND (MAUR_CNOH.CNOH_CCODE = MAUR_CNOB.CNOB_CCODE) 
AND (MAUR_CNOH.CNOH_CYY = MAUR_CNOB.CNOB_CYY) 
AND (MAUR_CNOH.CNOH_CSEQ = MAUR_CNOB.CNOB_CSEQ) 
AND (MAUR_CNOH.CNOH_CSEQ1 = MAUR_CNOB.CNOB_CSEQ1) 
AND(MAUR_CNOB.CNOB_ANO = MAUR_CDVD.CDVD_ANO) AND (MAUR_CNOB.CNOB_CCODE = MAUR_CDVD.CDVD_CCODE) 
AND (MAUR_CNOB.CNOB_CYY = MAUR_CDVD.CDVD_CYY) AND (MAUR_CNOB.CNOB_CSEQ = MAUR_CDVD.CDVD_CSEQ) 
AND (MAUR_CNOB.CNOB_CSEQ1 = MAUR_CDVD.CDVD_CSEQ1) AND (MAUR_CNOB.CNOB_ITEM = MAUR_CDVD.CDVD_ITEM) 
AND (MAUR_CNOB.CNOB_BITEM = MAUR_CDVD.CDVD_BITEM) AND (MAUR_CNOB.CNOB_ANO = MAUR_ANOB.ANOB_ANO) 
AND (MAUR_CNOB.CNOB_PCODE = MAUR_ANOB.ANOB_PCODE) AND (MAUR_CNOB.CNOB_PYY = MAUR_ANOB.ANOB_PYY) 
AND (MAUR_CNOB.CNOB_PSEQ = MAUR_ANOB.ANOB_PSEQ) AND (MAUR_CNOB.CNOB_SEQ = MAUR_ANOB.ANOB_SEQ) 
AND (MAUR_ANOB.ANOB_ANO = MAUR_ANOH.ANOH_ANO) AND (MAUR_ANOB.ANOB_PCODE = MAUR_ANOH.ANOH_PCODE) 
AND (MAUR_ANOB.ANOB_PYY = MAUR_ANOH.ANOH_PYY) AND (MAUR_ANOB.ANOB_PSEQ = MAUR_ANOH.ANOH_PSEQ)    
AND CDVD_ANO='0D' AND CDVD_CCODE='N' AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D'  
AND CDVD_PITEM='3';

:::

今天要查triger對ㄇ
:::spoiler


create or replace TRIGGER "FTLMR"."MAUR_CNOH_BEFORE_INS" BEFORE
INSERT ON "MAUR_CNOH" FOR EACH ROW DECLARE
  t_seq      NUMBER;

  t_bno1     NUMBER;
  t_bno2     VARCHAR2(1);
  t_bno3     VARCHAR2(1);
  t_bno4     NUMBER;
  t_bno5     NUMBER;
  t_kind3    VARCHAR2(1);
  t_kind4    VARCHAR2(1);
  t_ckind    VARCHAR2(1);
  t_cdate1   NUMBER;
  t_cdate2   NUMBER;
  t_nodate   NUMBER;
  t_nono     VARCHAR2(10);
  t_cdate31  NUMBER;
  t_no31     VARCHAR2(10);
  t_cdate32  NUMBER;
  t_no32     VARCHAR2(10);
  t_cdate33  NUMBER;
  t_no33     VARCHAR2(10);
  t_chkind   VARCHAR2(1);
  t_rate1    NUMBER;
  t_mamt     NUMBER;
  t_samt     NUMBER;
  t_chamt    NUMBER;
  t_amt2     NUMBER;
  t_edate    NUMBER;
  t_warranty NUMBER;
  t_bitem    NUMBER;

  -- used by 總契約
  CURSOR APPB_CRSR IS
    SELECT APPB_ITEM, APPB_MNO, APPB_QTY, APPB_PCODE, APPB_PYY,
           APPB_PSEQ, APPB_SEQ, APPB_WNO, APPB_MNAME, APPB_SCALE,
           APPB_UNITN
    FROM MAUR_APPB
    WHERE APPB_ANO    = :NEW.CNOH_ANO   AND
          APPB_BNO1   = :NEW.CNOH_BNO1  AND
          APPB_BNO2   = :NEW.CNOH_BNO2  AND
          APPB_BNO3   = :NEW.CNOH_BNO3  AND
          APPB_BNO4   = :NEW.CNOH_BNO4  AND
          APPB_BNO5   = :NEW.CNOH_BNO5  AND
          APPB_BITEM  = :NEW.CNOH_BITEM  ;

  -- used by 子契約
  CURSOR CNOB_CRSR IS
    SELECT CNOB_BITEM, CNOB_ITEM, CNOB_MNO, CNOB_QTY, CNOB_PRICE,
           CNOB_AMT, CNOB_AMT1, CNOB_AMT2, CNOB_MQTY, CNOB_MAMT,
           CNOB_BNO1, CNOB_BNO2, CNOB_BNO3, CNOB_BNO4, CNOB_BNO5,
           CNOB_PCODE, CNOB_PYY, CNOB_PSEQ, CNOB_SEQ, CNOB_WNO,
           CNOB_END, CNOB_EDATE, CNOB_MNAME, CNOB_SCALE, CNOB_UNITN
    FROM MAUR_CNOB
    WHERE CNOB_ANO   = :NEW.CNOH_ANO   AND
          CNOB_CCODE = :NEW.CNOH_CCODE AND
          CNOB_CYY   = :NEW.CNOH_CYY   AND
          CNOB_CSEQ  = :NEW.CNOH_CSEQ  AND
          CNOB_CSEQ1 = '00'             ;

BEGIN
/**
 * @author Samuel C. Fan
 * @last update: Jan. 20, 2006
 *
 * @Comment: (11/17/2005) 威宇要求勿預設單價為請購單價，單價一律空白
 *
 *           (01/20/2006) 忘記把總契約的契約單頭標項複製到子契約的契約單頭，現在追加
 **/

  -------------------- START 子契約 --------------------
  IF (:NEW.CNOH_WDATE = '99999999') THEN
    -- get data from 總契約; where CNOH_CSEQ1 = '00'
    SELECT CNOH_BNO1, CNOH_BNO2, CNOH_BNO3, CNOH_BNO4, CNOH_BNO5,
           CNOH_KIND3, CNOH_KIND4, CNOH_CKIND, CNOH_CDATE1, CNOH_CDATE2,
           CNOH_NODATE, CNOH_NONO, CNOH_CDATE31, CNOH_NO31, CNOH_CDATE32,
           CNOH_NO32, CNOH_CDATE33, CNOH_NO33, CNOH_CHKIND, CNOH_RATE1,
           CNOH_MAMT, CNOH_SAMT, CNOH_CHAMT, CNOH_AMT2, CNOH_EDATE,
           CNOH_WARRANTY, CNOH_BITEM
      INTO t_bno1, t_bno2, t_bno3, t_bno4, t_bno5,
           t_kind3, t_kind4, t_ckind, t_cdate1, t_cdate2,
           t_nodate, t_nono, t_cdate31, t_no31, t_cdate32,
           t_no32, t_cdate33, t_no33, t_chkind, t_rate1,
           t_mamt, t_samt, t_chamt, t_amt2, t_edate,
           t_warranty, t_bitem
    FROM MAUR_CNOH
    WHERE CNOH_ANO   = :NEW.CNOH_ANO   AND
          CNOH_CCODE = :NEW.CNOH_CCODE AND
          CNOH_CYY   = :NEW.CNOH_CYY   AND
          CNOH_CSEQ  = :NEW.CNOH_CSEQ  AND
          CNOH_CSEQ1 = '00'             ;

    -- write into 子契約
    :NEW.CNOH_BNO1     := t_bno1;
    :NEW.CNOH_BNO2     := t_bno2;
    :NEW.CNOH_BNO3     := t_bno3;
    :NEW.CNOH_BNO4     := t_bno4;
    :NEW.CNOH_BNO5     := t_bno5;
    :NEW.CNOH_KIND3    := t_kind3;
    :NEW.CNOH_KIND4    := t_kind4;
    :NEW.CNOH_CKIND    := t_ckind;
    :NEW.CNOH_CDATE1   := t_cdate1;
    :NEW.CNOH_CDATE2   := t_cdate2;
    :NEW.CNOH_NODATE   := t_nodate;
    :NEW.CNOH_NONO     := t_nono;
    :NEW.CNOH_CDATE31  := t_cdate31;
    :NEW.CNOH_NO31     := t_no31;
    :NEW.CNOH_CDATE32  := t_cdate32;
    :NEW.CNOH_NO32     := t_no32;
    :NEW.CNOH_CDATE33  := t_cdate33;
    :NEW.CNOH_NO33     := t_no33;
    :NEW.CNOH_CHKIND   := t_chkind;
    :NEW.CNOH_RATE1    := t_rate1;
    :NEW.CNOH_MAMT     := t_mamt;
    :NEW.CNOH_SAMT     := t_samt;
    :NEW.CNOH_CHAMT    := t_chamt;
    :NEW.CNOH_AMT2     := t_amt2;
    :NEW.CNOH_EDATE    := t_edate;
    :NEW.CNOH_WARRANTY := t_warranty;
    :NEW.CNOH_BITEM    := t_bitem;

    -- insert 子契約 into 契約單身資料表(MAUR_CNOB)
    FOR INS IN CNOB_CRSR LOOP
      INSERT INTO MAUR_CNOB (CNOB_ANO, CNOB_CCODE, CNOB_CYY, CNOB_CSEQ, CNOB_CSEQ1,
                             CNOB_BITEM, CNOB_ITEM, CNOB_MNO, CNOB_QTY, CNOB_PRICE,
                             CNOB_AMT, CNOB_AMT1, CNOB_AMT2, CNOB_MQTY, CNOB_MAMT,
                             CNOB_BNO1, CNOB_BNO2, CNOB_BNO3, CNOB_BNO4, CNOB_BNO5,
                             CNOB_PCODE, CNOB_PYY, CNOB_PSEQ, CNOB_SEQ, CNOB_WNO,
                             CNOB_END, CNOB_EDATE, CNOB_MNAME, CNOB_SCALE, CNOB_UNITN,
                             CNOB_UPUSER, CNOB_UPDATE, CNOB_UPTIME)
      VALUES (:NEW.CNOH_ANO, :NEW.CNOH_CCODE, :NEW.CNOH_CYY, :NEW.CNOH_CSEQ, :NEW.CNOH_CSEQ1,
              INS.CNOB_BITEM, INS.CNOB_ITEM, INS.CNOB_MNO, INS.CNOB_QTY, INS.CNOB_PRICE,
              INS.CNOB_AMT, INS.CNOB_AMT1, INS.CNOB_AMT2, INS.CNOB_MQTY, INS.CNOB_MAMT,
              INS.CNOB_BNO1, INS.CNOB_BNO2, INS.CNOB_BNO3, INS.CNOB_BNO4, INS.CNOB_BNO5,
              INS.CNOB_PCODE, INS.CNOB_PYY, INS.CNOB_PSEQ, INS.CNOB_SEQ, INS.CNOB_WNO,
              INS.CNOB_END, INS.CNOB_EDATE, INS.CNOB_MNAME, INS.CNOB_SCALE, INS.CNOB_UNITN,
              :NEW.CNOH_USERID, F_GETDATETIME('D'), F_GETDATETIME('T'));
    END LOOP;
  --------------------  END 子契約  --------------------
  -------------------- START 總契約 --------------------
  ELSE
    -- get 採購類別、採購性質、購案性質 from MAUR_APPH
    SELECT APPH_KIND3, APPH_KIND4, APPH_KIND5
      INTO t_kind3, t_kind4, t_ckind
    FROM MAUR_APPH
    WHERE APPH_ANO  = :NEW.CNOH_ANO  AND
          APPH_BNO1 = :NEW.CNOH_BNO1 AND
          APPH_BNO2 = :NEW.CNOH_BNO2 AND
          APPH_BNO3 = :NEW.CNOH_BNO3 AND
          APPH_BNO4 = :NEW.CNOH_BNO4 AND
          APPH_BNO5 = :NEW.CNOH_BNO5;

    IF NVL(:NEW.CNOH_CSEQ,0) = 0 THEN
       -- automatically create 契約字號（流水號）
       SELECT NVL(MAX(CNOH_CSEQ), 0) + 1
         INTO t_seq
       FROM MAUR_CNOH
       WHERE CNOH_ANO   = :NEW.CNOH_ANO   AND
             CNOH_CCODE = :NEW.CNOH_CCODE AND
             CNOH_CYY   = :NEW.CNOH_CYY   AND
             CNOH_CSEQ1 = :NEW.CNOH_CSEQ1  ;

       :NEW.CNOH_CSEQ  := t_seq;    -- latest created 流水號
    END IF;

    :NEW.CNOH_KIND3 := t_kind3;  -- 採購類別
    :NEW.CNOH_KIND4 := t_kind4;  -- 採購性質
    :NEW.CNOH_CKIND := t_ckind;  -- 購案性質

    -- write the latest 契約字號 back to 投標資料表(MAUR_ABID)
    UPDATE MAUR_ABID
    SET ABID_CCODE  = :NEW.CNOH_CCODE,
        ABID_CYY    = :NEW.CNOH_CYY,
        ABID_CSEQ   = :NEW.CNOH_CSEQ,
        ABID_CSEQ1  = :NEW.CNOH_CSEQ1,
        ABID_UPUSER = :NEW.CNOH_USERID
    WHERE ABID_ANO   = :NEW.CNOH_ANO  AND
          ABID_BNO1  = :NEW.CNOH_BNO1 AND
          ABID_BNO2  = :NEW.CNOH_BNO2 AND
          ABID_BNO3  = :NEW.CNOH_BNO3 AND
          ABID_BNO4  = :NEW.CNOH_BNO4 AND
          ABID_BNO5  = :NEW.CNOH_BNO5 AND
          ABID_BITEM = :NEW.CNOH_BITEM ;

    FOR INS IN APPB_CRSR LOOP
      -- insert into 契約單身資料表(MAUR_CNOB)
      INSERT INTO MAUR_CNOB (CNOB_ANO, CNOB_CCODE, CNOB_CYY, CNOB_CSEQ, CNOB_CSEQ1,
                             CNOB_BITEM, CNOB_ITEM, CNOB_MNO, CNOB_QTY, CNOB_BNO1,
                             CNOB_BNO2, CNOB_BNO3, CNOB_BNO4, CNOB_BNO5, CNOB_PCODE,
                             CNOB_PYY, CNOB_PSEQ, CNOB_SEQ, CNOB_WNO, CNOB_MNAME,
                             CNOB_SCALE, CNOB_UNITN, CNOB_END, CNOB_UPUSER, CNOB_UPDATE,
                             CNOB_UPTIME)
      VALUES (:NEW.CNOH_ANO, :NEW.CNOH_CCODE, :NEW.CNOH_CYY, :NEW.CNOH_CSEQ, :NEW.CNOH_CSEQ1,
              :NEW.CNOH_BITEM, INS.APPB_ITEM, INS.APPB_MNO, INS.APPB_QTY, :NEW.CNOH_BNO1,
              :NEW.CNOH_BNO2, :NEW.CNOH_BNO3, :NEW.CNOH_BNO4, :NEW.CNOH_BNO5, INS.APPB_PCODE,
              INS.APPB_PYY, INS.APPB_PSEQ, INS.APPB_SEQ, INS.APPB_WNO, INS.APPB_MNAME,
              INS.APPB_SCALE, INS.APPB_UNITN, '0', :NEW.CNOH_USERID, F_GETDATETIME('D'),
              F_GETDATETIME('T'));
/*
      -- insert into 契約交貨狀況資料表(MAUR_CDVD)
      INSERT INTO MAUR_CDVD (CDVD_ANO, CDVD_CCODE, CDVD_CYY, CDVD_CSEQ, CDVD_CSEQ1,
                             CDVD_BITEM, CDVD_ITEM, CDVD_PITEM, CDVD_MNO, CDVD_CQTY,
                             CDVD_USERID, CDVD_WDATE, CDVD_WTIME)
      VALUES (:NEW.CNOH_ANO, :NEW.CNOH_CCODE, :NEW.CNOH_CYY, :NEW.CNOH_CSEQ, :NEW.CNOH_CSEQ1,
              :NEW.CNOH_BITEM, INS.APPB_ITEM, '1', INS.APPB_MNO, INS.APPB_QTY,
              :NEW.CNOH_USERID, F_GETDATETIME('D'), F_GETDATETIME('T'));
*/
    END LOOP;
    --------------------  END 總契約  --------------------
  END IF;

  :NEW.CNOH_WDATE := F_GETDATETIME('D');
  :NEW.CNOH_WTIME := F_GETDATETIME('T');
  :NEW.CNOH_COMANO := F_GETCOMANO(:NEW.CNOH_ANO);
  :NEW.CNOH_COMUDEPT := F_GETCOMANO(:NEW.CNOH_UDEPT);

END;

:::
## Mr30114

SELECT  
	ANOH_TYPE, ANOB_MNAME, ANOB_SCALE,
	CNOH_CDATE2, CNOH_SNO,
	CNOB_ANO, CNOB_CCODE, CNOB_CYY, CNOB_CSEQ, CNOB_CSEQ1, CNOB_MNO, 
	NVL(CNOB_QTY,0) AS CNOB_QTY, -- 34800
	NVL(CNOB_MQTY,0) AS CNOB_MQTY, -- 34200
	NVL(CNOB_QTY,0) - NVL(CNOB_MQTY,0) AS 實際可再通知剩量, -- 34800-34200=600
	NVL(CNOB_PRICE,0) AS CNOB_PRICE,
	NVL(CNOB_AMT,0) AS CNOB_AMT ,
	NVL(CNOB_MAMT,0) AS CONB_MAMT  
FROM MAUR_CNOH,MAUR_CNOB,MAUR_ANOB,MAUR_ANOH 
WHERE (MAUR_CNOH.CNOH_ANO = MAUR_CNOB.CNOB_ANO) 
	AND (MAUR_CNOH.CNOH_CCODE = MAUR_CNOB.CNOB_CCODE) 
	AND (MAUR_CNOH.CNOH_CYY = MAUR_CNOB.CNOB_CYY) 
	AND (MAUR_CNOH.CNOH_CSEQ = MAUR_CNOB.CNOB_CSEQ) 
	AND (MAUR_CNOH.CNOH_CSEQ1 = MAUR_CNOB.CNOB_CSEQ1) 
	AND (MAUR_CNOB.CNOB_ANO = MAUR_ANOB.ANOB_ANO) 
	AND (MAUR_CNOB.CNOB_PCODE = MAUR_ANOB.ANOB_PCODE) 
	AND (MAUR_CNOB.CNOB_PYY = MAUR_ANOB.ANOB_PYY) 
	AND (MAUR_CNOB.CNOB_PSEQ = MAUR_ANOB.ANOB_PSEQ) 
	AND (MAUR_CNOB.CNOB_SEQ = MAUR_ANOB.ANOB_SEQ) 
	AND (MAUR_ANOB.ANOB_ANO = MAUR_ANOH.ANOH_ANO) 
	AND (MAUR_ANOB.ANOB_PCODE = MAUR_ANOH.ANOH_PCODE) 
	AND (MAUR_ANOB.ANOB_PYY = MAUR_ANOH.ANOH_PYY) 
	AND (MAUR_ANOB.ANOB_PSEQ = MAUR_ANOH.ANOH_PSEQ) 
	AND CNOB_ANO='0D' AND CNOB_CCODE='N' AND CNOB_CYY='110' 
	AND CNOB_CSEQ='06' AND CNOB_CSEQ1='0D'  
ORDER BY CNOB_ANO,CNOB_CCODE,CNOB_CYY,CNOB_CSEQ,CNOB_CSEQ1,CNOB_BITEM ,CONB_ITEM   

MR30114
:::spoiler
:::
實際可再通知剩量（CNOB_QTY減CNOB_MQTY
600=CNOB_QTY - CNOB_MQTY = 34800 -34200
## Mr30109

SELECT 
	ANOH_TYPE, ANOB_MNAME, ANOB_SCALE, 
	CDVD_BITEM, CDVD_PITEM, CDVD_ITEM, CDVD_MNO,
        -- 缺少一筆，導致目前 SUM(CDVD_CQTY) = 24600，補上一筆 4800 = 29400
	NVL(CDVD_CQTY,0) AS CDVD_CQTY , 
	NVL(CDVD_CAMT,0) AS CDVD_CAMT,
	NVL(CDVD_MQTY,0) AS CDVD_MQTY , 
	NVL(CDVD_MAMT,0) AS CDVD_MAMT,
	NVL(CDVD_SQTY,0) AS  CDVD_SQTY ,
	NVL(CDVD_SAMT,0) AS CDVD_SAMT ,
	NVL(CDVD_CHQTY,0) AS CDVD_CHQTY ,
	NVL(CDVD_CHAMT,0) AS CDVD_CHAMT   
FROM MAUR_CNOB,MAUR_CDVD,MAUR_ANOB,MAUR_ANOH 
WHERE  
	(MAUR_CNOB.CNOB_ANO = MAUR_CDVD.CDVD_ANO) 
	AND (MAUR_CNOB.CNOB_CCODE = MAUR_CDVD.CDVD_CCODE) 
	AND (MAUR_CNOB.CNOB_CYY = MAUR_CDVD.CDVD_CYY) 
	AND (MAUR_CNOB.CNOB_CSEQ = MAUR_CDVD.CDVD_CSEQ) 
	AND (MAUR_CNOB.CNOB_CSEQ1 = MAUR_CDVD.CDVD_CSEQ1) 
	AND (MAUR_CNOB.CNOB_ITEM = MAUR_CDVD.CDVD_ITEM) 
	AND (MAUR_CNOB.CNOB_BITEM = MAUR_CDVD.CDVD_BITEM) 
	AND (MAUR_CNOB.CNOB_ANO = MAUR_ANOB.ANOB_ANO) 
	AND (MAUR_CNOB.CNOB_PCODE = MAUR_ANOB.ANOB_PCODE) 
	AND (MAUR_CNOB.CNOB_PYY = MAUR_ANOB.ANOB_PYY) 
	AND (MAUR_CNOB.CNOB_PSEQ = MAUR_ANOB.ANOB_PSEQ) 
	AND (MAUR_CNOB.CNOB_SEQ = MAUR_ANOB.ANOB_SEQ) 
	AND (MAUR_ANOB.ANOB_ANO = MAUR_ANOH.ANOH_ANO) 
	AND (MAUR_ANOB.ANOB_PCODE = MAUR_ANOH.ANOH_PCODE) 
	AND (MAUR_ANOB.ANOB_PYY = MAUR_ANOH.ANOH_PYY) 
	AND (MAUR_ANOB.ANOB_PSEQ = MAUR_ANOH.ANOH_PSEQ)    
	AND CDVD_ANO='0D' AND CDVD_CCODE='N' AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D'  
ORDER BY CDVD_BITEM,CDVD_PITEM,CDVD_ITEM   

MR30114
缺少一筆 CDVD_CQTY 4800

SELECT * FROM  MAUR_CNOB WHERE CNOB_ANO='0D' AND CNOB_CCODE='N' AND CNOB_CYY='110' AND CNOB_CSEQ='06' AND CNOB_CSEQ1='0D';

SELECT * FROM MAUR_CDVD WHERE CDVD_ANO='0D' AND CDVD_CCODE='N' AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D';

SELECT A.CNOB_ANO, B.ANOB_ANO, C.ANOH_TYPE FROM MAUR_CNOB A
LEFT JOIN MAUR_ANOB B ON A.CNOB_ANO=B.ANOB_ANO AND A.CNOB_PCODE=B.ANOB_PCODE AND A.CNOB_PYY=B.ANOB_PYY AND A.CNOB_PSEQ=B.ANOB_PSEQ AND A.CNOB_SEQ=B.ANOB_SEQ 
LEFT JOIN MAUR_ANOH C ON B.ANOB_ANO=C.ANOH_ANO AND B.ANOB_PCODE=C.ANOH_PCODE AND B.ANOB_PYY=C.ANOH_PYY AND B.ANOB_PSEQ=C.ANOH_PSEQ
WHERE A.CNOB_ANO='0D' AND A.CNOB_CCODE='N' AND A.CNOB_CYY='110' AND A.CNOB_CSEQ='06' AND A.CNOB_CSEQ1='0D';

1.
2.
3.

DISABLE TRIGGER ALL ON MAUR_CNOB;
UPDATE MAUR_CNOB SET CNOB_MQTY='29400' WHERE ...;
ENABLE TRIGGER ALL ON MAUR_CNOB; 

DISABLE TRIGGER ALL ON MAUR_CNOB;
insert into MAUR_CDVD () values ()...;
ENABLE TRIGGER ALL ON TableName; 

> 要做為 script 執行：
> 每一個 disable->sql->enable 務必要一次執行，避免影響到其他的動作
> 來不及全線查修，先改資料，其他單位有再反映問題再改

所以最後要commit對嗎
where後面放select的資料嗎
values 直接複製 第一批次的資料嗎

DISABLE TRIGGER ALL ON MAUR_CNOB;
insert into MAUR_CDVD (
CDVD_ANO,CDVD_COMANO,CDVD_CCODE,
CDVD_CYY,CDVD_CSEQ,CDVD_CSEQ1,
CDVD_ITEM,CDVD_PITEM,CDVD_BITEM,
CDVD_MNO,CDVD_CQTY,CDVD_MQTY,CDVD_SQTY,
CDVD_CHQTY,CDVD_CAMT,CDVD_MAMT,CDVD_SAMT,
CDVD_CHAMT,CDVD_END,CDVD_EDATE,CDVD_USERID,
CDVD_WDATE,CDVD_WTIME,CDVD_UPUSER,CDVD_UPDATE,
CDVD_UPTIME) 
values (
'0D','1300005','N','110',
'6','0D','1','2','1','A23233200016',
'4800','4800','4800','4800',
'6216000','6216000','6216000','6216000',
'0','','107176','1110318',
'141413336295','110679','1110707','83028469268');
ENABLE TRIGGER ALL ON TableName;


SELECT * FROM MAUR_CDVD WHERE CDVD_ANO='0D' AND CDVD_CCODE='N' 
AND CDVD_CYY='110' AND CDVD_CSEQ='06' AND CDVD_CSEQ1='0D' ;


DISABLE TRIGGER ALL ON MAUR_CNOB;
UPDATE MAUR_CNOB SET CNOB_MQTY=29400 WHERE
CNOB_ANO='0D' AND CNOB_CCODE='N' AND CNOB_CYY='110' 
AND CNOB_CSEQ='06' AND CNOB_CSEQ1='0D';
ENABLE TRIGGER ALL ON MAUR_CNOB; 

SELECT * FROM  MAUR_CNOB WHERE CNOB_ANO='0D' AND CNOB_CCODE='N' 
AND CNOB_CYY='110' AND CNOB_CSEQ='06' AND CNOB_CSEQ1='0D';


如果這兩個沒問題的話
我儲存變更囉~