[Home] [Help]
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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,
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
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
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 */
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
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
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
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
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
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
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
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
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 */
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
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
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
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
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
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
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
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
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
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,
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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,
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
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
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
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
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,
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
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
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 */
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
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,
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
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
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 */
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
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,
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
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
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 */
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