物品管理系統資料操作邏輯分析 - 管理換(払)承認済

| SQL | 19 Reads

一、前提說明

本次分析基於以下操作場景:

  • 處理類型:「新規取得 C-00009 - 數量 4」

  • 操作方式:1 組「まとめる」,2 組「まとめない」

  • 觸發機制:PostgreSQL Trigger + 通知通道 table_update_channel 實時推送操作結果

  • 總操作數:28 條資料操作語句(UPDATE、INSERT 為主)


二、操作結構總覽

✅ 1. 承認流程(共 3 條)

操作 表名 備註
UPDATE kanrigae_tbl 更新承認狀態、處理進度等欄位
UPDATE ×2 kanrigae_meisai_tbl 每筆明細一條

更新承認狀態:

UPDATE on [kanrigae_tbl] (5 changes)                                                                                
doc_cd p_doc_cd seiri_kbn uketuke_ymd s_kanrikan_cd s_kanri_ymd s_kanri_doc_no s_kanrikan_kikan_name s_kanrikan_sisho_name s_kanrikan_name s_suitoukan_cd s_suitoukan_kikan_name s_suitoukan_sisho_name s_suitoukan_name s_kyouyoukan_cd s_kyouyoukan_kikan_name s_kyouyoukan_sisho_name s_kyouyoukan_name r_kanrikan_cd r_kanri_ymd r_kanri_doc_no r_kanrikan_kikan_name r_kanrikan_sisho_name r_kanrikan_name r_suitoukan_cd r_suitoukan_kikan_name r_suitoukan_sisho_name r_suitoukan_name r_kyouyoukan_cd r_kyouyoukan_kikan_name r_kyouyoukan_sisho_name r_kyouyoukan_name ukeharai_kbn kikan_kbn st ed konkyo hassou_moto atesaki hassou_ymd yusou_houhou touchaku_yotei hikiwatasi_basho hikiwatasi_nin touchaku_ymd shori_sts ins_op_cd ins_ymd upd_op_cd upd_ymd
573132   601 2025/03/24 0:00:00 10000   TEST-04-交換               10004       15000                   15012       1 0 2025/03/24 0:00:00   防衛装備庁⇒防衛装備庁航空装備研究所test 防衛装備庁 防衛装備庁航空装備研究所             50 10004 2025/03/24 14:29:17    
573132   601 2025/03/24 0:00:00 10000 2025/03/24 0:00:00 TEST-04-交換               10004       15000 2025/03/24 0:00:00                 15012       1 0 2025/03/24 0:00:00   防衛装備庁⇒防衛装備庁航空装備研究所test 防衛装備庁 防衛装備庁航空装備研究所             99 10004 2025/03/24 14:29:17 10000 2025/03/24 0:00:00

更新明細:

UPDATE on [kanrigae_meisai_tbl] (2 changes)                        
doc_cd meisai_no buppin_cd kingaku hassou_suryo konsu housou_shu juuryou juryo_suryo tekiyou kikan_kbn st ed hatti chakuti yusou_houhou yusou_tantou bikou ins_op_cd ins_ymd upd_op_cd upd_ymd
573132 1 C-00009 3000000 2           0               10004 2025/03/24 14:29:17    
573132 1 C-00009 3000000 2           0               10004 2025/03/24 14:29:17 10000 2025/03/24 0:00:00

UPDATE on [kanrigae_meisai_tbl] (2 changes)                        
doc_cd meisai_no buppin_cd kingaku hassou_suryo konsu housou_shu juuryou juryo_suryo tekiyou kikan_kbn st ed hatti chakuti yusou_houhou yusou_tantou bikou ins_op_cd ins_ymd upd_op_cd upd_ymd
573132 2 C-00009 6000000 2           0               10004 2025/03/24 14:29:17    
573132 2 C-00009 6000000 2           0               10004 2025/03/24 14:29:17 10000 2025/03/24 0:00:00

✅ 2. 移動元(まとめる / まとめない)操作(共 7 條)

まとめる(1 組):

操作 表名
INSERT kanribo_tbl
INSERT kyouyoubo_tbl
UPDATE link_tbl

