Query to retrieve roles, duties and privileges information for a given user and resource in Microsoft Dynamics 365 SCM

In this blog post I would like to provide useful queries for retrieving roles, duties and privileges information. 

DECLARE @Resource varchar(250)

DECLARE @NetworkAlias varchar(250)

 

SET @Resource = '<<RESOURCE NAME>>' -- sample: BATCHABORT, SALESLINE

SET @NetworkAlias = '<<NETWORK ALIAS>>' <name>@d365operationslicensing.ccsctp.net

 

DECLARE @UserID varchar(100)

 

SELECT @UserID = ID FROM USERINFO

WHERE NETWORKALIAS = @NetworkAlias

 

1. Roles for given User

SELECT DISTINCT SECURITYROLE, SR.AOTNAME, SR.NAME 

FROM SECURITYUSERROLE SUR

JOIN SECURITYROLE SR

ON SR.RECID = SUR.SECURITYROLE

WHERE SUR.USER_ = @UserID

AND SUR.ASSIGNMENTSTATUS = 1

1a. Roles specific to a company/organization

SELECT DISTINCT 

USER_ AS [USER ID], 

SUR.SECURITYROLE AS [ROLE ID],

SR.NAME AS [ROLE NAME],

ISNULL(CSR.DATAAREA, '4ALL') AS [COMPANY]

FROM SECURITYUSERROLE SUR

LEFT JOIN SECURITYUSERROLECONDITION CSR

ON SUR.RECID = CSR.SECURITYUSERROLE

JOIN SECURITYROLE SR

ON SR.RECID = SUR.SECURITYROLE

WHERE SUR.USER_ = @UserID

AND ASSIGNMENTSTATUS = 1

 

2. Roles and sub-roles for given User 

SELECT RECID AS [SECURITYROLE], AOTNAME, NAME, 'Yes' AS [IS SUB ROLE]

FROM SECURITYROLE SR

WHERE RECID IN

(SELECT SECURITYSUBROLE

FROM SECURITYSUBROLE 

WHERE SECURITYROLE IN

(SELECT SECURITYROLE 

FROM SECURITYUSERROLE

WHERE USER_ = @UserID

AND ASSIGNMENTSTATUS = 1

))

UNION

SELECT RECID AS [SECURITYROLE], AOTNAME, NAME, 'No' AS [IS SUB ROLE]

FROM SECURITYROLE SR

WHERE RECID IN

(SELECT SECURITYROLE

FROM SECURITYSUBROLE 

WHERE SECURITYROLE IN

(SELECT SECURITYROLE 

FROM SECURITYUSERROLE

WHERE USER_ = @UserID

AND ASSIGNMENTSTATUS = 1

))

 

3. Duties assigned to given User

SELECT * 

FROM SECURITYDUTY

WHERE RECID IN 

(SELECT SECURITYDUTY 

 FROM SECURITYROLEDUTYEXPLODEDGRAPH SDE

 JOIN SECURITYUSERROLE SUR

 ON SUR.SECURITYROLE = SDE.SECURITYROLE AND SUR.ASSIGNMENTSTATUS = 1

 WHERE SUR.USER_ = @UserID

)

 

4. Privileges for given User (this includes privileges assigned via duties and subrole)

SELECT DISTINCT 

SP.IDENTIFIER,

SP.NAME AS [PRIVILEGE]

FROM SECURITYPRIVILEGE SP

JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP

ON MAP.SECURITYPRIVILEGE = SP.RECID

WHERE MAP.SECURITYROLE 

IN (SELECT SECURITYROLE FROM SECURITYUSERROLE

WHERE USER_ = @UserID

AND ASSIGNMENTSTATUS = 1)

 

5. Permissions and grants for given User (this includes privileges assigned via duties)

SELECT DISTINCT SECURABLETYPE,

MAP.SECURITYROLE AS [ROLEID],

RES.AOTNAME AS [RESOURCE],

RES.AOTCHILDNAME AS [CHILD RESOURCE],

SP.NAME AS [PRIVILEGE],

RES.READACCESS,

RES.UPDATEACCESS,

RES.CREATEACCESS, 

RES.CORRECTACCESS,

RES.DELETEACCESS

FROM SECURITYRESOURCEPRIVILEGEPERMISSIONS RES

JOIN SECURITYPRIVILEGE SP

ON SP.IDENTIFIER = RES.PRIVILEGEIDENTIFIER

JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP

ON MAP.SECURITYPRIVILEGE = SP.RECID

