[Home] [Help]
PACKAGE BODY: APPS.POR_UTIL_PKG2_TEST
Source
1 PACKAGE BODY por_util_pkg2_test AS
2 /* $Header: PORUTL2B.pls 115.2 99/07/17 03:33:03 porting sh $ */
3
4 -- Private global variables
5 g_quote_char CONSTANT VARCHAR2(1) := '\';
6 g_field_delimiter CONSTANT VARCHAR2(1) := ';';
7 g_date_format_mask CONSTANT VARCHAR2(50) := 'DD-MON-YY HH24:MI:SS';
8
9 g_approval_list_string VARCHAR2(32767) := NULL;
10
11 -- Private routine prototypes
12 PROCEDURE MarshalField(p_string IN VARCHAR2,
13 p_quote_char IN VARCHAR2,
14 p_delimiter IN VARCHAR2);
15
16 FUNCTION GetNextToken(p_start_pos IN OUT NUMBER,
17 p_quote_char IN VARCHAR2,
18 p_delimiter IN VARCHAR2,
19 p_remove_quotes IN BOOLEAN,
20 p_result OUT VARCHAR2) RETURN BOOLEAN;
21
22
23 -- Public routines
24 PROCEDURE get_approval_list(p_document_id IN NUMBER,
25 p_first_approver_id IN NUMBER DEFAULT NULL,
26 p_default_flag IN NUMBER DEFAULT NULL,
27 p_rebuild_flag IN NUMBER DEFAULT NULL,
28 p_approval_list_header_id OUT NUMBER,
29 p_last_update_date OUT VARCHAR2,
30 p_approval_list_string OUT VARCHAR2,
31 p_approval_list_count OUT NUMBER,
32 p_quote_char OUT VARCHAR2,
33 p_field_delimiter OUT VARCHAR2,
34 p_return_code OUT NUMBER,
35 p_error_stack_string OUT VARCHAR2) IS
36 l_approval_list po_approvallist_s1.ApprovalListType;
37 l_error_stack po_approvallist_s1.ErrorStackType;
38 l_return_code NUMBER;
39 l_last_update_date DATE;
40 l_approval_list_header_id NUMBER;
41 l_index NUMBER;
42 BEGIN
43
44 p_approval_list_string := NULL;
45
46 IF (p_default_flag = 1) THEN
47 po_approvallist_s1.get_default_approval_list(
48 p_first_approver_id=>p_first_approver_id,
49 p_approval_path_id=>NULL,
50 p_document_id=>p_document_id,
51 p_document_type=>g_document_type,
52 p_document_subtype=>g_document_subtype,
53 p_rebuild_code=>'INITIAL_BUILD',
54 p_return_code=>l_return_code,
55 p_error_stack=>l_error_stack,
56 p_approval_list=>l_approval_list);
57 ELSE
58 IF (p_rebuild_flag = 1) THEN
59 po_approvallist_s1.rebuild_approval_list(
60 p_document_id=>p_document_id,
61 p_document_type=>g_document_type,
62 p_document_subtype=>g_document_subtype,
63 p_rebuild_code=>'DOCUMENT_CHANGED',
64 p_return_code=>l_return_code,
65 p_error_stack=>l_error_stack,
66 p_approval_list_header_id=>l_approval_list_header_id);
67
68 IF (l_return_code <> po_approvallist_s1.E_SUCCESS) THEN
69 GOTO HANDLE_ERROR; -- bad style huh?
70 END IF;
71 END IF;
72 po_approvallist_s1.get_latest_approval_list(
73 p_document_id=>p_document_id,
74 p_document_type=>g_document_type,
75 p_document_subtype=>g_document_subtype,
76 p_return_code=>l_return_code,
77 p_error_stack=>l_error_stack,
78 p_approval_list_header_id=>l_approval_list_header_id,
79 p_last_update_date=>l_last_update_date,
80 p_approval_list=>l_approval_list);
81
82 p_last_update_date := to_char(l_last_update_date, g_date_format_mask);
83 p_approval_list_header_id := l_approval_list_header_id;
84 END IF;
85
86 IF (l_return_code = po_approvallist_s1.E_SUCCESS) THEN
87
88 g_approval_list_string := NULL;
89
90 IF (l_approval_list.COUNT > 0) THEN
91 l_index := l_approval_list.FIRST;
92 WHILE (l_index IS NOT NULL) LOOP
93 MarshalField(to_char(l_approval_list(l_index).id), g_quote_char, g_field_delimiter);
94 MarshalField(to_char(l_approval_list(l_index).approver_id), g_quote_char, g_field_delimiter);
95 MarshalField(l_approval_list(l_index).approver_disp_name, g_quote_char, g_field_delimiter);
96 MarshalField(to_char(l_approval_list(l_index).responder_id), g_quote_char, g_field_delimiter);
97 MarshalField(l_approval_list(l_index).responder_disp_name, g_quote_char, g_field_delimiter);
98 MarshalField(to_char(l_approval_list(l_index).forward_to_id), g_quote_char, g_field_delimiter);
99 MarshalField(l_approval_list(l_index).forward_to_disp_name, g_quote_char, g_field_delimiter);
100 MarshalField(l_approval_list(l_index).status, g_quote_char, g_field_delimiter);
101 MarshalField(l_approval_list(l_index).approver_type, g_quote_char, g_field_delimiter);
102 MarshalField(l_approval_list(l_index).mandatory_flag, g_quote_char, g_field_delimiter);
103 MarshalField(to_char(l_approval_list(l_index).sequence_num), g_quote_char, g_field_delimiter);
104 l_index := l_approval_list.NEXT(l_index);
105 END LOOP;
106 END IF;
107
108 p_approval_list_count := l_approval_list.COUNT;
109 p_approval_list_string := g_approval_list_string;
110 p_quote_char := g_quote_char;
111 p_field_delimiter := g_field_delimiter;
112 p_return_code := 0;
113 RETURN;
114 END IF;
115
116 <<HANDLE_ERROR>>
117
118 IF (l_error_stack.COUNT > 0) THEN
119
120 NULL; -- Handle the error stack;
121
122 END IF;
123
124 p_return_code := -1;
125
126 END get_approval_list;
127
128 PROCEDURE save_approval_list(p_document_id IN NUMBER,
129 p_approval_list_string IN VARCHAR2,
130 p_approval_list_header_id IN OUT NUMBER,
131 p_first_approver_id IN NUMBER,
132 p_last_update_date IN VARCHAR2,
133 p_quote_char IN VARCHAR2,
134 p_field_delimiter IN VARCHAR2,
135 p_return_code OUT NUMBER,
136 p_error_stack_string OUT VARCHAR2) IS
137 l_approval_list po_approvallist_s1.ApprovalListType;
138 l_approval_list_elt po_approvallist_s1.ApprovalListEltType;
139 l_error_stack po_approvallist_s1.ErrorStackType;
140 l_index NUMBER;
141 l_pos NUMBER;
142 l_string VARCHAR2(32767);
143 l_last_update_date DATE;
144 l_return_code NUMBER;
145 BEGIN
146
147 -- Sanity check:
148 IF (p_approval_list_header_id IS NULL) THEN
149 l_last_update_date := NULL;
150 ELSE
151 IF (p_last_update_date IS NULL) THEN
152 RETURN;
153 ELSE
154 l_last_update_date := to_date(p_last_update_date, g_date_format_mask);
155 END IF;
156 END IF;
157
158 g_approval_list_string := p_approval_list_string;
159 l_index := 0;
160 l_pos := 1;
161
162 LOOP
163 l_approval_list_elt := NULL;
164
165 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
166 EXIT;
167 END IF;
168 l_approval_list_elt.id := to_number(l_string);
169
170 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
171 -- handle datacorruption: set error code (?)
172 RETURN;
173 END IF;
174 l_approval_list_elt.approver_id := to_number(l_string);
175
176 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
177 -- handle datacorruption: set error code (?)
178 RETURN;
179 END IF;
180 l_approval_list_elt.approver_disp_name := l_string;
181
182 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
183 -- handle datacorruption: set error code (?)
184 RETURN;
185 END IF;
186 l_approval_list_elt.responder_id := to_number(l_string);
187
188 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
189 -- handle datacorruption: set error code (?)
190 RETURN;
191 END IF;
192 l_approval_list_elt.responder_disp_name := l_string;
193
194 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
195 -- handle datacorruption: set error code (?)
196 RETURN;
197 END IF;
198 l_approval_list_elt.forward_to_id := to_number(l_string);
199
200 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
201 -- handle datacorruption: set error code (?)
202 RETURN;
203 END IF;
204 l_approval_list_elt.forward_to_disp_name := l_string;
205
206 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
207 -- handle datacorruption: set error code (?)
208 RETURN;
209 END IF;
210 l_approval_list_elt.status := l_string;
211
212 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
213 -- handle datacorruption: set error code (?)
214 RETURN;
215 END IF;
216 l_approval_list_elt.approver_type := l_string;
217
218 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
219 -- handle datacorruption: set error code (?)
220 RETURN;
221 END IF;
222 l_approval_list_elt.mandatory_flag := l_string;
223
224 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
225 -- handle datacorruption: set error code (?)
226 RETURN;
227 END IF;
228 l_approval_list_elt.sequence_num := to_number(l_string);
229
230 l_index := l_index + 1;
231 l_approval_list(l_index) := l_approval_list_elt;
232
233 END LOOP;
234
235 -- debug: po_approvallist_s1.print_approval_list(l_approval_list);
236
237 IF (l_approval_list.COUNT > 0) THEN
238 po_approvallist_s1.save_approval_list(
239 p_document_id=>p_document_id,
240 p_document_type=>g_document_type,
241 p_document_subtype=>g_document_subtype,
242 p_first_approver_id=>p_first_approver_id,
243 p_approval_path_id=>NULL,
244 p_approval_list=>l_approval_list,
245 p_last_update_date=>l_last_update_date,
246 p_approval_list_header_id=>p_approval_list_header_id,
247 p_return_code=>l_return_code,
248 p_error_stack=>l_error_stack);
249
250 IF (l_return_code = po_approvallist_s1.E_SUCCESS) THEN
251 NULL;
252 ELSE
253 -- Deal with the specific error codes if needed
254 -- Handle the error stack
255 NULL;
256 END IF;
257 END IF;
258
259 END save_approval_list;
260
261
262
263
264 -- Private routines.
265
266 PROCEDURE MarshalField(p_string IN VARCHAR2,
267 p_quote_char IN VARCHAR2,
268 p_delimiter IN VARCHAR2) IS
269 l_string VARCHAR2(32767) := NULL;
270 BEGIN
271 l_string := p_string;
272 l_string := REPLACE(l_string, p_quote_char, p_quote_char || p_quote_char);
273 l_string := REPLACE(l_string, p_delimiter, p_quote_char || p_delimiter);
274 g_approval_list_string := g_approval_list_string || l_string || p_delimiter;
275 END MarshalField;
276
277 FUNCTION GetNextToken(p_start_pos IN OUT NUMBER,
278 p_quote_char IN VARCHAR2,
279 p_delimiter IN VARCHAR2,
280 p_remove_quotes IN BOOLEAN,
281 p_result OUT VARCHAR2) RETURN BOOLEAN IS
282 l_pos NUMBER;
283 l_start_pos NUMBER;
284 l_max_pos NUMBER;
285 l_string VARCHAR2(32767);
286 BEGIN
287 l_start_pos := p_start_pos;
288 l_max_pos := LENGTH(g_approval_list_string);
289 l_pos := p_start_pos;
290
291 WHILE (l_start_pos < l_max_pos) LOOP
292 l_pos := INSTR(g_approval_list_string, p_delimiter, l_start_pos);
293 IF (l_pos > 0) THEN
294 IF (l_pos = p_start_pos) THEN
295 p_start_pos := l_pos + 1;
296 p_result := NULL;
297 RETURN TRUE;
298 END IF;
299 IF (substr(g_approval_list_string, l_pos-1, 1) <> p_quote_char) THEN
300 IF (p_remove_quotes) THEN
301 l_string := substr(g_approval_list_string, p_start_pos, l_pos-p_start_pos);
302 l_string := REPLACE(l_string, p_quote_char, NULL);
303 p_result := l_string;
304 ELSE
305 p_result := substr(g_approval_list_string, p_start_pos, l_pos-p_start_pos);
306 END IF;
307
308 p_start_pos := l_pos + 1;
309 RETURN TRUE;
310 ELSE
311 l_start_pos := l_pos + 2;
312 END IF;
313 ELSE
314 RETURN FALSE;
315 END IF;
316 END LOOP;
317
318 p_start_pos := l_start_pos;
319 RETURN FALSE;
320 END GetNextToken;
321
322 END por_util_pkg2_test;