1 PACKAGE BODY MSC_SRP_PIPE AS
2 -- $Header: MSCRPLNB.pls 120.5.12010000.2 2008/06/27 22:44:46 hulu ship $
3
4 PLANNER_READY constant number :=6;
5 PLANNER_STARTED constant number :=8;
6 PLANNER_ERROR constant number :=9;
7 LOAD_FILE constant number :=1;
8 LOAD_ITEM constant number :=11;
9 LOAD_SUPPLY constant number :=12;
10 LOAD_DEMAND constant number :=13;
11 LOAD_BOM constant number :=14;
12 DELETING constant number :=3;
13 SELECT_DATA constant number :=2;
14 REPLANNING constant number :=4;
15 FLUSHING constant number :=5;
16
17 SRP_FORECASTING constant number:=20;
18 SRP_LOAD_ITEM constant number:=21;
19 SRP_REPLANNING constant number:=22;
20 SRP_PEGGING constant number:=23;
21 SRP_FLUSHING constant number:=24;
22 SRP_DELETING constant number:=25;
23 SRP_NOTHING_TO_REPLAN constant number:=26;
24 SRP_OLP_ERROR constant number:=28;
25 SRP_OLP_COMPLETE constant number:=27;
26
27
28
29 /*
30 REM Currently, in DRP/ASCP, this is the list of command code engine accept
31 REM #define OLP_REPLAN 2
32 REM #define OLP_PING 4
33 REM #define OLP_EXIT 5
34
35 REM Currently, in DRP/ASCP, replan engine returns following status code
36 REM to indicate planner status.
37 REM #define OLP_READY 6
38 REM #define OLP_INFO 7 ---> this status returns with two additional number
39 REM ---> num#1 --> current milestone
40 REM ---> num#2 --> percentage of completion in this stage, not total
41 REM #define OLP_STARTED 8
42 REM #define OLP_ERROR 9
43
44 REM Planner can be return following stage code in OLP_INFO
45 REM #define OLP_LOAD_FILES 1
46 REM #define OLP_LOAD_ITEMS 11
47 REM #define OLP_LOAD_SUPPLY 12
48 REM #define OLP_LOAD_DEMAND 13
49 REM #define OLP_LOAD_BOM 14
50 REM #define OLP_SELECT_DATA 2
51 REM #define OLP_DELETING 3
52 REM #define OLP_REPLANNING 4
53 REM #define OLP_FLUSHING 5
54
55 REM /*
56 REM Currently, in DRP/ASCP UI (MSCFNSIM.pld), it has 4 timer
57 REM 1. start_olp_timer,which is started when CP is sbumit and before got OLP_READY code
58 REM 2. ready_olp_timer,which is started once engine return OLP_READY -- engine is ready for replan request
59 REM 3. planing_olp_timer,which is started once submit replan reqest
60 REM 3. stop_olp_times, which is started when stop command is sent
61 REM
62 REM The normal way to get the status from engine is to
63 REM 1. ping engine in outpipe with comamnd=4
64 REM 2. read iputpipe to get engine return. Engine could return (6,7,8,9)
65
66 REM #OLP_Ready ==>100%
67 REM /* this procedure is called from java servlet once user click ok button in replan option window
68 REM in DRP/ASCP,planner could be in stage (started,ready,plan,error stage). user can only submit request
69 REM if it is in ready status. but in srp, there is only 1 menu for user, so ui code need to start cp(if
70 REM it is not currently started, submit replan request only after planner is in ready status)
71 REM
72 REM ping engine, if engine return the following code
73 REM #define OLP_READY 6 ===> replan is 100% finished. (??? is this true)
74 REM #define OLP_ERROR 9 ===> replan is 100% finished. with error. (milestone=Error,percentage=100%)
75 REM #define OLP_INFO 7 ===> show milestone=??, percentage=??
76 REM #define OLP_STARTED 8
77 REM */
78
79 /*this procedure is called by servlet periodically.
80 REM p_error_code =-1 -->pipe error
81 REM p_error_code =0 --> normal status
82 REM p_error_code =2 -->no planner
83 REM p_stage_code --> return the stage code of the planner
84 REM p_pcnt -->percentage of completion
85 */
86
87 Procedure get_replan_progress(p_request_id in number,
88 p_outPipe in varchar2,
89 p_inPipe in varchar2,
90 p_error_code out nocopy number,
91 p_stage_code out nocopy number,
92 p_pcnt out nocopy number) is
93
94 l_engine_ping_return_code number; -- (6.7.8.9)
95 l_engine_stage_code number; -- (6,8,9,1,2,3,4,5,11,12,13,14)
96 l_engine_percentage number; -- (only if ping return 7)
97 l_cp_status number;
98
99 begin
100
101
102
103 p_error_code :=0 ; --- normal status
104 p_stage_code :=-99 ;--- not a valid stage code
105 p_pcnt :=-1; --- no change from previous percentage
106
107
108
109 ---dbms_pipe.purge(p_outPipe); --
110 dbms_pipe.pack_message(4); ---
111 if (dbms_pipe.send_message(p_outPipe,0,8192)) =3 then ---
112 p_error_code:=-1 ;-- pipe error
113 return;
114 end if;
115
116 if (DBMS_PIPE.RECEIVE_MESSAGE(p_inPipe,0) in (2,3) )then
117 p_error_code :=-1;
118 return;
119 else
120
121 dbms_pipe.unpack_message(l_engine_ping_return_code);
122 if (l_engine_ping_return_code is null) then
123
124 -- this could be that engine is not running or
125 -- does not reponse ping
126 -- need to check cp status
127 -- if it is not running, p_error_code=2;
128 if (check_cp_status(p_request_id,l_cp_status) =0) then
129 if (l_cp_status in (0,1)) then
130 p_error_code :=2 ; ---- no planner
131 elsif (l_cp_status =-1) then
132 p_error_code :=0 ; --- make sure p_error_code =0
133 p_pcnt :=1;
134 p_stage_code :=SRP_OLP_ERROR;
135 end if;
136 return;
137 end if;
138 elsif (l_engine_ping_return_code = 6 ) then
139 p_stage_code :=PLANNER_READY;
140 elsif (l_engine_ping_return_code =8) then
141 p_stage_code :=PLANNER_STARTED;
142 elsif (l_engine_ping_return_code =9) then
143 p_stage_code :=PLANNER_ERROR;
144 elsif (l_engine_ping_return_code=7) then
145
146 dbms_pipe.unpack_message(l_engine_stage_code);
147 dbms_pipe.unpack_message(l_engine_percentage);
148
149 p_pcnt :=l_engine_percentage;
150 if (l_engine_stage_code =1) then
151 p_stage_code :=LOAD_FILE;
152 elsif (l_engine_stage_code=11) then
153 p_stage_code:=LOAD_ITEM;
154 elsif (l_Engine_stage_code=12) then
155 p_stage_code:=LOAD_SUPPLY;
156 elsif (l_engine_stage_code=13) then
157 p_stage_code:=LOAD_DEMAND;
158 elsif (l_engine_stage_code=14) then
159 p_stage_code:=LOAD_BOM;
160 elsif (l_engine_stage_code=2) then
161 p_stage_code:=SELECT_DATA;
162 elsif(l_engine_stage_code=3) then
163 p_stage_code:=DELETING;
164 elsif (l_Engine_stage_code=4) then
165 p_stage_code:=REPLANNING;
166 elsif(l_engine_stage_code=5) then
167 p_stage_code:=FLUSHING;
168 elsif (l_engine_stage_code=20) then
169 p_stage_code:=SRP_FORECASTING;
170 elsif (l_engine_stage_code=21) then
171 p_stage_code:=SRP_LOAD_ITEM;
172 elsif(l_engine_stage_code=22) then
173 p_stage_code:=SRP_REPLANNING;
174 elsif (l_Engine_stage_code=23) then
175 p_stage_code:=SRP_PEGGING;
176 elsif(l_engine_stage_code=24) then
177 p_stage_code:=SRP_FLUSHING;
178 elsif(l_engine_stage_code=25) then
179 p_stage_code:=SRP_DELETING;
180 elsif(l_engine_stage_code=26) then
181 p_stage_code:=SRP_NOTHING_TO_REPLAN;
182
183 end if;
184 if (p_pcnt =1) then
185 p_stage_code:=SRP_OLP_COMPLETE;
186 end if;
187
188 end if;
189 end if;
190
191 exception
192 when others then
193 if (check_cp_status(p_request_id,l_cp_status) =0) then
194 if (l_cp_status in (0,1)) then
195 p_error_code :=2 ; ---- no planner
196 return;
197 elsif (l_cp_status =-1) then
198 p_error_code :=0 ; --- make sure p_error_code =0
199 p_pcnt :=1;
200 p_stage_code :=SRP_OLP_ERROR;
201 else
202 p_error_code :=0;
203 end if;
204
205 end if;
206
207 -- return;
208
209
210 end get_replan_progress;
211
212
213
214 Function load_pipe( p_pipeName in varchar2,
215 p_msg in varchar2) return number is
216
217 l_reStatus number;
218 begin
219 DBMS_PIPE.PACK_MESSAGE(p_msg);
220
221 l_reStatus := DBMS_PIPE.SEND_MESSAGE(p_pipeName,
222 1000,
223 8192);
224
225
226 --- possible returns are
227 -- 0 --> successfully
228 -- 1 --> time out
229 -- 3 --> internal error
230
231 return l_reStatus;
232
233 END load_pipe;
234
235 /*
236 possible return are:
237 1. message in the pipe;
238 2. PIPE_ERROR if internal error while read pipe
239 3. null if there is no message in the pipe;
240 */
241
242 Function read_pipe(p_pipeName in varchar2) return varchar2 is
243 l_msg varchar2(512) := NULL;
244 l_reStatus number;
245 begin
246 l_reStatus := DBMS_PIPE.RECEIVE_MESSAGE(p_pipeName,0);
247 --- possible return of dbms_pipe.receive_message are
248 --- 0 --> successfully
249 --- 1 --> time out
250 --- 2 --> message is too large. internal error
251 --- 3 --> internal error
252
253 IF l_reStatus = 0 THEN
254 DBMS_PIPE.UNPACK_MESSAGE(l_msg);
255 elsif (l_reStatus =2 ) OR (l_reStatus =3) then
256 l_msg := 'PIPE_ERROR';
257 END IF;
258 return l_msg;
259 END read_pipe;
260
261
262 /* check concurrent request status by calling fnd_concurrent.get_request_status
263 possible return from fnd_concurrent.get_request_status are
264 dev_phase: Running(R),Pending(P),Complete(C),Inactive(I);
265 ------------------------------------
266 dev_status: Normal(R)
267 Terminating(T)
268 Waiting(A)
269 Resuming(B)
270 -------------------------------------
271 Normal(I) -->pending normal
272 Standby(Q) -->pending due to incompatabilities
273 Scheduled(F) -->
274 Paused(W)
275 ------------------------------------
276 ON_HOLD(H) -->Inactive onhold
277 Suspended(S)
278 Disabled(D)
279 No_manager(M)
280 ------------------------------------
281 Normal(C)
282 Warning(G) --->Completed with warning
283 Error(E) --->Completed with Error
284 Terminated(X) -->Terminated
285
286
287
288 return:
289 -1 --> request is completed with error or is terminated
290 0 --> not running
291 1 --> inactive
292 2 --> pending
293 3 --> running
294
295 */
296
297
298 /* The function is better to return a BOOLEAN, however, since we can
299 not pass Types.BOOLEAN from jdbc to PLSQL. it returns number instead
300 -1 -->FALSE
301 0 --> TRUE
302 */
303 Function check_cp_status(p_request_id in number,p_status out NOCOPY number) return number is
304
305 l_phase varchar2(80);
306 l_status varchar2(80);
307 l_dev_phase varchar2(30);
308 l_dev_status varchar2(30);
309 l_msg varchar2(255);
310 l_st number ;
311 l_request_id number :=p_request_id;
312
313 cursor c_child_req (p_parent_req_id number) is
314 select request_id from Fnd_Concurrent_Requests
315 where parent_request_Id = p_parent_req_id;
316
317
318 begin
319
320 if (FND_CONCURRENT.GET_REQUEST_STATUS(request_id=>l_request_id,
321 appl_shortname=>'',
322 program =>'',
323 phase=>l_phase,
324 status=>l_status,
325 dev_phase=>l_dev_phase,
326 dev_status=>l_dev_status,
327 message=>l_msg) = TRUE) then
328 if (l_dev_phase = 'RUNNING' ) then
329 if ( l_dev_status = 'TERMINATING') THEN l_st := 0;
330 else l_st :=3;
331 end if;
332 elsif (l_dev_phase ='INACTIVE') then
333 l_st :=1;
334 elsif (l_dev_phase = 'PENDING' ) then
335 l_St :=2 ;
336 elsif (l_dev_phase='COMPLETE') then
337 if (l_dev_status ='ERROR') OR (l_dev_status = 'TERMINATED') then
338 -- cp completes with errors or is terminated
339 -- we may need to set plan_completion_date to null
340 l_st :=-1;
341 else
342 l_st:=0;
343 end if;
344 end if;
345
346 -- check each child request. if any of the child request is in inactive status
347 -- then set the l_st :=1;
348
349 for cur in c_child_req(l_request_id) loop
350 if (FND_CONCURRENT.GET_REQUEST_STATUS(request_id=>cur.request_id,
351 appl_shortname=>'',
352 program =>'',
353 phase=>l_phase,
354 status=>l_status,
355 dev_phase=>l_dev_phase,
356 dev_status=>l_dev_status,
357 message=>l_msg) = TRUE) then
358 if (l_dev_phase ='INACTIVE') then
359 l_st :=1; --Inactive
360 exit;
361 end if;
362 else
363 return -1;
364 exit;
365 end if;
366 end loop;
367 else
368 return -1;
369 end if;
370 p_status := l_st;
371 return 0;
372
373 end check_cp_status;
374
375 END;
376
377
[Home] [Help]