WHERE MAP.SECURITYROLE 

IN (SELECT SECURITYROLE FROM SECURITYUSERROLE

WHERE USER_ = @UserID

AND ASSIGNMENTSTATUS = 1)

 

6. Permission for a given User and Resource (this includes privileges assigned via duties)

SELECT DISTINCT SECURABLETYPE,

MAP.SECURITYROLE AS [ROLEID],

RES.AOTNAME AS [RESOURCE],

RES.AOTCHILDNAME AS [CHILD RESOURCE],

SP.NAME AS [PRIVILEGE],

RES.READACCESS,

RES.UPDATEACCESS,

RES.CREATEACCESS, 

RES.CORRECTACCESS,

RES.DELETEACCESS

FROM SECURITYRESOURCEPRIVILEGEPERMISSIONS RES

JOIN SECURITYPRIVILEGE SP

ON SP.IDENTIFIER = RES.PRIVILEGEIDENTIFIER

JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP

ON MAP.SECURITYPRIVILEGE = SP.RECID

WHERE MAP.SECURITYROLE 

IN (SELECT SECURITYROLE FROM SECURITYUSERROLE

WHERE USER_ = @UserID

AND ASSIGNMENTSTATUS = 1)

AND RES.AOTNAME = @Resource

 

7. Permissions required for given Resource

SELECT DISTINCT SECURABLETYPE,

MAP.SECURITYROLE AS [ROLEID],

SR.NAME AS [ROLE NAME],

RES.AOTNAME AS [RESOURCE],

RES.AOTCHILDNAME AS [CHILD RESOURCE],

SP.NAME AS [PRIVILEGE],

RES.READACCESS,

RES.UPDATEACCESS,

RES.CREATEACCESS, 

RES.CORRECTACCESS,

RES.DELETEACCESS

FROM SECURITYRESOURCEPRIVILEGEPERMISSIONS RES

JOIN SECURITYPRIVILEGE SP

ON SP.IDENTIFIER = RES.PRIVILEGEIDENTIFIER

JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH MAP

ON MAP.SECURITYPRIVILEGE = SP.RECID

JOIN SECURITYROLE SR

ON SR.RECID = MAP.SECURITYROLE

WHERE RES.AOTNAME = @Resource

ORDER BY MAP.SECURITYROLE , SECURABLETYPE

 

8. Permissions required for given Resource (using SecurityroleRuntime table. This is used during runtime and should have same data as above)

SELECT DISTINCT TYPE, SECURITYROLE AS [ROLEID], NAME AS [RESOURCE], CHILDNAME AS [CHILD RESOURCE], CREATEACCESS, READACCESS, UPDATEACCESS, DELETEACCESS FROM SECURITYROLERUNTIME

WHERE NAME = @Resource

ORDER BY SECURITYROLE, TYPE

That's it!

Financial dimensions defaulting logic on the purchase orders created from purchase requisitions (active dimension link, merge parameters) in Microsoft Dynamics 365 SCM

In this blog post I would like to explain what capabilities we have out of the box for financial dimension setup on the purchase order/lines when creating purchase order from purchase requisition. 

When Purchase requisition is being created (without project involvement), financial dimensions are set from the following setups and in the following priority: Dimension link -> Vendor -> Item.

When PO is being created from the Purchase requisition, financial dimensions on the created purchase orders are initialized in the following way:

• Purchase order header financial dimensions are set from the vendor
• Purchase lines financial dimensions are set from the purchase requisition lines

If Merge financial dimensions from the vendor that was added after purchase requisition approval = YES (Purchasing policies > Purchase order creation and consolidation rule > Manual purchase order creation), then financial dimension from the requisition line will be merged with the financial dimensions from the vendor and set it on the purchase order line.

• Purchase order header financial dimensions are set from the vendor
• Purchase lines financial dimensions are set from the purchase requisition lines. If any fin dims are blank on purchase requisition line -> then it will be taken from vendor. 

Imagine the following situation: 

You raised a Purchase requisition with Business unit = 001, Cost center = <blank> and submitted for approval. While waiting for approval, vendors financial dimensions Cost center gets defaulted to 007. Upon purchase order creation, purchase order line will get financial dimension: Business unit = 001, Cost center = 007. Without this parameter activated, it would have set to Business unit = 001, Cost center = <blank>.

Within 10.0.25 release, a new feature has been introduced that affects the financial dimensions merge logic.

Feature name: “Merge financial dimensions from the vendor with active dimension link financial dimension on the purchase order”

