IV Available Quantity Report - SQL Pivot Function

I have encountered a case in which a customer requested a certain report to be viewed in a certain format. The essence is that the values stored in Rows should be transferred into Columns, as Shown below:

Fortunately, SQL has the "Pivot" function which serves the need just to the point,
"Pivot Function rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output"
The script below enhances the use of "pivot" function in an Inventory Case Study in order to calculate the Quantity on Hand for each item per Location, as shown below;

Quantity on Hand per Item per Site - No Pivot Function

Quantity on Hand per Item per Site - Using Pivot Function

The functionality of pivot function is illustrated through an example on Historical Inventory Trial Balance Table SEE30303, as shown in the screenshots above. The script is tested on Fabrikam GP2013, after the HITB reset tool has been implemented to populate SEE30303 table.

-- Tables included;
    -- SEE30303 | Historical Inventory Trial Balance
USE TWO
SELECT  ITEMNMBR, 
        ISNULL(WAREHOUSE,0)   AS WAREHOUSE,
           ISNULL(RETURNS,0)  AS RETURNS,
           ISNULL(NORTH,0)  AS NORTH,
           ISNULL(DEPOT,0)   AS DEPOT
           FROM
        ( SELECT *
          FROM
              ( SELECT [TRXQTYInBase], [ITEMNMBR], [LOCNCODE] 
                FROM [SEE30303] 
                    ) P  
              PIVOT 
              (
                 SUM([TRXQTYInBase])
                 FOR [LOCNCODE] IN 
                 ([WAREHOUSE],[RETURNS],[NORTH],[DEPOT])
              ) AS PVT   
        ) AS AB

 

Best Regards,
Mahmoud M. AlSaadi

Business Portal Requisitions - Approval Hierarchy

In order to define the approval cycle workflow for creating requisitions in requisition management in Business Portal, an Approval Hierarchy must be configured defining the following roles;

• Requisition Administrator
• Requisition Approver
• Requisition Purchaser
• Requisitions Creator

Before creating the approval hierarchy, users must be assigned to one role or more of the ones mentioned above, in order to design a structure “Tree Like” that controls the workflow of the requisitions before being transferred into Purchase Order in Dynamics GP. In order to design an Approval Hierarchy, Go through the following steps;

1. On the Top-level Business Portal Site > Site Settings 
2. Go to Requisition Management
3. Click on Approval Hierarchies
4. Create a new Approval Hierarchy on the Pick Task section
5. Name your Approval Hierarchy
6. Design your Hierarchy including the Roles and Users
7.  Save 

 

Approval Hierarchy

Helping Note!

As for the Requisition Creator role specifically, even if you assign it to several users, these users must be explicitly added to the approval hierarchy in order to be able to create requisitions. If the users within the Creator role are not included in the hierarchy as shown above, they will not be granted an access to create requisitions

As for the remaining roles (Administrator, Approver and Purchaser) it is only enough to assign them to the users and these users will be granted the associated permissions.

Approval Limit

Each of the roles/ users within the Approval Hierarchy should be given an Approval Limit, you could choose among the following:

• Unlimited approval: The user or Role given this access can give a final approval regardless of the requisition amount
• Specific Amount: The user or Role given this access can give approval to requisitions which their amounts within the approval limit.

The Approval Hierarchy should be assigned to a specific Dynamics GP Company, or several GP Companies. On the other hand, a specific Dynamics GP company can be assigned to one Approval Hierarchy only.

 

 

Business Portal Requisitions - Users List WebPart

Purpose: This article would be of a great interest for those who are engaged in Microsoft Dynamics GP, SharePoint and specifically Business Portal.

One of the critical steps encountered during creating an “Approval Hierarchy” for Requisitions Management – Business Portal is “granting access to users on BP”.   

Therefore, it is quite essential to understand the user access across these integrated systems (MS GP, SP and BP).

Initially, it is of a considerable benefit to shed a light on a quick overview for the following definitions;

• SharePoint is a web-based platform that is best used for content management and collaboration across the organization, and much more.

• Business Portal – Dynamics GP utilizes the use of SharePoint sites and their contents (Lists and Libraries) in order to grant users access on GP data without the need to work on GP Client. Needless to say that Business Portal operates on the top of SharePoint Platform

• BP – Requisitions Management: an application within business portal that service organizations in entering, approving and managing purchases in order to eliminate the need for paper-based systems in handling purchase requests. Furthermore, approved purchases on BP are transferred automatically into Dynamics GP into either a new or existing purchase orders.

Unlike SharePoint, when it comes to creating an approval hierarchy within BP - requisitions management for the first time, you will encounter a semi problem illustrated with not finding your active directory users!

In order for users to be granted an access on the requisitions management, technician should be well aware of the following steps;

1. Granting users access on SharePoint sites
2. Granting users access on Business Portal

Granting users access on SharePoint sites

You can either manually add individual users or add all “domain users” into one of the following groups, depending on the business security risks

• Visitor
• Member
• Owner

To grant access on SharePoint for users, 

Go to SharePoint Home Page > Site Settings > Permissions

Granting users access on Business Portal

The problem that technician might encounter is “Not finding the Active Directory Users” on the User-List web part within Business portal. Regardless of the fact that these users are already seen by SharePoint. As shown below;

Business Portal - User List WebPart

In order to handle this issue, users shall be assigned to Microsoft Business Framework (MBF) role. On the other hand, users should have been already granted an access on SharePoint sites.

“The ability to enter requisitions is controlled by the user’s MB role assignments. The Read permission level applies only to modifying the page itself and any SharePoint content on the page.”

In order to assign users to MBF role, the following steps shall be applied;

1. On the top-level Business Portal Website > Site Settings 
2. Click on Business Portal Administration 
3. Click Users 
4. In the bottom of the left corner, Click Add New User and the user wizard appears. 
5. The first step on the User Wizard is to select the domain from which you want to create Business Portal Users 
6. Choose All Users > Next 
7. You need to assign users to Advanced Roles (Employees, Vendors, Customers .etc.) 
8. Assign Users to Portal Roles 
9. Click Finish and the new users will be added to MBF Roles.

Top Level Business Portal Website - Site Settings

 

Users List Wizar

Best Regards,

Mahmoud M. AlSaadi