1  --获取生成托标签
 2   FUNCTION get_tackle_num(p_frist      IN VARCHAR2,
 3                           p_num        IN NUMBER,
 4                           p_year_and_m IN VARCHAR2) RETURN VARCHAR2 IS
 5     lv_code       VARCHAR2(20);
 6     lv_code2      VARCHAR2(20);
 7     lv_codelist   VARCHAR2(20000);
 8     lv_maxnum     VARCHAR2(20);
 9     lv_num        number;
10     lv_workshop   VARCHAR2(20);
11     lv_org        VARCHAR2(20);
12     --ln_message_id number := ljmes.system_api_pkg.success;
13     lr_rows       longimes.LJ_MES_QP_TACKLE%ROWTYPE;
14   BEGIN
15     --获取车间，组织Id
16     lv_workshop := longimes.lj_mes_qp_public_pck.get_workshop_one;
17     lv_org      := get_organization_id;
18     --获取托板编号
19     lv_code := p_frist || lv_workshop || p_year_and_m;
20     --获取流水号
21     select Max(substr(t.tackle_num, -4))
22       into lv_maxnum
23       from longimes.lj_mes_qp_tackle t
24      where substr(t.tackle_num, 0, 6) = lv_code;
25   
26     --获取流水号
27     IF lv_maxnum is null THEN
28       lv_num := 0;
29     ELSE
30       lv_num := to_number(lv_maxnum);
31     END IF;
32     --是否超出范围
33     IF lv_num + p_num > 9999 THEN
34       RETURN 'E@错误@流水号超出最大范围';
35     END IF;
36     --公共部分
37     lr_rows.creation_date     := SYSDATE;
38     lr_rows.created_by        := ljmes.system_api_pkg.get_user_id;
39     lr_rows.last_update_date  := SYSDATE;
40     lr_rows.last_updated_by   := ljmes.system_api_pkg.get_user_id;
41     lr_rows.last_update_login := ljmes.system_api_pkg.get_client_session_id;
42   
43     --生成标签号并存到表里
44     FOR i IN 1 .. p_num LOOP
45       lv_code2    := lv_code || lpad(to_char(lv_num + i), 4, 0);
46       lv_codelist := lv_codelist || lv_code2 || ',';
47     
48       --保存
49       lr_rows.k_id            := longimes.lj_mes_qp_tackle_s.nextval;
50       lr_rows.organization_id := lv_org;
51       lr_rows.tackle_num      := lv_code2;
52       lr_rows.status          := 'VALID';
53       lr_rows.tackle_name     := '托';
54       INSERT INTO longimes.LJ_MES_QP_TACKLE VALUES lr_rows;
55     END LOOP;
56     RETURN lv_codelist;
57   END;