Feature description: This feature allows for the merge financial dimensions from the vendor with active dimension link financial dimension after purchase requisition approval if you set up a link between a financial dimension and the site inventory dimension. Purchase order creation and demand consolidation purchasing policy rule can be setup to drive the decision for merging financial dimensions from vendor with active dimension link financial dimension on purchase order header level.

Once this feature is enabled, new parameter will be displayed Merge financial dimensions from the vendor with active dimension link financial dimension on the purchase order (Purchasing policies > Purchase order creation and consolidation rule > Manual purchase order creation).

If this parameter is enabled, the financial dimension from the vendor and dimension link will be merged together and set on the purchase order header. 

Try out the following: 

1. Enable Dimension link (Inventory management > Setup > Posting > Dimension link).

• Select the financial dimension in the Dimension link page, for instance Cost center
• Go to Sites and set Cost center value for all sites (Inventory management > Setup > Inventory breakdown > Sites)
• Go to Dimension link page and select Activate link. Wait until completed. 

2. Set Business unit = 001, Cost center = <blank> on the vendor record. 

3. Create Purchase requisition with Business unit = 001, Cost center from the site, let's say 008. 

4. Submit and Approve requisition.

5. If parameter is disabled, Purchase order header financial dimensions will be defaulted to Business unit 001, Cost center = <blank>.

If parameter is enabled, Purchase order header financial dimensions will be defaulted to Business unit 001, Cost center = 008.

That's it!

Post registered quantities of stocked products and remainders of not-stocked products for receipts and vendor invoices feature in Microsoft D365 SCM

In this blog post I would like to briefly explain what has been done in scope of the Post registered quantities of stocked products and remainders of not-stocked products for receipts and vendor invoices feature. 

Feature name: Post registered quantities of stocked products and remainders of not-stocked products for receipts and vendor invoices

Description:

This feature changes how quantities of not-stocked products (such as services) are posted when processing vendor invoices and inbound shipments against purchase orders. The "Registered and services" quantity option now works as "Registered quantity and not-stocked products" and aligns with the similarly named option already available for posting quantities for sales packing slips.

When you post a product receipt or vendor invoice using the "Registered quantity and services" quantity option, the system posts the registered quantity of stocked products, but posts the remainder quantity of not-stocked products (including both services and non-services). Without this feature, the system still posts the registered quantity of stocked products (including services configured as stocked items) but always posts the full ordered quantity of not-stocked service products (and ignores not-stocked products that are not of type "Service").

Before this feature was introduced, the following logic was in place. 

When you were trying to Post product receipt and selecting 'Registered quantity and services' quantity option on the Posting product receipt page, the system was displaying:

• registered items
• procurement category lines
• items that have product type = Service

The system was not showing the items with the type of Item and item model group with the Stocked product parameter unselected. 

Unlike Product receipt, Packing slip posting on sales orders side is slightly different and Quantity field contains another option "Picked quantity and not stocked products". If this option is selected, the system picks up the following:

• picked items
• items that have product type = Service
• items that have product type = Item and item model group has Stocked product parameter disabled.

With the help of this feature, the logic is attempted to be the same. 

With new feature enabled, when trying to Post product receipt and selecting 'Registered quantity and services' quantity, the system will be displaying:

• registered items
• items that have product type = Service
• items that have product type = Item and item model group has Stocked product parameter disabled.

That's it!

How to assign employees from other legal entities on customer or prospect in Microsoft Dynamics 365 SCM

Currently if you try to set Employee responsible on the Customer or Prospect records, the lookup is filtered by Current legal entity only. 

Walkthrough:

All Customers page

1. Go to Sales and Marketing -> Customers -> All Customers.
2. Select any existing customer and expand the Sales demographics FastTab.
3. Choose the Employee responsible field.
4. Note: The Employee responsible field on the customer is constrained by a pre-defined non-removeable filter limiting its values to workers that are/have been employed by the current legal entity only. It is not allowing to choose another legal entity workers.

All Prospect page

1. Go to Sales and Marketing -> Relationships -> Prospects -> All prospects.
2. Select any existing prospect and expand the Sales demographics.
3. Choose the Employee responsible field.
4. Note: The Employee responsible field on the customer is constrained by a pre-defined non-removeable filter limiting its values to workers that are/have been employed by the current legal entity only. It is not allowing to choose another legal entity workers.

In 10.0.29 this has been expanded with the possibility to choose the employees not only from the current legal entity but also from other legal entities. 

