於是參考DSC的，寫了一個，比較彈性一點的自動編碼
這完整程度大約50%，尚未考慮同時搶號的問題
暫時先這樣，以後有時間在把它調整
################################################################################
# 取得自動編碼
# @author : JeffreyWu
# @date : 2012-06-11
# @param : ps_slip [STRING] 單號
# @param : ps_date [DATE] 日期
# @param : ps_method [STRING] 編碼方式 [1:依年月、2:依年週、3:依年月日]
# @param : ps_tab [STRING] 單據編號是否重複要檢查的table名稱
# @param : ps_fld [STRING] 單據編號對應要檢查的key欄位名稱
# @param : ps_length [STRING] 流水碼長度
# @return : li_result [BOOLEAN] 結果(TRUE/FALSE)
# @return : ls_no [STRING] 單據編號
# @description :
################################################################################
FUNCTION sl_GetAutoNumber(ps_slip,ps_date,ps_method,ps_tab,ps_fld,ps_length)
DEFINE ps_slip STRING
DEFINE ps_date LIKE type_file.dat
DEFINE ps_method STRING
DEFINE ps_tab STRING
DEFINE ps_fld STRING
DEFINE ps_length STRING
DEFINE lc_max_no LIKE type_file.chr50
DEFINE li_year LIKE azn_file.azn02 # 年度
DEFINE li_month LIKE azn_file.azn04 # 期別
DEFINE li_week LIKE azn_file.azn05 # 週別
DEFINE ls_date STRING
DEFINE lc_buf STRING
DEFINE lc_msg LIKE type_file.chr1000
DEFINE ls_format STRING
DEFINE li_i LIKE type_file.num10
DEFINE ls_no STRING
DEFINE l_sql STRING
DEFINE ls_sql STRING
#抓取執行日的年、月、週別
#透過ORACLE執行
LET ls_sql = "SELECT to_char(to_date('",ps_date,"','yy/MM/dd'),'yyyy'), ",
" to_char(to_date('",ps_date,"','yy/MM/dd'),'MM'), ",
" to_char(to_date('",ps_date,"','yy/MM/dd'),'ww') ",
" FROM dual"
PREPARE get_date_pre FROM ls_sql
EXECUTE get_date_pre INTO li_year,li_month,li_week
IF STATUS THEN
IF g_bgerr THEN
CALL s_errmsg('','','',"sub-142",0)
ELSE
CALL cl_err(ps_slip,"sub-142",0)
END IF
RETURN FALSE,ps_slip
END IF
#依照自動編號方式取得最大號LIKE根據
CASE
WHEN (ps_method = "1") #依年月
LET ls_date = li_year USING "&&&&",li_month USING "&&"
LET ls_date = ls_date.subString(3,6)
LET lc_buf = ps_slip CLIPPED,"-",ls_date
WHEN (ps_method = "2") #依年週
LET ls_date = li_year USING "&&&&",li_week USING "&&"
LET ls_date = ls_date.subString(3,6)
LET lc_buf = ps_slip CLIPPED,"-",ls_date
WHEN (ps_method = "3") #依年月日
LET ls_date = li_year USING "&&&&",li_month USING "&&",
DAY(ps_date) USING "&&"
LET ls_date = ls_date.subString(3,8)
LET lc_buf = ps_slip CLIPPED,"-",ls_date
END CASE
LET ls_sql = "SELECT MAX(substr(",ps_fld,",",lc_buf.getLength()+1,"))+1",
" FROM ",cl_get_target_table(g_plant,ps_tab),
" WHERE ",ps_fld," LIKE '",lc_buf CLIPPED,"%'"
LET ls_sql = cl_replace_sqldb(ls_sql)
PREPARE auto_no_pre FROM ls_sql
EXECUTE auto_no_pre INTO lc_max_no
IF STATUS THEN
IF g_bgerr THEN
CALL s_errmsg('','','',"sub-142",0)
ELSE
CALL cl_err(ps_slip,"sub-142",0)
END IF
RETURN FALSE,ps_slip
END IF
IF cl_null(lc_max_no) THEN
LET lc_max_no = "1"
END IF
LET ls_format = ""
FOR li_i = 1 TO ps_length
LET ls_format = ls_format,"&"
END FOR
LET ls_no = lc_buf,lc_max_no USING ls_format
RETURN TRUE,ls_no
END FUNCTION
