[Home] [Help]
PACKAGE BODY: APPS.PO_INTERFACE_ERRORS_UTL
Source
1 PACKAGE BODY PO_INTERFACE_ERRORS_UTL AS
2 /* $Header: PO_INTERFACE_ERRORS_UTL.plb 120.1 2006/05/25 22:00:12 bao noship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_INTERFACE_ERRORS_UTL');
6
7 TYPE errors_tbl_type IS TABLE OF PO_INTERFACE_ERRORS%ROWTYPE;
8
9 g_errors_tbl errors_tbl_type;
10
11 g_batch_size CONSTANT NUMBER := 5000; -- maximum number of messages the
12 -- structure will hold before flushing
13 -- them to PO_INTERFACE ERRORS table
14
15 -------------------------------------------------------
16 -------------- PUBLIC PROCEDURES ----------------------
17 -------------------------------------------------------
18
19 -----------------------------------------------------------------------
20 --Start of Comments
21 --Name: init_errors_tbl
22 --Function:
23 -- Initialize the data structure that holds interface errors records
24 --Parameters:
25 --IN:
26 --IN OUT:
27 --OUT:
28 --End of Comments
29 ------------------------------------------------------------------------
30 PROCEDURE init_errors_tbl IS
31
32 d_api_name CONSTANT VARCHAR2(30) := 'init_errors_tbl';
33 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
34 d_position NUMBER;
35
36 BEGIN
37 d_position := 0;
38
39 IF (PO_LOG.d_proc) THEN
40 PO_LOG.proc_begin(d_module);
41 END IF;
42
43 g_errors_tbl := errors_tbl_type();
44
45 IF (PO_LOG.d_proc) THEN
46 PO_LOG.proc_end(d_module);
47 END IF;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 PO_MESSAGE_S.add_exc_msg
52 ( p_pkg_name => d_pkg_name,
53 p_procedure_name => d_api_name || '.' || d_position
54 );
55 RAISE;
56 END init_errors_tbl;
57
58 -----------------------------------------------------------------------
59 --Start of Comments
60 --Name: add_to_errors_tbl
61 --Function:
62 -- Adds an interface error to the structure. It also derives error
63 -- message text from the message name.
64 --Parameters:
65 --IN:
66 --p_err_type
67 -- Type of the record. Possible values are 'FATAL' and 'WARNING'
68 --p_err_rec
69 -- Interfac error record that contains additional information about
70 -- the error
71 --IN OUT:
72 --OUT:
73 --End of Comments
74 ------------------------------------------------------------------------
75 PROCEDURE add_to_errors_tbl
76 ( p_err_type IN VARCHAR2,
77 p_err_rec IN PO_INTERFACE_ERRORS%ROWTYPE
78 ) IS
79
80 d_api_name CONSTANT VARCHAR2(30) := 'add_to_errors_tbl';
81 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
82 d_position NUMBER;
83
84 l_unknown_intf_type CONSTANT VARCHAR2(25) := 'UNKNOWN';
85 l_indx NUMBER;
86 l_err_type_msg VARCHAR2(2000);
87
88 BEGIN
89 d_position := 0;
90
91 IF (PO_LOG.d_proc) THEN
92 PO_LOG.proc_begin(d_module, 'err_name', p_err_rec.error_message_name );
93 END IF;
94
95 IF (g_errors_tbl IS NULL) THEN
96 init_errors_tbl;
97 END IF;
98
99 -- add one more record to the plsql table
100 g_errors_tbl.extend;
101 l_indx := g_errors_tbl.COUNT;
102 g_errors_tbl(l_indx) := p_err_rec;
103
104 IF (g_errors_tbl(l_indx).interface_type IS NULL) THEN
105 g_errors_tbl(l_indx).interface_type := l_unknown_intf_type;
106 END IF;
107
108 d_position := 10;
109
110 SELECT po_interface_errors_s.nextval
111 INTO g_errors_tbl(l_indx).interface_transaction_id
112 FROM DUAL;
113
114 -- default WHO columns, etc.
115 g_errors_tbl(l_indx).processing_date := SYSDATE;
116 g_errors_tbl(l_indx).creation_date := SYSDATE;
117 g_errors_tbl(l_indx).created_by := FND_GLOBAL.user_id;
118 g_errors_tbl(l_indx).last_update_date := SYSDATE;
119 g_errors_tbl(l_indx).last_updated_by := FND_GLOBAL.user_id;
120 g_errors_tbl(l_indx).last_update_login := FND_GLOBAL.login_id;
121 g_errors_tbl(l_indx).request_id := FND_GLOBAL.conc_request_id;
122 g_errors_tbl(l_indx).program_application_id := FND_GLOBAL.prog_appl_id;
123 g_errors_tbl(l_indx).program_id := FND_GLOBAL.conc_program_id;
124 g_errors_tbl(l_indx).program_update_date := SYSDATE;
125
126 IF (p_err_type = 'FATAL') THEN
127 FND_MESSAGE.set_name('PO', 'PO_ERROR');
128 l_err_type_msg := FND_MESSAGE.get || ' ';
129 ELSIF (p_err_type = 'WARNING') THEN
130 FND_MESSAGE.set_name('PO', 'PO_WARNING');
131 l_err_type_msg := FND_MESSAGE.get || ' ';
132 END IF;
133
134 d_position := 20;
135
136 -- Generate error message text
137 IF (g_errors_tbl(l_indx).error_message IS NULL AND
138 g_errors_tbl(l_indx).error_message_name IS NOT NULL) THEN
139
140 -- bug5247736 - Use app name provided
141 FND_MESSAGE.set_name (g_errors_tbl(l_indx).app_name,
142 g_errors_tbl(l_indx).error_message_name);
143
144 IF (g_errors_tbl(l_indx).token1_name IS NOT NULL) THEN
145 FND_MESSAGE.set_token(g_errors_tbl(l_indx).token1_name,
146 g_errors_tbl(l_indx).token1_value);
147 END IF;
148
149 IF (g_errors_tbl(l_indx).token2_name IS NOT NULL) THEN
150 FND_MESSAGE.set_token(g_errors_tbl(l_indx).token2_name,
151 g_errors_tbl(l_indx).token2_value);
152 END IF;
153
154 IF (g_errors_tbl(l_indx).token3_name IS NOT NULL) THEN
155 FND_MESSAGE.set_token(g_errors_tbl(l_indx).token3_name,
156 g_errors_tbl(l_indx).token3_value);
157 END IF;
158
159 IF (g_errors_tbl(l_indx).token4_name IS NOT NULL) THEN
160 FND_MESSAGE.set_token(g_errors_tbl(l_indx).token4_name,
161 g_errors_tbl(l_indx).token4_value);
162 END IF;
163
164 IF (g_errors_tbl(l_indx).token5_name IS NOT NULL) THEN
165 FND_MESSAGE.set_token(g_errors_tbl(l_indx).token5_name,
166 g_errors_tbl(l_indx).token5_value);
167 END IF;
168
169 IF (g_errors_tbl(l_indx).token6_name IS NOT NULL) THEN
170 FND_MESSAGE.set_token(g_errors_tbl(l_indx).token6_name,
171 g_errors_tbl(l_indx).token6_value);
172 END IF;
173
174 g_errors_tbl(l_indx).error_message :=
175 SUBSTRB(l_err_type_msg || FND_MESSAGE.get, 1, 2000);
176 ELSE
177 g_errors_tbl(l_indx).error_message :=
178 SUBSTRB(l_err_type_msg || g_errors_tbl(l_indx).error_message, 1, 2000);
179 END IF;
180
181 IF (get_error_count = g_BATCH_SIZE) THEN
182 flush_errors_tbl;
183 END IF;
184
185 IF (PO_LOG.d_proc) THEN
186 PO_LOG.proc_end(d_module);
187 END IF;
188 EXCEPTION
189 WHEN OTHERS THEN
190 PO_MESSAGE_S.add_exc_msg
191 ( p_pkg_name => d_pkg_name,
192 p_procedure_name => d_api_name || '.' || d_position
193 );
194 RAISE;
195 END add_to_errors_tbl;
196
197
198 -----------------------------------------------------------------------
199 --Start of Comments
200 --Name: flush_errors_tbl
201 --Function:
202 -- Insert all stored interface error records into database. The structure
203 -- is reinitialized within this procedure
204 --Parameters:
205 --IN:
206 --IN OUT:
207 --OUT:
208 --End of Comments
209 ------------------------------------------------------------------------
210 PROCEDURE flush_errors_tbl IS
211 PRAGMA AUTONOMOUS_TRANSACTION;
212
213 d_api_name CONSTANT VARCHAR2(30) := 'flush_errors_tbl';
214 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
215 d_position NUMBER;
216
217 BEGIN
218 d_position := 0;
219
220 IF (PO_LOG.d_proc) THEN
221 PO_LOG.proc_begin(d_module);
222 END IF;
223
224 IF (g_errors_tbl IS NULL) THEN
225 RETURN;
226 END IF;
227
228 FORALL i IN 1..g_errors_tbl.COUNT
229 INSERT INTO po_interface_errors
230 VALUES g_errors_tbl(i);
231
232 d_position := 10;
233
234 init_errors_tbl;
235
236 COMMIT;
237
238 IF (PO_LOG.d_proc) THEN
239 PO_LOG.proc_end(d_module);
240 END IF;
241 EXCEPTION
242 WHEN OTHERS THEN
243 PO_MESSAGE_S.add_exc_msg
244 ( p_pkg_name => d_pkg_name,
245 p_procedure_name => d_api_name || '.' || d_position
246 );
247 RAISE;
248 END flush_errors_tbl;
249
250 -----------------------------------------------------------------------
251 --Start of Comments
252 --Name: get_error_count
253 --Function:
254 -- Get the number of errors that are currently being held in the structure
255 --Parameters:
256 --IN:
257 --IN OUT:
258 --OUT:
259 --Returns:
260 --End of Comments
261 ------------------------------------------------------------------------
262 FUNCTION get_error_count RETURN NUMBER IS
263
264 d_api_name CONSTANT VARCHAR2(30) := 'get_error_count';
265 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
266 d_position NUMBER;
267
268 BEGIN
269 d_position := 0;
270
271 RETURN g_errors_tbl.COUNT;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 PO_MESSAGE_S.add_exc_msg
276 ( p_pkg_name => d_pkg_name,
277 p_procedure_name => d_api_name || '.' || d_position
278 );
279 RAISE;
280 END get_error_count;
281
282
283 END PO_INTERFACE_ERRORS_UTL;