[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 ' ||
1023: sysdate,
1024: fnd_global.user_id,
1025: sysdate
1026: FROM
1027: mtl_kanban_pull_sequences ps,
1028: bom_bill_of_materials bbom,
1029: mtl_item_categories mic,
1030: bom_inventory_components mrp_bic,
1031: mrp_low_level_codes mllc
1108: last_updated_by,
1109: last_update_date,
1110: created_by,
1111: creation_date)
1112: SELECT /*+ INDEX(PS MTL_KANBAN_PULL_SEQUENCES_N1) */ DISTINCT
1113: mllc.plan_id,
1114: mllc.organization_id,
1115: bbom.assembly_item_id,
1116: ps.subinventory_name,
1140: sysdate,
1141: fnd_global.user_id,
1142: sysdate
1143: FROM
1144: mtl_kanban_pull_sequences ps,
1145: bom_bill_of_materials bbom,
1146: mtl_item_categories mic,
1147: bom_inventory_components mrp_bic,
1148: mrp_low_level_codes mllc
1236:
1237:
1238: -- The purpose of this statment is to improve the performance
1239: -- The above insert stmt has performance problems and
1240: -- to avoid two outer join in mtl_kanban_pull_sequences
1241: -- we decide to break it down.
1242: UPDATE mrp_low_level_codes mllc
1243: SET (mllc.kanban_item_flag) =
1244: (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
1241: -- we decide to break it down.
1242: UPDATE mrp_low_level_codes mllc
1243: SET (mllc.kanban_item_flag) =
1244: (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
1245: from mtl_kanban_pull_sequences kbn_items
1246: where kbn_items.kanban_plan_id =
1247: mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1248: and kbn_items.organization_id =
1249: mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1395: fnd_global.user_id,
1396: sysdate
1397: FROM
1398: mtl_item_categories mic,
1399: mtl_kanban_pull_sequences kbn_items,
1400: mrp_low_level_codes mllc1
1401: WHERE
1402: mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id AND
1403: mllc1.organization_id =
1427: MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1428: END IF;
1429:
1430: -- ------------------------------------------------------------------------
1431: -- Now find information in mtl_kanban_pull_sequences about inter-org and
1432: -- intra-org transfers and insert into mrp_low_level_codes
1433: -- Note here that replan flag drives whether I pull infomation from the
1434: -- production kanban plan or the current kanban plan itself. Replan_flag
1435: -- = 2 is not a replan and if its 1 then its a replan run.
1484: fnd_global.user_id,
1485: sysdate,
1486: fnd_global.user_id,
1487: sysdate
1488: FROM mtl_kanban_pull_sequences ps,
1489: mrp_low_level_codes mllc
1490: WHERE ps.source_type = 3 -- only intra org replenishments
1491: AND ps.kanban_plan_id =
1492: decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,