[Home] [Help]
PACKAGE BODY: APPS.MSC_WS_SNO_PUBLISH
Source
1 PACKAGE BODY MSC_WS_SNO_PUBLISH AS
2 /* $Header: MSCWSPBB.pls 120.33 2011/05/24 10:48:19 saskrish 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) <> 0 )
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) <> 0 )
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' || ' : ' || SQLERRM;
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) <> 0 )
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) <> 0 )
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) <> 0 )
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) <> 0 )
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' || ' : ' || SQLERRM;
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' || ' : ' || SQLERRM;
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 (
317 case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
318 then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
319 else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
320 )
321 end,
322 -- department_id
323 case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
324 ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326 MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327 MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328 MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
329 MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330 ) > 0 ))
331 then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
332 else -1
333 end,
334 -- resource_id
335 case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
336 ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
337 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338 MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339 MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340 MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
341 MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
342 ) > 0 ) )
343 then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
344 else -1
345 end,
346 MSC_EXCEPTION_DETAILS_S.NEXTVAL,
347 MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
348 MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
349 MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
350 TO_DATE(PeriodStart, 'YYYY-MM-DD'),
351 TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
352 NULL,
353 CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
354 THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
355 ELSE
356 NULL
357 END,
358 null, --customerSiteId
359 null, --customerId
360 SYSDATE,
361 '-1',
362 SYSDATE,
363 '-1'
364 FROM MSC_INT_ASCP_EXCEPTION_DETAILS
365 WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
366 AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
367 AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
368 AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
369
370 EXCEPTION WHEN others THEN
371 g_ErrorCode := 'ERROR_UPDATE_ALERTS_001003' || ' : ' || SQLERRM;
372 raise;
373 END;
374
375 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
376 -- checkpoint commit;
377 Status := 'SUCCESS';
378
379 EXCEPTION
380 WHEN others THEN
381 Status := g_ErrorCode;
382 ROLLBACK;
383
384 END SET_ASCP_ALERTS;
385
386 PROCEDURE SET_UP_SYSTEM_ITEMS(
387 Status OUT NOCOPY VARCHAR2,
388 PlanIdVar IN NUMBER
389 ) AS
390
391 g_ErrorCode VARCHAR2(1000);
392 BEGIN
393 /* implementation start here */
394 -- init global variables
395 g_ErrorCode := '';
396
397
398 -- delete records from MSC_SYSTEM_ITEMS table for the given PlanId, if any
399 BEGIN
400 DELETE FROM MSC_SYSTEM_ITEMS WHERE PLAN_ID=PlanIdVar;
401 EXCEPTION WHEN others THEN
402 NULL; -- do nothing
403 END;
404
405
406 --Now update the temp table in order to get Number Of Sources measure for each item
407 -- delete records from MSC_INT_APCC_ITEM_SOURCE table for the given PlanId, if any
408 --BEGIN
409 --DELETE FROM MSC_INT_APCC_ITEM_SOURCE WHERE PLAN_ID=PlanIdVar;
410 --EXCEPTION WHEN others THEN
411 --NULL; -- do nothing
412 --END;
413
414 --BEGIN
415 --INSERT into msc_int_apcc_item_source
416 -- ( plan_id,
417 -- sr_instance_id,
418 -- organization_id,
419 -- inventory_item_id,
420 -- source_organization_id,
421 -- source_org_instance_id )
422 --SELECT DISTINCT
423 -- TO_NUMBER(msc_int_src_recommend_detail.planName),
424 --sr_instance_id
425 -- TO_NUMBER(substr(msc_int_src_recommend_detail.destination_code,1,instr(msc_int_src_recommend_detail.destination_code,':',1,1)-1)),
426 --organization_id
427 -- TO_NUMBER(substr(msc_int_src_recommend_detail.destination_code,instr(msc_int_src_recommend_detail.destination_code,':',1,1)+1)),
428 --inventory_item_id
429 -- TO_NUMBER(substr(msc_int_src_recommend_detail.item_code,instr(msc_int_src_recommend_detail.item_code,':',1,1)+1)),
430 --source_organization_id
431 -- TO_NUMBER(substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )),
432 ---source_org_instance_id
433 -- TO_NUMBER(substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1))
434 --FROM msc_int_src_recommend_detail
435 --WHERE msc_int_src_recommend_detail.planName = PlanIdVar
436 --GROUP BY msc_int_src_recommend_detail.planName,
437 -- msc_int_src_recommend_detail.origin_code,
438 -- msc_int_src_recommend_detail.destination_code,
439 -- msc_int_src_recommend_detail.item_code;
440 --EXCEPTION WHEN others THEN
441 --g_ErrorCode := 'ERROR_UPDATE_MSC_SYSTEM_ITEMS_001001' || ' : ' || SQLERRM;
442 --raise;
443 --END;
444
445 BEGIN
446 -- CODE GOES HERE
447 -- duplicate system items needed in the msc_system_items table with changed PlanId
448 INSERT INTO MSC_SYSTEM_ITEMS (
449 PLAN_ID,
450 ORGANIZATION_ID,
451 INVENTORY_ITEM_ID,
452 SR_INSTANCE_ID,
453 SR_INVENTORY_ITEM_ID,
454 ITEM_NAME,
455 LOTS_EXPIRATION,
456 LOT_CONTROL_CODE,
457 SHRINKAGE_RATE,
458 FIXED_DAYS_SUPPLY,
459 FIXED_ORDER_QUANTITY,
460 FIXED_LOT_MULTIPLIER,
461 MINIMUM_ORDER_QUANTITY,
462 MAXIMUM_ORDER_QUANTITY,
463 ROUNDING_CONTROL_TYPE,
464 PLANNING_TIME_FENCE_DAYS,
465 PLANNING_TIME_FENCE_DATE,
466 DEMAND_TIME_FENCE_DAYS,
467 DEMAND_TIME_FENCE_DATE,
468 DESCRIPTION,
469 RELEASE_TIME_FENCE_CODE,
470 RELEASE_TIME_FENCE_DAYS,
471 IN_SOURCE_PLAN,
472 REVISION,
473 SR_CATEGORY_ID,
474 ABC_CLASS,
475 CATEGORY_NAME,
476 MRP_PLANNING_CODE,
477 FIXED_LEAD_TIME,
478 VARIABLE_LEAD_TIME,
479 PREPROCESSING_LEAD_TIME,
480 POSTPROCESSING_LEAD_TIME,
481 FULL_LEAD_TIME,
482 CUMULATIVE_TOTAL_LEAD_TIME,
483 CUM_MANUFACTURING_LEAD_TIME,
484 UOM_CODE,
485 UNIT_WEIGHT,
486 UNIT_VOLUME,
487 WEIGHT_UOM,
488 VOLUME_UOM,
489 PRODUCT_FAMILY_ID,
490 ATP_RULE_ID,
491 ATP_COMPONENTS_FLAG,
492 BUILD_IN_WIP_FLAG,
493 PURCHASING_ENABLED_FLAG,
494 PLANNING_MAKE_BUY_CODE,
495 REPETITIVE_TYPE,
496 REPETITIVE_VARIANCE,
497 STANDARD_COST,
498 CARRYING_COST,
499 ORDER_COST,
500 MATERIAL_COST,
501 DMD_LATENESS_COST,
502 RESOURCE_COST,
503 SS_PENALTY_COST,
504 SUPPLIER_CAP_OVERUTIL_COST,
505 LIST_PRICE,
506 AVERAGE_DISCOUNT,
507 ENGINEERING_ITEM_FLAG,
508 WIP_SUPPLY_TYPE,
509 SAFETY_STOCK_CODE,
510 SAFETY_STOCK_PERCENT,
511 SAFETY_STOCK_BUCKET_DAYS,
512 INVENTORY_USE_UP_DATE,
513 BUYER_NAME,
514 PLANNER_CODE,
515 PLANNING_EXCEPTION_SET,
516 EXCESS_QUANTITY,
517 EXCEPTION_SHORTAGE_DAYS,
518 EXCEPTION_EXCESS_DAYS,
519 EXCEPTION_OVERPROMISED_DAYS,
520 EXCEPTION_CODE,
521 BOM_ITEM_TYPE,
522 ATO_FORECAST_CONTROL,
523 EFFECTIVITY_CONTROL,
524 ORGANIZATION_CODE,
525 ACCEPTABLE_RATE_INCREASE,
526 ACCEPTABLE_RATE_DECREASE,
527 EXCEPTION_REP_VARIANCE_DAYS,
528 OVERRUN_PERCENTAGE,
529 INVENTORY_PLANNING_CODE,
530 ACCEPTABLE_EARLY_DELIVERY,
531 CALCULATE_ATP,
532 END_ASSEMBLY_PEGGING_FLAG,
533 END_ASSEMBLY_PEGGING,
534 FULL_PEGGING,
535 INVENTORY_ITEM_FLAG,
536 SOURCE_ORG_ID,
537 BASE_ITEM_ID,
538 ABC_CLASS_NAME,
539 FIXED_SAFETY_STOCK_QTY,
540 PRIMARY_SUPPLIER_ID,
541 ATP_FLAG,
542 LOW_LEVEL_CODE,
543 PLANNER_STATUS_CODE,
544 NETTABLE_INVENTORY_QUANTITY,
545 NONNETTABLE_INVENTORY_QUANTITY,
546 REFRESH_NUMBER,
547 REQUEST_ID,
548 PROGRAM_APPLICATION_ID,
549 PROGRAM_ID,
550 PROGRAM_UPDATE_DATE,
551 ATTRIBUTE_CATEGORY,
552 ATTRIBUTE1,
553 ATTRIBUTE2,
554 ATTRIBUTE3,
555 ATTRIBUTE4,
556 ATTRIBUTE5,
557 ATTRIBUTE6,
558 ATTRIBUTE7,
559 ATTRIBUTE8,
560 ATTRIBUTE9,
561 ATTRIBUTE10,
562 ATTRIBUTE11,
563 ATTRIBUTE12,
564 ATTRIBUTE13,
565 ATTRIBUTE14,
566 ATTRIBUTE15,
567 REVISION_QTY_CONTROL_CODE,
568 EXPENSE_ACCOUNT,
569 INVENTORY_ASSET_FLAG,
570 BUYER_ID,
571 REPETITIVE_PLANNING_FLAG,
572 PICK_COMPONENTS_FLAG,
573 SERVICE_LEVEL,
574 REPLENISH_TO_ORDER_FLAG,
575 PIP_FLAG,
576 YIELD_CONV_FACTOR,
577 MIN_MINMAX_QUANTITY,
578 MAX_MINMAX_QUANTITY,
579 NEW_ATP_FLAG,
580 SOURCE_TYPE,
581 SUBSTITUTION_WINDOW,
582 CREATE_SUPPLY_FLAG,
583 REORDER_POINT,
584 AVERAGE_ANNUAL_DEMAND,
585 ECONOMIC_ORDER_QUANTITY,
586 SERIAL_NUMBER_CONTROL_CODE,
587 CONVERGENCE,
588 DIVERGENCE,
589 CONTINOUS_TRANSFER,
590 CRITICAL_COMPONENT_FLAG,
591 REDUCE_MPS,
592 CONSIGNED_FLAG,
593 VMI_MINIMUM_UNITS,
594 VMI_MINIMUM_DAYS,
595 VMI_MAXIMUM_UNITS,
596 VMI_MAXIMUM_DAYS,
597 AVERAGE_DAILY_DEMAND,
598 VMI_FIXED_ORDER_QUANTITY,
599 SO_AUTHORIZATION_FLAG,
600 VMI_FORECAST_TYPE,
601 FORECAST_HORIZON,
602 ASN_AUTOEXPIRE_FLAG,
603 VMI_REFRESH_FLAG,
604 BUDGET_CONSTRAINED,
605 MAX_QUANTITY,
606 MAX_QUANTITY_DOS,
607 DAYS_TGT_INV_WINDOW,
608 DAYS_MAX_INV_WINDOW,
609 DAYS_TGT_INV_SUPPLY,
610 DAYS_MAX_INV_SUPPLY,
611 DRP_PLANNED,
612 AGGREGATE_TIME_FENCE_DATE,
613 INFERRED_CRITICAL_FLAG,
614 SS_WINDOW_SIZE,
615 ITEM_CREATION_DATE,
616 PLANNING_TIME_FENCE_CODE,
617 SHORTAGE_TYPE,
618 EXCESS_TYPE,
619 PEGGING_DEMAND_WINDOW_DAYS,
620 PEGGING_SUPPLY_WINDOW_DAYS,
621 UNSATISFIED_DEMAND_FACTOR,
622 SAFETY_LEAD_TIME,
623 -- COUNT_OF_SOURCES,
624 LAST_UPDATE_DATE,
625 LAST_UPDATED_BY,
626 CREATION_DATE,
627 CREATED_BY,
628 LAST_UPDATE_LOGIN )
629 SELECT DISTINCT PlanIdVar,
630 ORGANIZATION_ID,
631 INVENTORY_ITEM_ID,
632 SR_INSTANCE_ID,
633 SR_INVENTORY_ITEM_ID,
634 ITEM_NAME,
635 LOTS_EXPIRATION,
636 LOT_CONTROL_CODE,
637 SHRINKAGE_RATE,
638 FIXED_DAYS_SUPPLY,
639 FIXED_ORDER_QUANTITY,
640 FIXED_LOT_MULTIPLIER,
641 MINIMUM_ORDER_QUANTITY,
642 MAXIMUM_ORDER_QUANTITY,
643 ROUNDING_CONTROL_TYPE,
644 PLANNING_TIME_FENCE_DAYS,
645 PLANNING_TIME_FENCE_DATE,
646 DEMAND_TIME_FENCE_DAYS,
647 DEMAND_TIME_FENCE_DATE,
648 DESCRIPTION,
649 RELEASE_TIME_FENCE_CODE,
650 RELEASE_TIME_FENCE_DAYS,
651 IN_SOURCE_PLAN,
652 REVISION,
653 SR_CATEGORY_ID,
654 ABC_CLASS,
655 CATEGORY_NAME,
656 MRP_PLANNING_CODE,
657 FIXED_LEAD_TIME,
658 VARIABLE_LEAD_TIME,
659 PREPROCESSING_LEAD_TIME,
660 POSTPROCESSING_LEAD_TIME,
661 FULL_LEAD_TIME,
662 CUMULATIVE_TOTAL_LEAD_TIME,
663 CUM_MANUFACTURING_LEAD_TIME,
664 UOM_CODE,
665 UNIT_WEIGHT,
666 UNIT_VOLUME,
667 WEIGHT_UOM,
668 VOLUME_UOM,
669 PRODUCT_FAMILY_ID,
670 ATP_RULE_ID,
671 ATP_COMPONENTS_FLAG,
672 BUILD_IN_WIP_FLAG,
673 PURCHASING_ENABLED_FLAG,
674 PLANNING_MAKE_BUY_CODE,
675 REPETITIVE_TYPE,
676 REPETITIVE_VARIANCE,
677 STANDARD_COST,
678 CARRYING_COST,
679 ORDER_COST,
680 MATERIAL_COST,
681 DMD_LATENESS_COST,
682 RESOURCE_COST,
683 SS_PENALTY_COST,
684 SUPPLIER_CAP_OVERUTIL_COST,
685 LIST_PRICE,
686 AVERAGE_DISCOUNT,
687 ENGINEERING_ITEM_FLAG,
688 WIP_SUPPLY_TYPE,
689 SAFETY_STOCK_CODE,
690 SAFETY_STOCK_PERCENT,
691 SAFETY_STOCK_BUCKET_DAYS,
692 INVENTORY_USE_UP_DATE,
693 BUYER_NAME,
694 PLANNER_CODE,
695 PLANNING_EXCEPTION_SET,
696 EXCESS_QUANTITY,
697 EXCEPTION_SHORTAGE_DAYS,
698 EXCEPTION_EXCESS_DAYS,
699 EXCEPTION_OVERPROMISED_DAYS,
700 EXCEPTION_CODE,
701 BOM_ITEM_TYPE,
702 ATO_FORECAST_CONTROL,
703 EFFECTIVITY_CONTROL,
704 ORGANIZATION_CODE,
705 ACCEPTABLE_RATE_INCREASE,
706 ACCEPTABLE_RATE_DECREASE,
707 EXCEPTION_REP_VARIANCE_DAYS,
708 OVERRUN_PERCENTAGE,
709 INVENTORY_PLANNING_CODE,
710 ACCEPTABLE_EARLY_DELIVERY,
711 CALCULATE_ATP,
712 END_ASSEMBLY_PEGGING_FLAG,
713 END_ASSEMBLY_PEGGING,
714 FULL_PEGGING,
715 INVENTORY_ITEM_FLAG,
716 SOURCE_ORG_ID,
717 BASE_ITEM_ID,
718 ABC_CLASS_NAME,
719 FIXED_SAFETY_STOCK_QTY,
720 PRIMARY_SUPPLIER_ID,
721 ATP_FLAG,
722 LOW_LEVEL_CODE,
723 PLANNER_STATUS_CODE,
724 NETTABLE_INVENTORY_QUANTITY,
725 NONNETTABLE_INVENTORY_QUANTITY,
726 REFRESH_NUMBER,
727 REQUEST_ID,
728 PROGRAM_APPLICATION_ID,
729 PROGRAM_ID,
730 PROGRAM_UPDATE_DATE,
731 ATTRIBUTE_CATEGORY,
732 ATTRIBUTE1,
733 ATTRIBUTE2,
734 ATTRIBUTE3,
735 ATTRIBUTE4,
736 ATTRIBUTE5,
737 ATTRIBUTE6,
738 ATTRIBUTE7,
739 ATTRIBUTE8,
740 ATTRIBUTE9,
741 ATTRIBUTE10,
742 ATTRIBUTE11,
743 ATTRIBUTE12,
744 ATTRIBUTE13,
745 ATTRIBUTE14,
746 ATTRIBUTE15,
747 REVISION_QTY_CONTROL_CODE,
748 EXPENSE_ACCOUNT,
749 INVENTORY_ASSET_FLAG,
750 BUYER_ID,
751 REPETITIVE_PLANNING_FLAG,
752 PICK_COMPONENTS_FLAG,
753 SERVICE_LEVEL,
754 REPLENISH_TO_ORDER_FLAG,
755 PIP_FLAG,
756 YIELD_CONV_FACTOR,
757 MIN_MINMAX_QUANTITY,
758 MAX_MINMAX_QUANTITY,
759 NEW_ATP_FLAG,
760 SOURCE_TYPE,
761 SUBSTITUTION_WINDOW,
762 CREATE_SUPPLY_FLAG,
763 REORDER_POINT,
764 AVERAGE_ANNUAL_DEMAND,
765 ECONOMIC_ORDER_QUANTITY,
766 SERIAL_NUMBER_CONTROL_CODE,
767 CONVERGENCE,
768 DIVERGENCE,
769 CONTINOUS_TRANSFER,
770 CRITICAL_COMPONENT_FLAG,
771 REDUCE_MPS,
772 CONSIGNED_FLAG,
773 VMI_MINIMUM_UNITS,
774 VMI_MINIMUM_DAYS,
775 VMI_MAXIMUM_UNITS,
776 VMI_MAXIMUM_DAYS,
777 AVERAGE_DAILY_DEMAND,
778 VMI_FIXED_ORDER_QUANTITY,
779 SO_AUTHORIZATION_FLAG,
780 VMI_FORECAST_TYPE,
781 FORECAST_HORIZON,
782 ASN_AUTOEXPIRE_FLAG,
783 VMI_REFRESH_FLAG,
784 BUDGET_CONSTRAINED,
785 MAX_QUANTITY,
786 MAX_QUANTITY_DOS,
787 DAYS_TGT_INV_WINDOW,
788 DAYS_MAX_INV_WINDOW,
789 DAYS_TGT_INV_SUPPLY,
790 DAYS_MAX_INV_SUPPLY,
791 DRP_PLANNED,
792 AGGREGATE_TIME_FENCE_DATE,
793 INFERRED_CRITICAL_FLAG,
794 SS_WINDOW_SIZE,
795 ITEM_CREATION_DATE,
796 PLANNING_TIME_FENCE_CODE,
797 SHORTAGE_TYPE,
798 EXCESS_TYPE,
799 PEGGING_DEMAND_WINDOW_DAYS,
800 PEGGING_SUPPLY_WINDOW_DAYS,
801 UNSATISFIED_DEMAND_FACTOR,
802 SAFETY_LEAD_TIME,
803 -- number of sources ( new measure )
804 --(select COUNT( DISTINCT MSC_INT_APCC_ITEM_SOURCE.source_organization_id )
805 -- from MSC_INT_APCC_ITEM_SOURCE
806 -- WHERE PlanIdVar = MSC_INT_APCC_ITEM_SOURCE.plan_ID AND
807 -- MSC_SYSTEM_ITEMS.SR_INSTANCE_ID = MSC_INT_APCC_ITEM_SOURCE.sr_instance_id AND
808 -- MSC_SYSTEM_ITEMS.ORGANIZATION_ID = MSC_INT_APCC_ITEM_SOURCE.organization_id AND
809 -- MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID = MSC_INT_APCC_ITEM_SOURCE.inventory_item_id
810 -- ), -- must specify table name before columns to avoid duplicate count
811 SYSDATE,
812 '-1',
813 SYSDATE,
814 '-1',
815 -- last update login :
816 -- we do not have the userId here ( no validation should be done at this point as required earlier) :
817 '-1'
818 FROM MSC_SYSTEM_ITEMS LEFT JOIN MSC_INT_ASCP_INVENTORY ON
819 MSC_INT_ASCP_INVENTORY.OrganizationID = MSC_SYSTEM_ITEMS.ORGANIZATION_ID AND
820 MSC_INT_ASCP_INVENTORY.InstanceID = MSC_SYSTEM_ITEMS.SR_INSTANCE_ID AND
821 MSC_INT_ASCP_INVENTORY.ItemId = MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID
822 WHERE MSC_SYSTEM_ITEMS.PLAN_ID=-1 AND MSC_INT_ASCP_INVENTORY.PLAN_ID=PlanIdVar;
823 EXCEPTION WHEN others THEN
824 g_ErrorCode := 'ERROR_UPDATE_MSC_SYSTEM_ITEMS_001002' || ' : ' || SQLERRM;
825 raise;
826 END;
827
828 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
829 -- checkpoint commit;
830 Status := 'SUCCESS';
831
832 EXCEPTION
833 WHEN others THEN
834 Status := g_ErrorCode;
835 ROLLBACK;
836
837 END SET_UP_SYSTEM_ITEMS;
838
839
840
841 PROCEDURE SET_ASCP_PLAN_BUCKETS(
842 Status OUT NOCOPY VARCHAR2,
843 PlanIdVar IN NUMBER
844 ) AS
845
846 g_ErrorCode VARCHAR2(1000);
847 BEGIN
848 /* implementation start here */
849 -- init global variables
850 g_ErrorCode := '';
851
852
853 -- delete records from MSC_PLAN_BUCKETS table for the given PlanId, if any
854 BEGIN
855 DELETE FROM MSC_PLAN_BUCKETS WHERE PLAN_ID=PlanIdVar;
856 EXCEPTION WHEN others THEN
857 NULL; -- do nothing
858 END;
859
860 BEGIN
861 -- CODE GOES HERE
862 INSERT INTO MSC_PLAN_BUCKETS (PLAN_ID,
863 ORGANIZATION_ID,
864 SR_INSTANCE_ID,
865 BUCKET_INDEX,
866 CURR_FLAG,
867 BKT_START_DATE,
868 BKT_END_DATE,
869 DAYS_IN_BKT,
870 BUCKET_TYPE,
871 LAST_UPDATE_DATE,
872 LAST_UPDATED_BY,
873 CREATION_DATE,
874 CREATED_BY)
875 SELECT PlanIdVar, MSC_PLANS.ORGANIZATION_ID, MSC_PLANS.SR_INSTANCE_ID,
876 TO_NUMBER(BucketIndex), 1,
877 TO_DATE(BktStartDate,'YYYY-MM-DD'), TO_DATE(BktEndDate,'YYYY-MM-DD')-1/86400,
878 TO_NUMBER(DaysInBucket), TO_NUMBER(BucketType),
879 SYSDATE, '-1',
880 SYSDATE, '-1'
881 FROM MSC_INT_ASCP_EXPORT_BUCKETS, MSC_PLANS
882 WHERE PLANID=PlanIdVar AND
883 MSC_PLANS.PLAN_ID=PlanIdVar;
884 EXCEPTION WHEN others THEN
885 g_ErrorCode := 'ERROR_UPDATE_PLAN_BUCKETS_001001' || ' : ' || SQLERRM;
886 raise;
887 END;
888
889
890
891
892 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
893 -- checkpoint commit;
894 Status := 'SUCCESS';
895
896 EXCEPTION
897 WHEN others THEN
898 Status := g_ErrorCode;
899 ROLLBACK;
900
901 END SET_ASCP_PLAN_BUCKETS;
902
903 -- New FacilityCosts measure
904 PROCEDURE SET_APCC_FACILITY_COST(
905 Status OUT NOCOPY VARCHAR2,
906 PlanIdVar IN NUMBER
907 ) AS
908
909 g_ErrorCode VARCHAR2(1000);
910 BEGIN
911 /* implementation start here */
912 -- init global variables
913 g_ErrorCode := '';
914
915 -- delete records from MSC_BIS_ORG_DETAIL table for the given PlanId, if any
916 BEGIN
917 DELETE FROM MSC_BIS_ORG_DETAIL WHERE PLAN_ID=PlanIdVar;
918 EXCEPTION WHEN others THEN
919 NULL; -- do nothing
920 END;
921
922 BEGIN
923 -- CODE GOES HERE
924 INSERT INTO MSC_BIS_ORG_DETAIL (PLAN_ID,
925 SR_INSTANCE_ID,
926 ORGANIZATION_ID,
927 DETAIL_DATE,
928 FACILITY_COST,
929 FACILITY_COST_TYPE,
930 LAST_UPDATE_DATE,
931 LAST_UPDATED_BY,
932 CREATION_DATE,
933 CREATED_BY,
934 LAST_UPDATE_LOGIN)
935 SELECT
936 PlanIdVar,
937 SR_INSTANCE_ID,
938 ORGANIZATION_ID,
939 TO_DATE(PERIOD_END,'YYYY-MM-DD'),
940 FACILITY_COST,
941 FACILITY_COST_TYPE,
942 SYSDATE, '-1',
943 SYSDATE, '-1', -1
944 FROM MSC_INT_APCC_ORG_DETAIL
945 WHERE PLAN_ID=PlanIdVar;
946 EXCEPTION WHEN others THEN
947 g_ErrorCode := 'ERROR_UPDATE_FACILITY_COSTS_001001' || ' : ' || SQLERRM;
948 raise;
949 END
950
951 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
952 -- checkpoint commit;
953 Status := 'SUCCESS';
954
955 EXCEPTION
956 WHEN others THEN
957 Status := g_ErrorCode;
958 ROLLBACK;
959
960 END SET_APCC_FACILITY_COST;
961
962
963 PROCEDURE SET_ASCP_DEMANDS(
964 Status OUT NOCOPY VARCHAR2,
965 PlanIdVar IN NUMBER
966 ) AS
967 g_ErrorCode VARCHAR2(1000);
968 BEGIN
969 /* implementation start here */
970 -- init global variables
971 g_ErrorCode := '';
972
973
974 -- delete records from MSC_WS_DEMANDS table for the given PlanIdVar, if any
975 BEGIN
976 DELETE FROM MSC_DEMANDS WHERE PLAN_ID=PlanIdVar;
977 EXCEPTION WHEN others THEN
978 NULL; -- do nothing
979 END;
980 -- insert new rows
981
982
983 BEGIN
984 -- CODE GOES HERE
985 --
986 -- populate MSC_DEMANDS from ASCP Demand (Forecast/Satisfied)
987 --
988 INSERT INTO MSC_DEMANDS (
989 ORGANIZATION_ID,
990 INVENTORY_ITEM_ID,
991 PLAN_ID,
992 SR_INSTANCE_ID,
993 DEMAND_ID,
994 ORIGINATION_TYPE,
995 USING_REQUIREMENT_QUANTITY,
996 QUANTITY_BY_DUE_DATE,
997 DMD_SATISFIED_DATE,
998 ZONE_ID,
999 CUSTOMER_SITE_ID,
1000 CUSTOMER_ID,
1001 SERVICE_LEVEL,
1002 USING_ASSEMBLY_DEMAND_DATE,
1003 USING_ASSEMBLY_ITEM_ID,
1004 DEMAND_TYPE,
1005 UNMET_QUANTITY,
1006 LAST_UPDATE_DATE,
1007 LAST_UPDATED_BY,
1008 CREATION_DATE,
1009 CREATED_BY)
1010 SELECT
1011 CASE WHEN ( ZoneID is not null )
1012 THEN -1
1013 ELSE
1014 OrganizationID
1015 END,
1016 ItemID,
1017 PlanIdVar,
1018 InstanceID,
1019 MSC_DEMANDS_S.NEXTVAL,
1020 81,
1021 Demand,
1022 Satisfied,
1023 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1024 CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':') = 0 )
1025 THEN TO_NUMBER(ZoneID)
1026 ELSE
1027 NULL
1028 END,
1029 CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':',1,1) <> 0 )
1030 THEN SUBSTR(ZoneID, INSTR(ZoneID,':',1,1)+1) -- CUSTOMER_SITE_ID
1031 ELSE
1032 NULL
1033 END,
1034 CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':',1,1) <> 0 )
1035 THEN SUBSTR(ZoneID, 1, INSTR(ZoneID,':',1,1)-1) -- CUSTOMER_ID
1036 ELSE
1037 NULL
1038 END,
1039 0,
1040 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1041 ItemID,
1042 1,
1043 Demand-Satisfied,
1044 SYSDATE, '-1', SYSDATE, '-1'
1045 FROM MSC_INT_ASCP_DEMANDS
1046 WHERE PLAN_ID=PlanIdVar;
1047 EXCEPTION WHEN others THEN
1048 g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_DEMAND_001001' || ' : ' || SQLERRM;
1049 raise;
1050 END;
1051 BEGIN
1052 -- populate MSC_DEMANDS from ASCP Dependent Demand
1053 --
1054 INSERT INTO MSC_DEMANDS (
1055 ORGANIZATION_ID,
1056 INVENTORY_ITEM_ID,
1057 PLAN_ID,
1058 SR_INSTANCE_ID,
1059 DEMAND_ID,
1060 ORIGINATION_TYPE,
1061 USING_REQUIREMENT_QUANTITY,
1062 DMD_SATISFIED_DATE,
1063 USING_ASSEMBLY_DEMAND_DATE,
1064 USING_ASSEMBLY_ITEM_ID,
1065 DEMAND_TYPE,
1066 LAST_UPDATE_DATE,
1067 LAST_UPDATED_BY,
1068 CREATION_DATE,
1069 CREATED_BY)
1070 SELECT
1071 FromOrgID,
1072 ItemID,
1073 PlanIdVar,
1074 InstanceID,
1075 MSC_DEMANDS_S.NEXTVAL, --DEMAND_ID,
1076 1, --ORIGINATION_TYPE,
1077 Quantity, --BUGBUG: how to show Infinity ? --USING_REQUIREMENT_QUANTITY
1078 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, --DMD_SATISFIED_DATE
1079 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, --USING_ASSEMBLY_DEMAND_DATE
1080 ItemID, --USING_ASSEMBLY_ITEM_ID
1081 1, --DEMAND_TYPE
1082 SYSDATE, '-1', SYSDATE, '-1'
1083 FROM MSC_INT_ASCP_DEPENDENT_DEMAND
1084 WHERE PLAN_ID=PlanIdVar;
1085 EXCEPTION WHEN others THEN
1086 g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_DEPENDENT_DEMAND_001002' || ' : ' || SQLERRM;
1087 raise;
1088 END;
1089
1090 BEGIN
1091 -- CODE GOES HERE
1092 --
1093 -- populate MSC_DEMANDS from ASCP Transportation (Move Order)
1094 --
1095 INSERT INTO MSC_DEMANDS (ORGANIZATION_ID,
1096 INVENTORY_ITEM_ID,
1097 PLAN_ID,
1098 SR_INSTANCE_ID,
1099 DEMAND_ID,
1100 ORIGINATION_TYPE,
1101 USING_REQUIREMENT_QUANTITY,
1102 QUANTITY_BY_DUE_DATE,
1103 DMD_SATISFIED_DATE,
1104 ZONE_ID,
1105 CUSTOMER_SITE_ID,
1106 CUSTOMER_ID,
1107 SERVICE_LEVEL,
1108 USING_ASSEMBLY_DEMAND_DATE,
1109 USING_ASSEMBLY_ITEM_ID,
1110 DEMAND_TYPE,
1111 LAST_UPDATE_DATE,
1112 LAST_UPDATED_BY,
1113 CREATION_DATE,
1114 CREATED_BY)
1115 SELECT
1116 FromOrgID,
1117 ItemID,
1118 PlanIdVar,
1119 FromInstanceID,
1120 MSC_DEMANDS_S.NEXTVAL,
1121 82,
1122 Quantity,
1123 Quantity,
1124 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1125 NULL,
1126 NULL,
1127 NULL,
1128 0,
1129 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1130 ItemID,
1131 1,
1132 SYSDATE, '-1', SYSDATE, '-1'
1133 FROM MSC_INT_ASCP_TRANSPORTATION
1134 WHERE PLAN_ID=PlanIdVar AND
1135 Subcategory <> 'Customer';
1136 EXCEPTION WHEN others THEN
1137 g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_TRANSPORTATION_001003' || ' : ' || SQLERRM;
1138 raise;
1139 END;
1140
1141
1142
1143 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1144 -- checkpoint commit;
1145 Status := 'SUCCESS';
1146
1147 EXCEPTION
1148 WHEN others THEN
1149 Status := g_ErrorCode;
1150 ROLLBACK;
1151 END SET_ASCP_DEMANDS;
1152
1153 PROCEDURE SET_ASCP_SUPPLIES (
1154 Status OUT NOCOPY VARCHAR2,
1155 PlanIdVar IN NUMBER
1156 ) AS
1157 g_ErrorCode VARCHAR2(1000);
1158 BEGIN
1159 /* implementation starts here */
1160 -- init global variables
1161 g_ErrorCode := '';
1162
1163
1164 -- delete records from MSC_SUPPLIES table for the given PlanIdVar, if any
1165 BEGIN
1166 DELETE FROM MSC_SUPPLIES WHERE PLAN_ID=PlanIdVar;
1167 EXCEPTION WHEN others THEN
1168 NULL; -- do nothing
1169 END;
1170 -- insert new rows
1171
1172
1173 BEGIN
1174 -- CODE GOES HERE
1175 --
1176 -- Fill in data from MSC_INT_ASCP_SUPPLY
1177 --
1178 INSERT INTO MSC_SUPPLIES (PLAN_ID,
1179 TRANSACTION_ID,
1180 ORGANIZATION_ID,
1181 SR_INSTANCE_ID,
1182 INVENTORY_ITEM_ID,
1183 ORDER_TYPE,
1184 NEW_SCHEDULE_DATE,
1185 NEW_ORDER_QUANTITY,
1186 FIRM_PLANNED_TYPE,
1187 SUPPLIER_ID,
1188 SUPPLIER_SITE_ID,
1189 LAST_UPDATE_DATE,
1190 LAST_UPDATED_BY,
1191 CREATION_DATE,
1192 CREATED_BY)
1193 SELECT PlanIdVar, msc_supplies_s.nextval, organizationID,
1194 instanceID, itemID, 1, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1195 supply, 2, SupplierID,
1196 supplierSiteID,
1197 SYSDATE, '-1', SYSDATE, '-1'
1198 FROM MSC_INT_ASCP_SUPPLY
1199 WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_SUPPLY.CATEGORY='Supply';
1200
1201 -- NOTE: Last four fields: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY are non-NULL,
1202 -- so I harcoded an arbitrary value
1203 EXCEPTION WHEN others THEN
1204 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_SUPPLY_001001' || ' : ' || SQLERRM;
1205 raise;
1206 END;
1207
1208 BEGIN
1209 -- CODE GOES HERE
1210 --
1211 -- Fill in data from MSC_INT_ASCP_TRANSPORTATION
1212 --
1213 INSERT INTO MSC_SUPPLIES (
1214 PLAN_ID,
1215 TRANSACTION_ID,
1216 ORGANIZATION_ID,
1217 SR_INSTANCE_ID,
1218 INVENTORY_ITEM_ID,
1219 ORDER_TYPE,
1220 NEW_SCHEDULE_DATE,
1221 NEW_ORDER_QUANTITY,
1222 FIRM_PLANNED_TYPE,
1223 SOURCE_ORGANIZATION_ID,
1224 SOURCE_SR_INSTANCE_ID,
1225 SHIP_METHOD,
1226 LAST_UPDATE_DATE,
1227 LAST_UPDATED_BY,
1228 CREATION_DATE,
1229 CREATED_BY)
1230 SELECT
1231 PlanIdVar,
1232 msc_supplies_s.nextval,
1233 toOrgID,
1234 toInstanceID,
1235 itemID,
1236 80,
1237 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1238 quantity,
1239 2,
1240 fromOrgID,
1241 fromInstanceID,
1242 transportMode,
1243 SYSDATE, '-1', SYSDATE, '-1'
1244 FROM MSC_INT_ASCP_TRANSPORTATION
1245 WHERE PLAN_ID=PlanIdVar AND
1246 Subcategory <> 'Customer';
1247 EXCEPTION WHEN others THEN
1248 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_ASCP_TRANSPORTATION_001002' || ' : ' || SQLERRM;
1249 raise;
1250 END;
1251
1252
1253 BEGIN
1254 -- CODE GOES HERE
1255 --
1256 -- Fill in data from MSC_INT_ASCP_INVENTORY
1257 --
1258 INSERT INTO MSC_SUPPLIES (PLAN_ID,
1259 TRANSACTION_ID,
1260 ORGANIZATION_ID,
1261 SR_INSTANCE_ID,
1262 INVENTORY_ITEM_ID,
1263 ORDER_TYPE,
1264 NEW_SCHEDULE_DATE,
1265 NEW_ORDER_QUANTITY,
1266 FIRM_PLANNED_TYPE,
1267 LAST_UPDATE_DATE,
1268 LAST_UPDATED_BY,
1269 CREATION_DATE,
1270 CREATED_BY)
1271 SELECT PlanIdVar, msc_supplies_s.nextval, organizationId, instanceId,
1272 itemID, 18, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, onHand, 2,
1273 SYSDATE, '-1', SYSDATE, '-1'
1274 FROM MSC_INT_ASCP_INVENTORY
1275 WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_INVENTORY.CATEGORY='Inventory';
1276 EXCEPTION WHEN others THEN
1277 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_INVENTORY_001003' || ' : ' || SQLERRM;
1278 raise;
1279 END;
1280
1281 BEGIN
1282 -- CODE GOES HERE
1283 --
1284 -- Fill in data from MSC_INT_ASCP_MFG_PLAN_MACHINE
1285 -- Note that the temp table has resource and department id
1286 -- to distinguish records but these two ids are not relevant
1287 -- to supplies. As a result if we directly write to the table
1288 -- we will get duplcates. Change the selection base to pick unique rows.
1289 --
1290 INSERT INTO MSC_SUPPLIES (PLAN_ID,
1291 TRANSACTION_ID,
1292 ORGANIZATION_ID,
1293 SR_INSTANCE_ID,
1294 INVENTORY_ITEM_ID,
1295 ORDER_TYPE,
1296 NEW_SCHEDULE_DATE,
1297 NEW_ORDER_QUANTITY,
1298 FIRM_PLANNED_TYPE,
1299 LAST_UPDATE_DATE,
1300 LAST_UPDATED_BY,
1301 CREATION_DATE,
1302 CREATED_BY)
1303 select PlanIdVar, msc_supplies_s.nextval,
1304 organizationID, instanceID, itemID, 88,
1305 TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, flow,
1306 2,
1307 SYSDATE, '-1', SYSDATE, '-1'
1308 from (
1309 ( select distinct organizationID, instanceID, itemID, PeriodEnd, flow
1310 from MSC_INT_ASCP_MFG_PLAN_MACHINE where PLAN_ID=PlanIdVar AND CATEGORY='Manufacturing')
1311 UNION
1312 ( select distinct organizationID, instanceID, itemID, PeriodEnd, flow
1313 from MSC_INT_ASCP_MFG_PLAN_LABOUR where PLAN_ID=PlanIdVar AND CATEGORY='Manufacturing') );
1314 EXCEPTION WHEN others THEN
1315 g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_MFG_PLAN_MACHINE_001004' || ' : ' || SQLERRM;
1316 raise;
1317 END;
1318
1319
1320 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1321 -- checkpoint commit;
1322 Status := 'SUCCESS';
1323
1324 EXCEPTION
1325 WHEN others THEN
1326 Status := g_ErrorCode;
1327 ROLLBACK;
1328
1329 END SET_ASCP_SUPPLIES;
1330
1331
1332
1333 PROCEDURE SET_ASCP_SAFETY_STOCKS (
1334 Status OUT NOCOPY VARCHAR2,
1335 PlanIdVar IN NUMBER
1336 ) AS
1337 g_ErrorCode VARCHAR2(1000);
1338 BEGIN
1339 /* implementation starts here */
1340 -- init global variables
1341 g_ErrorCode := '';
1342
1343
1344 -- delete records from MSC_SAFETY_STOCKS table for the given PlanId, if any
1345 BEGIN
1346 DELETE FROM MSC_SAFETY_STOCKS WHERE PLAN_ID=PlanIdVar;
1347 EXCEPTION WHEN others THEN
1348 NULL; -- do nothing
1349 END;
1350
1351 BEGIN
1352 -- CODE GOES HERE
1353 INSERT INTO MSC_SAFETY_STOCKS (PLAN_ID,
1354 ORGANIZATION_ID,
1355 SR_INSTANCE_ID,
1356 INVENTORY_ITEM_ID,
1357 PERIOD_START_DATE,
1358 SAFETY_STOCK_QUANTITY,
1359 TASK_ID,
1360 LAST_UPDATE_DATE,
1361 LAST_UPDATED_BY,
1362 CREATION_DATE,
1363 CREATED_BY)
1364 SELECT PlanIdVar, OrganizationID, InstanceID,
1365 ItemID, TO_DATE(Period,'YYYY-MM-DD'), Safety,
1366 ROWNUM, --BUGBUG this value is in, just to make index unique
1367 SYSDATE, '-1', SYSDATE, '-1'
1368 FROM MSC_INT_ASCP_INVENTORY
1369 WHERE PLAN_ID=PlanIdVar;
1370 -- NOTE: Last four fields: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY are non-NULL,
1371 -- so I harcoded an arbitrary value
1372 EXCEPTION WHEN others THEN
1373 g_ErrorCode := 'ERROR_UPDATE_MSC_SAFETY_STOCKS_001001' || ' : ' || SQLERRM;
1374 raise;
1375 END;
1376
1377
1378
1379
1380 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1381 -- checkpoint commit;
1382 Status := 'SUCCESS';
1383
1384 EXCEPTION
1385 WHEN others THEN
1386 Status := g_ErrorCode;
1387 ROLLBACK;
1388 END SET_ASCP_SAFETY_STOCKS;
1389
1390
1391 PROCEDURE SET_ASCP_DEPARTMENT_RESOURCES (
1392 Status OUT NOCOPY VARCHAR2,
1393 PlanIdVar IN NUMBER
1394 ) AS
1395 g_ErrorCode VARCHAR2(1000);
1396 BEGIN
1397 /* implementation starts here */
1398 -- init global variables
1399 g_ErrorCode := '';
1400
1401
1402 -- delete records from MSC_DEPARTMENT_RESOURCES table for the given PlanId, if any
1403 BEGIN
1404 DELETE FROM MSC_DEPARTMENT_RESOURCES WHERE PLAN_ID=PlanIdVar;
1405 EXCEPTION WHEN others THEN
1406 NULL; -- do nothing
1407 END;
1408
1409 BEGIN
1410 -- CODE GOES HERE
1411 -- insert records from MSC_INT_ASCP_MACHINE_UTIL
1412 INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
1413 ORGANIZATION_ID,
1414 SR_INSTANCE_ID,
1415 RESOURCE_ID,
1416 DEPARTMENT_ID,
1417 OWNING_DEPARTMENT_ID,
1418 CAPACITY_UNITS,
1419 RESOURCE_TYPE,
1420 RESOURCE_CODE,
1421 RESOURCE_DESCRIPTION,
1422 DEPARTMENT_CODE,
1423 DEPARTMENT_DESCRIPTION,
1424 DEPARTMENT_CLASS,
1425 RESOURCE_GROUP_NAME,
1426 BOTTLENECK_FLAG,
1427 LINE_FLAG,
1428 AGGREGATE_RESOURCE_FLAG,
1429 AVAILABLE_24_HOURS_FLAG,
1430 LAST_UPDATE_DATE,
1431 LAST_UPDATED_BY,
1432 CREATION_DATE,
1433 CREATED_BY)
1434 SELECT DISTINCT MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID,
1435 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID,
1436 MSC_INT_ASCP_MACHINE_UTIL.InstanceID,
1437 MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
1438 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID,
1439 CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.ResourceID = -1
1440 THEN NULL
1441 ELSE MSC_INT_ASCP_MACHINE_UTIL.DepartmentID
1442 END,--OWNING_DEPARTMENT_ID
1443 NULL, --CAPACITY_UNITS: nullable column, resource units
1444 1, -- RESOURCE_TYPE it is a machine
1445 MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1446 MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1447 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449 NULL, --DEPARTMENT_CLASS,
1450 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451 MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1452 MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1453 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454 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
1455 SYSDATE, '-1', SYSDATE, '-1'
1456 FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458 MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460 MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1461 WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
1462 EXCEPTION WHEN others THEN
1463 g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;
1464 raise;
1465 END;
1466
1467 BEGIN
1468 -- CODE GOES HERE
1469 -- insert records from MSC_INT_ASCP_LABOUR_UTIL
1470 INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
1471 ORGANIZATION_ID,
1472 SR_INSTANCE_ID,
1473 RESOURCE_ID,
1474 DEPARTMENT_ID,
1475 OWNING_DEPARTMENT_ID,
1476 CAPACITY_UNITS,
1477 RESOURCE_TYPE,
1478 RESOURCE_CODE,
1479 RESOURCE_DESCRIPTION,
1480 DEPARTMENT_CODE,
1481 DEPARTMENT_DESCRIPTION,
1482 DEPARTMENT_CLASS,
1483 RESOURCE_GROUP_NAME,
1484 BOTTLENECK_FLAG,
1485 LINE_FLAG,
1486 AGGREGATE_RESOURCE_FLAG,
1487 AVAILABLE_24_HOURS_FLAG,
1488 LAST_UPDATE_DATE,
1489 LAST_UPDATED_BY,
1490 CREATION_DATE,
1491 CREATED_BY)
1492 SELECT DISTINCT MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID,
1493 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID,
1494 MSC_INT_ASCP_LABOUR_UTIL.InstanceID,
1495 MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
1496 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID,
1497 CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.ResourceID = -1
1498 THEN NULL
1499 ELSE MSC_INT_ASCP_LABOUR_UTIL.DepartmentID
1500 END,--OWNING_DEPARTMENT_ID
1501 NULL, --CAPACITY_UNITS: nullable column, resource units
1502 2, -- RESOURCE_TYPE - it is a labour
1503 MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1504 MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1505 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506 MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507 NULL, --DEPARTMENT_CLASS,
1508 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509 MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1510 MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1511 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512 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
1513 SYSDATE, '-1', SYSDATE, '-1'
1514 FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516 MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518 MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1519 WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
1520 EXCEPTION WHEN others THEN
1521 g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;
1522 raise;
1523 END;
1524
1525
1526
1527 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1528 -- checkpoint commit;
1529 Status := 'SUCCESS';
1530
1531 EXCEPTION
1532 WHEN others THEN
1533 Status := g_ErrorCode;
1534 ROLLBACK;
1535 END SET_ASCP_DEPARTMENT_RESOURCES;
1536
1537 PROCEDURE SET_ASCP_RES_SUMMARY (
1538 Status OUT NOCOPY VARCHAR2,
1539 PlanIdVar IN NUMBER,
1540 ScenarioNameVar OUT NOCOPY VARCHAR2
1541 ) AS
1542 g_ErrorCode VARCHAR2(1000);
1543 BEGIN
1544 /* implementation starts here */
1545 -- init global variables
1546 g_ErrorCode := '';
1547 ScenarioNameVar := '';
1548
1549
1550 -- delete records from MSC_RES_SUMMARY table for the given PlanId, if any
1551 BEGIN
1552 DELETE FROM MSC_BIS_RES_SUMMARY WHERE PLAN_ID=PlanIdVar;
1553 EXCEPTION WHEN others THEN
1554 NULL; -- do nothing
1555 END;
1556
1557 BEGIN
1558 select DISTINCT ScenarioName into ScenarioNameVar from MSC_INT_ASCP_KPI where PLAN_ID=PlanIdVar;
1559 EXCEPTION WHEN others THEN
1560 ScenarioNameVar := '';
1561 END;
1562
1563 BEGIN
1564 -- CODE GOES HERE
1565 -- insert records from MSC_INT_ASCP_MACHINE_UTIL
1566 INSERT INTO MSC_BIS_RES_SUMMARY (PLAN_ID,
1567 ORGANIZATION_ID,
1568 SR_INSTANCE_ID,
1569 DEPARTMENT_ID,
1570 RESOURCE_ID,
1571 DETAIL_LEVEL,
1572 PERIOD_TYPE,
1573 RESOURCE_DATE,
1574 REQUIRED_HOURS,
1575 AVAILABLE_HOURS,
1576 UTILIZATION,
1577 RESOURCE_GROUP,
1578 OVERUTILIZATION_COST,
1579 RESOURCE_COST)
1580 SELECT MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID, MSC_INT_ASCP_MACHINE_UTIL.OrganizationID,
1581 MSC_INT_ASCP_MACHINE_UTIL.InstanceID, MSC_INT_ASCP_MACHINE_UTIL.DepartmentID, MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
1582 CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
1583 TO_DATE(MSC_INT_ASCP_MACHINE_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_MACHINE_UTIL.USED,
1584 MSC_INT_ASCP_MACHINE_UTIL.CAPACITY, MSC_INT_ASCP_MACHINE_UTIL.UTILIZATION,
1585 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_MACHINE_UTIL.OVERCOST,
1586 -- There may be multiple instances of the same machine (same dept and res id). We do not want to
1587 -- blindly get production cost from KPI table since it does not distinguish the instances. Instead
1588 -- we get corresponding cost of the resource (assume instances have same cost in the bucket).
1589 MSC_INT_ASCP_MACHINE_UTIL.USED *
1590 (
1591 select distinct MSC_INT_ASCP_MFG_PLAN_MACHINE.RESOURCECOST
1592 from MSC_INT_ASCP_MFG_PLAN_MACHINE
1593 where MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_MFG_PLAN_MACHINE.PLAN_ID AND
1594 MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_INT_ASCP_MFG_PLAN_MACHINE.INSTANCEID AND
1595 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_INT_ASCP_MFG_PLAN_MACHINE.ORGANIZATIONID AND
1596 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_INT_ASCP_MFG_PLAN_MACHINE.DEPARTMENTID AND
1597 MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_INT_ASCP_MFG_PLAN_MACHINE.RESOURCEID AND
1598 MSC_INT_ASCP_MACHINE_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_MACHINE.PERIODEND
1599 )
1600 FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES
1601 WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1602 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603 MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604 MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1605 MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606 MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1607
1608 --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)
1609 --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1610 --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1611 --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612 --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613 --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1614 --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1615 --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1616 --MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1617 --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND
1618 --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_INT_ASCP_KPI.ORGANIZATIONID (+) AND
1619 --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_INT_ASCP_KPI.ITEMID (+) AND -- the dept and item are toggled due to lack of category
1620 --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_INT_ASCP_KPI.DEPARTMENTID (+) AND -- toggled dept and resource id because in KPI export
1621 --MSC_INT_ASCP_MACHINE_UTIL.PERIODEND = MSC_INT_ASCP_KPI.PERIODEND (+);
1622 EXCEPTION WHEN others THEN
1623 g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;
1624 raise;
1625 END;
1626
1627 BEGIN
1628 -- CODE GOES HERE
1629 -- insert records from MSC_INT_ASCP_LABOUR_UTIL
1630 INSERT INTO MSC_BIS_RES_SUMMARY (PLAN_ID,
1631 ORGANIZATION_ID,
1632 SR_INSTANCE_ID,
1633 DEPARTMENT_ID,
1634 RESOURCE_ID,
1635 DETAIL_LEVEL,
1636 PERIOD_TYPE,
1637 RESOURCE_DATE,
1638 REQUIRED_HOURS,
1639 AVAILABLE_HOURS,
1640 UTILIZATION,
1641 RESOURCE_GROUP,
1642 OVERUTILIZATION_COST,
1643 RESOURCE_COST)
1644 SELECT MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID, MSC_INT_ASCP_LABOUR_UTIL.OrganizationID,
1645 MSC_INT_ASCP_LABOUR_UTIL.InstanceID, MSC_INT_ASCP_LABOUR_UTIL.DepartmentID,
1646 MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
1647 CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
1648 TO_DATE(MSC_INT_ASCP_LABOUR_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_LABOUR_UTIL.USED,
1649 MSC_INT_ASCP_LABOUR_UTIL.CAPACITY, MSC_INT_ASCP_LABOUR_UTIL.UTILIZATION,
1650 MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_LABOUR_UTIL.OVERCOST,
1651 -- There may be multiple instances of the same crew (same dept and res id). We do not want to
1652 -- blindly get production cost from KPI table since it does not distinguish the instances. Instead
1653 -- we get corresponding cost of the resource (assume instances have same cost in the bucket).
1654 MSC_INT_ASCP_LABOUR_UTIL.USED *
1655 (
1656 select distinct MSC_INT_ASCP_MFG_PLAN_LABOUR.RESOURCECOST
1657 from MSC_INT_ASCP_MFG_PLAN_LABOUR
1658 where MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_MFG_PLAN_LABOUR.PLAN_ID AND
1659 MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_INT_ASCP_MFG_PLAN_LABOUR.INSTANCEID AND
1660 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_INT_ASCP_MFG_PLAN_LABOUR.ORGANIZATIONID AND
1661 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_INT_ASCP_MFG_PLAN_LABOUR.DEPARTMENTID AND
1662 MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_INT_ASCP_MFG_PLAN_LABOUR.RESOURCEID AND
1663 MSC_INT_ASCP_LABOUR_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_LABOUR.PERIODEND
1664 )
1665 FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES
1666 WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1667 MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668 MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669 MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1670 MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671 MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1672
1673 --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )
1674 --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1675 --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1676 --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677 --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678 --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1679 --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1680 --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1681 --MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1682 --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND
1683 --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_INT_ASCP_KPI.ORGANIZATIONID (+) AND
1684 --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_INT_ASCP_KPI.ITEMID (+) AND
1685 --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_INT_ASCP_KPI.DEPARTMENTID (+) AND
1686 --MSC_INT_ASCP_LABOUR_UTIL.PERIODEND = MSC_INT_ASCP_KPI.PERIODEND (+);
1687 EXCEPTION WHEN others THEN
1688 g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;
1689 raise;
1690 END;
1691
1692
1693
1694 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
1695 -- checkpoint commit;
1696 Status := 'SUCCESS';
1697
1698 EXCEPTION
1699 WHEN others THEN
1700 Status := g_ErrorCode;
1701 ROLLBACK;
1702 END SET_ASCP_RES_SUMMARY;
1703
1704 PROCEDURE SET_ASCP_BIS_INV_DETAIL (
1705 Status OUT NOCOPY VARCHAR2,
1706 PlanIdVar IN NUMBER
1707 ) AS
1708 g_ErrorCode VARCHAR2(1000);
1709 BEGIN
1710 /* implementation starts here */
1711 -- init global variables
1712 g_ErrorCode := '';
1713
1714
1715 -- delete all from temporary tables to aid in the calculation of ProductionCost in the main procedure...
1716
1717 BEGIN
1718 DELETE FROM msc_int_ascp_prodcost_kpi; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar; --??BUGBUG ??
1719 EXCEPTION WHEN others THEN
1720 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001001' || ' : ' || SQLERRM;
1721 raise;
1722 END;
1723
1724 BEGIN
1725 DELETE FROM msc_int_ascp_totalprodcost; -- where msc_int_ascp_totalprodcost.plan_id=PlanIdVar; --??BUGBUG ??
1726 EXCEPTION WHEN others THEN
1727 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001002' || ' : ' || SQLERRM;
1728 raise;
1729 END;
1730
1731
1732 -- delete records from MSC_BIS_INV_DETAIL table for the given PlanId, if any
1733 BEGIN
1734 DELETE FROM MSC_BIS_INV_DETAIL WHERE PLAN_ID=PlanIdVar;
1735 EXCEPTION WHEN others THEN
1736 NULL; -- do nothing
1737 END;
1738
1739 BEGIN
1740 -- CODE GOES HERE
1741 -- fill in data from msc_int_ascp_mfg_plan_labour and msc_int_ascp_mfg_plan_machine
1742 INSERT INTO msc_int_ascp_prodcost_kpi ( plan_id,
1743 organizationID,
1744 instanceID,
1745 itemID,
1746 periodEnd,
1747 prodCost )
1748 SELECT plan_id, organizationID, instanceID, itemID, periodEnd, SUM(resourcecost*resourceusage)
1749 FROM msc_int_ascp_mfg_plan_machine
1750 WHERE plan_id=PlanIdVar
1751 GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
1752 EXCEPTION WHEN others THEN
1753 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001004' || ' : ' || SQLERRM;
1754 raise;
1755 END;
1756
1757 BEGIN
1758 -- CODE GOES HERE
1759 -- fill in data from msc_int_ascp_mfg_plan_labour and msc_int_ascp_mfg_plan_machine
1760 INSERT INTO msc_int_ascp_prodcost_kpi ( plan_id,
1761 organizationID,
1762 instanceID,
1763 itemID,
1764 periodEnd,
1765 prodCost )
1766 SELECT plan_id, organizationID, instanceID, itemID, periodEnd, SUM
1767 (resourcecost*resourceusage)
1768 FROM msc_int_ascp_mfg_plan_labour
1769 WHERE plan_id=PlanIdVar
1770 GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
1771 EXCEPTION WHEN others THEN
1772 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001005' || ' : ' || SQLERRM;
1773 raise;
1774 END;
1775
1776 BEGIN
1777 -- CODE GOES HERE
1778 -- fill in data from msc_int_ascp_prodcost_kpi
1779 -- Due to reansporation cost being mvoed out of msc_int_ascp_kpi, the table
1780 -- no longer has corresponding org-item records for heere to compare. But the
1781 -- records are already unique so we directly sum them up.
1782 INSERT INTO msc_int_ascp_totalprodcost ( plan_id,
1783 organizationID,
1784 instanceID,
1785 itemID,
1786 periodEnd,
1787 totalProdCost )
1788 SELECT msc_int_ascp_prodcost_kpi.plan_id,
1789 msc_int_ascp_prodcost_kpi.organizationID, msc_int_ascp_prodcost_kpi.instanceID,
1790 msc_int_ascp_prodcost_kpi.itemID, msc_int_ascp_prodcost_kpi.periodEnd,
1791 SUM(msc_int_ascp_prodcost_kpi.prodCost)
1792 FROM msc_int_ascp_prodcost_kpi
1793 --, MSC_INT_ASCP_KPI
1794 WHERE
1795 --MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar AND
1796 msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar -- AND
1797 --msc_int_ascp_prodcost_kpi.organizationID=MSC_INT_ASCP_KPI.OrganizationID AND
1798 --msc_int_ascp_prodcost_kpi.itemID=MSC_INT_ASCP_KPI.ItemID AND
1799 --msc_int_ascp_prodcost_kpi.periodEnd=MSC_INT_ASCP_KPI.PERIODEND AND
1800 --msc_int_ascp_prodcost_kpi.instanceID=MSC_INT_ASCP_KPI.InstanceID
1801 GROUP BY msc_int_ascp_prodcost_kpi.plan_id,
1802 msc_int_ascp_prodcost_kpi.organizationID, msc_int_ascp_prodcost_kpi.instanceID,
1803 msc_int_ascp_prodcost_kpi.itemID, msc_int_ascp_prodcost_kpi.PeriodEnd;
1804 EXCEPTION WHEN others THEN
1805 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001006' || ' : ' || SQLERRM;
1806 raise;
1807 END;
1808
1809 -- final table: MSC_BIS_INV_DETAIL
1810
1811 ----------------------------------------
1812 -- Purchasing cost
1813 ----------------------------------------
1814 BEGIN
1815 -- CODE GOES HERE
1816 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
1817 ORGANIZATION_ID,
1818 SR_INSTANCE_ID,
1819 INVENTORY_ITEM_ID,
1820 DETAIL_LEVEL,
1821 PERIOD_TYPE,
1822 DETAIL_DATE,
1823 MDS_PRICE,
1824 ZONE_ID,
1825 PRODUCTION_COST,
1826 PURCHASING_COST,
1827 CARRYING_COST,
1828 TRANSPORTATION_COST,
1829 OTHER_COST,
1830 PAB,
1831 TOTAL_COST,
1832 SERVICE_LEVEL_1,
1833 SERVICE_LEVEL_2,
1834 SHIP_METHOD,
1835 SUPPLIER_ID,
1836 SUPPLIER_SITE_ID,
1837 SUPPLIER_USAGE,
1838 SUPPLIER_CAPACITY,
1839 SOURCE_ORG_INSTANCE_ID,
1840 SOURCE_ORGANIZATION_ID,
1841 CUSTOMER_SITE_ID,
1842 CUSTOMER_ID,
1843 MDS_QUANTITY,
1844 INVENTORY_QUANTITY,
1845 LAST_UPDATE_DATE,
1846 LAST_UPDATED_BY,
1847 CREATION_DATE,
1848 CREATED_BY)
1849 --------------------------------
1850 -- Supplier-dependent records
1851 --------------------------------
1852 SELECT MSC_INT_ASCP_SUPPLY.PLAN_ID,
1853 MSC_INT_ASCP_SUPPLY.OrganizationID,
1854 MSC_INT_ASCP_SUPPLY.InstanceID,
1855 MSC_INT_ASCP_SUPPLY.ItemID,
1856 ( select DISTINCT MSC_INT_ASCP_KPI.PeriodType from MSC_INT_ASCP_KPI where MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar ), -- we do not have it ASCP_Supply report ( BUGBUG does not accept FIRST )
1857 1,
1858 TO_DATE( MSC_INT_ASCP_SUPPLY.PeriodEnd,'YYYY-MM-DD' )-1/86400,
1859 0, --TO_NUMBER(NULL) MDS_PRICE,
1860 -23453, -- ZONEID,
1861 0, --TO_NUMBER(NULL) PRODUCTION_COST,
1862 -- supplier related column: purchase cost
1863 MSC_INT_ASCP_SUPPLY.COST * MSC_INT_ASCP_SUPPLY.SUPPLY,
1864 0,--TO_NUMBER(NULL) CARRYING_COST,
1865 0,--TO_NUMBER(NULL) TRANSPORTATION_COST,
1866 0,--TO_NUMBER(NULL) OTHER_COST,
1867 0,--TO_NUMBER(NULL) PAB,
1868 0,--TO_NUMBER(NULL) TOTAL_COST,
1869 0,--TO_NUMBER(NULL) SERVICE_LEVEL_1,
1870 0,--TO_NUMBER(NULL) SERVICE_LEVEL_2,
1871 null,--TO_CHAR(NULL) SHIP_METHOD,
1872 -- supplier related columns
1873 MSC_INT_ASCP_SUPPLY.SupplierID,
1874 MSC_INT_ASCP_SUPPLY.SupplierSiteID,
1875 MSC_INT_ASCP_SUPPLY.SUPPLY,
1876 MSC_INT_ASCP_SUPPLY.CAPACITY,
1877 -23453, -- SOURCE_ORG_INSTANCE_ID
1878 -23453, -- SOURCE_ORGANIZATION_ID
1879 -23453, -- CUSTOMER_SITE_ID
1880 -23453, -- CUSTOMER_ID
1881 0,--TO_NUMBER(NULL) MDS_QUANTITY,
1882 0,--TO_NUMBER(NULL) INVENTORY_QUANTITY,
1883 SYSDATE, '-1', SYSDATE, '-1'
1884 FROM MSC_INT_ASCP_SUPPLY
1885 WHERE MSC_INT_ASCP_SUPPLY.PLAN_ID=PlanIdVar;
1886 EXCEPTION WHEN others THEN
1887 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001008' || ' : ' || SQLERRM;
1888 raise;
1889 END;
1890
1891 ----------------------------------------
1892 -- Transportation cost + Contrained Forecast
1893 ----------------------------------------
1894 BEGIN
1895 -- CODE GOES HERE
1896 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
1897 ORGANIZATION_ID,
1898 SR_INSTANCE_ID,
1899 INVENTORY_ITEM_ID,
1900 DETAIL_LEVEL,
1901 PERIOD_TYPE,
1902 DETAIL_DATE,
1903 SHIP_METHOD,
1904 TRANSPORTATION_COST,
1905 MDS_QUANTITY,
1906 INVENTORY_QUANTITY,
1907 SUPPLIER_ID,
1908 SUPPLIER_SITE_ID,
1909 SOURCE_ORG_INSTANCE_ID,
1910 SOURCE_ORGANIZATION_ID,
1911 ZONE_ID,
1912 CUSTOMER_SITE_ID,
1913 CUSTOMER_ID,
1914 LAST_UPDATE_DATE,
1915 LAST_UPDATED_BY,
1916 CREATION_DATE,
1917 CREATED_BY,
1918 LAST_UPDATE_LOGIN)
1919 SELECT
1920 PlanIdVar,
1921 -- Next 2 lines are changed to accomodate the S&OP request to populate dest org with source org when shipping to a customer:
1922 -- case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN -23453 else MSC_INT_ASCP_TRANSPORTATION.ToOrgID end,
1923 -- case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN -23453 else MSC_INT_ASCP_TRANSPORTATION.ToInstanceID end,
1924 case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN MSC_INT_ASCP_TRANSPORTATION.FromOrgID else MSC_INT_ASCP_TRANSPORTATION.ToOrgID end,
1925 case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN MSC_INT_ASCP_TRANSPORTATION.FromInstanceID else MSC_INT_ASCP_TRANSPORTATION.ToInstanceID end,
1926 MSC_INT_ASCP_TRANSPORTATION.ItemID,
1927 ( select DISTINCT MSC_INT_ASCP_KPI.PeriodType from MSC_INT_ASCP_KPI where MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar ), -- we do not have it ASCP_Transportation report, -- DETAIL_LEVEL ( BUGBUG does not accept FIRST )
1928 1, --PERIOD_TYPE
1929 TO_DATE( MSC_INT_ASCP_TRANSPORTATION.PeriodEnd,'YYYY-MM-DD' )-1/86400,
1930 MSC_INT_ASCP_TRANSPORTATION.TransportMode,
1931 MSC_INT_ASCP_TRANSPORTATION.Cost * MSC_INT_ASCP_TRANSPORTATION.Quantity,
1932 -- Constrained Forecast
1933 CASE
1934 WHEN MSC_INT_ASCP_TRANSPORTATION.CATEGORY='Transportation' THEN
1935 ( case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Inter-Organization' then
1936 ( case when (select count( MSC_INT_ASCP_DEMANDS.Satisfied ) from MSC_INT_ASCP_DEMANDS
1937 where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
1938 MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
1939 MSC_INT_ASCP_DEMANDS.InstanceID = MSC_INT_ASCP_TRANSPORTATION.ToInstanceID and
1940 MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
1941 MSC_INT_ASCP_DEMANDS.OrganizationID = MSC_INT_ASCP_TRANSPORTATION.ToOrgID and
1942 MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) > 0 then ( select MSC_INT_ASCP_DEMANDS.Satisfied from MSC_INT_ASCP_DEMANDS
1943 where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
1944 MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
1945 MSC_INT_ASCP_DEMANDS.InstanceID = MSC_INT_ASCP_TRANSPORTATION.ToInstanceID and
1946 MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
1947 MSC_INT_ASCP_DEMANDS.OrganizationID = MSC_INT_ASCP_TRANSPORTATION.ToOrgID and
1948 MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) else 0 end )
1949 else
1950 ( case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' then
1951 ( case when ( SELECT COUNT( MSC_INT_ASCP_DEMANDS.Satisfied ) from MSC_INT_ASCP_DEMANDS
1952 where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
1953 MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
1954 ( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) = 0 ) and
1955 MSC_INT_ASCP_DEMANDS.ZoneID = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
1956 MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
1957 --MSC_INT_ASCP_DEMANDS.OrganizationID = -1 and --BUGBUG Constrained Forecast - SS in ??
1958 --MSC_INT_ASCP_TRANSPORTATION.ToOrgID is NULL and --BUGBUG Constrained Forecast - SS in ??
1959 MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) > 0 then
1960 ( select MSC_INT_ASCP_DEMANDS.Satisfied from MSC_INT_ASCP_DEMANDS
1961 where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
1962 MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
1963 ( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) = 0 ) and
1964 MSC_INT_ASCP_DEMANDS.ZoneID = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
1965 MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
1966 --MSC_INT_ASCP_DEMANDS.OrganizationID = -1 and --BUGBUG Constrained Forecast - SS in ??
1967 --MSC_INT_ASCP_TRANSPORTATION.ToOrgID is NULL and --BUGBUG Constrained Forecast - SS in ??
1968 MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd )
1969 else ( case when ( SELECT COUNT( MSC_INT_ASCP_DEMANDS.Satisfied ) from MSC_INT_ASCP_DEMANDS
1970 where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
1971 MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
1972 ( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) <> 0 ) and
1973 SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)+1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToOrgID) and
1974 MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
1975 SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, 1, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)-1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
1976 MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd) > 0 then
1977 ( select MSC_INT_ASCP_DEMANDS.Satisfied from MSC_INT_ASCP_DEMANDS
1978 where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
1979 MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
1980 ( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) <> 0 ) and
1981 SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)+1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToOrgID) and
1982 MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
1983 SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, 1, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)-1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
1984 MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) else 0 end )
1985 end )
1986 else 0
1987 end )
1988 end )
1989 ELSE 0
1990 END,
1991 0,
1992 -23453, --SUPPLIER_ID
1993 -23453, --SUPPLIER_SITE_ID
1994 MSC_INT_ASCP_TRANSPORTATION.FromInstanceID, --SOURCE_ORG_INSTANCE_ID
1995 MSC_INT_ASCP_TRANSPORTATION.FromOrgID, --SOURCE_ORGANIZATION_ID
1996
1997 --Zone_Id
1998 case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN
1999 CASE WHEN ( ToInstanceID is not null ) AND ( ToOrgID is null )
2000 THEN ToInstanceID
2001 else -23453 end
2002 ELSE
2003 -23453
2004 END,
2005
2006 --Customer_Site_Id
2007 case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN
2008 CASE WHEN ( ToInstanceID is not null ) AND ( ToOrgID is not null )
2009 THEN ToOrgID
2010 else -23453 end
2011 ELSE
2012 -23453
2013 END,
2014 -- Customer_Id
2015 case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN
2016 CASE WHEN ( ToInstanceID is not null ) AND ( ToOrgID is not null )
2017 THEN ToInstanceID
2018 else -23453 end
2019 ELSE
2020 -23453
2021 END,
2022 SYSDATE, '-1',
2023 SYSDATE, '-1',
2024 -1
2025 FROM MSC_INT_ASCP_TRANSPORTATION
2026 WHERE MSC_INT_ASCP_TRANSPORTATION.PLAN_ID=PlanIdVar;
2027 EXCEPTION WHEN others THEN
2028 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001009' || ' : ' || SQLERRM;
2029 raise;
2030 END;
2031
2032 BEGIN
2033 --------------------------------
2034 -- Supplier-independent records ( Revenue, Production, Carry and Other costs )
2035 --------------------------------
2036 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
2037 ORGANIZATION_ID,
2038 SR_INSTANCE_ID,
2039 INVENTORY_ITEM_ID,
2040 DETAIL_LEVEL,
2041 PERIOD_TYPE,
2042 DETAIL_DATE,
2043 MDS_PRICE,
2044 SOURCE_ORG_INSTANCE_ID,
2045 SOURCE_ORGANIZATION_ID,
2046 ZONE_ID,
2047 CUSTOMER_SITE_ID,
2048 CUSTOMER_ID,
2049 PRODUCTION_COST,
2050 CARRYING_COST,
2051 OTHER_COST,
2052 PAB,
2053 SERVICE_LEVEL_1,
2054 SERVICE_LEVEL_2,
2055 SUPPLIER_ID,
2056 SUPPLIER_SITE_ID,
2057 SUPPLIER_USAGE,
2058 SUPPLIER_CAPACITY,
2059 MDS_QUANTITY,
2060 INVENTORY_QUANTITY,
2061 LAST_UPDATE_DATE,
2062 LAST_UPDATED_BY,
2063 CREATION_DATE,
2064 CREATED_BY)
2065 SELECT MSC_INT_ASCP_KPI.PLAN_ID,
2066 -- Next 2 lines are changed to accomodate the S&OP request to populate dest org with source org when shipping to a customer:
2067 -- case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Organization_ID else MSC_INT_ASCP_KPI.OrganizationID end,
2068 -- case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Org_Instance_ID else MSC_INT_ASCP_KPI.InstanceID end,
2069 case when MSC_INT_ASCP_KPI.DemandCost<>0 AND OrganizationID = -23453 THEN Source_Organization_ID ELSE OrganizationID END,
2070 case when MSC_INT_ASCP_KPI.DemandCost<>0 AND InstanceID = -23453 THEN Source_Org_Instance_ID ELSE InstanceID END,
2071 MSC_INT_ASCP_KPI.ItemID,
2072 MSC_INT_ASCP_KPI.PeriodType,
2073 1,
2074 TO_DATE( MSC_INT_ASCP_KPI.PeriodEnd,'YYYY-MM-DD' )-1/86400,
2075 -- demand cost
2076 MSC_INT_ASCP_KPI.DemandCost,
2077 case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Org_Instance_ID else -23453 end, -- SOURCE_ORG_INSTANCE_ID,
2078 case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Organization_ID else -23453 end, -- SOURCE_ORGANIZATION_ID,
2079 MSC_INT_ASCP_KPI.ZoneID,
2080 MSC_INT_ASCP_KPI.Customer_Site_ID, --CUSTOMER_SITE_ID,
2081 MSC_INT_ASCP_KPI.Customer_ID, --CUSTOMER_ID,
2082
2083 -- production_cost: added as separate records
2084 0,
2085 --case when (SELECT COUNT(*) FROM msc_int_ascp_totalprodcost
2086 -- WHERE PLAN_ID=PlanIdVar AND
2087 -- OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
2088 -- ItemID=MSC_INT_ASCP_KPI.ItemID AND
2089 -- PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
2090 -- InstanceID=MSC_INT_ASCP_KPI.InstanceID
2091 -- )=0 then 0
2092 -- else (SELECT DISTINCT ( totalProdCost ) FROM -- ( BUGBUG Does not accept FIRST )
2093 -- msc_int_ascp_totalprodcost
2094 -- WHERE PLAN_ID=PlanIdVar AND
2095 -- OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
2096 -- ItemID=MSC_INT_ASCP_KPI.ItemID AND
2097 -- PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
2098 -- InstanceID=MSC_INT_ASCP_KPI.InstanceID)
2099 --end,
2100
2101 -- carrying cost
2102 MSC_INT_ASCP_KPI.InventoryCost,
2103
2104 -- other cost
2105 MSC_INT_ASCP_KPI.OtherCost,
2106 -- PAB
2107 case when (SELECT COUNT(*) FROM MSC_INT_ASCP_INVENTORY
2108 WHERE PLAN_ID=PlanIdVar AND
2109 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
2110 ItemID=MSC_INT_ASCP_KPI.ItemID AND
2111 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
2112 InstanceID=MSC_INT_ASCP_KPI.InstanceID
2113 )=0 then 0
2114 else (SELECT DISTINCT ( StorageAmount ) FROM MSC_INT_ASCP_INVENTORY -- ( BUGBUG Does not accept FIRST )
2115 WHERE
2116 PLAN_ID=PlanIdVar AND
2117 OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
2118 ItemID=MSC_INT_ASCP_KPI.ItemID AND
2119 PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
2120 InstanceID=MSC_INT_ASCP_KPI.InstanceID )
2121 end,
2122 MSC_INT_ASCP_KPI.DemandFillRate,
2123 1,
2124 -- supplier related columns
2125 -23453, --SUPPLIER_ID,
2126 -23453, --SUPPLIER_SITE_ID,
2127 0,-- TO_NUMBER(NULL) SUPPLIER_USAGE,
2128 0,-- TO_NUMBER(NULL) SUPPLIER_CAPACITY,
2129 0,
2130 0,
2131 SYSDATE, '-1', SYSDATE, '-1'
2132 FROM MSC_INT_ASCP_KPI
2133 WHERE MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar;
2134 EXCEPTION WHEN others THEN
2135 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001010' || ' : ' || SQLERRM;
2136 raise;
2137
2138 END;
2139
2140 ----------------------------------------------
2141 -- Production cost: totalprodcost doesn't
2142 -- necessarily have same org-item combo
2143 -- as msc_int_ascp_kpi, so we insert ditectly
2144 -- from total prod cost
2145 ----------------------------------------------
2146 BEGIN
2147 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
2148 ORGANIZATION_ID,
2149 SR_INSTANCE_ID,
2150 INVENTORY_ITEM_ID,
2151 DETAIL_LEVEL,
2152 PERIOD_TYPE,
2153 DETAIL_DATE,
2154 MDS_PRICE,
2155 ZONE_ID,
2156 PRODUCTION_COST,
2157 PURCHASING_COST,
2158 CARRYING_COST,
2159 TRANSPORTATION_COST,
2160 OTHER_COST,
2161 PAB,
2162 TOTAL_COST,
2163 SERVICE_LEVEL_1,
2164 SERVICE_LEVEL_2,
2165 SHIP_METHOD,
2166 SUPPLIER_ID,
2167 SUPPLIER_SITE_ID,
2168 SUPPLIER_USAGE,
2169 SUPPLIER_CAPACITY,
2170 SOURCE_ORG_INSTANCE_ID,
2171 SOURCE_ORGANIZATION_ID,
2172 CUSTOMER_SITE_ID,
2173 CUSTOMER_ID,
2174 MDS_QUANTITY,
2175 INVENTORY_QUANTITY,
2176 LAST_UPDATE_DATE,
2177 LAST_UPDATED_BY,
2178 CREATION_DATE,
2179 CREATED_BY)
2180 SELECT msc_int_ascp_totalprodcost.PLAN_ID,
2181 msc_int_ascp_totalprodcost.OrganizationID,
2182 msc_int_ascp_totalprodcost.InstanceID,
2183 msc_int_ascp_totalprodcost.ItemID,
2184 ( select DISTINCT MSC_INT_ASCP_KPI.PeriodType from MSC_INT_ASCP_KPI where MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar ), -- we do not have it ASCP_Supply report ( BUGBUG does not accept FIRST )
2185 1,
2186 TO_DATE( msc_int_ascp_totalprodcost.PeriodEnd,'YYYY-MM-DD' )-1/86400,
2187 0, --TO_NUMBER(NULL) MDS_PRICE,
2188 -23453, -- ZONEID,
2189
2190 msc_int_ascp_totalprodcost.totalprodcost,
2191 0, -- purchase cost
2192 0,--TO_NUMBER(NULL) CARRYING_COST,
2193 0,--TO_NUMBER(NULL) TRANSPORTATION_COST,
2194 0,--TO_NUMBER(NULL) OTHER_COST,
2195 0,--TO_NUMBER(NULL) PAB,
2196 0,--TO_NUMBER(NULL) TOTAL_COST,
2197 0,--TO_NUMBER(NULL) SERVICE_LEVEL_1,
2198 0,--TO_NUMBER(NULL) SERVICE_LEVEL_2,
2199 null,--TO_CHAR(NULL) SHIP_METHOD,
2200 -23453,
2201 -23453,
2202 0,
2203 0,
2204 -23453, -- SOURCE_ORG_INSTANCE_ID
2205 -23453, -- SOURCE_ORGANIZATION_ID
2206 -23453, -- CUSTOMER_SITE_ID
2207 -23453, -- CUSTOMER_ID
2208 0,--TO_NUMBER(NULL) MDS_QUANTITY,
2209 0,--TO_NUMBER(NULL) INVENTORY_QUANTITY,
2210 SYSDATE, '-1', SYSDATE, '-1'
2211 FROM msc_int_ascp_totalprodcost
2212 WHERE msc_int_ascp_totalprodcost.PLAN_ID=PlanIdVar;
2213 EXCEPTION WHEN others THEN
2214 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001118' || ' : ' || SQLERRM;
2215 raise;
2216 END;
2217
2218 --------------------------------
2219 -- ItemTravelDistance records
2220 --------------------------------
2221 BEGIN
2222 -- CODE GOES HERE
2223 INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
2224 ORGANIZATION_ID,
2225 SR_INSTANCE_ID,
2226 INVENTORY_ITEM_ID,
2227 DETAIL_LEVEL,
2228 PERIOD_TYPE,
2229 DETAIL_DATE,
2230 SHIP_METHOD,
2231 ITEM_TRAVEL_DISTANCE,
2232 MDS_QUANTITY,
2233 INVENTORY_QUANTITY,
2234 SUPPLIER_ID,
2235 SUPPLIER_SITE_ID,
2236 ZONE_ID,
2237 SOURCE_ORG_INSTANCE_ID,
2238 SOURCE_ORGANIZATION_ID,
2239 CUSTOMER_ID,
2240 CUSTOMER_SITE_ID,
2241 LAST_UPDATE_DATE,
2242 LAST_UPDATED_BY,
2243 CREATION_DATE,
2244 CREATED_BY,
2245 LAST_UPDATE_LOGIN)
2246 SELECT
2247 PlanIdVar,
2248 -23453,
2249 SR_INSTANCE_ID,
2250 INVENTORY_ITEM_ID,
2251 PERIOD_TYPE, -- DETAIL_LEVEL
2252 1, --PERIOD_TYPE
2253 TO_DATE( MSC_INT_ITEM_TRAVEL_DISTANCE.Period_End,'YYYY-MM-DD' )-1/86400,
2254 TRANSPORTATION_MODE,
2255 ITEM_TRAVEL_DISTANCE,
2256 0,
2257 0,
2258 -23453, --SUPPLIER_ID
2259 -23453, --SUPPLIER_SITE_ID
2260 -23453, --ZONE_ID,
2261 -23453, --SOURCE_ORG_INSTANCE_ID
2262 -23453, --SOURCE_ORGANIZATION_ID
2263 -23453, --CUSTOMER_ID
2264 -23453, --CUSTOMER_SITE_ID
2265 SYSDATE, '-1',
2266 SYSDATE, '-1',
2267 -1
2268 FROM MSC_INT_ITEM_TRAVEL_DISTANCE
2269 WHERE PLAN_ID=PlanIdVar;
2270 EXCEPTION WHEN others THEN
2271 g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001011' || ' : ' || SQLERRM;
2272 raise;
2273 END;
2274
2275 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
2276 -- checkpoint commit;
2277 Status := 'SUCCESS';
2278
2279 EXCEPTION
2280 WHEN others THEN
2281 Status := g_ErrorCode;
2282 ROLLBACK;
2283 END SET_ASCP_BIS_INV_DETAIL;
2284
2285 PROCEDURE SET_ASCP_SRC_RECOMMEND_DETAIL (
2286 Status OUT NOCOPY VARCHAR2,
2287 PlanIdVar IN NUMBER,
2288 AssignmentSetOutIdVar IN NUMBER
2289 ) AS
2290 g_ErrorCode VARCHAR2(1000);
2291 BEGIN
2292 -- 1. and 2. drop, create, populate 2 temp tables msc_int_source1, msc_int_source2
2293 --BEGIN
2294 --DELETE FROM msc_int_source1; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar; --??BUGBUG ??
2295 --EXCEPTION WHEN others THEN
2296 -- g_ErrorCode := 'ERROR_DELETE_msc_int_source1_001011' || ' : ' || SQLERRM;
2297 -- raise;
2298 --END;
2299
2300 --BEGIN
2301 --DELETE FROM msc_int_source2; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar; --??BUGBUG ??
2302 --EXCEPTION WHEN others THEN
2303 -- g_ErrorCode := 'ERROR_DELETE_msc_int_source2_001012' || ' : ' || SQLERRM;
2304 -- raise;
2305 --END;
2306
2307 -- 1. SourceItem
2308 --BEGIN
2309 -- INSERT into msc_int_source1 ( item_code, branch_code )
2310 -- SELECT DISTINCT msc_int_src_recommend_detail.item_code,
2311 -- msc_int_src_recommend_detail.destination_code
2312 -- FROM msc_int_src_recommend_detail
2313 -- WHERE msc_int_src_recommend_detail.available = 'Yes';
2314 --EXCEPTION WHEN others THEN
2315 -- g_ErrorCode := 'ERROR_INSERT_msc_int_source1_001021' || ' : ' || SQLERRM;
2316 -- raise;
2317 --END;
2318
2319 -- 2. SourceItemBranch
2320 --BEGIN
2321 -- INSERT into msc_int_source2 ( item_code, branch_code, enable_date, disable_date )
2322 -- SELECT DISTINCT msc_int_src_recommend_detail.item_code,
2323 -- msc_int_src_recommend_detail.destination_code,
2324 -- TO_DATE(msc_int_src_recommend_detail.start_date, 'YYYY-MM-DD'),
2325 -- TO_DATE(msc_int_src_recommend_detail.end_date, 'YYYY-MM-DD')
2326 -- FROM msc_int_src_recommend_detail
2327 -- WHERE msc_int_src_recommend_detail.available = 'Yes';
2328 --EXCEPTION WHEN others THEN
2329 -- g_ErrorCode := 'ERROR_INSERT_msc_int_source2_001022' || ' : ' || SQLERRM;
2330 -- raise;
2331 --END;
2332
2333 -- 3. clear msc_sr_source_org
2334 --g_ErrorCode := '';
2335 --BEGIN
2336 --DELETE from MSC_SR_SOURCE_ORG
2337 -- WHERE
2338 -- MSC_SR_SOURCE_ORG.SR_RECEIPT_ID in
2339 -- ( select MSC_SR_RECEIPT_ORG.SR_RECEIPT_ID from MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG
2340 -- where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
2341 -- MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID = MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID );
2342 -- EXCEPTION WHEN others THEN
2343 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001001' || ' : ' || SQLERRM;
2344 -- NULL;
2345 --END;
2346
2347 -- 4. clear msc_sr_receipt_org
2348 --BEGIN
2349 --DELETE from MSC_SR_RECEIPT_ORG
2350 -- WHERE
2351 -- MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID in
2352 -- ( select MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID from MSC_SR_ASSIGNMENTS
2353 -- where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar );
2354 -- EXCEPTION WHEN others THEN
2355 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001002' || ' : ' || SQLERRM;
2356 -- NULL;
2357 --END;
2358
2359 -- 5. clear msc_sourcing_rules
2360 --BEGIN
2361 --DELETE from MSC_SOURCING_RULES
2362 -- WHERE
2363 -- MSC_SOURCING_RULES.SOURCING_RULE_ID in
2364 -- ( select MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID from MSC_SR_ASSIGNMENTS
2365 -- where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar );
2366 --EXCEPTION WHEN others THEN
2367 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001003' || ' : ' || SQLERRM;
2368 -- NULL;
2369 --END;
2370
2371 -- 6. clear msc_sr_assignments
2372 --BEGIN
2373 --DELETE from MSC_SR_ASSIGNMENTS
2374 -- WHERE
2375 --MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar;
2376 --EXCEPTION WHEN others THEN
2377 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001004' || ' : ' || SQLERRM;
2378 -- NULL;
2379 --END;
2380
2381 -- 7. clear msc_item_sourcing
2382 BEGIN
2383 DELETE from MSC_ITEM_SOURCING
2384 WHERE MSC_ITEM_SOURCING.PLAN_ID = PlanIdVar;
2385 EXCEPTION WHEN others THEN
2386 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001005' || ' : ' || SQLERRM;
2387 NULL;
2388 END;
2389
2390 -- 8. Insert into MSC_SR_ASSIGNMENTS
2391 --BEGIN
2392 --INSERT into MSC_SR_ASSIGNMENTS (
2393 -- ASSIGNMENT_ID,
2394 -- SR_ASSIGNMENT_ID,
2395 -- SR_ASSIGNMENT_INSTANCE_ID,
2396 -- ASSIGNMENT_SET_ID,
2397 -- ASSIGNMENT_TYPE,
2398 -- SOURCING_RULE_ID,
2399 -- SOURCING_RULE_TYPE,
2400 -- ORGANIZATION_ID,
2401 -- SR_INSTANCE_ID,
2402 -- INVENTORY_ITEM_ID,
2403 -- LAST_UPDATE_DATE,
2404 -- LAST_UPDATED_BY,
2405 -- CREATION_DATE,
2406 -- CREATED_BY)
2407 -- SELECT
2408 -- MSC_SR_ASSIGNMENTS_S.NEXTVAL,
2409 -- -1 * MSC_SR_ASSIGNMENTS_S.NEXTVAL, -- Part of unique key. Maybe should use current value.
2410 -- -1, -- // -1 until we figure out what goes here.
2411 -- assignmentSetOutIdVar,
2412 -- 6, -- // 6 is item-org assignment type
2413 -- MSC_SOURCING_RULES_S.NEXTVAL, -- //Cache these because we cannot look them up by name
2414 -- 1,
2415 -- substr(msc_int_source1.branch_code,instr(msc_int_source1.branch_code,':',1,1)+1),
2416 -- substr(msc_int_source1.item_code,1,instr(msc_int_source1.item_code,':',1,1)-1),
2417 -- substr(msc_int_source1.item_code,instr(msc_int_source1.item_code,':',1,1)+1), -- CHANGE
2418 -- sysdate,
2419 -- '-1',
2420 -- sysdate,
2421 -- '-1'
2422 -- FROM msc_int_source1;
2423 -- EXCEPTION WHEN others THEN
2424 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001006' || ' : ' || SQLERRM;
2425 -- raise;
2426 --END;
2427
2428 -- 9. INSERT into MSC_SOURCING_RULES
2429 --BEGIN
2430 --INSERT into MSC_SOURCING_RULES (
2431 -- SOURCING_RULE_ID,
2432 -- SR_SOURCING_RULE_ID,
2433 -- SR_INSTANCE_ID,
2434 -- ORGANIZATION_ID,
2435 -- SOURCING_RULE_NAME,
2436 -- STATUS,
2437 -- SOURCING_RULE_TYPE,
2438 -- PLANNING_ACTIVE,
2439 -- LAST_UPDATE_DATE,
2440 -- LAST_UPDATED_BY,
2441 -- CREATION_DATE,
2442 -- CREATED_BY,
2443 -- DELETED_FLAG )
2444 -- SELECT DISTINCT
2445 -- MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
2446 -- -1 * MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
2447 -- MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID, -- // I suppose these are the same.
2448 -- MSC_SR_ASSIGNMENTS.ORGANIZATION_ID,
2449 -- (MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_NAME || ':' || MSC_SYSTEM_ITEMS.ITEM_NAME || ':' || MSC_PLAN_ORGANIZATIONS.ORGANIZATION_CODE),
2450 -- 1, -- // STATUS
2451 -- 1, -- // 1 means sourcing rule type
2452 -- 1,
2453 -- sysdate,
2454 -- '-1',
2455 -- sysdate,
2456 -- '-1',
2457 -- '2'
2458 -- FROM MSC_SR_ASSIGNMENTS , MSC_ASSIGNMENT_SETS , MSC_SYSTEM_ITEMS , MSC_PLAN_ORGANIZATIONS -- ( alias org )
2459 -- WHERE
2460 -- MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
2461 -- MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID = MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID and
2462 -- MSC_SYSTEM_ITEMS.SR_INSTANCE_ID = MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID and
2463 -- MSC_SYSTEM_ITEMS.ORGANIZATION_ID = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID and
2464 -- MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID = MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID and
2465 -- MSC_SYSTEM_ITEMS.PLAN_ID = -1 and
2466 -- MSC_PLAN_ORGANIZATIONS.PLAN_ID = planIdVar and
2467 -- MSC_PLAN_ORGANIZATIONS.SR_INSTANCE_ID = MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID and
2468 -- MSC_PLAN_ORGANIZATIONS.ORGANIZATION_ID = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID;
2469 -- EXCEPTION WHEN others THEN
2470 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001007' || ' : ' || SQLERRM;
2471 -- raise;
2472 --END;
2473
2474 -- 10. INSERT into MSC_SR_RECEIPT_ORG
2475 --BEGIN
2476 --INSERT into MSC_SR_RECEIPT_ORG (
2477 -- SR_RECEIPT_ID,
2478 -- SR_SR_RECEIPT_ID,
2479 -- SR_INSTANCE_ID,
2480 -- SR_RECEIPT_ORG,
2481 -- RECEIPT_ORG_INSTANCE_ID,
2482 -- SOURCING_RULE_ID,
2483 -- RECEIPT_PARTNER_ID,
2484 -- RECEIPT_PARTNER_SITE_ID,
2485 -- EFFECTIVE_DATE,
2486 -- DISABLE_DATE,
2487 -- LAST_UPDATE_DATE,
2488 -- LAST_UPDATED_BY,
2489 -- CREATION_DATE,
2490 -- CREATED_BY
2491 -- ) -- // msc_int_source2 == source
2492 -- SELECT
2493 -- MSC_SR_RECEIPT_ORG_S.NEXTVAL,
2494 -- -1 * MSC_SR_RECEIPT_ORG_S.NEXTVAL,
2495 -- 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.
2496 -- substr(msc_int_source2.branch_code,instr(msc_int_source2.branch_code,':',1,1)+1),
2497 -- 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.
2498 -- MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
2499 -- NULL, -- This is empty until we get customer sourcing
2500 -- NULL, -- This is empty until we get customer sourcing
2501 -- msc_int_source2.enable_date,
2502 -- msc_int_source2.disable_date,
2503 -- sysdate,
2504 -- '-1',
2505 -- sysdate,
2506 -- '-1'
2507 -- FROM MSC_SR_ASSIGNMENTS, msc_int_source2
2508 -- WHERE
2509 -- MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
2510 -- MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID || ':' || MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID = msc_int_source2.item_code and
2511 -- MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID || ':' || MSC_SR_ASSIGNMENTS.ORGANIZATION_ID = msc_int_source2.branch_code;
2512 -- EXCEPTION WHEN others THEN
2513 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001008' || ' : ' || SQLERRM;
2514 -- raise;
2515 --END;
2516
2517 -- 11. INSERT into MSC_SR_SOURCE_ORG
2518 --BEGIN
2519 --INSERT into MSC_SR_SOURCE_ORG (
2520 -- SR_SOURCE_ID,
2521 -- SR_SR_SOURCE_ID,
2522 -- SR_RECEIPT_ID,
2523 -- SOURCE_PARTNER_ID,
2524 -- SOURCE_PARTNER_SITE_ID,
2525 -- SR_INSTANCE_ID,
2526 -- SOURCE_ORGANIZATION_ID,
2527 -- SOURCE_ORG_INSTANCE_ID,
2528 -- SHIP_METHOD,
2529 -- ALLOCATION_PERCENT,
2530 -- RANK,
2531 -- SOURCE_TYPE,
2532 -- LAST_UPDATE_DATE,
2533 -- LAST_UPDATED_BY,
2534 -- CREATION_DATE,
2535 -- CREATED_BY
2536 -- )
2537 -- SELECT
2538 -- MSC_SR_SOURCE_ORG_S.NEXTVAL,
2539 -- -1 * MSC_SR_SOURCE_ORG_S.NEXTVAL,
2540 -- MSC_SR_RECEIPT_ORG.SR_RECEIPT_ID,
2541 -- case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2542 -- then substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1)
2543 -- else NULL
2544 -- end,
2545 -- case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2546 -- then substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )
2547 -- else NULL
2548 -- end,
2549 -- MSC_SR_RECEIPT_ORG.SR_INSTANCE_ID,
2550 -- case when not MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2551 -- then substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )
2552 -- else NULL
2553 -- end,
2554 -- case when not MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
2555 -- then substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1)
2556 -- else NULL
2557 -- end,
2558 -- case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Internal'
2559 -- then MSC_INT_SRC_RECOMMEND_DETAIL.transport_mode_code
2560 -- end,
2561 -- NVL(MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_percent,100),
2562 -- MSC_INT_SRC_RECOMMEND_DETAIL.preference,
2563 -- case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Internal' then 1
2564 -- when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Manufactured' then 2
2565 -- when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier' then 3
2566 -- else 1
2567 -- end,
2568 -- sysdate,
2569 -- '-1',
2570 -- sysdate,
2571 -- '-1'
2572 -- FROM MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG, MSC_INT_SRC_RECOMMEND_DETAIL
2573 -- WHERE MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar
2574 -- AND MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID = MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID
2575 -- AND MSC_SR_RECEIPT_ORG.SR_RECEIPT_ORG = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID
2576 -- AND MSC_SR_RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID || ':' || MSC_SR_RECEIPT_ORG.SR_RECEIPT_ORG = MSC_INT_SRC_RECOMMEND_DETAIL.destination_code
2577 -- AND MSC_SR_RECEIPT_ORG.EFFECTIVE_DATE = TO_DATE( MSC_INT_SRC_RECOMMEND_DETAIL.start_date, 'YYYY-MM-DD' )
2578 -- 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)
2579 -- AND MSC_INT_SRC_RECOMMEND_DETAIL.available = 'Yes';
2580 -- EXCEPTION WHEN others THEN
2581 -- g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001009' || ' : ' || SQLERRM;
2582 -- raise;
2583 --END;
2584
2585 -- 12. INSERT into MSC_ITEM_SOURCING for item source count
2586 BEGIN
2587 INSERT into MSC_ITEM_SOURCING (
2588 PLAN_ID,
2589 SR_INSTANCE_ID,
2590 ORGANIZATION_ID,
2591 SR_INSTANCE_ID2,
2592 SOURCE_ORGANIZATION_ID,
2593 INVENTORY_ITEM_ID,
2594 EFFECTIVE_DATE,
2595 ASSIGNMENT_ID,
2596 ASSIGNMENT_SET_ID,
2597 ASSIGNMENT_TYPE,
2598 SOURCING_RULE_TYPE,
2599 SUPPLIER_ID,
2600 SUPPLIER_SITE_ID,
2601 CUSTOMER_ID,
2602 CUSTOMER_SITE_ID,
2603 ZONE_ID,
2604 SHIP_METHOD,
2605 LAST_UPDATE_DATE,
2606 LAST_UPDATED_BY,
2607 CREATION_DATE,
2608 CREATED_BY
2609 )
2610 SELECT
2611 PlanIdVar,
2612 -- Destination Instance: when destination is customer, assign to -23453
2613 CASE WHEN (sourcing_type = 'Sale') THEN
2614 -23453
2615 ELSE
2616 TO_NUMBER(substr(destination_code,1,instr(destination_code,':',1,1)-1))
2617 END,
2618 -- Destination Org: when destination is customer, assign to -23453
2619 CASE WHEN (sourcing_type = 'Sale') THEN
2620 -23453
2621 ELSE
2622 TO_NUMBER(substr(destination_code,instr(destination_code,':',1,1)+1))
2623 END,
2624 -- Origin Instance: when origin is supplier, assign to -23453
2625 CASE WHEN (sourcing_type = 'Supplier') THEN
2626 -23453
2627 ELSE
2628 TO_NUMBER(substr(origin_code,1,instr(origin_code,':',1,1)-1))
2629 END,
2630 -- Origin Org: when origin is supplier, assign to -23453
2631 CASE WHEN (sourcing_type = 'Supplier') THEN
2632 -23453
2633 ELSE
2634 TO_NUMBER(substr(origin_code,instr(origin_code,':',1,1)+1))
2635 END,
2636 TO_NUMBER(substr(item_code,instr(item_code,':',1,1)+1)),
2637 -- Effective date is string type in temp table
2638 TO_DATE((select start_date from msc_int_src_recommend_detail where rownum=1), 'YYYY-MM-DD'),
2639 -23453,
2640 assignmentSetOutIdVar,
2641 -23453,
2642 -23453,
2643 -- Supplier: when origin is not supplier, assign to -23453
2644 CASE WHEN (sourcing_type <> 'Supplier') THEN
2645 -23453
2646 ELSE
2647 TO_NUMBER(substr(origin_code,1,instr(origin_code,':',1,1)-1))
2648 END,
2649 -- Supplier Site: when origin is not supplier, assign to -23453
2650 CASE WHEN (sourcing_type <> 'Supplier') THEN
2651 -23453
2652 ELSE
2653 TO_NUMBER(substr(origin_code,instr(origin_code,':',1,1)+1))
2654 END,
2655 -- Customer: when destination is not customer, assign to -23453
2656 CASE WHEN (sourcing_type <> 'Sale') THEN
2657 -23453
2658 ELSE
2659 -- if zone, assign to -23453
2660 (CASE WHEN (INSTR(destination_code,':') = 0) THEN
2661 -23453
2662 ELSE
2663 TO_NUMBER(substr(destination_code,1,instr(destination_code,':',1,1)-1))
2664 END)
2665 END,
2666 -- Customer Site: when destination is not customer, assign to -23453
2667 CASE WHEN (sourcing_type <> 'Sale') THEN
2668 -23453
2669 ELSE
2670 -- if zone, assign to -23453
2671 (CASE WHEN (INSTR(destination_code,':') = 0) THEN
2672 -23453
2673 ELSE
2674 TO_NUMBER(substr(destination_code,instr(destination_code,':',1,1)+1))
2675 END)
2676 END,
2677 -- Zone: when destination is not customer, assign to -23453
2678 CASE WHEN (sourcing_type <> 'Sale') THEN
2679 -23453
2680 ELSE
2681 -- if not zone, assign to -23453
2682 (CASE WHEN (INSTR(destination_code,':') = 0) THEN
2683 TO_NUMBER(destination_code)
2684 ELSE
2685 -23453
2686 END)
2687 END,
2688 transport_mode_code,
2689 sysdate,
2690 -1,
2691 sysdate,
2692 -1
2693 FROM msc_int_src_recommend_detail
2694 WHERE planName = PlanIdVar
2695 GROUP BY planName, origin_code, destination_code,
2696 item_code, sourcing_type, transport_mode_code;
2697 EXCEPTION WHEN others THEN
2698 g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001010' || ' : ' || SQLERRM;
2699 raise;
2700 END;
2701
2702 Status := 'SUCCESS';
2703
2704
2705 COMMIT; -- BUGBUG SHould this be -- checkpoint commit INSTEAD
2706 -- checkpoint commit;
2707
2708 EXCEPTION
2709 WHEN others THEN
2710 Status := g_ErrorCode;
2711 ROLLBACK;
2712 END SET_ASCP_SRC_RECOMMEND_DETAIL;
2713
2714
2715
2716 -- =============================================================
2717 --
2718 -- Helper functions used defined in MSC_WS_COMMON package ( copies here ).
2719 -- =============================================================
2720
2721 -- get plan name from plan Id
2722
2723 FUNCTION GET_PLAN_NAME_BY_PLAN_ID(
2724 Status OUT NOCOPY VARCHAR2,
2725 PlanId IN NUMBER
2726 ) RETURN BOOLEAN AS
2727 l_PlanName VARCHAR2(100);
2728
2729 BEGIN
2730 BEGIN
2731 SELECT COMPILE_DESIGNATOR INTO l_PlanName
2732 FROM MSC_PLANS
2733 WHERE PLAN_ID = PlanId;
2734 EXCEPTION WHEN NO_DATA_FOUND THEN
2735 Status := 'INVALID_PLANID';
2736 RETURN FALSE;
2737 WHEN others THEN
2738 raise;
2739 END;
2740
2741 Status := l_PlanName;
2742 RETURN TRUE;
2743 END GET_PLAN_NAME_BY_PLAN_ID;
2744
2745 -- validate userId
2746 PROCEDURE VALIDATE_USER_RESP( VRETURN OUT NOCOPY VARCHAR2,
2747 USERID IN NUMBER,
2748 RESPID IN NUMBER) AS
2749 V_USER_ID NUMBER;
2750 V_RESPID NUMBER;
2751 V_APPID NUMBER :=0;
2752 BEGIN
2753
2754 BEGIN
2755 SELECT USER_ID INTO V_USER_ID
2756 FROM FND_USER
2757 WHERE USER_ID = USERID;
2758 EXCEPTION WHEN no_data_found THEN
2759 VRETURN := 'INVALID_USERID';
2760 RETURN;
2761 WHEN others THEN
2762 raise;
2763 END;
2764
2765 BEGIN
2766 SELECT RESPONSIBILITY_ID INTO V_RESPID
2767 FROM FND_USER_RESP_GROUPS
2768 WHERE USER_ID = V_USER_ID AND RESPONSIBILITY_ID = RESPID AND
2769 (sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate));
2770 EXCEPTION WHEN no_data_found THEN
2771 VRETURN := 'INVALID_RESP_ID';
2772 RETURN;
2773 WHEN others THEN
2774 raise;
2775 END;
2776
2777 BEGIN
2778 SELECT APPLICATION_ID INTO V_APPID
2779 FROM FND_RESPONSIBILITY
2780 WHERE RESPONSIBILITY_ID = V_RESPID;
2781 EXCEPTION WHEN others THEN
2782 raise;
2783 END;
2784
2785
2786 fnd_global.apps_initialize(USERID, RESPID, V_APPID);
2787 VRETURN :='OK';
2788
2789 END VALIDATE_USER_RESP;
2790
2791 -- no validation done
2792 PROCEDURE PUBLISH_SNO_RESULTS( processId OUT NOCOPY Number,
2793 status OUT NOCOPY Varchar2,
2794 planIdVar IN Number,
2795 assignmentSetOutIdVar IN Number) AS
2796 l_String VARCHAR2(1000);
2797 l_PlanName VARCHAR2(100);
2798 l_valid BOOLEAN;
2799 errbuf varchar2(1000);
2800 retcode varchar2(1000);
2801 l_plan_run_id number := null;
2802 ScenarioNameVar varchar2(100);
2803 g_ErrorCode VARCHAR2(1000);
2804 internal_SNO_Publish_Error exception;
2805 BEGIN
2806 -- init global variables
2807 g_ErrorCode := '';
2808 ScenarioNameVar := '';
2809
2810
2811 -- initialize items for the given PlanId
2812 BEGIN
2813 SET_UP_SYSTEM_ITEMS(l_string, PlanIdVar);
2814 IF (l_String <> 'SUCCESS') THEN
2815 processid := -1;
2816 status := l_String;
2817 raise internal_SNO_Publish_Error; --RETURN;
2818 END IF;
2819 EXCEPTION
2820 WHEN internal_SNO_Publish_Error THEN
2821 g_ErrorCode := 'Internal SNO Publish Error 01' || ' : ' || status;
2822 raise;
2823 WHEN others THEN
2824 g_ErrorCode := 'ERROR_UNEXPECTED_00023' || ' : ' || status || ' : ' || SQLERRM;
2825 raise;
2826 END;
2827
2828
2829 -- call procedure #1
2830 BEGIN
2831 SET_ASCP_PLAN_BUCKETS (l_String, PlanIdVar);
2832 IF (l_String <> 'SUCCESS') THEN
2833 processid := -1;
2834 status := l_String;
2835 raise internal_SNO_Publish_Error; --RETURN;
2836 END IF;
2837 EXCEPTION
2838 WHEN internal_SNO_Publish_Error THEN
2839 g_ErrorCode := 'Internal SNO Publish Error 02' || ' : ' || status;
2840 raise;
2841 WHEN others THEN
2842 g_ErrorCode := 'ERROR_UNEXPECTED_00023' || ' : ' || status || ' : ' || SQLERRM;
2843 raise;
2844 END;
2845
2846 -- call procedure #2
2847 BEGIN
2848 SET_ASCP_DEMANDS (l_String, PlanIdVar);
2849 IF (l_String <> 'SUCCESS') THEN
2850 processid := -1;
2851 status := l_String;
2852 raise internal_SNO_Publish_Error; --RETURN;
2853 END IF;
2854 EXCEPTION
2855 WHEN internal_SNO_Publish_Error THEN
2856 g_ErrorCode := 'Internal SNO Publish Error 03' || ' : ' || status;
2857 raise;
2858 WHEN others THEN
2859 g_ErrorCode := 'ERROR_UNEXPECTED_00024' || ' : ' || status || ' : ' || SQLERRM;
2860 raise;
2861 END;
2862
2863 -- call procedure #3
2864 BEGIN
2865 SET_ASCP_SUPPLIES (l_String, PlanIdVar);
2866 IF (l_String <> 'SUCCESS') THEN
2867 processid := -1;
2868 status := l_String;
2869 raise internal_SNO_Publish_Error; --RETURN;
2870 END IF;
2871 EXCEPTION
2872 WHEN internal_SNO_Publish_Error THEN
2873 g_ErrorCode := 'Internal SNO Publish Error 04' || ' : ' || status;
2874 raise;
2875 WHEN others THEN
2876 g_ErrorCode := 'ERROR_UNEXPECTED_00025' || ' : ' || status || ' : ' || SQLERRM;
2877 raise;
2878 END;
2879
2880 -- call procedure #4
2881 BEGIN
2882 SET_ASCP_SAFETY_STOCKS (l_String, PlanIdVar);
2883 IF (l_String <> 'SUCCESS') THEN
2884 processid := -1;
2885 status := l_String;
2886 raise internal_SNO_Publish_Error; --RETURN;
2887 END IF;
2888 EXCEPTION
2889 WHEN internal_SNO_Publish_Error THEN
2890 g_ErrorCode := 'Internal SNO Publish Error 05' || ' : ' || status;
2891 raise;
2892 WHEN others THEN
2893 g_ErrorCode := 'ERROR_UNEXPECTED_00026' || ' : ' || status || ' : ' || SQLERRM;
2894 raise;
2895 END;
2896
2897 -- call procedure #5
2898
2899 BEGIN
2900 SET_ASCP_ALERTS (l_String, PlanIdVar);
2901 IF (l_String <> 'SUCCESS') THEN
2902 processid := -1;
2903 status := l_String;
2904 raise internal_SNO_Publish_Error; --RETURN;
2905 END IF;
2906 EXCEPTION
2907 WHEN internal_SNO_Publish_Error THEN
2908 g_ErrorCode := 'Internal SNO Publish Error 06' || ' : ' || status;
2909 raise;
2910 WHEN others THEN
2911 g_ErrorCode := 'ERROR_UNEXPECTED_00027' || ' : ' || status || ' : ' || SQLERRM;
2912 raise;
2913 END;
2914
2915 -- call procedure #6
2916 BEGIN
2917 SET_ASCP_DEPARTMENT_RESOURCES (l_String, PlanIdVar);
2918 IF (l_String <> 'SUCCESS') THEN
2919 processid := -1;
2920 status := l_String;
2921 raise internal_SNO_Publish_Error; --RETURN;
2922 END IF;
2923 EXCEPTION
2924 WHEN internal_SNO_Publish_Error THEN
2925 g_ErrorCode := 'Internal SNO Publish Error 07' || ' : ' || status;
2926 raise;
2927 WHEN others THEN
2928 g_ErrorCode := 'ERROR_UNEXPECTED_00028' || ' : ' || status || ' : ' || SQLERRM;
2929 raise;
2930 END;
2931
2932 -- new temp tables upload
2933 BEGIN
2934 SET_APCC_FACILITY_COST (l_String, PlanIdVar);
2935 IF (l_String <> 'SUCCESS') THEN
2936 processid := -1;
2937 status := l_String;
2938 raise internal_SNO_Publish_Error; --RETURN;
2939 END IF;
2940 EXCEPTION
2941 WHEN internal_SNO_Publish_Error THEN
2942 g_ErrorCode := 'Internal SNO Publish Error 12' || ' : ' || status;
2943 raise;
2944 WHEN others THEN
2945 g_ErrorCode := 'ERROR_UNEXPECTED_00033' || ' : ' || status || ' : ' || SQLERRM;
2946 raise;
2947 END;
2948
2949 -- call procedure #7
2950 BEGIN
2951 SET_ASCP_RES_SUMMARY (l_String, PlanIdVar, ScenarioNameVar );
2952 IF (l_String <> 'SUCCESS') THEN
2953 processid := -1;
2954 status := l_String;
2955 raise internal_SNO_Publish_Error; --RETURN;
2956 END IF;
2957 IF ( ScenarioNameVar = '' ) THEN
2958 processid := -1;
2959 status := 'Invalid or empty Scenario Name';
2960 raise internal_SNO_Publish_Error;
2961 RETURN;
2962 END IF;
2963 EXCEPTION
2964 WHEN internal_SNO_Publish_Error THEN
2965 g_ErrorCode := 'Internal SNO Publish Error 08' || ' : ' || status;
2966 raise;
2967 WHEN others THEN
2968 g_ErrorCode := 'ERROR_UNEXPECTED_00029' || ' : ' || status || ' : ' || SQLERRM;
2969 raise;
2970 END;
2971
2972 -- call procedure #8
2973 BEGIN
2974 SET_ASCP_BIS_INV_DETAIL (l_String, PlanIdVar);
2975 IF (l_String <> 'SUCCESS') THEN
2976 processid := -1;
2977 status := l_String;
2978 raise internal_SNO_Publish_Error; --RETURN;
2979 END IF;
2980 EXCEPTION
2981 WHEN internal_SNO_Publish_Error THEN
2982 g_ErrorCode := 'Internal SNO Publish Error 09' || ' : ' || status;
2983 raise;
2984 WHEN others THEN
2985 g_ErrorCode := 'ERROR_UNEXPECTED_00030' || ' : ' || status || ' : ' || SQLERRM;
2986 raise;
2987 END;
2988
2989 -- call procedure #9
2990 BEGIN
2991 SET_ASCP_SRC_RECOMMEND_DETAIL (l_String, PlanIdVar, assignmentSetOutIdVar );
2992 IF (l_String <> 'SUCCESS') THEN
2993 processid := -1;
2994 status := l_String;
2995 raise internal_SNO_Publish_Error; --RETURN;
2996 END IF;
2997 EXCEPTION
2998 WHEN internal_SNO_Publish_Error THEN
2999 g_ErrorCode := 'Internal SNO Publish Error 10' || ' : ' || status;
3000 raise;
3001 WHEN others THEN
3002 g_ErrorCode := 'ERROR_UNEXPECTED_00031' || ' : ' || status || ' : ' || SQLERRM;
3003 raise;
3004 END;
3005
3006
3007 BEGIN
3008 -- 5th parameter p_archive_flag is invoked with default value = -1, which means p_archive_flag - yes;( TBD )
3009 -- values -1 ( default ) or 1, mean archive - Yes, otherwise No - based on the procedure definition.
3010
3011 MSC_PHUB_PKG.populate_sno_details(errbuf, retcode, PlanIdVar, l_plan_run_id, -1, ScenarioNameVar);
3012
3013
3014 IF (retcode <> null AND retcode <> '0') THEN
3015 processid := -1;
3016 status := errbuf;
3017 raise internal_SNO_Publish_Error; --RETURN;
3018 END IF;
3019
3020 EXCEPTION
3021 WHEN internal_SNO_Publish_Error THEN
3022 g_ErrorCode := 'Internal SNO Publish Error 11' || ' : ' || status;
3023 raise;
3024 WHEN others THEN
3025 g_ErrorCode := 'ERROR_UNEXPECTED_00032' || ' : ' || status || ' : ' || SQLERRM;
3026 raise;
3027 END;
3028
3029 COMMIT;
3030 processid := 1;
3031 Status := '';
3032
3033 EXCEPTION
3034 WHEN others THEN
3035 Status := g_ErrorCode;
3036 processid := -1;
3037 log_message( Status );
3038 ROLLBACK;
3039 END PUBLISH_SNO_RESULTS;
3040
3041 PROCEDURE PUBLISH_SNO_RESULTS_WITH_VAL( processId OUT NOCOPY Number,
3042 status OUT NOCOPY Varchar2,
3043 userId IN Number,
3044 responsibilityId IN Number,
3045 planIdVar IN Number,
3046 assignmentSetOutIdVar IN Number) AS
3047 l_String VARCHAR2(1000);
3048 l_processId NUMBER;
3049 l_PlanName VARCHAR2(100);
3050 l_valid BOOLEAN;
3051
3052
3053 g_ErrorCode VARCHAR2(1000);
3054 BEGIN
3055 -- init global variables
3056 g_ErrorCode := '';
3057
3058 -- validate and initialize apps
3059 l_processId := 1;
3060 VALIDATE_FOR_PUBLISH_SNO_RES( l_processId, l_String, userId, responsibilityId, planIdVar );
3061 IF ( l_processId='-1' OR l_String <> '' ) THEN
3062 processid := -1;
3063 status := l_String;
3064 log_message( status );
3065 RETURN;
3066 END IF;
3067 PUBLISH_SNO_RESULTS( processId, status, planIdVar, assignmentSetOutIdVar);
3068
3069 END PUBLISH_SNO_RESULTS_WITH_VAL;
3070
3071 PROCEDURE VALIDATE_FOR_PUBLISH_SNO_RES( processId OUT NOCOPY Number,
3072 status OUT NOCOPY Varchar2,
3073 userId IN Number,
3074 responsibilityId IN Number,
3075 planIdVar IN Number) AS
3076 l_String VARCHAR2(1000);
3077 l_valid BOOLEAN;
3078
3079
3080 g_ErrorCode VARCHAR2(1000);
3081 BEGIN
3082 -- init global variables
3083 g_ErrorCode := '';
3084
3085 -- validate and initialize apps
3086 BEGIN
3087 VALIDATE_USER_RESP(l_String, UserId, ResponsibilityId);
3088 IF (l_String <> 'OK') THEN
3089 processid := -1;
3090 status := l_String;
3091 RETURN;
3092 END IF;
3093 EXCEPTION WHEN others THEN
3094 g_ErrorCode := 'ERROR_UNEXPECTED_00021' || ' : ' || SQLERRM;
3095 raise;
3096 END;
3097
3098 -- check plan id
3099 BEGIN
3100 l_valid := GET_PLAN_NAME_BY_PLAN_ID(l_String, PlanIdVar);
3101 IF ( (l_String = 'INVALID_PLANID') OR l_valid=false ) THEN
3102 processid := -1;
3103 status := l_String;
3104 RETURN;
3105 END IF;
3106 EXCEPTION WHEN others THEN
3107 g_ErrorCode := 'ERROR_UNEXPECTED_00022' || ' : ' || SQLERRM;
3108 raise;
3109 END;
3110
3111 processid := 1;
3112 Status := '';
3113
3114 EXCEPTION
3115 WHEN others THEN
3116 Status := g_ErrorCode || ' : ' || SQLERRM;
3117 processid := -1;
3118 log_message( Status );
3119 ROLLBACK;
3120 END VALIDATE_FOR_PUBLISH_SNO_RES;
3121
3122 -- SOP Publish - Cost Modeling ( no validation done )
3123 PROCEDURE PUBLISH_SNO_RESULTS_SOP( processId OUT NOCOPY Number,
3124 status OUT NOCOPY Varchar2,
3125 planIdVar IN Number ) AS
3126 l_String VARCHAR2(1000);
3127 l_PlanName VARCHAR2(100);
3128 g_ErrorCode VARCHAR2(1000);
3129 internal_SNO_Publish_Error exception;
3130 BEGIN
3131 -- init global variables
3132 g_ErrorCode := '';
3133
3134
3135 -- initialize items for the given PlanId -- OUT for SOP -- SET_UP_SYSTEM_ITEMS
3136
3137 -- call procedure #1 -- IN for SOP
3138 BEGIN
3139 SET_ASCP_PLAN_BUCKETS (l_String, PlanIdVar);
3140 IF (l_String <> 'SUCCESS') THEN
3141 processid := -1;
3142 status := l_String;
3143 raise internal_SNO_Publish_Error; --RETURN;
3144 END IF;
3145 EXCEPTION
3146 WHEN internal_SNO_Publish_Error THEN
3147 g_ErrorCode := 'Internal SNO Publish Error 02' || ' : ' || status;
3148 raise;
3149 WHEN others THEN
3150 g_ErrorCode := 'ERROR_UNEXPECTED_00023' || ' : ' || status || ' : ' || SQLERRM;
3151 raise;
3152 END;
3153
3154 -- call procedure #2 -- OUT for SOP - we do not need data from msc_demand for the SOP case
3155 --BEGIN
3156 --SET_ASCP_DEMANDS (l_String, PlanIdVar);
3157 --IF (l_String <> 'SUCCESS') THEN
3158 -- processid := -1;
3159 -- status := l_String;
3160 -- raise internal_SNO_Publish_Error; --RETURN;
3161 -- END IF;
3162 -- EXCEPTION
3163 -- WHEN internal_SNO_Publish_Error THEN
3164 -- g_ErrorCode := 'Internal SNO Publish Error 03' || ' : ' || status;
3165 -- raise;
3166 -- WHEN others THEN
3167 -- g_ErrorCode := 'ERROR_UNEXPECTED_00024' || ' : ' || status || ' : ' || SQLERRM;
3168 -- raise;
3169 --END;
3170
3171 -- call procedure #3 -- OUT for SOP - we do not need data from msc_supplies for the SOP case
3172 --BEGIN
3173 --SET_ASCP_SUPPLIES (l_String, PlanIdVar);
3174 --IF (l_String <> 'SUCCESS') THEN
3175 -- processid := -1;
3176 -- status := l_String;
3177 -- raise internal_SNO_Publish_Error; --RETURN;
3178 -- END IF;
3179 -- EXCEPTION
3180 -- WHEN internal_SNO_Publish_Error THEN
3181 -- g_ErrorCode := 'Internal SNO Publish Error 04' || ' : ' || status;
3182 -- raise;
3183 -- WHEN others THEN
3184 -- g_ErrorCode := 'ERROR_UNEXPECTED_00025' || ' : ' || status || ' : ' || SQLERRM;
3185 -- raise;
3186 --END;
3187
3188 -- call procedure #4 -- OUT for SOP -- SET_ASCP_SAFETY_STOCKS
3189
3190 -- call procedure #5 -- OUT for SOP -- SET_ASCP_ALERTS
3191
3192 -- call procedure #6 -- OUT for SOP -- SET_ASCP_DEPARTMENT_RESOURCES
3193
3194 -- call procedure #7 -- OUT for SOP SET_ASCP_RES_SUMMARY
3195
3196
3197
3198 -- call SET_ASCP_BIS_INV_DETAIL ( procedure #8 ) -- IN for SOP - however ...
3199
3200 -- ... first clear the records from MSC_INT_ITEM_TRAVEL_DISTANCE table for the given PlanIdVar if any
3201 -- ... 'cause for SOP there should not be any in there
3202 BEGIN
3203 DELETE FROM MSC_INT_ITEM_TRAVEL_DISTANCE WHERE PLAN_ID=PlanIdVar;
3204 EXCEPTION WHEN others THEN
3205 NULL; -- do nothing
3206 END;
3207
3208 -- ... then clear the records from MSC_INT_ASCP_INVENTORY table for the given PlanIdVar if any
3209 BEGIN
3210 DELETE FROM MSC_INT_ASCP_INVENTORY WHERE PLAN_ID=PlanIdVar;
3211 EXCEPTION WHEN others THEN
3212 NULL; -- do nothing
3213 END;
3214
3215 -- ... then call SET_ASCP_BIS_INV_DETAIL procedure -- IN for SOP
3216 BEGIN
3217 SET_ASCP_BIS_INV_DETAIL (l_String, PlanIdVar);
3218 IF (l_String <> 'SUCCESS') THEN
3219 processid := -1;
3220 status := l_String;
3221 raise internal_SNO_Publish_Error; --RETURN;
3222 END IF;
3223 EXCEPTION
3224 WHEN internal_SNO_Publish_Error THEN
3225 g_ErrorCode := 'Internal SNO Publish Error 09' || ' : ' || status;
3226 raise;
3227 WHEN others THEN
3228 g_ErrorCode := 'ERROR_UNEXPECTED_00030' || ' : ' || status || ' : ' || SQLERRM;
3229 raise;
3230 END;
3231
3232 -- call procedure #9 -- SET_ASCP_SRC_RECOMMEND_DETAIL OUT for SOP
3233
3234 -- OUT for SOP -- MSC_PHUB_PKG.populate_sno_details
3235
3236 COMMIT;
3237 processid := 1;
3238 Status := '';
3239
3240 EXCEPTION
3241 WHEN others THEN
3242 Status := g_ErrorCode;
3243 processid := -1;
3244 log_message( Status );
3245 ROLLBACK;
3246 END PUBLISH_SNO_RESULTS_SOP;
3247 END MSC_WS_SNO_PUBLISH;