MSC Sourcing Rule Assignment Upload

Description

MSC Sourcing Rule Assignment Upload creates, updates and deletes assignment sets and the sourcing rule assignments within them on the Oracle ASCP / APS planning server for a selected planning instance, from Excel – mirroring the Sourcing Assignments setup window.

When to use it

You maintain sourcing rule assignments on the ASCP/APS planning server (not in the source EBS instance) and want to mass-load them.
Create new assignment sets and populate them with many assignments at once.
Bulk-update existing (non-collected) assignments – for example re-point them to a different sourcing rule.
Bulk-delete assignments, or remove an entire assignment set.
Assign across instance, item, category, organization, customer or region scopes in one spreadsheet.
Migrate or replicate assignments across instances or environments.

Before you start

Blitz Report is installed and you are signed in with access to the planning organizations.
A planning instance is registered and its data (items, trading partners, regions, categories, sourcing rules) is collected to the planning server.
The sourcing rules you assign exist on the planning server and are planning-active.
For Category-level assignments, the MSC sourcing-rule category-set profile is configured.

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

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

Parameter	Purpose
Planning Instance	The ASCP/APS instance to read from and write to (required).
Master Organization Code	The item-master organization that drives selectable items (required).
Upload Mode	Create (new only) or Create, Update (download existing to edit/delete). Default is Create.
Include Collected Data	Whether collected assignments are shown (they are read-only).
Assignment Set / Type / Sourcing Rule / Organization / Category / Customer / Region filters	Restrict which existing assignments are downloaded.

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 – instance, item, category, organization, customer or region), the target columns that level requires, and the Sourcing Rule Type and Sourcing Rule Name to assign.

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 and its assignments. Deletion requires Create, Update mode. Collected rows cannot be deleted here.

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 on the planning server. 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 on the planning server.
A result report listing every row with a status (success or error) and a message.

Common questions

How is this different from the MRP Sourcing Rule Assignment Upload?
This one writes to the planning server (MSC/ASCP) tables for a chosen planning instance; the MRP one writes to the source EBS instance’s sourcing tables. Use MSC when your assignments live on the planning server.

Why can’t I change some rows?
Rows from collected assignment sets/assignments are read-only; maintain those in the source instance and re-collect.

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.

How does it know whether to create or update?
It looks the set name and the full assignment key up on the planning server – found = update, not found = create.

Which columns are required for my assignment type?
The Assignment Type decides – item types need an Item, category types need a Category, region types need a zone or region, and some columns must be left blank for each type.

Troubleshooting

Message	Cause	What to do
You can not update / delete existing Assignments in Create Mode	The row matches an existing assignment or is flagged for delete while in Create mode.	Re-download with Create, Update mode, or remove the existing/delete rows.
You can not update / delete a collected Assignment (or Set)	The row belongs to a collected (read-only) set/assignment.	Maintain collected data in the source instance and re-collect.
The Sourcing Rule Type is invalid for this Assignment Type	A bill of distribution or org-specific rule was used where the level requires a global rule.	Use a sourcing rule type/org that matches the assignment type.
Category level assignments are not permitted – profile not set	A category-type assignment was used but the category-set profile isn’t set.	Set the MSC sourcing-rule category-set profile and use that set.
Columns can not be specified for this assignment type	Columns populated that aren’t valid for the chosen assignment type.	Clear the columns that don’t apply to that type.

Copy

