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