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