with sra_query as
(
select
 -- assignment set
 mas.assignment_set_name assignment_set,
 mas.description assignment_set_description,
 -- assignment
 case msav.assignment_type
 when 1 then 'Instance'
 when 2 then 'Category'
 when 3 then 'Item-Instance'
 when 4 then 'Instance-Organization'
 when 5 then 'Category-Instance-Organization'
 when 6 then 'Item-Instance-Organization'
 when 8 then 'Category-Instance-Region'
 when 7 then 'Instance-Region'
 when 9 then 'Item-Instance-Region'
 end assignment_type,
 decode(msav.assignment_type,3,null,msc_get_name.org_code(msav.organization_id, msav.sr_instance_id)) organization,
 msav.organization_code organization_code,
 case when msav.assignment_type in (3,6,9)
 then msav.entity_name
 else null
 end item,
 case when msav.assignment_type in (2,5,8)
 then msav.entity_name
 else null
 end category,
 msav.description description,
 msav.customer_name,
 hca.partner_number customer_number,
 hcsua.location customer_site,
 xxen_util.meaning(hcsua.tp_site_code,'SITE_USE_CODE',222) customer_site_use,
 msav.ship_to_address,
 hcsua.operating_unit_name customer_site_ou,
 msav.sourcing_rule_type_text sourcing_rule_type,
 msav.sourcing_rule_name,
 mp.organization_code sourcing_rule_org,
 msav.item_type_value_text condition,
 --
 case
 when mr.region_id is null then null
 when mr.zone is not null then 'Zone'
 else 'Region'
 end zone_or_region,
 mr.zone,
 mr.country region_country,
 mr.state region_state,
 mr.city region_city,
 mr.postal_code_from region_postal_code_from,
 mr.postal_code_to region_postal_code_to,
 msav.region_code region_zone_description,
 -- attributes
 mas.attribute_category set_attribute_category,
 mas.attribute1 set_attribute1,
 mas.attribute2 set_attribute2,
 mas.attribute3 set_attribute3,
 mas.attribute4 set_attribute4,
 mas.attribute5 set_attribute5,
 mas.attribute6 set_attribute6,
 mas.attribute7 set_attribute7,
 mas.attribute8 set_attribute8,
 mas.attribute9 set_attribute9,
 mas.attribute10 set_attribute10,
 mas.attribute11 set_attribute11,
 mas.attribute12 set_attribute12,
 mas.attribute13 set_attribute13,
 mas.attribute14 set_attribute14,
 mas.attribute15 set_attribute15,
 --
 msav.attribute_category assign_attribute_category,
 msav.attribute1 assign_attribute1,
 msav.attribute2 assign_attribute2,
 msav.attribute3 assign_attribute3,
 msav.attribute4 assign_attribute4,
 msav.attribute5 assign_attribute5,
 msav.attribute6 assign_attribute6,
 msav.attribute7 assign_attribute7,
 msav.attribute8 assign_attribute8,
 msav.attribute9 assign_attribute9,
 msav.attribute10 assign_attribute10,
 msav.attribute11 assign_attribute11,
 msav.attribute12 assign_attribute12,
 msav.attribute13 assign_attribute13,
 msav.attribute14 assign_attribute14,
 msav.attribute15 assign_attribute15,
 --
 xxen_util.meaning(nvl(mas.collected_flag,2),'SYS_YES_NO',700) set_collected_flag,
 xxen_util.meaning(nvl(msav.collected_flag,2),'SYS_YES_NO',700) collected_flag,
 -- ids
 mas.sr_instance_id,
 mas.assignment_set_id,
 msav.assignment_id,
 msav.sourcing_rule_id,
 msav.assignment_type assignment_type_id,
 --
 msav.last_updated_by,
 msav.last_update_date
from
 msc_assignment_sets mas,
 msc_sr_assignments_v msav,
 msc_sourcing_rules msr,
 msc_trading_partners mp,
 msc_trading_partners hca,
 msc_trading_partner_sites hcsua,
 msc_regions mr
where
 mas.sr_instance_id = :p_sr_instance_id and
 mas.assignment_set_id = msav.assignment_set_id and
 mas.sr_instance_id = msav.sr_assignment_instance_id and
 msav.sourcing_rule_id = msr.sourcing_rule_id (+) and
 msav.sr_assignment_instance_id = msr.sr_instance_id (+) and
 --
 msr.sr_instance_id = mp.sr_instance_id (+) and
 msr.organization_id = mp.sr_tp_id (+) and
 mp.partner_type (+) = 3 and
 --
 msav.sr_assignment_instance_id = hca.sr_instance_id (+) and
 msav.customer_id = hca.partner_id (+) and
 hca.partner_type (+) = 2 and
 msav.sr_assignment_instance_id = hcsua.sr_instance_id (+) and
 msav.ship_to_site_id = hcsua.partner_site_id (+) and
 hcsua.partner_type (+) = 2 and
 msav.sr_assignment_instance_id = mr.sr_instance_id (+) and
 msav.region_id = mr.region_id (+)
)
--
-- Main Query Starts Here
--
select
x.*
from
(
select /*+ push_pred(sra) */
 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_sr_instance_code instance,
 -- assignment set
 sra.assignment_set,
 sra.assignment_set_description,
 -- assignment
 sra.assignment_type,
 sra.organization,
 sra.item,
 sra.category,
 sra.description,
 sra.customer_name,
 sra.customer_number,
 sra.customer_site,
 sra.customer_site_use,
 sra.customer_site_ou,
 sra.ship_to_address,
 --
 sra.zone_or_region,
 sra.zone,
 sra.region_country,
 sra.region_state,
 sra.region_city,
 sra.region_postal_code_from,
 sra.region_postal_code_to,
 --
 sra.sourcing_rule_type,
 sra.sourcing_rule_name,
 sra.sourcing_rule_org,
 sra.condition,
 -- attributes
 sra.set_attribute_category,
 sra.set_attribute1,
 sra.set_attribute2,
 sra.set_attribute3,
 sra.set_attribute4,
 sra.set_attribute5,
 sra.set_attribute6,
 sra.set_attribute7,
 sra.set_attribute8,
 sra.set_attribute9,
 sra.set_attribute10,
 sra.set_attribute11,
 sra.set_attribute12,
 sra.set_attribute13,
 sra.set_attribute14,
 sra.set_attribute15,
 --
 sra.assign_attribute_category,
 sra.assign_attribute1,
 sra.assign_attribute2,
 sra.assign_attribute3,
 sra.assign_attribute4,
 sra.assign_attribute5,
 sra.assign_attribute6,
 sra.assign_attribute7,
 sra.assign_attribute8,
 sra.assign_attribute9,
 sra.assign_attribute10,
 sra.assign_attribute11,
 sra.assign_attribute12,
 sra.assign_attribute13,
 sra.assign_attribute14,
 sra.assign_attribute15,
 --
 null delete_option,
 sra.collected_flag,
 -- ids
 sra.sr_instance_id,
 sra.assignment_set_id,
 sra.assignment_id,
 sra.sourcing_rule_id,
 sra.assignment_type_id,
 :p_master_org_id master_organization_id
from
 sra_query sra
where
 :p_master_org_code is not null and
 1=1
&not_use_first_block
&report_table_select
&report_table_name &report_table_where_clause
&processed_run
) x
order by
 x.assignment_set,
 x.assignment_type_id,
 x.organization,
 nvl(x.item,x.category),
 x.customer_name,
 x.customer_site,
 x.zone,
 x.region_country,
 x.region_state,
 x.region_city,
 x.region_postal_code_from,
 x.region_postal_code_to,
 x.sourcing_rule_name

