1 PACKAGE BODY PO_INTERFACE_ERRORS_SV1 AS
2 /* $Header: POXPIIEB.pls 115.17 2004/03/05 23:30:53 mbhargav ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
6
7 -- <PDOI-Grants Integration Project: START>
8 -- Private function to this file
9 -- This function is a bare wrapper to the insert statement. It would be called
10 -- from the public procedures handle_interface_errors_msg() and
11 -- insert_po_interface_errors(). It is part of the refactoring done in the
12 -- PDOI-Grants Integration Project.
13 PROCEDURE insert_po_interface_errors_msg(
14 X_interface_type IN VARCHAR2,
15 X_Interface_Header_ID IN NUMBER,
16 X_Interface_Line_Id IN NUMBER,
17 X_Interface_Dist_Id IN NUMBER,
18 X_error_message_text IN VARCHAR2,
19 X_error_message_name IN VARCHAR2,
20 X_column_name IN VARCHAR2,
21 X_table_name IN VARCHAR2,
22 X_batch_id IN NUMBER)
23 IS
24
25 -- Moved the AUTONOMOUS_TRANSACTION inside this new procedure. Prior to this
26 -- it was present in the public function insert_po_interface_errors(). That
27 -- function, in turn, now calls this procedure.
28
29 -- Bug 2705777. Making this an autonomous transaction to avoid rollback issues
30 -- with the main transaction's savepoints.
31 PRAGMA AUTONOMOUS_TRANSACTION;
32
33 X_progress varchar2(30) := null;
34 BEGIN
35 X_progress := '010';
36
37 insert into po_interface_errors(Interface_Type,
38 Interface_Transaction_Id,
39 column_name,
40 table_name,
41 error_message,
42 Error_Message_Name,
43 processing_date,
44 Creation_Date,
45 Created_By,
46 Last_Update_Date,
47 Last_Updated_by,
48 Last_Update_Login,
49 Interface_Header_ID,
50 Interface_Line_Id,
51 Interface_Distribution_Id,
52 Request_Id,
53 Program_Application_id,
54 Program_Id,
55 Program_Update_date,
56 Batch_Id)
57 VALUES
58 (X_interface_type,
59 po_interface_errors_s.nextval,
60 X_column_name,
61 X_table_name,
62 X_Error_Message_text,
63 X_Error_Message_name,
64 sysdate,
65 sysdate,
66 fnd_global.user_id,
67 sysdate,
68 fnd_global.user_id,
69 fnd_global.login_id,
70 X_interface_header_id,
71 X_interface_line_id,
72 X_Interface_Dist_Id,
73 fnd_global.conc_request_id,
74 fnd_global.prog_appl_id,
75 fnd_global.conc_program_id,
76 sysdate,
77 X_batch_id);
78
79 -- Have to commit at the end of a successful autonomous transaction
80 commit;
81
82 EXCEPTION
83 WHEN OTHERS THEN
84 po_message_s.sql_error('insert_po_interface_errors_msg', X_progress,
85 sqlcode);
86 RAISE;
87 END insert_po_interface_errors_msg;
88 -- <PDOI-Grants Integration Project: END>
89
90 /*==================================================================*/
91 PROCEDURE handle_interface_errors(X_interface_type IN VARCHAR2,
92 X_Error_type IN VARCHAR2,
93 X_Batch_id IN NUMBER,
94 X_Interface_Header_Id IN NUMBER,
95 X_Interface_Line_id IN NUMBER,
96 X_Error_message_name IN VARCHAR2,
97 X_Table_name IN VARCHAR2,
98 X_Column_name IN VARCHAR2,
99 X_TokenName1 IN VARCHAR2,
100 X_TokenName2 IN VARCHAR2,
101 X_TokenName3 IN VARCHAR2,
102 X_TokenName4 IN VARCHAR2,
103 X_TokenName5 IN VARCHAR2,
104 X_TokenName6 IN VARCHAR2,
105 X_TokenValue1 IN VARCHAR2,
106 X_TokenValue2 IN VARCHAR2,
107 X_TokenValue3 IN VARCHAR2,
108 X_TokenValue4 IN VARCHAR2,
109 X_TokenValue5 IN VARCHAR2,
110 X_TokenValue6 IN VARCHAR2,
111 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
112 X_Interface_Dist_Id IN NUMBER
113 )
114 IS
115
116 X_progress VARCHAR2(3);
117 X_compl_code VARCHAR2(1);
118
119
120 BEGIN
121 IF (g_po_pdoi_write_to_file = 'Y') THEN
122 PO_DEBUG.put_line('-->Intfc error occurred ...' ||
123 X_error_message_name
124 );
125 END IF;
126
127 IF (X_header_processable_flag = 'Y') THEN
128 X_header_processable_flag := 'N';
129 END IF;
130
131 X_progress := '010';
132
133 -- call function po_interface_error_insert to insert a new error record
134 -- in an autonomous transaction. Therefore, all error messages will be
135 -- committed to the db without interfering with the main transaction's
136 -- savepoints
137 X_compl_code := po_interface_errors_sv1.insert_po_interface_errors(
138 X_interface_type,
139 X_Error_type, --<Bug 3375881>
140 X_Batch_id,
141 X_Interface_Header_ID,
142 X_Interface_Line_Id ,
143 X_Interface_Dist_Id,
144 X_Error_Message_name,
145 X_column_name,
146 X_table_name,
147 X_TokenName1,
148 X_TokenName2,
149 X_TokenName3,
150 X_TokenName4,
151 X_TokenName5,
152 X_TokenName6,
153 X_TokenValue1,
154 X_TokenValue2,
155 X_TokenValue3,
156 X_TokenValue4,
157 X_TokenValue5,
158 X_TokenValue6);
159
160 EXCEPTION
161 WHEN others THEN
162 po_message_s.sql_error('handle_interface_errors', x_progress, sqlcode);
163 raise;
164 END handle_interface_errors;
165
166 /* ========================================================================
167
168
169 FUNCTION NAME: insert_po_interface_errors()
170
171 =========================================================================*/
172
173 FUNCTION insert_po_interface_errors(X_interface_type IN VARCHAR2,
174 X_Error_type IN VARCHAR2,
175 X_Batch_id IN NUMBER,
176 X_Interface_Header_ID IN NUMBER,
177 X_Interface_Line_Id IN NUMBER,
178 X_Interface_Dist_Id IN NUMBER,
179 X_error_message_name IN VARCHAR2,
180 X_column_name IN VARCHAR2,
181 X_table_name IN VARCHAR2,
182 X_TokenName1 IN VARCHAR2,
183 X_TokenName2 IN VARCHAR2,
184 X_TokenName3 IN VARCHAR2,
185 X_TokenName4 IN VARCHAR2,
186 X_TokenName5 IN VARCHAR2,
187 X_TokenName6 IN VARCHAR2,
188 X_TokenValue1 IN VARCHAR2,
189 X_TokenValue2 IN VARCHAR2,
190 X_TokenValue3 IN VARCHAR2,
191 X_TokenValue4 IN VARCHAR2,
192 X_TokenValue5 IN VARCHAR2,
193 X_TokenValue6 IN VARCHAR2)
194 Return VARCHAR2 IS
195
196 X_progress varchar2(30) := null;
197 X_Error_Message fnd_new_messages.message_text%type;
198
199 --<Bug 3375881 mbhargav START>
200 l_original_message fnd_new_messages.message_text%type;
201 l_error_type_message fnd_new_messages.message_text%type;
202 --<Bug 3375881 mbhargav END>
203
204 BEGIN
205
206 X_progress := '010';
207
208 fnd_message.set_name('PO', X_Error_Message_Name);
209
210 X_progress := '020';
211
212 if (X_TokenName1 is not null and X_TokenValue1 is not null) then
213 fnd_message.set_token(X_TokenName1, X_TokenValue1);
214 end if;
215 if (X_TokenName2 is not null and X_TokenValue2 is not null) then
216 fnd_message.set_token(X_TokenName2, X_TokenValue2);
217 end if;
218 if (X_TokenName3 is not null and X_TokenValue3 is not null) then
219 fnd_message.set_token(X_TokenName3, X_TokenValue3);
220 end if;
221 if (X_TokenName4 is not null and X_TokenValue4 is not null) then
222 fnd_message.set_token(X_TokenName4, X_TokenValue4);
223 end if;
224 if (X_TokenName5 is not null and X_TokenValue5 is not null) then
225 fnd_message.set_token(X_TokenName5, X_TokenValue5);
226 end if;
227 if (X_TokenName6 is not null and X_TokenValue6 is not null) then
228 fnd_message.set_token(X_TokenName6, X_TokenValue6);
229 end if;
230
231 X_progress := '030';
232
233 --<Bug 3375881 mbhargav START>
234 l_original_Message := Fnd_message.get;
235
236 X_progress := '040';
237
238 IF x_error_type = 'FATAL' THEN
239 fnd_message.set_name('PO', 'PO_ERROR');
240 l_error_type_message := fnd_message.get;
241 ELSIF x_error_type = 'WARNING' THEN
242 fnd_message.set_name('PO', 'PO_WARNING');
243 l_error_type_message := fnd_message.get;
244 ELSE
245 l_error_type_message := null;
246 END IF;
247
248 --The message which is now returned has the_error_type appended to it.
249 --The message stored in interface tables will be of following format:
250 --Warning: <the error message itself>
251 --Error: <the error message itself>
252 x_error_message := substrb(l_error_type_message || ' ' || l_original_message, 1, 2000);
253 --<Bug 3375881 mbhargav END>
254
255 X_progress := '050';
256
257 /* Bug 2860580. Added BATCH_ID in the INSERT statement */
258
259 -- <PDOI-Grants Integration Project: START>
260 -- Refactored this code to call this private function. This private function
261 -- is also called from the new public procedure handle_interface_errors_msg()
262 insert_po_interface_errors_msg(X_interface_type,
263 X_interface_header_id,
264 X_interface_line_id,
265 X_Interface_Dist_Id,
266 X_Error_Message,
267 X_Error_Message_name,
268 X_column_name,
269 X_table_name,
270 X_Batch_id);
271 -- <PDOI-Grants Integration Project: END>
272
273 return('0');
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 po_message_s.sql_error('insert_po_interface_errors', X_progress, sqlcode);
278 ROLLBACK;
279 raise;
280 END insert_po_interface_errors;
281
282 -- Bug 2705777. Removed procedures rollback_changes and rollback_line_changes
283
284 -- <PDOI-Grants Integration Project: START>
285 PROCEDURE handle_interface_errors_msg(
286 X_interface_type IN VARCHAR2,
287 X_Error_type IN VARCHAR2,
288 X_Batch_id IN NUMBER,
289 X_Interface_Header_Id IN NUMBER,
290 X_Interface_Line_id IN NUMBER,
291 X_Error_message_text IN VARCHAR2,
292 X_Error_message_name IN VARCHAR2,
293 X_Table_name IN VARCHAR2,
294 X_Column_name IN VARCHAR2,
295 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
296 X_Interface_Dist_Id IN NUMBER DEFAULT NULL)
297 IS
298 X_progress VARCHAR2(3);
299 X_compl_code VARCHAR2(1);
300 BEGIN
301 PO_DEBUG.put_line('-->Intfc error occurred...<'||X_error_message_text||'>');
302
303 IF X_header_processable_flag = 'Y' THEN
304 X_header_processable_flag := 'N';
305 END IF;
306
307 X_progress := '010';
308 -- call function insert_po_interface_errors_msg to insert a new error record
309 insert_po_interface_errors_msg(X_interface_type,
310 X_Interface_Header_ID,
311 X_Interface_Line_Id ,
312 X_Interface_Dist_Id,
313 X_Error_Message_text,
314 X_Error_Message_name,
315 X_column_name,
316 X_table_name,
317 X_Batch_id);
318 EXCEPTION
319 WHEN OTHERS THEN
320 po_message_s.sql_error('handle_interface_errors_msg', x_progress, sqlcode);
321 RAISE;
322 END handle_interface_errors_msg;
323 -- <PDOI-Grants Integration Project: END>
324
325 END PO_INTERFACE_ERRORS_SV1;
[Home] [Help]