MRP Sourcing Rule Upload

➜ Back to Library
Description

MRP Sourcing Rule Upload creates, updates and deletes Oracle MRP/ASCP sourcing rules and bills of distribution – including their receiving organizations and shipping sources (Make At / Transfer From / Buy From) with rank and allocation percentages – in bulk from Excel, via the standard Oracle public API.

When to use it

  • Mass-create new sourcing rules or bills of distribution for many items and organizations.
  • Model multi-source supply chains with rank and allocation-percentage splits across suppliers or source organizations.
  • Roll out Transfer From / Make At / Buy From sourcing across organizations from one spreadsheet.
  • Bulk-update effective dates, allocation percentages, ranks, ship methods or intransit times.
  • Add or remove a shipping source on existing rules, or delete obsolete rules.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the organizations involved.
  • Inventory organizations, interorg shipping networks (ship methods, intransit times) and suppliers/sites are already set up.
  • This is an MRP/ASCP planning feature.

Step 1 – Choose a mode, set the parameters and download

In Blitz Report, open MRP Sourcing Rule Upload, choose an Upload Mode, and set the parameters:

ParameterPurpose
Upload ModeCreate (new rules only) or Create, Update (download existing rules to edit/delete). Default is Create.
Sourcing Rule TypeFilter to Sourcing Rule or Bill of Distribution.
Include Global Sourcing RulesInclude organization-independent (global / all-orgs) rules. Default Yes.
Sourcing Rule Organization / Name / Name LikeRestrict which existing rules are downloaded.

Run the upload to download and open the Excel file. Each rule and source is automatically handled as a create or an update depending on whether it already exists.

Step 2 – Enter the rules and sources

Each row carries one shipping source; the rule header and receiving-org values repeat across the rows of the same rule. Enter the Rule Name, Rule Type and All Orgs (Yes for a global rule), the Receipt Organization and Effective Date From/To, then the source: Source Type (Make At / Transfer From / Buy From), the Source Organization or Supplier/Supplier Site, the Allocation Percent and Rank, and any Ship Method/Intransit Time.

Step 3 – Delete rules or sources (optional)

Set the Delete Option column to “Sourcing Rule” to delete the whole rule, or “Shipping Org” to remove that one source entry. The targeted entity must already exist.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates, updates or deletes each rule and source. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created, updated or deleted sourcing rules / bills of distribution with their receiving orgs and shipping sources.
  • A result report listing every row with a status (success or error) and a message.

Common questions

How do I split demand 60/40 across two suppliers?
Enter two source rows for the same rule and receiving org with the same Source Type, setting Allocation Percent to 60 and 40 (or split by Rank for primary/secondary).

Why is Receipt Organization blank or greyed for my rule?
For global rules (All Orgs = Yes) and for bills of distribution the receipt organization must be blank – these are organization-independent.

Can I change a rule’s Type or Organization later?
No – those cannot be changed on an existing rule; create a new rule instead.

Can I delete just a receiving-organization line?
No – only the whole sourcing rule or an individual shipping-org source can be deleted.

My update failed asking me to use Create, Update mode.
Create mode only accepts brand-new rules; choose Create, Update to mix new and existing rows.

Troubleshooting

