MRP Sourcing Rule Assignment Upload

➜ Back to Library
Description

MRP Sourcing Rule Assignment Upload creates, updates and deletes Oracle MRP/ASCP assignment sets and the sourcing rule assignments within them – tying sourcing rules or bills of distribution to targets at any of the six assignment levels (Global, Category, Item, Organization, Category-Organization, Item-Organization) – from Excel, via the standard Oracle public API.

When to use it

  • Mass-create a new assignment set and load many sourcing-rule assignments into it.
  • Bulk-assign sourcing rules or bills of distribution to items, categories, organizations, or customers/sites.
  • Re-point many assignments to a different sourcing rule in one pass.
  • Bulk-remove obsolete assignments, or delete an entire assignment set.
  • Maintain assignment-set or assignment descriptive flexfield attributes in bulk.
  • Migrate or copy an assignment set’s contents between environments.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with the relevant organization access.
  • The sourcing rules / bills of distribution being assigned already exist, are planning-active, and have a currently-effective receipt organization.
  • For Category-level assignments, the MRP sourcing-rule category-set profile must be configured.

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

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

ParameterPurpose
Upload ModeCreate (new only) or Create, Update (download existing to edit/delete). Default is Create.
Master Organization CodeThe item-master organization that drives selectable items. Defaults to your manufacturing org.
Assignment Set / Assignment Type / Sourcing Rule Type / Name / Organization / Category / CustomerRestrict which existing assignments are downloaded.
Bypass DFF ValidationSkip descriptive-flexfield validation on upload, if needed.

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

Step 2 – Enter the assignments

On each row enter the Assignment Set, the Assignment Type (the level), the target for that level (Item, Category, Organization or Customer/site), and the Sourcing Rule Type and Sourcing Rule Name to assign. The required target columns depend on the assignment type.

Step 3 – Delete assignments or sets (optional)

Set the Delete Option column to “Assignment” to delete one assignment, or “Assignment Set” to delete the whole set. Deletion requires Create, Update mode.

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 set and assignment. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created, updated or deleted assignment sets and sourcing rule assignments.
  • A result report listing every row with a status (success or error) and a message.

Common questions

What do the six assignment types mean?
They define the scope a sourcing rule applies to – Global (everything), Category, Item, Organization, Category-Organization, or Item-Organization. The required target columns change accordingly.

Can I assign a bill of distribution at organization level?
No – bills of distribution are only valid for Global, Category and Item assignment types; organization-scoped types accept only sourcing rules.

Why can’t I pick Category as an assignment type?
Category and Category-Organization only appear when the MRP sourcing-rule category-set profile is configured.

How does it know whether to create or update?
It looks the set and assignment up by their keys – found = update, not found = create. The Upload Mode governs whether updates/deletes are allowed.

How do I delete an assignment or a whole set?
Set the Delete Option column to “Assignment” or “Assignment Set” and run in Create, Update mode.

Troubleshooting

MessageCauseWhat to do
You can not update / delete existing Assignments in Create ModeThe row’s operation conflicts with the chosen Upload Mode.Switch to Create, Update mode (or correct the row to match the mode).
Sourcing Rule Type / Name / Org is not validThe named rule doesn’t exist, isn’t planning-active, has no effective receipt org, or its org doesn’t match.Verify the Sourcing Rule Name, Type and (for org levels) that its organization matches.
Category level assignments are not permitted – profile not setA Category-type assignment was used but the category-set profile isn’t configured.Set the profile and use that category set.
Organization or Customer must be specified for this assignment type / cannot be specifiedThe target columns don’t match the chosen assignment type.Fill only the columns the level requires.
Assignment / Assignment Set cannot be deleted – it does not existDelete Option set but the target isn’t found.Confirm the set/assignment exists (download it first) before deleting.
select
 null action_,
 null status_,
 null message_,
 null request_id_,
 null modified_columns_,
 null set_row_id,
 null ass_row_id,
 :p_upload_mode upload_mode,
 :p_bypass_dff_validation bypass_dff_validation,
 -- assignment set
 mas.assignment_set_name assignment_set,
 mas.description assignment_set_description,
 -- assignment
 case mrav.assignment_type
 when 1 then 'Global'
 when 2 then 'Category'
 when 3 then 'Item'
 when 4 then 'Organization'
 when 5 then 'Category-Organization'
 when 6 then 'Item-Organization'
 end assignment_type,
 mrav.organization_code organization,
 msiv.concatenated_segments item,
 case when mrav.assignment_type in (2,5)
 then mrav.entity_name
 else null
 end category,
 case when mrav.assignment_type in (3,6)
 then msiv.description
 else mrav.description
 end description,
 mrav.customer_name,
 hca.account_number customer_number,
 hcsua.location customer_site,
 xxen_util.meaning(hcsua.site_use_code,'SITE_USE_CODE',222) customer_site_use,
 haouv.name customer_site_ou,
 mrav.ship_to_address,
 mrav.sourcing_rule_type_text sourcing_rule_type,
 mrav.sourcing_rule_name,
 mp.organization_code sourcing_rule_org,
 -- attributes
 xxen_util.display_flexfield_context(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category) set_attribute_category,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE1',mas.rowid,mas.attribute1) sr_set_attribute1,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE2',mas.rowid,mas.attribute2) sr_set_attribute2,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE3',mas.rowid,mas.attribute3) sr_set_attribute3,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE4',mas.rowid,mas.attribute4) sr_set_attribute4,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE5',mas.rowid,mas.attribute5) sr_set_attribute5,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE6',mas.rowid,mas.attribute6) sr_set_attribute6,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE7',mas.rowid,mas.attribute7) sr_set_attribute7,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE8',mas.rowid,mas.attribute8) sr_set_attribute8,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE9',mas.rowid,mas.attribute9) sr_set_attribute9,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE10',mas.rowid,mas.attribute10) sr_set_attribute10,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE11',mas.rowid,mas.attribute11) sr_set_attribute11,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE12',mas.rowid,mas.attribute12) sr_set_attribute12,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE13',mas.rowid,mas.attribute13) sr_set_attribute13,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE14',mas.rowid,mas.attribute14) sr_set_attribute14,
 xxen_util.display_flexfield_value(704,'MRP_ASSIGNMENT_SETS',mas.attribute_category,'ATTRIBUTE15',mas.rowid,mas.attribute15) sr_set_attribute15,
 --
 xxen_util.display_flexfield_context(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category) assign_attribute_category,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE1',mrav.row_id,mrav.attribute1) sr_assign_attribute1,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE2',mrav.row_id,mrav.attribute2) sr_assign_attribute2,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE3',mrav.row_id,mrav.attribute3) sr_assign_attribute3,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE4',mrav.row_id,mrav.attribute4) sr_assign_attribute4,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE5',mrav.row_id,mrav.attribute5) sr_assign_attribute5,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE6',mrav.row_id,mrav.attribute6) sr_assign_attribute6,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE7',mrav.row_id,mrav.attribute7) sr_assign_attribute7,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE8',mrav.row_id,mrav.attribute8) sr_assign_attribute8,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE9',mrav.row_id,mrav.attribute9) sr_assign_attribute9,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE10',mrav.row_id,mrav.attribute10) sr_assign_attribute10,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE11',mrav.row_id,mrav.attribute11) sr_assign_attribute11,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE12',mrav.row_id,mrav.attribute12) sr_assign_attribute12,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE13',mrav.row_id,mrav.attribute13) sr_assign_attribute13,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE14',mrav.row_id,mrav.attribute14) sr_assign_attribute14,
 xxen_util.display_flexfield_value(704,'MRP_SR_ASSIGNMENTS',mrav.attribute_category,'ATTRIBUTE15',mrav.row_id,mrav.attribute15) sr_assign_attribute15,
 --
 null delete_option,
 -- ids
 mas.assignment_set_id,
 mrav.assignment_id,
 mrav.sourcing_rule_id,
 mrav.assignment_type assignment_type_id,
 :p_master_org_id master_organization_id
