BOIs support importing from M3 by source

When source value is passed as input parameter to the M3 import program:

• #SOURCE# keywords in the BOI are replaced by the parameter value.
• If <REF_TABLE> tag is used in the BOI, a WHERE condition on the source column is appended at run time: WHERE source = '#SOURCE#'.
• If <REF_QUERY> tag is used in the BOI, a WHERE condition on the source column is added to the query at run time, if it does not already exist: SELECT * FROM <REF_QUERY> WHERE source = '#SOURCE#’.
• Existing data in the destination staging table is deleted by source, that is WHERE source = source parameter value. An existing data for previous global imports where source = "M3:<M3 company>" is also deleted from the destination staging table.
• The source columns of the imported records are populated using the same parameter value.

When source value is not passed or when the field with no value is passed as input parameter to the M3 import program:

• #SOURCE# keywords in the BOI are replaced by the default value, that is M3:<M3 company>.
• All existing data in the destination staging table is deleted.
• The source columns of the imported records are populated using the same default value M3:<M3 company>.

In SCP 2022.07, the definitions of the standard BOIs delivered as part of the M3 integration has remain unchanged.

In SCP 2022.10, the standard BOI definitions have been updated to allow importing from M3 by source. The solution uses warehouse groups in M3. When importing from M3 by source, a warehouse group must exist in M3 corresponding to each 'source' value used. In the standard M3 integration, the warehouse group definitions are imported from M3, where the warehouses in each warehouse group are listed. This list is used in the subsequent calls to M3 to filter the imported data by warehouse; only the planning data for the warehouses within the specified warehouse group is imported. Hence when importing from M3 by source, it is important to retrieve first the warehouse group definitions. Warehouse groups are defined in M3 through MMS009 session.

When importing from M3 by source, existing data in the destination staging table are deleted by source, that is WHERE source = source parameter value. An existing data for previous global imports where source = "M3:<M3 company>' is also deleted from the destination staging table.

In addition, the source columns of the imported records are populated using the "-SOURCE" parameter value. The source column has been used in several standard Staging -> Inbox transformation views to extract the M3 company number. Since the source column may not always include the M3 company number, these transformation views have been updated to the retrieve the M3 company number elsewhere.

The standard macros delivered as part of the M3 integration remained unchanged. The "-SOURCE" parameter is not passed to the M3 Import program in the macro calls. As a result, the import behavior remained unchanged in SCP 2022.10.

These M3 BOI definitions have been updated to use the #SOURCE# keyword:

• CRS008MI_ListFacility_ST_M3_IN_FACILITY
• CRS055MI_SelExchangeRate_ST_M3_IN_CRS055MI_SelExchangeRate
• DPS170MI_Select_ST_M3_IN_DPS170MI_Select
• MMS005MI_LstWarehouses_ST_M3_IN_WAREHOUSE
• MMS005MI_Select_ST_M3_IN_WAREHOUSE_WHGR
• MMS010MI_LstByLocType_ST_M3_IN_STOCK_LOCATION
• MMS060MI_LstBalID_ST_M3_IN_INVENTORY_SLB
• MMS080MI_LstPOHBalance_ST_M3_IN_INVENTORY_EXPIRY_PREREQ
• MMS080MI_LstPOHBalance_ST_M3_IN_INVENTORY_POH
• MMS080MI_SelForcTrans_ST_M3_IN_CONSUMED_FORECAST
• MMS080MI_SelItemWarehous_ST_M3_IN_INVENTORY_OHB
• MMS080MI_SelItemWarehous_ST_M3_IN_ITEM_WAREHOUSE
• MMS080MI_SelMtrlTrans_ST_M3_IN_MATERIAL_PLAN_TRX
• MMS080MI_SelPOHBalance_ST_M3_IN_INVENTORY_POH_APS_WHGR
• MMS100MI_GetHead_ST_M3_IN_DISTRIBUTION_ORDER
• MMS200MI_GetItmFac_ST_M3_IN_MMS200MI_GetItmFac
• MMS235MI_LstItmLot_ST_M3_IN_INVENTORY_LOT
• MNS100MI_GetBasicData_ST_M3_IN_DIVISION_DETAIL
• MNS100MI_LstDivisions_ST_M3_IN_DIVISION
• PDS001MI_SelEndProd_ST_M3_IN_PRODUCT_END_PRODUCT
• PDS001MI_Select_ST_M3_IN_PRODUCT
• PDS002MI_SelMatByHead_ST_M3_IN_PRODUCT_MATERIAL
• PDS002MI_SelOpeByHead_ST_M3_IN_PRODUCT_OPERATION
• PDS010MI_LstDailyCap_ST_M3_IN_RESOURCE_CAPACITY
• PDS010MI_SelRelations_ST_M3_IN_PDS010MI_SelRelations
• PDS010MI_SelShiftPatAdj_ ST_M3_IN_PDS010MI_SelShiftPatAdj
• PDS010MI_SelShiftinfo_ ST_M3_IN_PDS010MI_SelShiftinfo
• PDS010MI_Select_ST_M3_IN_PDS010MI_Select_ResUnit
• PDS010MI_Select_ST_M3_IN_PDS010MI_Select_Resource
• PDS016MI_LstDailyCap_ST_M3_IN_CAL_CAPACITY_PERIOD
• PDS016MI_LstDailyCap_ST_M3_IN_CAL_UNIT_CAPACITY_PERIOD
• PDS023MI_LstAltStructure_ST_M3_IN_PDS023MI_LstAltStructure
• PDS030MI_SelModelLines _ST_M3_IN_PDS030MI_SelModelLines
• PDS030MI_SelPatternLines_ST_M3_IN_PDS030MI_SelPatternLines
• PMS100MI_SelMatByHead_ST_M3_IN_MANUFACTURING_MATERIAL
• PMS100MI_SelOpeByHead_ST_M3_IN_MANUFACTURING_OPERATION
• PMS100MI_SelOrderHead_ST_M3_IN_MANUFACTURING_ORDER
• PMS170MI_SelMatByHead_ST_M3_IN_MAN_PLND_MATERIAL
• PMS170MI_SelOpeByHead_ST_M3_IN_MAN_PLND_OPERATION
• PMS170MI_SelOrderHead_ST_M3_IN_MAN_PLND_ORDER

