[Home] [Help]
PACKAGE BODY: APPS.MSC_WS_SNO_PUBLISH
Source
1 PACKAGE BODY MSC_WS_SNO_PUBLISH AS
2 /* $Header: MSCWSPBB.pls 120.17.12010000.1 2008/05/02 19:09:21 appldev ship $ */
3 PROCEDURE LOG_MESSAGE (p_message varchar2) AS
4 msg varchar2(1000);
5 begin
6 msg := p_message || ' : ' || SQLERRM;
7 fnd_file.put_line(fnd_file.log, msg);
8 END LOG_MESSAGE;
9
10 PROCEDURE SET_ASCP_ALERTS (
11 Status OUT NOCOPY VARCHAR2,
12 PlanIdVar IN NUMBER
13 ) AS
14 g_ErrorCode VARCHAR2(1000);
15 BEGIN
16 -- implementation starts here
17 -- init global variables
18 g_ErrorCode := '';
19
20
21 -- delete records from MSC_EXCEPTION_DETAILS table for the given PlanId, if any
22 BEGIN
23 DELETE FROM MSC_EXCEPTION_DETAILS WHERE PLAN_ID=PlanIdVar;
24 EXCEPTION WHEN others THEN
25 NULL; -- do nothing
26 END;
27
28 BEGIN
29 -- CODE GOES HERE Part 1A - for Demand category and organizationId <> -1
30 INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
31 SR_INSTANCE_ID,
32 ORGANIZATION_ID,
33 INVENTORY_ITEM_ID,
34 DEPARTMENT_ID,
35 RESOURCE_ID,
36 EXCEPTION_DETAIL_ID,
37 EXCEPTION_TYPE,
38 QUANTITY,
39 NUMBER2,
40 DATE1,
41 DATE2,
42 NUMBER1,
43 ZONE_ID,
44 CUSTOMER_SITE_ID,
45 CUSTOMER_ID,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 CREATION_DATE,
49 CREATED_BY)
50 SELECT
51 MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
52 MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
53 MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
54 MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
55 DepartmentID,
56 -1,
57 MSC_EXCEPTION_DETAILS_S.NEXTVAL,
58 MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
59 MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
60 MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
61 TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62 TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63 case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64 AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65 AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66 AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
69 AND msc_demands.origination_type=81
70 AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
71 then
72 msc_demands.demand_id
73 else
74 null
75 end,
76 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
77 THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
78 ELSE
79 NULL
80 END,
81 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
82 THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
83 ELSE
84 NULL
85 END,
86 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
87 THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
88 ELSE
89 NULL
90 END,
91 SYSDATE,
92 '-1',
93 SYSDATE,
94 '-1'
95 FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
96 where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97 AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98 AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99 AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
102 AND msc_demands.origination_type=81
103 AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
104 EXCEPTION WHEN others THEN
105 g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001';
106 raise;
107 END;
108 BEGIN
109 -- CODE GOES HERE Part 1B - for Demand category and organizationId = -1
110 INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
111 SR_INSTANCE_ID,
112 ORGANIZATION_ID,
113 INVENTORY_ITEM_ID,
114 DEPARTMENT_ID,
115 RESOURCE_ID,
116 EXCEPTION_DETAIL_ID,
117 EXCEPTION_TYPE,
118 QUANTITY,
119 NUMBER2,
120 DATE1,
121 DATE2,
122 NUMBER1,
123 ZONE_ID,
124 CUSTOMER_SITE_ID,
125 CUSTOMER_ID,
126 LAST_UPDATE_DATE,
127 LAST_UPDATED_BY,
128 CREATION_DATE,
129 CREATED_BY)
130 SELECT
131 MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
132 MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
133 MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
134 MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
135 DepartmentID,
136 -1,
137 MSC_EXCEPTION_DETAILS_S.NEXTVAL,
138 MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
139 MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
140 MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
141 TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142 TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143 case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144 AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145 AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146 AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
149 AND msc_demands.origination_type=81
150 AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
151 -- CUSTOMER_SITE_ID:
152 AND (
153 ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
154 AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
155 AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
156 -- CUSTOMER_ID:
157 AND (
158 MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
159 AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
160 AND ( SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) = msc_demands.CUSTOMER_ID
161 )
162 then
163 msc_demands.demand_id
164 else
165 null
166 end,
167 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
168 THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
169 ELSE
170 NULL
171 END,
172 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
173 THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
174 ELSE
175 NULL
176 END,
177 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
178 THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
179 ELSE
180 NULL
181 END,
182 SYSDATE,
183 '-1',
184 SYSDATE,
185 '-1'
186 FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
187 where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188 AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189 AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190 AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192 AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
193 AND msc_demands.origination_type=81
194 AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;
195 EXCEPTION WHEN others THEN
196 g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001';
197 raise;
198 END;
199 BEGIN
200 -- CODE GOES HERE Part 2, for Supply category. Need to
201 -- use MSC_PLANS to find owning org and instance
202 INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
203 SR_INSTANCE_ID,
204 ORGANIZATION_ID,
205 INVENTORY_ITEM_ID,
206 DEPARTMENT_ID,
207 RESOURCE_ID,
208 EXCEPTION_DETAIL_ID,
209 EXCEPTION_TYPE,
210 QUANTITY,
211 NUMBER2,
212 DATE1,
213 DATE2,
214 NUMBER1,
215 ZONE_ID,
216 SUPPLIER_SITE_ID,
217 SUPPLIER_ID,
218 LAST_UPDATE_DATE,
219 LAST_UPDATED_BY,
220 CREATION_DATE,
221 CREATED_BY)
222 SELECT
223 MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
224 MSC_PLANS.sr_instance_id,
225 MSC_PLANS.organization_id,
226 MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
227 -1,
228 -1,
229 MSC_EXCEPTION_DETAILS_S.NEXTVAL,
230 MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
231 MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
232 MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233 TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234 TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235 case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236 AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237 AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238 AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239 AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240 AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241 AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242 AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243 AND msc_supplies.order_type=1
244 AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245 then
246 msc_supplies.transaction_id
247 else
248 null
249 end,
250 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
251 THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
252 ELSE
253 NULL
254 END,
255 MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id, --SupplierSiteId
256 MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId, --SupplierId
257 SYSDATE,
258 '-1',
259 SYSDATE,
260 '-1'
261 FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262 WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263 AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264 AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265 AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266 AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267 AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268 AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269 AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270 AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271 AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272 AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273 AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274
275 EXCEPTION WHEN others THEN
276 g_ErrorCode := 'ERROR_UPDATE_ALERTS_001002';
277 raise;
278 END;
279
280 BEGIN
281 -- CODE GOES HERE Part 3
282 INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
283 SR_INSTANCE_ID,
284 ORGANIZATION_ID,
285 INVENTORY_ITEM_ID,
286 DEPARTMENT_ID,
287 RESOURCE_ID,
288 EXCEPTION_DETAIL_ID,
289 EXCEPTION_TYPE,
290 QUANTITY,
291 NUMBER2,
292 DATE1,
293 DATE2,
294 NUMBER1,
295 ZONE_ID,
296 CUSTOMER_SITE_ID,
297 CUSTOMER_ID,
298 LAST_UPDATE_DATE,
299 LAST_UPDATED_BY,
300 CREATION_DATE,
301 CREATED_BY)
302 SELECT
303 MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
304 MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
305 MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306 -- inventory_item_id
307 case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308 ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310 MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311 MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312 MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313 MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314 ) > 0 )
315 then -1
316 else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
317 then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
318 else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
319 end,
320 -- department_id
321 case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
322 ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
323 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
324 MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
325 MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
326 MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
327 MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
328 ) > 0 )
329 then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330 else -1
331 end,
332 -- resource_id
333 case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
334 ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
335 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
336 MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
337 MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
338 MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
339 MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
340 ) > 0 )
341 then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
342 else -1
343 end,
344 MSC_EXCEPTION_DETAILS_S.NEXTVAL,
345 MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
346 MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
347 MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
348 TO_DATE(PeriodStart, 'YYYY-MM-DD'),
349 TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
350 NULL,
351 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
352 THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
353 ELSE
354 NULL
355 END,
356 null, --customerSiteId
357 null, --customerId
358 SYSDATE,
359 '-1',
360 SYSDATE,
361 '-1'
362 FROM MSC_INT_ASCP_EXCEPTION_DETAILS
363 WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
364 AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
365 AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
366 AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
367
368 EXCEPTION WHEN others THEN
369 g_ErrorCode := 'ERROR_UPDATE_ALERTS_001003';
370 raise;
371 END;
372
373 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
374 -- checkpoint commit;
375 Status := 'SUCCESS';
376
377 EXCEPTION
378 WHEN others THEN
379 Status := g_ErrorCode;
380 ROLLBACK;
381
382 END SET_ASCP_ALERTS;
383
384 PROCEDURE SET_UP_SYSTEM_ITEMS(
385 Status OUT NOCOPY VARCHAR2,
386 PlanIdVar IN NUMBER
387 ) AS
388
389 g_ErrorCode VARCHAR2(1000);
390 BEGIN
391 /* implementation start here */
392 -- init global variables
393 g_ErrorCode := '';
394
395
396 -- delete records from MSC_SYSTEM_ITEMS table for the given PlanId, if any
397 BEGIN
398 DELETE FROM MSC_SYSTEM_ITEMS WHERE PLAN_ID=PlanIdVar;
399 EXCEPTION WHEN others THEN
400 NULL; -- do nothing
401 END;
402
403 BEGIN
404 -- CODE GOES HERE
405 -- duplicate system items needed in the msc_system_items table with changed PlanId
406 INSERT INTO MSC_SYSTEM_ITEMS (
407 PLAN_ID,
408 ORGANIZATION_ID,
409 INVENTORY_ITEM_ID,
410 SR_INSTANCE_ID,
411 SR_INVENTORY_ITEM_ID,
412 ITEM_NAME,
413 LOTS_EXPIRATION,
414 LOT_CONTROL_CODE,
415 SHRINKAGE_RATE,
416 FIXED_DAYS_SUPPLY,
417 FIXED_ORDER_QUANTITY,
418 FIXED_LOT_MULTIPLIER,
419 MINIMUM_ORDER_QUANTITY,
420 MAXIMUM_ORDER_QUANTITY,
421 ROUNDING_CONTROL_TYPE,
422 PLANNING_TIME_FENCE_DAYS,
423 PLANNING_TIME_FENCE_DATE,
424 DEMAND_TIME_FENCE_DAYS,
425 DEMAND_TIME_FENCE_DATE,
426 DESCRIPTION,
427 RELEASE_TIME_FENCE_CODE,
428 RELEASE_TIME_FENCE_DAYS,
429 IN_SOURCE_PLAN,
430 REVISION,
431 SR_CATEGORY_ID,
432 ABC_CLASS,
433 CATEGORY_NAME,
434 MRP_PLANNING_CODE,
435 FIXED_LEAD_TIME,
436 VARIABLE_LEAD_TIME,
437 PREPROCESSING_LEAD_TIME,
438 POSTPROCESSING_LEAD_TIME,
439 FULL_LEAD_TIME,
440 CUMULATIVE_TOTAL_LEAD_TIME,
441 CUM_MANUFACTURING_LEAD_TIME,
442 UOM_CODE,
443 UNIT_WEIGHT,
444 UNIT_VOLUME,
445 WEIGHT_UOM,
446 VOLUME_UOM,
447 PRODUCT_FAMILY_ID,
448 ATP_RULE_ID,
449 ATP_COMPONENTS_FLAG,
450 BUILD_IN_WIP_FLAG,
451 PURCHASING_ENABLED_FLAG,
452 PLANNING_MAKE_BUY_CODE,
453 REPETITIVE_TYPE,
454 REPETITIVE_VARIANCE,
455 STANDARD_COST,
456 CARRYING_COST,
457 ORDER_COST,
458 MATERIAL_COST,
459 DMD_LATENESS_COST,
460 RESOURCE_COST,
461 SS_PENALTY_COST,
462 SUPPLIER_CAP_OVERUTIL_COST,
463 LIST_PRICE,
464 AVERAGE_DISCOUNT,
465 ENGINEERING_ITEM_FLAG,
466 WIP_SUPPLY_TYPE,
467 SAFETY_STOCK_CODE,
468 SAFETY_STOCK_PERCENT,
469 SAFETY_STOCK_BUCKET_DAYS,
470 INVENTORY_USE_UP_DATE,
471 BUYER_NAME,
472 PLANNER_CODE,
473 PLANNING_EXCEPTION_SET,
474 EXCESS_QUANTITY,
475 EXCEPTION_SHORTAGE_DAYS,
476 EXCEPTION_EXCESS_DAYS,
477 EXCEPTION_OVERPROMISED_DAYS,
478 EXCEPTION_CODE,
479 BOM_ITEM_TYPE,
480 ATO_FORECAST_CONTROL,
481 EFFECTIVITY_CONTROL,
482 ORGANIZATION_CODE,
483 ACCEPTABLE_RATE_INCREASE,
484 ACCEPTABLE_RATE_DECREASE,
485 EXCEPTION_REP_VARIANCE_DAYS,
486 OVERRUN_PERCENTAGE,
487 INVENTORY_PLANNING_CODE,
488 ACCEPTABLE_EARLY_DELIVERY,
489 CALCULATE_ATP,
490 END_ASSEMBLY_PEGGING_FLAG,
491 END_ASSEMBLY_PEGGING,
492 FULL_PEGGING,
493 INVENTORY_ITEM_FLAG,
494 SOURCE_ORG_ID,
495 BASE_ITEM_ID,
496 ABC_CLASS_NAME,
497 FIXED_SAFETY_STOCK_QTY,
498 PRIMARY_SUPPLIER_ID,
499 ATP_FLAG,
500 LOW_LEVEL_CODE,
501 PLANNER_STATUS_CODE,
502 NETTABLE_INVENTORY_QUANTITY,
503 NONNETTABLE_INVENTORY_QUANTITY,
504 REFRESH_NUMBER,
505 REQUEST_ID,
506 PROGRAM_APPLICATION_ID,
507 PROGRAM_ID,
508 PROGRAM_UPDATE_DATE,
509 ATTRIBUTE_CATEGORY,
510 ATTRIBUTE1,
511 ATTRIBUTE2,
512 ATTRIBUTE3,
513 ATTRIBUTE4,
514 ATTRIBUTE5,
515 ATTRIBUTE6,
516 ATTRIBUTE7,
517 ATTRIBUTE8,
518 ATTRIBUTE9,
519 ATTRIBUTE10,
520 ATTRIBUTE11,
521 ATTRIBUTE12,
522 ATTRIBUTE13,
523 ATTRIBUTE14,
524 ATTRIBUTE15,
525 REVISION_QTY_CONTROL_CODE,
526 EXPENSE_ACCOUNT,
527 INVENTORY_ASSET_FLAG,
528 BUYER_ID,
529 REPETITIVE_PLANNING_FLAG,
530 PICK_COMPONENTS_FLAG,
531 SERVICE_LEVEL,
532 REPLENISH_TO_ORDER_FLAG,
533 PIP_FLAG,
534 YIELD_CONV_FACTOR,
535 MIN_MINMAX_QUANTITY,
536 MAX_MINMAX_QUANTITY,
537 NEW_ATP_FLAG,
538 SOURCE_TYPE,
539 SUBSTITUTION_WINDOW,
540 CREATE_SUPPLY_FLAG,
541 REORDER_POINT,
542 AVERAGE_ANNUAL_DEMAND,
543 ECONOMIC_ORDER_QUANTITY,
544 SERIAL_NUMBER_CONTROL_CODE,
545 CONVERGENCE,
546 DIVERGENCE,
547 CONTINOUS_TRANSFER,
548 CRITICAL_COMPONENT_FLAG,
549 REDUCE_MPS,
550 CONSIGNED_FLAG,
551 VMI_MINIMUM_UNITS,
552 VMI_MINIMUM_DAYS,
553 VMI_MAXIMUM_UNITS,
554 VMI_MAXIMUM_DAYS,
555 AVERAGE_DAILY_DEMAND,
556 VMI_FIXED_ORDER_QUANTITY,
557 SO_AUTHORIZATION_FLAG,
558 VMI_FORECAST_TYPE,
559 FORECAST_HORIZON,
560 ASN_AUTOEXPIRE_FLAG,
561 VMI_REFRESH_FLAG,
562 BUDGET_CONSTRAINED,
563 MAX_QUANTITY,
564 MAX_QUANTITY_DOS,
565 DAYS_TGT_INV_WINDOW,
566 DAYS_MAX_INV_WINDOW,
567 DAYS_TGT_INV_SUPPLY,
568 DAYS_MAX_INV_SUPPLY,
569 DRP_PLANNED,
570 AGGREGATE_TIME_FENCE_DATE,
571 INFERRED_CRITICAL_FLAG,
572 SS_WINDOW_SIZE,
573 ITEM_CREATION_DATE,
574 PLANNING_TIME_FENCE_CODE,
575 SHORTAGE_TYPE,
576 EXCESS_TYPE,
577 PEGGING_DEMAND_WINDOW_DAYS,
578 PEGGING_SUPPLY_WINDOW_DAYS,
579 UNSATISFIED_DEMAND_FACTOR,
580 SAFETY_LEAD_TIME,
581 LAST_UPDATE_DATE,
582 LAST_UPDATED_BY,
583 CREATION_DATE,
584 CREATED_BY,
585 LAST_UPDATE_LOGIN )
586 SELECT DISTINCT PlanIdVar,
587 ORGANIZATION_ID,
588 INVENTORY_ITEM_ID,
589 SR_INSTANCE_ID,
590 SR_INVENTORY_ITEM_ID,
591 ITEM_NAME,
592 LOTS_EXPIRATION,
593 LOT_CONTROL_CODE,
594 SHRINKAGE_RATE,
595 FIXED_DAYS_SUPPLY,
596 FIXED_ORDER_QUANTITY,
597 FIXED_LOT_MULTIPLIER,
598 MINIMUM_ORDER_QUANTITY,
599 MAXIMUM_ORDER_QUANTITY,
600 ROUNDING_CONTROL_TYPE,
601 PLANNING_TIME_FENCE_DAYS,
602 PLANNING_TIME_FENCE_DATE,
603 DEMAND_TIME_FENCE_DAYS,
604 DEMAND_TIME_FENCE_DATE,
605 DESCRIPTION,
606 RELEASE_TIME_FENCE_CODE,
607 RELEASE_TIME_FENCE_DAYS,
608 IN_SOURCE_PLAN,
609 REVISION,
610 SR_CATEGORY_ID,
611 ABC_CLASS,
612 CATEGORY_NAME,
613 MRP_PLANNING_CODE,
614 FIXED_LEAD_TIME,
615 VARIABLE_LEAD_TIME,
616 PREPROCESSING_LEAD_TIME,
617 POSTPROCESSING_LEAD_TIME,
618 FULL_LEAD_TIME,
619 CUMULATIVE_TOTAL_LEAD_TIME,
620 CUM_MANUFACTURING_LEAD_TIME,
621 UOM_CODE,
622 UNIT_WEIGHT,
623 UNIT_VOLUME,
624 WEIGHT_UOM,
625 VOLUME_UOM,
626 PRODUCT_FAMILY_ID,
627 ATP_RULE_ID,
628 ATP_COMPONENTS_FLAG,
629 BUILD_IN_WIP_FLAG,
630 PURCHASING_ENABLED_FLAG,
631 PLANNING_MAKE_BUY_CODE,
632 REPETITIVE_TYPE,
633 REPETITIVE_VARIANCE,
634 STANDARD_COST,
635 CARRYING_COST,
636 ORDER_COST,
637 MATERIAL_COST,
638 DMD_LATENESS_COST,
639 RESOURCE_COST,
640 SS_PENALTY_COST,
641 SUPPLIER_CAP_OVERUTIL_COST,
642 LIST_PRICE,
643 AVERAGE_DISCOUNT,
644 ENGINEERING_ITEM_FLAG,
645 WIP_SUPPLY_TYPE,
646 SAFETY_STOCK_CODE,
647 SAFETY_STOCK_PERCENT,
648 SAFETY_STOCK_BUCKET_DAYS,
649 INVENTORY_USE_UP_DATE,
650 BUYER_NAME,
651 PLANNER_CODE,
652 PLANNING_EXCEPTION_SET,
653 EXCESS_QUANTITY,
654 EXCEPTION_SHORTAGE_DAYS,
655 EXCEPTION_EXCESS_DAYS,
656 EXCEPTION_OVERPROMISED_DAYS,
657 EXCEPTION_CODE,
658 BOM_ITEM_TYPE,
659 ATO_FORECAST_CONTROL,
660 EFFECTIVITY_CONTROL,
661 ORGANIZATION_CODE,
662 ACCEPTABLE_RATE_INCREASE,
663 ACCEPTABLE_RATE_DECREASE,
664 EXCEPTION_REP_VARIANCE_DAYS,
665 OVERRUN_PERCENTAGE,
666 INVENTORY_PLANNING_CODE,
667 ACCEPTABLE_EARLY_DELIVERY,
668 CALCULATE_ATP,
669 END_ASSEMBLY_PEGGING_FLAG,
670 END_ASSEMBLY_PEGGING,
671 FULL_PEGGING,
672 INVENTORY_ITEM_FLAG,
673 SOURCE_ORG_ID,
674 BASE_ITEM_ID,
675 ABC_CLASS_NAME,
676 FIXED_SAFETY_STOCK_QTY,
677 PRIMARY_SUPPLIER_ID,
678 ATP_FLAG,
679 LOW_LEVEL_CODE,
680 PLANNER_STATUS_CODE,
681 NETTABLE_INVENTORY_QUANTITY,
682 NONNETTABLE_INVENTORY_QUANTITY,
683 REFRESH_NUMBER,
684 REQUEST_ID,
685 PROGRAM_APPLICATION_ID,
686 PROGRAM_ID,
687 PROGRAM_UPDATE_DATE,
688 ATTRIBUTE_CATEGORY,
689 ATTRIBUTE1,
690 ATTRIBUTE2,
691 ATTRIBUTE3,
692 ATTRIBUTE4,
693 ATTRIBUTE5,
694 ATTRIBUTE6,
695 ATTRIBUTE7,
696 ATTRIBUTE8,
697 ATTRIBUTE9,
698 ATTRIBUTE10,
699 ATTRIBUTE11,
700 ATTRIBUTE12,
701 ATTRIBUTE13,
702 ATTRIBUTE14,
703 ATTRIBUTE15,
704 REVISION_QTY_CONTROL_CODE,
705 EXPENSE_ACCOUNT,
706 INVENTORY_ASSET_FLAG,
707 BUYER_ID,
708 REPETITIVE_PLANNING_FLAG,
709 PICK_COMPONENTS_FLAG,
710 SERVICE_LEVEL,
711 REPLENISH_TO_ORDER_FLAG,
712 PIP_FLAG,
713 YIELD_CONV_FACTOR,
714 MIN_MINMAX_QUANTITY,
715 MAX_MINMAX_QUANTITY,
716 NEW_ATP_FLAG,
717 SOURCE_TYPE,
718 SUBSTITUTION_WINDOW,
719 CREATE_SUPPLY_FLAG,
720 REORDER_POINT,
721 AVERAGE_ANNUAL_DEMAND,
722 ECONOMIC_ORDER_QUANTITY,
723 SERIAL_NUMBER_CONTROL_CODE,
724 CONVERGENCE,
725 DIVERGENCE,
726 CONTINOUS_TRANSFER,
727 CRITICAL_COMPONENT_FLAG,
728 REDUCE_MPS,
729 CONSIGNED_FLAG,
730 VMI_MINIMUM_UNITS,
731 VMI_MINIMUM_DAYS,
732 VMI_MAXIMUM_UNITS,
733 VMI_MAXIMUM_DAYS,
734 AVERAGE_DAILY_DEMAND,
735 VMI_FIXED_ORDER_QUANTITY,
736 SO_AUTHORIZATION_FLAG,
737 VMI_FORECAST_TYPE,
738 FORECAST_HORIZON,
739 ASN_AUTOEXPIRE_FLAG,
740 VMI_REFRESH_FLAG,
741 BUDGET_CONSTRAINED,
742 MAX_QUANTITY,
743 MAX_QUANTITY_DOS,
744 DAYS_TGT_INV_WINDOW,
745 DAYS_MAX_INV_WINDOW,
746 DAYS_TGT_INV_SUPPLY,
747 DAYS_MAX_INV_SUPPLY,
748 DRP_PLANNED,
749 AGGREGATE_TIME_FENCE_DATE,
750 INFERRED_CRITICAL_FLAG,
751 SS_WINDOW_SIZE,
752 ITEM_CREATION_DATE,
753 PLANNING_TIME_FENCE_CODE,
754 SHORTAGE_TYPE,
755 EXCESS_TYPE,
756 PEGGING_DEMAND_WINDOW_DAYS,
757 PEGGING_SUPPLY_WINDOW_DAYS,
758 UNSATISFIED_DEMAND_FACTOR,
759 SAFETY_LEAD_TIME,
760 SYSDATE,
761 '-1',
762 SYSDATE,
763 '-1',
764 -- last update login :
765 -- we do not have the userId here ( no validation should be done at this point as required earlier) :
766 '-1'
767 FROM MSC_SYSTEM_ITEMS LEFT JOIN MSC_INT_ASCP_INVENTORY ON
768 MSC_INT_ASCP_INVENTORY.OrganizationID = MSC_SYSTEM_ITEMS.ORGANIZATION_ID AND
769 MSC_INT_ASCP_INVENTORY.InstanceID = MSC_SYSTEM_ITEMS.SR_INSTANCE_ID AND
770 MSC_INT_ASCP_INVENTORY.ItemId = MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID
771 WHERE MSC_SYSTEM_ITEMS.PLAN_ID=-1 AND MSC_INT_ASCP_INVENTORY.PLAN_ID=PlanIdVar;
772 EXCEPTION WHEN others THEN
773 g_ErrorCode := 'ERROR_UPDATE_MSC_SYSTEM_ITEMS_001001';
774 raise;
775 END;
776
777
778
779
780 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
781 -- checkpoint commit;
782 EXCEPTION
783 WHEN others THEN
784 Status := g_ErrorCode;
785 ROLLBACK;
786
787 END SET_UP_SYSTEM_ITEMS;
788
789
790
791 PROCEDURE SET_ASCP_PLAN_BUCKETS(
792 Status OUT NOCOPY VARCHAR2,
793 PlanIdVar IN NUMBER
794 ) AS
795
796 g_ErrorCode VARCHAR2(1000);
797 BEGIN
798 /* implementation start here */
799 -- init global variables
800 g_ErrorCode := '';
801
802
803 -- delete records from MSC_PLAN_BUCKETS table for the given PlanId, if any
804 BEGIN
805 DELETE FROM MSC_PLAN_BUCKETS WHERE PLAN_ID=PlanIdVar;
806 EXCEPTION WHEN others THEN
807 NULL; -- do nothing
808 END;
809
810 BEGIN
811 -- CODE GOES HERE
812 INSERT INTO MSC_PLAN_BUCKETS (PLAN_ID,
813 ORGANIZATION_ID,
814 SR_INSTANCE_ID,
815 BUCKET_INDEX,
816 CURR_FLAG,
817 BKT_START_DATE,
818 BKT_END_DATE,
819 DAYS_IN_BKT,
820 BUCKET_TYPE,
821 LAST_UPDATE_DATE,
822 LAST_UPDATED_BY,
823 CREATION_DATE,
824 CREATED_BY)
825 SELECT PlanIdVar, MSC_PLANS.ORGANIZATION_ID, MSC_PLANS.SR_INSTANCE_ID,
826 TO_NUMBER(BucketIndex), 1,
827 TO_DATE(BktStartDate,'YYYY-MM-DD'), TO_DATE(BktEndDate,'YYYY-MM-DD')-1/86400,
828 TO_NUMBER(DaysInBucket), TO_NUMBER(BucketType),
829 SYSDATE, '-1',
830 SYSDATE, '-1'
831 FROM MSC_INT_ASCP_EXPORT_BUCKETS, MSC_PLANS
832 WHERE PLANID=PlanIdVar AND
833 MSC_PLANS.PLAN_ID=PlanIdVar;
834 EXCEPTION WHEN others THEN
835 g_ErrorCode := 'ERROR_UPDATE_PLAN_BUCKETS_001001';
836 raise;
837 END;
838
839
840
841
842 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
843 -- checkpoint commit;
844 EXCEPTION
845 WHEN others THEN
846 Status := g_ErrorCode;
847 ROLLBACK;
848
849 END SET_ASCP_PLAN_BUCKETS;
850
851 PROCEDURE SET_ASCP_DEMANDS(
852 Status OUT NOCOPY VARCHAR2,
853 PlanIdVar IN NUMBER
854 ) AS
855 g_ErrorCode VARCHAR2(1000);
856 BEGIN
857 /* implementation start here */
858 -- init global variables
859 g_ErrorCode := '';
860
861
862 -- delete records from MSC_WS_DEMANDS table for the given PlanIdVar, if any
863 BEGIN
864 DELETE FROM MSC_DEMANDS WHERE PLAN_ID=PlanIdVar;
865 EXCEPTION WHEN others THEN
866 NULL; -- do nothing
867 END;
868 -- insert new rows
869
870
871 BEGIN
872 -- CODE GOES HERE
873 --
874 -- populate MSC_DEMANDS from ASCP Demand (Forecast/Satisfied)
875 --
876 INSERT INTO MSC_DEMANDS (
877 ORGANIZATION_ID,
878 INVENTORY_ITEM_ID,
879 PLAN_ID,
880 SR_INSTANCE_ID,
881 DEMAND_ID,
882 ORIGINATION_TYPE,
883 USING_REQUIREMENT_QUANTITY,
884 QUANTITY_BY_DUE_DATE,
885 DMD_SATISFIED_DATE,
886 ZONE_ID,
887 CUSTOMER_SITE_ID,
888 CUSTOMER_ID,
889 SERVICE_LEVEL,
890 USING_ASSEMBLY_DEMAND_DATE,
891 USING_ASSEMBLY_ITEM_ID,
892 DEMAND_TYPE,
893 UNMET_QUANTITY,
894 LAST_UPDATE_DATE,
895 LAST_UPDATED_BY,
896 CREATION_DATE,
897 CREATED_BY)
898 SELECT
899 CASE WHEN ( ZoneID is not null )
900 THEN -1
901 ELSE
902 OrganizationID
903 END,
904 ItemID,
905 PlanIdVar,
906 CASE WHEN ( ZoneID is not null )
907 THEN -1
908 ELSE
909 InstanceID
910 END,
911 MSC_DEMANDS_S.NEXTVAL,
912 81,
913 Demand,
914 Satisfied,
915 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
916 CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':') = 0 )
917 THEN TO_NUMBER(ZoneID)
918 ELSE
919 NULL
920 END,
921 CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':',1,1) is not null )
922 THEN SUBSTR(ZoneID, INSTR(ZoneID,':',1,1)+1) -- CUSTOMER_SITE_ID
923 ELSE
924 NULL
925 END,
926 CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':',1,1) is not null )
927 THEN SUBSTR(ZoneID, 1, INSTR(ZoneID,':',1,1)-1) -- CUSTOMER_ID
928 ELSE
929 NULL
930 END,
931 0,
932 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
933 ItemID,
934 1,
935 Demand-Satisfied,
936 SYSDATE, '-1', SYSDATE, '-1'
937 FROM MSC_INT_ASCP_DEMANDS
938 WHERE PLAN_ID=PlanIdVar;
939 EXCEPTION WHEN others THEN
940 g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_DEMAND_001001';
941 raise;
942 END;
943 BEGIN
944 -- populate MSC_DEMANDS from ASCP Dependent Demand
945 --
946 INSERT INTO MSC_DEMANDS (
947 ORGANIZATION_ID,
948 INVENTORY_ITEM_ID,
949 PLAN_ID,
950 SR_INSTANCE_ID,
951 DEMAND_ID,
952 ORIGINATION_TYPE,
953 USING_REQUIREMENT_QUANTITY,
954 DMD_SATISFIED_DATE,
955 USING_ASSEMBLY_DEMAND_DATE,
956 USING_ASSEMBLY_ITEM_ID,
957 DEMAND_TYPE,
958 LAST_UPDATE_DATE,
959 LAST_UPDATED_BY,
960 CREATION_DATE,
961 CREATED_BY)
962 SELECT
963 FromOrgID,
964 ItemID,
965 PlanIdVar,
966 InstanceID,
967 MSC_DEMANDS_S.NEXTVAL, --DEMAND_ID,
968 1, --ORIGINATION_TYPE,
969 Quantity, --BUGBUG: how to show Infinity ? --USING_REQUIREMENT_QUANTITY
970 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, --DMD_SATISFIED_DATE
971 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, --USING_ASSEMBLY_DEMAND_DATE
972 ItemID, --USING_ASSEMBLY_ITEM_ID
973 1, --DEMAND_TYPE
974 SYSDATE, '-1', SYSDATE, '-1'
975 FROM MSC_INT_ASCP_DEPENDENT_DEMAND
976 WHERE PLAN_ID=PlanIdVar;
977 EXCEPTION WHEN others THEN
978 g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_DEPENDENT_DEMAND_001002';
979 raise;
980 END;
981
982 BEGIN
983 -- CODE GOES HERE
984 --
985 -- populate MSC_DEMANDS from ASCP Transportation (Move Order)
986 --
987 INSERT INTO MSC_DEMANDS (ORGANIZATION_ID,
988 INVENTORY_ITEM_ID,
989 PLAN_ID,
990 SR_INSTANCE_ID,
991 DEMAND_ID,
992 ORIGINATION_TYPE,
993 USING_REQUIREMENT_QUANTITY,
994 QUANTITY_BY_DUE_DATE,
995 DMD_SATISFIED_DATE,
996 ZONE_ID,
997 CUSTOMER_SITE_ID,
998 CUSTOMER_ID,
999 SERVICE_LEVEL,
1000 USING_ASSEMBLY_DEMAND_DATE,
1001 USING_ASSEMBLY_ITEM_ID,
1002 DEMAND_TYPE,
1003 LAST_UPDATE_DATE,
1004 LAST_UPDATED_BY,
1005 CREATION_DATE,
1006 CREATED_BY)
1007 SELECT
1008 FromOrgID,
1009 ItemID,
1010 PlanIdVar,
1011 FromInstanceID,
1012 MSC_DEMANDS_S.NEXTVAL,
1013 82, Quantity,
1014 Quantity,
1015 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1016 NULL,
1017 CASE WHEN ( Category = 'Customer' )
1018 THEN ToInstanceID -- CUSTOMER_SITE_ID
1019 ELSE
1020 NULL
1021 END,
1022 CASE WHEN ( Category = 'Customer' )
1023 THEN ToOrgID -- CUSTOMER_ID
1024 ELSE
1025 NULL
1026 END,
1027 0,
1028 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1029 ItemID,
1030 1,
1031 SYSDATE, '-1', SYSDATE, '-1'
1032 FROM MSC_INT_ASCP_TRANSPORTATION
1033 WHERE PLAN_ID=PlanIdVar;
1034 EXCEPTION WHEN others THEN
1035 g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_TRANSPORTATION_001003';
1036 raise;
1037 END;
1038
1039
1040
1041 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1042 -- checkpoint commit;
1043
1044 EXCEPTION
1045 WHEN others THEN
1046 Status := g_ErrorCode;
1047 ROLLBACK;
1048 END SET_ASCP_DEMANDS;
1049
1050 PROCEDURE SET_ASCP_SUPPLIES (
1051 Status OUT NOCOPY VARCHAR2,
1052 PlanIdVar IN NUMBER
1053 ) AS
1054 g_ErrorCode VARCHAR2(1000);
1055 BEGIN
1056 /* implementation starts here */
1057 -- init global variables
1058 g_ErrorCode := '';
1059
1060
1061 -- delete records from MSC_SUPPLIES table for the given PlanIdVar, if any
1062 BEGIN
1063 DELETE FROM MSC_SUPPLIES WHERE PLAN_ID=PlanIdVar;
1064 EXCEPTION WHEN others THEN
1065 NULL; -- do nothing
1066 END;
1067 -- insert new rows
1068
1069
1070 BEGIN
1071 -- CODE GOES HERE
1072 --
1073 -- Fill in data from MSC_INT_ASCP_SUPPLY
1074 --
1075 INSERT INTO MSC_SUPPLIES (PLAN_ID,
1076 TRANSACTION_ID,
1077 ORGANIZATION_ID,
1078 SR_INSTANCE_ID,
1079 INVENTORY_ITEM_ID,
1080 ORDER_TYPE,
1081 NEW_SCHEDULE_DATE,
1082 NEW_ORDER_QUANTITY,
1083 FIRM_PLANNED_TYPE,
1084 SUPPLIER_ID,
1085 SUPPLIER_SITE_ID,
1086 LAST_UPDATE_DATE,
1087 LAST_UPDATED_BY,
1088 CREATION_DATE,
1089 CREATED_BY)
1090 SELECT PlanIdVar, msc_supplies_s.nextval, organizationID,
1091 instanceID, itemID, 1, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1092 supply, 2, SupplierID,
1093 supplierSiteID,
1094 SYSDATE, '-1', SYSDATE, '-1'
1095 FROM MSC_INT_ASCP_SUPPLY
1096 WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_SUPPLY.CATEGORY='Supply';
1097
1098 -- NOTE: Last four fields: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY are non-NULL,
1099 -- so I harcoded an arbitrary value
1100 EXCEPTION WHEN others THEN
1101 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_SUPPLY_001001';
1102 raise;
1103 END;
1104
1105 BEGIN
1106 -- CODE GOES HERE
1107 --
1108 -- Fill in data from MSC_INT_ASCP_TRANSPORTATION
1109 --
1110 INSERT INTO MSC_SUPPLIES (
1111 PLAN_ID,
1112 TRANSACTION_ID,
1113 ORGANIZATION_ID,
1114 SR_INSTANCE_ID,
1115 INVENTORY_ITEM_ID,
1116 ORDER_TYPE,
1117 NEW_SCHEDULE_DATE,
1118 NEW_ORDER_QUANTITY,
1119 FIRM_PLANNED_TYPE,
1120 SOURCE_ORGANIZATION_ID,
1121 SOURCE_SR_INSTANCE_ID,
1122 SHIP_METHOD,
1123 LAST_UPDATE_DATE,
1124 LAST_UPDATED_BY,
1125 CREATION_DATE,
1126 CREATED_BY)
1127 SELECT
1128 PlanIdVar,
1129 msc_supplies_s.nextval,
1130 toOrgID,
1131 toInstanceID,
1132 itemID,
1133 80,
1134 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1135 quantity,
1136 2,
1137 fromOrgID,
1138 fromInstanceID,
1139 transportMode,
1140 SYSDATE, '-1', SYSDATE, '-1'
1141 FROM MSC_INT_ASCP_TRANSPORTATION
1142 WHERE PLAN_ID=PlanIdVar;
1143 EXCEPTION WHEN others THEN
1144 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_ASCP_TRANSPORTATION_001002';
1145 raise;
1146 END;
1147
1148
1149 BEGIN
1150 -- CODE GOES HERE
1151 --
1152 -- Fill in data from MSC_INT_ASCP_INVENTORY
1153 --
1154 INSERT INTO MSC_SUPPLIES (PLAN_ID,
1155 TRANSACTION_ID,
1156 ORGANIZATION_ID,
1157 SR_INSTANCE_ID,
1158 INVENTORY_ITEM_ID,
1159 ORDER_TYPE,
1160 NEW_SCHEDULE_DATE,
1161 NEW_ORDER_QUANTITY,
1162 FIRM_PLANNED_TYPE,
1163 LAST_UPDATE_DATE,
1164 LAST_UPDATED_BY,
1165 CREATION_DATE,
1166 CREATED_BY)
1167 SELECT PlanIdVar, msc_supplies_s.nextval, organizationId, instanceId,
1168 itemID, 18, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, onHand, 2,
1169 SYSDATE, '-1', SYSDATE, '-1'
1170 FROM MSC_INT_ASCP_INVENTORY
1171 WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_INVENTORY.CATEGORY='Inventory';
1172 EXCEPTION WHEN others THEN
1173 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_INVENTORY_001003';
1174 raise;
1175 END;
1176
1177 BEGIN
1178 -- CODE GOES HERE
1179 --
1180 -- Fill in data from MSC_INT_ASCP_MFG_PLAN_MACHINE
1181 -- Note that the temp table has resource and department id
1182 -- to distinguish records but these two ids are not relevant
1183 -- to supplies. As a result if we directly write to the table
1184 -- we will get duplcates. Change the selection base to pick unique rows.
1185 --
1186 INSERT INTO MSC_SUPPLIES (PLAN_ID,
1187 TRANSACTION_ID,
1188 ORGANIZATION_ID,
1189 SR_INSTANCE_ID,
1190 INVENTORY_ITEM_ID,
1191 ORDER_TYPE,
1192 NEW_SCHEDULE_DATE,
1193 NEW_ORDER_QUANTITY,
1194 FIRM_PLANNED_TYPE,
1195 LAST_UPDATE_DATE,
1196 LAST_UPDATED_BY,
1197 CREATION_DATE,
1198 CREATED_BY)
1199 select PlanIdVar, msc_supplies_s.nextval,
1200 organizationID, instanceID, itemID, 88,
1201 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, flow,
1202 2,
1203 SYSDATE, '-1', SYSDATE, '-1'
1204 from
1205 (select distinct organizationID, instanceID, itemID, PeriodEnd, flow
1206 from MSC_INT_ASCP_MFG_PLAN_MACHINE
1207 where PLAN_ID=PlanIdVar AND CATEGORY='Manufacturing');
1208 EXCEPTION WHEN others THEN
1209 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_MFG_PLAN_MACHINE_001004';
1210 raise;
1211 END;
1212
1213
1214 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1215 -- checkpoint commit;
1216
1217 EXCEPTION
1218 WHEN others THEN
1219 Status := g_ErrorCode;
1220 ROLLBACK;
1221
1222 END SET_ASCP_SUPPLIES;
1223
1224
1225
1226 PROCEDURE SET_ASCP_SAFETY_STOCKS (
1227 Status OUT NOCOPY VARCHAR2,
1228 PlanIdVar IN NUMBER
1229 ) AS
1230 g_ErrorCode VARCHAR2(1000);
1231 BEGIN
1232 /* implementation starts here */
1233 -- init global variables
1234 g_ErrorCode := '';
1235
1236
1237 -- delete records from MSC_SAFETY_STOCKS table for the given PlanId, if any
1238 BEGIN
1239 DELETE FROM MSC_SAFETY_STOCKS WHERE PLAN_ID=PlanIdVar;
1240 EXCEPTION WHEN others THEN
1241 NULL; -- do nothing
1242 END;
1243
1244 BEGIN
1245 -- CODE GOES HERE
1246 INSERT INTO MSC_SAFETY_STOCKS (PLAN_ID,
1247 ORGANIZATION_ID,
1248 SR_INSTANCE_ID,
1249 INVENTORY_ITEM_ID,
1250 PERIOD_START_DATE,
1251 SAFETY_STOCK_QUANTITY,
1252 TASK_ID,
1253 LAST_UPDATE_DATE,
1254 LAST_UPDATED_BY,
1255 CREATION_DATE,
1256 CREATED_BY)
1257 SELECT PlanIdVar, OrganizationID, InstanceID,
1258 ItemID, TO_DATE(Period,'YYYY-MM-DD'), Safety,
1259 ROWNUM, --BUGBUG this value is in, just to make index unique
1260 SYSDATE, '-1', SYSDATE, '-1'
1261 FROM MSC_INT_ASCP_INVENTORY
1262 WHERE PLAN_ID=PlanIdVar;
1263 -- NOTE: Last four fields: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY are non-NULL,
1264 -- so I harcoded an arbitrary value
1265 EXCEPTION WHEN others THEN
1266 g_ErrorCode := 'ERROR_UPDATE_MSC_SAFETY_STOCKS_001001';
1267 raise;
1268 END;
1269
1270
1271
1272
1273 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1274 -- checkpoint commit;
1275
1276 EXCEPTION
1277 WHEN others THEN
1278 Status := g_ErrorCode;
1279 ROLLBACK;
1280 END SET_ASCP_SAFETY_STOCKS;
1281
1282
1283 PROCEDURE SET_ASCP_DEPARTMENT_RESOURCES (
1284 Status OUT NOCOPY VARCHAR2,
1285 PlanIdVar IN NUMBER
1286 ) AS
1287 g_ErrorCode VARCHAR2(1000);
1288 BEGIN
1289 /* implementation starts here */
1290 -- init global variables
1291 g_ErrorCode := '';
1292
1293
1294 -- delete records from MSC_DEPARTMENT_RESOURCES table for the given PlanId, if any
1295 BEGIN
1296 DELETE FROM MSC_DEPARTMENT_RESOURCES WHERE PLAN_ID=PlanIdVar;
1297 EXCEPTION WHEN others THEN
1298 NULL; -- do nothing
1299 END;
1300
1301 BEGIN
1302 -- CODE GOES HERE
1303 -- insert records from MSC_INT_ASCP_MACHINE_UTIL
1304 INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
1305 ORGANIZATION_ID,
1306 SR_INSTANCE_ID,
1307 RESOURCE_ID,
1308 DEPARTMENT_ID,
1309 OWNING_DEPARTMENT_ID,
1310 CAPACITY_UNITS,
1311 RESOURCE_TYPE,
1312 RESOURCE_CODE,
1313 RESOURCE_DESCRIPTION,
1314 DEPARTMENT_CODE,
1315 DEPARTMENT_DESCRIPTION,
1316 DEPARTMENT_CLASS,
1317 RESOURCE_GROUP_NAME,
1318 BOTTLENECK_FLAG,
1319 LINE_FLAG,
1320 AGGREGATE_RESOURCE_FLAG,
1321 AVAILABLE_24_HOURS_FLAG,
1322 LAST_UPDATE_DATE,
1323 LAST_UPDATED_BY,
1324 CREATION_DATE,
1325 CREATED_BY)
1326 SELECT DISTINCT MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID,
1327 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID,
1328 MSC_INT_ASCP_MACHINE_UTIL.InstanceID,
1329 MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
1330 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID,
1331 CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.ResourceID = -1
1332 THEN NULL
1333 ELSE MSC_INT_ASCP_MACHINE_UTIL.DepartmentID
1334 END,--OWNING_DEPARTMENT_ID
1335 NULL, --CAPACITY_UNITS: nullable column, resource units
1336 1, -- RESOURCE_TYPE it is a machine
1337 MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1338 MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1339 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1340 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1341 NULL, --DEPARTMENT_CLASS,
1342 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1343 MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1344 MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1345 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1346 MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1347 SYSDATE, '-1', SYSDATE, '-1'
1348 FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1349 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1350 MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1351 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1352 MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1353 WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
1354 EXCEPTION WHEN others THEN
1355 g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001';
1356 raise;
1357 END;
1358
1359 BEGIN
1360 -- CODE GOES HERE
1361 -- insert records from MSC_INT_ASCP_LABOUR_UTIL
1362 INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
1363 ORGANIZATION_ID,
1364 SR_INSTANCE_ID,
1365 RESOURCE_ID,
1366 DEPARTMENT_ID,
1367 OWNING_DEPARTMENT_ID,
1368 CAPACITY_UNITS,
1369 RESOURCE_TYPE,
1370 RESOURCE_CODE,
1371 RESOURCE_DESCRIPTION,
1372 DEPARTMENT_CODE,
1373 DEPARTMENT_DESCRIPTION,
1374 DEPARTMENT_CLASS,
1375 RESOURCE_GROUP_NAME,
1376 BOTTLENECK_FLAG,
1377 LINE_FLAG,
1378 AGGREGATE_RESOURCE_FLAG,
1379 AVAILABLE_24_HOURS_FLAG,
1380 LAST_UPDATE_DATE,
1381 LAST_UPDATED_BY,
1382 CREATION_DATE,
1383 CREATED_BY)
1384 SELECT DISTINCT MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID,
1385 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID,
1386 MSC_INT_ASCP_LABOUR_UTIL.InstanceID,
1387 MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
1388 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID,
1389 CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.ResourceID = -1
1390 THEN NULL
1391 ELSE MSC_INT_ASCP_LABOUR_UTIL.DepartmentID
1392 END,--OWNING_DEPARTMENT_ID
1393 NULL, --CAPACITY_UNITS: nullable column, resource units
1394 2, -- RESOURCE_TYPE - it is a labour
1395 MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1396 MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1397 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1398 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1399 NULL, --DEPARTMENT_CLASS,
1400 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1401 MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1402 MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1403 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1404 MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1405 SYSDATE, '-1', SYSDATE, '-1'
1406 FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1407 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1408 MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1409 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1410 MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1411 WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
1412 EXCEPTION WHEN others THEN
1413 g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002';
1414 raise;
1415 END;
1416
1417
1418
1419 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1420 -- checkpoint commit;
1421
1422 EXCEPTION
1423 WHEN others THEN
1424 Status := g_ErrorCode;
1425 ROLLBACK;
1426 END SET_ASCP_DEPARTMENT_RESOURCES;
1427
1428 PROCEDURE SET_ASCP_RES_SUMMARY (
1429 Status OUT NOCOPY VARCHAR2,
1430 PlanIdVar IN NUMBER,
1431 ScenarioNameVar OUT NOCOPY VARCHAR2
1432 ) AS
1433 g_ErrorCode VARCHAR2(1000);
1434 BEGIN
1435 /* implementation starts here */
1436 -- init global variables
1437 g_ErrorCode := '';
1438 ScenarioNameVar := '';
1439
1440
1441 -- delete records from MSC_RES_SUMMARY table for the given PlanId, if any
1442 BEGIN
1443 DELETE FROM MSC_BIS_RES_SUMMARY WHERE PLAN_ID=PlanIdVar;
1444 EXCEPTION WHEN others THEN
1445 NULL; -- do nothing
1446 END;
1447
1448 BEGIN
1449 select DISTINCT ScenarioName into ScenarioNameVar from MSC_INT_ASCP_KPI where PLAN_ID=PlanIdVar;
1450 EXCEPTION WHEN others THEN
1451 ScenarioNameVar := '';
1452 END;
1453
1454 BEGIN
1455 -- CODE GOES HERE
1456 -- insert records from MSC_INT_ASCP_MACHINE_UTIL
1457 INSERT INTO MSC_BIS_RES_SUMMARY (PLAN_ID,
1458 ORGANIZATION_ID,
1459 SR_INSTANCE_ID,
1460 DEPARTMENT_ID,
1461 RESOURCE_ID,
1462 DETAIL_LEVEL,
1463 PERIOD_TYPE,
1464 RESOURCE_DATE,
1465 REQUIRED_HOURS,
1466 AVAILABLE_HOURS,
1467 UTILIZATION,
1468 RESOURCE_GROUP,
1469 OVERUTILIZATION_COST,
1470 RESOURCE_COST)
1471 SELECT MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID, MSC_INT_ASCP_MACHINE_UTIL.OrganizationID,
1472 MSC_INT_ASCP_MACHINE_UTIL.InstanceID, MSC_INT_ASCP_MACHINE_UTIL.DepartmentID, MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
1473 CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
1474 TO_DATE(MSC_INT_ASCP_MACHINE_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_MACHINE_UTIL.USED,
1475 MSC_INT_ASCP_MACHINE_UTIL.CAPACITY, MSC_INT_ASCP_MACHINE_UTIL.UTILIZATION,
1476 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_MACHINE_UTIL.OVERCOST,
1477 MSC_INT_ASCP_KPI.PRODUCTIONCOST
1478 FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1479 WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1480 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1481 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1482 MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1483 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1484 MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1485 MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar AND
1486 MSC_INT_ASCP_KPI.INSTANCEID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1487 MSC_INT_ASCP_KPI.ORGANIZATIONID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1488 MSC_INT_ASCP_KPI.DEPARTMENTID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND -- toggled dept and resource id because in KPI export
1489 MSC_INT_ASCP_KPI.ITEMID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND -- the dept and item are toggled due to lack of category
1490 MSC_INT_ASCP_KPI.PERIODEND = MSC_INT_ASCP_MACHINE_UTIL.PERIODEND;
1491 EXCEPTION WHEN others THEN
1492 g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001';
1493 raise;
1494 END;
1495
1496 BEGIN
1497 -- CODE GOES HERE
1498 -- insert records from MSC_INT_ASCP_LABOUR_UTIL
1499 INSERT INTO MSC_BIS_RES_SUMMARY (PLAN_ID,
1500 ORGANIZATION_ID,
1501 SR_INSTANCE_ID,
1502 DEPARTMENT_ID,
1503 RESOURCE_ID,
1504 DETAIL_LEVEL,
1505 PERIOD_TYPE,
1506 RESOURCE_DATE,
1507 REQUIRED_HOURS,
1508 AVAILABLE_HOURS,
1509 UTILIZATION,
1510 RESOURCE_GROUP,
1511 OVERUTILIZATION_COST,
1512 RESOURCE_COST)
1513 SELECT MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID, MSC_INT_ASCP_LABOUR_UTIL.OrganizationID,
1514 MSC_INT_ASCP_LABOUR_UTIL.InstanceID, MSC_INT_ASCP_LABOUR_UTIL.DepartmentID,
1515 MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
1516 CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
1517 TO_DATE(MSC_INT_ASCP_LABOUR_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_LABOUR_UTIL.USED,
1518 MSC_INT_ASCP_LABOUR_UTIL.CAPACITY, MSC_INT_ASCP_LABOUR_UTIL.UTILIZATION,
1519 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_LABOUR_UTIL.OVERCOST,
1520 MSC_INT_ASCP_KPI.PRODUCTIONCOST
1521 FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1522 WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1523 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1524 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1525 MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1526 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1527 MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1528 MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar AND
1529 MSC_INT_ASCP_KPI.INSTANCEID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1530 MSC_INT_ASCP_KPI.ORGANIZATIONID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1531 MSC_INT_ASCP_KPI.ITEMID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1532 MSC_INT_ASCP_KPI.DEPARTMENTID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1533 MSC_INT_ASCP_KPI.PERIODEND = MSC_INT_ASCP_LABOUR_UTIL.PERIODEND;
1534 EXCEPTION WHEN others THEN
1535 g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002';
1536 raise;
1537 END;
1538
1539
1540
1541 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1542 -- checkpoint commit;
1543
1544 EXCEPTION
1545 WHEN others THEN
1546 Status := g_ErrorCode;
1547 ROLLBACK;
1548 END SET_ASCP_RES_SUMMARY;
1549
1550 PROCEDURE SET_ASCP_BIS_INV_DETAIL (
1551 Status OUT NOCOPY VARCHAR2,
1552 PlanIdVar IN NUMBER
1553 ) AS
1554 g_ErrorCode VARCHAR2(1000);
1555 BEGIN
1556 /* implementation starts here */
1557 -- init global variables
1558 g_ErrorCode := '';
1559
1560
1561 -- delete all from temporary tables to aid in the calculation of ProductionCost in the main procedure...
1562
1563 BEGIN
1564 DELETE FROM msc_int_ascp_prodcost_kpi; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar;
1565 EXCEPTION WHEN others THEN
1566 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001001';
1567 raise;
1568 END;
1569
1570 BEGIN
1571 DELETE FROM msc_int_ascp_totalprodcost; -- where msc_int_ascp_totalprodcost.plan_id=PlanIdVar;
1572 EXCEPTION WHEN others THEN
1573 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001002';
1574 raise;
1575 END;
1576
1577 BEGIN
1578 DELETE FROM msc_int_ascp_transpcost_kpi; -- where msc_int_ascp_transpcost_kpi.plan_id=PlanIdVar;
1579 EXCEPTION WHEN others THEN
1580 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001003';
1581 raise;
1582 END;
1583
1584 -- delete records from MSC_BIS_INV_DETAIL table for the given PlanId, if any
1585 BEGIN
1586 DELETE FROM MSC_BIS_INV_DETAIL WHERE PLAN_ID=PlanIdVar;
1587 EXCEPTION WHEN others THEN
1588 NULL; -- do nothing
1589 END;
1590
1591 BEGIN
1592 -- CODE GOES HERE
1593 -- fill in data from msc_int_ascp_mfg_plan_labour and msc_int_ascp_mfg_plan_machine
1594 INSERT INTO msc_int_ascp_prodcost_kpi ( plan_id,
1595 organizationID,
1596 instanceID,
1597 itemID,
1598 periodEnd,
1599 prodCost )
1600 SELECT plan_id, organizationID, instanceID, itemID, periodEnd, SUM(resourcecost*resourceusage)
1601 FROM msc_int_ascp_mfg_plan_machine
1602 WHERE plan_id=PlanIdVar
1603 GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
1604 EXCEPTION WHEN others THEN
1605 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001004';
1606 raise;
1607 END;
1608
1609 BEGIN
1610 -- CODE GOES HERE
1611 -- fill in data from msc_int_ascp_mfg_plan_labour and msc_int_ascp_mfg_plan_machine
1612 INSERT INTO msc_int_ascp_prodcost_kpi ( plan_id,
1613 organizationID,
1614 instanceID,
1615 itemID,
1616 periodEnd,
1617 prodCost )
1618 SELECT plan_id, organizationID, instanceID, itemID, periodEnd, SUM
1619 (resourcecost*resourceusage)
1620 FROM msc_int_ascp_mfg_plan_labour
1621 WHERE plan_id=PlanIdVar
1622 GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
1623 EXCEPTION WHEN others THEN
1624 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001005';
1625 raise;
1626 END;
1627
1628 BEGIN
1629 -- CODE GOES HERE
1630 -- fill in data from msc_int_ascp_prodcost_kpi
1631 INSERT INTO msc_int_ascp_totalprodcost ( plan_id,
1632 organizationID,
1633 instanceID,
1634 itemID,
1635 periodEnd,
1636 totalProdCost )
1637 SELECT msc_int_ascp_prodcost_kpi.plan_id,
1638 msc_int_ascp_prodcost_kpi.organizationID, msc_int_ascp_prodcost_kpi.instanceID,
1639 msc_int_ascp_prodcost_kpi.itemID, msc_int_ascp_prodcost_kpi.periodEnd,
1640 SUM(msc_int_ascp_prodcost_kpi.prodCost)
1641 FROM msc_int_ascp_prodcost_kpi, MSC_INT_ASCP_KPI
1642 WHERE MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar AND
1643 MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null AND
1644 msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar AND
1645
1646 msc_int_ascp_prodcost_kpi.organizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1647 msc_int_ascp_prodcost_kpi.itemID=MSC_INT_ASCP_KPI.ItemID AND
1648 msc_int_ascp_prodcost_kpi.periodEnd=MSC_INT_ASCP_KPI.PERIODEND AND
1649 msc_int_ascp_prodcost_kpi.instanceID=MSC_INT_ASCP_KPI.InstanceID
1650 GROUP BY msc_int_ascp_prodcost_kpi.plan_id,
1651 msc_int_ascp_prodcost_kpi.organizationID, msc_int_ascp_prodcost_kpi.instanceID,
1652 msc_int_ascp_prodcost_kpi.itemID, msc_int_ascp_prodcost_kpi.PeriodEnd;
1653 EXCEPTION WHEN others THEN
1654 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001006';
1655 raise;
1656 END;
1657
1658 BEGIN
1659 -- CODE GOES HERE
1660 INSERT INTO msc_int_ascp_transpcost_kpi ( plan_id,
1661 organizationID,
1662 instanceID,
1663 itemID,
1664 periodEnd,
1665 transpCost )
1666 SELECT DISTINCT plan_id, organizationID, instanceID, itemID, periodEnd, sum
1667 (transportationcost)
1668 FROM msc_int_ascp_kpi
1669 WHERE plan_id=PlanIdVar and transportationmode is not null
1670 GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
1671 EXCEPTION WHEN others THEN
1672 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001007';
1673 raise;
1674 END;
1675 -- final table: MSC_BIS_INV_DETAIL
1676 BEGIN
1677 -- CODE GOES HERE
1678 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
1679 ORGANIZATION_ID,
1680 SR_INSTANCE_ID,
1681 INVENTORY_ITEM_ID,
1682 DETAIL_LEVEL,
1683 PERIOD_TYPE,
1684 DETAIL_DATE,
1685 MDS_PRICE,
1686 ZONE_ID,
1687 PRODUCTION_COST,
1688 PURCHASING_COST,
1689 CARRYING_COST,
1690 TRANSPORTATION_COST,
1691 OTHER_COST,
1692 PAB,
1693 TOTAL_COST,
1694 SERVICE_LEVEL_1,
1695 SERVICE_LEVEL_2,
1696 SHIP_METHOD,
1697 SUPPLIER_ID,
1698 SUPPLIER_SITE_ID,
1699 SUPPLIER_USAGE,
1700 SUPPLIER_CAPACITY,
1701 MDS_QUANTITY,
1702 INVENTORY_QUANTITY,
1703 LAST_UPDATE_DATE,
1704 LAST_UPDATED_BY,
1705 CREATION_DATE,
1706 CREATED_BY)
1707 --------------------------------
1708 -- Supplier-denpendent records
1709 --------------------------------
1710 SELECT MSC_INT_ASCP_KPI.PLAN_ID,
1711 MSC_INT_ASCP_KPI.OrganizationID,
1712 MSC_INT_ASCP_KPI.InstanceID,
1713 MSC_INT_ASCP_KPI.ItemID,
1714 MSC_INT_ASCP_KPI.PeriodType,
1715 1,
1716 TO_DATE( MSC_INT_ASCP_KPI.PeriodEnd,'YYYY-MM-DD' )-1/86400,
1717 0, --TO_NUMBER(NULL) MDS_PRICE,
1718 TO_NUMBER(NULL), -- ZONEID,
1719 0, --TO_NUMBER(NULL) PRODUCTION_COST,
1720 -- supplier related column: purchase cost
1721 case when MSC_INT_ASCP_SUPPLY.COST * MSC_INT_ASCP_SUPPLY.SUPPLY is
1722 null then 0
1723 else MSC_INT_ASCP_SUPPLY.COST * MSC_INT_ASCP_SUPPLY.SUPPLY end,
1724 0,--TO_NUMBER(NULL) CARRYING_COST,
1725 0,--TO_NUMBER(NULL) TRANSPORTATION_COST,
1726 0,--TO_NUMBER(NULL) OTHER_COST,
1727 0,--TO_NUMBER(NULL) PAB,
1728 0,--TO_NUMBER(NULL) TOTAL_COST,
1729 0,--TO_NUMBER(NULL) SERVICE_LEVEL_1,
1730 0,--TO_NUMBER(NULL) SERVICE_LEVEL_2,
1731 null,--TO_CHAR(NULL) SHIP_METHOD,
1732 -- supplier related columns
1733 MSC_INT_ASCP_SUPPLY.SupplierID,
1734 MSC_INT_ASCP_SUPPLY.SupplierSiteID,
1735 MSC_INT_ASCP_SUPPLY.SUPPLY,
1736 MSC_INT_ASCP_SUPPLY.CAPACITY,
1737 0,--TO_NUMBER(NULL) MDS_QUANTITY,
1738 0,--TO_NUMBER(NULL) INVENTORY_QUANTITY,
1739 SYSDATE, '-1', SYSDATE, '-1'
1740 FROM MSC_INT_ASCP_KPI, MSC_INT_ASCP_SUPPLY
1741 WHERE MSC_INT_ASCP_SUPPLY.PLAN_ID=MSC_INT_ASCP_KPI.PLAN_ID AND
1742 MSC_INT_ASCP_SUPPLY.OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1743 MSC_INT_ASCP_SUPPLY.ItemID=MSC_INT_ASCP_KPI.ItemID AND
1744 MSC_INT_ASCP_SUPPLY.PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1745 MSC_INT_ASCP_SUPPLY.InstanceID=MSC_INT_ASCP_KPI.InstanceID AND
1746 MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar;
1747 EXCEPTION WHEN others THEN
1748 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001008';
1749 raise;
1750 END;
1751 BEGIN
1752 --------------------------------
1753 -- Supplier-indenpendent records
1754 --------------------------------
1755 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
1756 ORGANIZATION_ID,
1757 SR_INSTANCE_ID,
1758 INVENTORY_ITEM_ID,
1759 DETAIL_LEVEL,
1760 PERIOD_TYPE,
1761 DETAIL_DATE,
1762 MDS_PRICE,
1763 ZONE_ID,
1764 PRODUCTION_COST,
1765 PURCHASING_COST,
1766 CARRYING_COST,
1767 TRANSPORTATION_COST,
1768 OTHER_COST,
1769 PAB,
1770 TOTAL_COST,
1771 SERVICE_LEVEL_1,
1772 SERVICE_LEVEL_2,
1773 SHIP_METHOD,
1774 SUPPLIER_ID,
1775 SUPPLIER_SITE_ID,
1776 SUPPLIER_USAGE,
1777 SUPPLIER_CAPACITY,
1778 MDS_QUANTITY,
1779 INVENTORY_QUANTITY,
1780 LAST_UPDATE_DATE,
1781 LAST_UPDATED_BY,
1782 CREATION_DATE,
1783 CREATED_BY)
1784 SELECT MSC_INT_ASCP_KPI.PLAN_ID,
1785 MSC_INT_ASCP_KPI.OrganizationID,
1786 MSC_INT_ASCP_KPI.InstanceID,
1787 MSC_INT_ASCP_KPI.ItemID,
1788 MSC_INT_ASCP_KPI.PeriodType,
1789 1,
1790 TO_DATE( MSC_INT_ASCP_KPI.PeriodEnd,'YYYY-MM-DD' )-1/86400,
1791 -- demand cost
1792 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
1793 MSC_INT_ASCP_KPI.DemandCost else 0 end,
1794 MSC_INT_ASCP_KPI.ZoneID,
1795 -- production_cost
1796 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null
1797 then (
1798 case when (SELECT COUNT(*) FROM msc_int_ascp_totalprodcost
1799 WHERE PLAN_ID=PlanIdVar AND
1800 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID
1801 AND
1802 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1803 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1804 InstanceID=MSC_INT_ASCP_KPI.InstanceID
1805 )=0 then 0
1806 else (SELECT DISTINCT totalProdCost FROM
1807 msc_int_ascp_totalprodcost
1808 WHERE PLAN_ID=PlanIdVar AND
1809 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1810 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1811 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1812 InstanceID=MSC_INT_ASCP_KPI.InstanceID)
1813 end)
1814 else 0 end,
1815 -- supplier related column: purchase cost
1816 0, --TO_NUMBER(NULL) PURCHASING_COST,
1817 -- carrying cost
1818 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
1819 MSC_INT_ASCP_KPI.InventoryCost else 0 end,
1820 -- transportastion cost
1821 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then 0
1822 else MSC_INT_ASCP_KPI.TRANSPORTATIONCOST end,
1823 -- other cost
1824 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
1825 MSC_INT_ASCP_KPI.OtherCost else 0 end,
1826 -- PAB
1827 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null
1828 then (
1829 case when (SELECT COUNT(*) FROM MSC_INT_ASCP_INVENTORY
1830 WHERE PLAN_ID=PlanIdVar AND
1831 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID
1832 AND
1833 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1834 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1835 InstanceID=MSC_INT_ASCP_KPI.InstanceID
1836 )=0 then 0
1837 else (SELECT DISTINCT StorageAmount FROM MSC_INT_ASCP_INVENTORY
1838 WHERE
1839 PLAN_ID=PlanIdVar AND
1840 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1841 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1842 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1843 InstanceID=MSC_INT_ASCP_KPI.InstanceID )
1844 end)
1845 else
1846 0
1847 end,
1848 -- total cost
1849 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
1850 -- add demand cost
1851 case when MSC_INT_ASCP_KPI.DEMANDCOST > 0 then
1852 MSC_INT_ASCP_KPI.DEMANDCOST
1853 else 0 end +
1854 -- purchasing cost is by supplier, not added here
1855 -- production cost
1856 case when (SELECT COUNT(*) FROM msc_int_ascp_totalprodcost
1857 WHERE PLAN_ID=PlanIdVar AND
1858 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID
1859 AND
1860 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1861 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1862 InstanceID=MSC_INT_ASCP_KPI.InstanceID
1863 )=0 then 0
1864 else (SELECT DISTINCT totalProdCost FROM
1865 msc_int_ascp_totalprodcost
1866 WHERE PLAN_ID=PlanIdVar AND
1867 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1868 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1869 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1870 InstanceID=MSC_INT_ASCP_KPI.InstanceID)
1871 end +
1872 -- transportation cost
1873 case when (SELECT COUNT(*) FROM msc_int_ascp_transpcost_kpi
1874 WHERE PLAN_ID=PlanIdVar AND
1875 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID
1876 AND
1877 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1878 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1879 InstanceID=MSC_INT_ASCP_KPI.InstanceID
1880 )=0 then 0
1881 else (SELECT DISTINCT TRANSPCOST FROM
1882 msc_int_ascp_transpcost_kpi
1883 WHERE PLAN_ID=PlanIdVar AND
1884 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1885 ItemID=MSC_INT_ASCP_KPI.ItemID AND
1886 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
1887 InstanceID=MSC_INT_ASCP_KPI.InstanceID)
1888 end +
1889 MSC_INT_ASCP_KPI.INVENTORYCOST +
1890 MSC_INT_ASCP_KPI.OTHERCOST
1891 else 0 end,
1892 case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
1893 MSC_INT_ASCP_KPI.DemandFillRate
1894 else 0 end,
1895 1,
1896 MSC_INT_ASCP_KPI.TRANSPORTATIONMODE,
1897 -- supplier related columns
1898 TO_NUMBER(NULL), --SUPPLIER_ID,
1899 TO_NUMBER(NULL), --SUPPLIER_SITE_ID,
1900 0,-- TO_NUMBER(NULL) SUPPLIER_USAGE,
1901 0,-- TO_NUMBER(NULL) SUPPLIER_CAPACITY,
1902 0,
1903 0,
1904 SYSDATE, '-1', SYSDATE, '-1'
1905 FROM MSC_INT_ASCP_KPI
1906 WHERE MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar;
1907 EXCEPTION WHEN others THEN
1908 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001009';
1909 raise;
1910
1911 END;
1912
1913 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1914 -- checkpoint commit;
1915
1916 EXCEPTION
1917 WHEN others THEN
1918 Status := g_ErrorCode;
1919 ROLLBACK;
1920 END SET_ASCP_BIS_INV_DETAIL;
1921
1922 PROCEDURE SET_ASCP_SRC_RECOMMEND_DETAIL (
1923 Status OUT NOCOPY VARCHAR2,
1924 PlanIdVar IN NUMBER,
1925 AssignmentSetOutIdVar IN NUMBER
1926 ) AS
1927 g_ErrorCode VARCHAR2(1000);
1928 BEGIN
1929 /* implementation starts here */
1930 -- 1. and 2. drop, create, populate 2 temp tables msc_int_source1, msc_int_source2
1931 BEGIN
1932 DELETE FROM msc_int_source1; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar;
1933 EXCEPTION WHEN others THEN
1934 g_ErrorCode := 'ERROR_DELETE_msc_int_source1_001011';
1935 raise;
1936 END;
1937
1938 BEGIN
1939 DELETE FROM msc_int_source2; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar;
1940 EXCEPTION WHEN others THEN
1941 g_ErrorCode := 'ERROR_DELETE_msc_int_source2_001012';
1942 raise;
1943 END;
1944 BEGIN
1945 -- 1. SourceItem
1946
1947 INSERT into msc_int_source1 ( item_code, branch_code )
1948 SELECT msc_int_src_recommend_detail.item_code,
1949 msc_int_src_recommend_detail.destination_code
1950 FROM msc_int_src_recommend_detail
1951 WHERE msc_int_src_recommend_detail.available = 'Yes';
1952 EXCEPTION WHEN others THEN
1953 g_ErrorCode := 'ERROR_INSERT_msc_int_source1_001021';
1954 raise;
1955 END;
1956
1957 BEGIN
1958 -- 2. SourceItemBranch
1959
1960 INSERT into msc_int_source2 ( item_code, branch_code, enable_date, disable_date )
1961 SELECT msc_int_src_recommend_detail.item_code,
1962 msc_int_src_recommend_detail.destination_code,
1963 TO_DATE(msc_int_src_recommend_detail.start_date, 'YYYY-MM-DD'),
1964 TO_DATE(msc_int_src_recommend_detail.end_date, 'YYYY-MM-DD')
1965 FROM msc_int_src_recommend_detail
1966 WHERE msc_int_src_recommend_detail.available = 'Yes';
1967 EXCEPTION WHEN others THEN
1968 g_ErrorCode := 'ERROR_INSERT_msc_int_source2_001022';
1969 raise;
1970 END;
1971 -- 3. clear msc_sr_source_org
1972 g_ErrorCode := '';
1973 BEGIN
1974 DELETE from MSC_SR_SOURCE_ORG
1975 WHERE
1976 MSC_SR_SOURCE_ORG.SR_RECEIPT_ID in
1977 ( select MSC_SR_RECEIPT_ORG.SR_RECEIPT_ID from MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG
1978 where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
1979 MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID = MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID );
1980 EXCEPTION WHEN others THEN
1981 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001001';
1982 raise;
1983 END;
1984 -- 4. clear msc_sr_receipt_org
1985 BEGIN
1986 DELETE from MSC_SR_RECEIPT_ORG
1987 WHERE
1988 MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID in
1989 ( select MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID from MSC_SR_ASSIGNMENTS
1990 where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar );
1991 EXCEPTION WHEN others THEN
1992 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001002';
1993 NULL;
1994 END;
1995 -- 5. clear msc_sourcing_rules
1996 BEGIN
1997 DELETE from MSC_SOURCING_RULES
1998 WHERE
1999 MSC_SOURCING_RULES.SOURCING_RULE_ID in
2000 ( select MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID from MSC_SR_ASSIGNMENTS
2001 where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar );
2002 EXCEPTION WHEN others THEN
2003 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001003';
2004 raise;
2005 END;
2006 -- 6. clear msc_sr_assignments
2007 BEGIN
2008 DELETE from MSC_SR_ASSIGNMENTS
2009 WHERE
2010 MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar;
2011 EXCEPTION WHEN others THEN
2012 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001004';
2013 raise;
2014 END;
2015 -- 7. Insert into MSC_SR_ASSIGNMENTS
2016 BEGIN
2017 INSERT into MSC_SR_ASSIGNMENTS (
2018 ASSIGNMENT_ID,
2019 SR_ASSIGNMENT_ID,
2020 SR_ASSIGNMENT_INSTANCE_ID,
2021 ASSIGNMENT_SET_ID,
2022 ASSIGNMENT_TYPE,
2023 SOURCING_RULE_ID,
2024 SOURCING_RULE_TYPE,
2025 ORGANIZATION_ID,
2026 SR_INSTANCE_ID,
2027 INVENTORY_ITEM_ID,
2028 LAST_UPDATE_DATE,
2029 LAST_UPDATED_BY,
2030 CREATION_DATE,
2031 CREATED_BY)
2032 SELECT
2033 MSC_SR_ASSIGNMENTS_S.NEXTVAL,
2034 -1 * MSC_SR_ASSIGNMENTS_S.NEXTVAL, -- Part of unique key. Maybe should use current value.
2035 -1, -- // -1 until we figure out what goes here.
2036 assignmentSetOutIdVar,
2037 6, -- // 6 is item-org assignment type
2038 MSC_SOURCING_RULES_S.NEXTVAL, -- //Cache these because we cannot look them up by name
2039 1,
2040 substr(msc_int_source1.branch_code,instr(msc_int_source1.branch_code,':',1,1)+1),
2041 substr(msc_int_source1.item_code,1,instr(msc_int_source1.item_code,':',1,1)-1),
2042 substr(msc_int_source1.item_code,instr(msc_int_source1.item_code,':',1,1)+1), -- CHANGE
2043 sysdate,
2044 '-1',
2045 sysdate,
2046 '-1'
2047 FROM msc_int_source1;
2048 EXCEPTION WHEN others THEN
2049 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001005';
2050 raise;
2051 END;
2052 -- 8. INSERT into MSC_SOURCING_RULES
2053
2054 BEGIN
2055 INSERT into MSC_SOURCING_RULES (
2056 SOURCING_RULE_ID,
2057 SR_SOURCING_RULE_ID,
2058 SR_INSTANCE_ID,
2059 ORGANIZATION_ID,
2060 SOURCING_RULE_NAME,
2061 STATUS,
2062 SOURCING_RULE_TYPE,
2063 PLANNING_ACTIVE,
2064 LAST_UPDATE_DATE,
2065 LAST_UPDATED_BY,
2066 CREATION_DATE,
2067 CREATED_BY,
2068 DELETED_FLAG )
2069 SELECT DISTINCT
2070 MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
2071 -1 * MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
2072 MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID, -- // I suppose these are the same.
2073 MSC_SR_ASSIGNMENTS.ORGANIZATION_ID,
2074 (MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_NAME || ':' || MSC_SYSTEM_ITEMS.ITEM_NAME || ':' || MSC_PLAN_ORGANIZATIONS.ORGANIZATION_CODE),
2075 1, -- // STATUS
2076 1, -- // 1 means sourcing rule type
2077 1,
2078 sysdate,
2079 '-1',
2080 sysdate,
2081 '-1',
2082 '2'
2083 FROM MSC_SR_ASSIGNMENTS , MSC_ASSIGNMENT_SETS , MSC_SYSTEM_ITEMS , MSC_PLAN_ORGANIZATIONS -- ( alias org )
2084 WHERE
2085 MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
2086 MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID = MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID and
2087 MSC_SYSTEM_ITEMS.SR_INSTANCE_ID = MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID and
2088 MSC_SYSTEM_ITEMS.ORGANIZATION_ID = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID and
2089 MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID = MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID and
2090 MSC_SYSTEM_ITEMS.PLAN_ID = -1 and
2091 MSC_PLAN_ORGANIZATIONS.PLAN_ID = planIdVar and
2092 MSC_PLAN_ORGANIZATIONS.SR_INSTANCE_ID = MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID and
2093 MSC_PLAN_ORGANIZATIONS.ORGANIZATION_ID = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID;
2094 EXCEPTION WHEN others THEN
2095 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001006';
2096 raise;
2097 END;
2098 -- 9. INSERT into MSC_SR_RECEIPT_ORG
2099 BEGIN
2100 INSERT into MSC_SR_RECEIPT_ORG (
2101 SR_RECEIPT_ID,
2102 SR_SR_RECEIPT_ID,
2103 SR_INSTANCE_ID,
2104 SR_RECEIPT_ORG,
2105 RECEIPT_ORG_INSTANCE_ID,
2106 SOURCING_RULE_ID,
2107 RECEIPT_PARTNER_ID,
2108 RECEIPT_PARTNER_SITE_ID,
2109 EFFECTIVE_DATE,
2110 DISABLE_DATE,
2111 LAST_UPDATE_DATE,
2112 LAST_UPDATED_BY,
2113 CREATION_DATE,
2114 CREATED_BY
2115 ) -- // msc_int_source2 == source
2116 SELECT
2117 MSC_SR_RECEIPT_ORG_S.NEXTVAL,
2118 -1 * MSC_SR_RECEIPT_ORG_S.NEXTVAL,
2119 substr(msc_int_source2.branch_code,1,instr(msc_int_source2.branch_code,':',1,1)-1), -- // I suspect I am supposed to put something here but I don't know what.
2120 substr(msc_int_source2.branch_code,instr(msc_int_source2.branch_code,':',1,1)+1),
2121 substr(msc_int_source2.branch_code,1,instr(msc_int_source2.branch_code,':',1,1)-1), -- // Eventually this should be obtained from the branch code.
2122 MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
2123 NULL, -- This is empty until we get customer sourcing
2124 NULL, -- This is empty until we get customer sourcing
2125 msc_int_source2.enable_date,
2126 msc_int_source2.disable_date,
2127 sysdate,
2128 '-1',
2129 sysdate,
2130 '-1'
2131 FROM MSC_SR_ASSIGNMENTS, msc_int_source2
2132 WHERE
2133 MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
2134 MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID || ':' || MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID = msc_int_source2.item_code and
2135 MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID || ':' || MSC_SR_ASSIGNMENTS.ORGANIZATION_ID = msc_int_source2.branch_code;
2136 EXCEPTION WHEN others THEN
2137 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001007';
2138 raise;
2139 END;
2140 -- 10. INSERT into MSC_SR_SOURCE_ORG
2141 BEGIN
2142 INSERT into MSC_SR_SOURCE_ORG (
2143 SR_SOURCE_ID,
2144 SR_SR_SOURCE_ID,
2145 SR_RECEIPT_ID,
2146 SOURCE_PARTNER_ID,
2147 SOURCE_PARTNER_SITE_ID,
2148 SR_INSTANCE_ID,
2149 SOURCE_ORGANIZATION_ID,
2150 SOURCE_ORG_INSTANCE_ID,
2151 SHIP_METHOD,
2152 ALLOCATION_PERCENT,
2153 RANK,
2154 SOURCE_TYPE,
2155 LAST_UPDATE_DATE,
2156 LAST_UPDATED_BY,
2157 CREATION_DATE,
2158 CREATED_BY
2159 )
2160 SELECT
2161 MSC_SR_SOURCE_ORG_S.NEXTVAL,
2162 -1 * MSC_SR_SOURCE_ORG_S.NEXTVAL,
2163 MSC_SR_RECEIPT_ORG.SR_RECEIPT_ID,
2164 case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2165 then substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1)
2166 else NULL
2167 end,
2168 case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2169 then substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )
2170 else NULL
2171 end,
2172 MSC_SR_RECEIPT_ORG.SR_INSTANCE_ID,
2173 case when not MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2174 then substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )
2175 else NULL
2176 end,
2177 case when not MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2178 then substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1)
2179 else NULL
2180 end,
2181 case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Internal'
2182 then MSC_INT_SRC_RECOMMEND_DETAIL.transport_mode_code
2183 end,
2184 NVL(MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_percent,100),
2185 MSC_INT_SRC_RECOMMEND_DETAIL.preference,
2186 case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Internal' then 1
2187 when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Manufactured' then 2
2188 when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier' then 3
2189 else 1
2190 end,
2191 sysdate,
2192 '-1',
2193 sysdate,
2194 '-1'
2195 FROM MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG, MSC_INT_SRC_RECOMMEND_DETAIL
2196 WHERE MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar
2197 AND MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID = MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID
2198 AND MSC_SR_RECEIPT_ORG.SR_RECEIPT_ORG = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID
2199 AND MSC_SR_RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID || ':' || MSC_SR_RECEIPT_ORG.SR_RECEIPT_ORG = MSC_INT_SRC_RECOMMEND_DETAIL.destination_code
2200 AND MSC_SR_RECEIPT_ORG.EFFECTIVE_DATE = TO_DATE( MSC_INT_SRC_RECOMMEND_DETAIL.start_date, 'YYYY-MM-DD' )
2201 AND MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID = substr(MSC_INT_SRC_RECOMMEND_DETAIL.item_code,instr(MSC_INT_SRC_RECOMMEND_DETAIL.item_code,':',1)+1)
2202 AND MSC_INT_SRC_RECOMMEND_DETAIL.available = 'Yes';
2203 EXCEPTION WHEN others THEN
2204 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001007';
2205 raise;
2206 END;
2207 Status := 'SUCCESS';
2208
2209
2210 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
2211 -- checkpoint commit;
2212
2213 EXCEPTION
2214 WHEN others THEN
2215 Status := g_ErrorCode;
2216 ROLLBACK;
2217 END SET_ASCP_SRC_RECOMMEND_DETAIL;
2218
2219
2220
2221 -- =============================================================
2222 --
2223 -- Helper functions used defined in MSC_WS_COMMON package ( copies here ).
2224 -- =============================================================
2225
2226 -- get plan name from plan Id
2227
2228 FUNCTION GET_PLAN_NAME_BY_PLAN_ID(
2229 Status OUT NOCOPY VARCHAR2,
2230 PlanId IN NUMBER
2231 ) RETURN BOOLEAN AS
2232 l_PlanName VARCHAR2(10);
2233
2234 BEGIN
2235 BEGIN
2236 SELECT COMPILE_DESIGNATOR INTO l_PlanName
2237 FROM MSC_PLANS
2238 WHERE PLAN_ID = PlanId;
2239 EXCEPTION WHEN NO_DATA_FOUND THEN
2240 Status := 'INVALID_PLANID';
2241 RETURN FALSE;
2242 WHEN others THEN
2243 raise;
2244 END;
2245
2246 Status := l_PlanName;
2247 RETURN TRUE;
2248 END GET_PLAN_NAME_BY_PLAN_ID;
2249
2250 -- validate userId
2251 PROCEDURE VALIDATE_USER_RESP( VRETURN OUT NOCOPY VARCHAR2,
2252 USERID IN NUMBER,
2253 RESPID IN NUMBER) AS
2254 V_USER_ID NUMBER;
2255 V_RESPID NUMBER;
2256 V_APPID NUMBER :=0;
2257 BEGIN
2258
2259 BEGIN
2260 SELECT USER_ID INTO V_USER_ID
2261 FROM FND_USER
2262 WHERE USER_ID = USERID;
2263 EXCEPTION WHEN no_data_found THEN
2264 VRETURN := 'INVALID_USERID';
2265 RETURN;
2266 WHEN others THEN
2267 raise;
2268 END;
2269
2270 BEGIN
2271 SELECT RESPONSIBILITY_ID INTO V_RESPID
2272 FROM FND_USER_RESP_GROUPS
2273 WHERE USER_ID = V_USER_ID AND RESPONSIBILITY_ID = RESPID AND
2274 (sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate));
2275 EXCEPTION WHEN no_data_found THEN
2276 VRETURN := 'INVALID_RESP_ID';
2277 RETURN;
2278 WHEN others THEN
2279 raise;
2280 END;
2281
2282 BEGIN
2283 SELECT APPLICATION_ID INTO V_APPID
2284 FROM FND_RESPONSIBILITY
2285 WHERE RESPONSIBILITY_ID = V_RESPID;
2286 EXCEPTION WHEN others THEN
2287 raise;
2288 END;
2289
2290
2291 fnd_global.apps_initialize(USERID, RESPID, V_APPID);
2292 VRETURN :='OK';
2293
2294 END VALIDATE_USER_RESP;
2295
2296 -- no validation done
2297 PROCEDURE PUBLISH_SNO_RESULTS( processId OUT NOCOPY Number,
2298 status OUT NOCOPY Varchar2,
2299 planIdVar IN Number,
2300 assignmentSetOutIdVar IN Number) AS
2301 l_String VARCHAR2(1000);
2302 l_OrgId NUMBER;
2303 l_InsId NUMBER;
2304 l_PlanName VARCHAR2(100);
2305 l_valid BOOLEAN;
2306 errbuf varchar2(1000);
2307 retcode varchar2(1000);
2308 l_plan_run_id number := null;
2309 ScenarioNameVar varchar2(100);
2310 g_ErrorCode VARCHAR2(1000);
2311 internal_SNO_Publish_Error exception;
2312 BEGIN
2313 -- init global variables
2314 g_ErrorCode := '';
2315 ScenarioNameVar := '';
2316
2317
2318 -- initialize items for the given PlanId
2319 BEGIN
2320 SET_UP_SYSTEM_ITEMS(l_string, PlanIdVar);
2321 IF (l_String <> 'SUCCESS') THEN
2322 processid := -1;
2323 status := l_String;
2324 raise internal_SNO_Publish_Error; --RETURN;
2325 END IF;
2326 EXCEPTION
2327 WHEN internal_SNO_Publish_Error THEN
2328 g_ErrorCode := 'Internal SNO Publish Error 01';
2329 raise;
2330 WHEN others THEN
2331 g_ErrorCode := 'ERROR_UNEXPECTED_00023';
2332 raise;
2333 END;
2334
2335
2336 -- call procedure #1
2337 BEGIN
2338 SET_ASCP_PLAN_BUCKETS (l_String, PlanIdVar);
2339 IF (l_String <> 'SUCCESS') THEN
2340 processid := -1;
2341 status := l_String;
2342 raise internal_SNO_Publish_Error; --RETURN;
2343 END IF;
2344 EXCEPTION
2345 WHEN internal_SNO_Publish_Error THEN
2346 g_ErrorCode := 'Internal SNO Publish Error 02';
2347 raise;
2348 WHEN others THEN
2349 g_ErrorCode := 'ERROR_UNEXPECTED_00023';
2350 raise;
2351 END;
2352
2353 -- call procedure #2
2354 BEGIN
2355 SET_ASCP_DEMANDS (l_String, PlanIdVar);
2356 IF (l_String <> 'SUCCESS') THEN
2357 processid := -1;
2358 status := l_String;
2359 raise internal_SNO_Publish_Error; --RETURN;
2360 END IF;
2361 EXCEPTION
2362 WHEN internal_SNO_Publish_Error THEN
2363 g_ErrorCode := 'Internal SNO Publish Error 03';
2364 raise;
2365 WHEN others THEN
2366 g_ErrorCode := 'ERROR_UNEXPECTED_00024';
2367 raise;
2368 END;
2369
2370 -- call procedure #3
2371 BEGIN
2372 SET_ASCP_SUPPLIES (l_String, PlanIdVar);
2373 IF (l_String <> 'SUCCESS') THEN
2374 processid := -1;
2375 status := l_String;
2376 raise internal_SNO_Publish_Error; --RETURN;
2377 END IF;
2378 EXCEPTION
2379 WHEN internal_SNO_Publish_Error THEN
2380 g_ErrorCode := 'Internal SNO Publish Error 04';
2381 raise;
2382 WHEN others THEN
2383 g_ErrorCode := 'ERROR_UNEXPECTED_00025';
2384 raise;
2385 END;
2386
2387 -- call procedure #4
2388 BEGIN
2389 SET_ASCP_SAFETY_STOCKS (l_String, PlanIdVar);
2390 IF (l_String <> 'SUCCESS') THEN
2391 processid := -1;
2392 status := l_String;
2393 raise internal_SNO_Publish_Error; --RETURN;
2394 END IF;
2395 EXCEPTION
2396 WHEN internal_SNO_Publish_Error THEN
2397 g_ErrorCode := 'Internal SNO Publish Error 05';
2398 raise;
2399 WHEN others THEN
2400 g_ErrorCode := 'ERROR_UNEXPECTED_00026';
2401 raise;
2402 END;
2403
2404 -- call procedure #5
2405
2406 BEGIN
2407 SET_ASCP_ALERTS (l_String, PlanIdVar);
2408 IF (l_String <> 'SUCCESS') THEN
2409 processid := -1;
2410 status := l_String;
2411 raise internal_SNO_Publish_Error; --RETURN;
2412 END IF;
2413 EXCEPTION
2414 WHEN internal_SNO_Publish_Error THEN
2415 g_ErrorCode := 'Internal SNO Publish Error 06';
2416 raise;
2417 WHEN others THEN
2418 g_ErrorCode := 'ERROR_UNEXPECTED_00027';
2419 raise;
2420 END;
2421
2422 -- call procedure #6
2423 BEGIN
2424 SET_ASCP_DEPARTMENT_RESOURCES (l_String, PlanIdVar);
2425 IF (l_String <> 'SUCCESS') THEN
2426 processid := -1;
2427 status := l_String;
2428 raise internal_SNO_Publish_Error; --RETURN;
2429 END IF;
2430 EXCEPTION
2431 WHEN internal_SNO_Publish_Error THEN
2432 g_ErrorCode := 'Internal SNO Publish Error 07';
2433 raise;
2434 WHEN others THEN
2435 g_ErrorCode := 'ERROR_UNEXPECTED_00028';
2436 raise;
2437 END;
2438
2439 -- call procedure #7
2440 BEGIN
2441 SET_ASCP_RES_SUMMARY (l_String, PlanIdVar, ScenarioNameVar );
2442 IF (l_String <> 'SUCCESS') THEN
2443 processid := -1;
2444 status := l_String;
2445 raise internal_SNO_Publish_Error; --RETURN;
2446 END IF;
2447 IF ( ScenarioNameVar = '' ) THEN
2448 processid := -1;
2449 status := 'Invalid or empty Scenario Name';
2450 raise internal_SNO_Publish_Error;
2451 RETURN;
2452 END IF;
2453 EXCEPTION
2454 WHEN internal_SNO_Publish_Error THEN
2455 g_ErrorCode := 'Internal SNO Publish Error 08';
2456 raise;
2457 WHEN others THEN
2458 g_ErrorCode := 'ERROR_UNEXPECTED_00029';
2459 raise;
2460 END;
2461
2462 -- call procedure #8
2463 BEGIN
2464 SET_ASCP_BIS_INV_DETAIL (l_String, PlanIdVar);
2465 IF (l_String <> 'SUCCESS') THEN
2466 processid := -1;
2467 status := l_String;
2468 raise internal_SNO_Publish_Error; --RETURN;
2469 END IF;
2470 EXCEPTION
2471 WHEN internal_SNO_Publish_Error THEN
2472 g_ErrorCode := 'Internal SNO Publish Error 09';
2473 raise;
2474 WHEN others THEN
2475 g_ErrorCode := 'ERROR_UNEXPECTED_00030';
2476 raise;
2477 END;
2478
2479 -- call procedure #9
2480 BEGIN
2481 SET_ASCP_SRC_RECOMMEND_DETAIL (l_String, PlanIdVar, assignmentSetOutIdVar );
2482 IF (l_String <> 'SUCCESS') THEN
2483 processid := -1;
2484 status := l_String;
2485 raise internal_SNO_Publish_Error; --RETURN;
2486 END IF;
2487 EXCEPTION
2488 WHEN internal_SNO_Publish_Error THEN
2489 g_ErrorCode := 'Internal SNO Publish Error 10';
2490 raise;
2491 WHEN others THEN
2492 g_ErrorCode := 'ERROR_UNEXPECTED_00031';
2493 raise;
2494 END;
2495
2496 BEGIN
2497 -- 5th parameter p_archive_flag is invoked with default value = -1, which means p_archive_flag - yes;( TBD )
2498 -- values -1 ( default ) or 1, mean archive - Yes, otherwise No - based on the procedure definition.
2499
2500 MSC_PHUB_PKG.populate_sno_details(errbuf, retcode, PlanIdVar, l_plan_run_id, -1, ScenarioNameVar);
2501
2502
2503 IF (retcode <> null AND retcode <> '0') THEN
2504 processid := -1;
2505 status := errbuf;
2506 raise internal_SNO_Publish_Error; --RETURN;
2507 END IF;
2508
2509 EXCEPTION
2510 WHEN internal_SNO_Publish_Error THEN
2511 g_ErrorCode := 'Internal SNO Publish Error 11';
2512 raise;
2513 WHEN others THEN
2514 g_ErrorCode := 'ERROR_UNEXPECTED_00032';
2515 raise;
2516 END;
2517
2518 COMMIT;
2519 processid := 1;
2520 Status := '';
2521
2522 EXCEPTION
2523 WHEN others THEN
2524 Status := g_ErrorCode;
2525 processid := -1;
2526 log_message( Status );
2527 ROLLBACK;
2528 END PUBLISH_SNO_RESULTS;
2529
2530 PROCEDURE PUBLISH_SNO_RESULTS_WITH_VAL( processId OUT NOCOPY Number,
2531 status OUT NOCOPY Varchar2,
2532 userId IN Number,
2533 responsibilityId IN Number,
2534 planIdVar IN Number,
2535 assignmentSetOutIdVar IN Number) AS
2536 l_String VARCHAR2(1000);
2537 l_processId NUMBER;
2538 l_OrgId NUMBER;
2539 l_InsId NUMBER;
2540 l_PlanName VARCHAR2(100);
2541 l_valid BOOLEAN;
2542
2543
2544 g_ErrorCode VARCHAR2(1000);
2545 BEGIN
2546 -- init global variables
2547 g_ErrorCode := '';
2548
2549 -- validate and initialize apps
2550 l_processId := 1;
2551 VALIDATE_FOR_PUBLISH_SNO_RES( l_processId, l_String, userId, responsibilityId, planIdVar );
2552 IF ( l_processId='-1' OR l_String <> '' ) THEN
2553 processid := -1;
2554 status := l_String;
2555 log_message( status );
2556 RETURN;
2557 END IF;
2558 PUBLISH_SNO_RESULTS( processId, status, planIdVar, assignmentSetOutIdVar);
2559
2560 END PUBLISH_SNO_RESULTS_WITH_VAL;
2561
2562 PROCEDURE VALIDATE_FOR_PUBLISH_SNO_RES( processId OUT NOCOPY Number,
2563 status OUT NOCOPY Varchar2,
2564 userId IN Number,
2565 responsibilityId IN Number,
2566 planIdVar IN Number) AS
2567 l_String VARCHAR2(1000);
2568 l_OrgId NUMBER;
2569 l_InsId NUMBER;
2570 l_PlanName VARCHAR2(100);
2571 l_valid BOOLEAN;
2572
2573
2574 g_ErrorCode VARCHAR2(1000);
2575 BEGIN
2576 -- init global variables
2577 g_ErrorCode := '';
2578
2579 -- validate and initialize apps
2580 BEGIN
2581 VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
2582 IF (l_String <> 'OK') THEN
2583 processid := -1;
2584 status := l_String;
2585 RETURN;
2586 END IF;
2587 EXCEPTION WHEN others THEN
2588 g_ErrorCode := 'ERROR_UNEXPECTED_00021';
2589 raise;
2590 END;
2591
2592 -- check plan id
2593 BEGIN
2594 l_valid := GET_PLAN_NAME_BY_PLAN_ID(l_String, PlanIdVar);
2595 IF ( (l_String = 'INVALID_PLANID') OR l_valid=false ) THEN
2596 processid := -1;
2597 status := l_String;
2598 RETURN;
2599 END IF;
2600 EXCEPTION WHEN others THEN
2601 g_ErrorCode := 'ERROR_UNEXPECTED_00022';
2602 raise;
2603 END;
2604
2605 processid := 1;
2606 Status := '';
2607
2608 EXCEPTION
2609 WHEN others THEN
2610 Status := g_ErrorCode;
2611 processid := -1;
2612 log_message( Status );
2613 ROLLBACK;
2614 END VALIDATE_FOR_PUBLISH_SNO_RES;
2615 END MSC_WS_SNO_PUBLISH;