[Home] [Help]
PACKAGE BODY: APPS.WIP_JDI_UTILS
Source
1 package body WIP_JDI_Utils as
2 /* $Header: wipjdiub.pls 120.2 2005/12/13 11:41:23 yulin noship $ */
3
4 Procedure Begin_Processing_Request(p_group_id number,
5 p_parent_header_id number,
6 x_err_code out NOCOPY varchar2,
7 x_err_msg out NOCOPY varchar2,
8 x_return_status out NOCOPY varchar2) IS
9
10
11 BEGIN
12
13 -- Provide interface_id for every record
14 -- Update process status to be RUNNING
15 -- Do autonomous commit;
16
17 begin
18
19 IF (WIP_JOB_DETAILS.STD_ALONE = 1) THEN
20 Update WIP_JOB_DTLS_INTERFACE
21 SET interface_id = WIP_INTERFACE_S.NEXTVAL,
22 process_status = WIP_CONSTANTS.RUNNING
23 WHERE group_id = p_group_id
24 AND process_status = WIP_CONSTANTS.PENDING
25 AND process_phase = WIP_CONSTANTS.ML_VALIDATION;
26 ELSE
27 Update WIP_JOB_DTLS_INTERFACE
28 SET interface_id = WIP_INTERFACE_S.NEXTVAL,
29 process_status = WIP_CONSTANTS.RUNNING
30 WHERE group_id = p_group_id
31 AND parent_header_id = p_parent_header_id
32 AND process_status = WIP_CONSTANTS.PENDING
33 AND process_phase = WIP_CONSTANTS.ML_VALIDATION;
34 END IF;
35
36 exception
37 when no_data_found then
38 x_err_code := SQLCODE;
39 x_err_msg := 'No pending request!';
40 x_return_status := FND_API.G_RET_STS_ERROR;
41 end;
42
43 END Begin_Processing_Request;
44
45
46 Procedure Error_If_Batch(p_group_id number,
47 p_new_process_status number,
48 p_where_clause varchar2,
49 p_error_type number,
50 p_error_msg varchar2) IS
51
52 x_statement varchar2(2000) :=
53 ' select interface_id
54 from wip_job_dtls_interface WJDI' ||
55 ' where WJDI.group_id = :x_group_id'||
56 ' and WJDI.process_phase = '|| WIP_CONSTANTS.ML_VALIDATION ||
57 ' and WJDI.process_status in ('|| WIP_CONSTANTS.RUNNING||
58 ','||WIP_CONSTANTS.PENDING||
59 ','||WIP_CONSTANTS.WARNING ||') and '||
60 replace(p_where_clause, ' ',' ');
61
62 x_cursor_id integer;
63 n_rows_fetched integer;
64 x_jdi_interface_id number;
65 x_error_type number;
66 x_error_msg varchar2(500);
67
68 begin
69 if (p_error_type = MSG_COLUMN) then
70
71 -- Fetch the invalid-column message.
72
73 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
74 fnd_message.set_token('COLUMN', p_error_msg, false);
75 x_error_type := MSG_ERROR;
76
77 elsif (p_error_type = MSG_CONC) then
78
79 -- Use the message that is already on the stack;
80 -- there is no need to fetch it.
81 x_error_type := MSG_ERROR;
82
83 else
84
85 x_error_type := p_error_type;
86
87 end if;
88
89 -- Execute dynamic sql.
90
91 x_cursor_id := dbms_sql.open_cursor ;
92 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native) ;
93 dbms_sql.define_column(x_cursor_id, 1, x_jdi_interface_id);
94 dbms_sql.bind_variable(x_cursor_id, ':x_group_id', p_group_id);
95 n_rows_fetched := dbms_sql.execute(x_cursor_id) ;
96
97 LOOP
98 n_rows_fetched := dbms_sql.fetch_rows(x_cursor_id) ;
99
100 if (n_rows_fetched = 0) then
101 dbms_sql.close_cursor(x_cursor_id);
102 exit;
103 end if;
104
105 dbms_sql.column_value(x_cursor_id, 1,x_jdi_interface_id);
106
107 -- Fetch the requested error message.
108
109 fnd_message.set_name('WIP', p_error_msg) ;
110 fnd_message.set_token('INTERFACE', ' '||x_jdi_interface_id,FALSE);
111 x_error_msg :=substr(fnd_message.get,1,500);
112
113 -- If it stand alone, insert the interface_id of details
114 -- else insert the interface_id of job header.
115
116 if WIP_JOB_DETAILS.std_alone = 1 THEN
117
118 WIP_INTERFACE_ERR_Utils.add_error(x_jdi_interface_id,
119 x_error_msg,
120 x_error_type);
121
122 elsif WIP_JOB_DETAILS.std_alone = 0 then
123
124 WIP_INTERFACE_ERR_Utils.add_error(WIP_JSI_Utils.current_interface_id,
125 x_error_msg,
126 x_error_type);
127
128 end if;
129
130 END LOOP;
131
132 -- Update process_status of the records.
133
134 x_statement :=
135 ' UPDATE WIP_JOB_DTLS_INTERFACE WJDI'||
136 ' SET PROCESS_STATUS = :x_New_Process_Status ' ||
137 ' WHERE GROUP_ID = :x_Group_Id ' ||
138 ' AND PROCESS_PHASE = ' || WIP_CONSTANTS.ML_VALIDATION || '
139 AND PROCESS_STATUS IN ('|| WIP_CONSTANTS.RUNNING||
140 ','||WIP_CONSTANTS.PENDING||
141 ','||WIP_CONSTANTS.WARNING ||')'||
142 ' AND ' || replace(p_where_clause, ' ',' ');
143
144 begin
145 x_cursor_id := dbms_sql.open_cursor;
146 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native);
147 dbms_sql.bind_variable(x_cursor_id, ':x_Group_Id', P_Group_Id);
148 dbms_sql.bind_variable(x_cursor_id, ':x_New_Process_Status', P_New_Process_Status);
149 n_rows_fetched := dbms_sql.execute(x_cursor_id);
150 dbms_sql.close_cursor(x_cursor_id);
151 end;
152
153 END Error_If_Batch;
154
155
156 Procedure End_Processing_Request(p_wip_entity_id number,
157 p_organization_id number) IS
158
159 x_count number;
160
161 BEGIN
162
163 IF WIP_JOB_DETAILS.std_alone = 1 THEN
164
165 select count(*) into x_count
166 from wip_job_dtls_interface
167 where wip_entity_id = p_wip_entity_id
168 and organization_id = p_organization_id
169 and process_phase = WIP_CONSTANTS.ML_VALIDATION
170 and process_status = WIP_CONSTANTS.ERROR ;
171
172 if x_count > 0 then
173 rollback;
174
175 -- Update the process status in detail interface table.
176
177 Change_Status_Error(null,null, p_wip_entity_id, p_organization_id);
178
179 commit;
180 else
181 commit;
182 end if;
183
184 END IF;
185
186 /* bug 4650624, move load_errors out of if so errors got recorded no matter what mode it is in */
187 -- Load all errors from PL/SQL table into wip_interface_errors.
188
189 WIP_INTERFACE_ERR_Utils.load_errors;
190
191
192 END End_Processing_Request;
193
194
195 Procedure Change_Status_Error(p_group_id number := null,
196 p_parent_header_id number := null,
197 p_wip_entity_id number := null,
198 p_organization_id number := null) IS
199
200 BEGIN
201 begin
202 IF WIP_JOB_DETAILS.std_alone = 1 THEN
203
204 Update WIP_JOB_DTLS_INTERFACE
205 SET process_status = WIP_CONSTANTS.ERROR
206 WHERE wip_entity_id = p_wip_entity_id
207 AND organization_id = p_organization_id;
208
209 ELSIF WIP_JOB_DETAILS.std_alone = 0 THEN
210
211 Update WIP_JOB_DTLS_INTERFACE
212 SET process_status = WIP_CONSTANTS.ERROR
213 WHERE group_id = p_group_id
214 AND parent_header_id = p_parent_header_id;
215
216 END IF;
217
218 exception
219 when no_data_found then
220 null;
221 WHEN others then
222 raise;
223 end;
224
225 END Change_Status_Error;
226
227
228 /****** Used in pending jobs and schedules form for resubmitting******/
229 Procedure Change_Status_Pending(p_row_id varchar2,p_group_id number) IS
230
231 x_group_id number;
232 x_header_id number;
233
234 BEGIN
235 begin
236
237 select group_id, header_id
238 into x_group_id, x_header_id
239 from wip_job_schedule_interface
240 where rowid = p_row_id
241 and load_type in (WIP_CONSTANTS.CREATE_JOB,WIP_CONSTANTS.RESCHED_JOB,
242 WIP_CONSTANTS.CREATE_NS_JOB);
243
244 Update WIP_JOB_DTLS_INTERFACE
245 SET process_status = WIP_CONSTANTS.PENDING,
246 group_id = p_group_id
247 WHERE group_id = x_group_id
248 AND parent_header_id = x_header_id;
249
250 exception
251 WHEN no_data_found then
252 null;
253 WHEN others then
254 raise;
255 end;
256
257 END Change_Status_Pending;
258
259 end WIP_JDI_Utils;