from
 mrp_assignment_sets mas,
 mrp_sr_assignments_v mrav,
 mrp_sourcing_rules msr,
 mtl_parameters mp,
 mtl_system_items_vl msiv,
 hz_cust_accounts hca,
 hz_cust_site_uses_all hcsua,
 hr_all_organization_units_vl haouv
where
 1=1 and
 :p_master_org_code is not null and
 mas.assignment_set_id = mrav.assignment_set_id and
 mrav.sourcing_rule_id = msr.sourcing_rule_id (+) and
 msr.organization_id = mp.organization_id (+) and
 case when mrav.assignment_type in (3,6) then nvl(mrav.organization_id,:p_master_org_id) else null end = msiv.organization_id (+) and
 case when mrav.assignment_type in (3,6) then mrav.inventory_item_id else null end = msiv.inventory_item_id (+) and
 mrav.customer_id = hca.cust_account_id (+) and
 mrav.ship_to_site_id = hcsua.site_use_id (+) and
 hcsua.org_id = haouv.organization_id (+) and
 (mrav.assignment_type in (1,2,4,5) or
  mrav.organization_id is not null or
  (mrav.assignment_type in (3,6) and
   mrav.organization_id is null and
   exists (select null from mtl_system_items_b msib where msib.organization_id = :p_master_org_id and msib.inventory_item_id = mrav.inventory_item_id)
  )
 )
Parameter NameSQL textValidation
Master Organization Code
 
LOV
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Assignment Set
mas.assignment_set_name = :p_assign_set_name
LOV
Assignment Type
case mrav.assignment_type
when 1 then 'Global'
when 2 then 'Category'
when 3 then 'Item'
when 4 then 'Organization'
when 5 then 'Category-Organization'
when 6 then 'Item-Organization'
end = :p_assign_type
LOV
Sourcing Rule Type
mrav.sourcing_rule_type_text = :p_src_rule_type
LOV
Sourcing Rule Name
mrav.sourcing_rule_name = :p_src_rule_name
LOV
Organization Code
mrav.organization_code = :p_assign_org
LOV
Category
(mrav.category_set_id,mrav.category_id) in
(select
 mcsv.category_set_id, 
 mcv.category_id
 from
 mtl_category_sets_vl mcsv,
 mtl_categories_v mcv
 where
 mcsv.structure_id=mcv.structure_id and
 mcv.category_concat_segs = :p_assign_category and
 mcsv.category_set_name=:p_category_set
) and
mrav.assignment_type in (2,5)
LOV
Item
mrav.inventory_item_id in
(select 
 msiv.inventory_item_id
 from 
 mtl_system_items_vl msiv
 where 
 msiv.organization_id = nvl(mrav.organization_id,:p_master_org_id) and
 msiv.concatenated_segments = :p_assign_item
) and
mrav.assignment_type in (3,6)
LOV
Customer
mrav.customer_name = :p_assign_cust_name
LOV
Customer Number
hca.account_number= :p_assign_cust_num
LOV
Customer Operating Unit
haouv.name = :p_assign_cust_site_ou
LOV
Download
Related Blitz Reports
CAC Intercompany SO Price List vs. Item Cost Comparison CAC New Items CAC Last Standard Item Cost CAC Item vs. Component Include in Rollup Controls TB GL Account Analysis (Drilldown)v2 MRP Sourcing Rules and Bills of Distribution CAC New Standard Item Costs CAC User-Defined and Rolled Up Costs CAC Cost vs. Planning Item Controls
Blitz Report™