[Home] [Help]
738: -- the inventory item id from mtl_system_items , we also get location
739: -- information for this item by joining to bom_inventory_components. We
740: -- are not satisfied with that. So we join bom_bill_of_materials
741: -- to get the assembly_item_id and its location information which is
742: -- (if its a kanban item) stored (hopefully) in mtl_kanban_pull_sequences
743: -- table. The catch here is that for a production kind of source type in
744: -- in the replenishment chain we specify a line for the source. We want
745: -- this to be the line that's specified in the CFM routing with priority = 1
746: -- so we end up joining bom_operational_routings table also.
820: 'FROM ' ||
821: 'mrp_kanban_plans mkp, ' ||
822: 'mtl_system_items msi, ' ||
823: '( SELECT /*+ no_merge */ distinct inventory_item_id ,organization_id ' ||
824: ' FROM mtl_kanban_pull_sequences ' ||
825: ' WHERE kanban_plan_id = :b_PRODUCTION_KANBAN ) iv, ' ||
826: 'bom_inventory_components mrp_bic, ' ||
827: 'bom_bill_of_materials bbom, ' ||
828: 'mtl_kanban_pull_sequences ps, ' ||
824: ' FROM mtl_kanban_pull_sequences ' ||
825: ' WHERE kanban_plan_id = :b_PRODUCTION_KANBAN ) iv, ' ||
826: 'bom_inventory_components mrp_bic, ' ||
827: 'bom_bill_of_materials bbom, ' ||
828: 'mtl_kanban_pull_sequences ps, ' ||
829: l_additional_tables ||
830: 'mtl_system_items msi2 ' ||
831: 'WHERE mkp.kanban_plan_id = :b_kanban_plan_id ' ||
832: 'AND mkp.organization_id = :b_organization_id ' ||
1022: sysdate,
1023: fnd_global.user_id,
1024: sysdate
1025: FROM
1026: mtl_kanban_pull_sequences ps,
1027: bom_bill_of_materials bbom,
1028: mtl_item_categories mic,
1029: bom_inventory_components mrp_bic,
1030: mrp_low_level_codes mllc
1107: last_updated_by,
1108: last_update_date,
1109: created_by,
1110: creation_date)
1111: SELECT /*+ INDEX(PS MTL_KANBAN_PULL_SEQUENCES_N1) */ DISTINCT
1112: mllc.plan_id,
1113: mllc.organization_id,
1114: bbom.assembly_item_id,
1115: ps.subinventory_name,
1139: sysdate,
1140: fnd_global.user_id,
1141: sysdate
1142: FROM
1143: mtl_kanban_pull_sequences ps,
1144: bom_bill_of_materials bbom,
1145: mtl_item_categories mic,
1146: bom_inventory_components mrp_bic,
1147: mrp_low_level_codes mllc
1235:
1236:
1237: -- The purpose of this statment is to improve the performance
1238: -- The above insert stmt has performance problems and
1239: -- to avoid two outer join in mtl_kanban_pull_sequences
1240: -- we decide to break it down.
1241: UPDATE mrp_low_level_codes mllc
1242: SET (mllc.kanban_item_flag) =
1243: (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
1240: -- we decide to break it down.
1241: UPDATE mrp_low_level_codes mllc
1242: SET (mllc.kanban_item_flag) =
1243: (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
1244: from mtl_kanban_pull_sequences kbn_items
1245: where kbn_items.kanban_plan_id =
1246: mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1247: and kbn_items.organization_id =
1248: mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1394: fnd_global.user_id,
1395: sysdate
1396: FROM
1397: mtl_item_categories mic,
1398: mtl_kanban_pull_sequences kbn_items,
1399: mrp_low_level_codes mllc1
1400: WHERE
1401: mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id AND
1402: mllc1.organization_id =
1426: MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1427: END IF;
1428:
1429: -- ------------------------------------------------------------------------
1430: -- Now find information in mtl_kanban_pull_sequences about inter-org and
1431: -- intra-org transfers and insert into mrp_low_level_codes
1432: -- Note here that replan flag drives whether I pull infomation from the
1433: -- production kanban plan or the current kanban plan itself. Replan_flag
1434: -- = 2 is not a replan and if its 1 then its a replan run.
1483: fnd_global.user_id,
1484: sysdate,
1485: fnd_global.user_id,
1486: sysdate
1487: FROM mtl_kanban_pull_sequences ps,
1488: mrp_low_level_codes mllc
1489: WHERE ps.source_type = 3 -- only intra org replenishments
1490: AND ps.kanban_plan_id =
1491: decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,