[自製]TIPTOP 自動編碼

DSC的自動編碼，不符合我要的
於是參考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