[Home] [Help]
PACKAGE BODY: APPS.MSD_SOP
Source
1 PACKAGE BODY MSD_SOP AS
2 /* $Header: msdspwfb.pls 120.1 2006/02/13 12:47:38 faliu noship $ */
3
4
5 express_server varchar2(240);
6 DBName varchar2(80);
7 CodeLoc varchar2(80);
8 SharedLoc varchar2(80);
9 PlName varchar2(30);
10 planID varchar2(15);
11 Owner varchar2(320);
12 DPAdmin varchar2(320);
13 FixedDate varchar2(30);
14 g_owner varchar2(340);
15 Master varchar2(1); --agb 02/15/02 is ODP Master controling cycle
16 gItemType varchar2(30); -- noks 10/20/03 added for developers only,
17 gItemKey varchar2(250);
18 gPlanID varchar2(20);
19
20 -- ========================================================================
21 -- Set_Master_Attributes
22 --
23 -- This program creates process named by ProcessName and
24 -- sets basic attributes for it.
25 -- IN
26 -- PlanName - A valid plan name from msd_demand_plans_v
27 -- NumDays_to_delayDist - Number of days to delay the distribution process
28 -- NumDays_to_collect - Number of days for running collection
29 -- NumDays_to_delayUpld - Number of days to delay the upload process
30 -- OUT
31 -- errbuf - error message : process or PL/SQL error.
32 -- retcode - return code (0 = success, 2 = error).
33
34 procedure Set_Master_Attributes(
35 errbuf out NOCOPY varchar2,
36 retcode out NOCOPY number,
37 PlanName in varchar2,
38 Days_tocollect in varchar2,
39 Days_delayUpld in varchar2,
40 ProcessName in varchar2)
41 IS
42
43 retText varchar2(200);
44 dispMesg varchar2(200);
45 itemtype varchar2(8);
46 owner varchar2(320);
47 instcode varchar2(3);
48 org varchar2(8);
49 PlanID varchar2(16);
50 itemkey varchar2(240);
51 numDaysToCol number;
52 DelayDaysToUpld number;
53 codeDBName varchar2(20);
54
55 userID number;
56 respID number;
57 respApplID number;
58
59 BEGIN
60
61 itemtype := 'ODPCYCLE';
62 owner := fnd_global.user_name;
63 userID := fnd_global.user_id;
64 respID := fnd_global.resp_id;
65 respApplID := fnd_global.resp_appl_id;
66
67
68 errbuf := ' ';
69
70 -- Get needed plan information
71 SELECT demand_plan_id, code_location, shared_db_prefix, shared_db_location, express_connect_string INTO PlanID, CodeLoc, DBName, SharedLoc, express_server
72 from msd_demand_plans_v
73 where demand_plan_name=PlanName;
74
75 -- Get organiztion code and instance
76 SELECT msc_trading_partners.organization_code, msc_apps_instances.instance_code
77 INTO org, instcode
78 FROM msc_trading_partners, msc_apps_instances, msd_demand_plans_v
79 WHERE PlanID = msd_demand_plans_v.DEMAND_PLAN_ID AND
80 msd_demand_plans_v.ORGANIZATION_ID = msc_trading_partners.sr_tp_id
81 and msd_demand_plans_v.sr_instance_id = msc_trading_partners.sr_instance_id
82 and msc_trading_partners.partner_type = 3
83 AND msd_demand_plans_v.SR_INSTANCE_ID = msc_apps_instances.instance_id;
84
85 --get MSD code database name
86 select fnd_profile.value('MSD_CODE_AW') into codeDBName from dual;
87
88 codeDBName := nvl(codeDBName, 'ODPCODE');
89
90 numDaysToCol := to_number(Days_tocollect);
91 DelayDaysToUpld := to_number(Days_delayUpld);
92
93 -- Set item key
94 itemKey := org || '-' || PlanName || '-' || to_char( sysdate, 'MM/DD/YYYY-HH24:MI:SS') || '-' || ProcessName || '-' || TO_CHAR(DBMS_RANDOM.RANDOM);
95
96 gItemType := ItemType;
97 gItemKey := itemKey;
98 gPlanID := PlanID;
99
100 -- Create WF Automate process instance
101 wf_engine.CreateProcess(ItemType => ItemType,
102 itemKey => ItemKey,
103 process => ProcessName);
104
105 -- setup attributes for Master Automation process
106 wf_engine.SetItemAttrText(Itemtype => ItemType,
107 Itemkey => ItemKey,
108 aname => 'CODEDB',
109 avalue => codeDBName);
110
111 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
112 Itemkey => ItemKey,
113 aname => 'USER_ID',
114 avalue => userID);
115 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
116 Itemkey => ItemKey,
117 aname => 'RESP_ID',
118 avalue => respID);
119 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
120 Itemkey => ItemKey,
121 aname => 'RESP_APPL_ID',
122 avalue => respApplID);
123
124 -- This should be the demand planning administrator.
125 wf_engine.SetItemOwner(ItemType => ItemType,
126 ItemKey => ItemKey,
127 owner => owner);
128 -- Sets new attribute Is OPD Master running.
129 wf_engine.SetItemAttrText(Itemtype => ItemType,
130 Itemkey => ItemKey,
131 aname => 'ISMASTER',
132 avalue => 'Y');
133 wf_engine.SetItemAttrText(Itemtype => ItemType,
134 Itemkey => ItemKey,
135 aname => 'DPADMIN',
136 avalue => owner);
137 wf_engine.SetItemAttrText(Itemtype => ItemType,
138 Itemkey => ItemKey,
139 aname => 'ODPROLE',
140 avalue => owner);
141 -- Plan ID!
142 wf_engine.SetItemAttrText(Itemtype => ItemType,
143 Itemkey => ItemKey,
144 aname => 'ODPPLAN',
145 avalue => PlanID);
146 wf_engine.SetItemAttrText(Itemtype => ItemType,
147 Itemkey => ItemKey,
148 aname => 'PLNAME',
149 avalue => PlanName);
150 wf_engine.SetItemAttrText(Itemtype => ItemType,
151 Itemkey => ItemKey,
152 aname => 'CODELOC',
153 avalue => CodeLoc);
154 wf_engine.SetItemAttrText(Itemtype => ItemType,
155 Itemkey => ItemKey,
156 aname => 'DBNAME',
157 avalue => DBName);
158 wf_engine.SetItemAttrText(Itemtype => ItemType,
159 Itemkey => ItemKey,
160 aname => 'SHAREDLOC',
161 avalue => SharedLoc);
162 wf_engine.SetItemAttrText(Itemtype => ItemType,
163 Itemkey => ItemKey,
164 aname => 'EXPCONN',
165 avalue => express_server);
166 -- set NumDays to collect
167 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
168 Itemkey => ItemKey,
169 aname => 'NUMDAYS',
170 avalue => NumDaysToCol);
171 -- set DelayDays to Upload
172 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
173 Itemkey => ItemKey,
174 aname => 'DELAYDAYS',
175 avalue => DelayDaysToUpld);
176
177 return;
178
179 exception
180 when others then
181 retcode :='2';
182 errbuf:=substr(sqlerrm, 1, 255);
183 raise;
184
185
186
187 end Set_Master_Attributes;
188
189 --
190 --
191 -- ========================================================================
192 -- Start_SOP_Master
193 --
194 -- Procedure which is used by concurrent program MSDSOPMASTER
195 -- to run the S&OP Master Process (SOPAUTOMATE)
196 --
197 --
198 -- IN
199 -- PlanName - A valid plan name from msd_demand_plans_v
200 -- NumDays_to_delayDist - Number of days to delay the distribution process
201 -- NumDays_to_collect - Number of days for running collection
202 -- NumDays_to_delayUpld - Number of days to delay the upload process
203 -- OUT
204 -- errbuf - error message : process or PL/SQL error.
205 -- retcode - return code (0 = success, 2 = error).
206 --
207
208 procedure Start_SOP_Master(
209 errbuf out NOCOPY varchar2,
210 retcode out NOCOPY number,
211 PlanName in varchar2,
212 NumDays_to_delayDist in varchar2,
213 NumDays_to_collect in varchar2,
214 NumDays_to_delayUpld in varchar2)
215
216 IS
217
218 itemtype varchar2(8);
219 workflowProcess varchar2(11);
220 DelayDaysToDist number;
221 script varchar2(500);
222 aw_name varchar2(20);
223
224 BEGIN
225
226 workflowProcess := 'SOPAUTOMATE';
227
228 MSD_SOP.Set_Master_Attributes(errbuf, retcode,
229 PlanName => PlanName,
230 Days_tocollect => NumDays_to_collect,
231 Days_delayUpld => NumDays_to_delayUpld,
232 ProcessName => workflowProcess);
233
234 -- set attributes for SOP process
235 wf_engine.SetItemAttrNumber(Itemtype => gItemType,
236 Itemkey => gItemKey,
237 aname => 'DELAYDAYSDIST',
238 avalue => to_number(NumDays_to_delayDist)
239 );
240
241 wf_engine.SetItemAttrNumber(Itemtype => gItemType,
242 Itemkey => gItemKey,
243 aname => 'LAUNCHSNPSHT',
244 avalue => 3);
245
246 wf_engine.SetItemAttrNumber(Itemtype => gItemType,
247 Itemkey => gItemKey,
248 aname => 'LAUNCHPLANNER',
249 avalue => 1);
250
251 wf_engine.SetItemAttrText(Itemtype => gItemType,
252 Itemkey => gItemKey,
253 aname => 'MASTER_TYPE',
254 avalue => 'SOP');
255
256 wf_engine.SetItemAttrText(Itemtype => gItemType,
257 Itemkey => gItemKey,
258 aname => 'SHOW_LOG_TITLE',
259 avalue => 'Y');
260 commit;
261
262 -- set required variables in ODPCODE
263 script := 'aw attach ODPCODE ro; ';
264
265 aw_name := 'M' || 'SD.MSD' || gPlanID;
266
267 script := script || 'call odpwf.setMaster(''' || aw_name || ''');' ;
268 begin
269
270 msd_common_utilities.dbms_aw_interp_silent(script);
271
272 exception
273 when others then
274 errbuf:=substr(sqlerrm, 1, 255);
275
276 if instr(upper(SQLERRM), 'EXPRESS') > 0 or instr(upper(SQLERRM), 'SNAPI') > 0
277 then
278 wf_engine.SetItemAttrText(Itemtype => gItemType,
279 Itemkey => gItemKey,
280 aname => 'DPPROBLEM',
281 avalue => substr(SQLERRM, 1, 200));
282
283 commit;
284 return;
285 end if;
286
287 end;
288
289 -- Now when all is created and set, start the process!
290 wf_engine.StartProcess(ItemType => gItemType,
291 ItemKey => gItemKey);
292 commit;
293
294 -- Start background engine for this process.
295 MSD_WF.StartConcProc('ODPCYCLE', gItemKey);
296 commit;
297
298 return;
299
300 exception
301 when others then
302 retcode :='2';
303 errbuf:=substr(sqlerrm, 1, 255);
304 raise;
305
306
307 end Start_SOP_Master;
308
309 --
310 --
311
312 -- ========================================================================
313 -- Launch_ASCP_Plan
314 --
315 -- Procedure which is called by Recalculate Supply Plans activity(MSDRECALCSUP)
316 -- to launch ASCP Snapshot and Planner program (msc_launch_plan_pk.msc_launch_plan).
317 -- MSC_LAUNCH_PLAN program can be run in two different modes,
318 -- which are defined by values of attributes LAUNCHSNPSHT and LAUNCHPLANNER.
319 --
320 -- IN
321 -- itemtype, itemkey, actid, funcmode
322 -- OUT
323 -- resultout is Y/N.
324 --
325 --
326
327 procedure Launch_ASCP_Plan(
328 itemtype in varchar2,
329 itemkey in varchar2,
330 actid in number,
331 funcmode in varchar2,
332 resultout out NOCOPY varchar2)
333
334 IS
335 launch_snapshot number;
336 launch_planner number;
337 script varchar2(4000);
338 errbuf varchar2(1000);
339 planID varchar2(50);
340
341 v_status number;
342 v_request number;
343
344 TYPE NumTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
345 requests NumTable;
346 plans NumTable;
347 v_cur number;
348
349 -- get supply plans that are attached to a demand plan
350 cursor c_plans(dp_id number) IS
351 select SUPPLY_PLAN_ID,SUPPLY_PLAN_NAME
352 from msd_dp_scenarios
353 where demand_plan_id = dp_id
354 and supply_plan_id is not null;
355
356 BEGIN
357
358 IF (funcmode = 'RUN') THEN
359 resultout :='COMPLETE:N';
360 end if;
361
362 -- get attribute values
363 launch_snapshot := nvl(wf_engine.GetItemAttrNumber(
364 Itemtype => ItemType,
365 Itemkey => ItemKey,
366 aname => 'LAUNCHSNPSHT'),3);
367
368 launch_planner := nvl(wf_engine.GetItemAttrNumber(
369 Itemtype => ItemType,
370 Itemkey => ItemKey,
371 aname => 'LAUNCHPLANNER'),1);
372
373 planID := wf_engine.GetItemAttrText(
374 Itemtype => ItemType,
375 Itemkey => ItemKey,
376 aname => 'ODPPLAN');
377
378 msd_common_utilities.dp_log(planID, 'Launching supply plans','BEGIN');
379
380 -- populate denorm tables
381 msd_common_utilities.dp_log(planID, 'Populating denormalized tables');
382 MSD_ASCP_FLOW.populate_denorm_tables(planID);
383
384 v_cur := 0;
385 FOR plan_rec in c_plans(planID) LOOP
386 v_cur := v_cur+1;
387 v_request := fnd_request.submit_request('MSC', 'MSCSLPPR5',null,null,null,
388 plan_rec.supply_plan_name,
389 plan_rec.supply_plan_id,
390 3,1,2,sysdate);
391 commit;
392 requests(v_cur) := v_request;
393 plans(v_cur) := plan_rec.supply_plan_id;
394 msd_common_utilities.dp_log(planID, plan_rec.supply_plan_name||' (request id '||v_request||')');
395 END LOOP;
396
397 --wait until all the requests are done
398 FOR i in 1..v_cur LOOP
399 msd_common_utilities.dp_log(planID, 'Waiting for request id '||v_request);
400
401 -- wait for this plan run to complete
402 MSC_LAUNCH_PLAN_PK.MSC_CHECK_PLAN_COMPLETION(requests(i),plans(i),v_status);
403
404 msd_common_utilities.dp_log(planID, 'Request id '||v_request||' completed with status '||v_status);
405 end loop;
406
407 msd_common_utilities.dp_log(planID, 'Done launching supply plans.','END');
408
409 resultout := 'COMPLETE:Y';
410
411 return;
412
413 exception
414 when others then
415 resultout := 'COMPLETE:N';
416 errbuf:=substr(sqlerrm, 1, 255);
417 raise;
418
419 end Launch_ASCP_Plan;
420 --================================================================================
421
422 end MSD_SOP;