INSERT on [kanribo_tbl]                    
kanribo_cd buppin_cd shoumou_kbn kanrikan_cd kasitu_cd ymd kanribo_seiri_kbn tekiyou idou_su split_idou_su idou_kakaku sonota_su split_sonota_su bikou ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg seiri_no
2488328 C-00009 0 10000 1000 2025/03/24 0:00:00 71 TEST-04-交換 -2   -3000000   1 防衛装備庁⇒防衛装備庁航空装備研究所test 10000 2025/03/24 14:32:49        

INSERT on [kyouyoubo_tbl]                    
kyouyoubo_cd buppin_cd shoumou_kbn kyouyoukan_cd kakari_cd siyousha_cd ymd kyouyoubo_seiri_kbn tekiyou idou_su split_idou_su idou_kakaku sonota_su split_sonota_su bikou ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg
2348853 C-00009 0 10004 1000 10001 2025/03/24 0:00:00 2 TEST-04-交換 -2   -3000000   1 防衛装備庁⇒防衛装備庁航空装備研究所test 10000 2025/03/24 14:32:49      

UPDATE on [link_tbl] (5 changes)        
link_cd original_kobetu_cd kobetu_cd matome_cd_uke kanribo_cd_uke kyouyoubo_cd_uke matome_cd_harai kanribo_cd_harai kyouyoubo_cd_harai ins_op_cd ins_ymd upd_op_cd upd_ymd buppin_cd
1974240   1277302 4010101 2488288 2348813       10004 2025/03/24 14:17:26     C-00009
1974240   1277302 4010101 2488288 2348813 4010141 2488328 2348853 10004 2025/03/24 14:17:26 10000 2025/03/24 0:00:00 C-00009

 

まとめない(2 組,2個 kobetu_cd):

操作 表名
INSERT kanribo_tbl
INSERT kyouyoubo_tbl
UPDATE link_tbl ×2

INSERT on [kanribo_tbl]                    
kanribo_cd buppin_cd shoumou_kbn kanrikan_cd kasitu_cd ymd kanribo_seiri_kbn tekiyou idou_su split_idou_su idou_kakaku sonota_su split_sonota_su bikou ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg seiri_no
2488348 C-00009 0 10000 1000 2025/03/24 0:00:00 71 TEST-04-交換 -2   -6000000   1 防衛装備庁⇒防衛装備庁航空装備研究所test 10000 2025/03/24 14:32:50        

INSERT on [kyouyoubo_tbl]                    
kyouyoubo_cd buppin_cd shoumou_kbn kyouyoukan_cd kakari_cd siyousha_cd ymd kyouyoubo_seiri_kbn tekiyou idou_su split_idou_su idou_kakaku sonota_su split_sonota_su bikou ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg
2348873 C-00009 0 10004 1001 10002 2025/03/24 0:00:00 2 TEST-04-交換 -2   -6000000   1 防衛装備庁⇒防衛装備庁航空装備研究所test 10000 2025/03/24 14:32:50      

UPDATE on [link_tbl] (5 changes)        
link_cd original_kobetu_cd kobetu_cd matome_cd_uke kanribo_cd_uke kyouyoubo_cd_uke matome_cd_harai kanribo_cd_harai kyouyoubo_cd_harai ins_op_cd ins_ymd upd_op_cd upd_ymd buppin_cd
1974260   1277322 4010121 2488308 2348833       10004 2025/03/24 14:17:26     C-00009
1974260   1277322 4010121 2488308 2348833 4010161 2488348 2348873 10004 2025/03/24 14:17:26 10000 2025/03/24 0:00:00 C-00009

UPDATE on [link_tbl] (5 changes)        
link_cd original_kobetu_cd kobetu_cd matome_cd_uke kanribo_cd_uke kyouyoubo_cd_uke matome_cd_harai kanribo_cd_harai kyouyoubo_cd_harai ins_op_cd ins_ymd upd_op_cd upd_ymd buppin_cd
1974280   1277342 4010121 2488308 2348833       10004 2025/03/24 14:17:27     C-00009
1974280   1277342 4010121 2488308 2348833 4010161 2488348 2348873 10004 2025/03/24 14:17:27 10000 2025/03/24 0:00:00 C-00009

 

📌 まとめない 時,依據對象物數量(N),會產生 N 條 link_tbl 更新。


✅ 3. 既存個別物品處理(共 6 條)

每筆舊物品對應:

操作 表名
UPDATE kobetu_tbl
INSERT zougen_rireki_tbl

