MSC Sourcing Rule Assignment Upload

Description

MSC Sourcing Rule Assignment Upload creates, updates and deletes assignment sets and their sourcing rule assignments on the Oracle Advanced Supply Chain Planning (ASCP) planning server for a selected planning instance, from Excel, following the same rules as the planning server’s Sourcing Assignments window. Download the existing assignments of an assignment set to change or delete them, or add new assignment sets and assignments at instance, category, item, organization, customer or region level.

When to use it

Maintain sourcing rule assignments that are defined on the planning server rather than collected from the source instance.
Create a new assignment set and populate it with many assignments at once, for example for a what-if plan.
Re-point existing assignments to a different sourcing rule or bill of distribution in bulk.
Delete individual assignments, or an entire assignment set with all its assignments.
Add planning-server assignments to an assignment set collected from the source instance.

Before you start

The planning instance is registered and its items, organizations, customers, regions, categories and sourcing rules are collected to the planning server.
The sourcing rules and bills of distribution you assign exist on the planning server for that instance.
For category-level assignments, the profile MSC: Sourcing Rule Category Set is set. Without it, the Category assignment types are not offered.

Step 1 – Set the parameters

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

Parameter	Meaning
Planning Instance	The planning instance whose assignment sets you download and upload (required).
Master Organization Code	The item master organization on the planning server (required). Defaults from your session organization. Items on rows without an Organization are validated in this organization.
Upload Mode	Create (default) opens an empty sheet for new assignment sets and assignments; Create, Update downloads the existing assignments for editing or deleting and lets you add new ones.
Assignment Set	Downloads the assignments of this assignment set. Collected sets are marked (Collected) in the list.
Assignment Type, Sourcing Rule Type, Sourcing Rule Name, Organization Code, Category, Item, Customer, Customer Number, Customer Operating Unit, Region/Zone, Condition	Restrict the download to the assignments that match.
Include Collected Data	No (default) downloads only the assignments created on the planning server; Yes also downloads collected assignment sets and assignments. Defaults to Yes when the selected Assignment Set is a collected set.

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per existing assignment, or an empty sheet in Create mode.

Step 3 – Enter or change the assignments

On each row enter the Assignment Set, the Assignment Type, the columns that type requires, and the Sourcing Rule Type (Sourcing Rule or Bill of Distribution) and Sourcing Rule Name to assign. An assignment set that does not exist yet is created with its Assignment Set Description from the first row of that set.

Assignment Type	Required columns
Instance	None. Leave Organization, Item, Category, Customer and region columns empty.
Category	Category
Item-Instance	Item
Instance-Organization	Organization, or Customer Name with Customer Site
Category-Instance-Organization	Category, and Organization or Customer Name with Customer Site
Item-Instance-Organization	Item, and Organization or Customer Name with Customer Site
Instance-Region	Zone or region
Category-Instance-Region	Category, and zone or region
Item-Instance-Region	Item, and zone or region

For a zone, set Zone Or Region to Zone and enter the Zone; for a region, set it to Region and enter the Region Country and, as needed, Region State, Region City and the postal code range. When a row has both an Organization and a customer, the customer must be related to that organization. Use Customer Number, Customer Site Use and Customer Site Ou to identify the customer and site where the name or site is not unique.

A bill of distribution can only be assigned at Instance, Category or Item-Instance level. An organization-specific sourcing rule (Sourcing Rule Org) can only be assigned at the organization levels, to the same organization; all other levels need a global sourcing rule. Condition defaults to Usable. Description, Ship To Address and Collected Flag are for information only.

On a downloaded row you can change the sourcing rule or the assignment set description. The Assignment Type of a downloaded row cannot be changed. To delete, set Delete Option to Assignment to delete that assignment, or to Assignment Set on the first row of a set to delete the set with all its assignments.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values, checks that the columns entered fit each row’s assignment type and sourcing rule, and saves the file. Correct any rows it flags before uploading.

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 assignment set and assignment on the planning server.

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message, such as Assignment Created, Assignment Updated, Assignment Set Updated or Assignment Deleted.

What’s produced

Assignment sets and sourcing rule assignments created, updated or deleted on the planning server for the selected planning instance, available to plans that use the assignment set.
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 upload maintains assignment sets on the planning server for a planning instance. The MRP Sourcing Rule Assignment Upload maintains the assignment sets of the source EBS instance, which are then collected to the planning server.

Can I change collected assignments?
No. Collected assignments cannot be updated or deleted, and a collected assignment set cannot be deleted or have its description changed. Maintain them in the source instance and run collections. You can add new assignments to a collected assignment set.

