MRP Sourcing Rule Upload

➜ Back to Library
XMLXML import file

Description

MRP Sourcing Rule Upload creates, updates and deletes Oracle sourcing rules and bills of distribution from Excel, including their receiving organizations and shipping sources (Buy From, Transfer From, Make At) with allocation percentages and ranks. Each row of the Excel file is one shipping source; rows with the same rule name form one sourcing rule.

When to use it

  • Mass-create sourcing rules or bills of distribution for many organizations.
  • Model multi-source supply with allocation percentage splits and ranks across suppliers or source organizations.
  • Bulk-update rule descriptions, effective dates, allocation percentages, ranks, ship methods or intransit times.
  • Add a shipping source to existing rules, remove one, or delete obsolete rules.
  • Copy sourcing rules between environments by downloading from one and uploading to another.

Before you start

  • Your responsibility has access to the inventory organizations of the rules.
  • Suppliers and purchasing supplier sites, and for Transfer From sources the inter-organization shipping networks and ship methods, are set up.
  • Planning uses a sourcing rule only once it is assigned in an assignment set, for example with the MRP Sourcing Rule Assignment Upload.

Step 1 – Set the parameters

Open MRP Sourcing Rule Upload in Blitz Report and set the parameters:

ParameterMeaning
Upload ModeCreate (default) opens an empty sheet for new rules; Create, Update downloads existing rules for editing or deleting and lets you add new ones.
Sourcing Rule TypeSourcing Rule (default) or Bill of Distribution. Downloads rules of this type and is the default Rule Type of new rows.
Include Global Sourcing RulesYes (default) also downloads rules that apply to all organizations; No downloads only rules of the Sourcing Rule Organization. Bills of distribution are always global.
Sourcing Rule OrganizationDownloads the sourcing rules of this inventory organization. Defaults to your current organization for the Sourcing Rule type.
Sourcing Rule NameThe rule(s) to download.
Sourcing Rule Name LikeDownloads all rules whose name matches this text, ignoring case (use % as wildcard).
Blitz Report run screen for MRP Sourcing Rule Upload with Upload Mode Create, Update, Sourcing Rule Type Sourcing Rule, Include Global Sourcing Rules No, organization M1 and Sourcing Rule Name Like B-%

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per shipping source of the selected rules, or an empty sheet in Create mode.

Excel file with the Buy From sources of three existing M1 sourcing rules

Step 3 – Enter or change the rules and sources

Enter one row per shipping source and repeat the rule and receiving organization values on each row of the same rule:

  • Rule Name, Rule Description and All Orgs: No for a rule of one organization, Yes for a global rule. Bills of distribution are always global.
  • Receipt Organization: required for a rule with All Orgs = No and left blank for a global sourcing rule. For a bill of distribution, enter one receiving organization per group of rows.
  • Effective Date From (defaults to today) and optional Effective Date To.
  • Source Type: Buy From with a Supplier and Supplier Site (the Supplier Site Op Unit fills in when the site exists in one operating unit), or Transfer From / Make At with a Source Organization. Make At defaults the source organization to the receipt organization; for Transfer From the list shows the organizations with a shipping network to the receipt organization.
  • Allocation Percent and Rank: the percentages of the sources with the same rank should add up to 100.
  • Ship Method and Intransit Time for Transfer From sources; the intransit time defaults from the ship method.

On downloaded rows you can change the description, Effective Date To, allocation percent, rank, ship method and intransit time. Rule Type, All Orgs, Receipt Organization and Source Type cannot be changed on an existing rule. Planning Active is display only and is set by the upload. To delete, set Delete Option to Sourcing Rule (the whole rule) or Shipping Org (that one source) on a downloaded row.

Excel file with the description of rule B-ConElec-MAIN changed, marked Update, and a new rule B-Allied/Dressler-Split with a 60/40 split between two suppliers, marked Create

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

All edited rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates, updates or deletes each sourcing rule with its receiving organizations and shipping sources.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message, and the Planning Active flag of each rule.

Result report showing the created and updated sourcing rules with status Success and Planning Active Yes

