Tuesday, October 17, 2017

Dynamics NAV - "Unit of Measure does not exist" error when calculating warehouse movements for inventory journals

This is happening because a transaction exists in the whse journals with no uofm


update  [CRONUS].[dbo].[CRONUS$Warehouse Entry] set [Unit of Measure Code] = 'EA' where [Unit of Measure Code] = ''


update [CRONUS].[dbo].[CRONUS$Warehouse Journal Line] set [Unit of Measure Code] = 'EA' where [Unit of Measure Code] = ''

Tuesday, October 10, 2017

Dynamics GP - Field Service - How to find out Which User Created/Modified an Equipment ID?

Activity Tracking does not track any of the Field Service Tables.
The only thing it will track is opening and closing of the windows.

This table has a log of equipment number creation / modification.
It will only track changes to the reference number, and no other fields.
It does not track the value of the reference numbers, only whether or not it was changed.

select * from svc00310

or

SELECT        EQUIPID, LNITMSEQ, DATE1, TIME1, USERID, From_Customer_Number, To_Customer_Number, From_Serial_Number, To_Serial_Number, From_Item_Number, To_Item_Number, DSCRIPTN, DEX_ROW_ID
FROM            SVC00310

Sunday, October 8, 2017

Samsung un40f5500af - Turns off by itself.

Left the TV off for a few hours
Changed the batteries in the remote
It came back on all by itself
Not sure if it was trying to update itself, it says it has firmware 2207.
I'm not sure what the latest version is supposed to be, but it also says the last time it checked for updated was 2015

Friday, October 6, 2017

Dynamics GP - Purchase Receipt does not post correctly, causes PO to think it's received, but there is no stock

Situation: PO has been created, PO receipt is done and posted, but posting fails. The PO line says it's completely received, the receipt transaction is corrupted, and there is no inventory available from the receipt.

Solution:In this case, the PO receipt actually did not post, and we just have to recover the corrupt transaction and delete it.

--Get the Receipt number for the PO
select * from pop10310 where ponumber = 'PO000000000022484'

--Get the Receipt Header
select * from pop10300 where poprctnm = 'GSR00000000068562'

--Update the VENDORID and POPTYPE = 1 if they are blank to allow you to access the transaction in the receiving work screen
--Get the vendor id
select vendorid from pop10100 where ponumber = 'PO000000000022484'

update pop10300 set VENDORID  = 'APAS01', POPTYPE = 1 where POPRCTNM =  'GSR00000000068562'

After running the scripts, pull up the transaction in the purchase receipt screen and delete it.
This should free up the PO and allow you to receive the lines properly.

Thursday, October 5, 2017

Check Network Traffic and Connectivity issues - Microsoft Nework Monitor, Microsoft Message Analyzer

https://www.microsoft.com/en-us/download/details.aspx?id=4865


Use Filters
https://blogs.technet.microsoft.com/rmilne/2016/08/11/network-monitor-filter-examples/

Property.Source >= "MYPC" and Property.Source <= "MYPD"



A Newer version of this program with better message reporting is available here
https://www.microsoft.com/en-us/download/details.aspx?id=44226



Or you can also use Wireshark
https://www.wireshark.org/download.html

Dynamics GP - You cannot receive this purchase order line item because it hasn't been encumbered by encumbrance management. Please encumber the purchase in the purchase order entry window.

There is nowhere on the purchase order entry window to physically encumber this.

  • Encumbrance occurs when any one of the following occurs
    • Workflow approves PO line
    • PO approval Approves PO line
    • Mass Encumbrance processes PO line
Problem: The ENC10110.ENCBSTAT is set to 4, and is waiting for Approval or workflow, but neither of these are enabled, so the line can never be encumbered.


Solution:Set the ENC10110.ENCBSTAT to 2 to enable it in the Mass purchasing>transaction>Mass Encumbrance screen to complete the approval before it can be received.

 --Use this to get the dex_row_id's for the lines that cannot be received

select * from enc10110 where ponumber = ' PO00293'

--Use this to update the lines to enable them in the Mass Encumbrance window for processing
--Change the dex_row id's to match the lines that cannot be received
update ENC10110 set ENCBSTAT = 2 where DEX_ROW_ID in (298800,298811)

Dynamics GP - Instructor Training Manuals

https://mbs.microsoft.com/partnersource/northamerica/readiness-training/instructor-training-materials/

Monday, October 2, 2017

Dynamics GP - Encumbrance Tables

https://dyndeveloper.com/DynTable.aspx?ModuleID=ENC

  • ENC10110 - Encumbrance - PO Lines
    • ENCBSTAT
      • 1-Pre-Encumbered - After approved or mass encumbered
      • 2-Invalid - Default setting. Displays trx in Mass encumbrance for encumbrance.
      • 3-Blanket Control - Used for blanket control lines
      • 4-Pre-Budget - Default setting before approval if approvals in use
  • ENC10500 - Encumbrance Received Trx

Dynamics NAV - LS Retail - How to setup Object Transfer


  • Ensure your object is compiled
  • Search for Object Transfer
    • Create a new object transfer for your objects
    • Confirm it
  • Search for Jobs
    • Create a Job 
    • with type "Object Replication"
    • using codeunit 99001475
    • set your location destinations
    • Under object replication fasttab, add your object job
    • set a schedule, or click run

Friday, September 29, 2017

Dynamics GP - Last time an SOP Transaction was saved/printed/accessed

The DEX_ROW_TS will update to the current time every time a record is saved, printed or otherwise accessed in GP.

Dynamics GP - eOne Extender - Extender Windows do not open after importing solution

Check the DYNAMICS..EXT00001
It should have an entry for each object.
If it is missing ,create the required records for the company you're in

Thursday, September 28, 2017

Dynamics GP - SQL View - Receipt PO Numbers for posted and unposted receipts