How does the upload decide between create and update?
It looks up the assignment set by name and the assignment by its set, type, sourcing rule type and assigned entity. An existing set or assignment is updated, otherwise it is created. In Create mode existing assignments cannot be updated or deleted.

Why is the Category assignment type missing from the list?
Category assignment types need the profile MSC: Sourcing Rule Category Set. The Category type (without organization or region) is also not available when that category set is controlled at organization level.

Which category set is used for category assignments?
The category set from the profile MSC: Sourcing Rule Category Set. Categories of other category sets are rejected.

Troubleshooting

Message	Cause	What to do
Sourcing Rule Type/Name/Org is not valid	No sourcing rule or bill of distribution with this type, name and organization exists on the planning server for the instance.	Pick the Sourcing Rule Name from the list and check the Sourcing Rule Type and Sourcing Rule Org.
Item is not valid	The item does not exist in the Organization, or in the Master Organization when the row has no Organization.	Pick the Item from the list, or check the Organization and Master Organization Code.
Item must be specified for this assignment type (similar for Category, Zone or Region, Organization or Customer)	A column the assignment type requires is empty.	Fill the required columns for the Assignment Type, see Step 3.
Category, Item and Zone/Region cannot be specified for this assignment type (and similar)	Columns are filled that do not belong to the assignment type.	Clear the listed columns or choose a different Assignment Type.
The Sourcing Rule Type is invalid for this Assignment Type	A bill of distribution is assigned at organization or region level.	Assign bills of distribution at Instance, Category or Item-Instance level only.
Only Global Sourcing Rules can be specified for this assignment type	An organization-specific sourcing rule is assigned at instance, category, item or region level.	Use a global sourcing rule (no Sourcing Rule Org).
The Sourcing Rule Organization (if specified) must match the Assignment Organization	The Sourcing Rule Org differs from the row’s Organization.	Use a sourcing rule of the same organization or a global rule.
There is no relationship defined between this organization and customer	The row has an Organization and a customer that are not related on the planning server.	Correct the Organization or the customer, or leave the Organization empty.
Customer Site is required when a Customer is specified	Customer Name is entered without a Customer Site.	Enter the Customer Site.
Customer Site / Site Use / Operating Unit combination is not valid	No ship-to or bill-to site of the customer matches.	Pick the Customer Site, Customer Site Use and Customer Site Ou from the lists.
Category level assignments are not permitted. Profile ‘MSC: Sourcing Rule Category’ Set is not set	A Category is entered but the profile MSC: Sourcing Rule Category Set is not set.	Ask your administrator to set the profile.
Ship Method(s) in the Sourcing Rule/Bill of Distribution is not valid for the destination chosen, please check the ship method/s and enter valid ship method(s).	A ship method in the sourcing rule is not defined between the source organization and the assigned organization or customer site.	Define the inter-organization shipping network, or use a sourcing rule with valid ship methods.
You can not update existing Assignments in Create Mode	A new row in Create mode matches an existing assignment.	Remove the row, or download in Create, Update mode and change the existing row.
You can not delete existing Assignments in Create Mode	Delete Option is set in Create mode.	Download in Create, Update mode and delete there.
You can not update/delete a collected Assignment	The assignment was collected from the source instance.	Maintain it in the source instance and run collections.
You can not delete a collected Assignment Set	Delete Option Assignment Set on a collected set.	Maintain it in the source instance.
An Assignment Set can only be deleted from the first row of that set in the file	Delete Option Assignment Set is on a later row of the set.	Set it on the first row of that assignment set.
Assignment cannot be deleted. Assignment does not exist	Delete Option Assignment on a row that does not match an existing assignment.	Delete only downloaded rows.

SQL 217 lines

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,
 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,
 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 /*+ 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,
to_number(null) upload_row
from
 sra_query sra
where
 :p_master_org_code is not null and
 1=1

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, 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, 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 /*+ 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, to_number(null) upload_row from sra_query sra where :p_master_org_code is not null and 1=1

Parameters 16

Parameter

Type

List of values SQL

Planning Instance

LOV

Master Organization Code

LOV

Upload Mode

LOV

:p_upload_mode like '%' || xxen_upload.action_update

Assignment Set

LOV

sra.assignment_set = :p_assign_set_name

Assignment Type

LOV

sra.assignment_type = :p_assign_type

Sourcing Rule Type

LOV

sra.sourcing_rule_type = :p_src_rule_type

Sourcing Rule Name

LOV

sra.sourcing_rule_name = :p_src_rule_name

Organization Code

LOV

sra.organization = :p_assign_org