with sra_query as ( select -- assignment set mas.assignment_set_name assignment_set, mas.description assignment_set_description, -- assignment case msav.assignment_type when 1 then 'Instance' when 2 then 'Category' when 3 then 'Item-Instance' when 4 then 'Instance-Organization' when 5 then 'Category-Instance-Organization' when 6 then 'Item-Instance-Organization' when 8 then 'Category-Instance-Region' when 7 then 'Instance-Region' when 9 then 'Item-Instance-Region' end assignment_type, decode(msav.assignment_type,3,null,msc_get_name.org_code(msav.organization_id, msav.sr_instance_id)) organization, msav.organization_code organization_code, case when msav.assignment_type in (3,6,9) then msav.entity_name else null end item, case when msav.assignment_type in (2,5,8) then msav.entity_name else null end category, msav.description description, msav.customer_name, hca.partner_number customer_number, hcsua.location customer_site, xxen_util.meaning(hcsua.tp_site_code,'SITE_USE_CODE',222) customer_site_use, msav.ship_to_address, hcsua.operating_unit_name customer_site_ou, msav.sourcing_rule_type_text sourcing_rule_type, msav.sourcing_rule_name, mp.organization_code sourcing_rule_org, msav.item_type_value_text condition, -- case when mr.region_id is null then null when mr.zone is not null then 'Zone' else 'Region' end zone_or_region, mr.zone, mr.country region_country, mr.state region_state, mr.city region_city, mr.postal_code_from region_postal_code_from, mr.postal_code_to region_postal_code_to, msav.region_code region_zone_description, -- attributes mas.attribute_category set_attribute_category, mas.attribute1 set_attribute1, mas.attribute2 set_attribute2, mas.attribute3 set_attribute3, mas.attribute4 set_attribute4, mas.attribute5 set_attribute5, mas.attribute6 set_attribute6, mas.attribute7 set_attribute7, mas.attribute8 set_attribute8, mas.attribute9 set_attribute9, mas.attribute10 set_attribute10, mas.attribute11 set_attribute11, mas.attribute12 set_attribute12, mas.attribute13 set_attribute13, mas.attribute14 set_attribute14, mas.attribute15 set_attribute15, -- msav.attribute_category assign_attribute_category, msav.attribute1 assign_attribute1, msav.attribute2 assign_attribute2, msav.attribute3 assign_attribute3, msav.attribute4 assign_attribute4, msav.attribute5 assign_attribute5, msav.attribute6 assign_attribute6, msav.attribute7 assign_attribute7, msav.attribute8 assign_attribute8, msav.attribute9 assign_attribute9, msav.attribute10 assign_attribute10, msav.attribute11 assign_attribute11, msav.attribute12 assign_attribute12, msav.attribute13 assign_attribute13, msav.attribute14 assign_attribute14, msav.attribute15 assign_attribute15, -- xxen_util.meaning(nvl(mas.collected_flag,2),'SYS_YES_NO',700) set_collected_flag, xxen_util.meaning(nvl(msav.collected_flag,2),'SYS_YES_NO',700) collected_flag, -- ids mas.sr_instance_id, mas.assignment_set_id, msav.assignment_id, msav.sourcing_rule_id, msav.assignment_type assignment_type_id, -- msav.last_updated_by, msav.last_update_date from msc_assignment_sets mas, msc_sr_assignments_v msav, msc_sourcing_rules msr, msc_trading_partners mp, msc_trading_partners hca, msc_trading_partner_sites hcsua, msc_regions mr where mas.sr_instance_id = :p_sr_instance_id and mas.assignment_set_id = msav.assignment_set_id and mas.sr_instance_id = msav.sr_assignment_instance_id and msav.sourcing_rule_id = msr.sourcing_rule_id (+) and msav.sr_assignment_instance_id = msr.sr_instance_id (+) and -- msr.sr_instance_id = mp.sr_instance_id (+) and msr.organization_id = mp.sr_tp_id (+) and mp.partner_type (+) = 3 and -- msav.sr_assignment_instance_id = hca.sr_instance_id (+) and msav.customer_id = hca.partner_id (+) and hca.partner_type (+) = 2 and msav.sr_assignment_instance_id = hcsua.sr_instance_id (+) and msav.ship_to_site_id = hcsua.partner_site_id (+) and hcsua.partner_type (+) = 2 and msav.sr_assignment_instance_id = mr.sr_instance_id (+) and msav.region_id = mr.region_id (+) ) -- -- Main Query Starts Here -- select x.* from ( select /*+ push_pred(sra) */ 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_sr_instance_code instance, -- assignment set sra.assignment_set, sra.assignment_set_description, -- assignment sra.assignment_type, sra.organization, sra.item, sra.category, sra.description, sra.customer_name, sra.customer_number, sra.customer_site, sra.customer_site_use, sra.customer_site_ou, sra.ship_to_address, -- sra.zone_or_region, sra.zone, sra.region_country, sra.region_state, sra.region_city, sra.region_postal_code_from, sra.region_postal_code_to, -- sra.sourcing_rule_type, sra.sourcing_rule_name, sra.sourcing_rule_org, sra.condition, -- attributes sra.set_attribute_category, sra.set_attribute1, sra.set_attribute2, sra.set_attribute3, sra.set_attribute4, sra.set_attribute5, sra.set_attribute6, sra.set_attribute7, sra.set_attribute8, sra.set_attribute9, sra.set_attribute10, sra.set_attribute11, sra.set_attribute12, sra.set_attribute13, sra.set_attribute14, sra.set_attribute15, -- sra.assign_attribute_category, sra.assign_attribute1, sra.assign_attribute2, sra.assign_attribute3, sra.assign_attribute4, sra.assign_attribute5, sra.assign_attribute6, sra.assign_attribute7, sra.assign_attribute8, sra.assign_attribute9, sra.assign_attribute10, sra.assign_attribute11, sra.assign_attribute12, sra.assign_attribute13, sra.assign_attribute14, sra.assign_attribute15, -- null delete_option, sra.collected_flag, -- ids sra.sr_instance_id, sra.assignment_set_id, sra.assignment_id, sra.sourcing_rule_id, sra.assignment_type_id, :p_master_org_id master_organization_id from sra_query sra where :p_master_org_code is not null and 1=1 &not_use_first_block &report_table_select &report_table_name &report_table_where_clause &processed_run ) x order by x.assignment_set, x.assignment_type_id, x.organization, nvl(x.item,x.category), x.customer_name, x.customer_site, x.zone, x.region_country, x.region_state, x.region_city, x.region_postal_code_from, x.region_postal_code_to, x.sourcing_rule_name

Parameter Name

SQL text

Validation

Planning Instance

LOV

Master Organization Code

LOV

Upload Mode

:p_upload_mode like '%' || xxen_upload.action_update

LOV

Assignment Set

sra.assignment_set = :p_assign_set_name

LOV

Assignment Type

sra.assignment_type = :p_assign_type

LOV

Sourcing Rule Type

sra.sourcing_rule_type = :p_src_rule_type

LOV

Sourcing Rule Name

sra.sourcing_rule_name = :p_src_rule_name

LOV

Organization Code

sra.organization = :p_assign_org

LOV