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