共 3 組,共 6 條操作。

UPDATE on [kobetu_tbl] (4 changes)                                    
kobetu_cd p_kobetu_cd buppin_cd shutoku_ymd haraidasi_ymd buppin_kan_no gyousha_cd tanka zei_gaku hinsitu_kikaku kanrikan_cd kyouyoukan_cd siyousha_cd hontai_no zougen_kbn shoumou_kbn zou_kbn gen_kbn zou_riyuu gen_riyuu kanri_sts ins_op_cd ins_ymd upd_op_cd upd_ymd kaitei_kakaku kosuu del_flg
1277322   C-00009 2025/03/01 0:00:00       3000000 3000000 規格B 10000 10004 10002   1 0 1       1 10000 2025/03/24 13:46:01 10004 2025/03/24 0:00:00   1  
1277322   C-00009 2025/03/01 0:00:00 2025/03/24 0:00:00     3000000 3000000 規格B 10000 10004 10002   1 0 1 55     30 10000 2025/03/24 13:46:01 10000 2025/03/24 0:00:00   1  

INSERT on [zougen_rireki_tbl]                    
zougen_id sisho_cd kobetu_cd buppin_cd zougen_hinmoku_cd shuhinmoku_cd seiri_kbn tanka kosuu ymd riyuu doc_cd kanribo_cd gobyuu_flg ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg fix_flg
415057 20 1277322 C-00009   C-00009 55 3000000 1 2025/03/24 0:00:00   573132 2488348 0 10000 2025/03/24 0:00:00     0 0

UPDATE on [kobetu_tbl] (4 changes)                                    
kobetu_cd p_kobetu_cd buppin_cd shutoku_ymd haraidasi_ymd buppin_kan_no gyousha_cd tanka zei_gaku hinsitu_kikaku kanrikan_cd kyouyoukan_cd siyousha_cd hontai_no zougen_kbn shoumou_kbn zou_kbn gen_kbn zou_riyuu gen_riyuu kanri_sts ins_op_cd ins_ymd upd_op_cd upd_ymd kaitei_kakaku kosuu del_flg
1277302   C-00009 2025/03/01 0:00:00       1500000 1500000 規格A 10000 10004 10001   1 0 1       1 10000 2025/03/24 13:46:00 10004 2025/03/24 0:00:00   2  
1277302   C-00009 2025/03/01 0:00:00 2025/03/24 0:00:00     1500000 1500000 規格A 10000 10004 10001   1 0 1 55     30 10000 2025/03/24 13:46:00 10000 2025/03/24 0:00:00   2  

INSERT on [zougen_rireki_tbl]                    
zougen_id sisho_cd kobetu_cd buppin_cd zougen_hinmoku_cd shuhinmoku_cd seiri_kbn tanka kosuu ymd riyuu doc_cd kanribo_cd gobyuu_flg ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg fix_flg
415077 20 1277302 C-00009   C-00009 55 1500000 2 2025/03/24 0:00:00   573132 2488328 0 10000 2025/03/24 0:00:00     0 0

UPDATE on [kobetu_tbl] (4 changes)                                    
kobetu_cd p_kobetu_cd buppin_cd shutoku_ymd haraidasi_ymd buppin_kan_no gyousha_cd tanka zei_gaku hinsitu_kikaku kanrikan_cd kyouyoukan_cd siyousha_cd hontai_no zougen_kbn shoumou_kbn zou_kbn gen_kbn zou_riyuu gen_riyuu kanri_sts ins_op_cd ins_ymd upd_op_cd upd_ymd kaitei_kakaku kosuu del_flg
1277342   C-00009 2025/03/01 0:00:00       3000000 3000000 規格B 10000 10004 10002   1 0 1       1 10000 2025/03/24 13:46:01 10004 2025/03/24 0:00:00   1  
1277342   C-00009 2025/03/01 0:00:00 2025/03/24 0:00:00     3000000 3000000 規格B 10000 10004 10002   1 0 1 55     30 10000 2025/03/24 13:46:01 10000 2025/03/24 0:00:00   1  

INSERT on [zougen_rireki_tbl]                    
zougen_id sisho_cd kobetu_cd buppin_cd zougen_hinmoku_cd shuhinmoku_cd seiri_kbn tanka kosuu ymd riyuu doc_cd kanribo_cd gobyuu_flg ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg fix_flg
415097 20 1277342 C-00009   C-00009 55 3000000 1 2025/03/24 0:00:00   573132 2488348 0 10000 2025/03/24 0:00:00     0 0

 


