CREATE OR REPLACE FUNCTION ebsm func_payment_documen t_id_generator p_

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
CREATE OR REPLACE FUNCTION ebsm.func_payment_document_id_generator(p_account_services_id character varying, p_year integer, p_month integer)
RETURNS TABLE(code character varying, payment_document_id character varying)
LANGUAGE plpgsql
AS $function$
DECLARE
l_empty_id integer;
l_max_id integer;
l_account_services_id varchar(36);
begin
/*begin
select t.account_services_id
into l_account_services_id
from ebsm.eb_payment_documents_id t
where t.account_services_id = p_account_services_id
and t.year = p_year
and t.month = p_month
for update;
exception
when no_data_found then
l_account_services_id := null;
end;*/
--if l_account_services_id is null then
begin
insert into ebsm.eb_payment_documents_id
(account_services_id, year, month)
values
(p_account_services_id, p_year, p_month);
exception
when unique_violation then
select t.account_services_id
into l_account_services_id
from ebsm.eb_payment_documents_id t
where t.account_services_id = p_account_services_id
and t.year = p_year
and t.month = p_month
for update;
end;
--end if;
select min(case
when id != rn then
rn
else
null
end) empty_id,
coalesce(max(rn), 0) max_rn
into l_empty_id, l_max_id
from (select id, row_number() over(order by id) rn
from (select right(d.payment_document_id, 1) ::integer id
from ebsm.eb_payment_documents d
where d.account_services_id = p_account_services_id
and d.year = p_year
and d.month = p_month
union
select l.id
from ebsm.eb_payment_documents_id_list l
where l.account_services_id = p_account_services_id
and l.year = p_year
and l.month = p_month) tt) qq;
if l_empty_id is not null then
return query
select 'OK' ::varchar code,
(p_account_services_id || '-' || right(p_year::varchar, 1) ::varchar || lpad(p_month ::varchar,2,'0') || l_empty_id ::varchar)::varchar payment_document_id;
insert into ebsm.eb_payment_documents_id_list
(account_services_id, year, month, id, create_date)
values
(p_account_services_id, p_year, p_month, l_empty_id,now());
elsif l_max_id < 9 then
return QUERY
select 'OK' ::varchar code,
(p_account_services_id || '-' || right(p_year::varchar, 1) ::varchar || lpad(p_month ::varchar,2,'0') || (l_max_id + 1) ::varchar)::varchar payment_document_id;
insert into ebsm.eb_payment_documents_id_list
(account_services_id, year, month, id, create_date)
values
(p_account_services_id, p_year, p_month, l_max_id + 1,now());
else
return QUERY
select 'MAX_NUMBER_EXCEED' ::varchar code,
null ::varchar payment_document_id;
end if;
END; $function$
;