/** This view will get the min PO number from the posted and unposted receipts
**** Object:  View [dbo].[BI_PORcts]    Script Date: 9/28/2017 10:25:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_PORcts]
AS
SELECT        POPRCTNM, PONum
FROM            (SELECT        POPRCTNM, MIN(PONUMBER) AS PONum
                          FROM            dbo.POP30310
                          GROUP BY POPRCTNM
                          UNION
                          SELECT        POPRCTNM, MIN(PONUMBER) AS PONum
                          FROM            dbo.POP10310
                          GROUP BY POPRCTNM) AS RctPO

GO


Wednesday, September 27, 2017

eOne Smartview - Smartview tables - security access to smartview favorites, lock or unlock favorites

Sometimes, if you accidentally remove access to SA for a smartview, you can never get back in to see it again.

You can manually update the security tables to give access again

You can also use the SV00108 table to force-lock favorites to prevent anyone from making changes.
  • dynamics.dbo.sv00100 - Favorites index
  • dynamics.dbo.sv00101 - Favorites layout
  • dynamics.dbo.sv00102 - Favorites restrictions
  • dynamics.dbo.sv00103 - Favorites grouping
  • dynamics.dbo.sv00104 - Favorites to Company link
  • dynamics.dbo.sv00105 - Favorites security roles (insert security records here to grant access)
  • dynamics.dbo.sv00108 - Favorite Lock (Put your favorite id here to lock it from changes, remove it to allow changes)
  • dynamics.dbo.sv00110 - Favorites status

Friday, September 22, 2017

Thursday, September 21, 2017

Dynamics GP - Smartlist Builder - Go To Triggers do not fire in the correct order

Sometimes certain scripts run when the window opens, causing unpredictable behaviour with the way the go to triggers will submit data to the open window.

Most times, if you leave the window open and click the go to a second time, the trigger will work as expected since the code that runs on window open has already run.


  • If using a macro, ensure there is one blank line at the end of the macro to ensure the last macro line fires.
  • when using a macro, smartlist builder does not wait for the macro to finish before moving on to the next step, which causes conflicts if the macro is affecting fields that smartlist builder is trying to update.
    • The wait command stops the entire string of commands, and does not wait after each step
    • Either do not use macros, and try to use smartlist builder tasks, or only use macros as the last step



Dynamics GP - SOP User Defined Fields Table

select * from sop10106

Wednesday, September 20, 2017

Dynamics NAV - How to login to nav on a domain using a local account

On the local account, use Windows Credential Manager and add the NAV windows domain account and the NAV address. This will allow the local user to map to the domain NAV user seamlessly.

Dynamics GP - Receivings Transaction does not print - Receivings Edit List, POP Receivings Posting Journal

ASSESSMENT: Is the report you are printing modified or default?:

- if it is a modified report, please use the Alternate/Modified Forms and Reports window (Administration | Setup | System | Alternate/Modified Forms and Reports) to temporarily switch to the default report when testing the issue.

If the default report prints fine it tells you there is something incorrect with the modification (possibly the template) that is causing issue and we can troubleshoot in this direction on next contact.

Regarding Templates, please insure that 'Standard' is the selection in the Report Destination screen and you are also printing the Edit List out to the screen in your testing.


- If not modified there is a chance that prior temp/cache information may be interfering with the new printing if it is linked to the User/Report in question in some manner (which will not populate the Process Monitor.

To help insure this is not the case please perform the following before re-testing the issue:
1. Insure ALL USERS are out of Dynamics GP.
2. Log into SQL Server Management Studio on the SQL Server housing Dynamics GP.
3. Make a new backup of the erring company db.
4. Run the following SQL commands against the erring SQL instance:

DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION

5. Right-click and stop | start the SQL instance, which will clear the rest of the temp/cache materials in the system from past processing.
6. Log into Dynamics GP as the 'sa' user and retest the issue.


 ------------------------------------------------------------------
We have attempted these solutions, but the issue persists.

  • We are using the standard print, not the word templates
  • The receiving edit list is not modified
  • The live and TEST companies use the same dictionary, and same report, and it prints In the test company,  but not the live
    • We also tested by using a clean gp client install and clean dictionary with the same issue
  • We ran the script and restarted the server
  • We ran a complete check links and reconcile
  • We logged in and tested the print as sa, but the report does not print
-------------------------------------------------------------------
After a few days , the issue went away by itself, and the receivings edit list started printing again.
The only solutions we could think would be
  • Some corrupted transaction was deleted, posted or processed to allow the printing to continue
  • Some nightly process ran which allowed the printing to continue

Friday, September 15, 2017

Dynamics NAV - Restore NAV company to a new SQL Server - Setup test instance on different domain

Related Errors:
The Microsoft Dynamics NAV server is currently not ready to serve requests. Try again later or contact your system administrator.
  • After installing Nav, ensure that all service account information remains unchanged until you can successfully login to your restored database
  • Backup and restore company database to new server
  • Whatever account you install nav with, you must reset the sql security for that account
    • The NT AUTHORITY\NETWORK SERVICE security info will be incorrect when coming from a restore
    • If you are using the network service account for your install, you must go to SQL Manager>Security>Logins>NT Authority\NETWORK SERVICE 
      • Remove the default user mapping (to remove old network user credentials)
      • re-add the user mapping
    • Add your new administrator account to sql
      • Add it to the db_owner role

  • Ensure NAV services are configured to use new accounts
  • Use the SQL full name and user full domain names, avoid using . or localhost
  • Set new admin account as db_owner under Security>Logins>User>Properties>User Mapping
  • Drop all users to allow you to login with new admin account

------------------------------------------------------------------------
USE [MyDB]
GO
delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
---------------------------------------------------------------------------


  • Grant full access to your new admin user


USE [master]
GO
CREATE LOGIN [navdemo1\cloudadmin] FROM WINDOWS 
CREATE USER [navdemo1\cloudadmin] FOR LOGIN [navdemo1\cloudadmin] 
GRANT SELECT ON [master].[dbo].[$ndo$srvproperty] TO [navdemo1\cloudadmin]
GO
USE [CRONUS]
GO
CREATE USER [navdemo1\cloudadmin] FOR LOGIN [navdemo1\cloudadmin]
ALTER ROLE [db_owner] ADD MEMBER [navdemo1\cloudadmin]
GRANT VIEW DATABASE STATE TO [navdemo1\cloudadmin]


  • Restart SQL Service
  • Restart NAV Services

Wednesday, September 13, 2017

Dynamics GP - SQL View - Location Lookup

select locncode, locndscr, rtrim(locncode) + ' | ' + rtrim(locndscr) as loclbl from iv40700

Monday, September 11, 2017

Dynamics GP - Extended prices not working after importing prices to table

This happens because the extended pricing tables look for the EXACT sheet id value, including all of the white spaces after the id.

You need to run this script to fill in all the white space in all the extended pricing tables to ensure everything matches up.


update sop10110 set prcshid = left(prcshid + '               ',15)
update rm00500 set prcshid = left(prcshid + '               ',15)
update iv10401 set prcshid = left(prcshid + '               ',15)
update iv10402 set prcshid = left(prcshid + '               ',15)

Skype - Recording session stuck on pending


  • Plug your laptop in.
  • Click the three dots on bottom right>Manage recordings
  • The recording will start to process
  • The default location is C:\Users\Username\Videos\Lync Recordings



Dynamics GP - Myridas Catchweights



  • Overview

    • Catchweights should be used for items where quantities of items may be non-standard weights, for example chickens
      • Each chicken is a different weight
      • Customers order Qty of Chicken, and pay by the total weight
      • System must be able to track 10 chickens, but also track the total kg's of actual weight sold
      • System will sell at estimated weight per chicken and then adjust based on user entry at point of sale
    • Not compatible with Drop-Ship or Manufacturing

  • System Setup

    • Tools >> Setup >> m-hance >> Catchweights >> Catchweight Setup
      • Define default % Variance accepted between estimated and actual
      • Enable Catchweight UofM and Weight details on Item Description prints


  • Setup Catchweight UofMs

    • Cards >> m-hance >> Catchweights >> Catchweight Maintenance
      • Enter conversion rate of pieces to weight
    • Dual Quantities
      • Tick the dual quantities to track stock in both the Base uofm and the additional Uofm's setup (Eg. Base is in Kilos, other units can be EACH or CASES)
      • System will track the stock independently
      • Stock counts and transactions will require you to enter KG AND EACH qtys
      • Non-Dual Quantities means only base uofm is tracked for stock, and all other quantities will be calculated based on estimated values of uofm conversion

  • Item Setup

    • Assign Catchweight to Items
      • Cards >> m-hance >> Catchweights >> Assign Catchweight to Item Number
      • Can also use the Assign items to Catchweights-Ranges
    • Assign Individual Catchweight % to Items (optional)
      • Cards >> m-hance >> Catchweights >> Assign CPV to Item Number

  • POP Entry

    • On each line item entry, Catchweight screen prompts for Catchweight Qty, and defaults estimated Weight.
    • PO's usually are done for the estimated weight

  • POP Entry

    • On each line item entry, Catchweight screen prompts for Catchweight Qty, and defaults estimated Weight.
    • Actual weight is entered on receipt

  • SOP Invoice

    • On each line item entry, Catchweight screen prompts for Catchweight Qty, and defaults estimated Weight. User can adjust the actual weight.

    Saturday, September 9, 2017

    Dynamics GP - Returns Management - RMA Type Selected is invalid for this process


    • To create a return from an SOP document go to
      • Sales Transaction Entry>Additional>Create Return
      • If you get the error message "RMA Type Selected is invalid for this process" untick the "Create RMA" box
        • To permanently remove this default check 
          • Go to the Registration window from Tools >> Setup >> System >> Registration
          • Uncheck the Returns Management module
          • This will prevent you from using the actual Field Service Returns Management, and limit the functionality to only SOP returns
          • Click OK

    • Select Return Type ID
    • Select Customer
    • Select Posted SOP Document to return
    • Select Items to Return
    • Adjust quantities to return in necessary in the "Return Quantity" line item field
    • If you have already returned against this sop document, you will get a warning, but it will not stop you from returning against it again

    Wednesday, September 6, 2017

    eOne Smartview - Users cannot see the Smartview menu button to launch Smartview

    After installing Smartlist Builder and Smartview as administrator, confirm they are working by logging in as sa.
    For all other regular users,

    Create a security task for

    • Product: Smartview
    • Type: Windows
    • Series: Financial
    • Tick Smartview
    Also ensure that you enable the Smartlist builder security to access SQL data smartlists under
    • Product: Smartlist Builder
    • Type: Smartlist Builder Permissions
    • Series: Smartlist Builder
    • Tick View Smartlists with SQL Tables

    Windows - See what programs or processes are using or locking a file



    For Windows 7, 8 and 10 you can use the built-in Resource Monitor for this.
    1. Open Resource Monitor, which can be found
      • By searching for resmon.exe in the start menu, or
      • As a button on the Performance tab in your Task Manager
    2. Use the search field in the Associated Handles section on the CPU tab
      • Pointed at by blue arrow in screen shot below

    Monday, September 4, 2017

    Dynamics GP - "Remember User" has been checked, and user login screen no longer appears

    • GP>Tools>Setup>System>System Preferences
      • Untick Enable Remember User
    • Disable the function by editing the dex.ini
      • RememberUser=False
      • If it is not there then add this Switch to your Dex.ini and open GP again. It works for sure.
    Related: Remember this company is marked
    • delete from SY01402 where sydefaulttype = 70 and userid = 'youruser'

    Dynamics NAV - LS Retail - Custom Gift Card Import

    SPL will send list of gift card numbers for $50 and $500 cards to be uploaded into system
    The numbers are based on cards physically printed with barcodes

    Clear all values from T50008 before starting, or they will be re-uploaded
    Copy Number, Initial amount, Used Amount into Table 50008 Gift Card Staging

    Modify Codeunit 50009 Proces Staging Gift Cards, change "Receipt" variable to increment xx number by one 00000INITxx00000000

    Run C50009

    Gift card entries will be created with incrementing numbers

    Check T990015558 POS Data Entry to confirm gift cards imported correctly
    Filter by "Created by receipt No." it will contain the INIT receipt numbers

    Check T99001467 Voucher Entries to confirm gift cards imported correctly
    Filter by "receipt No." it will contain the INIT receipt numbers

    ---------------------------------------------------------------------------------
    SELECT     [CRONUS$POS Data Entry].[Entry Type], [CRONUS$POS Data Entry].[Entry Code], [CRONUS$POS Data Entry].Amount,
                          [CRONUS$POS Data Entry].[Applied Amount], [CRONUS$POS Data Entry].[Created by Receipt No_] AS RctNum_POSDataEntry,
                          [CRONUS$Voucher Entries].[Receipt Number] AS RctNum_VchEntry, [CRONUS$POS Data Entry].[Date Created],
                          [CRONUS$POS Data Entry].[Expiring Date]
    FROM         [CRONUS$POS Data Entry] LEFT OUTER JOIN
                          [CRONUS$Voucher Entries] ON [CRONUS$POS Data Entry].[Created by Receipt No_] = [CRONUS$Voucher Entries].[Receipt Number]
    WHERE     ([CRONUS$POS Data Entry].[Created by Receipt No_] LIKE N'%INIT11%')

    Friday, September 1, 2017

    Dynamics NAV - Bin Transfer - Warehouse Movement


    • Use a Reclassification Journal
      • Whse. Reclassification Journal
      • Get Bin Content to get all quantities in specified bins


      • Or Use a Warehouse Movement
        • Create Warehouse Movement Template
        • Create Movement Worksheet
        • Get Bin Content to get all quantities in specified bins
        • Create Configuration Package for Table Whse. Worksheet Line (7326)
          • Set NAME filter to Worksheet Template Name
          • Export to excel
          • Update bin Code
          • Import and Apply
        • Movement Worksheet > Create Movement
          • Print Report
        • Movements>Register Movement

      Dynamics NAV - NAS Service or Web Services do not start, and are set to stopped

      This happens when the NAS services are trying to start, but the SQL service has not started up properly as yet.

      To resolve this issue

      • Set the NAS and Web Services to Delayed Start
        • This will delay the start of these programs to 2 minutes after the last automated program has started it's startup process
          • HKLM\SYSTEM\CurrentControlSet\services\\AutoStartDelay decimal number of seconds to wait
      • Set the services>Recovery>Restart after subsequent failures
        • This will keep trying to restart the service and not stop after 3 fails

      Smartconnect and GP - Limitations

      This is a list of integrations that cannot be done, or are not straightforward when using smartconnect and GP


      Dynamics GP - How to Remove GL Transactions manually without closing the year (testing and development only)

      Tuesday, August 29, 2017

      SSRS - Display multi-value parameter as text string

      =JOIN(Parameters!type.Value,",")

      LS One - Failed to insert. Could not connect to Site Service.

      Confirm that the table schema in the company database matches the schema in the Audit database.
      Modify the Audit database if necessary.

      You may also get a "Could not connect to Site Service" error if there are any errors in the schema

      Use this script to identify and update all fields to the correct schema.
      Update the FScript and c.name where necessary




      SELECT      c.name  AS 'ColumnName'
                  ,t.name AS 'TableName','alter table test_audit.dbo.' + t.name + ' alter column ITEMID nvarchar(30) not null' as FScript
      FROM        sys.columns c
      JOIN        sys.tables  t   ON c.object_id = t.object_id
      WHERE       c.name = 'ITEMID'
      ORDER BY    TableName
                  ,ColumnName;


                           SELECT      c.name  AS 'ColumnName'
                  ,t.name AS 'TableName','alter table test.dbo.' + t.name + ' alter column ITEMID nvarchar(30) not null' as FScript
      FROM        sys.columns c
      JOIN        sys.tables  t   ON c.object_id = t.object_id
      WHERE       c.name = 'ITEMID'
      ORDER BY    TableName
                  ,ColumnName;

      Monday, August 28, 2017

      Dynamics GP - AP Check stub/remittance prints with a large number of 0 transactions

      Cause:
      There are a number of credits or other transactions applied to this invoice in addition to this cheque

      Resolution:

      • GP>Tools>Setup>Payables>List Documents on Remittance
      • Switch to "Invoices Only" instead of "All Documents" to hide non-invoice transactions


      Thursday, August 24, 2017

      Dynamics GP - Enable Backorders on all Items and Classes


      • Tools>SOP Setup>Back Order>Setup Backorder Doc Type
      • Tools>SOP Setup>Order>Assign Backorder type to Order
      • Cards>Item>Options>Enable Backorders
      • or Item Class - Enable Backorders and roll down
      • Or use these scripts

      update iv00101 set alwbkord = 1
      update iv40400 set alwbkord = 1


      Dynamics GP - Current Cost is wrong. Compare Last Received Cost to Current Cost Values

      /****** Object:  View [dbo].[BI_AUDIT_CurrentCost]    Script Date: 8/24/2017 10:39:14 AM ******/
      //This will take the lowest cost of all receipts for an item on the last date received to get around the //issue of large rounded 1-item costs

      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE VIEW [dbo].[BI_AUDIT_CurrentCost]
      AS
      SELECT        TOP (100) PERCENT dbo.IV10200.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV10200.RCTSEQNM AS LRSNM, dbo.IV10200.DATERECD, dbo.IV10200.QTYONHND, dbo.IV10200.UNITCOST,
                               dbo.IV10200.DEX_ROW_ID AS Lastentry, dbo.IV00101.CURRCOST, LRSNM.LsLowRcvCost, LRSNM.LsLowRcvCost - dbo.IV00101.CURRCOST AS CostDiff, Qty.AllQtyOnHnd
      FROM            (SELECT        ITEMNMBR, QTYONHND AS AllQtyOnHnd
                                FROM            dbo.IV00102
                                WHERE        (LOCNCODE = '')) AS Qty RIGHT OUTER JOIN
                               dbo.IV00101 ON Qty.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
                               dbo.IV10200 INNER JOIN
                                   (SELECT        IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM, MIN(IV10200_2.UNITCOST) AS LsLowRcvCost
                                     FROM            dbo.IV10200 AS IV10200_2 INNER JOIN
                                                                   (SELECT        ITEMNMBR, MAX(DATERECD) AS LDR
                                                                     FROM            dbo.IV10200 AS IV10200_1
                                                                     GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                                     GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON dbo.IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND dbo.IV10200.RCTSEQNM = LRSNM.LRSNM ON
                               dbo.IV00101.ITEMNMBR = dbo.IV10200.ITEMNMBR
      ORDER BY CostDiff
      GO

      Monday, August 21, 2017

      Dynamics NAV - LS Retail - Pharmacy - "The operation could not complete because a record in the Prescription Order Table was locked by another user. Please retry the activity." when scanning a prescription

      Codeunits involved upon prescription scan

      • T10015350 Prescription Order
        • Flowfields:Prescription Order Lines Sum: Amount, Insurance Payment, Discount amount,Customer Payment
      • C10015331 Prescription POS Connection
        • ScanPrescriptionOrder
          • GetPrescriptionOrder
          • C10015395 Pharmacy Web client
            • GetPrescriptionOrder
              • Possibly Writing to Prescription Order, and stalling while calculating flowfields, resulting in access error as code continues to populate Prescription Order
          • GetAndReservePrescriptionOrder

      Friday, August 18, 2017

      Thursday, August 17, 2017

      Dynamics GP - SQL View - Inventory Transaction Adjustment Line Detail with Reason codes, accounts and username

      /****** Object:  View [dbo].[BI_INV_ItemAdjDtl]    Script Date: 17/08/2017 10:11:09 AM ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO


      CREATE VIEW [dbo].[BI_INV_ItemAdjDtl]
      AS
      SELECT        dbo.IV30200.TRXSORCE, dbo.IV30200.IVDOCTYP, dbo.IV30200.DOCNUMBR, dbo.IV30200.DOCDATE, dbo.IV30200.BACHNUMB, dbo.IV30200.GLPOSTDT, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                               dbo.IV30300.UOFM, dbo.IV30300.TRXQTY, dbo.IV30300.UNITCOST, dbo.IV30300.EXTDCOST, dbo.IV30300.TRXLOCTN, dbo.IV30300.TRNSTLOC, dbo.IV30300.QTYBSUOM, dbo.IV30300.Reason_Code,
                               dbo.IV40300.Reason_Code_Description, dbo.GL00105.ACTNUMST AS IVActNum, dbo.GL00100.ACTDESCR AS IVActDesc, GL00105_1.ACTNUMST AS OffsetActNum, GL00100_1.ACTDESCR AS OffsetActDesc,
                               GLTrxSmry.UserNm
      FROM            dbo.IV30200 INNER JOIN
                               dbo.IV30300 ON dbo.IV30200.IVDOCTYP = dbo.IV30300.DOCTYPE AND dbo.IV30200.DOCNUMBR = dbo.IV30300.DOCNUMBR INNER JOIN
                               dbo.GL00100 ON dbo.IV30300.IVIVINDX = dbo.GL00100.ACTINDX INNER JOIN
                               dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                               dbo.GL00100 AS GL00100_1 ON dbo.IV30300.IVIVOFIX = GL00100_1.ACTINDX INNER JOIN
                               dbo.GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX INNER JOIN
                               dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                                   (SELECT        ORTRXSRC AS TrxSource, DTAControlNum AS SourceDocNum, USWHPSTD AS UserNm
                                     FROM            dbo.GL10000
                                     UNION
                                     SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                                     FROM            dbo.GL20000
                                     UNION
                                     SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                                     FROM            dbo.GL30000) AS GLTrxSmry ON dbo.IV30300.TRXSORCE = GLTrxSmry.TrxSource AND dbo.IV30300.DOCNUMBR = GLTrxSmry.SourceDocNum LEFT OUTER JOIN
                               dbo.IV40300 ON dbo.IV30300.Reason_Code = dbo.IV40300.Reason_Code


      GO


      Dynamics GP - Reason Code Table - IV40300

      Reason Code Table - IV40300

      Monday, August 14, 2017

      Eone Smartview and Smartlist builder - the multi-part identifier could not be bound

      http://www.eonesolutions.com/discussion/smartlist-builder-lists-not-populating/


      Reinstall the latest versions of Smartlist Builder and Smartview

      Globalprotect v4 server certificate verification failed

      Cause:
      The proper certificates are not installed

      Resolution:

      • After browsing to the portal to download the globalprotect client, if the site is showing a certificate error
        • Click on the error>View Certificates>Certification Path
        • It may have multiple certificates listed here. Install all certificates with a red x to the Trusted Certification Authority folder
        • Refresh the page to confirm that the certificates are working correctly

      Saturday, August 12, 2017

      Windows IRQL not less or equal BSOD


      • Download Malwarebytes and scan machine to remove any malware
      • Stop steam service
      • Stop all antivirus
      • Stop all firewall
      • Install new drivers

      Thursday, August 10, 2017

      SQL - Cannot edit job steps

      https://davidbridge.wordpress.com/2011/05/13/cannot-edit-job-steps-in-sql-server-2008-r2-management-studio-on-64-bit-windows/

      Run cmd as administrator
      Run these two lines
      This will register 64 bit DTS.dll
      c:\windows\system32\regsvr32 "c:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"
      This will register 32 bit which is required as SSMS is a 32-bit application.
      c:\windows\syswow64\regsvr32 "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dts.dll"

      SQL - getdate() without any time

      DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

      Dynamics GP - SQL View - Aged AP Payments, GL, Invoices, POs - AP Aging by Payment Date

      /****** Object:  View [dbo].[BI_AP_Apply]    Script Date: 13/09/2017 03:49:46 PM ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE VIEW [dbo].[BI_AP_Apply]
      AS
      SELECT        P.VENDORID AS Vendor_ID, PM.VENDNAME AS Vendor_Name, CASE P.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' END AS Document_Type,
                               P.DOCDATE AS Document_Date, P.VCHRNMBR AS Voucher_Number, P.DOCNUMBR AS Document_Number, P.DOCAMNT AS Document_Amount, P.TEN99AMNT AS [1099_Amount],
                               P.TEN99BOXNUMBER AS [1099_Box],
                               CASE P.TEN99TYPE WHEN 1 THEN 'Not a 1099 Vendor' WHEN 2 THEN 'Divident' WHEN 3 THEN 'Interest' WHEN 4 THEN 'Miscellaneous' WHEN 5 THEN 'Withholding' ELSE '' END AS [1099_Type],
                               COALESCE (PA.APPLDAMT, 0) AS Applied_Amount, COALESCE (PA.VCHRNMBR, '') AS Payment_Voucher_Number, COALESCE (P2.DOCNUMBR, '') AS Payment_Doc_Number, COALESCE (P2.DOCDATE, '1/1/1900')
                               AS Payment_Date, COALESCE (PA.DATE1, '1/1/1900') AS Apply_Date, COALESCE (PA.GLPOSTDT, '1/1/1900') AS Apply_GL_Posting_Date,
                               CASE PA.DOCTYPE WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' ELSE '' END AS Payment_Type, COALESCE (PA.POSTED, 'Unpaid') AS Payment_Status, P.PORDNMBR,
                               PO.DOCDATE AS PODate, PO.CURNCYID, P.DUEDATE, PM.VNDCLSID AS VendorClass, PO.POPCONTNUM AS ReqNo, P.CURTRXAM, CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) < 31 AND
                                P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) < 31 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [Current], CASE WHEN datediff(d, COALESCE (P2.DOCDATE,
                               getdate()), getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 31 AND 60 AND
                               P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [31_to_60_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 61 AND 90 AND
                               P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [61_to_90_Days], CASE WHEN datediff(d,
                               COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 91 AND 120 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 91 AND 120 AND
                               P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [91_to_120_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) > 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d,
                                P.DOCDATE, getdate()) > 120 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [120_and_Over], DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS AgingDate, DATEDIFF(d, COALESCE (P2.DOCDATE,
                                GETDATE()), GETDATE()) AS DaysOld, ISNULL(PO.SUBTOTAL, 0) AS POSubTot, ISNULL(PO.TAXAMNT, 0) AS POTaxAmt, ISNULL(PO.SUBTOTAL + PO.TAXAMNT, 0) AS POTotal
      FROM            (SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                                FROM            dbo.PM30200
                                UNION ALL
                                SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                                FROM            dbo.PM20000) AS P LEFT OUTER JOIN
                               dbo.POP10100 AS PO ON P.PORDNMBR = PO.PONUMBER LEFT OUTER JOIN
                                   (SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, CASE POSTED WHEN 0 THEN 'Unposted' ELSE 'Posted' END AS POSTED
                                     FROM            dbo.PM10200
                                     UNION
                                     SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, 'Posted' AS POSTED
                                     FROM            dbo.PM30300) AS PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN
                                   (SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                                     FROM            dbo.PM20000 AS PM20000_1
                                     UNION ALL
                                     SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                                     FROM            dbo.PM30200 AS PM30200_1) AS P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN
                               dbo.PM00200 AS PM ON P.VENDORID = PM.VENDORID
      WHERE        (P.DOCTYPE IN (1, 2, 3)) AND (P.VOIDED = 0)

      GO


      Dynamics GP - Can print to screen, but when printing to printer, nothing prints, with no error message

      When a batch is posted, 3 or 4 reports may print to screen.
      The way GP processes these tasks is as follows

      • Print Report1 to screen
      • Print Report2 to screen
      • Print Report3 to screen
      • Print Report4 to screen
      • Complete Posting
      • Submit print requests to printer
      GP will not actually submit the print jobs to the printer until all of the "print to screen"s have been processed


      If this is not the issue then try
      Backup Reports.dic first
      After ruling out printer and connectivity issues, test printing with a new dictionary.

      If this works, dictionary is corrupted.
      Remove old dictionary, reimport all customizations

      Wednesday, August 9, 2017

      Dynamics GP - Fix Note indexes on imported records

      https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/02/04/fixing-missing-note-index-values/

      This script will update all NOTEINDX fields with a value of 0 to the correct noteindx value for the @tablename defined


      /*
      ** AssignNoteIndx.sql
      **
      ** This script assigns note index values to all records
      ** in the given table that currently do not have one.
      **
      ** Edit the table name below and execute in
      ** context of appropriate company database.
      */

      /* -- parameters -- */

      Declare @tablename sysname

      /* ############## */

      set @tablename = 'RM00101' -- put the table name here

      /* ############## */


      /* -- working variables -- */
      set nocount on

      declare @tloop int
      declare @sCompanyID integer
      declare @sSessionID integer
      declare @DEX_ROW_ID integer
      declare @NewNoteIndex numeric(19,5)
      declare @sError integer
      declare @Cnt integer

      set @tloop = 1
      select @sCompanyID = CMPANYID
       from DYNAMICS.dbo.SY01500
       where INTERID = db_name()
      set @sSessionID = @@spid
      set @Cnt = 0

      /* @@@ MAINLINE @@@ */

      while @tloop = 1 begin
      set @tloop = 0

      /* -- check table exists -- */
      if object_id (@tablename) is null begin
      print 'table : ' + @tablename
      print 'does not exist in this database : ' + db_name()
      break
      end
      if @sCompanyID is null begin
      print 'No Entry in DYNAMICS system table for this company'
      break
      end

      print 'Assigning Note Index for table : ' + @tablename

      /* -- create a proc for repeated calls -- */

      exec ('create procedure #nextdex as declare @DEXROWID integer select top 1 @DEXROWID = DEX_ROW_ID from ' + @tablename + ' where NOTEINDX = 0 return isnull(@DEXROWID,0) ' )
      exec ('create procedure #assignnext (@NextIndx integer, @DEXROWID integer ) as update ' + @tablename + ' set NOTEINDX = @NextIndx where DEX_ROW_ID = @DEXROWID return @@ERROR ' )


      exec @DEX_ROW_ID = #nextdex

      while @DEX_ROW_ID > 0 begin

      begin transaction
      exec DYNAMICS.dbo.smGetNextNoteIndex @sCompanyID, @sSessionID, @NewNoteIndex OUTPUT, @sError OUTPUT
      exec #assignnext @NewNoteIndex, @DEX_ROW_ID
      commit transaction

      set @Cnt = @Cnt + 1

      exec @DEX_ROW_ID = #nextdex
      end

      print 'Number of records changed : ' + convert(varchar(8), @Cnt)


      drop procedure #nextdex
      drop procedure #assignnext

      end

      /* ###### end of script #### */

      Monday, August 7, 2017

      Friday, August 4, 2017

      Dynamics GP - Integration Manager 2016 - Root data not found, Could not access Microsoft.Dynamics.GP.IntegrationManager.ini file

      Cause: UAC is on, and IM has been installed to one location, but then uninstalled and reinstalled to another location.

      The xml files in the C:\Users\%userid%\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\16.0.0.0
      folder cannot be overwritten, even when run as administrator causing im to constantly point to the old location


      Resolution

      • Delete the following folders
        • C:\Users\%userid%\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\16.0.0.0 
      • Relaunch IM as administrator, and that should generate a new xml file to the new path

      Thursday, August 3, 2017

      Dynamics GP - Sales Comment Inquiry Zoom - Source Table and Fields



      • SY04200 - Preset comment id lines - use CMMTTEXT
      • SOP10102 - Line Comments in the blue arrow after comment id on the sop line - use CMMTTEXT
      • SOP10106 - Document Comments on the blue arrow after comment id - use CMMTTEXT

      Wednesday, August 2, 2017

      Dynamics GP - Manufacturing Views - MO Receipts

      /****** Object:  View [dbo].[BI_Mfg_MORcts]    Script Date: 8/2/2017 1:12:12 PM ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE VIEW [dbo].[BI_Mfg_MORcts]
      AS
      SELECT        dbo.MOP1100.MANUFACTUREORDER_I AS MONum, dbo.WO010032.DSCRIPTN, dbo.MOP1100.MOPRCTNM, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.MOP1100.DATERECD,
                               dbo.MOP1100.QTYRECVD, dbo.MOP1100.TO_SITE_I, dbo.MOP1100.USERID, dbo.MOP1100.RECEIPTCOSTSARRAY_10 AS BackflushCost, dbo.MOP1100.POSTED
      FROM            dbo.IV00101 RIGHT OUTER JOIN
                               dbo.WO010032 ON dbo.IV00101.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
                               dbo.MOP1100 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.MOP1100.MANUFACTUREORDER_I
      GO


      Monday, July 31, 2017

      Dynamics GP - Manufacturing Views - BOM and Exploded BOM

      /****** Object:  View [dbo].[BI_Mfg_BOM]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_BOM]
      AS
      SELECT        TOP (100) PERCENT dbo.IV00101.ITEMNMBR AS BomNumber, dbo.IV00101.ITEMDESC AS BOMName, IV00101_1.ITEMNMBR AS CmpNumber, IV00101_1.ITEMDESC AS CmpName,
                               dbo.BM010115.QUANTITY_I AS CmpQty, dbo.BM010115.UOFM AS CmpUofM, dbo.BM010415.LOCNCODE, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + dbo.IV00101.ITEMDESC AS BomLookup,
                               dbo.IV00101.ITMCLSCD, dbo.IV40400.ITMCLSDC, dbo.IVR10015.ITEMSTATUS_I, dbo.IVR10015.MAKEBUYCODE_I, dbo.IVR10015.ITEMFULFILLMETHOD,
                               CASE makebuycode_i WHEN 1 THEN 'Make' WHEN 2 THEN 'Buy' WHEN 3 THEN 'Make/Buy' END AS MakeBuy,
                               CASE itemfulfillmethod WHEN 1 THEN 'Make to Stock' WHEN 2 THEN 'Make to Order-Manual' WHEN 3 THEN 'Make to Order-Silent' END AS FulfillMethod, dbo.MN010000.NOTETEXT_I AS BomNote,
                               IV00101_1.CURRCOST AS CmpCurrCost, dbo.BM010415.BOMTYPE_I, CASE itemstatus_i WHEN 1 THEN 'Active' ELSE 'Inactive' END AS ItemEngStatus,
                               CASE WHEN iv00101.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMStatus, dbo.BM010415.EFFECTIVEDATE_I AS BOMInDate, CASE WHEN BM010415.EFFECTIVEDATE_I < getdate()
                               THEN 'Active' WHEN BM010415.EFFECTIVEDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS BOMDateStatus, CASE WHEN iv00101_1.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMCmpStatus,
                               dbo.BM010115.EFFECTIVEINDATE_I AS CmpInDate, dbo.BM010115.EFFECTIVEOUTDATE_I AS CmpOutDate, CASE WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I >= getdate()
                               THEN 'Active' WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS CmpStatus, dbo.BM010115.ALTERNATE_I AS IsAlternate,
                               dbo.BM010115.ALTERNATEPARTFOR_I AS AlternateFor
      FROM            dbo.MN010000 RIGHT OUTER JOIN
                               dbo.IVR10015 ON dbo.MN010000.NOTEINDX = dbo.IVR10015.MFGNOTEINDEX_I RIGHT OUTER JOIN
                               dbo.IV00101 LEFT OUTER JOIN
                               dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD RIGHT OUTER JOIN
                               dbo.BM010415 ON dbo.IV00101.ITEMNMBR = dbo.BM010415.ITEMNMBR LEFT OUTER JOIN
                               dbo.IV00101 AS IV00101_1 RIGHT OUTER JOIN
                               dbo.BM010115 ON IV00101_1.ITEMNMBR = dbo.BM010115.CPN_I ON dbo.BM010415.BOMCAT_I = dbo.BM010115.BOMCAT_I AND dbo.BM010415.ITEMNMBR = dbo.BM010115.PPN_I ON
                               dbo.IVR10015.ITEMNMBR = dbo.BM010415.ITEMNMBR
      GO
      /****** Object:  View [dbo].[BI_Mfg_ActiveBomCmps]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_ActiveBomCmps]
      AS
      SELECT        BomNumber AS BN, CmpNumber AS CN, ItemEngStatus, BOMStatus, BOMDateStatus, BOMCmpStatus, CmpStatus
      FROM            dbo.BI_Mfg_BOM
      WHERE        (ItemEngStatus = 'active') AND (BOMStatus = 'active') AND (BOMDateStatus = 'active') AND (BOMCmpStatus = 'active') AND (CmpStatus = 'active')
      GO
      /****** Object:  View [dbo].[BI_Mfg_BOM2]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_BOM2]
      AS
      SELECT        dbo.BI_Mfg_BOM.BomNumber, dbo.BI_Mfg_BOM.BOMName, dbo.BI_Mfg_BOM.CmpNumber, dbo.BI_Mfg_BOM.CmpName, dbo.BI_Mfg_BOM.CmpQty, dbo.BI_Mfg_BOM.CmpUofM, dbo.BI_Mfg_BOM.LOCNCODE,
                               dbo.BI_Mfg_BOM.BomLookup, dbo.BI_Mfg_BOM.ITMCLSCD, dbo.BI_Mfg_BOM.ITMCLSDC, dbo.BI_Mfg_BOM.ITEMSTATUS_I, dbo.BI_Mfg_BOM.MAKEBUYCODE_I, dbo.BI_Mfg_BOM.ITEMFULFILLMETHOD,
                               dbo.BI_Mfg_BOM.MakeBuy, dbo.BI_Mfg_BOM.FulfillMethod, dbo.BI_Mfg_BOM.BomNote, dbo.BI_Mfg_BOM.CmpCurrCost, dbo.BI_Mfg_BOM.BOMTYPE_I, dbo.BI_Mfg_BOM.ItemEngStatus,
                               dbo.BI_Mfg_BOM.BOMStatus, dbo.BI_Mfg_BOM.BOMInDate, dbo.BI_Mfg_BOM.BOMDateStatus, dbo.BI_Mfg_BOM.BOMCmpStatus, dbo.BI_Mfg_BOM.CmpInDate, dbo.BI_Mfg_BOM.CmpOutDate,
                               dbo.BI_Mfg_BOM.CmpStatus, dbo.BI_Mfg_BOM.IsAlternate, dbo.BI_Mfg_BOM.AlternateFor, CASE WHEN CN IS NULL THEN 'Inactive' ELSE 'Active' END AS IsActive
      FROM            dbo.BI_Mfg_BOM LEFT OUTER JOIN
                               dbo.BI_Mfg_ActiveBomCmps ON dbo.BI_Mfg_BOM.BomNumber = dbo.BI_Mfg_ActiveBomCmps.BN AND dbo.BI_Mfg_BOM.CmpNumber = dbo.BI_Mfg_ActiveBomCmps.CN
      GO
      /****** Object:  View [dbo].[BI_Mfg_BOM_L1]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_BOM_L1]
      AS
      SELECT        L0.BomNumber AS ParentBomNo, L1.BomNumber AS CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.cmpnumber ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                               THEN l0.cmpqty ELSE L1.CmpQty * L0.CmpQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                               THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
      FROM            dbo.BI_Mfg_BOM2 AS L0 LEFT OUTER JOIN
                               dbo.BI_Mfg_BOM2 AS L1 ON L0.CmpNumber = L1.BomNumber
      GO
      /****** Object:  View [dbo].[BI_Mfg_BOM_L2]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_BOM_L2]
      AS
      SELECT        L0.ParentBomNo, L0.CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.fnum ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                               THEN l0.fqty ELSE L1.CmpQty * L0.fQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                               THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
      FROM            dbo.BI_Mfg_BOM_L1 AS L0 LEFT OUTER JOIN
                               dbo.BI_Mfg_BOM2 AS L1 ON L0.FNum = L1.BomNumber
      GO
      /****** Object:  View [dbo].[BI_Mfg_BOM_L3]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_BOM_L3]
      AS
      SELECT        L0.ParentBomNo, L0.CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.fnum ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                               THEN l0.fqty ELSE L1.CmpQty * L0.fQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                               THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
      FROM            dbo.BI_Mfg_BOM_L2 AS L0 LEFT OUTER JOIN
                               dbo.BI_Mfg_BOM2 AS L1 ON L0.FNum = L1.BomNumber
      GO
      /****** Object:  View [dbo].[BI_Mfg_BOM_Exploded]    Script Date: 8/2/2017 11:35:48 AM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_BOM_Exploded]
      AS
      SELECT        dbo.BI_Mfg_BOM_L3.ParentBomNo, dbo.IV00101.ITEMDESC AS ParentBOMDesc, dbo.BI_Mfg_BOM_L3.FNum AS CmpNum, IV00101_1.ITEMDESC AS CmpDesc, dbo.BI_Mfg_BOM_L3.FQty AS CmpTotalQty,
                               dbo.BI_Mfg_BOM_L3.IsActive, dbo.BI_Mfg_BOM_L3.IsAlternate, dbo.BI_Mfg_BOM_L3.AlternateFor, IV00101_1.CURRCOST * dbo.BI_Mfg_BOM_L3.FQty AS CmpExtCost, IV00101_1.CURRCOST AS CmpUnitCost,
                               RTRIM(dbo.BI_Mfg_BOM_L3.ParentBomNo) + ' | ' + RTRIM(dbo.IV00101.ITEMDESC) AS ParentLabel, RTRIM(dbo.BI_Mfg_BOM_L3.FNum) + ' | ' + RTRIM(IV00101_1.ITEMDESC) AS CmpLabel,
                               CASE WHEN rt010001.itemnmbr IS NULL THEN 'No' ELSE 'Yes' END AS HasRouting, dbo.RT010001.ROUTINGNAME_I AS Routing
      FROM            dbo.RT010001 RIGHT OUTER JOIN
                               dbo.IV00101 ON dbo.RT010001.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
                               dbo.BI_Mfg_BOM_L3 LEFT OUTER JOIN
                               dbo.IV00101 AS IV00101_1 ON dbo.BI_Mfg_BOM_L3.FNum = IV00101_1.ITEMNMBR ON dbo.IV00101.ITEMNMBR = dbo.BI_Mfg_BOM_L3.ParentBomNo

      Friday, July 28, 2017

      Dynamics GP - SOP Open and Hist Sales View 2017

      /****** Object:  View [dbo].[BI_SOP_Hist]    Script Date: 8/16/2017 1:32:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_SOP_Hist]
      AS
      SELECT        TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                                CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
                               CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
                                dbo.SOP30200.CUSTNMBR AS [Customer Number],
                               CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
                                5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
                               dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
                               (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
                               ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
                               dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
                               AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
                               YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
                               + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
                               RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                               WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
                               '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
                               DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                                CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
                               dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice,
                               CASE WHEN sop30200.soptype = 4 THEN sop30300.MRKDNAMT * - 1 ELSE sop30300.MRKDNAMT END AS MarkdownAmt,
                               CASE WHEN sop30200.soptype = 4 THEN sop30300.TRDISAMT * - 1 ELSE sop30300.TRDISAMT END AS DiscountAmt, CASE WHEN sop30200.soptype = 4 THEN (sop30300.UNITPRCE * SOP30300.qtytoinv)
                               * - 1 ELSE (sop30300.UNITPRCE * SOP30300.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop30200.soptype = 4 THEN sop30300.taxamnt * - 1 ELSE sop30300.taxamnt END AS TaxAmt
      FROM            dbo.RM00101 INNER JOIN
                               dbo.SOP30200 INNER JOIN
                               dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
                               dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
                               dbo.IV40400 INNER JOIN
                               dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                               dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                               dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                               dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
      WHERE        (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
      ORDER BY [GL Post Date] DESC

      GO
      /****** Object:  View [dbo].[BI_SOP_Open]    Script Date: 8/16/2017 1:32:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_SOP_Open]
      AS
      SELECT        TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
                                CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
                               CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
                               CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
                               CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
                                = 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
                               dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
                               (CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
                               ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
                               dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
                               AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
                               YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
                               + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
                               RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                               WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
                               '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
                               DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                                CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
                               dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice,
                               CASE WHEN sop10100.soptype = 4 THEN sop10200.MRKDNAMT * - 1 ELSE sop10200.MRKDNAMT END AS MarkdownAmt,
                               CASE WHEN sop10100.soptype = 4 THEN sop10200.TRDISAMT * - 1 ELSE sop10200.TRDISAMT END AS DiscountAmt, CASE WHEN sop10100.soptype = 4 THEN (sop10200.UNITPRCE * SOP10200.qtytoinv)
                               * - 1 ELSE (sop10200.UNITPRCE * SOP10200.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop10100.soptype = 4 THEN sop10200.taxamnt * - 1 ELSE sop10200.taxamnt END AS TaxAmt
      FROM            dbo.RM00101 INNER JOIN
                               dbo.SOP10100 INNER JOIN
                               dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
                               dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
                               dbo.IV40400 INNER JOIN
                               dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                               dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                               dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                               dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
      WHERE        (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
      ORDER BY [GL Post Date] DESC

      GO
      /****** Object:  View [dbo].[BI_SOP_ALLSOP]    Script Date: 8/16/2017 1:32:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_SOP_ALLSOP]
      AS
      SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code], [Customer Name], [Customer Class],
                               [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, [Item Number], [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB,
                               UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth, DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR,
                               MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, QtytoBackOrder, LNITMSEQ, ReqShipDate, QtyToInvoice, MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
      FROM            (SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
                                                          [Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
                                                          [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
                                                          DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
                                                          MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
                                FROM            dbo.BI_SOP_Open
                                UNION
                                SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
                                                         [Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
                                                         [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
                                                         DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
                                                         MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
                                FROM            dbo.BI_SOP_Hist) AS ALLSOP

      GO

      Dynamics GP - Manufacturing - Order-MOPSOP Link-MO-Received-Invoice View

      /****** Object:  View [dbo].[BI_Mfg_MOSOLink]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_MOSOLink]
      AS
      SELECT        SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QtyProcess AS MOQty, QtyMatch AS QtyAllocatedToSO, InHouseDueDt, PromiseDate
      FROM            (SELECT        SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, 0 AS QtyProcess, 0 AS QtyMatch, SOITEMDUEDATE_I AS InHouseDueDt,
                                                          SOITEMPROMISEDATE_I AS PromiseDate
                                FROM            dbo.IS030001
                                UNION
                                SELECT        SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QTYProcess, QTYMATCH, SOITEMDUEDATE_I AS InHouseDueDt,
                                                         SOITEMPROMISEDATE_I AS PromiseDate
                                FROM            dbo.IS010001) AS MOSO
      WHERE        (MANUFACTUREORDER_I > '')
      GO
      /****** Object:  View [dbo].[BI_SOP_Hist]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_SOP_Hist]
      AS
      SELECT        TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                                CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
                               CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
                                dbo.SOP30200.CUSTNMBR AS [Customer Number],
                               CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
                                5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
                               dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
                               (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
                               ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
                               dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
                               AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
                               YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
                               + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
                               RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                               WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
                               '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
                               DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                                CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
                               dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice, dbo.SOP30200.NOTEINDX,
                               dbo.SOP30200.PCKSLPNO AS PackNo, dbo.SOP30200.PICTICNU AS PickNo
      FROM            dbo.RM00101 INNER JOIN
                               dbo.SOP30200 INNER JOIN
                               dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
                               dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
                               dbo.IV40400 INNER JOIN
                               dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                               dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                               dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                               dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
      WHERE        (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
      ORDER BY [GL Post Date] DESC
      GO
      /****** Object:  View [dbo].[BI_SOP_Open]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_SOP_Open]
      AS
      SELECT        TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
                                CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
                               CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
                               CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
                               CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
                                = 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
                               dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
                               (CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
                               ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
                               dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
                               AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
                               YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
                               + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
                               RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                               WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
                               '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
                               DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                                CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
                               dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice, dbo.SOP10100.NOTEINDX,
                               dbo.SOP10100.PCKSLPNO AS PackNo, dbo.SOP10100.PICTICNU AS PickNo
      FROM            dbo.RM00101 INNER JOIN
                               dbo.SOP10100 INNER JOIN
                               dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
                               dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
                               dbo.IV40400 INNER JOIN
                               dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                               dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                               dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                               dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
      WHERE        (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
      ORDER BY [GL Post Date] DESC
      GO
      /****** Object:  View [dbo].[BI_SOP_ALLSOP]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_SOP_ALLSOP]
      AS
      SELECT        ALLSOP.DOCID, ALLSOP.ORIGNUMB, ALLSOP.[SOP Number], ALLSOP.[Document Date], ALLSOP.[GL Post Date], ALLSOP.Sales, ALLSOP.Costs, ALLSOP.Qty, ALLSOP.[Customer Number], ALLSOP.[SOP Type],
                               ALLSOP.[Item Class Description], ALLSOP.[Item Class Code], ALLSOP.[Customer Name], ALLSOP.[Customer Class], ALLSOP.[Base Unit Of Measure], ALLSOP.[Units Sold], ALLSOP.STATE, ALLSOP.PRSTADCD,
                               ALLSOP.[Salesperson ID], ALLSOP.USCATVLS_1, ALLSOP.USCATVLS_2, ALLSOP.USCATVLS_3, ALLSOP.[Item Number], ALLSOP.[Item Description], ALLSOP.ShipToName, ALLSOP.Category, ALLSOP.UNITCOST,
                               ALLSOP.CREATDDT, ALLSOP.AB, ALLSOP.UNITPRCE, ALLSOP.[Sales Person Department], ALLSOP.[Sales Person First Name], ALLSOP.[Sales Person Last Name], ALLSOP.DocYear, ALLSOP.DocMonth,
                               ALLSOP.DocYrMonth, ALLSOP.CusIdName, ALLSOP.ItemIdName, ALLSOP.UOFM, ALLSOP.Div, ALLSOP.Salsterr, ALLSOP.SRNm, ALLSOP.CusClassDiv, ALLSOP.CLASDSCR, ALLSOP.MODIFDT, ALLSOP.ItemAge,
                               ALLSOP.COUNTRY, ALLSOP.BQty, ALLSOP.NatCusNm, ALLSOP.NatCus, ALLSOP.LocExpCus, ALLSOP.SOPTYPE, ALLSOP.QtytoBackOrder, ALLSOP.LNITMSEQ, ALLSOP.ReqShipDate, ALLSOP.QtyToInvoice,
                               RTRIM(CAST(dbo.SY03900.TXTFIELD AS varchar)) AS SOPNote, dbo.SOP10106.USRDAT01, dbo.SOP10106.USRDAT02, dbo.SOP10106.USRTAB01, dbo.SOP10106.USRTAB09, dbo.SOP10106.USRTAB03,
                               dbo.SOP10106.USERDEF1, dbo.SOP10106.USERDEF2, dbo.SOP10106.USRDEF03, dbo.SOP10106.USRDEF04, dbo.SOP10106.USRDEF05, dbo.SOP10106.COMMENT_1, dbo.SOP10106.COMMENT_2,
                               dbo.SOP10106.COMMENT_3, dbo.SOP10106.COMMENT_4, dbo.SOP10106.CMMTTEXT, ALLSOP.PackNo AS PackingSlip, ALLSOP.PickNo AS PickTicket
      FROM            (SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
                                                          [Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
                                                          [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
                                                          DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
                                                          NOTEINDX, PackNo, PickNo
                                FROM            dbo.BI_SOP_Open
                                UNION
                                SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
                                                         [Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
                                                         [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
                                                         DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
                                                         NOTEINDX, PackNo, PickNo
                                FROM            dbo.BI_SOP_Hist) AS ALLSOP LEFT OUTER JOIN
                               dbo.SOP10106 ON ALLSOP.SOPTYPE = dbo.SOP10106.SOPTYPE AND ALLSOP.[SOP Number] = dbo.SOP10106.SOPNUMBE LEFT OUTER JOIN
                               dbo.SY03900 ON ALLSOP.NOTEINDX = dbo.SY03900.NOTEINDX
      GO
      /****** Object:  View [dbo].[BI_Mfg_SO]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_SO]
      AS
      SELECT        dbo.BI_SOP_ALLSOP.SRNm, dbo.BI_SOP_ALLSOP.DOCID AS SODocID, dbo.BI_SOP_ALLSOP.ORIGNUMB AS SOOrigNum, dbo.BI_SOP_ALLSOP.[SOP Number] AS SODocNum,
                               dbo.BI_SOP_ALLSOP.[Document Date] AS SODate, dbo.BI_SOP_ALLSOP.Qty AS SOQty, dbo.BI_SOP_ALLSOP.[Item Number], dbo.BI_SOP_ALLSOP.[Item Description],
                               dbo.BI_SOP_ALLSOP.SOPTYPE AS SODocType, dbo.BI_SOP_ALLSOP.Sales AS SOSls, dbo.BI_SOP_ALLSOP.Costs AS SOCost, dbo.BI_SOP_ALLSOP.QtytoBackOrder, dbo.BI_SOP_ALLSOP.LNITMSEQ,
                               dbo.BI_SOP_ALLSOP.ReqShipDate AS SOReqShipDate, dbo.BI_SOP_ALLSOP.[Customer Number] AS SOCusNum, dbo.BI_SOP_ALLSOP.[Customer Name] AS SOCusName, dbo.BI_SOP_ALLSOP.QtyToInvoice,
                               dbo.BI_SOP_ALLSOP.SOPNote, dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, dbo.BI_SOP_ALLSOP.USERDEF1 AS SOUD1, dbo.BI_SOP_ALLSOP.USERDEF2 AS SOUD2,
                               dbo.BI_SOP_ALLSOP.USRDEF03 AS SOUD3, dbo.BI_SOP_ALLSOP.CMMTTEXT AS SOCMT
      FROM            dbo.BI_SOP_ALLSOP INNER JOIN
                               dbo.BI_Mfg_MOSOLink ON dbo.BI_SOP_ALLSOP.[SOP Number] = dbo.BI_Mfg_MOSOLink.SOPNUMBE
      WHERE        (dbo.BI_SOP_ALLSOP.SOPTYPE = 2)
      GO
      /****** Object:  View [dbo].[BI_Mfg_SI]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_SI]
      AS
      SELECT        DOCID AS SIDocID, ORIGNUMB AS SIOrigNum, [SOP Number] AS SIDocNum, SOPNote AS SINote, [Document Date] AS SIDocDate, Qty AS SIQty, [Item Number], [Item Description], SOPTYPE AS SIDocType,
                               Sales AS SISls, Costs AS SICost, LNITMSEQ, USERDEF1 AS SIUD1, USERDEF2 AS SIUD2, USRDEF03 AS SIUD3, CMMTTEXT AS SICMT, PackingSlip, PickTicket, PackingSlip AS DeliveryNote
      FROM            dbo.BI_SOP_ALLSOP
      WHERE        (SOPTYPE = 3) AND (Qty <> 0)
      GO
      /****** Object:  View [dbo].[BI_Mfg_MO]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_MO]
      AS
      SELECT        dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.DSCRIPTN, dbo.WO010032.MANUFACTUREORDERST_I AS MOStatus, dbo.WO010032.ITEMNMBR, dbo.WO010032.ARCHIVED_MO_I,
                               dbo.WO010032.BOMCAT_I, dbo.WO010032.BOMNAME_I, dbo.WO010032.ROUTINGNAME_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.STARTQTY_I, dbo.WO010032.STRTDATE, dbo.WO010032.STARTTIME_I,
                               dbo.WO010032.ENDDATE, dbo.WO010032.DRAWFROMSITE_I, dbo.WO010032.CHANGEDATE_I, dbo.WO010032.USERID, dbo.WO010032.SCHEDULEMETHOD_I, dbo.WO010032.PROJEMPLOYEEHRSSUM_I,
                               dbo.WO010032.PROJMACHINEHRSSUM_I, dbo.WO010032.MATPROJCOSTI_1, dbo.WO010032.MATPROJCOSTI_2, dbo.WO010032.MATFIXOHDPROJCOSTI_1, dbo.WO010032.MATFIXOHDPROJCOSTI_2,
                               dbo.WO010032.MATVAROHDPROJCOST_1, dbo.WO010032.MATVAROHDPROJCOST_2, dbo.WO010032.LABPROJCOSTI_1, dbo.WO010032.LABPROJCOSTI_2, dbo.WO010032.LABFIXOHDPROJCOSTI_1,
                               dbo.WO010032.LABFIXOHDPROJCOSTI_2, dbo.WO010032.LABVAROHDPROJCOSTI_1, dbo.WO010032.LABVAROHDPROJCOSTI_2, dbo.WO010032.MACHPROJCOSTI_1, dbo.WO010032.MACHPROJCOSTI_2,
                               dbo.WO010032.MACHFIXOHDPROJCOSTI_1, dbo.WO010032.MACHFIXOHDPROJCOSTI_2, dbo.WO010032.MACHVAROHDPROJCOSTI_1, dbo.WO010032.MACHVAROHDPROJCOSTI_2,
                               dbo.WO010032.POSTTOSITE_I, dbo.WO010032.LOTNUMBR, dbo.WO010032.SCHEDULINGPREFEREN_I, dbo.WO010032.PLANNAME_I, dbo.WO010032.ACTUALDEMAND_I,
                               dbo.WO010032.MANUFACTUREORDPRI_I, dbo.WO010032.Partial_Purge_Date, dbo.WO010032.NOTEINDX, dbo.WO010032.OUTSOURCED_I, dbo.WO010032.COMPCALCOPTION,
                               dbo.WO010032.COMPLETECLOSEDATE, dbo.WO010032.PSTGDATE, dbo.WO010032.PLNNDSPPLID, dbo.WO010032.PICKNUMBER, dbo.WO010032.QUICK_MO_I, dbo.WO010032.ROUTING_REVISION_LEVEL,
                               dbo.WO010032.BOM_REVISION_LEVEL, dbo.WO010032.DEX_ROW_ID, ISNULL(MORctQty.MOQtyRecvd, 0) AS MOQtyRecvd,
                               CASE WHEN MANUFACTUREORDERST_I = 1 THEN 'Quote,' WHEN MANUFACTUREORDERST_I = 2 THEN 'Open' WHEN MANUFACTUREORDERST_I = 3 THEN 'Released' WHEN MANUFACTUREORDERST_I = 4 THEN
                                'Hold' WHEN MANUFACTUREORDERST_I = 5 THEN 'Cancelled' WHEN MANUFACTUREORDERST_I = 6 THEN 'Complete' WHEN MANUFACTUREORDERST_I = 7 THEN 'Partial Receipt' WHEN MANUFACTUREORDERST_I
                                = 8 THEN 'Closed' ELSE 'Now Being Entered by ' + wo010032.userid END AS MOStatusDesc, dbo.MN010000.NOTETEXT_I AS MONote
      FROM            dbo.WO010032 LEFT OUTER JOIN
                               dbo.MN010000 ON dbo.WO010032.NOTEINDX = dbo.MN010000.NOTEINDX LEFT OUTER JOIN
                                   (SELECT        MANUFACTUREORDER_I, SUM(QTYRECVD) AS MOQtyRecvd
                                     FROM            dbo.MOP1100
                                     GROUP BY MANUFACTUREORDER_I
                                     HAVING         (MANUFACTUREORDER_I > '')) AS MORctQty ON dbo.WO010032.MANUFACTUREORDER_I = MORctQty.MANUFACTUREORDER_I
      WHERE        (dbo.WO010032.MANUFACTUREORDER_I > '')
      GO
      /****** Object:  View [dbo].[BI_Mfg_AllDocs]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_AllDocs]
      AS
      SELECT        TOP (100) PERCENT dbo.BI_Mfg_SO.SODocID, dbo.BI_Mfg_SO.SODocType, dbo.BI_Mfg_SI.SIDocID, dbo.BI_Mfg_SI.SIDocType, dbo.BI_Mfg_SO.SRNm, dbo.BI_Mfg_SO.SODocNum,
                               dbo.BI_Mfg_SO.SOPNote AS SONote, dbo.BI_Mfg_SO.SOReqShipDate, dbo.BI_Mfg_SO.SOCusNum, dbo.BI_Mfg_SO.SOCusName, dbo.BI_Mfg_SO.SOQty, dbo.BI_Mfg_SO.QtytoBackOrder,
                               dbo.BI_Mfg_SO.QtyToInvoice, ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') AS MODocNo, dbo.BI_Mfg_SI.SIDocNum, dbo.BI_Mfg_SI.SINote, dbo.BI_Mfg_SI.SIQty,
                               dbo.BI_Mfg_SO.LNITMSEQ AS SOLine, dbo.BI_Mfg_MOSOLink.QtyAllocatedToSO, dbo.BI_Mfg_MOSOLink.MOQty, dbo.BI_Mfg_MO.MOStatus, dbo.BI_Mfg_MO.MOQtyRecvd, dbo.BI_Mfg_MO.MOStatusDesc,
                               dbo.BI_Mfg_MO.MONote, dbo.BI_Mfg_MO.ITEMNMBR AS MOItem, dbo.BI_ItemMaster.ITEMDESC AS MOItemDesc, dbo.BI_Mfg_MOSOLink.InHouseDueDt, dbo.BI_Mfg_MOSOLink.PromiseDate,
                               dbo.BI_Mfg_MO.ENDDATE AS MOEndDate, dbo.BI_Mfg_MO.STRTDATE AS MOStartDate, dbo.BI_Mfg_MOSOLink.MOQty AS MOQtyRem, dbo.BI_Mfg_SI.SIDocDate, CASE WHEN siud1 IS NULL
                               THEN soud1 ELSE siud1 END AS MainSalesOrd, CASE WHEN siud2 IS NULL THEN soud2 ELSE siud2 END AS OrderedBy, CASE WHEN siud3 IS NULL THEN soud3 ELSE siud3 END AS TypeID,
                               CASE WHEN sicmt IS NULL THEN socmt ELSE sicmt END AS Comment, dbo.BI_Mfg_SI.PackingSlip, dbo.BI_Mfg_SI.PickTicket, dbo.BI_Mfg_SI.DeliveryNote, dbo.BI_Mfg_SO.SODate,
                               CASE WHEN SIDOCNUM IS NOT NULL THEN '7 In Dispatch' WHEN DeliveryNote IS NOT NULL THEN '8 Dispatched' ELSE CAST(mostatus AS varchar) + ' MO ' + mostatusdesc END AS ProcessStatus
      FROM            dbo.BI_Mfg_SI RIGHT OUTER JOIN
                               dbo.BI_ItemMaster INNER JOIN
                               dbo.BI_Mfg_MO ON dbo.BI_ItemMaster.ITEMNMBR = dbo.BI_Mfg_MO.ITEMNMBR RIGHT OUTER JOIN
                               dbo.BI_Mfg_SO LEFT OUTER JOIN
                               dbo.BI_Mfg_MOSOLink ON dbo.BI_Mfg_SO.MANUFACTUREORDER_I = dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I AND dbo.BI_Mfg_SO.SODocNum = dbo.BI_Mfg_MOSOLink.SOPNUMBE AND
                               dbo.BI_Mfg_SO.LNITMSEQ = dbo.BI_Mfg_MOSOLink.LNITMSEQ AND dbo.BI_Mfg_SO.[Item Number] = dbo.BI_Mfg_MOSOLink.ITEMNMBR ON
                               dbo.BI_Mfg_MO.ITEMNMBR = dbo.BI_Mfg_MOSOLink.ITEMNMBR AND dbo.BI_Mfg_MO.MANUFACTUREORDER_I = dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I ON
                               dbo.BI_Mfg_SI.SIOrigNum = dbo.BI_Mfg_SO.SODocNum AND dbo.BI_Mfg_SI.[Item Number] = dbo.BI_Mfg_SO.[Item Number] AND dbo.BI_Mfg_SI.LNITMSEQ = dbo.BI_Mfg_SO.LNITMSEQ
      WHERE        (ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') > '')
      GO
      /****** Object:  View [dbo].[BI_Mfg_Picklist]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_Picklist]
      AS
      SELECT        dbo.PK010033.ITEMNMBR AS RMNumber, dbo.BI_ItemMaster.ITEMDESC AS RMDescription, dbo.PK010033.SUGGESTEDQTY_I AS RMQty, dbo.BI_ItemMaster.BASEUOFM AS UofM, dbo.PK010033.ALTERNATE_I,
                               dbo.PK010033.QTY_BACKFLUSHED_I AS RMQtyAllocatedtoMO, dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl, dbo.BI_ItemQty_ByLoc.MacoyaQtyAvlbl, dbo.BI_ItemQty_ByLoc.WIPQtyAvlbl,
                               dbo.BI_ItemQty_ByLoc.TotalOnOrd, dbo.BI_ItemMaster.ITMCLSCD AS ItemClass, dbo.BI_ItemMaster.USCATVLS_6 AS Origin, dbo.BI_Mfg_AllDocs.SODocID, dbo.BI_Mfg_AllDocs.SODocType,
                               dbo.BI_Mfg_AllDocs.SIDocID, dbo.BI_Mfg_AllDocs.SIDocType, dbo.BI_Mfg_AllDocs.SRNm, dbo.BI_Mfg_AllDocs.SODocNum, dbo.BI_Mfg_AllDocs.SONote, dbo.BI_Mfg_AllDocs.SOReqShipDate,
                               dbo.BI_Mfg_AllDocs.SOCusNum, dbo.BI_Mfg_AllDocs.SOCusName, dbo.BI_Mfg_AllDocs.SOQty, dbo.BI_Mfg_AllDocs.QtytoBackOrder, dbo.BI_Mfg_AllDocs.QtyToInvoice, RTRIM(dbo.BI_Mfg_AllDocs.MODocNo)
                               AS MODocNo, dbo.BI_Mfg_AllDocs.SIDocNum, dbo.BI_Mfg_AllDocs.SINote, dbo.BI_Mfg_AllDocs.SIQty, dbo.BI_Mfg_AllDocs.SOLine, dbo.BI_Mfg_AllDocs.QtyAllocatedToSO, dbo.BI_Mfg_AllDocs.MOQty,
                               dbo.BI_Mfg_AllDocs.MOStatus, dbo.BI_Mfg_AllDocs.MOQtyRecvd, dbo.BI_Mfg_AllDocs.MOStatusDesc, dbo.BI_Mfg_AllDocs.MONote, dbo.BI_Mfg_AllDocs.MOItem, dbo.BI_Mfg_AllDocs.MOItemDesc,
                               CASE WHEN dbo.PK010033.SUGGESTEDQTY_I - dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl - dbo.BI_ItemQty_ByLoc.TotalOnOrd > 0 THEN dbo.PK010033.SUGGESTEDQTY_I - dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl - dbo.BI_ItemQty_ByLoc.TotalOnOrd
                                ELSE 0 END AS SuggestedPurchaseQty, dbo.BI_Mfg_AllDocs.InHouseDueDt, dbo.BI_Mfg_AllDocs.PromiseDate, dbo.BI_Mfg_AllDocs.MOStartDate, dbo.BI_Mfg_AllDocs.MOEndDate,
                               dbo.BI_Mfg_AllDocs.MOQtyRem, CASE WHEN dbo.PK010033.SUGGESTEDQTY_I > dbo.BI_ItemQty_ByLoc.TotalQtyAvlbl THEN 'Insufficient Raw Materials' ELSE '' END AS RMCheck,
                               RTRIM(dbo.PK010033.ITEMNMBR) + ' | ' + RTRIM(dbo.BI_ItemMaster.ITEMDESC) AS RMItemLbl, dbo.BI_Mfg_AllDocs.MODocNo AS RlMODocNo
      FROM            dbo.PK010033 LEFT OUTER JOIN
                               dbo.BI_Mfg_AllDocs ON dbo.PK010033.MANUFACTUREORDER_I = dbo.BI_Mfg_AllDocs.MODocNo LEFT OUTER JOIN
                               dbo.BI_ItemQty_ByLoc ON dbo.PK010033.ITEMNMBR = dbo.BI_ItemQty_ByLoc.ITEMNMBR LEFT OUTER JOIN
                               dbo.BI_ItemMaster ON dbo.PK010033.ITEMNMBR = dbo.BI_ItemMaster.ITEMNMBR
      GO
      /****** Object:  View [dbo].[BI_Mfg_WorkOrder]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_WorkOrder]
      AS
      SELECT        Origin, MOStatus, MOStatusDesc, MONote, RMNumber, RMDescription, RMQty, UofM, SODocNum, SONote, SOCusNum, SOCusName, SRNm, MODocNo, MOQtyRem, RMCheck, PromiseDate, MOStartDate,
                               MOEndDate, MOItem, MOItemDesc
      FROM            dbo.BI_Mfg_Picklist
      WHERE        (MOStatus IN (3, 7))
      GO
      /****** Object:  View [dbo].[BI_Mfg_MORcts]    Script Date: 9/21/2017 3:42:01 PM ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE VIEW [dbo].[BI_Mfg_MORcts]
      AS
      SELECT        dbo.MOP1100.MANUFACTUREORDER_I AS MONum, dbo.WO010032.DSCRIPTN, dbo.MOP1100.MOPRCTNM, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.MOP1100.DATERECD,
                               dbo.MOP1100.QTYRECVD, dbo.MOP1100.TO_SITE_I, dbo.MOP1100.USERID, dbo.MOP1100.RECEIPTCOSTSARRAY_10 AS BackflushCost, dbo.MOP1100.POSTED
      FROM            dbo.IV00101 RIGHT OUTER JOIN
                               dbo.WO010032 ON dbo.IV00101.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
                               dbo.MOP1100 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.MOP1100.MANUFACTUREORDER_I
      GO