MessageCauseWhat to do
Receipt Organization should be null for global rules / must be specified for org-specific rulesThe receipt org doesn’t match the All Orgs flag.Blank the receipt org for global rules; provide one for org-specific rules.
Shipping source must specify either a Source Organization or a Supplier (not both)The source data doesn’t match the Source Type.For Make At / Transfer From give a Source Organization; for Buy From give a Supplier and Site – never both.
Allocation Percent / Rank must be specifiedA shipping source row is missing required fields.Populate Allocation Percent and Rank on every source row.
Sourcing Rule cannot be deleted – it does not existDelete Option set but the rule/source isn’t found.Verify the rule/source exists, or remove the Delete Option.
One or more other lines in this sourcing rule have errorsAny line error rolls back the whole rule.Fix the offending line and re-upload the rule.
with sr_query as
(
select
 --
 -- sourcing rule
 xxen_util.meaning(mrs.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700) rule_type,
 mrs.sourcing_rule_name rule_name,
 mrs.description rule_description,
 xxen_util.meaning(nvl2(mp.organization_code,'2','1'),'INV_YES_NO',3) all_orgs,
 mp.organization_code  organization,
 xxen_util.meaning(mrs.status,'INV_YES_NO',3) active,
 xxen_util.meaning(decode(mrs.planning_active,1,1,null),'INV_YES_NO',3) planning_active,
 -- receiving organizations
 msrov.organization_code receipt_organization,
 msrov.customer          receipt_customer,
 msrov.address           receipt_address,
 msrov.effective_date    effective_date_from,
 msrov.disable_date      effective_date_to,
 -- shipping organization
 xxen_util.meaning(mssov.source_type,'MRP_SOURCE_TYPE',700) source_type,
 mssov.source_organization_code source_organization,
 asu.vendor_name supplier,
 asu.segment1 supplier_number,
 mssov.vendor_site supplier_site,
 haouv.name supplier_site_op_unit,
 mssov.allocation_percent allocation_percent,
 mssov.rank rank,
 mssov.ship_method ship_method,
 mssov.intransit_time intransit_time,
 -- attributes
 xxen_util.display_flexfield_context(704,'MRP_SOURCING_RULES',mrs.attribute_category) rule_attribute_category,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE1',mrs.rowid,mrs.attribute1) sr_rule_attribute1,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE2',mrs.rowid,mrs.attribute2) sr_rule_attribute2,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE3',mrs.rowid,mrs.attribute3) sr_rule_attribute3,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE4',mrs.rowid,mrs.attribute4) sr_rule_attribute4,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE5',mrs.rowid,mrs.attribute5) sr_rule_attribute5,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE6',mrs.rowid,mrs.attribute6) sr_rule_attribute6,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE7',mrs.rowid,mrs.attribute7) sr_rule_attribute7,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE8',mrs.rowid,mrs.attribute8) sr_rule_attribute8,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE9',mrs.rowid,mrs.attribute9) sr_rule_attribute9,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE10',mrs.rowid,mrs.attribute10) sr_rule_attribute10,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE11',mrs.rowid,mrs.attribute11) sr_rule_attribute11,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE12',mrs.rowid,mrs.attribute12) sr_rule_attribute12,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE13',mrs.rowid,mrs.attribute13) sr_rule_attribute13,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE14',mrs.rowid,mrs.attribute14) sr_rule_attribute14,
 xxen_util.display_flexfield_value(704,'MRP_SOURCING_RULES',mrs.attribute_category,'ATTRIBUTE15',mrs.rowid,mrs.attribute15) sr_rule_attribute15,
 --
 xxen_util.display_flexfield_context(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category) receipt_attribute_category,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE1',msrov.row_id,msrov.attribute1) sr_receipt_attribute1,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE2',msrov.row_id,msrov.attribute2) sr_receipt_attribute2,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE3',msrov.row_id,msrov.attribute3) sr_receipt_attribute3,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE4',msrov.row_id,msrov.attribute4) sr_receipt_attribute4,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE5',msrov.row_id,msrov.attribute5) sr_receipt_attribute5,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE6',msrov.row_id,msrov.attribute6) sr_receipt_attribute6,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE7',msrov.row_id,msrov.attribute7) sr_receipt_attribute7,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE8',msrov.row_id,msrov.attribute8) sr_receipt_attribute8,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE9',msrov.row_id,msrov.attribute9) sr_receipt_attribute9,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE10',msrov.row_id,msrov.attribute10) sr_receipt_attribute10,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE11',msrov.row_id,msrov.attribute11) sr_receipt_attribute11,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE12',msrov.row_id,msrov.attribute12) sr_receipt_attribute12,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE13',msrov.row_id,msrov.attribute13) sr_receipt_attribute13,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE14',msrov.row_id,msrov.attribute14) sr_receipt_attribute14,
 xxen_util.display_flexfield_value(704,'MRP_SR_RECEIPT_ORG',msrov.attribute_category,'ATTRIBUTE15',msrov.row_id,msrov.attribute15) sr_receipt_attribute15,
 --
 xxen_util.display_flexfield_context(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category) ship_attribute_category,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE1',mssov.row_id,mssov.attribute1) sr_ship_attribute1,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE2',mssov.row_id,mssov.attribute2) sr_ship_attribute2,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE3',mssov.row_id,mssov.attribute3) sr_ship_attribute3,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE4',mssov.row_id,mssov.attribute4) sr_ship_attribute4,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE5',mssov.row_id,mssov.attribute5) sr_ship_attribute5,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE6',mssov.row_id,mssov.attribute6) sr_ship_attribute6,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE7',mssov.row_id,mssov.attribute7) sr_ship_attribute7,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE8',mssov.row_id,mssov.attribute8) sr_ship_attribute8,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE9',mssov.row_id,mssov.attribute9) sr_ship_attribute9,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE10',mssov.row_id,mssov.attribute10) sr_ship_attribute10,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE11',mssov.row_id,mssov.attribute11) sr_ship_attribute11,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE12',mssov.row_id,mssov.attribute12) sr_ship_attribute12,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE13',mssov.row_id,mssov.attribute13) sr_ship_attribute13,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE14',mssov.row_id,mssov.attribute14) sr_ship_attribute14,
 xxen_util.display_flexfield_value(704,'MRP_SR_SOURCE_ORG',mssov.attribute_category,'ATTRIBUTE15',mssov.row_id,mssov.attribute15) sr_ship_attribute15,
 -- ids
 mrs.sourcing_rule_id,
 msrov.sr_receipt_id,
 mssov.sr_source_id,
 --
 mssov.last_updated_by,
 mssov.last_update_date
