1 PACKAGE BODY GMF_ALLOC_PROCESS_PRIVATE AS
2 /* $Header: gmfalcpb.pls 115.5 2002/11/11 00:28:41 rseshadr ship $ */
3
4 /*********************************************************************************
5 * FUNCTION
6 * get_company_acct_masks
7 *
8 * DESCRIPTION
9 * Retrieves the masks of accounting units and accounts.
10 * INPUT PARAMETERS
11 * v_co_code
12 *
13 * OUTPUT PARAMETERS
14 * P_deli_acct_mask
15 * P_deli_au_mask :=
16 * P_deli_comp :=
17 * P_deli_au_len :
18 * P_deli_acct_len
19 * P_deli_key_mask
20 *
21 * RETURNS
22 * 0 = Sucessful
23 * -1 = No Segments found
24 * -2 = No delimeter found
25 * -3 = No Account unit segments found.
26 * HISTORY
27 * sukarna Reddy 12/15/97 Converted from jpl to PLSQL
28 * Manish Gupta 02-MAR-99 Bug 841019
29 * Commented Package DBMS_OUTPUT.
30 * Sukarna Reddy 21-JAN-01 Changed variable declarations to be
31 * of database column type.
32 * R.Sharath Kumar 30-Oct-2002 Bug# 2641405
33 * Added NOCOPY hint
34 ************************************************************************************/
35
36
37 FUNCTION get_company_acct_masks(V_co_code IN VARCHAR2) RETURN NUMBER IS
38 x_au_deli_nm VARCHAR2(200) DEFAULT NULL;
39 x_acct_deli_nm VARCHAR2(300) DEFAULT NULL;
40 x_acct_deli VARCHAR2(2) DEFAULT NULL;
41 x_au_deli VARCHAR2(2) DEFAULT NULL;
42 x_gl_deli VARCHAR2(4) DEFAULT NULL;
43
44 CURSOR Cur_seg_delimeter IS
45 SELECT segment_delimiter
46 FROM gl_plcy_mst
47 WHERE co_code = v_co_code;
48
49 CURSOR Cur_segtypecnt(v_type IN NUMBER) IS
50 SELECT COUNT(1)
51 FROM gl_plcy_seg
52 WHERE co_code = v_co_code
53 AND type = v_type;
54
55 CURSOR Cur_segment_type(v_type IN NUMBER) IS
56 SELECT segment_no,
57 length,
58 short_name
59 FROM gl_plcy_seg
60 WHERE co_code = v_co_code
61 AND type = v_type
62 ORDER BY 1 ASC;
63
64 CURSOR Cur_segment_cnt IS
65 SELECT COUNT(1)
66 FROM gl_plcy_seg
67 WHERE co_code = v_co_code;
68
69 x_a NUMBER(8) DEFAULT 0; /* stores accts start position*/
70 x_deli_au_len NUMBER(10) DEFAULT 0; /* sotres AU length*/
71 x_deli_acct_len NUMBER(10) DEFAULT 0; /* stores accts length*/
72 x_deli_comp VARCHAR2(4) DEFAULT NULL; /* stores company delimeter*/
73 x_deli_key_mask VARCHAR2(500) DEFAULT NULL; /* stores complete mask for key*/
74 x_Count NUMBER(10) DEFAULT 0;
75 x_deli_au_mask gl_accu_mst.acctg_unit_no%TYPE DEFAULT NULL; /* stores mask for AU*/
76 x_deli_acct_mask gl_acct_mst.acct_no%TYPE DEFAULT NULL; /* stores mask for accts*/
77 x_type0_cnt NUMBER(10) DEFAULT 0; /* stores no of segments for AU*/
78 x_type1_cnt NUMBER(10) DEFAULT 0; /* stores no of segments for accts*/
79 x_seg_delimeter VARCHAR2(2); /* stores the segment delimeter.*/
80
81 BEGIN
82
83 OPEN Cur_segment_cnt;
84 FETCH Cur_segment_cnt INTO X_count;
85 CLOSE Cur_segment_cnt;
86
87 IF (X_count = 0) THEN
88 RETURN(-1);
89 END IF;
90
91 OPEN Cur_seg_delimeter;
92 FETCH Cur_seg_delimeter INTO X_seg_delimeter;
93 IF (Cur_seg_delimeter%NOTFOUND) THEN
94 -- DBMS_OUTPUT.PUT_LINE('Delimeter not found');
95 RETURN(-2); /* Delimiter not found*/
96 END IF;
97
98 IF Cur_seg_delimeter%ISOPEN THEN
99 CLOSE Cur_seg_delimeter;
100 END IF;
101 X_gl_deli := X_seg_delimeter;
102
103 IF (X_gl_deli IS NULL) THEN
104 X_gl_deli := '^';
105 END IF;
106 x_deli_au_len := 0;
107 x_deli_acct_len := 0;
108 x_deli_comp := NULL;
109 x_deli_key_mask := NULL;
110 x_deli_au_mask := NULL;
111 x_deli_acct_mask := NULL;
112
113 /* get the segments for the accounting units making sure they are in seg Order*/
114
115 OPEN cur_segtypecnt(0);
116 FETCH Cur_segtypecnt INTO X_count;
117 CLOSE Cur_segtypecnt;
118 x_type0_cnt := x_count;
119
120 IF (x_count = 0) THEN
121 RETURN(-3);
122 END IF;
123
124 FOR Cur_tmp_segtyp0 IN Cur_Segment_type(0) LOOP
125 x_deli_au_len := x_deli_au_len + Cur_tmp_segtyp0.length + 1;
126 x_type1_cnt := Cur_Segment_type%ROWCOUNT;
127 END LOOP;
128 IF (x_deli_au_len > 0) THEN
129 x_deli_au_len := x_deli_au_len - 1;
130 END IF;
131
132 FOR Cur_tmp_segtyp1 IN Cur_Segment_type(1) LOOP
133 x_deli_acct_len := x_deli_acct_len + Cur_tmp_segtyp1.length + 1;
134 x_type1_cnt := Cur_Segment_type%ROWCOUNT;
135 END LOOP;
136 IF (x_deli_acct_len > 0) THEN
137 x_deli_acct_len := X_deli_acct_len - 1;
138 END IF;
139
140 X_deli_comp := V_co_code;
141
142 /* get the segments for the accounts making sure they are in seg order*/
143 OPEN cur_segtypecnt(1);
144 FETCH Cur_segtypecnt INTO x_count;
145 CLOSE Cur_segtypecnt;
146 x_type1_cnt := x_count;
147 IF (x_count = 0) THEN
148 RETURN(-1);
149 END IF;
150 FOR Cur_tmpseg_typ0 IN Cur_segment_type(0) LOOP
151 FOR i IN 1..Cur_tmpseg_typ0.length LOOP
152 x_deli_key_mask := x_deli_key_mask||' ';
153 END LOOP;
154 IF(x_type0_cnt > 1) AND
155 (x_type0_cnt <> Cur_segment_type%ROWCOUNT)THEN
156 x_deli_key_mask := x_deli_key_mask||x_gl_deli;
157 END IF;
158 END LOOP;
159
160 x_deli_key_mask := x_deli_key_mask||'^';
161 FOR Cur_tmpseg_typ1 IN Cur_segment_type(1) LOOP
162 FOR i IN 1..Cur_tmpseg_typ1.length LOOP
163 x_deli_key_mask := x_deli_key_mask||' ';
164 END LOOP;
165 IF (x_type1_cnt > 1) AND
166 (x_type1_cnt <> Cur_segment_type%ROWCOUNT) THEN
167 x_deli_key_mask := x_deli_key_mask||x_gl_deli;
168 END IF;
169 END LOOP;
170 x_a := x_deli_au_len + 2;
171
172 -- B1043070 Changed substrb to substr, We need mask character and not byte
173 -- P_deli_acct_mask := substrb(x_deli_key_mask, x_a, x_deli_acct_len);
174 P_deli_acct_mask := substr(x_deli_key_mask, x_a, x_deli_acct_len);
175 -- P_deli_au_mask := substrb(x_deli_key_mask, 1, x_deli_au_len);
176 P_deli_au_mask := substr(x_deli_key_mask, 1, x_deli_au_len);
177 P_deli_comp := x_deli_comp;
178 P_deli_au_len := TO_CHAR(x_deli_au_len);
179 P_deli_acct_len := TO_CHAR(x_deli_acct_len);
180 P_deli_key_mask := x_deli_key_mask;
181 RETURN(0);
182 END get_company_acct_masks;
183
184
185 /*********************************************************************************
186 * FUNCTION
187 * format_string
188 *
189 * DESCRIPTION
190 * Copy string and/or mask from string to output.
191 * The result is returned in x_output.
192 *
193 * Mask characters include
194 * ! = change to upper case
195 * # = number only, leave blank if not number
196 * ^ = leave space
197 *
198 * Any other character in the mask is assumed to be a literal
199 * and will be used as a separator in the output string.
200 *
201 * Example:
202 * format_string '...xyz01234567890" . . ^ .'
203 *
204 * Would produce the result: "xyz.123.456 67.890"
205 *
206 * INPUT PARAMETERS
207 * v_string
208 * v_mask
209 *
210 * OUTPUT PARAMETERS
211 * <None>
212 *
213 * RETURNS
214 * Formatted string.
215 *
216 *
217 ***********************************************************************************/
218
219 FUNCTION format_string (V_string IN OUT NOCOPY VARCHAR2, V_mask IN VARCHAR2) RETURN VARCHAR2 IS
220 X_rvar VARCHAR2(9);
221 i NUMBER(10);
222 j NUMBER(10);
223 k NUMBER(10);
224 X_slen NUMBER(9);
225 X_mlen NUMBER(9);
226 X_punct VARCHAR2(250) DEFAULT NULL;
227 X_output VARCHAR2(250) DEFAULT NULL;
228 X_temp VARCHAR2(250) DEFAULT NULL;
229 X_pos NUMBER(10) DEFAULT 0;
230 BEGIN
231 /* i index into output*/
232 /* j index into string*/
233 /* k index into mask*/
234 -- B1043070 No change for "length"
235 X_slen := LENGTH(V_string);
236 X_mlen := LENGTH(V_mask);
237 -- B1043070: Changed instrb to instr, we need character position
238 -- X_pos :=instrb(V_mask, '^', 1);
239 X_pos :=instr(V_mask, '^', 1);
240 /* Step 1, get all the characters in the mask to remove from the input string*/
241
242 i := 1;
243 WHILE (i <= X_mlen) LOOP
244 -- B1043070: Changed all substrb to substr, we need character comparision
245 IF ((substr(V_mask, i, 1) <> ' ') AND
246 (substr(V_mask, i, 1) <> '^') AND
247 (substr(V_mask, i, 1) <> '#') AND
248 (substr(V_mask, i, 1) <> '!')) THEN
249 X_punct := X_punct || substr(V_mask, i, 1);
250 END IF;
251 /* Step 2 remove the punctuation character(s) from the input string*/
252 i := i + 1;
253 END LOOP;
254 j := 1;
255 i := 1;
256 WHILE (i <= X_slen) LOOP
257 -- B1043070: Changed instrb to instr, we need character position
258 -- B1043070: Changed substrb to substr, we need character
259 -- X_rvar := instrb(X_punct, substrb(V_string, i, 1), 1);
260 X_rvar := instr(X_punct, substr(V_string, i, 1), 1);
261 IF (X_rvar = 0) THEN
262 X_temp := X_temp || substr(V_string, i, 1);
263 END IF;
264 i := i + 1;
265 END LOOP;
266 IF X_temp IS NOT NULL THEN
267 V_string := X_temp;
268 END IF;
269 j := 1;
270 k := 1;
271 i := 1;
272 WHILE (i <= 255) LOOP
273 IF (k <= X_mlen) THEN
274 IF (substr(V_mask, k, 1) = ' ') THEN
275 IF (j <= X_slen) THEN
276 X_output := X_output||substr(V_string, j, 1);
277 j := j + 1;
278 ELSE
279 X_output:= X_output||' ';
280 END IF;
281 ELSIF (substr(V_mask, k, 1) = '!') THEN
282 IF (j <= X_slen) THEN
283 X_output:= X_output||UPPER(substr(V_string, j, 1));
284 j := j + 1;
285 END IF;
286 ELSIF (substr(V_mask, k, 1) = '#') THEN
287 IF (j <= X_slen) THEN
288 IF ((substr(V_string, j, 1) >= '0' AND substr(V_string, j, 1) <= '9')) THEN
289 X_output:= X_output||substr(V_string, j, 1);
290 ELSE
291 X_output:= X_output||' ';
292 END IF;
293 j := j + 1;
294 END IF;
295 ELSIF (substr(V_mask, k, 1) = '^') THEN
296 IF (j <= X_slen) THEN
297 IF (substr(V_string, J, 1) = ' ') THEN
298 j := j + 1;
299 ELSE
300 X_output:= X_output||' ';
301
302 END IF;
303 END IF;
304 ELSE
305 IF (k <> X_pos - 1) THEN
306 X_output:= X_output||substr(V_mask, k, 1);
307 END IF;
308 END IF;
309 ELSIF (j <= X_slen) THEN
310 X_output:= X_output||substr(V_string, j, 1);
311 j := j + 1;
312 ELSE
313 EXIT;
314 END IF;
315 k := k + 1;
316 i := i + 1;
317 END LOOP;
318 RETURN(X_output);
319 END format_string;
320 END GMF_ALLOC_PROCESS_PRIVATE;
[Home] [Help]