1 PACKAGE MSC_WS_APS_DATA_SERVICES AS
2 /* $Header: MSCWDATS.pls 120.7 2008/03/27 20:01:47 mtsui noship $ */
3
4 -- =============================================================
5 -- A note about output levels.
6 -- Output levels can be grouped into four categories, Item, Organization,
7 -- Customer and Demand Class. Category Item is mandatory, the rest are optional.
8 -- When any category is included in the set of output levels, only one member
9 -- from the category can be specified.
10 -- Calegory Item has 1(Item) and 3(Product Family).
11 -- Category Organization has single member, 7(Organization).
12 -- Category Customer has four members, 11(Customer Ship To Site),
13 -- 15(Customer), 41(Customer Zone) and 42(Zone).
14 -- Category Demand Class has single member, 34(Demand Class).
15 -- =============================================================
16
17
18 -- =============================================================
19 -- Desc: Upload demand forecasts into msd_dp_scn_entries_denorm table.
20 --
21 -- Input:
22 -- UserName User name.
23 -- RespName Responsibility name.
24 -- RespAppName Responsibility application name.
25 -- SecurityGroupName Security group name.
26 -- Language Language.
27 -- ScenarioName Scenario Name, it is used to retrieve the scenario_id.
28 -- ItemOutputLevel Item output level, either ITEM or PRODUCT_FAMILY.
29 -- OrganizationOutputLevel
30 -- Organization output level, either Y or N.
31 -- CustomerOutputLevel
32 -- Customer output level, either NONE, CUSTOMER_SHIP_TO_SITE,
33 -- CUSTOMER, CUSTOMER_ZONE or ZONE.
34 -- DemandClassOutputLevel
35 -- Demand class output level, either Y or N.
36 -- PurgeAllFlag Allowed input is Y or N.
37 -- If this parameter is set to Y, this operation will delete
38 -- msd_dp_scn_entries_denorm for 5555555 + senario_id before
39 -- any upload data is inserted/updated.
40 -- ForecastTbl List of forecasts to be uploaded to msd_dp_scn_entries_denorm.
41 -- BucketType Type of time bucket, allowed value is 1(Day), 2(Week), 3(Month).
42 -- StartDate Start date of the bucket, ignore the time part.
43 -- EndDate End Start date of the bucket, ignore the time part.
44 -- Quantity Quantity.
45 -- SrOrganizationId Source organization id. Ignore this if OutputToOrganization is N.
46 -- SrInstanceId Source instance id.
47 -- ErrorType Error type, allowed value is MAD, MAPE or NULL.
48 -- ForecastError NULL, or >= 0 for MAD, 0 - 100 for MAPE.
49 -- ItemId Inventory item. This parameter will be used to query the SR_INVENTORY_ITEM_ID.
50 -- If OutputToItem is ITEM, this is an item id.
51 -- If OutputToItem is PRODUCT_FAMILY, this is a product family id.
52 -- ShipToLocation Ship to location. Ignore this if OutputToCustomer is not CUSTOMER_SHIP_TO_SITE.
53 -- SrCustomerId Source customer id. Ignore this unless OutputToCustomer is either Customer or Customer Zone.
54 -- SrZoneId Source Zone id. Ignore this if OutputToCustomer is not Customer Zone.
55 -- Priority Priority.
56 -- Uom Primary UOM code, ASCP.
57 -- DemandClass Demand class. Ignore this if OutputToDemandClass is N.
58 -- UnitPrice Unit price.
59 -- ErrorStatus Always ignore this,
60 --
61 -- Output:
62 -- Status The possible return statuses are:
63 -- SUCCESS if everything is ok
64 -- COMPLETED_WITH_ERROR if any record in ForecastTbl failed the validation, the bad
65 -- forecast record will be added to BadRecTbl
66 -- INVALID_FND_USERID
67 -- INVALID_FND_RESPONSIBILITYID
68 -- INVALID_SCENARIO_NAME
69 -- DUPLICATE_SCENARIO_NAME
70 -- INCONSIST_OUTPUT_LEVELS
71 -- NO_DATA
72 -- BadRecTbl List of forecasts that are failed the validation.
73 -- =============================================================
74 PROCEDURE UPLOAD_FORECAST(
75 Status OUT NOCOPY VARCHAR2,
76 BadRecTbl OUT NOCOPY MscForecastTbl,
77 -- UserId IN NUMBER,
78 -- ResponsibilityId IN NUMBER,
79 UserName IN VARCHAR2,
80 RespName IN VARCHAR2,
81 RespAppName IN VARCHAR2,
82 SecurityGroupName IN VARCHAR2,
83 Language IN VARCHAR2,
84 ScenarioName IN VARCHAR2,
85 ItemOutputLevel IN VARCHAR2,
86 OrganizationOutputLevel
87 IN VARCHAR2,
88 CustomerOutputLevel
89 IN VARCHAR2,
90 DemandClassOutputLevel
91 IN VARCHAR2,
92 PurgeAllFlag IN VARCHAR2,
93 ForecastTbl IN MscForecastTbl
94 );
95
96
97 -- =============================================================
98 -- Desc: Download demand forecast from msd_dp_scn_entries_denorm table.
99 --
100 -- Input:
101 -- UserName User name.
102 -- RespName Responsibility name.
103 -- RespAppName Responsibility application name.
104 -- SecurityGroupName Security group name.
105 -- Language Language.
106 -- DemandPlanName Demand plan name, it is used to retrieve the demand_plan_id
107 -- ScenarioName Scenario Name, its is used to retrieve the scenario_id.
108 -- ItemIdList List of item ids.
109 -- ProductFamilyIdList
110 -- List of product family ids.
111 -- OrganizationIdList Organization output level, either Y or N.
112 -- ShipToLocIdList List of ship to location ids.
113 -- CustomerIdList List of customer ids.
114 -- CustomerZoneList List of customer id and zone id pairs.
115 -- ZoneIdList List of zone ids.
116 -- DemandClassList List of demand classes.
117 -- StartDate Start date.
118 -- EndDate End date.
119 --
120 -- Output:
121 -- Status The possible return statuses are:
122 -- SUCCESS if everything is ok
123 -- INVALID_FND_USERID
124 -- INVALID_FND_RESPONSIBILITYID
125 -- INVALID_DEMAND_PLAN_NAME
126 -- DUPLICATE_DEMAND_PLAN_NAME
127 -- INVALID_SCENARIO_NAME
128 -- DUPLICATE_SCENARIO_NAME
129 -- BOTH_ITEM_AND_PRODUCT_FAMILY
130 -- INVALID_ITEM_ID
131 -- INVALID_PRODUCT_FAMILY_ID
132 -- INVALID_SR_ORGANIZATION_ID
133 -- INVALID_SHIP_TO_LOCATION_ID
134 -- MULTIPLE_CUSTOMER_LEVEL_DATA
135 -- INVALID_SR_CUSTOMER_ID
136 -- INVALID_SR_ZONE_ID
137 -- INVALID_DEMAND_CLASS
138 -- INVALID_START_END_DATE
139 -- ForecastTbl List of forecasts that are downloaded.
140 -- =============================================================
141 PROCEDURE DOWNLOAD_FORECAST(
142 Status OUT NOCOPY VARCHAR2,
143 ForecastTbl OUT NOCOPY MscForecastTbl,
144 -- UserId IN NUMBER,
145 -- ResponsibilityId IN NUMBER,
146 UserName IN VARCHAR2,
147 RespName IN VARCHAR2,
148 RespAppName IN VARCHAR2,
149 SecurityGroupName IN VARCHAR2,
150 Language IN VARCHAR2,
151 DemandPlanName IN VARCHAR2,
152 ScenarioName IN VARCHAR2,
153 ItemIdList IN MscNumberArr,
154 ProductFamilyIdList
155 IN MscNumberArr,
156 OrganizationIdList IN MscNumberArr,
157 ShipToLocIdList IN MscNumberArr,
158 CustomerIdList IN MscNumberArr,
159 CustomerZoneList IN MscCustZoneTbl,
160 ZoneIdList IN MscNumberArr,
161 DemandClassList IN MscChar255Arr,
162 StartDate IN DATE,
163 EndDate IN DATE
164 );
165
166
167 -- =============================================================
168 -- Desc: Upload safety stocks into msc_safety_stocks table.
169 --
170 -- Input:
171 -- UserName User name.
172 -- RespName Responsibility name.
173 -- RespAppName Responsibility application name.
174 -- SecurityGroupName Security group name.
175 -- Language Language.
176 -- PlanName Plan name, use this + OwningOrgId + SrInstanceId to guery plan_id from msc_plans.
177 -- OwningOrgId Owning organization id for the plan.
178 -- SrInstanceId Source instance id.
179 -- PurgeAllFlag Allowed input is Y or N.
180 -- If this parameter is set to Y, this operation will delete
181 -- msc_safety_stocks for plan_id + SrInstanceId + OrganizationId + ItemId
182 -- before any upload data is inserted/updated.
183 -- SafetyStockTbl List of safety stocks to be uploaded to msc_safety_stocks.
184 -- OrganizationId Oragnization id.
185 -- SrInstanceId Source instance id.
186 -- ItemId Item id.
187 -- PeriodStartDate Period start date.
188 -- SafetyStockQty Safety stock quantity.
189 -- TargetSafetyStock Target safety stock.
190 -- ProjectId Project Id.
191 -- TaskId Task Id.
192 -- PlanningGroup Planning group.
193 -- UserDefinedSafetyStock
194 -- User defined safety stock quantity.
195 -- UserDefinedDOS User defined days of supply.
196 -- TargetDOS Taget days of supply.
197 -- AchievedDOS Achieved days of supply.
198 -- DemandVarSSPct Percentage of total Safety Stock held for demand variability.
199 -- MfgLTVarSSPct Percentage of total Safety Stock held for Mfg. leadtime variability.
200 -- TransitLTVarSSPct Percentage of total Safety Stock held for Transit leadtime variability.
201 -- SupLTVarSSPct Percentage of total Safety Stock held for Supplier leadtime variability.
202 -- TotalUnpooledSS Unpooled Sum of Safety Stocks for all Uncertainity forms.
203 -- ErrorStatus Always ignore this,
204 --
205 -- Output:
206 -- Status The possible return statuses are:
207 -- SUCCESS if everything is ok
208 -- COMPLETED_WITH_ERROR if any record in ForecastTbl failed the validation, the bad
209 -- forecast record will be added to BadRecTbl
210 -- INVALID_FND_USERID
211 -- INVALID_FND_RESPONSIBILITYID
212 -- FAILED_TO_QUERY_PLAN_ID
213 -- PLAN_IS_NOT_READY
214 -- NO_DATA
215 -- BadRecTbl List of safety stocks that are failed the validation.
216 -- =============================================================
217 PROCEDURE UPLOAD_SAFETY_STOCKS(
218 Status OUT NOCOPY VARCHAR2,
219 BadRecTbl OUT NOCOPY MscSafetyStockTbl,
220 -- UserId IN NUMBER,
221 -- ResponsibilityId IN NUMBER,
222 UserName IN VARCHAR2,
223 RespName IN VARCHAR2,
227 PlanName IN VARCHAR2,
224 RespAppName IN VARCHAR2,
225 SecurityGroupName IN VARCHAR2,
226 Language IN VARCHAR2,
228 OwningOrgId IN NUMBER,
229 SrInstanceId IN NUMBER,
230 PurgeAllFlag IN VARCHAR2,
231 SafetyStockTbl IN MscSafetyStockTbl
232 );
233
234
235 -- =============================================================
236 -- Desc: Download safety stocks from mss_safety_stocks table.
237 --
238 -- Input:
239 -- UserName User name.
240 -- RespName Responsibility name.
241 -- RespAppName Responsibility application name.
242 -- SecurityGroupName Security group name.
243 -- Language Language.
244 -- PlanName Plan name, use this + OwningOrgId + SrInstanceId to guery plan_id from msc_plans.
245 -- OwningOrgId Owning organization id for the plan.
246 -- SrInstanceId Source instance id.
247 -- ItemIdList List of item Ids.
248 -- OrganizationIdList List of organization Ids
249 --
250 -- Output:
251 -- Status The possible return statuses are:
252 -- SUCCESS if everything is ok
253 -- INVALID_FND_USERID
254 -- INVALID_FND_RESPONSIBILITYID
255 -- FAILED_TO_QUERY_PLAN_ID
256 -- INVALID_ITEM_ID
257 -- INVALID_ORGANIZATION_ID
258 -- ForecastTbl List of safety stocks that are downloaded.
259 -- =============================================================
260 PROCEDURE DOWNLOAD_SAFETY_STOCKS(
261 Status OUT NOCOPY VARCHAR2,
262 SafetyStockTbl OUT NOCOPY MscSafetyStockTbl,
263 -- UserId IN NUMBER,
264 -- ResponsibilityId IN NUMBER,
265 UserName IN VARCHAR2,
266 RespName IN VARCHAR2,
267 RespAppName IN VARCHAR2,
268 SecurityGroupName IN VARCHAR2,
269 Language IN VARCHAR2,
270 PlanName IN VARCHAR2,
271 OwningOrgId IN NUMBER,
272 SrInstanceId IN NUMBER,
273 ItemIdList IN MscNumberArr,
274 OrganizationIdList IN MscNumberArr
275 );
276
277
278 -- =============================================================
279 -- Desc: Upload firmed plan orders into msc_supplies table.
280 --
281 -- Input:
282 -- UserName User name.
283 -- RespName Responsibility name.
284 -- RespAppName Responsibility application name.
285 -- SecurityGroupName Security group name.
286 -- Language Language.
287 -- PlanName Plan name, use this + OwningOrgId + SrInstanceId to guery plan_id from msc_plans.
288 -- OwningOrgId Owning organization id for the plan.
289 -- SrInstanceId Source instance id.
290 -- PlannedSupplyTbl List of safety stocks to be uploaded to msc_supplies.
291 -- OrganizationId Organization id.
292 -- ItemId Item id.
293 -- Quantity Quantity.
294 -- FirmDate Firm date.
295 --
296 -- Output:
297 -- Status The possible return statuses are:
298 -- SUCCESS if everything is ok
299 -- COMPLETED_WITH_ERROR if any record in ForecastTbl failed the validation, the bad
300 -- forecast record will be added to BadRecTbl
301 -- INVALID_FND_USERID
302 -- INVALID_FND_RESPONSIBILITYID
303 -- FAILED_TO_QUERY_PLAN_ID
304 -- INVALID_PLAN_TYPE
305 -- PLAN_IS_NOT_READY
306 -- NO_DATA
307 -- BadRecTbl List of firmed plann orders that are failed the validation.
308 -- =============================================================
309 PROCEDURE UPLOAD_PLANNED_SUPPLY(
310 Status OUT NOCOPY VARCHAR2,
311 BadRecTbl OUT NOCOPY MscPlannedSupplyTbl,
312 -- UserId IN NUMBER,
313 -- ResponsibilityId IN NUMBER,
314 UserName IN VARCHAR2,
315 RespName IN VARCHAR2,
316 RespAppName IN VARCHAR2,
317 SecurityGroupName IN VARCHAR2,
318 Language IN VARCHAR2,
319 PlanName IN VARCHAR2,
320 OwningOrgId IN NUMBER,
321 SrInstanceId IN NUMBER,
322 PlannedSupplyTbl IN MscPlannedSupplyTbl
323 );
324
325
326
327 END MSC_WS_APS_DATA_SERVICES;
328
[Home] [Help]