from
 mrp_sourcing_rules mrs,
 mtl_parameters mp,
 mrp_sr_receipt_org_v msrov,
 mrp_sr_source_org_v mssov,
 ap_suppliers asu,
 ap_supplier_sites_all assa,
 hr_all_organization_units_vl haouv
where
 mp.organization_id (+) = mrs.organization_id and
 msrov.sourcing_rule_id (+) = mrs.sourcing_rule_id and
 mssov.sr_receipt_id (+) = msrov.sr_receipt_id and
 asu.vendor_id (+) = mssov.vendor_id and
 assa.vendor_site_id (+) = mssov.vendor_site_id and
 haouv.organization_id (+) = assa.org_id and
 (mrs.organization_id is null or
  mrs.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
 )
)
--
-- Main Query Starts Here
--
select /*+ push_pred(sr) */
 null action_,
 null status_,
 null message_,
 null request_id_,
 null modified_columns_,
 null sr_row_id,
 null ro_row_id,
 null so_row_id,
 :p_upload_mode upload_mode,
 :p_bypass_dff_validation bypass_dff_validation,
 -- sourcing rule
 sr.rule_name,
 sr.rule_description,
 sr.rule_type,
 sr.planning_active,
 sr.all_orgs,
 -- receiving organizations
 sr.receipt_organization,
 sr.receipt_customer,
 sr.receipt_address,
 sr.effective_date_from,
 sr.effective_date_to,
 -- shipping organization
 sr.source_type,
 sr.source_organization,
 sr.supplier,
 sr.supplier_number,
 sr.supplier_site,
 sr.supplier_site_op_unit,
 sr.allocation_percent,
 sr.rank,
 sr.ship_method,
 sr.intransit_time,
 -- attributes
 sr.rule_attribute_category,
 sr.sr_rule_attribute1,
 sr.sr_rule_attribute2,
 sr.sr_rule_attribute3,
 sr.sr_rule_attribute4,
 sr.sr_rule_attribute5,
 sr.sr_rule_attribute6,
 sr.sr_rule_attribute7,
 sr.sr_rule_attribute8,
 sr.sr_rule_attribute9,
 sr.sr_rule_attribute10,
 sr.sr_rule_attribute11,
 sr.sr_rule_attribute12,
 sr.sr_rule_attribute13,
 sr.sr_rule_attribute14,
 sr.sr_rule_attribute15,
 --
 sr.receipt_attribute_category,
 sr.sr_receipt_attribute1,
 sr.sr_receipt_attribute2,
 sr.sr_receipt_attribute3,
 sr.sr_receipt_attribute4,
 sr.sr_receipt_attribute5,
 sr.sr_receipt_attribute6,
 sr.sr_receipt_attribute7,
 sr.sr_receipt_attribute8,
 sr.sr_receipt_attribute9,
 sr.sr_receipt_attribute10,
 sr.sr_receipt_attribute11,
 sr.sr_receipt_attribute12,
 sr.sr_receipt_attribute13,
 sr.sr_receipt_attribute14,
 sr.sr_receipt_attribute15,
 --
 sr.ship_attribute_category,
 sr.sr_ship_attribute1,
 sr.sr_ship_attribute2,
 sr.sr_ship_attribute3,
 sr.sr_ship_attribute4,
 sr.sr_ship_attribute5,
 sr.sr_ship_attribute6,
 sr.sr_ship_attribute7,
 sr.sr_ship_attribute8,
 sr.sr_ship_attribute9,
 sr.sr_ship_attribute10,
 sr.sr_ship_attribute11,
 sr.sr_ship_attribute12,
 sr.sr_ship_attribute13,
 sr.sr_ship_attribute14,
 sr.sr_ship_attribute15,
 --
 null delete_option,
 -- ids
 sr.organization,
 sr.active,
 sr.sourcing_rule_id,
 sr.sr_receipt_id,
 sr.sr_source_id,
 to_number(null) group_id
from
 sr_query sr
where
 :p_include_global = :p_include_global and
 1=1
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Sourcing Rule Type
sr.rule_type = :p_rule_type
LOV
Include Global Sourcing Rules
 
LOV
Sourcing Rule Organization
(sr.organization = :p_organization or
 (sr.organization is null and
  :p_include_global = 'Y'
 )
)
LOV
Sourcing Rule Name
sr.rule_name = :p_rule_name
LOV
Sourcing Rule Name Like
lower(sr.rule_name) like lower(:p_rule_name_like)
Char
Download
Blitz Report™