Material Workbench Query Logic
What's main code logic while performing query in material workbench form(INVMWBIV.fmb).
INVMWBIV.fmb
INVMWQMB.pls - INV_MWB_QUERY_MANAGER
INVMWCTB.pls - INV_MWB_CONTROLLER
Key Table:
MTL_MWB_GTMP
mtl_onhand_quantities_detail
=====================
First Delete All records from MTL_MWB_GTMP.
DELETE FROM MTL_MWB_GTMP(Happen at INV_MWB_CONTROLLER.initialize)
INV_MWB_CONTROLLER.Event
|-INV_MWB_CONTROLLER.initialize
=====================
INV_MWB_CONTROLLER.initialize Call inv_mwb_query_manager.initialize_xxx procedure to do initialization.
inv_mwb_query_manager.initialize_union_query;
inv_mwb_query_manager.initialize_onhand_query; --Build MOQD select statement
inv_mwb_query_manager.initialize_inbound_query;
inv_mwb_query_manager.initialize_receiving_query;
=====================
Then retrieve records from mtl_onhand_quantities_detail to insert mtl_mwb_gtmp. Where clause depend on user's condition on query find form.
INSERT INTO mtl_mwb_gtmp (PO_RELEASE_ID,
RELEASE_LINE_NUMBER,
.....
SECONDARY_ONHAND,
SECONDARY_RECEIVING,
SECONDARY_INBOUND,
GRADE_CODE,
OWNING_ORGANIZATION_ID,
PLANNING_ORGANIZATION_ID,
OWNING_TP_TYPE,
PLANNING_TP_TYPE)
SELECT NULL PO_RELEASE_ID,
NULL RELEASE_LINE_NUMBER,
...
NULL SECONDARY_INBOUND,
NULL GRADE_CODE,
moqd.owning_organization_id OWNING_ORGANIZATION_ID,
moqd.planning_organization_id PLANNING_ORGANIZATION_ID,
moqd.owning_tp_type OWNING_TP_TYPE,
moqd.planning_tp_type PLANNING_TP_TYPE
FROM mtl_onhand_quantities_detail moqd
WHERE 1 = 1
AND moqd.lpn_id IS NULL
AND moqd.locator_id = :onh_tree_loc_id
AND moqd.subinventory_code = :onh_tree_sub_code
AND moqd.inventory_item_id = :onh_tree_inventory_item_id
AND moqd.organization_id = :onh_tree_organization_id
AND moqd.organization_id = :onh_organization_id
AND moqd.inventory_item_id = :onh_inventory_item_id
GROUP BY moqd.subinventory_code,
moqd.locator_id,
moqd.lpn_id,
moqd.cost_group_id,
moqd.organization_id,
moqd.inventory_item_id,
moqd.secondary_uom_code,
moqd.owning_organization_id,
moqd.planning_organization_id,
moqd.owning_tp_type,
moqd.planning_tp_type
=====================
Then Query from MTL_MWB_GTMP
SELECT MATURITY_DATE,
HOLD_DATE,
SUPPLIER_LOT,
PARENT_LOT,
....
PLANNING_PARTY,
PLANNING_PARTY_ID,
OWNING_PARTY,
OWNING_PARTY_ID,
OWNING_ORGANIZATION_ID,
PLANNING_ORGANIZATION_ID,
PLANNING_TP_TYPE,
OWNING_TP_TYPE,
PROJECT_ID,
TASK_ID
FROM MTL_MWB_GTMP
======================================
Update MTL_MWB_GTMP (Below update happen at INV_MWB_QUERY_MANAGER.post_query)
UPDATE MTL_MWB_GTMP SET ORGANIZATION_CODE = :B1
...
UPDATE MTL_MWB_GTMP
SET COST_GROUP =
(SELECT DISTINCT COST_GROUP
FROM CST_COST_GROUPS
WHERE COST_GROUP_ID = :B1)
WHERE CG_ID = :B1
...
UPDATE MTL_MWB_GTMP
SET (ITEM,ITEM_DESCRIPTION,PRIMARY_UOM_CODE) =
(SELECT :B3, DESCRIPTION, PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_VL
WHERE INVENTORY_ITEM_ID = :B1 AND ORGANIZATION_ID = :B2)
WHERE ITEM_ID = :B1
...
UPDATE MTL_MWB_GTMP
SET LOCATOR =
(SELECT CONCATENATED_SEGMENTS
FROM MTL_ITEM_LOCATIONS_KFV
WHERE INVENTORY_LOCATION_ID = :B1)
WHERE LOCATOR_ID = :B1
...
UPDATE MTL_MWB_GTMP
SET SECONDARY_ONHAND = NULL,
SECONDARY_UNPACKED = NULL,
SECONDARY_PACKED = NULL,
SECONDARY_UOM_CODE = NULL
WHERE ORG_ID = :B2 AND ITEM_ID = :B1
...
=====================================
SELECT SUM(INBOUND) ,SUM(ONHAND) ,SUM(RECEIVING) FROM MTL_MWB_GTMP
转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7712755
======EOF======