A new parameter has been added that controls the behavior:

1. Go to Accounts receivable -> Setup -> Accounts receivable parameters.
2. Select General Fast Tab.
3. Parameter: Assign employee responsible from other legal entities.
4. Tool tip: Set this option to Yes to assign an employee responsible to a customer from another legal entity. 

Once the parameter is set to YES, you are able to select the employees from other legal entities by changing the defaulted filter.

That's it!

Saved views for purchase orders and Enable resetting procurement related workflows features in Microsoft Dynamics 365 Supply Chain Management

In this blogpost I would like to go through some features and explain what has been done for them.

Name: Saved views for purchase orders

Description: This feature provides several saved views for the purchase order details page. The views help users to focus on relevant fields and actions while working with purchase orders. The feature adds views optimized for each of the following purposes: order creation, order approval, inventory management, and financial management.

When this feature is enabled, new views for purchase order details will be available. Go to Purchase order details, select views and then go to Manage my views.

Select Organization views. 4 new views are available.

Name: Enable resetting procurement related workflows

Description: This feature allows you to reset following workflow to draft status: Purchase Order, Vendor Change and Purchase Requisitions.

This feature makes customer can recall the workflow for PR, PO and vendor in the UI of PR, PO and vendor directly if the workflow is unrecoverable.

This works in the following way:

• Go to any procurement documents that are stuck (Purchase requisition, Purchase order, Vendor). 
• Select Workflow -> History (Note: do it with Admin role)
• Make sure that workflow status is Unrecoverable.
• Select Reset button
• After that document will be set to Draft state

That's it!

Release to warehouse in Microsoft Dynamics 365 Supply Chain Management: Shipment, wave, load, work

In this blog post I would like to go through the Release to warehouse process and show you what is happening there. Below you may find an illustration of the process. 

When you Release a sales order, the system performs a set of validations whether it can be released at all. Upon successful validation, the system starts creation of the Shipment/Shipment lines and Load lines. 

A shipment is a single delivery of the items to a single address within the same mode of delivery. One shipment might have multiple sales orders if they are for the same delivery address and mode of delivery, 

When shipment has been created, the system is looking for a wave template which matches the Shipment. When wave template has been found the system determines whether a new wave needs to be created or the shipment to be added to an existing wave. If you have not defined parameters on the wave template to create wave/or assign to the existing wave, the system will stop the process here. In this scenario, users will need manually add shipments to the wave. A wave is a group of shipments to be processed together in the warehouse and combines the warehouse work. One wave might have multiple shipments, but a single shipment can only be on one wave. 

If Wave template is configured to process wave, the shipment(s) will be released to the warehouse for picking. In other words, the system will try to create a work. But first the system will try to add shipments to a Load. A load is set of Shipments that are to be delivered at the same time (consider it as a single vehicle).  One load might have multiple shipments, but a single shipment can only be on one load. 

Work creation process starts upon Wave allocation step. At this step, the system tries to determine from where the items need to be picked up. So, system tries to construct Work pick lines. For this reason, the system searches for a location directive to determine pick location and work template to determine the policy on how pick lines need to be grouped into work headers.  Then the system determines where to put the items. So, the system constructs put line for your work. Once it is determined the system creates a work(s) with pick and put pair. 

If Wave template is configured to release the wave automatically, created work is unblocked and warehouse workers can start picking and putting items using mobile device. 

That's it!

Vendor bank information enhancements in Microsoft Dynamics 365 SCM

If you are using Maintain vendor bank information using vendor collaboration workspace feature, you might experience some issue with entering bank details. For example, prior 10.0.28 when you create a new bank account in the vendor collaboration for non-USD vendors, IBAN and Swift code fields were mandatory. 

In 10.0.28 IBAN and Swift code fields became optional by default, but a new parameter was introduced that controls whether SWIFT and IBAN are required for non-USD vendors. Keep in mind that this parameter is applied only to the bank accounts created from the vendor collaboration (not from the vendor)

1. Go to Accounts payable -> Setup -> Accounts payable parameters.
2. Select General Fast Tab
3. Parameter: Require SWIFT and IBAN for non-US vendors.

Walkthrough:

1. Go to Vendor collaboration -> Workspaces -> Vendor information.
2. Select Vendor that has not USD currency.
3. Select More details button.
4. Choose Bank information.
5. Select Add button
6. Based on the "Require SWIFT and IBAN for non-US vendors", IBAN and SWIFT fields will be mandatory or optional. 

That's it!