1 PACKAGE BODY MSC_WS_PLAN_MANAGEMENT AS
2 /* $Header: MSCWPMAB.pls 120.8 2008/03/14 23:17:46 ryliu noship $ */
3
4 g_UserId NUMBER;
5 g_ErrorCode VARCHAR2(9);
6
7 -- validate source plan id, simulate the logic from Value Set "MSC_SRS_NAME_COPY"
8 FUNCTION VALIDATE_COPY_PLAN_ID(
9 OrgId OUT NOCOPY NUMBER,
10 InsId OUT NOCOPY NUMBER,
11 SrcPlanName OUT NOCOPY VARCHAR2,
12 PlanId IN NUMBER
13 ) RETURN VARCHAR2 AS
14 l_ReturnString VARCHAR2(30);
15 l_OrgId NUMBER;
16 l_InsId NUMBER;
17 l_PlanName VARCHAR2(10);
18 BEGIN
19 BEGIN
20 SELECT
21 plans.organization_id,
22 plans.sr_instance_id,
23 plans.compile_designator
24 INTO
25 l_OrgId,
26 l_InsId,
27 l_PlanName
28 FROM
29 msc_plans plans,
30 msc_designators desig
31 WHERE
32 plans.organization_id = desig.organization_id AND
33 plans.sr_instance_id = desig.sr_instance_id AND
34 plans.compile_designator = desig.designator AND
35 NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
36 plans.organization_selection <> 1 AND
37 plans.curr_plan_type in (1,2,3,4,5,8,9) AND
38 plans.plan_completion_date IS NOT NULL AND
39 plans.data_completion_date IS NOT NULL AND
40 plans.plan_id <> -1 AND
41 ( (plans.curr_plan_type in (1,2,3,4,8,9) AND
42 plans.organization_selection <> 1) or
43 plans.curr_plan_type = 5 ) AND
44 NVL(plans.copy_plan_id,-1) = -1 AND
45 NVL(desig.copy_designator_id, -1) = -1 AND
46 plans.plan_id = PlanId;
47 EXCEPTION WHEN NO_DATA_FOUND THEN
48 l_ReturnString := 'INVALID_SRCPLNID';
49 RETURN l_ReturnString;
50 WHEN others THEN
51 g_ErrorCode := 'ERROR_UNEXPECTED_00001';
52 raise;
53 END;
54
55 l_ReturnString := 'OK';
56 OrgId := l_OrgId;
57 InsId := l_InsId;
58 SrcPlanName := l_PlanName;
59 RETURN l_ReturnString;
60 END VALIDATE_COPY_PLAN_ID;
61
62 -- validate destination plan name
63 FUNCTION VALIDATE_DEST_PLAN_NAME(
64 OrgId IN NUMBER,
65 InsId IN NUMBER,
66 PlanName IN VARCHAR2
67 ) RETURN VARCHAR2 AS
68 l_ReturnString VARCHAR2(30);
69 l_Dummy NUMBER;
70 BEGIN
71 BEGIN
72 SELECT 1 INTO l_Dummy
73 FROM
74 msc_plans plans
75 WHERE
76 plans.organization_id = OrgId AND
77 plans.sr_instance_id = InsId AND
78 plans.compile_designator = PlanName;
79 EXCEPTION WHEN NO_DATA_FOUND THEN
80 l_ReturnString := 'OK';
81 RETURN l_ReturnString;
82 WHEN others THEN
83 g_ErrorCode := 'ERROR_UNEXPECTED_00002';
84 raise;
85 END;
86
87 l_ReturnString := 'INVALID_DESTPLNNAME';
88 RETURN l_ReturnString;
89 END VALIDATE_DEST_PLAN_NAME;
90
91 -- get Destination Org Selection
92 FUNCTION GET_DEST_ORG_SELECTION(
93 OrgId IN NUMBER,
94 InsId IN NUMBER,
95 PlanName IN VARCHAR2
96 ) RETURN NUMBER AS
97 l_Dest_OrgSel NUMBER;
98 BEGIN
99 BEGIN
100 SELECT organization_selection INTO l_Dest_OrgSel
101 FROM msc_designators
102 WHERE
103 designator = PlanName AND
104 organization_id = OrgId AND
105 sr_instance_id = InsId;
106 EXCEPTION
107 WHEN others THEN
108 g_ErrorCode := 'ERROR_UNEXPECTED_00003';
109 raise;
110 END;
111 RETURN l_Dest_OrgSel;
112 END GET_DEST_ORG_SELECTION;
113
114
115 -- validate Designator Id
116 FUNCTION VALIDATE_DESIGATOR_ID( DesignatorId IN NUMBER ) RETURN VARCHAR2 AS
117 l_ReturnString VARCHAR2(30);
118 l_DesignatorId NUMBER;
119 BEGIN
120 BEGIN
121 SELECT
122 designator_id INTO l_DesignatorId
123 FROM
124 msc_designators
125 WHERE
126 designator_id = DesignatorId AND
127 nvl(copy_designator_id,-1) = -1 AND
128 (
129 -- 2,3,4 for ASCP. 8 is DRP. 11 is SRP
130 ( designator_type IN (2, 3, 4, 8, 11) AND nvl(collected_flag,2) <> 1 ) OR
131 -- IO plan
132 /*
133 This decode statement is copied from the 'Name' form of IO plan.
134 Will come back and re-look at this decode, it always returns 5,
135 why?
136 */
137 ( designator_type = decode(4, 1,3,2,2,3,4,4,5,9,12) )
138 );
139 EXCEPTION WHEN NO_DATA_FOUND THEN
140 l_ReturnString := 'INVALID_DESIGNATORID';
141 RETURN l_ReturnString;
142 WHEN others THEN
143 g_ErrorCode := 'ERROR_UNEXPECTED_00004';
144 raise;
145
146 END;
147
148 l_ReturnString := 'OK';
149 RETURN l_ReturnString;
150 END VALIDATE_DESIGATOR_ID;
151
152 -- validate archive plan id, simulate the logic from Value Set "MSC_SRS_NAME_COPY"
153 FUNCTION VALIDATE_ARCHIVE_PLAN_ID(
154 ArchiveFlag OUT NOCOPY NUMBER,
155 PlanId IN NUMBER
156 ) RETURN VARCHAR2 AS
157 BEGIN
158 BEGIN
159 SELECT NVL(archive_flag, 2)
160 INTO ArchiveFlag
161 FROM
162 msc_plans plans,
163 msc_designators desig
164 WHERE
165 plans.organization_id = desig.organization_id AND
166 plans.sr_instance_id = desig.sr_instance_id AND
167 plans.compile_designator = desig.designator AND
168 NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
169 plans.organization_selection <> 1 AND
170 plans.curr_plan_type in (1,2,3,4,5,8,9) AND
171 plans.plan_completion_date IS NOT NULL AND
172 plans.data_completion_date IS NOT NULL AND
173 plans.plan_id <> -1 AND
174 ( (plans.curr_plan_type in (1,2,3,4,8,9) AND
175 plans.organization_selection <> 1) or
176 plans.curr_plan_type = 5 ) AND
177 NVL(plans.copy_plan_id,-1) = -1 AND
178 NVL(desig.copy_designator_id, -1) = -1 AND
179 plans.plan_id = PlanId;
180 EXCEPTION WHEN NO_DATA_FOUND THEN
181 RETURN 'INVALID_PLAN_ID';
182 WHEN others THEN
183 g_ErrorCode := 'ERROR_UNEXPECTED_00007';
184 raise;
185 END;
186
187 RETURN 'OK';
188 END VALIDATE_ARCHIVE_PLAN_ID;
189
190 PROCEDURE COPY_PLAN(
191 ProcessId OUT NOCOPY NUMBER,
192 Status OUT NOCOPY VARCHAR2,
193 UserName IN VARCHAR2,
194 RespName IN VARCHAR2,
195 RespAppName IN VARCHAR2,
196 SecurityGroupName IN VARCHAR2,
197 Language IN VARCHAR2,
198 SrcPlanId IN NUMBER,
199 DestPlanName IN VARCHAR2,
200 DestPlanDesc IN VARCHAR2 default NULL,
201 DestATP IN VARCHAR2,
202 DestProd IN VARCHAR2,
203 DestNoti IN VARCHAR2,
204 DestInacOn IN DATE default NULL,
205 CopyOptionsOnly IN VARCHAR2
206 ) IS
207 l_String VARCHAR2(100);
208 l_ResponsibilityId NUMBER;
209 l_SecurityGroupId NUMBER;
210 l_OrgId NUMBER;
211 l_InsId NUMBER;
212 l_SrcPlanName VARCHAR2(10);
213 l_DestOrgSelection NUMBER;
214 l_Number NUMBER;
215 l_PlanType NUMBER;
216 BEGIN
217 -- query user id, responsibility id and security group id
218 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
219 IF (l_String <> 'OK') THEN
220 Status := l_String;
221 RETURN;
222 END IF;
223
224 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SCP',l_SecurityGroupId);
225 IF (l_String <> 'OK') THEN
226 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SRO',l_SecurityGroupId);
227 IF (l_String <> 'OK') THEN
228 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCDP',l_SecurityGroupId);
229 IF (l_String <> 'OK') THEN
230 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SRP',l_SecurityGroupId);
231 IF (l_String <> 'OK') THEN
232 Status := l_String;
233 RETURN;
234 END IF;
235 END IF;
236 END IF;
237 END IF;
238
239 IF MSC_WS_COMMON.BOOL_TO_NUMBER(CopyOptionsOnly) = MSC_UTIL.SYS_YES THEN
240 /*
241 The Copy Plan Options Only feature is not implemented yet, will do it later.
242 */
243 ProcessId := -1;
244 Status := 'We don''t support Copy Plan Options Only feature for now!';
245 ELSE
246 -- check source plan id
247 l_String := VALIDATE_COPY_PLAN_ID(l_OrgId, l_InsId, l_SrcPlanName, SrcPlanId);
248 IF (l_String <> 'OK') THEN
249 ProcessId := -1;
250 Status := l_String;
251 RETURN;
252 END IF;
253
254 -- check destination plan name
255 l_String := VALIDATE_DEST_PLAN_NAME(l_OrgId, l_InsId, DestPlanName);
256 IF (l_String <> 'OK') THEN
257 ProcessId := -1;
258 Status := l_String;
259 RETURN;
260 END IF;
261
262 -- get Destination Org Selection
263 l_DestOrgSelection := GET_DEST_ORG_SELECTION(l_OrgId, l_InsId, l_SrcPlanName);
264
265 BEGIN
266 SELECT curr_plan_type INTO l_PlanType
267 FROM msc_plans
268 WHERE plan_id = SrcPlanId;
269 EXCEPTION WHEN others THEN
270 g_ErrorCode := 'ERROR_UNEXPECTED_00005';
271 raise;
272 END;
273
274 -- validate the rest parameters for diff plan type
275 IF l_PlanType = 4 OR l_PlanType = 8 THEN -- IO/SRP
276 IF DestATP <> 'N' THEN
277 ProcessId := -1;
278 Status := 'INVALID_DESTATP';
279 RETURN;
280 END IF;
281 IF DestProd <> 'N' THEN
282 ProcessId := -1;
283 Status := 'INVALID_DESTPROD';
284 RETURN;
285 END IF;
286 IF l_PlanType = 4 THEN -- IO
287 IF DestNoti <> 'N' THEN
288 ProcessId := -1;
289 Status := 'INVALID_DESTNOTI';
290 RETURN;
291 END IF;
292 END IF;
293 END IF;
294
295 -- Now, submit the conc. program to run
296 l_Number := fnd_request.submit_request(
297 application => 'MSC',
298 program => 'MSCCPP5',
299 argument1 => SrcPlanId,
300 argument2 => DestPlanName,
301 argument3 => DestPlanDesc,
302 argument4 => l_DestOrgSelection,
303 argument5 => MSC_WS_COMMON.BOOL_TO_NUMBER(DestATP),
304 argument6 => MSC_WS_COMMON.BOOL_TO_NUMBER(DestProd),
305 argument7 => MSC_WS_COMMON.BOOL_TO_NUMBER(DestNoti),
306 argument8 => to_char(DestInacOn, 'DD-MM-YYYY'),
307 argument9 => l_OrgId,
308 argument10 => l_InsId
309 );
310 IF (l_Number = 0) THEN
311 ProcessId := -1;
312 -- Status := 'Failed '||fnd_message.get;
313 Status := 'ERROR_SUBMIT';
314 ELSE
315 ProcessId := l_Number;
316 Status := 'SUCCESS';
317 END IF;
318
319 END IF;
320 EXCEPTION
321 WHEN others THEN
322 ProcessId := -1;
323 Status := g_ErrorCode;
324
325 END COPY_PLAN;
326
327 PROCEDURE PURGE_PLAN(
328 ProcessId OUT NOCOPY NUMBER,
329 Status OUT NOCOPY VARCHAR2,
330 UserName IN VARCHAR2,
331 RespName IN VARCHAR2,
332 RespAppName IN VARCHAR2,
333 SecurityGroupName IN VARCHAR2,
334 Language IN VARCHAR2,
335 DesignatorId IN NUMBER
336 ) IS
337 l_String VARCHAR2(100);
338 l_ResponsibilityId NUMBER;
339 l_SecurityGroupId NUMBER;
340 l_Number NUMBER;
341 BEGIN
342 -- query user id, responsibility id and security group id
343 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
344 IF (l_String <> 'OK') THEN
345 Status := l_String;
346 RETURN;
347 END IF;
348
349 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SCP',l_SecurityGroupId);
350 IF (l_String <> 'OK') THEN
351 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SRO',l_SecurityGroupId);
352 IF (l_String <> 'OK') THEN
353 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCDP',l_SecurityGroupId);
354 IF (l_String <> 'OK') THEN
355 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SRP',l_SecurityGroupId);
356 IF (l_String <> 'OK') THEN
357 Status := l_String;
358 RETURN;
359 END IF;
360 END IF;
361 END IF;
362 END IF;
363
364
365 -- check DesignatorId
366 l_String := Validate_Desigator_Id(DesignatorId);
367 IF (l_String <> 'OK') THEN
368 ProcessId := -1;
369 Status := l_String;
370 RETURN;
371 END IF;
372
373 -- Hardcoding to mfg, Advanced Supply Chain Planner for prototype
374 -- this init function call is embedded in MSC_WS_COMMON.VALIDATE_USER_RESP
375 -- fnd_global.apps_initialize(1068, 21634, 724);
376
377 -- Update the MSC_DESIGNATORS table to disable it to prevent further
378 -- activities, such as plan launch while the purge is taking place
379 BEGIN
380 UPDATE msc_designators
381 SET disable_date = sysdate
382 WHERE designator_id = DesignatorId;
383 EXCEPTION WHEN others THEN
384 g_ErrorCode := 'ERROR_UNEXPECTED_00006';
385 raise;
386 END;
387
388 -- Now, submit the conc. program to run
389 l_Number := fnd_request.submit_request(
390 application => 'MSC',
391 program => 'MSCPRG',
392 argument1 => to_char(DesignatorId)
393 );
394 IF (l_Number = 0) THEN
395 ProcessId := -1;
396 -- Status := 'Failed '||fnd_message.get;
397 Status := 'ERROR_SUBMIT';
398 ELSE
399 ProcessId := l_Number;
400 Status := 'SUCCESS';
401 END IF;
402
403 EXCEPTION
404 WHEN others THEN
405 ProcessId := -1;
406 Status := g_ErrorCode;
407
408 END PURGE_PLAN;
409
410 PROCEDURE ARCHIVE_PLAN(
411 ProcessId OUT NOCOPY NUMBER,
412 Status OUT NOCOPY VARCHAR2,
413 UserName IN VARCHAR2,
414 RespName IN VARCHAR2,
415 RespAppName IN VARCHAR2,
416 SecurityGroupName IN VARCHAR2,
417 Language IN VARCHAR2,
418 PlanId IN NUMBER
419 ) IS
420 l_String VARCHAR2(100);
421 l_ResponsibilityId NUMBER;
422 l_SecurityGroupId NUMBER;
423 l_ArchiveFlag NUMBER;
424 l_Number NUMBER;
425 BEGIN
426 -- dbms_output.put_line('Matthew: Init');
427 -- init global variables
428 g_ErrorCode := '';
429 ProcessId := -1;
430
431 -- query user id, responsibility id and security group id
432 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
433 IF (l_String <> 'OK') THEN
434 Status := l_String;
435 RETURN;
436 END IF;
437
438 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SCP',l_SecurityGroupId);
439 IF (l_String <> 'OK') THEN
440 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SRO',l_SecurityGroupId);
441 IF (l_String <> 'OK') THEN
442 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCDP',l_SecurityGroupId);
443 IF (l_String <> 'OK') THEN
444 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSCFPCMN-SRP',l_SecurityGroupId);
445 IF (l_String <> 'OK') THEN
446 Status := l_String;
447 RETURN;
448 END IF;
449 END IF;
450 END IF;
451 END IF;
452
453 -- check plan id
454 l_String := VALIDATE_ARCHIVE_PLAN_ID(l_ArchiveFlag, PlanId);
455 IF (l_String <> 'OK') THEN
456 Status := l_String;
457 RETURN;
458 END IF;
459
460 -- Now, submit the conc. program to run
461 /* * * * * * * * * * * * * * * * * * * * * * * *
462 2008/02/25
463 Although Archive Plan Summary conc prog is documented
464 to take plan id as the only parameter in MA1DV210,
465 it is coded to take an extra parameter in
466 patch/115/sql/MSCBISUB.pls, 120.7 2008/01/26 01:18:16.
467
468 l_Number := fnd_request.submit_request(
469 application => 'MSC',
470 program => 'MSCHUBA',
471 argument1 => to_char(PlanId)
472 );
473 * * * * * * * * * * * * * * * * * * * * * * * */
474
475 l_Number := fnd_request.submit_request(
476 application => 'MSC',
477 program => 'MSCHUBA',
478 argument1 => PlanId,
479 argument3 => l_ArchiveFlag
480 );
481
482 IF (l_Number = 0) THEN
483 Status := 'ERROR_SUBMIT';
484 ELSE
485 ProcessId := l_Number;
486 Status := 'SUCCESS';
487 END IF;
488
489 EXCEPTION
490 WHEN others THEN
491 Status := g_ErrorCode;
492
493 END ARCHIVE_PLAN;
494
495 END MSC_WS_PLAN_MANAGEMENT;
496
[Home] [Help]