✅ 4. 新規生成(接收方資料新增)(共 12 條)

以下是針對 3 個物品所進行的新建紀錄動作:

操作 表名
INSERT kanrigae_tbl
INSERT kanrigae_meisai_tbl ×2
INSERT kobetu_tbl ×3
INSERT kobetu_rireki_tbl ×3
INSERT zougen_rireki_tbl ×3

🔸 與以往不同,這裡每一筆 kobetu_tbl 新增時都伴隨新增 zougen_rireki_tbl

INSERT on [kanrigae_tbl]                                                                                
doc_cd p_doc_cd seiri_kbn uketuke_ymd s_kanrikan_cd s_kanri_ymd s_kanri_doc_no s_kanrikan_kikan_name s_kanrikan_sisho_name s_kanrikan_name s_suitoukan_cd s_suitoukan_kikan_name s_suitoukan_sisho_name s_suitoukan_name s_kyouyoukan_cd s_kyouyoukan_kikan_name s_kyouyoukan_sisho_name s_kyouyoukan_name r_kanrikan_cd r_kanri_ymd r_kanri_doc_no r_kanrikan_kikan_name r_kanrikan_sisho_name r_kanrikan_name r_suitoukan_cd r_suitoukan_kikan_name r_suitoukan_sisho_name r_suitoukan_name r_kyouyoukan_cd r_kyouyoukan_kikan_name r_kyouyoukan_sisho_name r_kyouyoukan_name ukeharai_kbn kikan_kbn st ed konkyo hassou_moto atesaki hassou_ymd yusou_houhou touchaku_yotei hikiwatasi_basho hikiwatasi_nin touchaku_ymd shori_sts ins_op_cd ins_ymd upd_op_cd upd_ymd
573152   151 2025/03/24 0:00:00 10000 2025/03/24 0:00:00 TEST-04-交換               10004       15000 2025/03/24 0:00:00 TEST-04-交換               15012       0 0 2025/03/24 0:00:00   防衛装備庁⇒防衛装備庁航空装備研究所test 防衛装備庁 防衛装備庁航空装備研究所             12 10000 2025/03/24 14:32:53    

INSERT on [kanrigae_meisai_tbl]                        
doc_cd meisai_no buppin_cd kingaku hassou_suryo konsu housou_shu juuryou juryo_suryo tekiyou kikan_kbn st ed hatti chakuti yusou_houhou yusou_tantou bikou ins_op_cd ins_ymd upd_op_cd upd_ymd
573152 1 C-00009 3000000         2   0               10000 2025/03/24 14:32:53    

INSERT on [kanrigae_meisai_tbl]                        
doc_cd meisai_no buppin_cd kingaku hassou_suryo konsu housou_shu juuryou juryo_suryo tekiyou kikan_kbn st ed hatti chakuti yusou_houhou yusou_tantou bikou ins_op_cd ins_ymd upd_op_cd upd_ymd
573152 2 C-00009 6000000         2   0               10000 2025/03/24 14:32:53    

INSERT on [kobetu_tbl]                                    
kobetu_cd p_kobetu_cd buppin_cd shutoku_ymd haraidasi_ymd buppin_kan_no gyousha_cd tanka zei_gaku hinsitu_kikaku kanrikan_cd kyouyoukan_cd siyousha_cd hontai_no zougen_kbn shoumou_kbn zou_kbn gen_kbn zou_riyuu gen_riyuu kanri_sts ins_op_cd ins_ymd upd_op_cd upd_ymd kaitei_kakaku kosuu del_flg
1277362 1277302 C-00009 2025/03/24 0:00:00       1500000 1500000 規格A 15000 15012     1 0 3       1 10000 2025/03/24 0:00:00       2  

INSERT on [kobetu_rireki_tbl]          
doc_cd meisai_no kobetu_cd ins_op_cd ins_ymd upd_op_cd upd_ymd
573152 1 1277362 10000 2025/03/24 14:32:54    

