1 package body WIP_INTERFACE_ERR_Utils as
2 /* $Header: wipieutb.pls 120.4 2006/09/04 06:39:09 panagara noship $ */
3
4 --- Added a new Autonomous procedure to insert errors into wip_interface_errors table.
5 --- This is added for bug 5124822
6
7 procedure insert_error(p_interface_id IN number,
8 p_error_type IN Varchar2,
9 p_error IN Varchar2,
10 p_last_update_date IN Date,
11 p_creation_date IN Date,
12 p_created_by IN Number,
13 p_last_update_login IN Number,
14 p_updated_by IN Number);
15
16 -- End of bug fix 5124822
17
18
19 Procedure add_error(p_interface_id number,
20 p_text varchar2,
21 p_error_type number) IS
22
23 error_record request_error;
24 error_type number;
25
26 BEGIN
27
28 error_record.interface_id := p_interface_id;
29 error_record.error_type := p_error_type;
30 error_record.error := substr(p_text,1,500);
31
32 current_errors(current_errors.count+1) := error_record;
33
34 END add_error;
35
36 Procedure load_errors IS
37
38 n_errors number;
39 error_no number := 1;
40
41 l_dummy2 VARCHAR2(1);
42 l_logLevel number;
43 l_last_update_login number;
44 l_last_updated_by number;
45 l_created_by number;
46 l_WJSI_error_exist number;
47
48 BEGIN
49
50 n_errors := current_errors.count;
51
52 WHILE (error_no <= n_errors) LOOP
53 l_logLevel := fnd_log.g_current_runtime_level;
54 if (l_logLevel <= wip_constants.trace_logging) then
55 wip_logger.log('error:' || current_errors(error_no).error, l_dummy2);
56 end if;
57 -- Added the following stmt for bug fix 5124822
58 -- selecting the audit column values from wip_job_schedule_interface
59 -- and pass it to api that inserts into interface errors table.
60 -- We cannot derive these values in insert_error api, as the insert_error api
61 -- is autonomous transaction api.
62
63 /* Fix for bug 5507379. Errors can be either in WJSI or in WJDI */
64 l_WJSI_error_exist := WIP_CONSTANTS.YES;
65 begin
66 select last_update_login,
67 last_updated_by,
68 created_by
69 into l_last_update_login,
70 l_last_updated_by,
71 l_created_by
72 from wip_job_schedule_interface
73 where interface_id = current_errors(error_no).interface_id;
74 exception
75 when no_data_found then
76 l_WJSI_error_exist := WIP_CONSTANTS.NO;
77 end;
78
79 if (l_WJSI_error_exist = WIP_CONSTANTS.NO) then
80 select last_update_login,
81 last_updated_by,
82 created_by
83 into l_last_update_login,
84 l_last_updated_by,
85 l_created_by
86 from wip_job_dtls_interface
87 where interface_id = current_errors(error_no).interface_id;
88 end if;
89
90
91 -- Started calling a new autonomous transaction API to insert a record into
92 -- interface error . This API will commit immediately after inserting into
93 -- interface error table.
94
95 insert_error(p_interface_id => current_errors(error_no).interface_id,
96 p_error_type => current_errors(error_no).error_type,
97 p_error => current_errors(error_no).error,
98 p_last_update_date => sysdate,
99 p_creation_date => sysdate,
100 p_created_by => l_created_by,
101 p_last_update_login => l_last_update_login,
102 p_updated_by => l_last_updated_by);
103
104 -- End of bug fix 5124822
105 error_no := error_no + 1;
106
107 END LOOP;
108
109 /* bug 4650624, commit */
110 --commit;
111
112 current_errors.delete ;
113
114 wip_logger.cleanup(l_dummy2);
115
116 END load_errors;
117
118
119 -- The following API is added for bug fix 5124822
120 -- This API will insert a record into wip_interface_errors table
121 -- and commit it immediately. But this is an autonomous transcation
122
123 procedure insert_error(p_interface_id IN number,
124 p_error_type IN Varchar2,
125 p_error IN Varchar2,
126 p_last_update_date IN Date,
127 p_creation_date IN Date,
128 p_created_by IN Number,
129 p_last_update_login IN Number,
130 p_updated_by IN Number) is
131 PRAGMA AUTONOMOUS_TRANSACTION;
132 Begin
133 insert into wip_interface_errors
134 (interface_id,
135 error_type,
136 error,
137 last_update_date,
138 creation_date,
139 created_by,
140 last_update_login,
141 last_updated_by
142 )
143 Values
144 (p_interface_id,
145 p_error_type,
146 p_error,
147 p_last_update_date,
148 p_creation_date,
149 p_created_by,
150 p_last_update_login,
151 p_updated_by);
152
153
154 commit;
155 End insert_error;
156 END WIP_INTERFACE_ERR_Utils;