1 package body gmf_get_opm_code as
2 /* $Header: gmfopmcb.pls 115.3 2004/09/10 06:37:55 anthiyag ship $ */
3
4 -----------------------------------------------------------------
5 -- The pl/sql table to contain all the session generated codes
6 -----------------------------------------------------------------
7 type session_code_tab_type is table of op_ship_mst.shipper_code%TYPE
8 index by binary_integer;
9
10 g_session_code_tab session_code_tab_type;
11
12 -------------------------------------------
13 -- Declare all the cursors here
14 -------------------------------------------
15 cursor c_fobc(p_original_code varchar2) is
16 select count(*)
17 from op_fobc_mst
18 where fob_code = p_original_code;
19
20 cursor c_term(p_original_code varchar2) is
21 select count(*)
22 from op_term_mst
23 where terms_code = p_original_code;
24
25 cursor c_frgt(p_original_code varchar2) is
26 select count(*)
27 from op_frgt_mth
28 where frtbill_mthd = p_original_code;
29
30 cursor c_ship(p_original_code varchar2) is
31 select count(*)
32 from op_ship_mst
33 where shipper_code = p_original_code;
34
35 --Bug # 3464154 ANTHIYAG Anand Thiyagarajan 05-05-2004 GL Exchange Rate Type Enhancement Start
36
37 Cursor c_xrtc (p_original_code varchar2) is
38 select count (*)
39 from gl_rate_typ
40 where rate_type_code = p_original_code;
41
42 --Bug # 3464154 ANTHIYAG Anand Thiyagarajan 05-05-2004 GL Exchange Rate Type Enhancement End
43
44 ----------------------------------------------
45 -- Declare all the variables here
46 ----------------------------------------------
47 l_counter NUMBER:=0;
48
49 --------------------------------------------
50 -- Forward declaration of functions
51 --------------------------------------------
52 function code_exists (p_original_code varchar2,
53 p_table_code varchar2) return boolean;
54 procedure insert_code (p_original_code varchar2);
55
56
57 function generate_code (p_original_code varchar2,
58 p_table_code varchar2)
59 return varchar2 is
60 l_count_code number;
61 l_length number;
62 l_original_code varchar2(4);
63 begin
64 if (p_table_code='FOBC') then
65 open c_fobc(p_original_code);
66 fetch c_fobc into l_count_code;
67 close c_fobc;
68 elsif (p_table_code='TERM') then
69 open c_term(p_original_code);
70 fetch c_term into l_count_code;
71 close c_term;
72 elsif (p_table_code='FRGT') then
73 open c_frgt(p_original_code);
74 fetch c_frgt into l_count_code;
75 close c_frgt;
76 elsif (p_table_code='SHIP') then
77 open c_ship(p_original_code);
78 fetch c_ship into l_count_code;
79 close c_ship;
80
81 --Bug # 3464154 ANTHIYAG Anand Thiyagarajan 05-05-2004 GL Exchange Rate Type Enhancement Start
82
83 elsif (p_table_code='XRTC') then
84 open c_xrtc (p_original_code);
85 fetch c_xrtc into l_count_code;
86 close c_xrtc;
87
88 --Bug # 3464154 ANTHIYAG Anand Thiyagarajan 05-05-2004 GL Exchange Rate Type Enhancement End
89
90 else
91 -- Raise an invalid table type passed
92 -- Return error
93 return('-1');
94 end if;
95
96 if ((l_count_code = 0) and (code_exists(p_original_code,p_table_code))) then
97 l_counter:=0;
98 return (p_original_code);
99 end if;
100
101 l_counter:=l_counter+1;
102
103 if (l_counter > 999) then
104 -- Raise an error, no unique code generated
105 --return;
106 return('-2');
107 end if;
108 --------------------------
109 -- Length of counter
110 --------------------------
111 l_length:=length(l_counter);
112
113 -------------------------
114 -- Make a new code, rtrim is used so that the string does not go beyond 4 byetes
115 --------------------------
116 l_original_code:=rtrim(substrb(p_original_code,1,4-l_length))||to_char(l_counter);
117 ------------------------------------
118 -- Give a recursive call to function
119 ------------------------------------
120 return generate_code(l_original_code,p_table_code);
121 end generate_code;
122
123
124 --------------------------------------------------------------------
125 -- Function to find if the code exists in pl/sql table
126 --------------------------------------------------------------------
127 function code_exists (p_original_code varchar2,
128 p_table_code varchar2)
129 return boolean is
130 l_table_index number;
131 begin
132 if ( p_table_code in ('FOBC','TERMS', 'XRTC')) then
133 return true;
134 else
135 if g_session_code_tab.last is not null then
136 l_table_index:=g_session_code_tab.first;
137 while l_table_index is not null
138 loop
139 if (g_session_code_tab(l_table_index)=p_original_code) then
140 return false;
141 end if;
142 l_table_index:=g_session_code_tab.next(l_table_index);
143 end loop;
144 insert_code(p_original_code);
145 return true;
146 else
147 insert_code(p_original_code);
148 return true;
149 end if;
150 end if;
151 end code_exists;
152
153 ----------------------------------------------------------
154 -- Procedure to store generated code in the same session
155 ----------------------------------------------------------
156 procedure insert_code(p_original_code varchar2) is
157 l_table_index binary_integer;
158 begin
159 if g_session_code_tab.last is null then
160 l_table_index:=1;
161 else
162 l_table_index:=g_session_code_tab.last+1;
163 end if;
164 g_session_code_tab(l_table_index) :=p_original_code;
165 end insert_code;
166
167
168 ----------------------------------------------------------
169 -- delete plsql table, called from the form at the time
170 -- of commit or exit form
171 ----------------------------------------------------------
172 function delete_session_codes_tab
173 return number is
174 begin
175 g_session_code_tab.delete;
176 return 1;
177 exception
178 when no_data_found then
179 return 1;
180 when others then
181 return -1;
182 end delete_session_codes_tab;
183
184
185 ----------------------------------------------------------
186 -- delete a row in plsql table, called from the form at
187 -- clear_record
188 ----------------------------------------------------------
189 function delrow_session_tab(p_original_code varchar2)
190 return number is
191 l_table_index binary_integer;
192 begin
193 l_table_index:=g_session_code_tab.first;
194 while l_table_index <= g_session_code_tab.LAST
195 loop
196 if (g_session_code_tab(l_table_index)=p_original_code) then
197 g_session_code_tab.delete(l_table_index);
198 return 1;
199 end if;
200 l_table_index:=g_session_code_tab.next(l_table_index);
201 end loop;
202 return -1;
203 end delrow_session_tab;
204 end gmf_get_opm_code;
[Home] [Help]