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