1 PACKAGE BODY MSD_SALES_OPERATION_PLAN AS
2 /* $Header: msdsoplb.pls 120.18 2006/05/25 09:28:58 brampall noship $ */
3
4 v_retcode varchar2(5) := '0';
5 v_demand_plan_id number;
6
7 procedure log_debug( pBUFF in varchar2)
8 is
9 begin
10
11 if C_MSC_DEBUG = 'Y' then
12 null;
13 --fnd_file.put_line( fnd_file.log, pBUFF);
14 else
15 null;
16 --dbms_output.put_line( pBUFF);
17 end if;
18
19 end log_debug;
20
21 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
22 IS
23 BEGIN
24 null;
25 -- Bug 4395606. Cannot call fnd file from DPE.
26 --FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
27
28 END LOG_MESSAGE;
29
30
31 function calculate_cu_and_lt ( p_cu_or_lt IN NUMBER,
32 p_instance_id IN NUMBER,
33 p_supply_plan_id IN NUMBER,
34 p_assembly_pk IN VARCHAR2,
35 p_component_pk IN VARCHAR2,
36 p_res_comp IN VARCHAR2,
37 p_effectivity_date DATE,
38 p_disable_date DATE)
39 return number
40 is
41
42 l_numerator NUMBER :=0;
43 l_denominator NUMBER :=0;
44
45 l_return_value NUMBER :=0;
46
47 begin
48
49 LOG_MESSAGE('Entering in the function to calculate CAPACITY_USAGE_RATIO and LEAD_TIME');
50
51 --Calculations for Critical Components
52 IF p_res_comp = 'C' THEN
53
54
55 -- Calculating Capacity Usage Ratios for Critical Components
56 IF p_cu_or_lt = C_CU THEN
57
58 LOG_MESSAGE('Calculating the CAPACITY_USAGE_RATIO for Critical Component');
59 LOG_MESSAGE('The SR_ASSEMBLY_PK is : '||p_assembly_pk);
60 LOG_MESSAGE('The SR_COMPONENT_PK is : '||p_component_pk);
61
62 select sum(cmp_mfp.allocated_quantity)
63 --,sum(ass_mfp.allocated_quantity)
64 INTO l_numerator
65 --,l_denominator
66 from
67 msc_plan_organizations ass_mpo,
68 msc_system_items ass_msi,
69 msc_demands md,
70 msc_supplies ass_ms,
71 msc_full_pegging ass_mfp,
72 msc_full_pegging cmp_mfp,
73 msc_supplies cmp_ms,
74 msc_system_items cmp_msi,
75 msc_plan_organizations cmp_mpo
76 where cmp_mpo.plan_id = p_supply_plan_id
77 /* mpo_plan_organizations - assembly and msc_system_items - assembly */
78 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
79 and ass_msi.plan_id = ass_mpo.plan_id
80 and ass_msi.organization_id = ass_mpo.organization_id
81 /* msc_system_items - assembly and msc_supplies - assembly */
82 and ass_ms.inventory_item_id = ass_msi.inventory_item_id
83 and ass_ms.plan_id = ass_msi.plan_id
84 and ass_ms.sr_instance_id = ass_msi.sr_instance_id
85 and ass_ms.organization_id = ass_msi.organization_id
86 /* msc_demands - assembly and msc_full_pegging - assembly */
87 and md.demand_id = ass_mfp.demand_id
88 and md.plan_id = ass_mfp.plan_id
89 and md.sr_instance_id = ass_mfp.sr_instance_id
90 and md.organization_id = ass_mfp.organization_id
91 and md.origination_type in (6,8,29,30)
92 /* msc_supplies - assembly and msc_full_pegging - assembly */
93 and ass_ms.transaction_id = ass_mfp.transaction_id
94 and ass_ms.plan_id = ass_mfp.plan_id
95 and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
96 and ass_ms.organization_id = ass_mfp.organization_id
97 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
98 and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
99 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
100 and cmp_mfp.plan_id = ass_mfp.plan_id
101 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
102 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
103 /* msc_full_pegging - components and msc_supplies - components */
104 and cmp_mfp.transaction_id = cmp_ms.transaction_id
105 and cmp_mfp.organization_id = cmp_ms.organization_id
106 and cmp_mfp.sr_instance_id = cmp_ms.sr_instance_id
107 and cmp_mfp.plan_id = cmp_ms.plan_id
108 /* msc_supplies - components and msc_system_items - components */
109 and cmp_ms.inventory_item_id = cmp_msi.inventory_item_id
110 and cmp_ms.plan_id = cmp_msi.plan_id
111 and cmp_ms.sr_instance_id = cmp_msi.sr_instance_id
112 and cmp_ms.organization_id = cmp_msi.organization_id
113 /* msc_system_items - components and mpo_plan_organizations - components */
114 and cmp_msi.organization_id = cmp_mpo.organization_id
115 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
116 and cmp_msi.plan_id = cmp_mpo.plan_id
117 /* For given PLAN,INSTANCE,ASSEMBLY,COMPONENT,EFF and DISABLE DATE */
118 and cmp_mpo.plan_id = p_supply_plan_id
119 and ass_msi.sr_instance_id = p_instance_id
120 and ass_msi.sr_inventory_item_id = p_assembly_pk
121 and cmp_msi.sr_inventory_item_id = p_component_pk
122 /* Is this really required, as we know the ASSEMBLY and COMPONENT? */
123 and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id
124 and md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
125
126 /* Splitting Nmr and Dmr because a Critical Component can be used at numerous places
127 in BOM, hence can't aggregate the Assembly's allocated quantity
128 in above query which may cause double counting in Dmr */
129
130 select /*+ ORDERED */ sum(ass_mfp.allocated_quantity)
131 INTO l_denominator
132 from msc_plan_organizations mpo,
133 msc_demands ass_md,
134 msc_system_items ass_msi,
135 msc_full_pegging ass_mfp,
136 msc_supplies ass_ms
137 where ass_msi.plan_id = mpo.plan_id
138 and ass_msi.sr_instance_id = mpo.sr_instance_id
139 and ass_msi.organization_id = mpo.organization_id
140 /* msc_system_items - assembly and msd_demands - assembly */
141 and ass_msi.plan_id = ass_md.plan_id
142 and ass_msi.sr_instance_id = ass_md.sr_instance_id
143 and ass_msi.organization_id = ass_md.organization_id
144 and ass_msi.inventory_item_id = ass_md.inventory_item_id
145 and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
146 /*msc_demands - assembly and msc_full_pegging - assembly */
147 and ass_md.demand_id = ass_mfp.demand_id
148 and ass_md.plan_id = ass_mfp.plan_id
149 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
150 and ass_md.organization_id = ass_mfp.organization_id
151 and ass_md.origination_type in (6,8,29,30)
152 /* msc_full_pegging - assembly and msc_supplies - assembly */
153 and ass_ms.transaction_id = ass_mfp.transaction_id
154 and ass_ms.plan_id = ass_mfp.plan_id
155 and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
156 and ass_ms.organization_id = ass_mfp.organization_id
157 and ass_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
158 /* For given PLAN,INSTANCE,ASSEMBLY,EFF and DISABLE DATE */
159 and mpo.plan_id = p_supply_plan_id
160 and ass_msi.sr_instance_id = p_instance_id
161 and ass_msi.sr_inventory_item_id = p_assembly_pk
162 and ass_md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
163
164 LOG_MESSAGE('The value for numerator is:'||l_numerator);
165 LOG_MESSAGE('The value for denominator is:'||l_denominator);
166
167
168 IF l_denominator <> 0 THEN
169 l_return_value := l_numerator/l_denominator;
170 LOG_MESSAGE('The value of return value is:'||l_return_value);
171
172 ELSE
173 l_return_value := 0;
174 LOG_MESSAGE('The return value is zero because of zero denominator');
175
176 END IF;
177
178 return l_return_value;
179
180 -- Calculating Lead Times for Critical Components
181 ELSE
182
183 LOG_MESSAGE('Calculating the LEAD_TIME for Critical Component');
184 LOG_MESSAGE('The SR_ASSEMBLY_PK is :'||p_assembly_pk);
185 LOG_MESSAGE('The SR_COMPONENT_PK is :'||p_component_pk);
186
187 select /*+ ORDERED */ sum(cmp_mfp.allocated_quantity*(greatest((ass_ms.new_schedule_date - cmp_ms.new_schedule_date),0)))
188 -- ,sum(cmp_mfp.allocated_quantity)
189 INTO l_numerator
190 -- ,l_denominator
191
192 from
193 msc_plan_organizations ass_mpo,
194 msc_system_items ass_msi,
195 msc_supplies ass_ms,
196 msc_full_pegging ass_mfp,
197 msc_full_pegging cmp_mfp,
198 msc_supplies cmp_ms,
199 msc_system_items cmp_msi,
200 msc_plan_organizations cmp_mpo
201 where cmp_mpo.plan_id = p_supply_plan_id
202 /* mpo_plan_organizations - assembly and msc_system_items - assembly */
203 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
204 and ass_msi.plan_id = ass_mpo.plan_id
205 and ass_msi.organization_id = ass_mpo.organization_id
206 /* msc_system_items - assembly and msc_supplies - assembly */
207 and ass_ms.inventory_item_id = ass_msi.inventory_item_id
208 and ass_ms.plan_id = ass_msi.plan_id
209 and ass_ms.sr_instance_id = ass_msi.sr_instance_id
210 and ass_ms.organization_id = ass_msi.organization_id
211 /* msc_supplies - assembly and msc_full_pegging - assembly */
212 and ass_ms.transaction_id = ass_mfp.transaction_id
213 and ass_ms.plan_id = ass_mfp.plan_id
214 and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
215 and ass_ms.organization_id = ass_mfp.organization_id
216 and ass_ms.order_type not in (18,3 ) -- Exclude On Hand Supplies and Discrete Jobs Bug 4878648
217 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
218 and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
219 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
220 and cmp_mfp.plan_id = ass_mfp.plan_id
221 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
222 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
223 /* msc_full_pegging - components and msc_supplies - components */
224 and cmp_mfp.transaction_id = cmp_ms.transaction_id
225 and cmp_mfp.organization_id = cmp_ms.organization_id
226 and cmp_mfp.sr_instance_id = cmp_ms.sr_instance_id
227 and cmp_mfp.plan_id = cmp_ms.plan_id
228 /* msc_supplies - components and msc_system_items - components */
229 and cmp_ms.inventory_item_id = cmp_msi.inventory_item_id
230 and cmp_ms.plan_id = cmp_msi.plan_id
231 and cmp_ms.sr_instance_id = cmp_msi.sr_instance_id
232 and cmp_ms.organization_id = cmp_msi.organization_id
233 /* msc_system_items - components and mpo_plan_organizations - components */
234 and cmp_msi.organization_id = cmp_mpo.organization_id
235 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
236 and cmp_msi.plan_id = cmp_mpo.plan_id
237 /* For given PLAN,INSTANCE,ASSEMBLY,COMPONENT,EFF and DISABLE DATE */
238 and cmp_mpo.plan_id = p_supply_plan_id
239 and ass_msi.sr_instance_id = p_instance_id
240 and ass_msi.sr_inventory_item_id = p_assembly_pk
241 and cmp_msi.sr_inventory_item_id = p_component_pk
242 --and ass_mfp.demand_date between p_effectivity_date and p_disable_date
243 /* Is this really required, as we know the ASSEMBLY and COMPONENT? */
244 and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id;
245
246 /* Splitting Nmr and Dmr because a Critical Component can be used at numerous places
247 in BOM, hence can't aggregate the Assembly's allocated quantity
248 in above query which may cause double counting in Dmr */
249
250 select /*+ ORDERED */ sum(cmp_mfp.allocated_quantity)
251 INTO l_denominator
252 from
253 msc_plan_organizations ass_mpo,
254 msc_system_items ass_msi,
255 msc_supplies ass_ms,
256 msc_full_pegging ass_mfp,
257 msc_full_pegging cmp_mfp,
258 msc_supplies cmp_ms,
259 msc_system_items cmp_msi,
260 msc_plan_organizations cmp_mpo
261 where cmp_mpo.plan_id = p_supply_plan_id
262 /* mpo_plan_organizations - assembly and msc_system_items - assembly */
263 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
264 and ass_msi.plan_id = ass_mpo.plan_id
265 and ass_msi.organization_id = ass_mpo.organization_id
266 /* msc_system_items - assembly and msc_supplies - assembly */
267 and ass_ms.inventory_item_id = ass_msi.inventory_item_id
268 and ass_ms.plan_id = ass_msi.plan_id
269 and ass_ms.sr_instance_id = ass_msi.sr_instance_id
270 and ass_ms.organization_id = ass_msi.organization_id
271 /* msc_supplies - assembly and msc_full_pegging - assembly */
272 and ass_ms.transaction_id = ass_mfp.transaction_id
273 and ass_ms.plan_id = ass_mfp.plan_id
274 and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
275 and ass_ms.organization_id = ass_mfp.organization_id
276 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
277 and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
278 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
279 and cmp_mfp.plan_id = ass_mfp.plan_id
280 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
281 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
282 /* msc_full_pegging - components and msc_supplies - components */
283 and cmp_mfp.transaction_id = cmp_ms.transaction_id
284 and cmp_mfp.organization_id = cmp_ms.organization_id
285 and cmp_mfp.sr_instance_id = cmp_ms.sr_instance_id
286 and cmp_mfp.plan_id = cmp_ms.plan_id
287 /* msc_supplies - components and msc_system_items - components */
288 and cmp_ms.inventory_item_id = cmp_msi.inventory_item_id
289 and cmp_ms.plan_id = cmp_msi.plan_id
290 and cmp_ms.sr_instance_id = cmp_msi.sr_instance_id
291 and cmp_ms.organization_id = cmp_msi.organization_id
292 /* msc_system_items - components and mpo_plan_organizations - components */
293 and cmp_msi.organization_id = cmp_mpo.organization_id
294 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
295 and cmp_msi.plan_id = cmp_mpo.plan_id
296 /* For given PLAN,INSTANCE,ASSEMBLY,COMPONENT,EFF and DISABLE DATE */
297 and cmp_mpo.plan_id = p_supply_plan_id
298 and ass_msi.sr_instance_id = p_instance_id
299 and ass_msi.sr_inventory_item_id = p_assembly_pk
300 and cmp_msi.sr_inventory_item_id = p_component_pk
301 --and ass_mfp.demand_date between p_effectivity_date and p_disable_date
302 /* Is this really required, as we know the ASSEMBLY and COMPONENT? */
303 and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id;
304
305 LOG_MESSAGE('The value for numerator is:'||l_numerator);
306 LOG_MESSAGE('The value for denominator is:'||l_denominator);
307
308 IF l_denominator <> 0 THEN
309 l_return_value := l_numerator/l_denominator;
310 LOG_MESSAGE('The value of return value is:'||l_return_value);
311 ELSE
312 l_return_value := 0;
313 LOG_MESSAGE('The return value is zero because of zero denominator');
314 END IF;
315
316 return l_return_value;
317
318
319 END IF; --IF p_cu_or_lt = C_CU THEN
320
321
322 --Calculations for Resources
323 ELSE
324
325 -- Calculating Capacity Usage Ratios for Resources
326 IF p_cu_or_lt = C_CU THEN
327
328 LOG_MESSAGE('Calculating the CAPACITY_USAGE_RATIO for Resource');
329 LOG_MESSAGE('The SR_ASSEMBLY_PK is :'||p_assembly_pk);
330 LOG_MESSAGE('The SR_RESOURCE_PK is :'||p_component_pk);
331
332 /*
333 Formulae = Sigma[ (Pegged Qty for Ass/Comp based on where resource instance is applied/Total Qty for Ass/Comp based on where resource instance is applied)* Resource Hours
334 ------------------------------------------------------------------------------------------------------------------------------------------------------------------
335 Sigma[ Pegged Qty for Assmb excluding On Hand Suppplies
336 */
337
338 select sum((mfp2.allocated_quantity/ms2.new_order_quantity)*mrr.resource_hours)
339 --,sum(mfp2.allocated_quantity)
340 INTO l_numerator
341 --,l_denominator
342 from
343 msc_plan_organizations mpo1,
344 msc_system_items msi,
345 msc_demands md,
346 msc_full_pegging mfp1,
347 msc_full_pegging mfp2,
348 msc_supplies ms2,
349 msc_resource_requirements mrr,
350 msc_department_resources mdr,
351 msc_plan_organizations mpo2
352 where
353 mpo1.plan_id = p_supply_plan_id
354 and msi.sr_inventory_item_id = p_assembly_pk
355 and msi.plan_id = mpo1.plan_id
356 and msi.organization_id = mpo1.organization_id
357 and msi.sr_instance_id = mpo1.sr_instance_id
358 /* msc_system_items and msc_demands */
359 and msi.inventory_item_id = md.inventory_item_id
360 and msi.plan_id = md.plan_id
361 and msi.organization_id = md.organization_id
362 and msi.sr_instance_id = md.sr_instance_id
363 /*msc_demands and msc_full_pegging1 */
364 and md.demand_id = mfp1.demand_id
365 and md.plan_id = mfp1.plan_id
366 and md.sr_instance_id = mfp1.sr_instance_id
367 and md.organization_id = mfp1.organization_id
368 and md.origination_type in (6,8,29,30)
369 /*msc_full_pegging1 and msc_full_pegging2 */
370 and mfp1.pegging_id = mfp2.end_pegging_id
371 and mfp1.plan_id = mfp2.plan_id
372 and mfp1.sr_instance_id = mfp2.sr_instance_id -- (No organization id join between mfp1 and mfp2 because single demand can span across various orgs.
373 /* msc_full_pegging2 and msc_resource_requirements */
374 and mfp2.transaction_id = mrr.supply_id
375 and mfp2.plan_id = mrr.plan_id
376 and mfp2.sr_instance_id = mrr.sr_instance_id
377 and mfp2.organization_id = mrr.organization_id
378 /* msc_full_pegging2 and msc_supplies */
379 and ms2.transaction_id = mfp2.transaction_id
380 and ms2.plan_id = mfp2.plan_id
381 and ms2.sr_instance_id = mfp2.sr_instance_id
382 and ms2.organization_id = mfp2.organization_id
383 /* msc_resource_requirements and msc_department_resources */
384 and mrr.resource_id = mdr.resource_id
385 and mrr.plan_id = mdr.plan_id
386 and mrr.sr_instance_id = mdr.sr_instance_id
387 and mrr.organization_id = mdr.organization_id
388 /* msc_department_resources and msc_plan_organizations */
389 and decode(mdr.resource_id,-1,mdr.department_code,mdr.resource_code)= p_component_pk
390 and mdr.plan_id = p_supply_plan_id
391 and mdr.sr_instance_id = p_instance_id
392 and mdr.organization_id = mpo2.organization_id
393 and mpo2.plan_id = p_supply_plan_id
394 and mrr.parent_id = 2 -- Records Inserted by HLS as Net Resource Requirements
395 and md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
396
397 /* Splitting Nmr and Dmr because a Resource can be used at numerous places
398 in routing, hence can't aggregate the Assembly's allocated quantity
399 in above query which may cause double counting in Dmr */
400
401 select /*+ ORDERED */ sum(ass_mfp.allocated_quantity)
402 INTO l_denominator
403 from msc_plan_organizations mpo,
404 msc_demands ass_md,
405 msc_system_items ass_msi,
406 msc_full_pegging ass_mfp,
407 msc_supplies ass_ms
408 where ass_msi.plan_id = mpo.plan_id
409 and ass_msi.sr_instance_id = mpo.sr_instance_id
410 and ass_msi.organization_id = mpo.organization_id
411 /* msc_system_items - assembly and msd_demands - assembly */
412 and ass_msi.plan_id = ass_md.plan_id
413 and ass_msi.sr_instance_id = ass_md.sr_instance_id
414 and ass_msi.organization_id = ass_md.organization_id
415 and ass_msi.inventory_item_id = ass_md.inventory_item_id
416 and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
417 /*msc_demands - assembly and msc_full_pegging - assembly */
418 and ass_md.demand_id = ass_mfp.demand_id
419 and ass_md.plan_id = ass_mfp.plan_id
420 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
421 and ass_md.organization_id = ass_mfp.organization_id
422 and ass_md.origination_type in (6,8,29,30)
423 /* msc_full_pegging - assembly and msc_supplies - assembly */
424 and ass_ms.transaction_id = ass_mfp.transaction_id
425 and ass_ms.plan_id = ass_mfp.plan_id
426 and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
427 and ass_ms.organization_id = ass_mfp.organization_id
428 and ass_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
429 /* For given PLAN,INSTANCE,ASSEMBLY,EFF and DISABLE DATE */
430 and mpo.plan_id = p_supply_plan_id
431 and ass_msi.sr_instance_id = p_instance_id
432 and ass_msi.sr_inventory_item_id = p_assembly_pk
433 and ass_md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
434
435 LOG_MESSAGE('The value for numerator is:'||l_numerator);
436 LOG_MESSAGE('The value for denominator is:'||l_denominator);
437
438 IF l_denominator <> 0 THEN
439 l_return_value := l_numerator/l_denominator;
440 LOG_MESSAGE('The value of return value is:'||l_return_value);
441 ELSE
442 l_return_value := 0;
443 LOG_MESSAGE('The return value is zero because of zero denominator');
444 END IF;
445
446 return l_return_value;
447
448 -- Calculating Lead Times for Resources
449 ELSE
450
451 LOG_MESSAGE('Calculating the LEAD_TIME for Resource');
452 LOG_MESSAGE('The SR_ASSEMBLY_PK is :'||p_assembly_pk);
453 LOG_MESSAGE('The SR_RESOURCE_PK is :'||p_component_pk);
454
455 select sum(mfp2.allocated_quantity*(greatest((ms.new_schedule_date-mrr.end_date),0)))
456 --sum(mfp2.allocated_quantity),
457 INTO l_numerator
458 --l_denominator,
459 from
460 msc_plan_organizations mpo1,
461 msc_system_items msi,
462 msc_supplies ms,
463 msc_full_pegging mfp1,
464 msc_full_pegging mfp2,
465 msc_resource_requirements mrr,
466 msc_department_resources mdr,
467 msc_plan_organizations mpo2
468 where
469 mpo1.plan_id = p_supply_plan_id
470 and msi.sr_inventory_item_id = p_assembly_pk
471 and msi.plan_id = mpo1.plan_id
472 and msi.organization_id = mpo1.organization_id
473 and msi.sr_instance_id = mpo1.sr_instance_id
474 /* msc_system_items - assembly and msc_supplies - assembly */
475 and msi.inventory_item_id = ms.inventory_item_id
476 and msi.plan_id = ms.plan_id
477 and msi.organization_id = ms.organization_id
478 and msi.sr_instance_id = ms.sr_instance_id
479 /*msc_supplies - assembly and msc_full_pegging1 - assembly */
480 and ms.transaction_id = mfp1.transaction_id
481 and ms.plan_id = mfp1.plan_id
482 and ms.sr_instance_id = mfp1.sr_instance_id
483 and ms.organization_id = mfp1.organization_id
484 /*msc_full_pegging1 - assembly and msc_full_pegging2 - component */
485 and mfp1.pegging_id = mfp2.end_pegging_id
486 and mfp1.plan_id = mfp2.plan_id
487 and mfp1.sr_instance_id = mfp2.sr_instance_id -- (No organization id join between mfp1 and mfp2 because single demand ca span across various orgs.
488 /* msc_full_pegging2 - component and msc_resource_requirements */
489 and mfp2.transaction_id = mrr.supply_id
490 and mfp2.plan_id = mrr.plan_id
491 and mfp2.sr_instance_id = mrr.sr_instance_id
492 and mfp2.organization_id = mrr.organization_id
493 /* msc_resource_requirements and msc_department_resources */
494 and mrr.resource_id = mdr.resource_id
495 and mrr.plan_id = mdr.plan_id
496 and mrr.sr_instance_id = mdr.sr_instance_id
497 and mrr.organization_id = mdr.organization_id
498 /* msc_department_resources and msc_plan_organizations - component */
499 and decode(mdr.resource_id,-1,mdr.department_code,mdr.resource_code)= p_component_pk
500 and mdr.plan_id = mpo2.plan_id
501 and mdr.sr_instance_id = mpo2.sr_instance_id
502 and mdr.organization_id = mpo2.organization_id
503 and mpo2.plan_id = p_supply_plan_id
504 --and mfp1.demand_date between p_effectivity_date and p_disable_date
505 and mrr.parent_id = 2; -- Records Inserted by HLS as Net Resource Requirements
506
507 /* Splitting Nmr and Dmr because a Resource can be used at numerous places
508 in routing, hence can't aggregate the Assembly's allocated quantity
509 in above query which may cause double counting in Dmr */
510
511 select /*+ ORDERED */ sum(ass_mfp.allocated_quantity)
512 INTO l_denominator
513 from msc_plan_organizations mpo,
514 msc_demands ass_md,
515 msc_system_items ass_msi,
516 msc_full_pegging ass_mfp,
517 msc_supplies ass_ms
518 where ass_msi.plan_id = mpo.plan_id
519 and ass_msi.sr_instance_id = mpo.sr_instance_id
520 and ass_msi.organization_id = mpo.organization_id
521 /* msc_system_items and msd_demands */
522 and ass_msi.plan_id = ass_md.plan_id
523 and ass_msi.sr_instance_id = ass_md.sr_instance_id
524 and ass_msi.organization_id = ass_md.organization_id
525 and ass_msi.inventory_item_id = ass_md.inventory_item_id
526 and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
527 /*msc_demands and msc_full_pegging */
528 and ass_md.demand_id = ass_mfp.demand_id
529 and ass_md.plan_id = ass_mfp.plan_id
530 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
531 and ass_md.organization_id = ass_mfp.organization_id
532 and ass_md.origination_type in (6,8,29,30)
533 /* msc_full_pegging and msc_supplies */
534 and ass_ms.transaction_id = ass_mfp.transaction_id
535 and ass_ms.plan_id = ass_mfp.plan_id
536 and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
537 and ass_ms.organization_id = ass_mfp.organization_id
538 and ass_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
539 /* For given PLAN,INSTANCE,ASSEMBLY,EFF and DISABLE DATE */
540 and mpo.plan_id = p_supply_plan_id
541 and ass_msi.sr_instance_id = p_instance_id
542 and ass_msi.sr_inventory_item_id = p_assembly_pk;
543 --and ass_md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
544
545 LOG_MESSAGE('The value for numerator is:'||l_numerator);
546 LOG_MESSAGE('The value for denominator is:'||l_denominator);
547
548
549 IF l_denominator <> 0 THEN
550 l_return_value := l_numerator/l_denominator;
551 LOG_MESSAGE('The value of return value is:'||l_return_value);
552 ELSE
553 l_return_value := 0;
554 LOG_MESSAGE('The return value is zero because of zero denominator');
555 END IF;
556
557 return l_return_value;
558
559 END IF; --IF p_cu_or_lt = C_CU THEN
560
561
562 END IF; --IF p_res_comp = 'C' THEN
563
564 LOG_MESSAGE('Exiting the function to calculate CAPACITY_USAGE_RATIO and LEAD_TIMES sucessfully');
565
566
567 exception
568 when others then
569 l_return_value := 0;
570 LOG_MESSAGE('Exiting the function to calculate CAPACITY_USAGE_RATIO and LEAD_TIMES from an exception block');
571 return l_return_value;
572 end calculate_cu_and_lt;
573
574 function calc_eol_wur( p_instance_id IN NUMBER,
575 p_supply_plan_id IN NUMBER,
576 p_assembly_pk IN VARCHAR2,
577 p_component_pk IN VARCHAR2 )
578 return number
579 is
580 l_numerator NUMBER :=0;
581 l_denominator NUMBER :=0;
582
583 l_return_value NUMBER :=0;
584
585 begin
586
587 LOG_MESSAGE('Entering in the function - calc_eol_wur - to calculate CAPACITY_USAGE_RATIO');
588
589 LOG_MESSAGE('Calculating the CAPACITY_USAGE_RATIO..');
590 LOG_MESSAGE('The SR_ASSEMBLY_PK is : '||p_assembly_pk);
591 LOG_MESSAGE('The SR_COMPONENT_PK is : '||p_component_pk);
592
593 /* Bug 5211017
594 IF ( p_assembly_pk = p_component_pk ) THEN
595
596 l_return_value := 1;
597 LOG_MESSAGE('The return value is one because p_assembly_pk and p_component_pk are same.');
598
599 return l_return_value;
600
601 ELSE */
602
603 -- Sum of Allocated Qunatities of Component meeting Independent Demands of Assembly
604 -- across All Organizations.
605 select sum(cmp_mfp.allocated_quantity)
606 INTO l_numerator
607 from
608 msc_plan_organizations ass_mpo,
609 msc_system_items ass_msi,
610 msc_demands ass_md,
611 msc_full_pegging ass_mfp,
612 msc_full_pegging cmp_mfp,
613 msc_demands cmp_md,
614 msc_system_items cmp_msi,
615 msc_plan_organizations cmp_mpo
616 where ass_mpo.plan_id = p_supply_plan_id
617 /* mpo_plan_organizations - components and msc_system_items - assembly */
618 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
619 and ass_msi.plan_id = ass_mpo.plan_id
620 and ass_msi.organization_id = ass_mpo.organization_id
621 /* msc_system_items - assembly and msc_demands - assembly */
622 and ass_md.inventory_item_id = ass_msi.inventory_item_id
623 and ass_md.plan_id = ass_msi.plan_id
624 and ass_md.sr_instance_id = ass_msi.sr_instance_id
625 and ass_md.organization_id = ass_msi.organization_id
626 and ass_md.origination_type in (6,8,29,30)
627 /* msc_demands - assembly and msc_full_pegging - assembly */
628 and ass_md.demand_id = ass_mfp.demand_id
629 and ass_md.plan_id = ass_mfp.plan_id
630 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
631 and ass_md.organization_id = ass_mfp.organization_id
632 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
633 and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
634 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
635 and cmp_mfp.plan_id = ass_mfp.plan_id
636 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
637 /* and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id Bug 5211017*/
638 /* msc_full_pegging - components and msc_demands - components */
639 and cmp_mfp.demand_id = cmp_md.demand_id
640 and cmp_mfp.organization_id = cmp_md.organization_id
641 and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
642 and cmp_mfp.plan_id = cmp_md.plan_id
643 /* msc_demands - components and msc_system_items - components */
644 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
645 and cmp_md.plan_id = cmp_msi.plan_id
646 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
647 and cmp_md.organization_id = cmp_msi.organization_id
648 /* msc_system_items - components and mpo_plan_organizations - components */
649 and cmp_msi.organization_id = cmp_mpo.organization_id
650 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
651 and cmp_msi.plan_id = cmp_mpo.plan_id
652 /* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
653 and cmp_mpo.plan_id = p_supply_plan_id
654 and ass_msi.sr_instance_id = p_instance_id
655 and ass_msi.sr_inventory_item_id = p_assembly_pk
656 and cmp_msi.sr_inventory_item_id = p_component_pk;
657
658 -- Gross Requirements of Components across All Organizations.
659
660 select sum(cmp_md.USING_REQUIREMENT_QUANTITY)
661 INTO l_denominator
662 from
663 msc_plan_organizations cmp_mpo,
664 msc_system_items cmp_msi,
665 msc_demands cmp_md
666 where cmp_mpo.plan_id = p_supply_plan_id
667 /* msc_system_items - comp and msc_plan_organizations - comp */
668 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
669 and cmp_msi.plan_id = cmp_mpo.plan_id
670 and cmp_msi.organization_id = cmp_mpo.organization_id
671 /* msc_demands - comp and msc_system_items - comp */
672 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
673 and cmp_md.plan_id = cmp_msi.plan_id
674 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
675 and cmp_md.organization_id = cmp_msi.organization_id
676 and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
677 /* For given PLAN,INSTANCE and COMPONENT*/
678 and cmp_mpo.plan_id = p_supply_plan_id
679 and cmp_msi.sr_instance_id = p_instance_id
680 and cmp_msi.sr_inventory_item_id = p_component_pk;
681
682 LOG_MESSAGE('The value for numerator is:'||l_numerator);
683 LOG_MESSAGE('The value for denominator is:'||l_denominator);
684
685
686 IF l_denominator <> 0 THEN
687 l_return_value := l_numerator/l_denominator;
688 LOG_MESSAGE('The value of return value is:'||l_return_value);
689
690 ELSE
691 l_return_value := 0;
692 LOG_MESSAGE('The return value is zero because of zero denominator');
693
694 END IF;
695
696 return l_return_value;
697
698 /* END IF; --IF p_assembly_pk = p_component_pk THEN Bug 5211017*/
699
700
701 exception
702 when others then
703 l_return_value := 0;
704 LOG_MESSAGE('Exiting the function to calculate CAPACITY_USAGE_RATIO for Where Used Report in EOL from an exception block');
705 return l_return_value;
706 end calc_eol_wur;
707
708 function calc_eol_smb( p_cu_or_lt IN NUMBER,
709 p_instance_id IN NUMBER,
710 p_supply_plan_id IN NUMBER,
711 p_assembly_pk IN VARCHAR2,
712 p_component_pk IN VARCHAR2)
713 return number
714
715
716 is
717 l_numerator NUMBER :=0;
718 l_denominator NUMBER :=0;
719
720 l_return_value NUMBER :=0;
721
722 begin
723
724 LOG_MESSAGE('Entering in the function - calc_eol_smb - to calculate CAPACITY_USAGE_RATIO and LEAD_TIME');
725
726 IF p_cu_or_lt = C_CU THEN
727
728 LOG_MESSAGE('Calculating the CAPACITY_USAGE_RATIO..');
729 LOG_MESSAGE('The SR_ASSEMBLY_PK is : '||p_assembly_pk);
730 LOG_MESSAGE('The SR_COMPONENT_PK is : '||p_component_pk);
731
732
733 IF ( p_assembly_pk = p_component_pk ) THEN
734
735 l_return_value := 1;
736 LOG_MESSAGE('The return value is one because p_assembly_pk and p_component_pk are same.');
737
738 return l_return_value;
739
740 ELSE
741
742 -- Sum of Gross Requirements for Components across All Organizations
743 -- that are end pegged to independent demands for Assembly across all Organizations.
744
745 select sum(cmp_mfp.allocated_quantity)
746 INTO l_numerator
747 from
748 msc_plan_organizations ass_mpo,
749 msc_system_items ass_msi,
750 msc_demands ass_md,
751 msc_full_pegging ass_mfp,
752 msc_full_pegging cmp_mfp,
753 msc_demands cmp_md,
754 --msc_supplies cmp_ms BUG 5210812,
755 msc_system_items cmp_msi,
756 msc_plan_organizations cmp_mpo
757 where ass_mpo.plan_id = p_supply_plan_id
758 /* mpo_plan_organizations - components and msc_system_items - assembly */
759 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
760 and ass_msi.plan_id = ass_mpo.plan_id
761 and ass_msi.organization_id = ass_mpo.organization_id
762 /* msc_system_items - assembly and msc_demands - assembly */
763 and ass_md.inventory_item_id = ass_msi.inventory_item_id
764 and ass_md.plan_id = ass_msi.plan_id
765 and ass_md.sr_instance_id = ass_msi.sr_instance_id
766 and ass_md.organization_id = ass_msi.organization_id
767 and ass_md.origination_type in (6,8,29,30) -- Independent Demands of Assembly
768 /* msc_demands - assembly and msc_full_pegging - assembly */
769 and ass_md.demand_id = ass_mfp.demand_id
770 and ass_md.plan_id = ass_mfp.plan_id
771 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
772 and ass_md.organization_id = ass_mfp.organization_id
773 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
774 and ass_mfp.end_origination_type in (6,8,29,30) -- Independent Demands of Assembly
775 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
776 and cmp_mfp.plan_id = ass_mfp.plan_id
777 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
778 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
779 /* msc_full_pegging - components and msc_demands - components */
780 and cmp_mfp.demand_id = cmp_md.demand_id
781 and cmp_mfp.organization_id = cmp_md.organization_id
782 and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
783 and cmp_mfp.plan_id = cmp_md.plan_id
784 and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
785 /* msc_full_pegging - components and msc_supplies - componnets
786 and cmp_ms.transaction_id = cmp_mfp.transaction_id
787 and cmp_ms.plan_id = cmp_mfp.plan_id
788 and cmp_ms.sr_instance_id = cmp_mfp.sr_instance_id
789 and cmp_ms.organization_id = cmp_mfp.organization_id
790 and cmp_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
791 Commented above code for the BUG 5210812*/
792 /* msc_demands - components and msc_system_items - components */
793 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
794 and cmp_md.plan_id = cmp_msi.plan_id
795 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
796 and cmp_md.organization_id = cmp_msi.organization_id
797 /* msc_system_items - components and mpo_plan_organizations - components */
798 and cmp_msi.organization_id = cmp_mpo.organization_id
799 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
800 and cmp_msi.plan_id = cmp_mpo.plan_id
801 /* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
802 and cmp_mpo.plan_id = p_supply_plan_id
803 and ass_msi.sr_instance_id = p_instance_id
804 and ass_msi.sr_inventory_item_id = p_assembly_pk
805 and cmp_msi.sr_inventory_item_id = p_component_pk;
806
807
808 -- Independent Demands for Assembly across All Organizations.
809
810 select sum(ass_md.USING_REQUIREMENT_QUANTITY)
811 INTO l_denominator
812 from
813 msc_plan_organizations ass_mpo,
814 msc_system_items ass_msi,
815 msc_demands ass_md
816 where ass_mpo.plan_id = p_supply_plan_id
817 /* msc_system_items - asmb and msc_plan_organizations - asmb */
818 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
819 and ass_msi.plan_id = ass_mpo.plan_id
820 and ass_msi.organization_id = ass_mpo.organization_id
821 /* msc_demands - asmb and msc_system_items - asmb */
822 and ass_md.inventory_item_id = ass_msi.inventory_item_id
823 and ass_md.plan_id = ass_msi.plan_id
824 and ass_md.sr_instance_id = ass_msi.sr_instance_id
825 and ass_md.organization_id = ass_msi.organization_id
826 and ass_md.origination_type in (6,8,29,30) -- Independent Demands for Assembly
827 /* For given PLAN,INSTANCE and COMPONENT */
828 and ass_mpo.plan_id = p_supply_plan_id
829 and ass_msi.sr_instance_id = p_instance_id
830 and ass_msi.sr_inventory_item_id = p_assembly_pk;
831
832
833
834
835 LOG_MESSAGE('The value for numerator is:'||l_numerator);
836 LOG_MESSAGE('The value for denominator is:'||l_denominator);
837
838
839 IF l_denominator <> 0 THEN
840 l_return_value := l_numerator/l_denominator;
841 LOG_MESSAGE('The value of return value is:'||l_return_value);
842
843 ELSE
844 l_return_value := 0;
845 LOG_MESSAGE('The return value is zero because of zero denominator');
846
847 END IF;
848
849 return l_return_value;
850
851 END IF; -- IF ( p_assembly_pk = p_component_pk ) THEN
852
853
854 ELSE
855
856 LOG_MESSAGE('Calculating the LEAD_TIME ..');
857 LOG_MESSAGE('The SR_ASSEMBLY_PK is :'||p_assembly_pk);
858 LOG_MESSAGE('The SR_COMPONENT_PK is :'||p_component_pk);
859
860 IF ( p_assembly_pk = p_component_pk ) THEN
861
862 l_return_value := 0;
863 LOG_MESSAGE('The return value is zero because p_assembly_pk and p_component_pk are same.');
864
865 return l_return_value;
866
867 ELSE
868
869
870 select sum(cmp_mfp.allocated_quantity*(ass_md.USING_ASSEMBLY_DEMAND_DATE - cmp_md.USING_ASSEMBLY_DEMAND_DATE))
871 INTO l_denominator
872 from
873 msc_plan_organizations ass_mpo,
874 msc_system_items ass_msi,
875 msc_demands ass_md,
876 msc_full_pegging ass_mfp,
877 msc_full_pegging cmp_mfp,
878 msc_demands cmp_md,
879 msc_system_items cmp_msi,
880 msc_plan_organizations cmp_mpo
881 where ass_mpo.plan_id = p_supply_plan_id
882 /* mpo_plan_organizations - components and msc_system_items - assembly */
883 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
884 and ass_msi.plan_id = ass_mpo.plan_id
885 and ass_msi.organization_id = ass_mpo.organization_id
886 /* msc_system_items - assembly and msc_demands - assembly */
887 and ass_md.inventory_item_id = ass_msi.inventory_item_id
888 and ass_md.plan_id = ass_msi.plan_id
889 and ass_md.sr_instance_id = ass_msi.sr_instance_id
890 and ass_md.organization_id = ass_msi.organization_id
891 and ass_md.origination_type in (6,8,29,30) -- Independent Demands of Assembly
892 /* msc_demands - assembly and msc_full_pegging - assembly */
893 and ass_md.demand_id = ass_mfp.demand_id
894 and ass_md.plan_id = ass_mfp.plan_id
895 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
896 and ass_md.organization_id = ass_mfp.organization_id
897 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
898 and ass_mfp.end_origination_type in (6,8,29,30) -- Independent Demands of Assembly
899 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
900 and cmp_mfp.plan_id = ass_mfp.plan_id
901 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
902 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
903 /* msc_full_pegging - components and msc_demands - components */
904 and cmp_mfp.demand_id = cmp_md.demand_id
905 and cmp_mfp.organization_id = cmp_md.organization_id
906 and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
907 and cmp_mfp.plan_id = cmp_md.plan_id
908 and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
909 /* msc_demands - components and msc_system_items - components */
910 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
911 and cmp_md.plan_id = cmp_msi.plan_id
912 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
913 and cmp_md.organization_id = cmp_msi.organization_id
914 /* msc_system_items - components and mpo_plan_organizations - components */
915 and cmp_msi.organization_id = cmp_mpo.organization_id
916 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
917 and cmp_msi.plan_id = cmp_mpo.plan_id
918 /* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
919 and cmp_mpo.plan_id = p_supply_plan_id
920 and ass_msi.sr_instance_id = p_instance_id
921 and ass_msi.sr_inventory_item_id = p_assembly_pk
922 and cmp_msi.sr_inventory_item_id = p_component_pk;
923
924
925
926
927
928
929 -- Sum of Gross Requirements for Components across All Organizations
930 -- that are end pegged to independent demands for Assembly across all Organizations.
931
932 select sum(cmp_mfp.allocated_quantity)
933 INTO l_denominator
934 from
935 msc_plan_organizations ass_mpo,
936 msc_system_items ass_msi,
937 msc_demands ass_md,
938 msc_full_pegging ass_mfp,
939 msc_full_pegging cmp_mfp,
940 msc_demands cmp_md,
941 msc_system_items cmp_msi,
942 msc_plan_organizations cmp_mpo
943 where ass_mpo.plan_id = p_supply_plan_id
944 /* mpo_plan_organizations - components and msc_system_items - assembly */
945 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
946 and ass_msi.plan_id = ass_mpo.plan_id
947 and ass_msi.organization_id = ass_mpo.organization_id
948 /* msc_system_items - assembly and msc_demands - assembly */
949 and ass_md.inventory_item_id = ass_msi.inventory_item_id
950 and ass_md.plan_id = ass_msi.plan_id
951 and ass_md.sr_instance_id = ass_msi.sr_instance_id
952 and ass_md.organization_id = ass_msi.organization_id
953 and ass_md.origination_type in (6,8,29,30) -- Independent Demands of Assembly
954 /* msc_demands - assembly and msc_full_pegging - assembly */
955 and ass_md.demand_id = ass_mfp.demand_id
956 and ass_md.plan_id = ass_mfp.plan_id
957 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
958 and ass_md.organization_id = ass_mfp.organization_id
959 /* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
960 and ass_mfp.end_origination_type in (6,8,29,30) -- Independent Demands of Assembly
961 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
962 and cmp_mfp.plan_id = ass_mfp.plan_id
963 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
964 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
965 /* msc_full_pegging - components and msc_demands - components */
966 and cmp_mfp.demand_id = cmp_md.demand_id
967 and cmp_mfp.organization_id = cmp_md.organization_id
968 and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
969 and cmp_mfp.plan_id = cmp_md.plan_id
970 and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
971 /* msc_demands - components and msc_system_items - components */
972 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
973 and cmp_md.plan_id = cmp_msi.plan_id
974 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
975 and cmp_md.organization_id = cmp_msi.organization_id
976 /* msc_system_items - components and mpo_plan_organizations - components */
977 and cmp_msi.organization_id = cmp_mpo.organization_id
978 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
979 and cmp_msi.plan_id = cmp_mpo.plan_id
980 /* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
981 and cmp_mpo.plan_id = p_supply_plan_id
982 and ass_msi.sr_instance_id = p_instance_id
983 and ass_msi.sr_inventory_item_id = p_assembly_pk
984 and cmp_msi.sr_inventory_item_id = p_component_pk;
985
986
987
988
989
990 LOG_MESSAGE('The value for numerator is:'||l_numerator);
991 LOG_MESSAGE('The value for denominator is:'||l_denominator);
992
993
994 IF l_denominator <> 0 THEN
995 l_return_value := l_numerator/l_denominator;
996 LOG_MESSAGE('The value of return value is:'||l_return_value);
997
998 ELSE
999 l_return_value := 0;
1000 LOG_MESSAGE('The return value is zero because of zero denominator');
1001
1002 END IF;
1003
1004 return l_return_value;
1005
1006 END IF; --IF ( p_assembly_pk = p_component_pk ) THEN
1007
1008 END IF; --IF p_cu_or_lt = C_CU THEN
1009
1010 exception
1011 when others then
1012 l_return_value := 0;
1013 LOG_MESSAGE('Exiting the function to calculate CAPACITY_USAGE_RATIO and LEAD TIME for Simulation BOM in EOL from an exception block');
1014 return l_return_value;
1015 end calc_eol_smb;
1016
1017
1018
1019
1020 procedure populate_bom ( errbuf OUT NOCOPY VARCHAR2,
1021 retcode OUT NOCOPY NUMBER,
1022 p_demand_plan_id IN NUMBER)
1023 is
1024
1025 cursor Supply_Plans is
1026 select distinct supply_plan_id
1027 from msd_dp_scenarios
1028 where demand_plan_id = p_demand_plan_id
1029 and nvl(supply_plan_id,-1) > 0; -- For Legacy Supply Plans the Supply_Plan_Name field will be populated with the Designators.
1030 -- However, UI will populate supply_plan_id as -99.
1031
1032 cursor c_assmb_comp (p_supply_plan_id NUMBER ) is
1033 select /*+ ORDERED */ distinct
1034 ass_msi.sr_instance_id SR_INSTANCE_ID,
1035 ass_msi.sr_inventory_item_id SR_ASSEMBLY_PK,
1036 cmp_msi.sr_inventory_item_id SR_COMPONENT_PK,
1037 trunc(ass_mfp.demand_date,'MM') EFFECTIVITY_DATE,
1038 last_day(ass_mfp.demand_date) DISABLE_DATE
1039 from
1040 msc_plan_organizations ass_mpo,
1041 msc_system_items ass_msi,
1042 msd_level_values ass_mlv,
1043 msc_demands ass_md,
1044 msc_full_pegging ass_mfp,
1045 msc_full_pegging cmp_mfp,
1046 msc_demands cmp_md,
1047 msd_level_values cmp_mlv,
1048 msc_system_items cmp_msi,
1049 msc_plan_organizations cmp_mpo
1050 where ass_mpo.plan_id = p_supply_plan_id
1051 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
1052 and ass_msi.plan_id = ass_mpo.plan_id
1053 and ass_msi.organization_id = ass_mpo.organization_id
1054 /* msc_system_items - assembly and msd_leve_values - assembly */
1055 and ass_mlv.instance = ass_msi.sr_instance_id
1056 and ass_mlv.level_id = 1
1057 and ass_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
1058 /* msc_system_items - assembly and msc_demands - assembly */
1059 and ass_md.inventory_item_id = ass_msi.inventory_item_id
1060 and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
1061 and ass_md.plan_id = ass_msi.plan_id
1062 and ass_md.sr_instance_id = ass_msi.sr_instance_id
1063 and ass_md.organization_id = ass_msi.organization_id
1064 /* msc_demands - assembly and msc_full_pegging - assembly */
1065 and ass_md.demand_id = ass_mfp.demand_id
1066 and ass_md.plan_id = ass_mfp.plan_id
1067 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
1068 and ass_md.organization_id = ass_mfp.organization_id
1069 /* msc_full_pegging - assembly and msc_full_pegging - component */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
1070 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
1071 and cmp_mfp.plan_id = ass_mfp.plan_id
1072 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
1073 and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
1074 /* msc_full_pegging - component and msc_demands - component */
1075 and cmp_mfp.demand_id = cmp_md.demand_id
1076 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
1077 and cmp_md.plan_id = cmp_msi.plan_id
1078 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
1079 and cmp_md.organization_id = cmp_msi.organization_id
1080 /* msc_system_items - assembly and msd_leve_values - assembly */
1081 and cmp_mlv.instance = ass_msi.sr_instance_id
1082 and cmp_mlv.level_id = 1
1083 and cmp_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
1084 /* msc_system_items - components and msc_demands - components */
1085 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
1086 and cmp_msi.plan_id = cmp_mpo.plan_id
1087 and cmp_msi.organization_id = cmp_mpo.organization_id
1088 and cmp_msi.planning_make_buy_code = 2 -- Buy Items Only
1089 and cmp_msi.critical_component_flag = 1 -- Critical Component Only
1090 and cmp_mpo.plan_id = p_supply_plan_id -- For a given ascp plan
1091 and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id; -- This condition is required as we are getting the assembly as component to same assembly, in case of Inter Org Transfer.
1092
1093
1094 cursor c_assmb_res(p_supply_plan_id NUMBER) is
1095 select /*+ ORDERED */
1096 distinct
1097 msi.sr_instance_id SR_INSTANCE_ID,
1098 msi.sr_inventory_item_id SR_ASSEMBLY_PK,
1099 decode(mdr.resource_id,-1,'L'||'.'||mdr.department_code,'R'||'.'||mdr.resource_code) SR_COMPONENT_PK,
1100 trunc(mfp1.demand_date,'MM') EFFECTIVITY_DATE,
1101 last_day(mfp1.demand_date) DISABLE_DATE
1102 from
1103 msc_plan_organizations mpo1,
1104 msc_system_items msi,
1105 msd_level_values mlv1,
1106 msc_demands md,
1107 msc_full_pegging mfp1,
1108 msc_full_pegging mfp2,
1109 msc_resource_requirements mrr,
1110 msc_department_resources mdr,
1111 msd_level_values mlv2,
1112 msc_plan_organizations mpo2
1113 where
1114 mpo1.plan_id = p_supply_plan_id
1115 and msi.plan_id = mpo1.plan_id
1116 and msi.organization_id = mpo1.organization_id
1117 and msi.sr_instance_id = mpo1.sr_instance_id
1118 /*msc_system_items and msd_level_values */
1119 and mlv1.instance = msi.sr_instance_id
1120 and mlv1.level_id = 1
1121 and mlv1.sr_level_pk = to_char(msi.sr_inventory_item_id)
1122 /* msc_system_items and msc_demands */
1123 and msi.inventory_item_id = md.inventory_item_id
1124 and msi.plan_id = md.plan_id
1125 and msi.organization_id = md.organization_id
1126 and msi.sr_instance_id = md.sr_instance_id
1127 /*msc_demands and msc_full_pegging1 */
1128 and md.demand_id = mfp1.demand_id
1129 and md.plan_id = mfp1.plan_id
1130 and md.sr_instance_id = mfp1.sr_instance_id
1131 and md.organization_id = mfp1.organization_id
1132 /*msc_full_pegging1 and msc_full_pegging2 */
1133 and mfp1.pegging_id = mfp2.end_pegging_id
1134 and mfp1.plan_id = mfp2.plan_id
1135 and mfp1.sr_instance_id = mfp2.sr_instance_id -- (No organization id join between mfp1 and mfp2 because single demand ca span across various orgs.
1136 /* msc_full_pegging2 and msc_resource_requirements */
1137 and mfp2.transaction_id = mrr.supply_id
1138 and mfp2.plan_id = mrr.plan_id
1139 and mfp2.sr_instance_id = mrr.sr_instance_id
1140 and mfp2.organization_id = mrr.organization_id
1141 /* msc_resource_requirements and msc_department_resources */
1142 and mrr.resource_id = mdr.resource_id
1143 and mrr.plan_id = mdr.plan_id
1144 and mrr.sr_instance_id = mdr.sr_instance_id
1145 and mrr.organization_id = mdr.organization_id
1146 /* msc_department_resources and msd_level_values */
1147 and mlv2.instance = mdr.sr_instance_id
1148 and mlv2.level_id = 1
1149 and mlv2.sr_level_pk = decode(mdr.resource_id,-1,'L'||'.'||mdr.department_code,'R'||'.'||mdr.resource_code)
1150 /* msc_department_resources and msc_plan_organizations */
1151 and mdr.plan_id = mpo2.plan_id
1152 and mdr.sr_instance_id = mpo2.sr_instance_id
1153 and mdr.organization_id = mpo2.organization_id
1154 and mpo2.plan_id = p_supply_plan_id
1155 and mrr.parent_id = 2;
1156
1157
1158
1159
1160 cursor c_collapsed_bom(p_demand_plan_id NUMBER) is
1161 select distinct
1162 sup_plan_bom.sr_instance_id,
1163 sup_plan_bom.sr_assembly_pk,
1164 sup_plan_bom.sr_component_pk,
1165 sup_plan_bom.effectivity_date,
1166 sup_plan_bom.disable_date,
1167 sup_plan_bom.res_comp
1168 from msd_ascp_bom_comp sup_plan_bom,
1169 msc_plans sup_plan,
1170 msd_dp_scenarios dp_scen
1171 where sup_plan_bom.cap_usg_ratio_obj = sup_plan.compile_designator
1172 and sup_plan.plan_id = dp_scen.supply_plan_id
1173 and dp_scen.demand_plan_id = p_demand_plan_id
1174 and dp_scen.supply_plan_id is not null
1175 --and dp_scen.supply_plan_id > 0 (Required or Redundant)..?
1176 order by sup_plan_bom.sr_instance_id,sup_plan_bom.sr_assembly_pk,sup_plan_bom.sr_component_pk;
1177
1178
1179 cursor c_supply_plan_scenarios is
1180 select scenario_name,supply_plan_name,old_supply_plan_name
1181 from msd_dp_scenarios
1182 where demand_plan_id = p_demand_plan_id
1183 and supply_plan_name is not null; -- No need to add condition of supply_plan_id > 0 because we need to
1184 -- consider scenarios with legacy loaded streams attached.
1185
1186 cursor c_if_plan_still_attached(p_supply_plan_name varchar2) is
1187 select scenario_name
1188 from msd_dp_scenarios
1189 where demand_plan_id = p_demand_plan_id
1190 and old_supply_plan_name = p_supply_plan_name
1191 AND rownum < 2;
1192
1193 cursor c_if_plan_removed_currently(p_supply_plan_name varchar2) is
1194 select scenario_name
1195 from msd_dp_scenarios
1196 where demand_plan_id = p_demand_plan_id
1197 and supply_plan_name = p_supply_plan_name
1198 AND rownum < 2;
1199
1200 v_plan_comp_date date := C_NULL_DATE;
1201 v_plan_name varchar2(30) := to_char(null);
1202 v_last_collected_date date := C_NULL_DATE;
1203 v_bom_cal BOOLEAN := FALSE;
1204 /*
1205 l_effectivity_date DATE;
1206 l_disable_date DATE;
1207 */
1208 lb_FetchComplete Boolean;
1209 ln_rows_to_fetch Number := nvl(TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1210
1211 TYPE CharTblTyp IS TABLE OF VARCHAR2(240);
1212 TYPE NumTblTyp IS TABLE OF NUMBER;
1213 TYPE DateTblTyp IS TABLE OF DATE;
1214
1215 lb_instance_id CharTblTyp;
1216 lb_assembly_pk CharTblTyp;
1217 lb_component_pk CharTblTyp;
1218 lb_res_comp CharTblTyp;
1219 lb_effectivity_date DateTblTyp;
1220 lb_disable_date DateTblTyp;
1221
1222 lv_new_plan_attached BOOLEAN :=FALSE;
1223 lv_removed_plan BOOLEAN :=FALSE;
1224
1225 l_scenario_name msd_dp_scenarios.scenario_name%TYPE;
1226 l_supply_plan_name msd_dp_scenarios.supply_plan_name%TYPE;
1227 l_old_supply_plan_name msd_dp_scenarios.old_supply_plan_name%TYPE;
1228
1229 l_scen_name msd_dp_scenarios.scenario_name%TYPE;
1230
1231 begin
1232
1233 LOG_MESSAGE('***********************************************************************');
1234 LOG_MESSAGE('Entered in procedure POPULATE_BOM');
1235 LOG_MESSAGE('***********************************************************************');
1236
1237 LOG_MESSAGE('The value for Demand plan ID is: '||p_demand_plan_id);
1238
1239 retcode := 0;
1240
1241 LOG_MESSAGE('***********************************************************************');
1242 LOG_MESSAGE('Entering the Loop for all Supply Plans Attached...');
1243 LOG_MESSAGE('***********************************************************************');
1244 For Supply_Plans_Rec in Supply_Plans Loop
1245
1246 select compile_designator,trunc(plan_completion_date) into v_plan_name,v_plan_comp_date
1247 from msc_plans
1248 where plan_id = Supply_Plans_Rec.supply_plan_id;
1249
1250
1251 BEGIN
1252
1253 select nvl(trunc(last_collected_date),C_NULL_DATE) into v_last_collected_date
1254 from msd_ascp_bom_comp
1255 where cap_usg_ratio_obj = v_plan_name
1256 and plan_type= 'SOP'
1257 and rownum < 2;
1258
1259 EXCEPTION
1260
1261 WHEN NO_DATA_FOUND THEN
1262 v_last_collected_date := C_NULL_DATE;
1263 WHEN OTHERS THEN
1264 LOG_MESSAGE('Error fetching the LAST_COLLECTED_DATE from MSD_ASCP_BOM_COMP');
1265 retcode := -1 ;
1266 errbuf := substr(SQLERRM,1,150);
1267 END;
1268
1269 LOG_MESSAGE('Supply Plans Name: '||v_plan_name);
1270 LOG_MESSAGE('Supply Plans ID: '||Supply_Plans_Rec.supply_plan_id);
1271
1272 IF ( v_plan_comp_date > v_last_collected_date ) THEN
1273
1274 LOG_MESSAGE('Entering to start the calculation for this Plan.');
1275
1276
1277 v_bom_cal := TRUE;
1278
1279 LOG_MESSAGE('Deleting the table MSD_ASCP_BOM_COMP for this Plan');
1280 delete from msd_ascp_bom_comp
1281 where cap_usg_ratio_obj = v_plan_name
1282 and plan_type = 'SOP';
1283
1284 lb_FetchComplete := FALSE;
1285
1286 LOG_MESSAGE('***********************************************************************');
1287 LOG_MESSAGE('Opening the Assembly Component cursor');
1288 LOG_MESSAGE('***********************************************************************');
1289
1290 OPEN c_assmb_comp(Supply_Plans_Rec.supply_plan_id);
1291
1292 IF (c_assmb_comp%ISOPEN) THEN
1293 LOG_MESSAGE('Value of c_assmb_comp%ISOPEN is TRUE');
1294 ELSE
1295 LOG_MESSAGE('Value of c_assmb_comp%ISOPEN is FALSE');
1296 END IF;
1297
1298 IF (c_assmb_comp%ISOPEN) THEN
1299
1300 LOOP
1301
1302 IF (lb_FetchComplete) THEN
1303 EXIT;
1304 END IF;
1305
1306 FETCH c_assmb_comp BULK COLLECT INTO
1307 lb_instance_id,
1308 lb_assembly_pk,
1309 lb_component_pk,
1310 lb_effectivity_date,
1311 lb_disable_date
1312 LIMIT ln_rows_to_fetch;
1313
1314
1315 IF (c_assmb_comp%NOTFOUND) THEN
1316 lb_FetchComplete := TRUE;
1317 END IF;
1318
1319
1320 if c_assmb_comp%ROWCOUNT > 0 then
1321
1322 FORALL j IN lb_instance_id.FIRST..lb_instance_id.LAST
1323 INSERT INTO msd_ascp_bom_comp
1324 ( SR_INSTANCE_ID,
1325 CAP_USG_RATIO_OBJ,
1326 SR_ASSEMBLY_PK,
1327 SR_COMPONENT_PK,
1328 EFFECTIVITY_DATE,
1329 DISABLE_DATE,
1330 RES_COMP,
1331 CAPACITY_USAGE_RATIO,
1332 LEAD_TIME,
1333 LAST_COLLECTED_DATE,
1334 LAST_UPDATE_DATE,
1335 LAST_UPDATED_BY,
1336 CREATION_DATE,
1337 CREATED_BY,
1338 PLAN_TYPE,
1339 BOM_TYPE )
1340 SELECT
1341 lb_instance_id(j),
1342 v_plan_name,
1343 lb_assembly_pk(j),
1344 lb_component_pk(j),
1345 lb_effectivity_date(j),
1346 lb_disable_date(j),
1347 'C',
1348 calculate_cu_and_lt(C_CU,
1349 lb_instance_id(j),
1350 Supply_Plans_Rec.supply_plan_id,
1351 lb_assembly_pk(j),
1352 lb_component_pk(j),
1353 'C',
1354 lb_effectivity_date(j),
1355 lb_disable_date(j)),
1356 calculate_cu_and_lt(C_LT,
1357 lb_instance_id(j),
1358 Supply_Plans_Rec.supply_plan_id,
1359 lb_assembly_pk(j),
1360 lb_component_pk(j),
1361 'C',
1362 lb_effectivity_date(j),
1363 lb_disable_date(j)),
1364 sysdate,
1365 sysdate,
1366 FND_GLOBAL.USER_ID,
1367 sysdate,
1368 FND_GLOBAL.USER_ID,
1369 'SOP',
1370 'SOP'
1371 FROM DUAL;
1372
1373 end if;
1374
1375 END LOOP; --LOOP
1376
1377 END IF; --IF (c_assmb_comp%ISOPEN) THEN
1378 CLOSE c_assmb_comp;
1379
1380 LOG_MESSAGE('***********************************************************************');
1381 LOG_MESSAGE('Closed the Assembly Component cursor');
1382 LOG_MESSAGE('***********************************************************************');
1383
1384 -- Populate Resources.
1385
1386 lb_instance_id := NULL;
1387 lb_assembly_pk := NULL;
1388 lb_component_pk := NULL;
1389 lb_FetchComplete := FALSE;
1390
1391 LOG_MESSAGE('***********************************************************************');
1392 LOG_MESSAGE('Opening the Assembly Resource cursor');
1393 LOG_MESSAGE('***********************************************************************');
1394
1395 OPEN c_assmb_res(Supply_Plans_Rec.supply_plan_id);
1396 IF (c_assmb_res%ISOPEN) THEN
1397
1398 LOOP
1399
1400 IF (lb_FetchComplete) THEN
1401 EXIT;
1402 END IF;
1403
1404 FETCH c_assmb_res BULK COLLECT INTO
1405 lb_instance_id,
1406 lb_assembly_pk,
1407 lb_component_pk,
1408 lb_effectivity_date,
1409 lb_disable_date
1410 LIMIT ln_rows_to_fetch;
1411
1412
1413 IF (c_assmb_res%NOTFOUND) THEN
1414 lb_FetchComplete := TRUE;
1415 END IF;
1416
1417
1418 if c_assmb_res%ROWCOUNT > 0 then
1419
1420 FORALL j IN lb_instance_id.FIRST..lb_instance_id.LAST
1421 INSERT INTO msd_ascp_bom_comp
1422 ( SR_INSTANCE_ID,
1423 CAP_USG_RATIO_OBJ,
1424 SR_ASSEMBLY_PK,
1425 SR_COMPONENT_PK,
1426 EFFECTIVITY_DATE,
1427 DISABLE_DATE,
1428 RES_COMP,
1429 CAPACITY_USAGE_RATIO,
1430 LEAD_TIME,
1431 LAST_COLLECTED_DATE,
1432 LAST_UPDATE_DATE,
1433 LAST_UPDATED_BY,
1434 CREATION_DATE,
1435 CREATED_BY,
1436 PLAN_TYPE,
1437 BOM_TYPE )
1438 SELECT
1439 lb_instance_id(j),
1440 v_plan_name,
1441 lb_assembly_pk(j),
1442 lb_component_pk(j),
1443 lb_effectivity_date(j),
1444 lb_disable_date(j),
1445 substr(lb_component_pk(j),1,1),
1446 calculate_cu_and_lt(C_CU,
1447 lb_instance_id(j),
1448 Supply_Plans_Rec.supply_plan_id,
1449 lb_assembly_pk(j),
1450 substr(lb_component_pk(j),3,length(lb_component_pk(j))),
1451 substr(lb_component_pk(j),1,1),
1452 lb_effectivity_date(j),
1453 lb_disable_date(j)),
1454 calculate_cu_and_lt(C_LT,
1455 lb_instance_id(j),
1456 Supply_Plans_Rec.supply_plan_id,
1457 lb_assembly_pk(j),
1458 substr(lb_component_pk(j),3,length(lb_component_pk(j))),
1459 substr(lb_component_pk(j),1,1),
1460 lb_effectivity_date(j),
1461 lb_disable_date(j)),
1462 sysdate,
1463 sysdate,
1464 FND_GLOBAL.USER_ID,
1465 sysdate,
1466 FND_GLOBAL.USER_ID,
1467 'SOP',
1468 'SOP'
1469 FROM DUAL;
1470 end if;
1471
1472 END LOOP; --LOOP
1473
1474 END IF; --IF (c_assmb_res%ISOPEN) THEN
1475
1476 CLOSE c_assmb_res;
1477
1478 LOG_MESSAGE('***********************************************************************');
1479 LOG_MESSAGE('Closed the Assembly Resource cursor');
1480 LOG_MESSAGE('***********************************************************************');
1481
1482
1483 -- Calculate the Capacity Usage ratios and LeadTimes for
1484 -- Critical Components and Resources pertaining to a particular assembly.
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494 End IF; --IF ( v_plan_comp_date > v_last_collected_date ) THEN
1495
1496 End Loop;
1497 LOG_MESSAGE('***********************************************************************');
1498 LOG_MESSAGE('Exiting the Loop for all Supply Plans Attached');
1499 LOG_MESSAGE('***********************************************************************');
1500
1501
1502 --Determining the Flags, so as to rec-populate the Collapsed BOM
1503 -- only if there is no calculation of Capacity Usage Ratios and Lead Times
1504 -- in which case, Collapsed BOM will always be populated.
1505 IF ( v_bom_cal = FALSE ) THEN
1506
1507 LOG_MESSAGE('***********************************************************************');
1508 LOG_MESSAGE('Entering - to evaluate flags for Collapsed BOM');
1509 LOG_MESSAGE('***********************************************************************');
1510
1511 OPEN c_supply_plan_scenarios;
1512 LOOP
1513 FETCH c_supply_plan_scenarios INTO l_scenario_name,
1514 l_supply_plan_name,
1515 l_old_supply_plan_name;
1516
1517 EXIT WHEN c_supply_plan_scenarios%NOTFOUND;
1518
1519 IF l_supply_plan_name <> nvl(l_old_supply_plan_name,'-999') THEN
1520
1521
1522 --Setting the flag to recalculate the collapsed BOM if,
1523 --any of the new ASCP Plans have been attached
1524 OPEN c_if_plan_still_attached(l_supply_plan_name);
1525 FETCH c_if_plan_still_attached INTO l_scen_name;
1526
1527 IF c_if_plan_still_attached%NOTFOUND THEN
1528 lv_new_plan_attached := TRUE;
1529
1530 LOG_MESSAGE('Setting the Flag as New Supply Plan Attached');
1531 LOG_MESSAGE('Name of the New Supply Plan Attached: '||l_supply_plan_name);
1532
1533 END IF;
1534
1535 CLOSE c_if_plan_still_attached;
1536
1537 EXIT WHEN lv_new_plan_attached;
1538
1539
1540 --Setting the flag to recalculate the collapsed BOM if,
1541 --any of the ASCP Plans attached earlier but are not attcahed currently.
1542 OPEN c_if_plan_removed_currently(l_old_supply_plan_name); -- or OPEN c_if_plan_removed_currently(nvl(l_old_supply_plan_name,'-999')); ??
1543 FETCH c_if_plan_removed_currently INTO l_scen_name;
1544
1545 IF c_if_plan_removed_currently%NOTFOUND THEN
1546 lv_removed_plan := TRUE;
1547
1548 LOG_MESSAGE('Setting the Flag as Supply Plan has been Removed');
1549 LOG_MESSAGE('Name of the Existing Supply Plan Removed: '||l_old_supply_plan_name);
1550
1551 END IF;
1552
1553 CLOSE c_if_plan_removed_currently;
1554
1555
1556
1557 END IF;
1558
1559 EXIT WHEN lv_removed_plan;
1560
1561 END LOOP;
1562 CLOSE c_supply_plan_scenarios;
1563
1564 LOG_MESSAGE('***********************************************************************');
1565 LOG_MESSAGE('Exiting - to evaluate flags for Collapsed BOM');
1566 LOG_MESSAGE('***********************************************************************');
1567
1568 END IF;
1569
1570 IF lv_new_plan_attached THEN
1571 LOG_MESSAGE(' lv_new_plan_attached is true');
1572 ELSE
1573 LOG_MESSAGE(' lv_new_plan_attached is false');
1574 END IF;
1575
1576 IF lv_removed_plan THEN
1577 LOG_MESSAGE(' lv_removed_plan is true');
1578 ELSE
1579 LOG_MESSAGE(' lv_removed_plan is false');
1580 END IF;
1581
1582
1583 -- Deleting the Collapsed BOM table and populating the same.
1584 IF v_bom_cal OR lv_new_plan_attached OR lv_removed_plan THEN
1585
1586 LOG_MESSAGE('***********************************************************************');
1587 LOG_MESSAGE('Entering - to evaluate the Collapsed BOM');
1588 LOG_MESSAGE('***********************************************************************');
1589
1590 --Initialize the l_effectivity_date and l_disable_date for this SOP Plan.
1591 /*
1592 BEGIN
1593 select nvl(min(CURR_START_DATE),to_date(null)),to_date(null) INTO l_effectivity_date,l_disable_date
1594 from msc_plans
1595 where plan_id in ( select distinct supply_plan_id
1596 from msd_dp_scenarios
1597 where demand_plan_id = p_demand_plan_id
1598 and supply_plan_id > 0 );
1599 EXCEPTION
1600 WHEN NO_DATA_FOUND THEN
1601 l_effectivity_date := to_date(null);
1602 l_disable_date := to_date(null);
1603 --RETURN;
1604 WHEN OTHERS THEN
1605 l_effectivity_date := to_date(null);
1606 l_disable_date := to_date(null);
1607 log_debug ('There is an error while trying to fetch the Supply Plan Horizon Dates attached to the SNOP Plan.');
1608 --RETURN;
1609 END;
1610 */
1611 LOG_MESSAGE('Deleting the Collapsed BOM Data from table MSD_SOP_COLLAPESD_BOM_COMP');
1612 delete msd_sop_collapsed_bom_comp
1613 where demand_plan_id = p_demand_plan_id; --where sop_plan_id = p_demand_plan_id;
1614
1615
1616 lb_instance_id := NULL;
1617 lb_assembly_pk := NULL;
1618 lb_component_pk := NULL;
1619 lb_FetchComplete := FALSE;
1620
1621 LOG_MESSAGE('Opening the Collapsed BOM Cursor');
1622 OPEN c_collapsed_bom(p_demand_plan_id);
1623 IF (c_collapsed_bom%ISOPEN) THEN
1624
1625 LOOP
1626
1627 IF (lb_FetchComplete) THEN
1628 EXIT;
1629 END IF;
1630
1631 FETCH c_collapsed_bom BULK COLLECT INTO
1632 lb_instance_id,
1633 lb_assembly_pk,
1634 lb_component_pk,
1635 lb_effectivity_date,
1636 lb_disable_date,
1637 lb_res_comp
1638 LIMIT ln_rows_to_fetch;
1639
1640
1641 IF (c_collapsed_bom%NOTFOUND) THEN
1642 lb_FetchComplete := TRUE;
1643 END IF;
1644
1645
1646 if c_collapsed_bom%ROWCOUNT > 0 then
1647
1648 FORALL j IN lb_instance_id.FIRST..lb_instance_id.LAST
1649 INSERT INTO msd_sop_collapsed_bom_comp
1650 ( SR_INSTANCE_ID,
1651 DEMAND_PLAN_ID, --SOP_PLAN_ID,
1652 SR_ASSEMBLY_PK,
1653 SR_COMPONENT_PK,
1654 EFFECTIVITY_DATE,
1655 DISABLE_DATE,
1656 RES_COMP,
1657 LAST_UPDATE_DATE,
1658 LAST_UPDATED_BY,
1659 CREATION_DATE,
1660 CREATED_BY,
1661 PLAN_TYPE,
1662 BOM_TYPE )
1663 VALUES
1664 ( lb_instance_id(j),
1665 p_demand_plan_id,
1666 lb_assembly_pk(j),
1667 lb_component_pk(j),
1668 lb_effectivity_date(j), --l_effectivity_date,
1669 lb_disable_date(j), --l_disable_date,
1670 lb_res_comp(j),
1671 sysdate,
1672 FND_GLOBAL.USER_ID,
1673 sysdate,
1674 FND_GLOBAL.USER_ID,
1675 'SOP',
1676 'SOP' );
1677
1678 end if;
1679
1680 END LOOP; --LOOP
1681
1682 END IF; --IF (c_collapsed_bom%ISOPEN) THEN
1683 CLOSE c_collapsed_bom;
1684 LOG_MESSAGE('Closed the Collapsed BOM Cursor');
1685
1686 LOG_MESSAGE('Updating the Old Supply Plan Columns');
1687 -- Populating the Old_Supply_Plan_Name and Old_Supply_Plan_ID
1688 -- to track for the the next run if we need to re-populate the collapsed bom table for
1689 -- this plan or not.
1690 update msd_dp_scenarios
1691 set old_supply_plan_id = supply_plan_id,
1692 old_supply_plan_name = supply_plan_name
1693 where demand_plan_id = p_demand_plan_id
1694 and supply_plan_name is not null;
1695
1696
1697 LOG_MESSAGE('***********************************************************************');
1698 LOG_MESSAGE('Exiting - to evaluate the Collapsed BOM');
1699 LOG_MESSAGE('***********************************************************************');
1700
1701 END IF; --IF v_bom_cal OR lv_new_plan_attached OR lv_removed_plan THEN
1702
1703 /*
1704 LOG_MESSAGE('Updating the Old Supply Plan Columns');
1705 -- Populating the Old_Supply_Plan_Name and Old_Supply_Plan_ID
1706 -- to track for the the next run if we need to re-populate the collapsed bom table for
1707 -- this plan or not.
1708 update msd_dp_scenarios
1709 set old_supply_plan_id = supply_plan_id,
1710 old_supply_plan_name = supply_plan_name
1711 where demand_plan_id = p_demand_plan_id
1712 and supply_plan_name is not null;
1713 */
1714
1715 -- Final Commit;
1716 commit;
1717
1718 LOG_MESSAGE('***********************************************************************');
1719 LOG_MESSAGE('Exiting from the procedure POPULATE_BOM Sucessfully');
1720 LOG_MESSAGE('***********************************************************************');
1721
1722 exception
1723 when others then
1724 retcode := -1 ;
1725 errbuf := substr(SQLERRM,1,150);
1726
1727
1728 end populate_bom;
1729
1730 procedure populate_eol_bom (errbuf OUT NOCOPY VARCHAR2,
1731 retcode OUT NOCOPY NUMBER,
1732 p_demand_plan_id IN NUMBER)
1733
1734 is
1735
1736 cursor Supply_Plans is
1737 select distinct supply_plan_id
1738 from msd_dp_scenarios
1739 where demand_plan_id = p_demand_plan_id
1740 and nvl(supply_plan_id,-1) > 0; -- For Legacy Supply Plans the Supply_Plan_Name field will be populated with the Designators.
1741 -- However, UI will populate supply_plan_id as -99.
1742
1743 cursor c_assmb_comp_wur_bom (p_supply_plan_id NUMBER ) is
1744 select /*+ ORDERED */ distinct
1745 ass_msi.sr_instance_id SR_INSTANCE_ID,
1746 ass_msi.sr_inventory_item_id SR_ASSEMBLY_PK,
1747 cmp_msi.sr_inventory_item_id SR_COMPONENT_PK
1748 from
1749 msc_plan_organizations ass_mpo,
1750 msc_system_items ass_msi,
1751 msd_level_values ass_mlv,
1752 msc_demands ass_md,
1753 msc_full_pegging ass_mfp,
1754 msc_full_pegging cmp_mfp,
1755 msc_demands cmp_md,
1756 msd_level_values cmp_mlv,
1757 msc_system_items cmp_msi,
1758 msc_plan_organizations cmp_mpo
1759 where
1760 /* msc_system_items - assembly and msc_plans - assembly */
1761 ass_mpo.plan_id = p_supply_plan_id
1762 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
1763 and ass_msi.plan_id = ass_mpo.plan_id
1764 and ass_msi.organization_id = ass_mpo.organization_id
1765 /* msc_system_items - assembly and msd_level_values - assembly */
1766 and ass_mlv.instance = ass_msi.sr_instance_id
1767 and ass_mlv.level_id = 1
1768 and ass_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
1769 /* msc_system_items - assembly and msc_demands - assembly */
1770 and ass_md.inventory_item_id = ass_msi.inventory_item_id
1771 and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
1772 and ass_md.plan_id = ass_msi.plan_id
1773 and ass_md.sr_instance_id = ass_msi.sr_instance_id
1774 and ass_md.organization_id = ass_msi.organization_id
1775 /* msc_demands - assembly and msc_full_pegging - assembly */
1776 and ass_md.demand_id = ass_mfp.demand_id
1777 and ass_md.plan_id = ass_mfp.plan_id
1778 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
1779 and ass_md.organization_id = ass_mfp.organization_id
1780 /* msc_full_pegging - assembly and msc_full_pegging - component */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
1781 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
1782 and cmp_mfp.plan_id = ass_mfp.plan_id
1783 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
1784 /* msc_full_pegging - component and msc_demands - component */
1785 and cmp_mfp.demand_id = cmp_md.demand_id
1786 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
1787 and cmp_md.plan_id = cmp_msi.plan_id
1788 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
1789 and cmp_md.organization_id = cmp_msi.organization_id
1790 /* msc_system_items - assembly and msd_leve_values - assembly */
1791 and cmp_mlv.instance = ass_msi.sr_instance_id
1792 and cmp_mlv.level_id = 1
1793 and cmp_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
1794 /* msc_system_items - components and msc_demands - components */
1795 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
1796 and cmp_msi.plan_id = cmp_mpo.plan_id
1797 and cmp_msi.organization_id = cmp_mpo.organization_id
1798 and cmp_mpo.plan_id = p_supply_plan_id; -- For a given ascp plan
1799
1800
1801 cursor c_assmb_comp_smb_bom (p_supply_plan_id NUMBER ) is
1802 select /*+ ORDERED */ distinct
1803 ass_msi.sr_instance_id SR_INSTANCE_ID,
1804 ass_msi.sr_inventory_item_id SR_ASSEMBLY_PK,
1805 cmp_msi.sr_inventory_item_id SR_COMPONENT_PK
1806 from
1807 msc_plan_organizations ass_mpo,
1808 msc_system_items ass_msi,
1809 msd_level_values ass_mlv,
1810 msc_demands ass_md,
1811 msc_full_pegging ass_mfp,
1812 msc_full_pegging cmp_mfp,
1813 msc_demands cmp_md,
1814 msd_level_values cmp_mlv,
1815 msc_system_items cmp_msi,
1816 msc_plan_organizations cmp_mpo
1817 where
1818 /* msc_system_items - assembly and msc_plans - assembly */
1819 ass_mpo.plan_id = p_supply_plan_id
1820 and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
1821 and ass_msi.plan_id = ass_mpo.plan_id
1822 and ass_msi.organization_id = ass_mpo.organization_id
1823 /* msc_system_items - assembly and msd_level_values - assembly */
1824 and ass_mlv.instance = ass_msi.sr_instance_id
1825 and ass_mlv.level_id = 1
1826 and ass_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
1827 /* msc_system_items - assembly and msc_demands - assembly */
1828 and ass_md.inventory_item_id = ass_msi.inventory_item_id
1829 and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
1830 and ass_md.plan_id = ass_msi.plan_id
1831 and ass_md.sr_instance_id = ass_msi.sr_instance_id
1832 and ass_md.organization_id = ass_msi.organization_id
1833 /* msc_demands - assembly and msc_full_pegging - assembly */
1834 and ass_md.demand_id = ass_mfp.demand_id
1835 and ass_md.plan_id = ass_mfp.plan_id
1836 and ass_md.sr_instance_id = ass_mfp.sr_instance_id
1837 and ass_md.organization_id = ass_mfp.organization_id
1838 /* msc_full_pegging - assembly and msc_full_pegging - component */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
1839 and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
1840 and cmp_mfp.plan_id = ass_mfp.plan_id
1841 and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
1842 /* msc_full_pegging - component and msc_demands - component */
1843 and cmp_mfp.demand_id = cmp_md.demand_id
1844 and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
1845 and cmp_md.plan_id = cmp_msi.plan_id
1846 and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
1847 and cmp_md.organization_id = cmp_msi.organization_id
1848 /* msc_system_items - assembly and msd_leve_values - assembly */
1849 and cmp_mlv.instance = ass_msi.sr_instance_id
1850 and cmp_mlv.level_id = 1
1851 and cmp_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
1852 /* msc_system_items - components and msc_demands - components */
1853 and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
1854 and cmp_msi.plan_id = cmp_mpo.plan_id
1855 and cmp_msi.organization_id = cmp_mpo.organization_id
1856 and cmp_mpo.plan_id = p_supply_plan_id; -- For a given ascp plan
1857
1858
1859 cursor c_collapsed_bom(p_demand_plan_id NUMBER) is
1860 select distinct
1861 sup_plan_bom.sr_instance_id,
1862 sup_plan_bom.sr_assembly_pk,
1863 sup_plan_bom.sr_component_pk,
1864 sup_plan_bom.bom_type
1865 from msd_ascp_bom_comp sup_plan_bom,
1866 msc_plans sup_plan,
1867 msd_dp_scenarios dp_scen
1868 where sup_plan_bom.cap_usg_ratio_obj = sup_plan.compile_designator
1869 and sup_plan.plan_id = dp_scen.supply_plan_id
1870 and dp_scen.demand_plan_id = p_demand_plan_id
1871 and dp_scen.supply_plan_id is not null
1872 order by sup_plan_bom.sr_instance_id,sup_plan_bom.sr_assembly_pk,sup_plan_bom.sr_component_pk;
1873
1874 cursor c_supply_plan_scenarios is
1875 select scenario_name,supply_plan_name,old_supply_plan_name
1876 from msd_dp_scenarios
1877 where demand_plan_id = p_demand_plan_id
1878 and supply_plan_name is not null; -- No need to add condition of supply_plan_id > 0 because we need to
1879 -- consider scenarios with legacy loaded streams attached.
1880
1881 cursor c_if_plan_still_attached(p_supply_plan_name varchar2) is
1882 select scenario_name
1883 from msd_dp_scenarios
1884 where demand_plan_id = p_demand_plan_id
1885 and old_supply_plan_name = p_supply_plan_name
1886 AND rownum < 2;
1887
1888 cursor c_if_plan_removed_currently(p_supply_plan_name varchar2) is
1889 select scenario_name
1890 from msd_dp_scenarios
1891 where demand_plan_id = p_demand_plan_id
1892 and supply_plan_name = p_supply_plan_name
1893 AND rownum < 2;
1894
1895 v_plan_comp_date date := C_NULL_DATE;
1896 v_plan_name varchar2(30) := to_char(null);
1897 v_last_collected_date date := C_NULL_DATE;
1898 v_bom_cal BOOLEAN := FALSE;
1899
1900 lb_FetchComplete Boolean;
1901 ln_rows_to_fetch Number := nvl(TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1902
1903 TYPE CharTblTyp IS TABLE OF VARCHAR2(240);
1904 TYPE NumTblTyp IS TABLE OF NUMBER;
1905 TYPE DateTblTyp IS TABLE OF DATE;
1906
1907 lb_instance_id CharTblTyp;
1908 lb_assembly_pk CharTblTyp;
1909 lb_component_pk CharTblTyp;
1910 lb_bom_type CharTblTyp;
1911
1912 lv_new_plan_attached BOOLEAN :=FALSE;
1913 lv_removed_plan BOOLEAN :=FALSE;
1914
1915 l_scenario_name msd_dp_scenarios.scenario_name%TYPE;
1916 l_supply_plan_name msd_dp_scenarios.supply_plan_name%TYPE;
1917 l_old_supply_plan_name msd_dp_scenarios.old_supply_plan_name%TYPE;
1918
1919 l_scen_name msd_dp_scenarios.scenario_name%TYPE;
1920
1921 begin
1922
1923 LOG_MESSAGE('***********************************************************************');
1924 LOG_MESSAGE('Entered in procedure POPULATE_EOL_BOM');
1925 LOG_MESSAGE('***********************************************************************');
1926
1927 LOG_MESSAGE('The value for EOL plan ID is: '||p_demand_plan_id);
1928
1929 retcode := 0;
1930
1931 LOG_MESSAGE('***********************************************************************');
1932 LOG_MESSAGE('Entering the Loop for all Supply Plans Attached...');
1933 LOG_MESSAGE('***********************************************************************');
1934
1935 For Supply_Plans_Rec in Supply_Plans Loop
1936
1937 select compile_designator,trunc(plan_completion_date) into v_plan_name,v_plan_comp_date
1938 from msc_plans
1939 where plan_id = Supply_Plans_Rec.supply_plan_id;
1940
1941
1942 BEGIN
1943
1944 select distinct nvl(trunc(last_collected_date),C_NULL_DATE) into v_last_collected_date
1945 from msd_ascp_bom_comp
1946 where cap_usg_ratio_obj = v_plan_name
1947 and plan_type = 'EOL'
1948 and rownum < 2;
1949
1950 EXCEPTION
1951
1952 WHEN NO_DATA_FOUND THEN
1953 v_last_collected_date := C_NULL_DATE;
1954 WHEN OTHERS THEN
1955 LOG_MESSAGE('Error fetching the LAST_COLLECTED_DATE from MSD_ASCP_BOM_COMP');
1956 retcode := -1 ;
1957 errbuf := substr(SQLERRM,1,150);
1958 END;
1959
1960 LOG_MESSAGE('Supply Plans Name: '||v_plan_name);
1961 LOG_MESSAGE('Supply Plans ID: '||Supply_Plans_Rec.supply_plan_id);
1962
1963 IF ( v_plan_comp_date > v_last_collected_date ) THEN
1964
1965 LOG_MESSAGE('Entering to start the calculation for this Plan.');
1966
1967
1968 v_bom_cal := TRUE;
1969
1970 LOG_MESSAGE('Deleting the table MSD_ASCP_BOM_COMP for this Plan');
1971 delete from msd_ascp_bom_comp
1972 where cap_usg_ratio_obj = v_plan_name
1973 and plan_type = 'EOL';
1974
1975 lb_FetchComplete := FALSE;
1976
1977 LOG_MESSAGE('***********************************************************************');
1978 LOG_MESSAGE('Opening the Assembly Component cursor for Where Used Report');
1979 LOG_MESSAGE('***********************************************************************');
1980
1981 OPEN c_assmb_comp_wur_bom(Supply_Plans_Rec.supply_plan_id);
1982
1983 IF (c_assmb_comp_wur_bom%ISOPEN) THEN
1984 LOG_MESSAGE('Value of c_assmb_comp_wur_bom%ISOPEN is TRUE');
1985 ELSE
1986 LOG_MESSAGE('Value of c_assmb_comp_wur_bom%ISOPEN is FALSE');
1987 END IF;
1988
1989 IF (c_assmb_comp_wur_bom%ISOPEN) THEN
1990
1991 LOOP
1992
1993 IF (lb_FetchComplete) THEN
1994 EXIT;
1995 END IF;
1996
1997 FETCH c_assmb_comp_wur_bom BULK COLLECT INTO
1998 lb_instance_id,
1999 lb_assembly_pk,
2000 lb_component_pk
2001 LIMIT ln_rows_to_fetch;
2002
2003
2004 IF (c_assmb_comp_wur_bom%NOTFOUND) THEN
2005 lb_FetchComplete := TRUE;
2006 END IF;
2007
2008
2009 if c_assmb_comp_wur_bom%ROWCOUNT > 0 then
2010
2011 FORALL j IN lb_instance_id.FIRST..lb_instance_id.LAST
2012 INSERT INTO msd_ascp_bom_comp
2013 ( SR_INSTANCE_ID,
2014 CAP_USG_RATIO_OBJ,
2015 SR_ASSEMBLY_PK,
2016 SR_COMPONENT_PK,
2017 CAPACITY_USAGE_RATIO,
2018 LAST_COLLECTED_DATE,
2019 LAST_UPDATE_DATE,
2020 LAST_UPDATED_BY,
2021 CREATION_DATE,
2022 CREATED_BY,
2023 PLAN_TYPE,
2024 BOM_TYPE )
2025 SELECT
2026 lb_instance_id(j),
2027 v_plan_name,
2028 lb_assembly_pk(j),
2029 lb_component_pk(j),
2030 calc_eol_wur(lb_instance_id(j),
2031 Supply_Plans_Rec.supply_plan_id,
2032 lb_assembly_pk(j),
2033 lb_component_pk(j)),
2034 sysdate,
2035 sysdate,
2036 FND_GLOBAL.USER_ID,
2037 sysdate,
2038 FND_GLOBAL.USER_ID,
2039 'EOL',
2040 'WUR'
2041 FROM DUAL;
2042
2043 end if;
2044
2045 END LOOP; --LOOP
2046
2047 END IF; --IF (c_assmb_comp_wur_bom%ISOPEN) THEN
2048 CLOSE c_assmb_comp_wur_bom;
2049
2050 LOG_MESSAGE('***********************************************************************');
2051 LOG_MESSAGE('Closed the Assembly Component cursor for Where Used Report');
2052 LOG_MESSAGE('***********************************************************************');
2053
2054 --Populating Simulation BOM
2055
2056 lb_instance_id := NULL;
2057 lb_assembly_pk := NULL;
2058 lb_component_pk := NULL;
2059 lb_FetchComplete := FALSE;
2060
2061
2062 LOG_MESSAGE('***********************************************************************');
2063 LOG_MESSAGE('Opening the Assembly Component cursor for Simulation BOM');
2064 LOG_MESSAGE('***********************************************************************');
2065
2066 OPEN c_assmb_comp_smb_bom(Supply_Plans_Rec.supply_plan_id);
2067
2068 IF (c_assmb_comp_smb_bom%ISOPEN) THEN
2069 LOG_MESSAGE('Value of c_assmb_comp_smb_bom%ISOPEN is TRUE');
2070 ELSE
2071 LOG_MESSAGE('Value of c_assmb_comp_smb_bom%ISOPEN is FALSE');
2072 END IF;
2073
2074 IF (c_assmb_comp_smb_bom%ISOPEN) THEN
2075
2076 LOOP
2077
2078 IF (lb_FetchComplete) THEN
2079 EXIT;
2080 END IF;
2081
2082 FETCH c_assmb_comp_smb_bom BULK COLLECT INTO
2083 lb_instance_id,
2084 lb_assembly_pk,
2085 lb_component_pk
2086 LIMIT ln_rows_to_fetch;
2087
2088
2089 IF (c_assmb_comp_smb_bom%NOTFOUND) THEN
2090 lb_FetchComplete := TRUE;
2091 END IF;
2092
2093
2094 if c_assmb_comp_smb_bom%ROWCOUNT > 0 then
2095
2096 FORALL j IN lb_instance_id.FIRST..lb_instance_id.LAST
2097 INSERT INTO msd_ascp_bom_comp
2098 ( SR_INSTANCE_ID,
2099 CAP_USG_RATIO_OBJ,
2100 SR_ASSEMBLY_PK,
2101 SR_COMPONENT_PK,
2102 CAPACITY_USAGE_RATIO,
2103 LEAD_TIME,
2104 LAST_COLLECTED_DATE,
2105 LAST_UPDATE_DATE,
2106 LAST_UPDATED_BY,
2107 CREATION_DATE,
2108 CREATED_BY,
2109 PLAN_TYPE,
2110 BOM_TYPE )
2111 SELECT
2112 lb_instance_id(j),
2113 v_plan_name,
2114 lb_assembly_pk(j),
2115 lb_component_pk(j),
2116 calc_eol_smb(C_CU,
2117 lb_instance_id(j),
2118 Supply_Plans_Rec.supply_plan_id,
2119 lb_assembly_pk(j),
2120 lb_component_pk(j)),
2121 calc_eol_smb(C_LT,
2122 lb_instance_id(j),
2123 Supply_Plans_Rec.supply_plan_id,
2124 lb_assembly_pk(j),
2125 lb_component_pk(j)),
2126 sysdate,
2127 sysdate,
2128 FND_GLOBAL.USER_ID,
2129 sysdate,
2130 FND_GLOBAL.USER_ID,
2131 'EOL',
2132 'SMB'
2133 FROM DUAL;
2134
2135 end if;
2136
2137 END LOOP; --LOOP
2138
2139 END IF; --IF (c_assmb_comp_smb_bom%ISOPEN) THEN
2140 CLOSE c_assmb_comp_smb_bom;
2141
2142 LOG_MESSAGE('***********************************************************************');
2143 LOG_MESSAGE('Closed the Assembly Component cursor for Simulation BOM');
2144 LOG_MESSAGE('***********************************************************************');
2145
2146
2147 End IF; --IF ( v_plan_comp_date > v_last_collected_date ) THEN
2148
2149 End Loop;
2150 LOG_MESSAGE('***********************************************************************');
2151 LOG_MESSAGE('Exiting the Loop for all Supply Plans Attached');
2152 LOG_MESSAGE('***********************************************************************');
2153
2154
2155 --Determining the Flags, so as to rec-populate the Collapsed BOM
2156 -- only if there is no calculation of Capacity Usage Ratios and Lead Times
2157 -- in which case, Collapsed BOM will always be populated.
2158 IF ( v_bom_cal = FALSE ) THEN
2159
2160 LOG_MESSAGE('***********************************************************************');
2161 LOG_MESSAGE('Entering - to evaluate flags for Collapsed BOM');
2162 LOG_MESSAGE('***********************************************************************');
2163
2164 OPEN c_supply_plan_scenarios;
2165 LOOP
2166 FETCH c_supply_plan_scenarios INTO l_scenario_name,
2167 l_supply_plan_name,
2168 l_old_supply_plan_name;
2169
2170 EXIT WHEN c_supply_plan_scenarios%NOTFOUND;
2171
2172 IF l_supply_plan_name <> nvl(l_old_supply_plan_name,'-999') THEN
2173
2174
2175 --Setting the flag to recalculate the collapsed BOM if,
2176 --any of the new ASCP Plans have been attached
2177 OPEN c_if_plan_still_attached(l_supply_plan_name);
2178 FETCH c_if_plan_still_attached INTO l_scen_name;
2179
2180 IF c_if_plan_still_attached%NOTFOUND THEN
2181 lv_new_plan_attached := TRUE;
2182
2183 LOG_MESSAGE('Setting the Flag as New Supply Plan Attached');
2184 LOG_MESSAGE('Name of the New Supply Plan Attached: '||l_supply_plan_name);
2185
2186 END IF;
2187
2188 CLOSE c_if_plan_still_attached;
2189
2190 EXIT WHEN lv_new_plan_attached;
2191
2192
2193 --Setting the flag to recalculate the collapsed BOM if,
2194 --any of the ASCP Plans attached earlier but are not attcahed currently.
2195 OPEN c_if_plan_removed_currently(l_old_supply_plan_name); -- or OPEN c_if_plan_removed_currently(nvl(l_old_supply_plan_name,'-999')); ??
2196 FETCH c_if_plan_removed_currently INTO l_scen_name;
2197
2198 IF c_if_plan_removed_currently%NOTFOUND THEN
2199 lv_removed_plan := TRUE;
2200
2201 LOG_MESSAGE('Setting the Flag as Supply Plan has been Removed');
2202 LOG_MESSAGE('Name of the Existing Supply Plan Removed: '||l_old_supply_plan_name);
2203
2204 END IF;
2205
2206 CLOSE c_if_plan_removed_currently;
2207
2208
2209
2210 END IF;
2211
2212 EXIT WHEN lv_removed_plan;
2213
2214 END LOOP;
2215 CLOSE c_supply_plan_scenarios;
2216
2217 LOG_MESSAGE('***********************************************************************');
2218 LOG_MESSAGE('Exiting - to evaluate flags for Collapsed BOM');
2219 LOG_MESSAGE('***********************************************************************');
2220
2221 END IF;
2222
2223 IF lv_new_plan_attached THEN
2224 LOG_MESSAGE(' lv_new_plan_attached is true');
2225 ELSE
2226 LOG_MESSAGE(' lv_new_plan_attached is false');
2227 END IF;
2228
2229 IF lv_removed_plan THEN
2230 LOG_MESSAGE(' lv_removed_plan is true');
2231 ELSE
2232 LOG_MESSAGE(' lv_removed_plan is false');
2233 END IF;
2234
2235 -- Deleting the Collapsed BOM table and populating the same.
2236 IF v_bom_cal OR lv_new_plan_attached OR lv_removed_plan THEN
2237
2238 LOG_MESSAGE('***********************************************************************');
2239 LOG_MESSAGE('Entering - to evaluate the Collapsed BOM');
2240 LOG_MESSAGE('***********************************************************************');
2241
2242
2243 LOG_MESSAGE('Deleting the Collapsed BOM Data from table MSD_SOP_COLLAPESD_BOM_COMP');
2244 delete msd_sop_collapsed_bom_comp
2245 where demand_plan_id = p_demand_plan_id;
2246
2247
2248 lb_instance_id := NULL;
2249 lb_assembly_pk := NULL;
2250 lb_component_pk := NULL;
2251 lb_bom_type := NULL;
2252 lb_FetchComplete := FALSE;
2253
2254 LOG_MESSAGE('Opening the Collapsed BOM Cursor');
2255 OPEN c_collapsed_bom(p_demand_plan_id);
2256 IF (c_collapsed_bom%ISOPEN) THEN
2257
2258 LOOP
2259
2260 IF (lb_FetchComplete) THEN
2261 EXIT;
2262 END IF;
2263
2264 FETCH c_collapsed_bom BULK COLLECT INTO
2265 lb_instance_id,
2266 lb_assembly_pk,
2267 lb_component_pk,
2268 lb_bom_type
2269 LIMIT ln_rows_to_fetch;
2270
2271
2272 IF (c_collapsed_bom%NOTFOUND) THEN
2273 lb_FetchComplete := TRUE;
2274 END IF;
2275
2276
2277 if c_collapsed_bom%ROWCOUNT > 0 then
2278
2279 FORALL j IN lb_instance_id.FIRST..lb_instance_id.LAST
2280 INSERT INTO msd_sop_collapsed_bom_comp
2281 ( SR_INSTANCE_ID,
2282 DEMAND_PLAN_ID,
2283 SR_ASSEMBLY_PK,
2284 SR_COMPONENT_PK,
2285 LAST_UPDATE_DATE,
2286 LAST_UPDATED_BY,
2287 CREATION_DATE,
2288 CREATED_BY,
2289 PLAN_TYPE,
2290 BOM_TYPE )
2291 VALUES
2292 ( lb_instance_id(j),
2293 p_demand_plan_id,
2294 lb_assembly_pk(j),
2295 lb_component_pk(j),
2296 sysdate,
2297 FND_GLOBAL.USER_ID,
2298 sysdate,
2299 FND_GLOBAL.USER_ID,
2300 'EOL',
2301 lb_bom_type(j) );
2302
2303 end if;
2304
2305 END LOOP; --LOOP
2306
2307 END IF; --IF (c_collapsed_bom%ISOPEN) THEN
2308 CLOSE c_collapsed_bom;
2309 LOG_MESSAGE('Closed the Collapsed BOM Cursor');
2310
2311 LOG_MESSAGE('Updating the Old Supply Plan Columns');
2312 -- Populating the Old_Supply_Plan_Name and Old_Supply_Plan_ID
2313 -- to track for the the next run if we need to re-populate the collapsed bom table for
2314 -- this plan or not.
2315 update msd_dp_scenarios
2316 set old_supply_plan_id = supply_plan_id,
2317 old_supply_plan_name = supply_plan_name
2318 where demand_plan_id = p_demand_plan_id
2319 and supply_plan_name is not null;
2320
2321
2322 LOG_MESSAGE('***********************************************************************');
2323 LOG_MESSAGE('Exiting - to evaluate the Collapsed BOM');
2324 LOG_MESSAGE('***********************************************************************');
2325
2326 END IF; --IF v_bom_cal OR lv_new_plan_attached OR lv_removed_plan THEN
2327
2328
2329 -- Final Commit;
2330 commit;
2331
2332 LOG_MESSAGE('***********************************************************************');
2333 LOG_MESSAGE('Exiting from the procedure POPULATE_BOM Sucessfully');
2334 LOG_MESSAGE('***********************************************************************');
2335
2336 exception
2337 when others then
2338 retcode := -1 ;
2339 errbuf := substr(SQLERRM,1,150);
2340
2341
2342 end populate_eol_bom;
2343
2344
2345 procedure msd_dp_pre_download_hook( errbuf OUT NOCOPY VARCHAR2,
2346 retcode OUT NOCOPY NUMBER,
2347 p_demand_plan_id IN NUMBER )
2348 is
2349 v_plan_type NUMBER;
2350 begin
2351 retcode := 0;
2352
2353 select decode(nvl(plan_type,C_DP),'SOP',C_SOP,'EOL',C_EOL,10) into v_plan_type
2354 from msd_demand_plans
2355 where demand_plan_id = p_demand_plan_id;
2356
2357 IF ( v_plan_type = C_SOP ) THEN
2358
2359 LOG_MESSAGE('Calling procedure - populate_bom');
2360
2361 populate_bom (errbuf => errbuf,
2362 retcode => retcode,
2363 p_demand_plan_id => p_demand_plan_id);
2364
2365 if nvl(retcode,'0') <> '0' then
2366 v_retcode := retcode;
2367 else
2368 LOG_MESSAGE('Sucessfully completed procedure - populate_bom');
2369 end if;
2370
2371 ELSIF ( v_plan_type = C_EOL ) THEN
2372
2373 LOG_MESSAGE('Calling procedure - msd_eol_plan.msd_eol_pre_download_hook');
2374
2375 msd_eol_plan.msd_eol_pre_download_hook(p_demand_plan_id);
2376
2377 LOG_MESSAGE('Completed procedure - msd_eol_plan.msd_eol_pre_download_hook');
2378
2379 LOG_MESSAGE('Calling procedure - populate_eol_bom');
2380
2381 populate_eol_bom (errbuf => errbuf,
2382 retcode => retcode,
2383 p_demand_plan_id => p_demand_plan_id);
2384
2385 if nvl(retcode,'0') <> '0' then
2386 v_retcode := retcode;
2387 else
2388 LOG_MESSAGE('Sucessfully completed procedure - populate_eol_bom');
2389 end if;
2390
2391 ELSE
2392
2393 null;
2394
2395 END IF;
2396
2397
2398
2399 retcode := v_retcode;
2400
2401 exception
2402 when others then
2403 retcode := -1 ;
2404 errbuf := substr(SQLERRM,1,150);
2405
2406 end msd_dp_pre_download_hook;
2407
2408
2409 END MSD_SALES_OPERATION_PLAN ;
[Home] [Help]