[Home] [Help]
14: -- declare a cursor for detailed debug information. This will be used
15: -- only if debug is turned on.
16: CURSOR cur_debug is
17: SELECT distinct msi.concatenated_segments
18: FROM mtl_system_items_kfv msi, mrp_low_level_codes mllc
19: WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
20: AND mllc.organization_id =
21: mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
22: AND mllc.from_subinventory IS NULL
97: UPDATE mrp_low_level_codes mllc
98: SET (mllc.from_subinventory, mllc.from_locator_id) =
99: (SELECT msi.wip_supply_subinventory,
100: msi.wip_supply_locator_id
101: FROM mtl_system_items msi
102: WHERE msi.organization_id = mllc.organization_id
103: AND msi.inventory_item_id = mllc.component_item_id)
104: WHERE mllc.plan_id =
105: mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
207: child_loc.inventory_location_id from_location
208: FROM
209: mtl_item_locations parent_loc,
210: mtl_item_locations child_loc,
211: mtl_system_items_kfv parent,
212: mtl_system_items_kfv child,
213: mrp_low_level_codes mllc
214: WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
215: AND mllc.organization_id =
208: FROM
209: mtl_item_locations parent_loc,
210: mtl_item_locations child_loc,
211: mtl_system_items_kfv parent,
212: mtl_system_items_kfv child,
213: mrp_low_level_codes mllc
214: WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
215: AND mllc.organization_id =
216: mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
531: CURSOR config_item_flag(p_assemply_item_id IN NUMBER,
532: p_organization_id IN NUMBER) IS
533:
534: SELECT msi.base_item_id,msi.bom_item_type
535: FROM mtl_system_items msi
536: WHERE msi.inventory_item_id = p_assembly_item_id
537: AND msi.organization_id = p_organization_id;
538:
539: config_item_flag_rec config_item_flag%ROWTYPE;
543: CURSOR comp_type(p_comp_item_id IN NUMBER,
544: p_organization_id IN NUMBER) IS
545:
546: SELECT msi.bom_item_type
547: FROM mtl_system_items msi
548: WHERE msi.inventory_item_id = p_comp_item_id
549: AND msi.organization_id = p_organization_id;
550:
551: comp_type_rec comp_type%ROWTYPE;
730: -- mrp_low_codes table
731:
732: -- Probably this sql statement needs a little explanation. We are driving
733: -- off of mrp_kanban_plans table where we get the organization id in which
734: -- this plan is defined, then we hit mtl_system_items table to get the
735: -- list of kanban items (here if category set or item/category range is
736: -- is specified, then we impose extra where conditions and join a couple
737: -- of more tables as seen in the sql statement we just built). As we get
738: -- the inventory item id from mtl_system_items , we also get location
734: -- this plan is defined, then we hit mtl_system_items table to get the
735: -- list of kanban items (here if category set or item/category range is
736: -- is specified, then we impose extra where conditions and join a couple
737: -- of more tables as seen in the sql statement we just built). As we get
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
818: 'fnd_global.user_id,' ||
819: 'sysdate ' ||
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, ' ||
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 ' ||
833: 'AND msi.organization_id = mkp.organization_id ' ||
834: 'AND iv.inventory_item_id= msi.inventory_item_id ' ||
1071: mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1072: AND mllc2.component_item_id = mrp_bic.component_item_id )
1073: AND EXISTS(
1074: SELECT /*+no_unnest*/ 1
1075: FROM mtl_system_items msi
1076: WHERE msi.organization_id = bbom.organization_id
1077: AND msi.inventory_item_id = bbom.assembly_item_id
1078: AND msi.planning_make_buy_code = 1);
1079: else
1218: mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1219: AND mllc2.component_item_id = mrp_bic.component_item_id )
1220: AND EXISTS(
1221: SELECT /*+no_unnest*/ 1
1222: FROM mtl_system_items msi
1223: WHERE msi.organization_id = bbom.organization_id
1224: AND msi.inventory_item_id = bbom.assembly_item_id
1225: AND msi.planning_make_buy_code = 1);
1226: