1 PACKAGE BODY MSC_WS_APS_DATA_SERVICES AS
2 /* $Header: MSCWDATB.pls 120.10 2008/03/27 20:02:22 mtsui noship $ */
3
4 g_UserId NUMBER; -- used by UPLOAD_XXX
5 g_DemandId NUMBER; -- Cache the current demand id, used by UPLOAD_FORECAST
6
7 g_DummyDemandPlanId NUMBER;
8 g_DummyDemandPlanName VARCHAR2(30);
9
10 -- Global variable for un-handled exceptions
11 g_ErrorCode VARCHAR2(30);
12
13
14
15 -- =============================================================
16 -- Un-handled exceptions generate error tokens in the
17 -- format of ERROR_UNEXPECTED_#####.
18 --
19 -- The possible values are:
20 -- 02001 - UPLOAD_FORECAST/GetScenarioId
21 -- 02002 - UPLOAD_FORECAST/ValidateBucketType/CreateNewScenario
22 -- 02003 - UPLOAD_FORECAST/ValidateBucketType
23 -- 02004 - UPLOAD_FORECAST/PurgeAllFcstData
24 -- 02005 - UPLOAD_FORECAST/InsertOutputLevels
25 -- 02006 - UPLOAD_FORECAST/ValidateOutputLevels
26 -- 02007 - UPLOAD_FORECAST/ProcessForecast/ValidateStartEndDates
27 -- 02008 - UPLOAD_FORECAST/ProcessForecast/ValidateSrData/ sr instance id
28 -- 02009 - UPLOAD_FORECAST/ProcessForecast/ValidateSrData/ local forecast
29 -- 02010 - UPLOAD_FORECAST/ProcessForecast/ValidateItemData
30 -- 02011 - UPLOAD_FORECAST/ProcessForecast/ValidateCustomerData
31 -- 02012 - UPLOAD_FORECAST/ProcessForecast/ValidateUomCode
32 -- 02013 - UPLOAD_FORECAST/ProcessForecast/ValidateDemandClass
33 -- 02014 - UPLOAD_FORECAST/ProcessForecast/InsertForecast/GenerateDemandId
34 -- 02015 - UPLOAD_FORECAST/ProcessForecast/InsertForecast
35 -- 02016 - UPLOAD_FORECAST/Insert dummy record
36 -- 02020 - DOWNLOAD_FORECAST/GetDemandPlanId
37 -- 02021 - DOWNLOAD_FORECAST
38 -- 02031 - UPLOAD_SAFETY_STOCKS/GetPlanId
39 -- 02032 - UPLOAD_SAFETY_STOCKS/ProcessSafetyStock/ organization id
40 -- 02033 - UPLOAD_SAFETY_STOCKS/ProcessSafetyStock/ item id
41 -- 02034 - UPLOAD_SAFETY_STOCKS/ purge all safety stocks
42 -- 02035 - UPLOAD_SAFETY_STOCKS/ProcessSafetyStock/ValidatePeriodStartDate
43 -- 02036 - UPLOAD_SAFETY_STOCKS/ProcessSafetyStock/ValidateProjectDate
44 -- 02037 - UPLOAD_SAFETY_STOCKS/ProcessSafetyStock/InsertSafetyStock
45 -- 02038 - UPLOAD_SAFETY_STOCKS/ProcessSafetyStock/UpdateSafetyStock
46 -- 02040 - DOWNLOAD_SAFETY_STOCKS
47 -- 02050 - UPLOAD_PLANNED_SUPPLY/ValidatePlanType
48 -- 02051 - UPLOAD_PLANNED_SUPPLY/ purge all firmed plan orders
49 -- 02052 - UPLOAD_PLANNED_SUPPLY/ProcessPlannedSupply/ organization id
50 -- 02053 - UPLOAD_PLANNED_SUPPLY/ProcessPlannedSupply/ item id
51 -- 02054 - UPLOAD_PLANNED_SUPPLY/ProcessPlannedSupply/InsertPlannedSupply
52
53 -- 02098 - UPLOAD_PLANNED_SUPPLY/ check msc_plans.plan_completion_date
54 -- 02099 - UPLOAD_SAFETY_STOCKS/ check msc_plans.plan_completion_date
55 -- =============================================================
56
57
58
59 -- =============================================================
60 --
61 -- Private helper functions for MSC_WS_APS_DATA_SERVICES.
62 --
63 -- =============================================================
64
65 -- =============================================================
66 --
67 -- Private helper functions for Demand Forecast.
68 --
69 -- =============================================================
70 FUNCTION GetScenarioId(ScenarioId OUT NOCOPY NUMBER, DemandPlanId IN NUMBER, ScenarioName IN VARCHAR2) RETURN VARCHAR2;
71
72 -- Private helper functions used by UPLOAD_FORECAST only.
73 PROCEDURE CreateNewScenario(ScenarioId OUT NOCOPY NUMBER, ScenarioName IN VARCHAR2);
74 PROCEDURE MakeOutputLevelSet(OutputLevels OUT NOCOPY MscNumberArr, ItemOutputLevel IN VARCHAR2, OrganizationOutputLevel IN VARCHAR2, CustomerOutputLevel IN VARCHAR2, DemandClassOutputLevel IN VARCHAR2);
75 PROCEDURE PurgeAllFcstData(ScenarioId IN NUMBER);
76 PROCEDURE InsertOutputLevels(ScenarioId IN NUMBER, ItemOutputLevel IN VARCHAR2, OrganizationOutputLevel IN VARCHAR2, CustomerOutputLevel IN VARCHAR2, DemandClassOutputLevel IN VARCHAR2);
77 FUNCTION ValidateOutputLevels(ScenarioId IN NUMBER, ItemOutputLevel IN VARCHAR2, OrganizationOutputLevel IN VARCHAR2, CustomerOutputLevel IN VARCHAR2, DemandClassOutputLevel IN VARCHAR2) RETURN VARCHAR2;
78 FUNCTION ValidateBucketType(BucketType IN NUMBER) RETURN VARCHAR2;
79 FUNCTION ValidateStartEndDates(BucketType IN NUMBER, StartDate IN DATE, EndDate IN DATE) RETURN VARCHAR2;
80 FUNCTION ValidateSrData(NewOrgId OUT NOCOPY NUMBER, ValidationOrgId OUT NOCOPY NUMBER, OutputToOrganization IN VARCHAR2, SrInstanceId IN NUMBER, SrOrgId IN NUMBER) RETURN VARCHAR2;
81 FUNCTION ValidateErrorData(ErrorType IN VARCHAR2, ForecastError IN NUMBER) RETURN VARCHAR2;
82 FUNCTION ValidateItemData(SrItemId OUT NOCOPY NUMBER, OutputToItem IN VARCHAR2, SrInstanceId IN NUMBER, OrgId IN NUMBER, ItemId IN NUMBER) RETURN VARCHAR2;
83 FUNCTION ValidateCustomerData(OutputToCustomer IN VARCHAR2, SrInstanceId IN NUMBER, ShipToLocation IN NUMBER, SrCustomerId IN NUMBER, SrZoneId IN NUMBER) RETURN VARCHAR2;
84 FUNCTION ValidateUomCode(SrInstanceId IN NUMBER, OrgId IN NUMBER, ItemId IN NUMBER, UomCode IN VARCHAR2) RETURN VARCHAR2;
85 FUNCTION ValidateDemandClass(OutputToDemandClass IN VARCHAR2, SrInstanceId IN NUMBER, DemandClass IN VARCHAR2) RETURN VARCHAR2;
86 PROCEDURE GenerateDemandId(ScenarioId IN NUMBER);
87 PROCEDURE InsertForecast(ScenarioId IN NUMBER, ForecastData IN MscForecastRec, OrganizationId IN NUMBER, SrItemId IN NUMBER);
88 FUNCTION ProcessForecast(ScenarioId IN NUMBER, OutputToItem IN VARCHAR2, OutputToOrganization IN VARCHAR2, OutputToCustomer IN VARCHAR2, OutputToDemandClass IN VARCHAR2, ForecastData IN MscForecastRec) RETURN VARCHAR2;
89
90 -- Private helper functions used by DOWNLOAD_FORECAST and DOWNLOAD_SAFETY_STOCK.
91 FUNCTION GetDemandPlanId(DemandPlanId OUT NOCOPY NUMBER, DemandPlanName IN VARCHAR2) RETURN VARCHAR2;
92 FUNCTION MakeSubClause(Clause OUT NOCOPY VARCHAR2, ColumnName IN VARCHAR2, IdList IN MscNumberArr) RETURN VARCHAR2;
93
94 -- Private helper functions used by DOWNLOAD_FORECAST only.
95 FUNCTION MakeSubClause(Clause OUT NOCOPY VARCHAR2, ColumnName1 IN VARCHAR2, ColumnName2 IN VARCHAR2, IdPairList IN MscCustZoneTbl) RETURN VARCHAR2;
96 FUNCTION MakeSubClause(Clause OUT NOCOPY VARCHAR2, ColumnName IN VARCHAR2, IdList IN MscChar255Arr) RETURN VARCHAR2;
97 FUNCTION GetItemClause(Clause OUT NOCOPY VARCHAR2, ItemIdList IN MscNumberArr, ProductFamilyIdList IN MscNumberArr) RETURN VARCHAR2;
98 FUNCTION GetCustomerClause(Clause OUT NOCOPY VARCHAR2, ShipToLocIdList IN MscNumberArr, CustomerIdList IN MscNumberArr, CustZonePairList IN MscCustZoneTbl, ZoneIdList IN MscNumberArr) RETURN VARCHAR2;
99 FUNCTION GetStartEndDateClause(Clause OUT NOCOPY VARCHAR2, StartDate IN DATE, EndDate IN DATE)RETURN VARCHAR2;
100 PROCEDURE QueryForecasts(ForecastTbl OUT NOCOPY MscForecastTbl, WhereClause IN VARCHAR2);
101
102 -- =============================================================
103 --
104 -- Private helper functions for Safety Stock.
105 --
106 -- =============================================================
107 -- Private helper functions used by UPLOAD_SAFETY_STOCKS and UPLOAD_PLANNED_SUPPLIES
108 FUNCTION GetPlanId(PlanId OUT NOCOPY NUMBER, PlanName IN VARCHAR2, OwningOrgId IN NUMBER, SrInstanceId IN NUMBER) RETURN VARCHAR2;
109 FUNCTION ValidateOrgId(PlanId IN NUMBER, OrgId IN NUMBER, SrInstId IN NUMBER) RETURN VARCHAR2;
110 FUNCTION ValidateItemId(PlanId IN NUMBER, SrInstId IN NUMBER, OrgId IN NUMBER, ItemId IN NUMBER) RETURN VARCHAR2;
111
112 -- Private helper functions used by UPLOAD_SAFETY_STOCKS only.
113 FUNCTION ValidatePeriodStartDate(PlanId IN NUMBER, OrgId IN NUMBER, SrInstId IN NUMBER, PeriodStartDate IN DATE) RETURN VARCHAR2;
114 FUNCTION ValidateProjectDate(PlanId IN NUMBER, OrgId IN NUMBER, SrInstId IN NUMBER, ProjectId IN NUMBER, TaskId IN NUMBER, PlanningGroup IN VARCHAR2) RETURN VARCHAR2;
115 PROCEDURE InsertSafetyStock(PlanId IN NUMBER, SafetyStockData IN MscSafetyStockRec);
116 PROCEDURE UpdateSafetyStock(PlanId IN NUMBER, SafetyStockData IN MscSafetyStockRec);
117 FUNCTION ProcessSafetyStock(PlanId IN NUMBER, OwningOrgId IN NUMBER, SrInstId IN NUMBER, SafetyStockData IN MscSafetyStockRec) RETURN VARCHAR2;
118
119 -- Private helper functions used by DOWNLOAD_SAFETY_STOCKS only.
120 PROCEDURE QuerySafetyStocks(SafetyStockTbl OUT NOCOPY MscSafetyStockTbl, WhereClause IN VARCHAR2);
121
122 -- Private helper functions used by UPLOAD_PLANNED_SUPPLIES only.
123 FUNCTION ValidatePlanType(PlanId IN NUMBER) RETURN VARCHAR2;
124 PROCEDURE InsertPlannedSupply(PlanId IN NUMBER, SrInstId IN NUMBER, PlannedSupplyData IN MscPlannedSupplyRec);
125 FUNCTION ProcessPlannedSupply(PlanId IN NUMBER, SrInstanceId IN NUMBER, PlannedSupplyData IN MscPlannedSupplyRec) RETURN VARCHAR2;
126
127 -- =============================================================
128 -- Desc: Create a new scenario.
129 --
130 -- Input:
131 -- ScenarioName Scenario name.
132 --
133 -- Output: No output.
134 -- =============================================================
135 PROCEDURE CreateNewScenario(
136 ScenarioId OUT NOCOPY NUMBER,
137 ScenarioName IN VARCHAR2
138 ) AS
139 BEGIN
140 BEGIN
141 SELECT MSD_DP_SCENARIOS_S.NEXTVAL INTO ScenarioId FROM DUAL;
142
143 INSERT INTO msd_dp_scenarios
144 (
145 demand_plan_id, scenario_id, scenario_name, forecast_based_on,
146 last_update_date, last_updated_by, creation_date, created_by
147 )
148 VALUES
149 (
150 g_DummyDemandPlanId, ScenarioId, ScenarioName, 'APS_DATA_SERVICE',
151 sysdate, g_UserId, sysdate, g_UserId
152 );
153 END;
154
155 EXCEPTION
156 WHEN others THEN
157 g_ErrorCode := 'ERROR_UNEXPECTED_02002';
158 raise;
159 END CreateNewScenario;
160
161 -- =============================================================
162 -- Desc: Get demand plan id.
163 --
164 -- Input:
165 -- DemandPlanName Demand plan name.
166 --
167 -- Output: The possible return statuses are:
168 -- OK
169 -- INVALID_DEMAND_PLAN_NAME
170 -- DUPLICATE_DEMAND_PLAN_NAME
171 -- =============================================================
172 FUNCTION GetDemandPlanId(
173 DemandPlanId OUT NOCOPY NUMBER,
174 DemandPlanName IN VARCHAR2
175 ) RETURN VARCHAR2 AS
176 BEGIN
177 BEGIN
178 SELECT distinct demand_plan_id INTO DemandPlanId
179 FROM msd_dp_ascp_scenarios_v
180 WHERE demand_plan_name = DemandPlanName;
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 RETURN 'INVALID_DEMAND_PLAN_NAME';
184 WHEN TOO_MANY_ROWS THEN
185 RETURN 'DUPLICATE_DEMAND_PLAN_NAME';
186 WHEN others THEN
187 g_ErrorCode := 'ERROR_UNEXPECTED_02020';
188 raise;
189 END;
190 RETURN 'OK';
191 END GetDemandPlanId;
192
193 -- =============================================================
194 -- Desc: Get scenario id.
195 --
196 -- Input:
197 -- DemandPlanId
198 -- ScenarioName Scenario name.
199 --
200 -- Output: The possible return statuses are:
201 -- OK
202 -- INVALID_SCENARIO_NAME
203 -- DUPLICATE_SCENARIO_NAME
204 -- =============================================================
205 FUNCTION GetScenarioId(
206 ScenarioId OUT NOCOPY NUMBER,
207 DemandPlanId IN NUMBER,
208 ScenarioName IN VARCHAR2
209 ) RETURN VARCHAR2 AS
210 BEGIN
211 BEGIN
212 SELECT scenario_id
213 INTO ScenarioId
214 FROM msd_dp_scenarios
215 WHERE
216 demand_plan_id = DemandPlanId AND
217 scenario_name = ScenarioName;
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 ScenarioId := -1;
221 RETURN 'INVALID_SCENARIO_NAME';
222 WHEN TOO_MANY_ROWS THEN
223 ScenarioId := -1;
224 RETURN 'DUPLICATE_SCENARIO_NAME';
225 WHEN others THEN
226 ScenarioId := -1;
227 g_ErrorCode := 'ERROR_UNEXPECTED_02001';
228 raise;
229 END;
230 RETURN 'OK';
231 END GetScenarioId;
232
233 -- =============================================================
234 -- Desc: Build the output level sets
235 --
236 -- Input:
237 -- ItemOutputLevel Item output level, either ITEM or PRODUCT_FAMILY.
238 -- OrganizationOutputLevel
239 -- Organization output level, either Y or N.
240 -- CustomerOutputLevel
241 -- Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
242 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
243 -- DemandClassOutputLevel
244 -- Demand class output level, either Y or N.
245 --
246 -- Output: No output.
247 -- =============================================================
248 PROCEDURE MakeOutputLevelSet(
249 OutputLevels OUT NOCOPY MscNumberArr,
250 ItemOutputLevel IN VARCHAR2,
251 OrganizationOutputLevel
252 IN VARCHAR2,
253 CustomerOutputLevel IN VARCHAR2,
254 DemandClassOutputLevel
255 IN VARCHAR2
256 ) AS
257 BEGIN
258 -- all the output levels are restricted by xsd
259 OutputLevels := MscNumberArr(1);
260 IF ItemOutputLevel = 'PRODUCT_FAMILY' THEN
261 OutputLevels(1) := 3;
262 END IF;
263 IF OrganizationOutputLevel = 'Y' THEN
264 OutputLevels.extend;
265 OutputLevels(OutputLevels.COUNT) := 7;
266 END IF;
267 IF CustomerOutputLevel <> 'NONE' THEN
268 OutputLevels.extend;
269 IF CustomerOutputLevel = 'CUSTOMER_SHIP_TO_SITE' THEN
270 OutputLevels(OutputLevels.COUNT) := 11;
271 ELSIF CustomerOutputLevel = 'CUSTOMER' THEN
272 OutputLevels(OutputLevels.COUNT) := 15;
273 ELSIF CustomerOutputLevel = 'CUSTOMER_ZONE' THEN
274 OutputLevels(OutputLevels.COUNT) := 41;
275 ELSIF CustomerOutputLevel = 'ZONE' THEN
276 OutputLevels(OutputLevels.COUNT) := 42;
277 END IF;
278 END IF;
279 IF DemandClassOutputLevel = 'Y' THEN
280 OutputLevels.extend;
281 OutputLevels(OutputLevels.COUNT) := 34;
282 END IF;
283 END MakeOutputLevelSet;
284
285 -- =============================================================
286 -- Desc: Purge forecast data in msd_dp_scn_entries_denorm
287 -- purge output levels in msd_dp_scenario_output_levels
288 -- insert new output levels in msd_dp_scenario_output_levels
289 --
290 -- Input:
291 -- ScenarioId Scenario id.
292 --
293 -- Output: No output.
294 -- =============================================================
295 PROCEDURE PurgeAllFcstData(ScenarioId IN NUMBER) AS
296 BEGIN
297 BEGIN
298 -- purge all forecast
299 DELETE FROM msd_dp_scn_entries_denorm
300 WHERE scenario_id = ScenarioId;
301
302 -- purge all output levels
303 DELETE FROM msd_dp_scenario_output_levels
304 WHERE
305 demand_plan_id = g_DummyDemandPlanId AND
306 scenario_id = ScenarioId;
307 END;
308
309 EXCEPTION WHEN others THEN
310 g_ErrorCode := 'ERROR_UNEXPECTED_02004';
311 raise;
312
313 END PurgeAllFcstData;
314
315 -- =============================================================
316 -- Desc: insert output levels in msd_dp_scenario_output_levels
317 --
318 -- Input:
319 -- ScenarioId Scenario id.
320 -- ItemOutputLevel Item output level, either ITEM or PRODUCT_FAMILY.
321 -- OrganizationOutputLevel
322 -- Organization output level, either Y or N.
323 -- CustomerOutputLevel
324 -- Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
325 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
326 -- DemandClassOutputLevel
327 -- Demand class output level, either Y or N.
328 --
329 -- Output: No output.
330 -- =============================================================
331 PROCEDURE InsertOutputLevels(
332 ScenarioId IN NUMBER,
336 CustomerOutputLevel
333 ItemOutputLevel IN VARCHAR2,
334 OrganizationOutputLevel
335 IN VARCHAR2,
337 IN VARCHAR2,
338 DemandClassOutputLevel
339 IN VARCHAR2
340 ) AS
341 l_OutputLevels MscNumberArr;
342 BEGIN
343 l_OutputLevels := MscNumberArr();
344 MakeOutputLevelSet(l_OutputLevels, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);
345 FOR I IN l_OutputLevels.first..l_OutputLevels.last
346 LOOP
347 INSERT INTO msd_dp_scenario_output_levels
348 (
349 demand_plan_id, scenario_id, level_id,
350 last_update_date, last_updated_by, creation_date, created_by
351 )
352 VALUES
353 (
354 g_DummyDemandPlanId, ScenarioId, l_OutputLevels(I),
355 sysdate, g_UserId, sysdate, g_UserId
356 );
357 END LOOP;
358
359 EXCEPTION WHEN others THEN
360 g_ErrorCode := 'ERROR_UNEXPECTED_02005';
361 raise;
362 END InsertOutputLevels;
363
364 -- =============================================================
365 -- Desc: check consistency of output level parameters against
366 -- those outpul levels in msd_dp_scenario_output_levels
367 --
368 -- Input:
369 -- ItemOutputLevel Item output level, either ITEM or PRODUCT_FAMILY.
370 -- OrganizationOutputLevel
371 -- Organization output level, either Y or N.
372 -- CustomerOutputLevel
373 -- Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
374 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
375 -- DemandClassOutputLevel
376 -- Demand class output level, either Y or N.
377 --
378 -- Output: The possible return statuses are:
379 -- OK
380 -- INCONSIST_OUTPUT_LEVELS
381 -- =============================================================
382 FUNCTION ValidateOutputLevels(
383 ScenarioId IN NUMBER,
384 ItemOutputLevel IN VARCHAR2,
385 OrganizationOutputLevel
386 IN VARCHAR2,
387 CustomerOutputLevel
388 IN VARCHAR2,
389 DemandClassOutputLevel
390 IN VARCHAR2
391 ) RETURN VARCHAR2 AS
392 l_OutputLevels MscNumberArr;
393 l_Count NUMBER;
394 BEGIN
395 l_OutputLevels := MscNumberArr();
396 MakeOutputLevelSet(l_OutputLevels, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);
397
398 BEGIN
399 -- check number of output levels
400 SELECT count(*) INTO l_Count FROM msd_dp_scenario_output_levels
401 WHERE demand_plan_id = g_DummyDemandPlanId AND scenario_id = ScenarioId;
402 IF l_Count <> 0 THEN -- Don't need to check if this is a new scenario
403 IF l_Count <> l_OutputLevels.COUNT THEN
404 RETURN 'INCONSIST_OUTPUT_LEVELS';
405 END IF;
406 FOR I IN l_OutputLevels.first..l_OutputLevels.last
407 LOOP
408 BEGIN
409 SELECT 1 INTO l_Count
410 FROM msd_dp_scenario_output_levels
411 WHERE
412 demand_plan_id = g_DummyDemandPlanId AND
413 scenario_id = ScenarioId AND
414 level_id = l_OutputLevels(I);
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 RETURN 'INCONSIST_OUTPUT_LEVELS';
418 END;
419 END LOOP;
420 END IF;
421 END;
422
423 RETURN 'OK';
424 EXCEPTION WHEN others THEN
425 g_ErrorCode := 'ERROR_UNEXPECTED_02006';
426 raise;
427 END ValidateOutputLevels;
428
429 -- =============================================================
430 -- Desc: Validate bucket type.
431 --
432 -- Input:
433 -- BucketType Bucket Type.
434 --
435 -- Output: The possible return statuses are:
436 -- OK
437 -- INVALID_BUCKET_TYPE
438 -- =============================================================
439 FUNCTION ValidateBucketType( BucketType IN NUMBER ) RETURN VARCHAR2 AS
440 l_Count NUMBER;
441 BEGIN
442 -- 'MSC_X_BUCKET_TYPE' is not in fnd_lookups
443 IF BucketType IS NULL THEN
444 RETURN 'INVALID_BUCKET_TYPE';
445 END IF;
446 BEGIN
447 SELECT count(*) INTO l_Count
448 FROM fnd_lookup_values
449 WHERE lookup_type = 'MSC_X_BUCKET_TYPE' AND lookup_code = BucketType;
450 EXCEPTION WHEN others THEN
451 g_ErrorCode := 'ERROR_UNEXPECTED_02003';
452 raise;
453 END;
454 IF l_Count = 0 THEN
455 RETURN 'INVALID_BUCKET_TYPE';
456 END IF;
457 RETURN 'OK';
458 END ValidateBucketType;
459
460 -- =============================================================
461 -- Desc: Validate start date and end date.
462 --
463 -- Input:
464 -- BucketType Bucket Type.
465 -- StartDate Start date.
469 -- OK
466 -- EndDate End date.
467 --
468 -- Output: The possible return statuses are:
470 -- INVALID_START_END_DATE_FOR_DAILY_BUCKET
471 -- INVALID_START_END_DATE_FOR_WEEkLY_BUCKET
472 -- INVALID_START_END_DATE_FOR_MONTHLY_BUCKET
473 -- =============================================================
474 FUNCTION ValidateStartEndDates(
475 BucketType IN NUMBER,
476 StartDate IN DATE,
477 EndDate IN DATE
478 ) RETURN VARCHAR2 AS
479 l_Date DATE;
480 BEGIN
481 IF BucketType = 1 THEN -- daily bucket
482 IF StartDate <> EndDate THEN
483 RETURN 'INVALID_START_END_DATE_FOR_DAILY_BUCKET';
484 END IF;
485 ELSIF BucketType = 2 THEN -- weekly bucket
486 -- l_Date := StartDate + 6;
487 -- SELECT StartDate + 6 INTO l_Date FROM DUAL;
488 IF StartDate + 6 <> EndDate THEN
489 -- IF l_Date <> EndDate THEN
490 RETURN 'INVALID_START_END_DATE_FOR_WEEKLY_BUCKET';
491 END IF;
492 ELSE -- monthly bucket can be 5/5 weeks or Gregorian calendar month
493 -- start with 4 weeks
494 -- SELECT StartDate + 27 INTO l_Date FROM DUAL; -- 4 weeks
495 -- IF l_Date <> EndDate THEN
496 IF StartDate + 27 <> EndDate THEN
497 -- its not 4 weeks period, try 5 weeks
498 -- SELECT StartDate + 34 INTO l_Date FROM DUAL; -- 5 weeks
499 -- IF l_Date <> EndDate THEN
500 IF StartDate + 34 <> EndDate THEN
501 -- its not 5 weeks, try Gregorian calendar month
502 SELECT TRUNC(StartDate, 'MONTH') INTO l_Date FROM DUAL;
503 -- check if StartDate is the first day of the month
504 IF StartDate <> l_Date THEN
505 RETURN 'INVALID_START_END_DATE_FOR_MONTHLY_BUCKET';
506 ELSE
507 SELECT LAST_DAY(StartDate) INTO l_Date FROM DUAL;
508 -- check if EndDate is the last day of the month
509 IF EndDate <> l_Date THEN
510 RETURN 'INVALID_START_END_DATE_FOR_MONTHLY_BUCKET';
511 END IF;
512 END IF; -- check for Gregorian calendar month
513 END IF; -- check for 5 weeks period
514 END IF; -- check for 4 weeks period
515 END IF;
516
517 RETURN 'OK';
518
519 EXCEPTION WHEN others THEN
520 g_ErrorCode := 'ERROR_UNEXPECTED_02007';
521 raise;
522
523 END ValidateStartEndDates;
524
525 -- =============================================================
526 -- Desc: Validate SrOrganizationId, SrInstanceId, get the
527 -- validation organization id for global forecast as well
528 --
529 -- Input:
530 -- OutputToOrganization
531 -- Organization output level, either Y or N.
532 -- SrOrgId Source organization id.
533 -- SrInstanceId Source instance id.
534 --
535 -- Output: The possible return statuses are:
536 -- OK
537 -- INVALID_SR_INSTANCE_ID
538 -- INVALID_SR_ORGANIZATION_ID
539 -- MISSING_VALIDATION_ORG_ID
540 -- =============================================================
541 FUNCTION ValidateSrData(
542 NewOrgId OUT NOCOPY NUMBER,
543 ValidationOrgId OUT NOCOPY NUMBER,
544 OutputToOrganization IN VARCHAR2,
545 SrInstanceId IN NUMBER,
546 SrOrgId IN NUMBER
547 ) RETURN VARCHAR2 AS
548 l_ValidationOrgId NUMBER;
549 l_Dummy NUMBER;
550 BEGIN
551 -- validate sr instance id
552 BEGIN
553 SELECT validation_org_id INTO l_ValidationOrgId
554 FROM msc_apps_instances
555 WHERE instance_id = SrInstanceId;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 RETURN 'INVALID_SR_INSTANCE_ID';
559 WHEN others THEN
560 g_ErrorCode := 'ERROR_UNEXPECTED_02008';
561 raise;
562 END;
563 -- validate sr org id, set the validation org id as well
564 IF OutputToOrganization = 'Y' THEN
565 BEGIN
566 SELECT 1 INTO l_Dummy
567 FROM msc_trading_partners
568 WHERE
569 partner_type = 3 AND
570 sr_instance_id = SrInstanceId AND
571 sr_tp_id = SrOrgId;
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN
574 RETURN 'INVALID_SR_ORGANIZATION_ID';
575 WHEN others THEN
576 g_ErrorCode := 'ERROR_UNEXPECTED_02009';
577 raise;
578 END;
579 NewOrgId := SrOrgId;
580 ValidationOrgId := SrOrgId;
581 ELSE
582 IF SrOrgId IS NOT NULL THEN
583 RETURN 'INVALID_SR_ORGANIZATION_ID';
584 ELSE
585 NewOrgId := -1;
586 IF l_ValidationOrgId IS NULL THEN
587 RETURN 'MISSING_VALIDATION_ORG_ID';
588 ELSE
589 ValidationOrgId := l_ValidationOrgId;
590 END IF;
591 END IF;
592 END IF;
593
594 RETURN 'OK';
598 -- Desc: Validate ErrorType and ForecastError.
595 END ValidateSrData;
596
597 -- =============================================================
599 --
600 -- Input:
601 -- ErrorType Error type.
602 -- ForecastError Forecast error.
603 --
604 -- Output: The possible return statuses are:
605 -- OK
606 -- INVALID_ERROR_TYPE
607 -- INVALID_FORECAST_ERROR
608 -- =============================================================
609 FUNCTION ValidateErrorData(
610 ErrorType IN VARCHAR2,
611 ForecastError IN NUMBER
612 ) RETURN VARCHAR2 AS
613 l_Dummy NUMBER;
614 BEGIN
615 -- ErrorType can be NULL, MAD or MAPE
616 IF ErrorType IS NOT NULL AND ErrorType <> 'MAD' AND ErrorType <> 'MAPE' THEN
617 RETURN 'INVALID_ERROR_TYPE';
618 END IF;
619
620 -- if ErrorType is null, ForecastError has to be null
621 -- if ErrorType is MAD, ForecastError can be null, or greater or equal to zero
622 -- if ErrorType is MAPE, ForecastError can be null, or between 0 to 100
623 IF ErrorType IS NULL THEN
624 IF ForecastError IS NOT NULL THEN
625 RETURN 'INVALID_FORECAST_ERROR';
626 END IF;
627 ELSIF ErrorType = 'MAD' THEN
628 IF ForecastError IS NOT NULL AND ForecastError < 0 THEN
629 RETURN 'INVALID_FORECAST_ERROR';
630 END IF;
631 ELSE
632 IF ForecastError IS NOT NULL AND ForecastError NOT BETWEEN 0 AND 100 THEN
633 RETURN 'INVALID_FORECAST_ERROR';
634 END IF;
635 END IF;
636
637 RETURN 'OK';
638 END ValidateErrorData;
639
640 -- =============================================================
641 -- Desc: Validate item Id.
642 --
643 -- Input:
644 -- OutputToItem Item output level, either ITEM or PRODUCT_FAMILY.
645 -- SrInstanceId Source instance id.
646 -- OrgId Organization id.
647 -- ItemId Item id.
648 --
649 -- Output: The possible return statuses are:
650 -- OK
651 -- INVALID_ITEM_ID
652 -- INVALID_PRODUCT_FAMILY_ID
653 -- FAILED_TO_QUERY_SR_ITEM_ID
654 -- =============================================================
655 FUNCTION ValidateItemData(
656 SrItemId OUT NOCOPY NUMBER,
657 OutputToItem IN VARCHAR2,
658 SrInstanceId IN NUMBER,
659 OrgId IN NUMBER,
660 ItemId IN NUMBER
661 ) RETURN VARCHAR2 AS
662 l_Count NUMBER;
663 BEGIN
664 IF OutputToItem = 'ITEM' THEN
665 BEGIN
666 SELECT 1 INTO l_Count
667 FROM msc_items
668 WHERE
669 inventory_item_id = ItemId;
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 RETURN 'INVALID_ITEM_ID';
673 END;
674 ELSE
675 SELECT count(*) INTO l_Count
676 FROM msc_system_items
677 WHERE
678 plan_id = -1 and
679 inventory_item_id = ItemId and
680 sr_instance_id = SrInstanceId and
681 organization_id = OrgId and
682 bom_item_type = 5;
683 IF l_Count = 0 THEN
684 RETURN 'INVALID_PRODUCT_FAMILY_ID';
685 END IF;
686 END IF;
687 BEGIN
688 SELECT sr_inventory_item_id INTO SrItemId
689 FROM msc_system_items
690 WHERE
691 plan_id = -1 and
692 inventory_item_id = ItemId and
693 sr_instance_id = SrInstanceId and
694 organization_id = OrgId;
695 EXCEPTION
696 WHEN NO_DATA_FOUND THEN
697 RETURN 'FAILED_TO_QUERY_SR_ITEM_ID';
698 END;
699
700
701 RETURN 'OK';
702 EXCEPTION WHEN others THEN
703 g_ErrorCode := 'ERROR_UNEXPECTED_02010';
704 raise;
705 END ValidateItemData;
706
707 -- =============================================================
708 -- Desc: Validate data related to customer output levels.
709 --
710 -- Input:
711 -- OutputToCustomer Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
712 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
713 -- SrInstanceId Source instance id.
714 -- ShipToLocation Ship to location id.
715 -- SrCustomerId Source customer id.
716 -- SrZoneId Source Zone primary key.
717 --
718 -- Output: The possible return statuses are:
719 -- OK
720 -- INVALID_SHIP_TO_LOCATION_ID
721 -- INVALID_SR_CUSTOMER_ID
722 -- INVALID_SR_ZONE_ID
723 -- =============================================================
724 FUNCTION ValidateCustomerData(
725 OutputToCustomer IN VARCHAR2,
726 SrInstanceId IN NUMBER,
727 ShipToLocation IN NUMBER,
728 SrCustomerId IN NUMBER,
729 SrZoneId IN NUMBER
730 ) RETURN VARCHAR2 AS
731 l_Dummy NUMBER;
732 BEGIN
733 IF OutputToCustomer = 'CUSTOMER_SHIP_TO_SITE' THEN
734 IF ShipToLocation IS NULL THEN
738 ELSIF SrZoneId IS NOT NULL THEN
735 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
736 ELSIF SrCustomerId IS NOT NULL THEN
737 RETURN 'INVALID_SR_CUSTOMER_ID';
739 RETURN 'INVALID_SR_ZONE_ID';
740 END IF;
741 BEGIN
742 SELECT 1 INTO l_Dummy
743 FROM msc_tp_site_id_lid
744 WHERE
745 sr_tp_site_id = ShipToLocation AND
746 partner_type = 2 AND
747 sr_instance_id = SrInstanceId;
748 EXCEPTION
749 WHEN NO_DATA_FOUND THEN
750 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
751 END;
752 ELSIF OutputToCustomer = 'CUSTOMER' THEN
753 IF ShipToLocation IS NOT NULL THEN
754 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
755 ELSIF SrCustomerId IS NULL THEN
756 RETURN 'INVALID_SR_CUSTOMER_ID';
757 ELSIF SrZoneId IS NOT NULL THEN
758 RETURN 'INVALID_SR_ZONE_ID';
759 END IF;
760 BEGIN
761 SELECT 1 INTO l_Dummy
762 FROM msc_tp_id_lid
763 WHERE
764 sr_tp_id = SrCustomerId AND
765 partner_type = 2 AND
766 sr_instance_id = SrInstanceId;
767 EXCEPTION
768 WHEN NO_DATA_FOUND THEN
769 RETURN 'INVALID_SR_CUSTOMER_ID';
770 END;
771 ELSIF OutputToCustomer = 'CUSTOMER_ZONE' THEN
772 IF ShipToLocation IS NOT NULL THEN
773 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
774 ELSIF SrCustomerId IS NULL THEN
775 RETURN 'INVALID_SR_CUSTOMER_ID';
776 ELSIF SrZoneId IS NULL THEN
777 RETURN 'INVALID_SR_ZONE_ID';
778 END IF;
779 BEGIN
780 SELECT 1 INTO l_Dummy
781 FROM msc_tp_id_lid
782 WHERE
783 sr_tp_id = SrCustomerId AND
784 partner_type = 2 AND
785 sr_instance_id = SrInstanceId;
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 RETURN 'INVALID_SR_CUSTOMER_ID';
789 END;
790 BEGIN
791 SELECT 1 INTO l_Dummy
792 FROM msc_regions
793 WHERE
794 region_id = SrZoneId AND
795 sr_instance_id = SrInstanceId;
796 EXCEPTION
797 WHEN NO_DATA_FOUND THEN
798 RETURN 'INVALID_SR_ZONE_ID';
799 END;
800 ELSIF OutputToCustomer = 'ZONE' THEN
801 IF ShipToLocation IS NOT NULL THEN
802 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
803 ELSIF SrCustomerId IS NOT NULL THEN
804 RETURN 'INVALID_SR_CUSTOMER_ID';
805 ELSIF SrZoneId IS NULL THEN
806 RETURN 'INVALID_SR_ZONE_ID';
807 END IF;
808 BEGIN
809 SELECT 1 INTO l_Dummy
810 FROM msc_regions
811 WHERE
812 region_id = SrZoneId AND
813 sr_instance_id = SrInstanceId;
814 EXCEPTION
815 WHEN NO_DATA_FOUND THEN
816 RETURN 'INVALID_SR_ZONE_ID';
817 END;
818 ELSE -- NONE
819 IF ShipToLocation IS NOT NULL THEN
820 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
821 ELSIF SrCustomerId IS NOT NULL THEN
822 RETURN 'INVALID_SR_CUSTOMER_ID';
823 ELSIF SrZoneId IS NOT NULL THEN
824 RETURN 'INVALID_SR_ZONE_ID';
825 END IF;
826 END IF;
827
828 RETURN 'OK';
829
830 EXCEPTION WHEN others THEN
831 g_ErrorCode := 'ERROR_UNEXPECTED_02011';
832 raise;
833 END ValidateCustomerData;
834
835 -- =============================================================
836 -- Desc: Validate UOM code.
837 --
838 -- Input:
839 -- SrInstanceId Source instance id.
840 -- ItemId Item id.
841 -- Uom Unit of meansure code.
842 --
843 -- Output: The possible return statuses are:
844 -- OK
845 -- UOM_REQUIRED_FOR_ITEM: Uom
846 -- =============================================================
847 FUNCTION ValidateUomCode(
848 SrInstanceId IN NUMBER,
849 OrgId IN NUMBER,
850 ItemId IN NUMBER,
851 UomCode IN VARCHAR2
852 ) RETURN VARCHAR2 AS
853 l_UomCode VARCHAR2(10);
854 BEGIN
855 -- validate the dp uom
856 BEGIN
857 SELECT uom_code INTO l_UomCode
858 FROM msc_system_items
859 WHERE
860 plan_id = -1 and
861 sr_instance_id = SrInstanceId and
862 organization_id = OrgId and
863 inventory_item_id = ItemId;
864 EXCEPTION WHEN others THEN
865 g_ErrorCode := 'ERROR_UNEXPECTED_02012';
866 raise;
867 END;
868 IF l_UomCode <> UomCode THEN
869 RETURN 'UOM_REQUIRED_FOR_ITEM: ' || l_UomCode;
870 END IF;
871
872 RETURN 'OK';
873 END ValidateUomCode;
874
878 -- Input:
875 -- =============================================================
876 -- Desc: Validate demand class.
877 --
879 -- OutputToDemandClass
880 -- Demand class output level, either Y or N.
881 -- SrInstanceId Source instance id.
882 -- DemandClass Demand class.
883 --
884 -- Output: The possible return statuses are:
885 -- OK
886 -- INVALID_DEMAND_CLASS
887 -- =============================================================
888 FUNCTION ValidateDemandClass(
889 OutputToDemandClass IN VARCHAR2,
890 SrInstanceId IN NUMBER,
891 DemandClass IN VARCHAR2
892 ) RETURN VARCHAR2 AS
893 l_Dummy NUMBER;
894 BEGIN
895 IF OutputToDemandClass = 'Y' THEN
896 IF DemandClass IS NULL THEN
897 RETURN 'INVALID_DEMAND_CLASS';
898 END IF;
899 BEGIN
900 SELECT 1 INTO l_Dummy
901 FROM msc_demand_classes
902 WHERE
903 demand_class = DemandClass AND
904 sr_instance_id = SrInstanceId;
905 EXCEPTION
906 WHEN NO_DATA_FOUND THEN
907 RETURN 'INVALID_DEMAND_CLASS';
908 WHEN others THEN
909 g_ErrorCode := 'ERROR_UNEXPECTED_02013';
910 raise;
911 END;
912 ELSE
913 IF DemandClass IS NOT NULL THEN
914 RETURN 'INVALID_DEMAND_CLASS';
915 END IF;
916 END IF;
917
918 RETURN 'OK';
919 END ValidateDemandClass;
920
921 -- =============================================================
922 -- Desc: Generate an unique demand id
923 --
924 -- Input:
925 -- ScenarioId Scenario id.
926 --
927 -- Output: No output.
928 -- =============================================================
929 PROCEDURE GenerateDemandId(ScenarioId IN NUMBER) AS
930 l_Count NUMBER;
931 BEGIN
932 IF g_DemandId = 0 THEN
933 BEGIN
934 SELECT count(*) INTO l_Count
935 FROM msd_dp_scn_entries_denorm
936 WHERE scenario_id = ScenarioId;
937
938 IF l_Count = 0 THEN
939 g_DemandId := 1000;
940 ELSE
941 SELECT MAX(demand_id) INTO l_Count
942 FROM msd_dp_scn_entries_denorm
943 WHERE
944 demand_plan_id = g_DummyDemandPlanId AND
945 scenario_id = ScenarioId;
946 g_DemandId := l_Count + 1;
947 END IF;
948
949 EXCEPTION WHEN others THEN
950 g_ErrorCode := 'ERROR_UNEXPECTED_02014';
951 raise;
952 END;
953 ELSE
954 g_DemandId := g_DemandId + 1;
955 END IF;
956
957 END GenerateDemandId;
958
959 -- =============================================================
960 -- Desc: Insert demand forecast into msd_dp_scn_entries_denorm table.
961 --
962 -- Input:
963 -- ScenarioId Scenario id.
964 -- ForecastData the demand forecast.
965 -- OrganizationId Organization id.
966 -- SrItemId Source item id.
967 -- AscpUom ASCP UOM code.
968 --
969 -- Output: No output.
970 -- =============================================================
971 PROCEDURE InsertForecast(
972 ScenarioId IN NUMBER,
973 ForecastData IN MscForecastRec,
974 OrganizationId IN NUMBER,
975 SrItemId IN NUMBER
976 ) AS
977 BEGIN
978 -- generate an unique demand id
979 GenerateDemandId(ScenarioId);
980
981 -- insert data
982 INSERT INTO msd_dp_scn_entries_denorm
983 (
984 demand_plan_id, scenario_id, demand_id, bucket_type, start_time, end_time, quantity,
985 sr_organization_id, sr_instance_id, sr_inventory_item_id, error_type, forecast_error,
986 inventory_item_id, sr_ship_to_loc_id, sr_customer_id, sr_zone_id, priority,
987 dp_uom_code, ascp_uom_code, demand_class, unit_price,
988 creation_date, created_by, last_update_login
989 )
990 VALUES
991 (
992 g_DummyDemandPlanId , -- demand_plan_id
993 ScenarioId, -- scenario_id
994 g_DemandId, -- demand_id
995 ForecastData.BucketType, -- bucket_type
996 ForecastData.StartDate, -- start_time
997 ForecastData.EndDate, -- end_time
998 ForecastData.Quantity, -- quantity
999 ForecastData.SrOrganizationId, -- sr_organization_id
1000 ForecastData.SrInstanceId, -- sr_instance_id
1001 SrItemId, -- sr_inventory_item_id
1002 ForecastData.ErrorType, -- error_type
1003 ForecastData.ForecastError, -- forecast_error
1004 ForecastData.ItemId, -- inventory_item_id
1005 ForecastData.ShipToLocation, -- sr_ship_to_loc_id
1006 ForecastData.SrCustomerId, -- sr_customer_id
1007 ForecastData.SrZoneId, -- sr_zone_id
1008 ForecastData.Priority, -- priority
1012 ForecastData.UnitPrice, -- unit_price
1009 ForecastData.Uom, -- dp_uom_code
1010 ForecastData.Uom, -- ascp_uom_code
1011 ForecastData.DemandClass, -- demand_class
1013 sysdate, -- creation_date
1014 g_UserId, -- created_by
1015 g_UserId -- last_update_login
1016 ) ;
1017 EXCEPTION WHEN others THEN
1018 g_ErrorCode := 'ERROR_UNEXPECTED_02015';
1019 raise;
1020
1021 END InsertForecast;
1022
1023 -- =============================================================
1024 -- Desc: Process a demand forecast.
1025 --
1026 -- Input:
1027 -- ScenarioId Scenario Id.
1028 -- OutputToItem Item output level, either ITEM or PRODUCT_FAMILY.
1029 -- OutputToOrganization
1030 -- Organization output level, either Y or N.
1031 -- OutputToCustomer Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
1032 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
1033 -- OutputToDemandClass
1034 -- Demand class output level, either Y or N.
1035 -- ForecastData A demand forecast.
1036 --
1037 -- Output: The possible return statuses are:
1038 -- OK
1039 -- INVALID_BUCKET_TYPE
1040 -- INVALID_START_END_DATE_FOR_DAILY_BUCKET
1041 -- INVALID_START_END_DATE_FOR_WEEkLY_BUCKET
1042 -- INVALID_START_END_DATE_FOR_MONTHLY_BUCKET
1043 -- INVALID_QUANTITY
1044 -- INVALID_SR_INSTANCE_ID
1045 -- INVALID_SR_ORGANIZATION_ID
1046 -- MISSING_VALIDATION_ORG_ID
1047 -- INVALID_ERROR_TYPE
1048 -- INVALID_FORECAST_ERROR
1049 -- INVALID_ITEM_ID
1050 -- INVALID_PRODUCT_FAMILY_ID
1051 -- FAILED_TO_QUERY_SR_ITEM_ID
1052 -- INVALID_SHIP_TO_LOCATION_ID
1053 -- INVALID_SR_CUSTOMER_ID
1054 -- INVALID_SR_ZONE_ID
1055 -- INVALID_PRIORITY
1056 -- INVALID_DEMAND_CLASS
1057 -- INVALID_UNIT_PRICE
1058 -- =============================================================
1059 FUNCTION ProcessForecast(
1060 ScenarioId IN NUMBER,
1061 OutputToItem IN VARCHAR2,
1062 OutputToOrganization IN VARCHAR2,
1063 OutputToCustomer IN VARCHAR2,
1064 OutputToDemandClass IN VARCHAR2,
1065 ForecastData IN MscForecastRec
1066 ) RETURN VARCHAR2 AS
1067 l_String VARCHAR2(100);
1068 l_OrgId NUMBER; -- we need this for global forecast
1069 l_ValidationOrgId NUMBER; -- we need this for global forecast
1070 l_SrItemId NUMBER;
1071 BEGIN
1072 -- check BucketType
1073 l_string := ValidateBucketType(ForecastData.BucketType);
1074 IF (l_String <> 'OK') THEN
1075 RETURN l_String;
1076 END IF;
1077
1078 -- check StartDate and EndDate
1079 /* Note the validation for start date end date is not completed yet, we need to
1080 find all the rules */
1081 l_string := ValidateStartEndDates(ForecastData.BucketType, ForecastData.StartDate, ForecastData.EndDate);
1082 IF (l_String <> 'OK') THEN
1083 RETURN l_String;
1084 END IF;
1085
1086 -- check Quantity
1087 IF ForecastData.Quantity < 0 THEN
1088 RETURN 'INVALID_QUANTITY';
1089 END IF;
1090
1091 -- check SrOrganizationId, SrInstanceId, get the validation organization id for global forecast as well
1092 l_string := ValidateSrData(l_OrgId, l_ValidationOrgId, OutputToOrganization, ForecastData.SrInstanceId, ForecastData.SrOrganizationId);
1093 IF (l_String <> 'OK') THEN
1094 RETURN l_String;
1095 END IF;
1096
1097 -- check ErrorType and ForecastError
1098 l_string := ValidateErrorData(ForecastData.ErrorType, ForecastData.ForecastError);
1099 IF (l_String <> 'OK') THEN
1100 RETURN l_String;
1101 END IF;
1102
1103 -- check ItemId
1104 l_string := ValidateItemData(l_SrItemId, OutputToItem, ForecastData.SrInstanceId, l_ValidationOrgId, ForecastData.ItemId);
1105 IF (l_String <> 'OK') THEN
1106 RETURN l_String;
1107 END IF;
1108
1109 -- check data related to customer output levels
1110 l_string := ValidateCustomerData(OutputToCustomer, ForecastData.SrInstanceId, ForecastData.ShipToLocation, ForecastData.SrCustomerId, ForecastData.SrZoneId);
1111 IF (l_String <> 'OK') THEN
1112 RETURN l_String;
1113 END IF;
1114
1115 -- check Priority
1116 IF ForecastData.Priority IS NULL OR ForecastData.Priority < 0 THEN
1117 RETURN 'INVALID_PRIORITY';
1118 END IF;
1119
1120 -- check UOM code
1121 l_string := ValidateUomCode(ForecastData.SrInstanceId, l_ValidationOrgId, ForecastData.ItemId, ForecastData.Uom);
1122 IF (l_String <> 'OK') THEN
1123 RETURN l_String;
1124 END IF;
1125
1126 -- check demand class
1127 l_string := ValidateDemandClass(OutputToDemandClass, ForecastData.SrInstanceId, ForecastData.DemandClass);
1128 IF (l_String <> 'OK') THEN
1129 RETURN l_String;
1130 END IF;
1131
1132 -- check UnitPrice
1133 IF ForecastData.UnitPrice IS NULL OR ForecastData.UnitPrice < 0 THEN
1134 RETURN 'INVALID_UNIT_PRICE';
1135 END IF;
1136
1140
1137 -- All parametera pass the validation, insert the new demand
1138 -- forecast into msd_dp_scn_entries_denorm table.
1139 InsertForecast(ScenarioId, ForecastData, l_OrgId, l_SrItemId);
1141 RETURN 'OK';
1142
1143 EXCEPTION
1144 WHEN others THEN
1145 RETURN g_ErrorCode;
1146
1147 END ProcessForecast;
1148
1149 -- =============================================================
1150 -- Desc: Make sub clause.
1151 --
1152 -- Input:
1153 -- ColumnName Column name.
1154 -- IdList List ids.
1155 --
1156 -- Output: The possible return statuses are:
1157 -- OK
1158 -- ERROR
1159 -- =============================================================
1160 FUNCTION MakeSubClause(
1161 Clause OUT NOCOPY VARCHAR2,
1162 ColumnName IN VARCHAR2,
1163 IdList IN MscNumberArr
1164 ) RETURN VARCHAR2 AS
1165 l_AddComma BOOLEAN;
1166 BEGIN
1167 IF IdList.COUNT = 1 THEN
1168 IF IdList(1) IS NULL THEN
1169 RETURN 'ERROR';
1170 END IF;
1171 Clause := ' and ' || ColumnName || ' = ' || IdList(1);
1172 ELSE
1173 l_AddComma := FALSE;
1174 Clause := ' and ' || ColumnName || ' IN (';
1175 FOR I IN IdList.first..IdList.last
1176 LOOP
1177 IF IdList(I) IS NULL THEN
1178 RETURN 'ERROR';
1179 END IF;
1180 IF l_AddComma THEN
1181 Clause := Clause || ', ';
1182 ELSE
1183 l_AddComma := TRUE;
1184 END IF;
1185 Clause := Clause || IdList(I);
1186 END LOOP;
1187 Clause := Clause || ')';
1188 END IF;
1189 RETURN 'OK';
1190 END MakeSubClause;
1191
1192 -- =============================================================
1193 -- Desc: Overload make sub clause.
1194 --
1195 -- Input:
1196 -- ColumnName1 Column name.
1197 -- ColumnName2 Column name.
1198 -- IdPairList List id pairs.
1199 --
1200 -- Output: The possible return statuses are:
1201 -- OK
1202 -- ERROR1
1203 -- ERROR2
1204 -- =============================================================
1205 FUNCTION MakeSubClause(
1206 Clause OUT NOCOPY VARCHAR2,
1207 ColumnName1 IN VARCHAR2,
1208 ColumnName2 IN VARCHAR2,
1209 IdPairList IN MscCustZoneTbl
1210 ) RETURN VARCHAR2 AS
1211 l_AddOr BOOLEAN;
1212 l_Clause VARCHAR2(1024);
1213 BEGIN
1214 IF IdPairList.COUNT = 1 THEN
1215 IF IdPairList(1).CustomerId IS NULL OR IdPairList(1).ZoneId IS NULL THEN
1216 RETURN 'ERROR';
1217 END IF;
1218 Clause := ' and ' || ColumnName1 || ' = ' || IdPairList(1).CustomerId;
1219 Clause := Clause || ' and ' || ColumnName2 || ' = ' || IdPairList(1).ZoneId;
1220 ELSE
1221 l_AddOr := FALSE;
1222 Clause := ' and ( ';
1223 FOR I IN IdPairList.first..IdPairList.last
1224 LOOP
1225 IF IdPairList(I).CustomerId IS NULL OR IdPairList(I).ZoneId IS NULL THEN
1226 RETURN 'ERROR';
1227 END IF;
1228 IF l_AddOr THEN
1229 Clause := Clause || ' or ';
1230 ELSE
1231 l_AddOr := TRUE;
1232 END IF;
1233 Clause := Clause || '( (' || ColumnName1 || ' = ' || IdPairList(I).CustomerId || ' ) and ( ' ||
1234 ColumnName1 || ' = ' || IdPairList(I).ZoneId || ') )';
1235 END LOOP;
1236 Clause := Clause || ')';
1237 END IF;
1238 RETURN 'OK';
1239 END MakeSubClause;
1240
1241 -- =============================================================
1242 -- Desc: Overload make sub clause.
1243 --
1244 -- Input:
1245 -- ColumnName Column name.
1246 -- IdList List ids.
1247 --
1248 -- Output: The possible return statuses are:
1249 -- OK
1250 -- ERROR
1251 -- =============================================================
1252 FUNCTION MakeSubClause(
1253 Clause OUT NOCOPY VARCHAR2,
1254 ColumnName IN VARCHAR2,
1255 IdList IN MscChar255Arr
1256 ) RETURN VARCHAR2 AS
1257 l_AddComma BOOLEAN;
1258 BEGIN
1259 IF IdList.COUNT = 1 THEN
1260 IF IdList(1) IS NULL THEN
1261 RETURN 'ERROR';
1262 END IF;
1263 Clause := ' and ' || ColumnName || ' = ' || IdList(1);
1264 ELSE
1265 l_AddComma := FALSE;
1266 Clause := ' and ' || ColumnName || ' IN (';
1267 FOR I IN IdList.first..IdList.last
1268 LOOP
1269 IF IdList(I) IS NULL THEN
1270 RETURN 'ERROR';
1271 END IF;
1272 IF l_AddComma THEN
1273 Clause := Clause || ', ';
1274 ELSE
1275 l_AddComma := TRUE;
1276 END IF;
1277 Clause := Clause || '''' || IdList(I) || '''';
1278 END LOOP;
1279 Clause := Clause || ')';
1280 END IF;
1281 RETURN 'OK';
1285 -- Desc: make where clause for item output level.
1282 END MakeSubClause;
1283
1284 -- =============================================================
1286 --
1287 -- Input:
1288 -- ItemIdList List of item ids.
1289 -- ProductFamilyIdList List of product family ids.
1290 --
1291 -- Output: The possible return statuses are:
1292 -- OK
1293 -- BOTH_ITEM_AND_PRODUCT_FAMILY
1294 -- INVALID_ITEM_ID
1295 -- INVALID_PRODUCT_FAMILY_ID
1296 -- =============================================================
1297 FUNCTION GetItemClause(
1298 Clause OUT NOCOPY VARCHAR2,
1299 ItemIdList IN MscNumberArr,
1300 ProductFamilyIdList IN MscNumberArr
1301 ) RETURN VARCHAR2 AS
1302 l_String VARCHAR2(100);
1303 BEGIN
1304 -- If any item or product family id is specified, make sure it is consistantent
1305 -- with the the rules for output levels.
1306 IF ItemIdList IS NOT NULL AND ItemIdList.COUNT > 0 AND
1307 ProductFamilyIdList IS NOT NULL AND ProductFamilyIdList.COUNT > 0 THEN
1308 RETURN 'BOTH_ITEM_AND_PRODUCT_FAMILY';
1309 END IF;
1310
1311 IF ItemIdList IS NOT NULL AND ItemIdList.COUNT > 0 THEN
1312 l_string := MakeSubClause(Clause, 'inventory_item_id', ItemIdList);
1313 IF (l_String <> 'OK') THEN
1314 RETURN 'INVALID_ITEM_ID';
1315 END IF;
1316 ELSIF ProductFamilyIdList IS NOT NULL AND ProductFamilyIdList.COUNT > 0 THEN
1317 l_string := MakeSubClause(Clause, 'inventory_item_id', ProductFamilyIdList);
1318 IF (l_String <> 'OK') THEN
1319 RETURN 'INVALID_PRODUCT_FAMILY_ID';
1320 END IF;
1321 ELSE
1322 Clause := ''; -- both lists are null.
1323 END IF;
1324
1325 RETURN 'OK';
1326 END GetItemClause;
1327
1328 -- =============================================================
1329 -- Desc: make where clause for customer output level.
1330 --
1331 -- Input:
1332 -- ScenarioId Scenario id.
1333 -- ForecastData the demand forecast.
1334 -- OrganizationId Organization id.
1335 -- SrItemId Source item id.
1336 --
1337 -- Output: The possible return statuses are:
1338 -- OK
1339 -- INVALID_SHIP_TO_LOCATION_ID
1340 -- MULTIPLE_CUSTOMER_LEVEL_DATA
1341 -- INVALID_SR_CUSTOMER_ID
1342 -- INVALID_SR_ZONE_ID
1343 -- =============================================================
1344 FUNCTION GetCustomerClause(
1345 Clause OUT NOCOPY VARCHAR2,
1346 ShipToLocIdList IN MscNumberArr,
1347 CustomerIdList IN MscNumberArr,
1348 CustZonePairList IN MscCustZoneTbl,
1349 ZoneIdList IN MscNumberArr
1350 ) RETURN VARCHAR2 AS
1351 l_HasCustomer BOOLEAN;
1352 l_String VARCHAR2(100);
1353 BEGIN
1354 -- If any customer data related is specified, make sure it is consistantent
1355 -- with the the rules for Customer output levels.
1356
1357 l_HasCustomer := FALSE;
1358 Clause := '';
1359 IF ShipToLocIdList IS NOT NULL AND ShipToLocIdList.COUNT > 0 THEN
1360 l_string := MakeSubClause(Clause, 'sr_ship_to_loc_id', ShipToLocIdList);
1361 IF (l_String <> 'OK') THEN
1362 RETURN 'INVALID_SHIP_TO_LOCATION_ID';
1363 END IF;
1364 l_HasCustomer := TRUE;
1365 END IF;
1366
1367 IF CustomerIdList IS NOT NULL AND CustomerIdList.COUNT > 0 THEN
1368 IF l_HasCustomer THEN
1369 return 'MULTIPLE_CUSTOMER_LEVEL';
1370 ELSE
1371 l_string := MakeSubClause(Clause, 'sr_customer_id', CustomerIdList);
1372 IF (l_String <> 'OK') THEN
1373 RETURN 'INVALID_SR_CUSTOMER_ID';
1374 END IF;
1375 l_HasCustomer := TRUE;
1376 END IF;
1377 END IF;
1378
1379 IF CustZonePairList IS NOT NULL AND CustZonePairList.COUNT > 0 THEN
1380 IF l_HasCustomer THEN
1381 return 'MULTIPLE_CUSTOMER_LEVEL';
1382 ELSE
1383 l_string := MakeSubClause(Clause, 'sr_customer_id', 'sr_zone_id', CustZonePairList);
1384 IF (l_String <> 'OK') THEN
1385 IF l_String = 'ERROR1' THEN
1386 RETURN 'INVALID_SR_CUSTOMER_ID';
1387 ELSE
1388 RETURN 'INVALID_SR_ZONE_ID';
1389 END IF;
1390 END IF;
1391 l_HasCustomer := TRUE;
1392 END IF;
1393 END IF;
1394
1395 IF ZoneIdList IS NOT NULL AND ZoneIdList.COUNT > 0 THEN
1396 IF l_HasCustomer THEN
1397 return 'MULTIPLE_CUSTOMER_LEVEL';
1398 ELSE
1399 l_string := MakeSubClause(Clause, 'sr_zone_id', ZoneIdList);
1400 IF (l_String <> 'OK') THEN
1401 RETURN 'INVALID_SR_ZONE_ID';
1402 END IF;
1403 l_HasCustomer := TRUE;
1404 END IF;
1405 END IF;
1406
1407 RETURN 'OK';
1408 END GetCustomerClause;
1409
1410 -- =============================================================
1411 -- Desc: make where clause for start date and end date.
1412 --
1413 -- Input:
1417 -- Output: The possible return statuses are:
1414 -- StartDate Start date, time is ignored.
1415 -- EndDate End date, time is ignored.
1416 --
1418 -- OK
1419 -- INVALID_START_END_DATE
1420 -- =============================================================
1421 FUNCTION GetStartEndDateClause(
1422 Clause OUT NOCOPY VARCHAR2,
1423 StartDate IN DATE,
1424 EndDate IN DATE
1425 )RETURN VARCHAR2 AS
1426 BEGIN
1427 IF StartDate IS NOT NULL AND EndDate IS NOT NULL AND EndDate < StartDate THEN
1428 RETURN 'INVALID_START_END_DATE';
1429 END IF;
1430
1431 Clause := '';
1432 IF StartDate IS NOT NULL THEN
1433 Clause := ' and start_time >= to_date(''' || to_char(StartDate,'yyyy-mm-dd') || ''',''yyyy-mm-dd'')';
1434 END IF;
1435
1436 IF EndDate IS NOT NULL THEN
1437 Clause := Clause || ' and end_time <= to_date(''' || to_char(EndDate,'yyyy-mm-dd') || ''',''yyyy-mm-dd'')';
1438 END IF;
1439
1440 RETURN 'OK';
1441 END GetStartEndDateClause;
1442
1443 -- =============================================================
1444 -- Desc: build the select statement and query the data.
1445 --
1446 -- Input:
1447 -- WhereClause where clause.
1448 --
1449 -- Output: No output.
1450 -- =============================================================
1451 PROCEDURE QueryForecasts(
1452 ForecastTbl OUT NOCOPY MscForecastTbl,
1453 WhereClause IN VARCHAR2
1454 ) AS
1455 TYPE FCSTCurType IS REF CURSOR;
1456 fcst_cursor FCSTCurType;
1457
1458 l_Sql VARCHAR2(2048);
1459 l_BucketType NUMBER;
1460 l_StartDate DATE;
1461 l_EndDate DATE;
1462 l_Quantity NUMBER;
1463 l_SrOrganizationId NUMBER;
1464 l_SrInstanceId NUMBER;
1465 l_ErrorType VARCHAR2(30);
1466 l_ForecastError NUMBER;
1467 l_ItemId NUMBER;
1468 l_ShipToLocation NUMBER;
1469 l_SrCustomerId NUMBER;
1470 l_SrZoneId NUMBER;
1471 l_Priority NUMBER;
1472 l_Uom VARCHAR2(10);
1473 l_DemandClass VARCHAR2(240);
1474 l_UnitPrice NUMBER;
1475 BEGIN
1476 ForecastTbl := MscForecastTbl();
1477 l_Sql := 'SELECT ' ||
1478 'nvl(bucket_type, -23453), ' ||
1479 'nvl(start_time, to_date(''1970-01-01'', ''YYYY-MM-DD'')), ' ||
1480 'nvl(end_time, to_date(''1970-01-01'', ''YYYY-MM-DD'')), ' ||
1481 'nvl(quantity, -23453), ' ||
1482 'sr_organization_id, ' ||
1483 'nvl(sr_instance_id, -23453), ' ||
1484 'error_type, ' ||
1485 'forecast_error, ' ||
1486 'nvl(inventory_item_id, -23453), ' ||
1487 'sr_ship_to_loc_id, ' ||
1488 'sr_customer_id, ' ||
1489 'sr_zone_id, ' ||
1490 'nvl(priority, -23453), ' ||
1491 'nvl(ascp_uom_code, ''''), ' ||
1492 'demand_class, ' ||
1493 'nvl(unit_price, -23453) ' ||
1494 'FROM msd_dp_scn_entries_denorm ' ||
1495 'WHERE ' ||
1496 WhereClause;
1497
1498
1499 OPEN fcst_cursor FOR l_Sql;
1500 LOOP
1501 FETCH fcst_cursor
1502 INTO
1503 l_BucketType,
1504 l_StartDate,
1505 l_EndDate,
1506 l_Quantity,
1507 l_SrOrganizationId,
1508 l_SrInstanceId,
1509 l_ErrorType,
1510 l_ForecastError,
1511 l_ItemId,
1512 l_ShipToLocation,
1513 l_SrCustomerId,
1514 l_SrZoneId,
1515 l_Priority,
1516 l_Uom,
1517 l_DemandClass,
1518 l_UnitPrice;
1519 EXIT WHEN fcst_cursor%NOTFOUND;
1520
1521 ForecastTbl.extend;
1522 ForecastTbl(ForecastTbl.count) :=
1523 MscForecastRec(
1524 l_BucketType,
1525 l_StartDate,
1526 l_EndDate,
1527 l_Quantity,
1528 l_SrOrganizationId,
1529 l_SrInstanceId,
1530 l_ErrorType,
1531 l_ForecastError,
1532 l_ItemId,
1533 l_ShipToLocation,
1534 l_SrCustomerId,
1535 l_SrZoneId,
1536 l_Priority,
1537 l_Uom,
1538 l_DemandClass,
1539 l_UnitPrice,
1543 END QueryForecasts;
1540 '');
1541 END LOOP;
1542 CLOSE fcst_cursor;
1544
1545 -- =============================================================
1546 -- Desc: Get plan id.
1547 --
1548 -- Input:
1549 -- PlanName Plan name.
1550 -- OwningOrgId Owning organization id
1551 -- SrInstanceId Source instance id.
1552 --
1553 -- Output: The possible return statuses are:
1554 -- OK
1555 -- FAILED_TO_QUERY_PLAN_ID
1556 -- =============================================================
1557 FUNCTION GetPlanId(
1558 PlanId OUT NOCOPY NUMBER,
1559 PlanName IN VARCHAR2,
1560 OwningOrgId IN NUMBER,
1561 SrInstanceId IN NUMBER
1562 ) RETURN VARCHAR2 AS
1563 l_Count NUMBER;
1564 BEGIN
1565 BEGIN
1566 SELECT plan_id
1567 INTO PlanId
1568 FROM msc_plans
1569 WHERE
1570 organization_id = OwningOrgId AND
1571 compile_designator = PlanName AND
1572 sr_instance_id = SrInstanceId;
1573 EXCEPTION
1574 WHEN NO_DATA_FOUND THEN
1575 RETURN 'FAILED_TO_QUERY_PLAN_ID';
1576 WHEN others THEN
1577 g_ErrorCode := 'ERROR_UNEXPECTED_02031';
1578 raise;
1579 END;
1580 RETURN 'OK';
1581 END GetPlanId;
1582
1583
1584 -- =============================================================
1585 -- Desc: Check if the plan contains this org organization.
1586 --
1587 -- Input:
1588 -- PlanId Plan Id.
1589 -- OrgId Organization Id.
1590 -- SrInstId Source instance id
1591 --
1592 -- Output: The possible return statuses are:
1593 -- OK
1594 -- INVALID_ORGID
1595 -- =============================================================
1596 FUNCTION ValidateOrgId(
1597 PlanId IN NUMBER,
1598 OrgId IN NUMBER,
1599 SrInstId IN NUMBER
1600 ) RETURN VARCHAR2 AS
1601 l_Dummy NUMBER;
1602 BEGIN
1603 BEGIN
1604 SELECT 1 INTO l_Dummy
1605 FROM
1606 msc_plan_organizations
1607 WHERE
1608 plan_id = PlanId AND
1609 organization_id = OrgId AND
1610 sr_instance_id = SrInstId;
1611 EXCEPTION WHEN NO_DATA_FOUND THEN
1612 RETURN 'INVALID_ORGID';
1613 WHEN others THEN
1614 raise;
1615 END;
1616
1617 RETURN 'OK';
1618 END ValidateOrgId;
1619
1620 -- =============================================================
1621 -- Desc: check item id.
1622 --
1623 -- Input:
1624 -- PlanId Plan id.
1625 -- SrInsId Source instance id.
1626 -- OrgId Organization id.
1627 -- ItemId Item id
1628 --
1629 -- Output: The possible return statuses are:
1630 -- OK
1631 -- INVALID_ITEM_ID
1632 -- =============================================================
1633 FUNCTION ValidateItemId(
1634 PlanId IN NUMBER,
1635 SrInstId IN NUMBER,
1636 OrgId IN NUMBER,
1637 ItemId IN NUMBER
1638 ) RETURN VARCHAR2 AS
1639 l_Dummy NUMBER;
1640 BEGIN
1641 BEGIN
1642 SELECT 1 INTO l_Dummy
1643 FROM msc_system_items
1644 WHERE
1645 plan_id = planId AND
1646 sr_instance_id = SrInstId AND
1647 organization_id = OrgId AND
1648 inventory_item_id = ItemId;
1649 EXCEPTION
1650 WHEN NO_DATA_FOUND THEN
1651 RETURN 'INVALID_ITEM_ID';
1652 WHEN others THEN
1653 g_ErrorCode := 'ERROR_UNEXPECTED_02033';
1654 raise;
1655 END;
1656 RETURN 'OK';
1657 END ValidateItemId;
1658
1659 -- =============================================================
1660 -- Desc: check period start date.
1661 --
1662 -- Input:
1663 -- PlanId Plan id.
1664 -- OrgId Organization id.
1665 -- SrInsId Source instance id.
1666 -- PeriodStartDate Period start date.
1667 --
1668 -- Output: The possible return statuses are:
1669 -- OK
1670 -- INVALID_PERIOD_START_DATE
1671 -- =============================================================
1672 FUNCTION ValidatePeriodStartDate(
1673 PlanId IN NUMBER,
1674 OrgId IN NUMBER,
1675 SrInstId IN NUMBER,
1676 PeriodStartDate IN DATE
1677 ) RETURN VARCHAR2 AS
1678 l_Dummy NUMBER;
1679 BEGIN
1680 BEGIN
1681 SELECT 1 INTO l_Dummy
1682 FROM msc_plan_buckets
1683 WHERE
1684 plan_id = PlanId AND
1685 organization_id = OrgId AND
1686 sr_instance_id = SrInstId AND
1687 curr_flag = 1 AND
1691 RETURN 'INVALID_PERIOD_START_DATE';
1688 trunc(bkt_start_date) = PeriodStartDate; -- PeriodStartDate is date only in xsd
1689 EXCEPTION
1690 WHEN NO_DATA_FOUND THEN
1692 WHEN others THEN
1693 g_ErrorCode := 'ERROR_UNEXPECTED_02035';
1694 raise;
1695 END;
1696 RETURN 'OK';
1697 END ValidatePeriodStartDate;
1698
1699 -- =============================================================
1700 -- Desc: check project id, task id and planning group.
1701 --
1702 -- Input:
1703 -- PlanId Scenario Id.
1704 -- OrgId Organization id.
1705 -- SrInstId Source instance id.
1706 -- ProjectId Project id.
1707 -- TaskId Task id.
1708 -- PlanningGroup Planning group.
1709 --
1710 -- Output: The possible return statuses are:
1711 -- OK
1712 -- INVALID_PROJECT_ID
1713 -- INVALID_TASK_ID
1714 -- INVALID_PLANNING_GROUP
1715 -- =============================================================
1716 FUNCTION ValidateProjectDate(
1717 PlanId IN NUMBER,
1718 OrgId IN NUMBER,
1719 SrInstId IN NUMBER,
1720 ProjectId IN NUMBER,
1721 TaskId IN NUMBER,
1722 PlanningGroup IN VARCHAR2
1723 ) RETURN VARCHAR2 AS
1724 l_Dummy NUMBER;
1725 BEGIN
1726 IF ProjectId IS NULL THEN
1727 IF TaskId IS NOT NULL THEN
1728 RETURN 'INVALID_TASK_ID';
1729 END IF;
1730 IF PlanningGroup IS NOT NULL THEN
1731 RETURN 'INVALID_PLANNING_GROUP';
1732 END IF;
1733 ELSE
1734 BEGIN -- check project id
1735 SELECT 1 INTO l_Dummy
1736 FROM msc_projects
1737 WHERE
1738 plan_id = PlanId AND
1739 sr_instance_id = SrInstId AND
1740 organization_id = OrgId AND
1741 project_id = ProjectId;
1742 EXCEPTION
1743 WHEN NO_DATA_FOUND THEN
1744 RETURN 'INVALID_PROJECT_ID';
1745 END;
1746 IF TaskId IS NOT NULL THEN
1747 BEGIN -- check task id
1748 SELECT 1 INTO l_Dummy
1749 FROM msc_project_tasks
1750 WHERE
1751 plan_id = PlanId AND
1752 sr_instance_id = SrInstId AND
1753 organization_id = OrgId AND
1754 project_id = ProjectId AND
1755 task_id = TaskId;
1756 EXCEPTION
1757 WHEN NO_DATA_FOUND THEN
1758 RETURN 'INVALID_TASK_ID';
1759 END;
1760 END IF;
1761 IF PlanningGroup IS NOT NULL THEN
1762 BEGIN -- check planning group
1763 SELECT 1 INTO l_Dummy
1764 FROM pjm_project_parameters
1765 WHERE
1766 organization_id = OrgId AND
1767 project_id = ProjectId AND
1768 planning_group = PlanningGroup;
1769 EXCEPTION
1770 WHEN NO_DATA_FOUND THEN
1771 RETURN 'INVALID_PLANNING_GROUP';
1772 END;
1773 END IF;
1774 END IF;
1775
1776 RETURN 'OK';
1777
1778 EXCEPTION
1779 WHEN others THEN
1780 g_ErrorCode := 'ERROR_UNEXPECTED_02036';
1781 raise;
1782 END ValidateProjectDate;
1783
1784 -- =============================================================
1785 -- Desc: insert a safety stock into msc_safety_stocks.
1786 --
1787 -- Input:
1788 -- PlanId Scenario Id.
1789 -- SafetyStockData A safety stock.
1790 --
1791 -- Output: No output.
1792 -- =============================================================
1793 PROCEDURE InsertSafetyStock(
1794 PlanId IN NUMBER,
1795 SafetyStockData IN MscSafetyStockRec
1796 ) AS
1797 BEGIN
1798 BEGIN
1799 INSERT INTO msc_safety_stocks
1800 (
1801 plan_id, organization_id, sr_instance_id, inventory_item_id,
1802 period_start_date, safety_stock_quantity,
1803 last_update_date, last_updated_by, creation_date, created_by,
1804 target_safety_stock,
1805 project_id, task_id, planning_group,
1806 user_defined_safety_stocks, user_defined_dos,
1807 target_days_of_supply, achieved_days_of_supply,
1808 demand_var_ss_percent, mfg_ltvar_ss_percent,
1809 transit_ltvar_ss_percent, sup_ltvar_ss_percent,
1810 total_unpooled_safety_stock
1811 )
1812 VALUES
1813 (
1814 PlanId, SafetyStockData.OrganizationId, SafetyStockData.SrInstanceId, SafetyStockData.ItemId,
1815 SafetyStockData.PeriodStartDate, SafetyStockData.SafetyStockQty,
1816 sysdate, g_UserId, sysdate, g_UserId,
1817 SafetyStockData.TargetSafetyStock,
1818 SafetyStockData.ProjectId, SafetyStockData.TaskId, SafetyStockData.PlanningGroup,
1822 SafetyStockData.TransitLTVarSSPct, SafetyStockData.SupLTVarSSPct,
1819 SafetyStockData.UserDefinedSafetyStock, SafetyStockData.UserDefinedDOS,
1820 SafetyStockData.TargetDOS, SafetyStockData.AchievedDOS,
1821 SafetyStockData.DemandVarSSPct, SafetyStockData.MfgLTVarSSPct,
1823 SafetyStockData.TotalUnpooledSS
1824 ) ;
1825 EXCEPTION
1826 WHEN DUP_VAL_ON_INDEX THEN
1827 UpdateSafetyStock(PlanId, SafetyStockData);
1828 WHEN others THEN
1829 g_ErrorCode := 'ERROR_UNEXPECTED_02037';
1830 raise;
1831 END;
1832
1833 END InsertSafetyStock;
1834
1835 -- =============================================================
1836 -- Desc: update a safety stock in msc_safety_stocks.
1837 --
1838 -- Input:
1839 -- PlanId Scenario Id.
1840 -- SafetyStockData A safety stock.
1841 --
1842 -- Output: No output.
1843 -- =============================================================
1844 PROCEDURE UpdateSafetyStock(
1845 PlanId IN NUMBER,
1846 SafetyStockData IN MscSafetyStockRec
1847 ) AS
1848 BEGIN
1849 UPDATE msc_safety_stocks
1850 SET
1851 safety_stock_quantity = SafetyStockData.SafetyStockQty,
1852 last_update_date = sysdate,
1853 last_updated_by = g_UserId,
1854 target_safety_stock = SafetyStockData.TargetSafetyStock,
1855 user_defined_safety_stocks = SafetyStockData.UserDefinedSafetyStock,
1856 user_defined_dos = SafetyStockData.UserDefinedDOS,
1857 target_days_of_supply = SafetyStockData.TargetDOS,
1858 achieved_days_of_supply = SafetyStockData.AchievedDOS,
1859 demand_var_ss_percent = SafetyStockData.DemandVarSSPct,
1860 mfg_ltvar_ss_percent = SafetyStockData.MfgLTVarSSPct,
1861 transit_ltvar_ss_percent = SafetyStockData.TransitLTVarSSPct,
1862 sup_ltvar_ss_percent = SafetyStockData.SupLTVarSSPct,
1863 total_unpooled_safety_stock = SafetyStockData.TotalUnpooledSS
1864 WHERE
1865 plan_id = PlanId AND
1866 organization_id = SafetyStockData.OrganizationId AND
1867 sr_instance_id = SafetyStockData.SrInstanceId AND
1868 inventory_item_id = SafetyStockData.ItemId AND
1869 period_start_date = SafetyStockData.PeriodStartDate AND
1870 nvl(project_id, -1) = nvl(SafetyStockData.ProjectId, -1) AND
1871 nvl(task_id, -1) = nvl(SafetyStockData.TaskId, -1) AND
1872 nvl(planning_group, -1) = nvl(SafetyStockData.PlanningGroup, -1) AND
1873 unit_number IS NULL;
1874 EXCEPTION
1875 WHEN others THEN
1876 g_ErrorCode := 'ERROR_UNEXPECTED_02038';
1877 raise;
1878 END UpdateSafetyStock;
1879
1880 -- =============================================================
1881 -- Desc: Process a safety stock.
1882 --
1883 -- Input:
1884 -- PlanId Scenario Id.
1885 -- OwningOrgId Plan owning org id.
1886 -- SrInstId Source instance id.
1887 -- SafetyStockData A safety stock.
1888 --
1889 -- Output: The possible return statuses are:
1890 -- OK
1891 -- INVALID_ORGANIZATION_ID
1892 -- INVALID_PERIOD_START_DATE
1893 -- INVALID_SS_QTY
1894 -- INVALID_TARGET_SS_QTY
1895 -- INVALID_PROJECT_ID
1896 -- INVALID_TASK_ID
1897 -- INVALID_PLANNING_GROUP
1898 -- INVALID_USER_DEFINED_SS
1899 -- INVALID_USER_DEFINED_DOS
1900 -- INVALID_TARGET_DOS
1901 -- INVALID_ACHIEVED_DOS
1902 -- INVALID_DEMAND_VAR_SS_PERCENT
1903 -- INVALID_MFG_LTVAR_SS_PERCENT
1904 -- INVALID_TRANSIT_LTVAR_SS_PERCENT
1905 -- INVALID_SUP_LTVAR_SS_PERCENT
1906 -- INVALID_TOTAL_UNPOOLED_SS
1907 -- =============================================================
1908 FUNCTION ProcessSafetyStock(
1909 PlanId IN NUMBER,
1910 OwningOrgId IN NUMBER,
1911 SrInstId IN NUMBER,
1912 SafetyStockData IN MscSafetyStockRec
1913 ) RETURN VARCHAR2 AS
1914 l_String VARCHAR2(100);
1915 BEGIN
1916 -- check organization id
1917 BEGIN
1918 l_string := ValidateOrgId(PlanId, SafetyStockData.OrganizationId, SafetyStockData.SrInstanceId);
1919 IF (l_string <> 'OK') THEN
1920 -- overwrite the error token here.
1921 RETURN 'INVALID_ORGANIZATION_ID';
1922 END IF;
1923 EXCEPTION WHEN others THEN
1924 g_ErrorCode := 'ERROR_UNEXPECTED_02032';
1925 raise;
1926 END;
1927
1928 -- No need to check SrInstanceId. GetPlanId should catch this.
1929 -- check item id
1930 l_string := ValidateItemId(PlanId, SafetyStockData.SrInstanceId, SafetyStockData.OrganizationId, SafetyStockData.ItemId);
1931 IF (l_String <> 'OK') THEN
1932 RETURN l_String;
1933 END IF;
1934
1935 -- check period start date
1936 l_string := ValidatePeriodStartDate(PlanId, OwningOrgId, SrInstId, SafetyStockData.PeriodStartDate);
1937 IF (l_String <> 'OK') THEN
1938 RETURN l_String;
1942 IF SafetyStockData.SafetyStockQty < 0 THEN
1939 END IF;
1940
1941 -- check safety stock quantity
1943 RETURN 'INVALID_SS_QTY';
1944 END IF;
1945
1946 -- check target safety stock quantity
1947 IF SafetyStockData.TargetSafetyStock IS NOT NULL AND SafetyStockData.TargetSafetyStock < SafetyStockData.SafetyStockQty THEN
1948 RETURN 'INVALID_TARGET_SS_QTY';
1949 END IF;
1950
1951 -- check project id, task id and planning group
1952 l_string := ValidateProjectDate(PlanId, OwningOrgId, SrInstId, SafetyStockData.ProjectId, SafetyStockData.TaskId, SafetyStockData.PlanningGroup);
1953 IF (l_String <> 'OK') THEN
1954 RETURN l_String;
1955 END IF;
1956
1957 -- check user defined safety stock
1958 IF SafetyStockData.UserDefinedSafetyStock IS NOT NULL AND SafetyStockData.UserDefinedSafetyStock < 0 THEN
1959 RETURN 'INVALID_USER_DEFINED_SS';
1960 END IF;
1961
1962 -- check user defined days of supply
1963 IF SafetyStockData.UserDefinedDOS IS NOT NULL AND SafetyStockData.UserDefinedDOS < 0 THEN
1964 RETURN 'INVALID_USER_DEFINED_DOS';
1965 END IF;
1966
1967 -- check target days of supply
1968 IF SafetyStockData.TargetDOS IS NOT NULL AND SafetyStockData.TargetDOS < 0 THEN
1969 RETURN 'INVALID_TARGET_DOS';
1970 END IF;
1971
1972 -- check achieved days of supply
1973 IF SafetyStockData.AchievedDOS IS NOT NULL AND SafetyStockData.AchievedDOS < 0 THEN
1974 RETURN 'INVALID_ACHIEVED_DOS';
1975 END IF;
1976
1977 -- check percentage of total SS held for demand variability
1978 IF SafetyStockData.DemandVarSSPct IS NOT NULL AND SafetyStockData.DemandVarSSPct NOT BETWEEN 0 AND 100 THEN
1979 RETURN 'INVALID_DEMAND_VAR_SS_PERCENT';
1980 END IF;
1981
1982 -- check percentage of total SS held for Mfg. Leadtime variability
1983 IF SafetyStockData.MfgLTVarSSPct IS NOT NULL AND SafetyStockData.MfgLTVarSSPct NOT BETWEEN 0 AND 100 THEN
1984 RETURN 'INVALID_MFG_LTVAR_SS_PERCENT';
1985 END IF;
1986
1987 -- check percentage of total SS held for Transit Leadtime variability
1988 IF SafetyStockData.MfgLTVarSSPct IS NOT NULL AND SafetyStockData.MfgLTVarSSPct NOT BETWEEN 0 AND 100 THEN
1989 RETURN 'INVALID_TRANSIT_LTVAR_SS_PERCENT';
1990 END IF;
1991
1992 -- check percentage of total SS held for Supplier Leadtime variability
1993 IF SafetyStockData.SupLTVarSSPct IS NOT NULL AND SafetyStockData.SupLTVarSSPct NOT BETWEEN 0 AND 100 THEN
1994 RETURN 'INVALID_SUP_LTVAR_SS_PERCENT';
1995 END IF;
1996
1997 -- check Unpooled Sum of Safety Stocks for all Uncertainity forms
1998 IF SafetyStockData.TotalUnpooledSS IS NOT NULL AND SafetyStockData.TotalUnpooledSS < 0 THEN
1999 RETURN 'INVALID_TOTAL_UNPOOLED_SS';
2000 END IF;
2001
2002 -- All parametera pass the validation, insert/update the safety
2003 -- stock into msc_safety_stocks table.
2004 InsertSafetyStock(PlanId, SafetyStockData);
2005
2006 RETURN 'OK';
2007
2008 EXCEPTION
2009 WHEN others THEN
2010 RETURN g_ErrorCode;
2011
2012 END ProcessSafetyStock;
2013
2014 -- =============================================================
2015 -- Desc: build the select statement and query the data.
2016 --
2017 -- Input:
2018 -- WhereClause where clause.
2019 --
2020 -- Output: No output.
2021 -- =============================================================
2022 PROCEDURE QuerySafetyStocks(
2023 SafetyStockTbl OUT NOCOPY MscSafetyStockTbl,
2024 WhereClause IN VARCHAR2
2025 ) AS
2026 TYPE SSCurType IS REF CURSOR;
2027 ss_cursor SSCurType;
2028
2029 l_Sql VARCHAR2(2048);
2030 l_OrganizationId NUMBER;
2031 l_SrInstanceId NUMBER;
2032 l_ItemId NUMBER;
2033 l_PeriodStartDate DATE;
2034 l_SafetyStockQty NUMBER;
2035 l_TargetSafetyStock NUMBER;
2036 l_ProjectId NUMBER;
2037 l_TaskId NUMBER;
2038 l_PlanningGroup VARCHAR2(30);
2039 l_UserDefinedSafetyStock NUMBER;
2040 l_UserDefinedDOS NUMBER;
2041 l_TargetDOS NUMBER;
2042 l_AchievedDOS NUMBER;
2043 l_DemandVarSSPct NUMBER;
2044 l_MfgLTVarSSPct NUMBER;
2045 l_TransitLTVarSSPct NUMBER;
2046 l_SupLTVarSSPct NUMBER;
2047 l_TotalUnpooledSS NUMBER;
2048 BEGIN
2049 SafetyStockTbl := MscSafetyStockTbl();
2050 l_Sql := 'SELECT ' ||
2051 'organization_id, ' ||
2052 'sr_instance_id, ' ||
2053 'inventory_item_id, ' ||
2054 'period_start_date, ' ||
2055 'safety_stock_quantity, ' ||
2056 'target_safety_stock, ' ||
2057 'project_id, ' ||
2058 'task_id, ' ||
2059 'planning_group, ' ||
2060 'user_defined_safety_stocks, ' ||
2061 'user_defined_dos, ' ||
2062 'target_days_of_supply, ' ||
2066 'transit_ltvar_ss_percent, ' ||
2063 'achieved_days_of_supply, ' ||
2064 'demand_var_ss_percent, ' ||
2065 'mfg_ltvar_ss_percent, ' ||
2067 'sup_ltvar_ss_percent, ' ||
2068 'total_unpooled_safety_stock ' ||
2069 'FROM msc_safety_stocks ' ||
2070 'WHERE ' ||
2071 WhereClause;
2072
2073 /*
2074 sample where clause
2075 plan_id = 204934 and inventory_item_id IN (10, 11, 12) and organization_id IN (201, 207, 201)
2076 */
2077 OPEN ss_cursor FOR l_Sql;
2078 LOOP
2079 FETCH ss_cursor
2080 INTO
2081 l_OrganizationId,
2082 l_SrInstanceId,
2083 l_ItemId,
2084 l_PeriodStartDate,
2085 l_SafetyStockQty,
2086 l_TargetSafetyStock,
2087 l_ProjectId,
2088 l_TaskId,
2089 l_PlanningGroup,
2090 l_UserDefinedSafetyStock,
2091 l_UserDefinedDOS,
2092 l_TargetDOS,
2093 l_AchievedDOS,
2094 l_DemandVarSSPct,
2095 l_MfgLTVarSSPct,
2096 l_TransitLTVarSSPct,
2097 l_SupLTVarSSPct,
2098 l_TotalUnpooledSS;
2099 EXIT WHEN ss_cursor%NOTFOUND;
2100
2101 SafetyStockTbl.extend;
2102 SafetyStockTbl(SafetyStockTbl.count) :=
2103 MscSafetyStockRec(
2104 l_OrganizationId,
2105 l_SrInstanceId,
2106 l_ItemId,
2107 l_PeriodStartDate,
2108 l_SafetyStockQty,
2109 l_TargetSafetyStock,
2110 l_ProjectId,
2111 l_TaskId,
2112 l_PlanningGroup,
2113 l_UserDefinedSafetyStock,
2114 l_UserDefinedDOS,
2115 l_TargetDOS,
2116 l_AchievedDOS,
2117 l_DemandVarSSPct,
2118 l_MfgLTVarSSPct,
2119 l_TransitLTVarSSPct,
2120 l_SupLTVarSSPct,
2121 l_TotalUnpooledSS,
2122 '');
2123 END LOOP;
2124 CLOSE ss_cursor;
2125 END QuerySafetyStocks;
2126
2127
2128 -- =============================================================
2129 -- Desc: Check plan type, we only support ASCP for now.
2130 --
2131 -- Input:
2132 -- PlanId Scenario Id.
2133 --
2134 -- Output: The possible return statuses are:
2135 -- OK
2136 -- INVALID_PLAN_TYPE
2137 -- =============================================================
2138 FUNCTION ValidatePlanType(PlanId IN NUMBER) RETURN VARCHAR2 AS
2139 l_Dummy NUMBER;
2140 BEGIN
2141 BEGIN
2142 SELECT 1 INTO l_Dummy
2143 FROM msc_plans
2144 WHERE
2145 curr_plan_type IN (1, 2, 3) AND
2146 plan_id = PlanId;
2147 EXCEPTION WHEN NO_DATA_FOUND THEN
2148 RETURN 'INVALID_PLAN_TYPE';
2149 WHEN others THEN
2150 g_ErrorCode := 'ERROR_UNEXPECTED_02050';
2151 raise;
2152 END;
2153
2154 RETURN 'OK';
2155 END ValidatePlanType;
2156
2157
2158 -- =============================================================
2159 -- Desc: Insert planned supply into msc_supplies table.
2160 --
2161 -- Input:
2162 -- PlanId Plan id.
2163 -- SrInstId Source instance id.
2164 -- PlannedSupplyData The planned supply.
2165 --
2166 -- Output: No output.
2167 -- =============================================================
2168 PROCEDURE InsertPlannedSupply(
2169 PlanId IN NUMBER,
2170 SrInstId IN NUMBER,
2171 PlannedSupplyData IN MscPlannedSupplyRec
2172 ) AS
2173 l_TransactionId NUMBER;
2174 BEGIN
2175 -- generate an unique demand id
2176 SELECT msc_supplies_s.nextval INTO l_TransactionId FROM DUAL;
2177
2178 -- insert data
2179 INSERT INTO msc_supplies
2180 (
2181 plan_id, transaction_id, organization_id,
2182 sr_instance_id, inventory_item_id, new_schedule_date,
2183 disposition_status_type, order_type, new_order_quantity,
2184 quantity_in_process, firm_planned_type, firm_quantity,
2185 firm_date, implement_firm, new_dock_date,
2186 status, applied,
2187 last_update_date, last_updated_by, creation_date, created_by
2188 )
2189 VALUES
2190 (
2191 PlanId, l_TransactionId, PlannedSupplyData.OrganizationId,
2192 SrInstId, PlannedSupplyData.ItemId, PlannedSupplyData.FirmDate,
2193 1, 5, 0,
2194 0, 1, PlannedSupplyData.Quantity,
2195 PlannedSupplyData.FirmDate, 2, PlannedSupplyData.FirmDate,
2196 0, 2,
2197 sysdate, g_UserId, sysdate, g_UserId
2198 );
2199 EXCEPTION WHEN others THEN
2200 g_ErrorCode := 'ERROR_UNEXPECTED_02054';
2201 raise;
2202 END InsertPlannedSupply;
2203
2204 -- =============================================================
2208 -- PlanId Scenario Id.
2205 -- Desc: Process a planned supply.
2206 --
2207 -- Input:
2209 -- SrInstanceId Source instance id.
2210 -- PlannedSupplyData A planned supply.
2211 --
2212 -- Output: The possible return statuses are:
2213 -- OK
2214 -- INVALID_ORGANIZATION_ID
2215 -- INVALID_ITEM_ID
2216 -- INVALID_QUANTITY
2217 -- =============================================================
2218 FUNCTION ProcessPlannedSupply(
2219 PlanId IN NUMBER,
2220 SrInstanceId IN NUMBER,
2221 PlannedSupplyData IN MscPlannedSupplyRec
2222 ) RETURN VARCHAR2 AS
2223 l_String VARCHAR2(100);
2224 BEGIN
2225 -- check organization id
2226 BEGIN
2227 l_string := ValidateOrgId(PlanId, PlannedSupplyData.OrganizationId, SrInstanceId);
2228 IF (l_string <> 'OK') THEN
2229 -- overwrite the error token here.
2230 RETURN 'INVALID_ORGANIZATION_ID';
2231 END IF;
2232 EXCEPTION WHEN others THEN
2233 g_ErrorCode := 'ERROR_UNEXPECTED_02052';
2234 raise;
2235 END;
2236
2237 -- check item id
2238 BEGIN
2239 l_string := ValidateItemId(PlanId, SrInstanceId, PlannedSupplyData.OrganizationId, PlannedSupplyData.ItemId);
2240 IF (l_String <> 'OK') THEN
2241 RETURN l_String;
2242 END IF;
2243 EXCEPTION WHEN others THEN
2244 g_ErrorCode := 'ERROR_UNEXPECTED_02053';
2245 raise;
2246 END;
2247
2248 -- check quantity
2249 IF PlannedSupplyData.Quantity <= 0 THEN
2250 RETURN 'INVALID_QUANTITY';
2251 END IF;
2252
2253 -- All parametera pass the validation, insert/update the planned
2254 -- supply into msc_supplies table.
2255 InsertPlannedSupply(PlanId, SrInstanceId, PlannedSupplyData);
2256
2257 RETURN 'OK';
2258
2259 EXCEPTION
2260 WHEN others THEN
2261 RETURN g_ErrorCode;
2262
2263 END ProcessPlannedSupply;
2264
2265 -- =============================================================
2266 --
2267 -- Public functions for APS Data Services.
2268 --
2269 -- =============================================================
2270
2271 -- =============================================================
2272 -- Desc: Upload demand forecasts into msd_dp_scn_entries_denorm table.
2273 --
2274 -- Input:
2275 -- UserName User name.
2276 -- RespName Responsibility name.
2277 -- RespAppName Responsibility application name.
2278 -- SecurityGroupName Security group name.
2279 -- Language Language.
2280 -- ScenarioName Scenario Name, its is used to retrieve the scenario_id.
2281 -- ItemOutputLevel Item output level, either ITEM or PRODUCT_FAMILY.
2282 -- OrganizationOutputLevel
2283 -- Organization output level, either Y or N.
2284 -- CustomerOutputLevel
2285 -- Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
2286 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
2287 -- DemandClassOutputLevel
2288 -- Demand class output level, either Y or N.
2289 -- PurgeAllFlag Allowed input is Y or N.
2290 -- If this parameter is set to Y, this operation will delete
2291 -- msd_dp_scn_entries_denorm for 5555555 + senario_id before
2292 -- any upload data is inserted/updated.
2293 -- ForecastTbl List of forecasts to be uploaded to msd_dp_scn_entries_denorm.
2294 -- BucketType Type of time bucket, allowed value is 1(Day), 2(Week), 3(Month).
2295 -- StartDate Start date of the bucket, ignore the time part.
2296 -- EndDate End Start date of the bucket, ignore the time part.
2297 -- Quantity Quantity.
2298 -- SrOrganizationId Source organization id. Ignore this if OutputToOrganization is N.
2299 -- SrInstanceId Source instance id.
2300 -- ErrorType Error type, allowed value is MAD, MAPE or NULL.
2301 -- ForecastError NULL, or >= 0 for MAD, 0 - 100 for MAPE.
2302 -- ItemId Inventory item. This parameter will be used to query the SR_INVENTORY_ITEM_ID.
2303 -- If OutputToItem is ITEM, this is an item id.
2304 -- If OutputToItem is PRODUCT_FAMILY, this is a product family id.
2305 -- ShipToLocation Ship to location. Ignore this if OutputToCustomer is not CUSTOMER_SHIP_TO_SITE.
2306 -- SrCustomerId Source customer id. Ignore this unless OutputToCustomer is either Customer or Customer Zone.
2307 -- SrZoneId Source Zone id. Ignore this if OutputToCustomer is not Customer Zone.
2308 -- Priority Priority.
2309 -- Uom Primary UOM code, ASCP.
2310 -- DemandClass Demand class. Ignore this if OutputToDemandClass is N.
2311 -- UnitPrice Unit price.
2312 -- ErrorStatus Always ignore this,
2313 --
2314 -- Output:
2315 -- Status The possible return statuses are:
2316 -- SUCCESS if everything is ok
2320 -- INVALID_FND_RESPONSIBILITYID
2317 -- COMPLETED_WITH_ERROR if any record in ForecastTbl failed the validation, the bad
2318 -- forecast record will be added to BadRecTbl
2319 -- INVALID_FND_USERID
2321 -- INVALID_SCENARIO_NAME
2322 -- DUPLICATE_SCENARIO_NAME
2323 -- INCONSIST_OUTPUT_LEVELS
2324 -- NO_DATA
2325 -- BadRecTbl List of forecasts that are failed the validation.
2326 -- =============================================================
2327 PROCEDURE UPLOAD_FORECAST(
2328 Status OUT NOCOPY VARCHAR2,
2329 BadRecTbl OUT NOCOPY MscForecastTbl,
2330 -- UserId IN NUMBER,
2331 -- ResponsibilityId IN NUMBER,
2332 UserName IN VARCHAR2,
2333 RespName IN VARCHAR2,
2334 RespAppName IN VARCHAR2,
2335 SecurityGroupName IN VARCHAR2,
2336 Language IN VARCHAR2,
2337 ScenarioName IN VARCHAR2,
2338 ItemOutputLevel IN VARCHAR2,
2339 OrganizationOutputLevel
2340 IN VARCHAR2,
2341 CustomerOutputLevel
2342 IN VARCHAR2,
2343 DemandClassOutputLevel
2344 IN VARCHAR2,
2345 PurgeAllFlag IN VARCHAR2,
2346 ForecastTbl IN MscForecastTbl
2347 ) AS
2348 l_String VARCHAR2(100);
2349 l_ResponsibilityId NUMBER;
2350 l_SecurityGroupId NUMBER;
2351 l_ScenarioId NUMBER;
2352 l_IsNewScenario BOOLEAN;
2353 l_GoodRecCount NUMBER;
2354 l_BadRecCount NUMBER;
2355 l_BadData MscForecastRec;
2356 BEGIN
2357 -- init global variables
2358 g_DemandId := 0;
2359 g_DummyDemandPlanId := 5555555;
2360 g_DummyDemandPlanName := 'Default';
2361 g_ErrorCode := '';
2362
2363 -- init bad record table
2364 BadRecTbl := MscForecastTbl();
2365
2366 -- query user id, responsibility id and security group id
2367 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
2368 IF (l_String <> 'OK') THEN
2369 Status := l_String;
2370 RETURN;
2371 END IF;
2372
2373 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSC_FNDRSRUN_LEG_COLL',l_SecurityGroupId);
2374 IF (l_String <> 'OK') THEN
2375 Status := l_String;
2376 RETURN;
2377 END IF;
2378
2379 -- check any demand forecast data is available
2380 IF ForecastTbl IS NULL OR ForecastTbl.count <= 0 THEN
2381 Status := 'NO_DATA';
2382 RETURN;
2383 END IF;
2384
2385 -- Don't need to check output levels, they're restricted by xsd
2386 -- ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel and DemandClassOutputLevel
2387
2388 -- query scenario id from ScenarioName
2389 l_IsNewScenario := FALSE;
2390 l_string := GetScenarioId(l_ScenarioId, g_DummyDemandPlanId, ScenarioName);
2391 IF (l_String <> 'OK') THEN
2392 IF l_String = 'INVALID_SCENARIO_NAME' THEN
2393 l_IsNewScenario := TRUE;
2394 ELSE
2395 Status := l_String;
2396 RETURN;
2397 END IF;
2398 END IF;
2399
2400 -- if PurgeAllFlag is set, purge all rows in msd_dp_scn_entries_denorm
2401 -- and msd_dp_scenario_output_levels for l_ScenarioId, if it is there.
2402 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllFlag) = MSC_UTIL.SYS_YES THEN
2403 IF l_IsNewScenario THEN
2404 CreateNewScenario(l_ScenarioId, ScenarioName);
2405 ELSE
2406 PurgeAllFcstData(l_ScenarioId);
2407 END IF;
2408 InsertOutputLevels(l_ScenarioId, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);
2409 ELSE
2410 IF l_IsNewScenario THEN
2411 CreateNewScenario(l_ScenarioId, ScenarioName);
2412 InsertOutputLevels(l_ScenarioId, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);
2413 ELSE
2414 -- check consistency of output level parameters against
2415 -- those outpul levels in msd_dp_scenario_output_levels
2416 l_string := ValidateOutputLevels(l_ScenarioId, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);
2417 IF (l_String <> 'OK') THEN
2418 Status := l_String;
2419 RETURN;
2420 END IF;
2421 END IF;
2422 END IF;
2423
2424 -- process forecast data
2425 l_GoodRecCount := 0;
2426 l_BadRecCount := 0;
2427 FOR I IN ForecastTbl.first..ForecastTbl.last
2428 LOOP
2429 l_string := ProcessForecast(l_ScenarioId,
2430 ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel,
2431 ForecastTbl(I)
2432 );
2433 IF (l_string <> 'OK') THEN
2434 -- ForecastTbl(I).ErrorStatus := l_string;
2438 BadRecTbl.extend;
2435 l_BadData := ForecastTbl(I);
2436 l_BadData.ErrorStatus := l_string;
2437 -- BadRecTbl(BadRecTbl.COUNT + 1) := l_BadData; crash
2439 BadRecTbl(BadRecTbl.COUNT) := l_BadData;
2440 l_BadRecCount := l_BadRecCount + 1;
2441 ELSE
2442 l_GoodRecCount := l_GoodRecCount + 1;
2443 END IF;
2444 END LOOP;
2445
2446 IF l_BadRecCount = 0 THEN
2447 Status := 'SUCCESS';
2448 ELSIF l_GoodRecCount > 0 THEN
2449 Status := 'COMPLETED_WITH_ERROR';
2450 ELSE
2451 -- if all forecast data are bad, roll back
2452 Status := 'FAILED';
2453 rollback;
2454 RETURN;
2455 END IF;
2456
2457 -- insert the dummy record in msd_demand_plans, we need
2458 -- this in order to see the scenarios that are created
2459 -- by us in LOV of Plan Option form.
2460 BEGIN
2461 INSERT INTO msd_demand_plans
2462 (
2463 demand_plan_id, organization_id, sr_instance_id, demand_plan_name, use_org_specific_bom_flag,
2464 last_update_date, last_updated_by, creation_date, created_by
2465 )
2466 VALUES
2467 (
2468 g_DummyDemandPlanId, -23453, -23453, g_DummyDemandPlanName, 'N',
2469 sysdate, g_UserId, sysdate, g_UserId
2470 ) ;
2471 EXCEPTION
2472 WHEN DUP_VAL_ON_INDEX THEN
2473 NULL; -- do nothing
2474 WHEN others THEN
2475 g_ErrorCode := 'ERROR_UNEXPECTED_02016';
2476 raise;
2477 END;
2478 COMMIT;
2479
2480 EXCEPTION
2481 WHEN others THEN
2482 rollback;
2483 Status := g_ErrorCode;
2484
2485 END UPLOAD_FORECAST;
2486
2487
2488 -- =============================================================
2489 -- Desc: Download demand forecast from msd_dp_scn_entries_denorm table.
2490 --
2491 -- Input:
2492 -- UserName User name.
2493 -- RespName Responsibility name.
2494 -- RespAppName Responsibility application name.
2495 -- SecurityGroupName Security group name.
2496 -- Language Language.
2497 -- DemandPlanName Demand plan name, it is used to retrieve the demand_plan_id
2498 -- ScenarioName Scenario Name, its is used to retrieve the scenario_id.
2499 -- ItemIdList List of item ids.
2500 -- ProductFamilyIdList
2501 -- List of product family ids.
2502 -- OrganizationIdList Organization output level, either Y or N.
2503 -- ShipToLocIdList List of ship to location ids.
2504 -- CustomerIdList List of customer ids.
2505 -- CustomerZoneList List of customer id and zone id pairs.
2506 -- ZoneIdList List of zone ids.
2507 -- DemandClassList List of demand classes.
2508 -- StartDate Start date.
2509 -- EndDate End date.
2510 --
2511 -- Output:
2512 -- Status The possible return statuses are:
2513 -- SUCCESS if everything is ok
2514 -- INVALID_FND_USERID
2515 -- INVALID_FND_RESPONSIBILITYID
2516 -- INVALID_DEMAND_PLAN_NAME
2517 -- DUPLICATE_DEMAND_PLAN_NAME
2518 -- INVALID_SCENARIO_NAME
2519 -- DUPLICATE_SCENARIO_NAME
2520 -- BOTH_ITEM_AND_PRODUCT_FAMILY
2521 -- INVALID_ITEM_ID
2522 -- INVALID_PRODUCT_FAMILY_ID
2523 -- INVALID_SR_ORGANIZATION_ID
2524 -- INVALID_SHIP_TO_LOCATION_ID
2525 -- MULTIPLE_CUSTOMER_LEVEL_DATA
2526 -- INVALID_SR_CUSTOMER_ID
2527 -- INVALID_SR_ZONE_ID
2528 -- INVALID_DEMAND_CLASS
2529 -- INVALID_START_END_DATE
2530 -- ForecastTbl List of forecasts that are downloaded.
2531 -- =============================================================
2532 PROCEDURE DOWNLOAD_FORECAST(
2533 Status OUT NOCOPY VARCHAR2,
2534 ForecastTbl OUT NOCOPY MscForecastTbl,
2535 -- UserId IN NUMBER,
2536 -- ResponsibilityId IN NUMBER,
2537 UserName IN VARCHAR2,
2538 RespName IN VARCHAR2,
2539 RespAppName IN VARCHAR2,
2540 SecurityGroupName IN VARCHAR2,
2541 Language IN VARCHAR2,
2542 DemandPlanName IN VARCHAR2,
2543 ScenarioName IN VARCHAR2,
2544 ItemIdList IN MscNumberArr,
2545 ProductFamilyIdList
2546 IN MscNumberArr,
2547 OrganizationIdList IN MscNumberArr,
2548 ShipToLocIdList IN MscNumberArr,
2549 CustomerIdList IN MscNumberArr,
2550 CustomerZoneList IN MscCustZoneTbl,
2551 ZoneIdList IN MscNumberArr,
2552 DemandClassList IN MscChar255Arr,
2553 StartDate IN DATE,
2557 l_ResponsibilityId NUMBER;
2554 EndDate IN DATE
2555 ) AS
2556 l_String VARCHAR2(100);
2558 l_SecurityGroupId NUMBER;
2559 l_DemandPlanId NUMBER;
2560 l_WhereClause VARCHAR2(2048);
2561 l_SubClause VARCHAR2(1024);
2562 l_ScenarioId NUMBER;
2563 BEGIN
2564 -- init global variables
2565 g_ErrorCode := '';
2566
2567 -- init output table
2568 ForecastTbl := MscForecastTbl();
2569
2570 -- UnitTest('all MakeSubClause');
2571 -- /*
2572
2573 -- query user id, responsibility id and security group id
2574 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
2575 IF (l_String <> 'OK') THEN
2576 Status := l_String;
2577 RETURN;
2578 END IF;
2579
2580 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSC_FNDRSRUN_LEG_COLL',l_SecurityGroupId);
2581 IF (l_String <> 'OK') THEN
2582 Status := l_String;
2583 RETURN;
2584 END IF;
2585
2586 -- get demand plan id from DemandPlanName
2587 l_string := GetDemandPlanId(l_DemandPlanId, DemandPlanName);
2588 IF (l_String <> 'OK') THEN
2589 Status := l_String;
2590 RETURN;
2591 END IF;
2592
2593 -- get scenario id from ScenarioName
2594 l_string := GetScenarioId(l_ScenarioId, l_DemandPlanId, ScenarioName);
2595 IF (l_String <> 'OK') THEN
2596 Status := l_String;
2597 RETURN;
2598 END IF;
2599 l_WhereClause := ' demand_plan_id = ' || l_DemandPlanId || ' and scenario_id = ' || l_ScenarioId;
2600
2601 -- make where clause for item output level
2602 l_string := GetItemClause(l_SubClause, ItemIdList, ProductFamilyIdList);
2603 IF (l_String <> 'OK') THEN
2604 Status := l_String;
2605 RETURN;
2606 END IF;
2607 l_WhereClause := l_WhereClause || l_SubClause;
2608
2609 -- make where clause for organization output level
2610 IF OrganizationIdList IS NOT NULL AND OrganizationIdList.COUNT > 0 THEN
2611 l_string := MakeSubClause(l_SubClause, 'sr_organization_id', OrganizationIdList);
2612 IF (l_String <> 'OK') THEN
2613 Status := 'INVALID_SR_ORGANIZATION_ID';
2614 RETURN;
2615 END IF;
2616 l_WhereClause := l_WhereClause || l_SubClause;
2617 END IF;
2618
2619 -- make where clause for customer output level
2620 l_string := GetCustomerClause(l_SubClause, ShipToLocIdList, CustomerIdList, CustomerZoneList, ZoneIdList);
2621 IF (l_String <> 'OK') THEN
2622 Status := l_String;
2623 RETURN;
2624 END IF;
2625 l_WhereClause := l_WhereClause || l_SubClause;
2626
2627 -- make where clause for demand class output level
2628 IF DemandClassList IS NOT NULL AND DemandClassList.COUNT > 0 THEN
2629 l_string := MakeSubClause(l_SubClause, 'demand_class', DemandClassList);
2630 IF (l_String <> 'OK') THEN
2631 Status := 'INVALID_DEMAND_CLASS';
2632 RETURN;
2633 END IF;
2634 IF l_SubClause <> '' THEN
2635 l_WhereClause := l_WhereClause || l_SubClause;
2636 END IF;
2637 END IF;
2638
2639 -- make where clause for start date and end date
2640 l_string := GetStartEndDateClause(l_SubClause, StartDate, EndDate);
2641 IF (l_String <> 'OK') THEN
2642 Status := l_String;
2643 RETURN;
2644 END IF;
2645 l_WhereClause := l_WhereClause || l_SubClause;
2646
2647
2648 -- query the data
2649 QueryForecasts(ForecastTbl, l_WhereClause);
2650 -- */
2651
2652 Status := 'SUCCESS';
2653
2654 EXCEPTION
2655 WHEN others THEN
2656 -- Status := 'Failed '|| fnd_message.get;
2657 Status := 'ERROR_UNEXPECTED_02021';
2658
2659 END DOWNLOAD_FORECAST;
2660
2661
2662 -- =============================================================
2663 -- Desc: Upload safety stocks into msc_safety_stocks table.
2664 --
2665 -- Input:
2666 -- UserName User name.
2667 -- RespName Responsibility name.
2668 -- RespAppName Responsibility application name.
2669 -- SecurityGroupName Security group name.
2670 -- Language Language.
2671 -- PlanName Plan name, use this + OwningOrgId + SrInstanceId to guery plan_id from msc_plans.
2672 -- OwningOrgId Owning organization id for the plan.
2673 -- SrInstanceId Source instance id.
2674 -- PurgeAllFlag Allowed input is Y or N.
2675 -- If this parameter is set to Y, this operation will delete
2676 -- msc_safety_stocks for plan_id + SrInstanceId + OrganizationId + ItemId
2677 -- before any upload data is inserted/updated.
2678 -- SafetyStockTbl List of safety stocks to be uploaded to ms_safety_stocks.
2679 -- OrganizationId Oragnization id.
2680 -- SrInstanceId Source instance id.
2681 -- ItemId Item id.
2682 -- PeriodStartDate Period start date.
2683 -- SafetyStockQty Safety stock quantity.
2684 -- TargetSafetyStock Target safety stock.
2685 -- ProjectId Project Id.
2686 -- TaskId Task Id.
2687 -- PlanningGroup Planning group.
2688 -- UserDefinedSafetyStock
2689 -- User defined safety stock quantity.
2690 -- UserDefinedDOS User defined days of supply.
2691 -- TargetDOS Taget days of supply.
2692 -- AchievedDOS Achieved days of supply.
2693 -- DemandVarSSPct Percentage of total Safety Stock held for demand variability.
2694 -- MfgLTVarSSPct Percentage of total Safety Stock held for Mfg. leadtime variability.
2695 -- TransitLTVarSSPct Percentage of total Safety Stock held for Transit leadtime variability.
2696 -- SupLTVarSSPct Percentage of total Safety Stock held for Supplier leadtime variability.
2697 -- TotalUnpooledSS Unpooled Sum of Safety Stocks for all Uncertainity forms.
2698 -- ErrorStatus Always ignore this,
2699 --
2700 -- Output:
2701 -- Status The possible return statuses are:
2702 -- SUCCESS if everything is ok
2703 -- COMPLETED_WITH_ERROR if any record in ForecastTbl failed the validation, the bad
2704 -- forecast record will be added to BadRecTbl
2705 -- INVALID_FND_USERID
2706 -- INVALID_FND_RESPONSIBILITYID
2707 -- FAILED_TO_QUERY_PLAN_ID
2708 -- PLAN_IS_NOT_READY
2709 -- NO_DATA
2710 -- BadRecTbl List of forecasts that are failed the validation.
2711 -- =============================================================
2712 PROCEDURE UPLOAD_SAFETY_STOCKS(
2713 Status OUT NOCOPY VARCHAR2,
2714 BadRecTbl OUT NOCOPY MscSafetyStockTbl,
2715 -- UserId IN NUMBER,
2716 -- ResponsibilityId IN NUMBER,
2717 UserName IN VARCHAR2,
2718 RespName IN VARCHAR2,
2719 RespAppName IN VARCHAR2,
2720 SecurityGroupName IN VARCHAR2,
2721 Language IN VARCHAR2,
2722 PlanName IN VARCHAR2,
2723 OwningOrgId IN NUMBER,
2724 SrInstanceId IN NUMBER,
2725 PurgeAllFlag IN VARCHAR2,
2726 SafetyStockTbl IN MscSafetyStockTbl
2727 ) AS
2728 l_String VARCHAR2(100);
2729 l_ResponsibilityId NUMBER;
2730 l_SecurityGroupId NUMBER;
2731 l_PlanId NUMBER;
2732 l_Date DATE;
2733 l_GoodRecCount NUMBER;
2734 l_BadRecCount NUMBER;
2735 l_BadData MscSafetyStockRec;
2736 BEGIN
2737 -- init global variables
2738 g_ErrorCode := '';
2739
2740 -- init bad record table
2741 BadRecTbl := MscSafetyStockTbl();
2742
2743 -- query user id, responsibility id and security group id
2744 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
2745 IF (l_String <> 'OK') THEN
2746 Status := l_String;
2747 RETURN;
2748 END IF;
2749
2750 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSC_FNDRSRUN_LEG_COLL',l_SecurityGroupId);
2751 IF (l_String <> 'OK') THEN
2752 Status := l_String;
2753 RETURN;
2754 END IF;
2755
2756 -- get plan id from PlanName + OwningOrgId + SrInstanceId
2757 l_string := GetPlanId(l_PlanId, PlanName, OwningOrgId, SrInstanceId);
2758 IF (l_String <> 'OK') THEN
2759 Status := l_String;
2760 RETURN;
2761 END IF;
2762
2763 /*
2764 Richard, I don't think we should check the msc_plans.plan_completion_date.
2765 The plan_completion_date is always null for new plans until they are ran.
2766 We cannot detect any ASCP plan is running and using SS from this IO plan.
2767 BEGIN
2768 SELECT plan_completion_date INTO l_Date
2769 FROM msc_plans
2770 WHERE plan_id = l_PlanId;
2771 EXCEPTION WHEN others THEN
2772 g_ErrorCode := 'ERROR_UNEXPECTED_02099';
2773 raise;
2774 IF l_Date IS NULL THEN
2775 Status := 'PLAN_IS_NOT_READY';
2776 END IF;
2777 END;
2778 */
2779
2780 -- check any safety stock data is available
2781 IF SafetyStockTbl IS NULL OR SafetyStockTbl.count <= 0 THEN
2782 Status := 'NO_DATA';
2783 RETURN;
2784 END IF;
2785
2786 -- if PurgeAllFlag is set, purge all rows in msc_safety_stocks table for l_PlanId.
2790 WHERE
2787 IF MSC_WS_COMMON.BOOL_TO_NUMBER(PurgeAllFlag) = MSC_UTIL.SYS_YES THEN
2788 BEGIN
2789 DELETE FROM msc_safety_stocks
2791 plan_id = l_PlanId;
2792 EXCEPTION WHEN others THEN
2793 g_ErrorCode := 'ERROR_UNEXPECTED_02034';
2794 raise;
2795 END;
2796 END IF;
2797
2798 -- process safety stock data
2799 l_GoodRecCount := 0;
2800 l_BadRecCount := 0;
2801 FOR I IN SafetyStockTbl.first..SafetyStockTbl.last
2802 LOOP
2803 l_string := ProcessSafetyStock(l_PlanId, OwningOrgId, SrInstanceId, SafetyStockTbl(I));
2804 IF (l_string <> 'OK') THEN
2805 -- SafetyStockTbl(I).ErrorStatus := l_string;
2806 l_BadData := SafetyStockTbl(I);
2807 l_BadData.ErrorStatus := l_string;
2808 -- BadRecTbl(BadRecTbl.COUNT + 1) := l_BadData; crash
2809 BadRecTbl.extend;
2810 BadRecTbl(BadRecTbl.COUNT) := l_BadData;
2811 l_BadRecCount := l_BadRecCount + 1;
2812 ELSE
2813 l_GoodRecCount := l_GoodRecCount + 1;
2814 END IF;
2815 END LOOP;
2816
2817 IF l_BadRecCount = 0 THEN
2818 Status := 'SUCCESS';
2819 ELSIF l_GoodRecCount > 0 THEN
2820 Status := 'COMPLETED_WITH_ERROR';
2821 ELSE
2822 -- if all safety stock data are bad, roll back
2823 Status := 'FAILED';
2824 rollback;
2825 RETURN;
2826 END IF;
2827 COMMIT;
2828
2829 EXCEPTION
2830 WHEN others THEN
2831 rollback;
2832 Status := g_ErrorCode;
2833
2834 END UPLOAD_SAFETY_STOCKS;
2835
2836
2837 -- =============================================================
2838 -- Desc: Download safety stocks from mss_safety_stocks table.
2839 --
2840 -- Input:
2841 -- UserName User name.
2842 -- RespName Responsibility name.
2843 -- RespAppName Responsibility application name.
2844 -- SecurityGroupName Security group name.
2845 -- Language Language.
2846 -- PlanName Plan name, use this + OwningOrgId + SrInstanceId to guery plan_id from msc_plans.
2847 -- OwningOrgId Owning organization id for the plan.
2848 -- SrInstanceId Source instance id.
2849 -- ItemIdList List of item Ids.
2850 -- OrganizationIdList List of organization Ids
2851 --
2852 -- Output:
2853 -- Status The possible return statuses are:
2854 -- SUCCESS if everything is ok
2855 -- INVALID_FND_USERID
2856 -- INVALID_FND_RESPONSIBILITYID
2857 -- FAILED_TO_QUERY_PLAN_ID
2858 -- INVALID_ITEM_ID
2859 -- INVALID_ORGANIZATION_ID
2860 -- ForecastTbl List of safety stocks that are downloaded.
2861 -- =============================================================
2862 PROCEDURE DOWNLOAD_SAFETY_STOCKS(
2863 Status OUT NOCOPY VARCHAR2,
2864 SafetyStockTbl OUT NOCOPY MscSafetyStockTbl,
2865 -- UserId IN NUMBER,
2866 -- ResponsibilityId IN NUMBER,
2867 UserName IN VARCHAR2,
2868 RespName IN VARCHAR2,
2869 RespAppName IN VARCHAR2,
2870 SecurityGroupName IN VARCHAR2,
2871 Language IN VARCHAR2,
2872 PlanName IN VARCHAR2,
2873 OwningOrgId IN NUMBER,
2874 SrInstanceId IN NUMBER,
2875 ItemIdList IN MscNumberArr,
2876 OrganizationIdList IN MscNumberArr
2877 ) AS
2878 l_String VARCHAR2(100);
2879 l_ResponsibilityId NUMBER;
2880 l_SecurityGroupId NUMBER;
2881 l_WhereClause VARCHAR2(2048);
2882 l_SubClause VARCHAR2(1024);
2883 l_PlanId NUMBER;
2884 BEGIN
2885 -- init output table
2886 SafetyStockTbl := MscSafetyStockTbl();
2887
2888 -- query user id, responsibility id and security group id
2889 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
2890 IF (l_String <> 'OK') THEN
2891 Status := l_String;
2892 RETURN;
2893 END IF;
2894
2895 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSC_FNDRSRUN_LEG_COLL',l_SecurityGroupId);
2896 IF (l_String <> 'OK') THEN
2897 Status := l_String;
2898 RETURN;
2899 END IF;
2900
2901 -- get plan id from PlanName + OwningOrgId + SrInstanceId
2902 l_string := GetPlanId(l_PlanId, PlanName, OwningOrgId, SrInstanceId);
2903 IF (l_String <> 'OK') THEN
2904 Status := l_String;
2905 RETURN;
2906 END IF;
2907 l_WhereClause := ' plan_id = ' || l_PlanId;
2908
2909 -- make where clause for item ids
2910 IF ItemIdList IS NOT NULL AND ItemIdList.COUNT > 0 THEN
2911 l_string := MakeSubClause(l_SubClause, 'inventory_item_id', ItemIdList);
2912 IF (l_String <> 'OK') THEN
2913 Status := 'INVALID_ITEM_ID';
2914 RETURN;
2915 END IF;
2916 l_WhereClause := l_WhereClause || l_SubClause;
2917 END IF;
2918
2919 -- make where clause for organization ids
2920 IF OrganizationIdList IS NOT NULL AND OrganizationIdList.COUNT > 0 THEN
2921 l_string := MakeSubClause(l_SubClause, 'organization_id', OrganizationIdList);
2922 IF (l_String <> 'OK') THEN
2923 Status := 'INVALID_SR_ORGANIZATION_ID';
2924 RETURN;
2925 END IF;
2929 -- query the data
2926 l_WhereClause := l_WhereClause || l_SubClause;
2927 END IF;
2928
2930 QuerySafetyStocks(SafetyStockTbl, l_WhereClause);
2931
2932 Status := 'SUCCESS';
2933
2934 EXCEPTION
2935 WHEN others THEN
2936 -- Status := 'Failed '|| fnd_message.get;
2937 Status := 'ERROR_UNEXPECTED_02040';
2938
2939 END DOWNLOAD_SAFETY_STOCKS;
2940
2941
2942 -- =============================================================
2943 -- Desc: Upload firmed plan orders into msc_supplies table.
2944 --
2945 -- Input:
2946 -- UserName User name.
2947 -- RespName Responsibility name.
2948 -- RespAppName Responsibility application name.
2949 -- SecurityGroupName Security group name.
2950 -- Language Language.
2951 -- PlanName Plan name, use this + OwningOrgId + SrInstanceId to guery plan_id from msc_plans.
2952 -- OwningOrgId Owning organization id for the plan.
2953 -- SrInstanceId Source instance id.
2954 -- PlannedSupplyTbl List of safety stocks to be uploaded to msc_supplies.
2955 -- OrganizationId Organization id.
2956 -- ItemId Item id.
2957 -- Quantity Quantity.
2958 -- FirmDate Firm date.
2959 --
2960 -- Output:
2961 -- Status The possible return statuses are:
2962 -- SUCCESS if everything is ok
2963 -- COMPLETED_WITH_ERROR if any record in ForecastTbl failed the validation, the bad
2964 -- forecast record will be added to BadRecTbl
2965 -- INVALID_FND_USERID
2966 -- INVALID_FND_RESPONSIBILITYID
2967 -- FAILED_TO_QUERY_PLAN_ID
2968 -- INVALID_PLAN_TYPE
2969 -- PLAN_IS_NOT_READY
2970 -- NO_DATA
2971 -- BadRecTbl List of firmed plann orders that are failed the validation.
2972 -- =============================================================
2973 PROCEDURE UPLOAD_PLANNED_SUPPLY(
2974 Status OUT NOCOPY VARCHAR2,
2975 BadRecTbl OUT NOCOPY MscPlannedSupplyTbl,
2976 -- UserId IN NUMBER,
2977 -- ResponsibilityId IN NUMBER,
2978 UserName IN VARCHAR2,
2979 RespName IN VARCHAR2,
2980 RespAppName IN VARCHAR2,
2981 SecurityGroupName IN VARCHAR2,
2982 Language IN VARCHAR2,
2983 PlanName IN VARCHAR2,
2984 OwningOrgId IN NUMBER,
2985 SrInstanceId IN NUMBER,
2986 PlannedSupplyTbl IN MscPlannedSupplyTbl
2987 ) AS
2988 l_String VARCHAR2(100);
2989 l_ResponsibilityId NUMBER;
2990 l_SecurityGroupId NUMBER;
2991 l_PlanId NUMBER;
2992 l_CompletionDate DATE;
2993 l_GoodRecCount NUMBER;
2994 l_BadRecCount NUMBER;
2995 l_BadData MscPlannedSupplyRec;
2996 BEGIN
2997 -- init global variables
2998 g_ErrorCode := '';
2999
3000 -- init bad record table
3001 BadRecTbl := MscPlannedSupplyTbl();
3002
3003 -- query user id, responsibility id and security group id
3004 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, g_UserId, l_ResponsibilityId, l_SecurityGroupId, UserName, RespName, RespAppName, SecurityGroupName, Language);
3005 IF (l_String <> 'OK') THEN
3006 Status := l_String;
3007 RETURN;
3008 END IF;
3009
3010 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, g_UserId, l_ResponsibilityId, 'MSC_FNDRSRUN_LEG_COLL',l_SecurityGroupId);
3011 IF (l_String <> 'OK') THEN
3012 Status := l_String;
3013 RETURN;
3014 END IF;
3015
3016 -- get plan id from PlanName + OwningOrgId + SrInstanceId
3017 l_String := GetPlanId(l_PlanId, PlanName, OwningOrgId, SrInstanceId);
3018 IF (l_String <> 'OK') THEN
3019 Status := l_String;
3020 RETURN;
3021 END IF;
3022
3023 -- we only support ASCP for now
3024 l_String := ValidatePlanType(l_PlanId);
3025 IF (l_String <> 'OK') THEN
3026 Status := l_String;
3027 RETURN;
3028 END IF;
3029
3030 -- No need to check SrInstanceId. GetPlanId should catch this.
3031
3032 /*
3033 Richard, I don't think we should check the msc_plans.plan_completion_date.
3034 1) The plan_completion_date is always null for new plans until they are ran.
3035 2) Consultant will run the ASCP plan after the upload.
3036 BEGIN
3037 SELECT plan_completion_date INTO l_CompletionDate
3038 FROM msc_plans
3039 WHERE plan_id = l_PlanId;
3040 EXCEPTION WHEN others THEN
3041 g_ErrorCode := 'ERROR_UNEXPECTED_02098';
3042 raise;
3043 END;
3044 -- Richard, can callers re-set the msc_plans.plan_completion_date?
3045 IF l_CompletionDate IS NULL THEN
3046 Status := 'PLAN_IS_NOT_READY';
3047 RETURN;
3048 END IF;
3049 */
3050
3051 -- check any planned supply data is available
3052 IF PlannedSupplyTbl IS NULL OR PlannedSupplyTbl.count <= 0 THEN
3053 Status := 'NO_DATA';
3054 RETURN;
3055 END IF;
3056
3057 -- purge all firmed plan orders in msc_supplies table
3058 -- for l_PlanId + order_type (5) + firm_planned_type .
3059 BEGIN
3060 DELETE FROM msc_supplies
3061 WHERE
3062 plan_id = l_PlanId AND
3063 order_type = 5 AND
3064 firm_planned_type = 1;
3065 EXCEPTION WHEN others THEN
3069
3066 g_ErrorCode := 'ERROR_UNEXPECTED_02051';
3067 raise;
3068 END;
3070 -- process safety stock data
3071 l_GoodRecCount := 0;
3072 l_BadRecCount := 0;
3073 FOR I IN PlannedSupplyTbl.first..PlannedSupplyTbl.last
3074 LOOP
3075 l_string := ProcessPlannedSupply(l_PlanId, SrInstanceId, PlannedSupplyTbl(I));
3076 IF (l_string <> 'OK') THEN
3077 l_BadData := PlannedSupplyTbl(I);
3078 l_BadData.ErrorStatus := l_string;
3079 -- BadRecTbl(BadRecTbl.COUNT + 1) := l_BadData; crash
3080 BadRecTbl.extend;
3081 BadRecTbl(BadRecTbl.COUNT) := l_BadData;
3082 l_BadRecCount := l_BadRecCount + 1;
3083 ELSE
3084 l_GoodRecCount := l_GoodRecCount + 1;
3085 END IF;
3086 END LOOP;
3087
3088 IF l_BadRecCount = 0 THEN
3089 Status := 'SUCCESS';
3090 ELSIF l_GoodRecCount > 0 THEN
3091 Status := 'COMPLETED_WITH_ERROR';
3092 ELSE
3093 -- if all planned supply data are bad, roll back
3094 Status := 'FAILED';
3095 rollback;
3096 RETURN;
3097 END IF;
3098 COMMIT;
3099
3100 EXCEPTION
3101 WHEN others THEN
3102 rollback;
3103 Status := g_ErrorCode;
3104
3105 END UPLOAD_PLANNED_SUPPLY;
3106
3107
3108 END MSC_WS_APS_DATA_SERVICES;
3109
[Home] [Help]