What’s produced

  • Sourcing rules and bills of distribution created, updated or deleted, with their receiving organizations and shipping sources.
  • Planning Active set to Yes when the allocation percentages of each rank add up to 100 for every receiving organization, otherwise No.
  • A result report listing every row with a status (success or error) and a message.

Common questions

How do I split supply 60/40 across two suppliers?
Enter two Buy From rows for the same rule, receipt organization and effective date, both with Rank 1, and Allocation Percent 60 and 40. For a primary and a backup supplier, give each 100 percent with Rank 1 and Rank 2.

Why is Planning Active No for my rule?
The allocation percentages of at least one rank do not add up to 100 for a receiving organization. Correct the percentages and upload again.

Can I change a rule’s type or organization later?
No. Rule type, organization, receipt organization and source type of existing entries cannot be changed; create a new rule or source instead.

Can I delete just a receiving organization line?
No. You can delete the whole sourcing rule or an individual shipping source.

What happens when one row of a rule fails?
The whole rule is not saved. The failing rows show the error and the other rows of the rule show “One or more other lines in this sourcing rule have errors”.

Troubleshooting

MessageCauseWhat to do
You can not update existing Sourcing Rule Entries in Create ModeThe row matches an existing rule, receiving organization and source, and Upload Mode is Create.Upload in Create, Update mode.
You can not delete existing Sourcing Rule Entries in Create ModeA Delete Option is set and Upload Mode is Create.Upload in Create, Update mode.
Receipt Organization should be null for global Sourcing Rules (All Orgs=Yes)A receipt organization on a global sourcing rule.Clear the Receipt Organization, or set All Orgs to No.
Receipt Organization must be specified for org specific Sourcing Rules (All Orgs=No)All Orgs is No and the receipt organization is blank.Enter the Receipt Organization.
Bill of Distributions are Global. The All Orgs flag must be set to Yes for a Bill of DistributionAll Orgs is No on a bill of distribution.Set All Orgs to Yes.
Sourcing Rule Type cannot be changed / Sourcing Rule Organization cannot be changedThe rule exists with another type or organization.Create a new rule with another name.
Supplier is not validThe supplier name does not exist.Pick the Supplier from the list.
Supplier Site / Operating Unit combination is not validThe site does not exist for the supplier in that operating unit.Pick the Supplier Site and Supplier Site Op Unit from the lists.
Shipping Source must specify either a Source Organization or VendorNeither a source organization nor a supplier and site are entered.Enter a Source Organization, or a Supplier and Supplier Site.
Shipping Source cannot specify both a Source Organization and VendorBoth a source organization and a supplier are entered.Keep only the one that matches the Source Type.
Source Organization is required for Transfer From and Make At Source TypesTransfer From or Make At without a source organization.Enter the Source Organization.
Vendor is required for Buy From Source TypeBuy From without a supplier and site.Enter the Supplier and Supplier Site.
Source Allocation Percent must be specified / Source Rank be specifiedAllocation Percent or Rank is blank.Enter both on every source row.
Shipping Source Type cannot be changed / Shipping Source cannot be switched between vendor and organizationA downloaded source row was changed to another source type.Delete the source with Delete Option Shipping Org and add a new row.
Sourcing Rule cannot be deleted. Sourcing Rule does not existDelete Option Sourcing Rule on a rule that does not exist.Check the rule name and organization, or clear the Delete Option.
Shipping Organization cannot be deleted. Shipping Organization entry does not existDelete Option Shipping Org on a source that does not exist.Check the source values, or clear the Delete Option.
One or more other lines in this sourcing rule have errorsAnother row of the same rule failed, so the whole rule was not saved.Fix the failing row and upload the rule again.

SQL 209 lines

Parameters 6

ParameterTypeList of values SQL
Upload ModeLOV
:p_upload_mode like '%' || xxen_upload.action_update
Sourcing Rule TypeLOV
sr.rule_type = :p_rule_type
Include Global Sourcing RulesLOV
Sourcing Rule OrganizationLOV
(sr.organization = :p_organization or
 (sr.organization is null and
  :p_include_global = 'Y'
 )
)
Sourcing Rule NameLOV
sr.rule_name = :p_rule_name
Sourcing Rule Name LikeChar
lower(sr.rule_name) like lower(:p_rule_name_like)