These M3 BOI definitions have been updated to retrieve the M3 company number elsewhere:

• CRS140MI_LstDistrict_ST_M3_IN_CRS140MI_LstDistrict: CONO is passed as input and also stored in the return results
• MMS200MI_LstItmAltUnitMs_ST_M3_IN_ITEM_ALT_UNIT: CONO is saved in the return results
• MMS200MI_LstItmByChgDate_ST_M3_IN_MMS200MI_LstItmByChgDate: CONO is saved in the return results

These M3 Staging -> Inbox transformation views have been updated to retrieve the M3 company number (CONO) elsewhere:

• ST_V_M3_BUSINESS_AREA
• ST_V_M3_COMMISSION_GROUP
• ST_V_M3_CUSTOMER_FORECAST
• ST_V_M3_CUSTOMER_GROUP
• ST_V_M3_CUSTOMER_SHIPTO_FORECAST
• ST_V_M3_DELIVERY_METHOD
• ST_V_M3_DISCOUNT_GROUP
• ST_V_M3_DISTRIBUTION_RELATION
• ST_V_M3_DISTRIBUTION_RELATION_IN
• ST_V_M3_DISTRIBUTION_RELATION_OUT
• ST_V_M3_ITEM
• ST_V_M3_ITEM_CUSTOMER_ASSM
• ST_V_M3_ITEM_CUSTOMER_ASSM_BUSCHAIN
• ST_V_M3_ITEM_CUSTOMER_ASSM_BUSCHAIN_CURRENT
• ST_V_M3_ITEM_CUSTOMER_ASSM_CURRENT
• ST_V_M3_ITEM_CUSTOMER_NON_ASSM
• ST_V_M3_ITEM_GROUP
• ST_V_M3_ITEM_SUPPLIER
• ST_V_M3_ITEM_TYPE
• ST_V_M3_ITEM_UOM_RATE
• ST_V_M3_LOCATION_GROUP
• ST_V_M3_PRODUCT_GROUP
• ST_V_M3_PRODUCT_LINE
• ST_V_M3_PURCHASE_PRICE
• ST_V_M3_REGION
• ST_V_M3_SALES_PERSON
• ST_V_M3_SUPPLIER
• ST_V_M3_SUPPLIER_DAY_CAPACITY
• ST_V_M3_USER
• ST_V_M3_CMS050MI_CreateHeader
• ST_V_M3_CMS050MI_CreateLine
• ST_V_M3_CMS050MI_Implement
• ST_V_M3_SUPPLY_PLANNED_POST_TRIGGER
• ST_V_M3_SUPPLY_PLANNED_PRE_BUILD
• ST_V_M3_SUPPLY_POST_TRIGGER
• ST_V_M3_SUPPLY_PROD_PLAN
• ST_V_M3_SUPPLY_PRODUCTION_ORDERS_NEW
• ST_V_M3_SUPPLY_PRODUCTION_PLANNED
• ST_V_M3_SUPPLY_PROD_PLAN_MO_CRT
• ST_V_M3_SUPPLY_PURCHASE_PLAN
• ST_V_M3_SUPPLY_PURCHASE_PLANNED
• ST_V_M3_SUPPLY_TRANSFER_PLAN
• ST_V_M3_SUPPLY_TRANSFER_PLANNED

These SCV entities for M3 staging tables and the corresponding SCV datasheets on these staging tables have been updated to include M3 company number (CONO):

• ITEM_ALT_UNIT
• MMS200MI_LstItmByChgDate
• OIS040MI_LstBusChain
• OIS040MI_LstBusChainCust

In addition, all SCV datasheets on M3 staging tables, both for import and export, have been reviewed and if required the 'source' column is added to the datasheet definitions.