INSERT on [zougen_rireki_tbl]                    
zougen_id sisho_cd kobetu_cd buppin_cd zougen_hinmoku_cd shuhinmoku_cd seiri_kbn tanka kosuu ymd riyuu doc_cd kanribo_cd gobyuu_flg ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg fix_flg
415117 21 1277362 C-00009   C-00009 3 1500000 2 2025/03/24 0:00:00   573152 0 0 10000 2025/03/24 0:00:00     0 0

INSERT on [kobetu_tbl]                                    
kobetu_cd p_kobetu_cd buppin_cd shutoku_ymd haraidasi_ymd buppin_kan_no gyousha_cd tanka zei_gaku hinsitu_kikaku kanrikan_cd kyouyoukan_cd siyousha_cd hontai_no zougen_kbn shoumou_kbn zou_kbn gen_kbn zou_riyuu gen_riyuu kanri_sts ins_op_cd ins_ymd upd_op_cd upd_ymd kaitei_kakaku kosuu del_flg
1277382 1277322 C-00009 2025/03/24 0:00:00       3000000 3000000 規格B 15000 15012     1 0 3       1 10000 2025/03/24 0:00:00       1  

INSERT on [kobetu_rireki_tbl]          
doc_cd meisai_no kobetu_cd ins_op_cd ins_ymd upd_op_cd upd_ymd
573152 2 1277382 10000 2025/03/24 14:32:54    

INSERT on [zougen_rireki_tbl]                    
zougen_id sisho_cd kobetu_cd buppin_cd zougen_hinmoku_cd shuhinmoku_cd seiri_kbn tanka kosuu ymd riyuu doc_cd kanribo_cd gobyuu_flg ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg fix_flg
415137 21 1277382 C-00009   C-00009 3 3000000 1 2025/03/24 0:00:00   573152 0 0 10000 2025/03/24 0:00:00     0 0

INSERT on [kobetu_tbl]                                    
kobetu_cd p_kobetu_cd buppin_cd shutoku_ymd haraidasi_ymd buppin_kan_no gyousha_cd tanka zei_gaku hinsitu_kikaku kanrikan_cd kyouyoukan_cd siyousha_cd hontai_no zougen_kbn shoumou_kbn zou_kbn gen_kbn zou_riyuu gen_riyuu kanri_sts ins_op_cd ins_ymd upd_op_cd upd_ymd kaitei_kakaku kosuu del_flg
1277402 1277342 C-00009 2025/03/24 0:00:00       3000000 3000000 規格B 15000 15012     1 0 3       1 10000 2025/03/24 0:00:00       1  

INSERT on [kobetu_rireki_tbl]          
doc_cd meisai_no kobetu_cd ins_op_cd ins_ymd upd_op_cd upd_ymd
573152 2 1277402 10000 2025/03/24 14:32:55    

INSERT on [zougen_rireki_tbl]                    
zougen_id sisho_cd kobetu_cd buppin_cd zougen_hinmoku_cd shuhinmoku_cd seiri_kbn tanka kosuu ymd riyuu doc_cd kanribo_cd gobyuu_flg ins_op_cd ins_ymd upd_op_cd upd_ymd del_flg fix_flg
415157 21 1277402 C-00009   C-00009 3 3000000 1 2025/03/24 0:00:00   573152 0 0 10000 2025/03/24 0:00:00     0 0

所有操作到此結束。


三、資料觸發紀錄關鍵回顧

從觸發紀錄中可明確看到以下流程節點:

  • kanrigae_tblshori_sts5099,表示承認完了。

  • kanrigae_meisai_tbl 兩筆記錄分別對應 2 和 2 的數量(共 4)

  • kanribo_tblkyouyoubo_tbl 插入 2 次,對應「まとめる」與「まとめない」

  • link_tbl 更新 3 次,符合一個「まとめる」+ 兩個「まとめない」

  • kobetu_tbl 更新 3 筆,反映移動出庫(設定 haraidasi_ymd

  • 對應的 zougen_rireki_tbl 插入 3 筆

  • 最後插入新 kobetu_tbl ×3 + kobetu_rireki_tbl ×3 + zougen_rireki_tbl ×3(共 9 條)


四、總結

類型 操作數 備註
UPDATE 9 kanrigae, meisai, link, kobetu
INSERT 19 kanribo, kyouyoubo, kobetu, rireki, zougen
合計 28 與原始推論完全一致 ✅

 

→返回目錄

This article was last edited at