How to remove audio tracks from avi files

Use avidemux

https://sourceforge.net/projects/avidemux/?source=typ_redirect

• Make a copy of all files to keep originals just in case
• Open file in avidemux
• Go to Audio>Select Track
• Untick the track you don't want
• Save File

How to track if an application has been launched multiple times

• Use Windows Task Manager, or download Process Explorer to view all running processes
• For complete logging capabilities, you can use Process Monitor
• Check Sysinternals in general for other useful tools

Dynamics NAV - LS Retail - Calculate Statement does not detect any lines

There is a previous statement already calculated for those transactions

OR

Run the Z-Report at least once

OR

Tick Functionality Profile>General>Z.Rep Autopr. After T.Dec EOD
To autoprint a Z-Report whenever an EOD is done, which allows the statement to calculate correctly.

Dynamics NAV - LS Retail - Wrong Login on POS causes transactions to overwrite at head office due to re-used transaction number

To resolve this

• Identify all transactions on terminal that are not in head office
• SELECT * FROM [CRONUS$Transaction Header] where [POS Terminal No_] in ('REG01','REG02')
• Identify all transactions on Terminal
• SELECT * FROM [CRONUS$Transaction Header] 
• Compare the Transaction No_ and Receipt no_ fields to confirm which transactions were overwritten
• Renumber the Transaction No_ fields on the POS for all the missing transactions using new transaction numbers
• Set the next transaction number on the POS to the new last number
• Delete all of the old incorrect transactions
• delete FROM [CRONUS$Transaction Header] where [POS Terminal No_] = 'S001' and [Transaction No_] <= 170
• delete FROM [CRONUS$Trans_ Discount Entry] where [POS Terminal No_] = 'S001' and [Transaction No_] <= 170
• delete FROM [CRONUS$Trans_ Infocode Entry] where [POS Terminal No_] = 'S001' and [Transaction No_] <= 170
• delete FROM [CRONUS$Trans_ Payment Entry] where [POS Terminal No_] = 'S001' and [Transaction No_] <= 170
• delete FROM [CRONUS$Trans_ Sales Entry] where [POS Terminal No_] = 'S001' and [Transaction No_] <= 170
• delete FROM [CRONUS$Trans_ Tender Declar_ Entry] where [POS Terminal No_] = 'S001' and [Transaction No_] <= 170
• Run the replication to pull the transactions

This codeunit has been written to perform the changes.

This should only be used on the POS having the issue

Before you to run this codeunit, you must change the values for the following fields:

      OldStoreNo

      OldPOSTerminal

      NewStoreNo

      NewPOSTerminal

      NewTransNo

      OldTransNo

-------------------------------------------------------------------------------------------------------------

OBJECT Codeunit 50080 Fix Transaction

{

  OBJECT-PROPERTIES

  {

    Date=06/01/16;

    Time=12:04:53 PM;

    Modified=Yes;

    Version List=DAV;

  }

  PROPERTIES

  {

    OnRun=BEGIN

            OldStoreNo := 'S0002';

            OldPOSTerminal := 'P0003';

            NewStoreNo := 'S0012';

            NewPOSTerminal := 'P0028';

            NewTransNo := 1755;  //First New Transaction No.

            FOR OldTransNo := 20000161 TO 20000162 DO BEGIN

              TransactionHeader.GET(OldStoreNo,OldPOSTerminal,OldTransNo);

              TransactionHeaderNew := TransactionHeader;

              TransactionHeaderNew."Store No." := NewStoreNo;

              TransactionHeaderNew."POS Terminal No." := NewPOSTerminal;

              TransactionHeaderNew."Transaction No." := NewTransNo;

              TransactionHeaderNew.INSERT(TRUE);  //This updates the Replication Counter field

              TransSalesEntry.RESET;

              TransSalesEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransSalesEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransSalesEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransSalesEntry.FINDSET THEN REPEAT

                TransSalesEntryNew := TransSalesEntry;

                TransSalesEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransSalesEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransSalesEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransSalesEntryNew.VALIDATE("Replication Counter");

                TransSalesEntryNew.INSERT(FALSE);

                TransSalesEntry.DELETE;

              UNTIL TransSalesEntry.NEXT = 0;

              TransPaymentEntry.RESET;

              TransPaymentEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransPaymentEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransPaymentEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransPaymentEntry.FINDSET THEN REPEAT

                TransPaymentEntryNew := TransPaymentEntry;

                TransPaymentEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransPaymentEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransPaymentEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransPaymentEntryNew.VALIDATE("Replication Counter");

                TransPaymentEntryNew.INSERT(FALSE);

                TransPaymentEntry.DELETE;

              UNTIL TransPaymentEntry.NEXT = 0;

              TransIncomeExpenseEntry.RESET;

              TransIncomeExpenseEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransIncomeExpenseEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransIncomeExpenseEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransIncomeExpenseEntry.FINDSET THEN REPEAT

                TransIncomeExpenseEntryNew := TransIncomeExpenseEntry;

                TransIncomeExpenseEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransIncomeExpenseEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransIncomeExpenseEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransIncomeExpenseEntryNew.VALIDATE("Replication Counter");

                TransIncomeExpenseEntryNew.INSERT(FALSE);

                TransIncomeExpenseEntry.DELETE;

              UNTIL TransIncomeExpenseEntry.NEXT = 0;

              TransCouponEntry.RESET;

              TransCouponEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransCouponEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransCouponEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransCouponEntry.FINDSET THEN REPEAT

                TransCouponEntryNew := TransCouponEntry;

                TransCouponEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransCouponEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransCouponEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransCouponEntryNew.VALIDATE("Replication Counter");

                TransCouponEntryNew.INSERT(FALSE);

                TransCouponEntry.DELETE;

              UNTIL TransCouponEntry.NEXT = 0;

              TransInfocodeEntry.RESET;

              TransInfocodeEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransInfocodeEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransInfocodeEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransInfocodeEntry.FINDSET THEN REPEAT

                TransInfocodeEntryNew := TransInfocodeEntry;

                TransInfocodeEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransInfocodeEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransInfocodeEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransInfocodeEntryNew.VALIDATE("Replication Counter");

                TransInfocodeEntryNew.INSERT(FALSE);

                TransInfocodeEntry.DELETE;

              UNTIL TransInfocodeEntry.NEXT = 0;

              TransInventoryEntry.RESET;

              TransInventoryEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransInventoryEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransInventoryEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransInventoryEntry.FINDSET THEN REPEAT

                TransInventoryEntryNew := TransInventoryEntry;

                TransInventoryEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransInventoryEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransInventoryEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransInventoryEntryNew.VALIDATE("Replication Counter");

                TransInventoryEntryNew.INSERT(FALSE);

                TransInventoryEntry.DELETE;

              UNTIL TransInventoryEntry.NEXT = 0;

              TransMixMatchEntry.RESET;

              TransMixMatchEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransMixMatchEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransMixMatchEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransMixMatchEntry.FINDSET THEN REPEAT

                TransMixMatchEntryNew := TransMixMatchEntry;

                TransMixMatchEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransMixMatchEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransMixMatchEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransMixMatchEntryNew.VALIDATE("Replication Counter");

                TransMixMatchEntryNew.INSERT(FALSE);

                TransMixMatchEntry.DELETE;

              UNTIL TransMixMatchEntry.NEXT = 0;

              TransDiscountEntry.RESET;

              TransDiscountEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransDiscountEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransDiscountEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransDiscountEntry.FINDSET THEN REPEAT

                TransDiscountEntryNew := TransDiscountEntry;

                TransDiscountEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransDiscountEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransDiscountEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransDiscountEntryNew.VALIDATE("Replication Counter");

                TransDiscountEntryNew.INSERT(FALSE);

                TransDiscountEntry.DELETE;

              UNTIL TransDiscountEntry.NEXT = 0;

              TransDiscBenefitEntry.RESET;

              TransDiscBenefitEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransDiscBenefitEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransDiscBenefitEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransDiscBenefitEntry.FINDSET THEN REPEAT

                TransDiscBenefitEntryNew := TransDiscBenefitEntry;

                TransDiscBenefitEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransDiscBenefitEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransDiscBenefitEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransDiscBenefitEntryNew.VALIDATE("Replication Counter");

                TransDiscBenefitEntryNew.INSERT(FALSE);

                TransDiscBenefitEntry.DELETE;

              UNTIL TransDiscBenefitEntry.NEXT = 0;

              MemberProcessOrderEntry.RESET;

              MemberProcessOrderEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              MemberProcessOrderEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              MemberProcessOrderEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF MemberProcessOrderEntry.FINDSET THEN REPEAT

                MemberProcessOrderEntryNew := MemberProcessOrderEntry;

                MemberProcessOrderEntryNew."Store No." := TransactionHeaderNew."Store No.";

                MemberProcessOrderEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                MemberProcessOrderEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                MemberProcessOrderEntryNew.VALIDATE("Replication Counter");

                MemberProcessOrderEntryNew.INSERT(FALSE);

                MemberProcessOrderEntry.DELETE;

              UNTIL MemberProcessOrderEntry.NEXT = 0;

              TransTenderDeclarEntry.RESET;

              TransTenderDeclarEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransTenderDeclarEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransTenderDeclarEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransTenderDeclarEntry.FINDSET THEN REPEAT

                TransTenderDeclarEntryNew := TransTenderDeclarEntry;

                TransTenderDeclarEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransTenderDeclarEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransTenderDeclarEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransTenderDeclarEntryNew.VALIDATE("Replication Counter");

                TransTenderDeclarEntryNew.INSERT(FALSE);

                TransTenderDeclarEntry.DELETE;

              UNTIL TransTenderDeclarEntry.NEXT = 0;

              TransSafeEntry.RESET;

              TransSafeEntry.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransSafeEntry.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransSafeEntry.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransSafeEntry.FINDSET THEN REPEAT

                TransSafeEntryNew := TransSafeEntry;

                TransSafeEntryNew."Store No." := TransactionHeaderNew."Store No.";

                TransSafeEntryNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransSafeEntryNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransSafeEntryNew.VALIDATE("Replication Counter");

                TransSafeEntryNew.INSERT(FALSE);

                TransSafeEntry.DELETE;

              UNTIL TransSafeEntry.NEXT = 0;

              TransCashDeclaration.RESET;

              TransCashDeclaration.SETRANGE("Store No.",TransactionHeader."Store No.");

              TransCashDeclaration.SETRANGE("POS Terminal No.",TransactionHeader."POS Terminal No.");

              TransCashDeclaration.SETRANGE("Transaction No.",TransactionHeader."Transaction No.");

              IF TransCashDeclaration.FINDSET THEN REPEAT

                TransCashDeclarationNew := TransCashDeclaration;

                TransCashDeclarationNew."Store No." := TransactionHeaderNew."Store No.";

                TransCashDeclarationNew."POS Terminal No." := TransactionHeaderNew."POS Terminal No.";

                TransCashDeclarationNew."Transaction No." := TransactionHeaderNew."Transaction No.";

                TransCashDeclarationNew.VALIDATE("Replication Counter");

                TransCashDeclarationNew.INSERT(FALSE);

                TransCashDeclaration.DELETE;

              UNTIL TransCashDeclaration.NEXT = 0;

              TransactionHeader.DELETE;

              NewTransNo := NewTransNo + 1;

            END;

          END;

  }

  CODE

  {

    VAR

      OldStoreNo@1200070011 : Code[10];

      NewStoreNo@1200070012 : Code[10];

      OldPOSTerminal@1200070013 : Code[10];

      NewPOSTerminal@1200070014 : Code[10];

      OldTransNo@1200070015 : Integer;

      NewTransNo@1200070016 : Integer;

      TransactionHeader@1200070000 : Record 99001472;

      TransactionHeaderNew@1200070017 : Record 99001472;

      TransSalesEntry@1200070001 : Record 99001473;

      TransSalesEntryNew@1200070018 : Record 99001473;

      TransPaymentEntry@1200070002 : Record 99001474;

      TransPaymentEntryNew@1200070019 : Record 99001474;

      TransIncomeExpenseEntry@1200070003 : Record 99001475;

      TransIncomeExpenseEntryNew@1200070020 : Record 99001475;

      TransCouponEntry@1200070004 : Record 99001477;

      TransCouponEntryNew@1200070021 : Record 99001477;

      TransInfocodeEntry@1200070005 : Record 99001478;

      TransInfocodeEntryNew@1200070022 : Record 99001478;

      TransInventoryEntry@1200070006 : Record 99001490;

      TransInventoryEntryNew@1200070023 : Record 99001490;

      TransMixMatchEntry@1200070007 : Record 99001496;

      TransMixMatchEntryNew@1200070024 : Record 99001496;

      TransDiscountEntry@1200070008 : Record 99001642;

      TransDiscountEntryNew@1200070025 : Record 99001642;

      TransDiscBenefitEntry@1200070009 : Record 99001674;

      TransDiscBenefitEntryNew@1200070026 : Record 99001674;

      MemberProcessOrderEntry@1200070010 : Record 99009038;

      MemberProcessOrderEntryNew@1200070027 : Record 99009038;

      TransTenderDeclarEntry@1200070028 : Record 99001465;

      TransTenderDeclarEntryNew@1200070029 : Record 99001465;

      TransSafeEntry@1200070030 : Record 99001630;

      TransSafeEntryNew@1200070032 : Record 99001630;

      TransCashDeclaration@1200070031 : Record 99001626;

      TransCashDeclarationNew@1200070033 : Record 99001626;

    BEGIN

    {

      This codeunit is used to renumber POS transactions.

      This should only be used on POS machines to create new transactions with the correct store and terminal to be re-sent to HO.

      Before you to run this codeunit, you must change the values for the following fields:

      OldStoreNo

      OldPOSTerminal

      NewStoreNo

      NewPOSTerminal

      NewTransNo

      OldTransNo

    }

    END.

  }

}

-----------------------------------------------------------------------------------------------------------

Dynamics GP - SQL Views - All AR Distribution Accounts that have been hit by AR transactions

SELECT     YEAR(AllRM.POSTEDDT) AS PostYr, MONTH(AllRM.POSTEDDT) AS PostMth, GL00105.ACTNUMST, GL00100.ACTDESCR, SUM(AllRM.DEBITAMT) AS Deb,
                      SUM(AllRM.CRDTAMNT) AS Cred, AllRM.DISTTYPE
FROM         GL00105 INNER JOIN
                      GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX INNER JOIN
                          (SELECT     POSTEDDT, DSTINDX, DEBITAMT, CRDTAMNT, DISTTYPE
                            FROM          (SELECT     POSTEDDT, DSTINDX, DEBITAMT, CRDTAMNT, DISTTYPE
                                                    FROM          RM10101
                                                    UNION
                                                    SELECT     POSTEDDT, DSTINDX, DEBITAMT, CRDTAMNT, DISTTYPE
                                                    FROM         RM30301) AS derivedtbl_1) AS AllRM ON GL00105.ACTINDX = AllRM.DSTINDX
GROUP BY YEAR(AllRM.POSTEDDT), GL00105.ACTNUMST, GL00100.ACTDESCR, MONTH(AllRM.POSTEDDT), AllRM.DISTTYPE
ORDER BY YEAR(AllRM.POSTEDDT), MONTH(AllRM.POSTEDDT), GL00105.ACTNUMST

Dynamics GP 2013 - VBA how to open an odbc connection to pull data

Be sure to include the Microsoft ActiveX Data Objects 2.8 Library in the customizations import

Frx - "An error (-5009:0x8002801d) has ocurred while running the setup. Please make sure you have finished any previous setup and closed other applications. if the error still occurs, please contact your vendor. "

Copy the install files locally. Run them from there as administrator.

Dynamics GP - SOP Dummy Report Form - Cannot print any SOP documents

Add security access to Product - Manufacturing, Type Windows, Series 3rd Party, Operations - SOP Dummy Report Form

Dynamics NAV - Year End Closing Process and Checklist

http://www.archerpoint.com/blog/Posts/microsoft-dynamics-nav-2015-how-run-close-year-end-process

https://www.scribd.com/document/111003831/Year-End-Checklist-for-Microsoft-Dynamics-NAV-Navision

https://www.youtube.com/watch?v=LXFhq2jb-5o

• 4hr review session, activities may take some time if reconciliation issues exist.
• Year End Close Overview
• Create Next New Year
• Post all transactions for the year to be closed
• Reconcile all subledgers for the year to be closed
• Set Final Exchange Rate, Run Gain/Loss Routines
• Lock out posting to closed period
• Close Year
• Close income statement
• Post closing Journal
• Run Financial Reports
• Setup Budget for next year

• Post All Open Journals
• Payments, Deposits, Cash Receipts, Payroll.
• Post Recurring Entries for the year to be closed
• INVENTORY RECONCILIATION
• As at End of Fiscal Year Date 
• Run the /Departments/Financial Management/Inventory/Costing/Adjust Cost Routine
• If Automatic Cost Posting is not enabled, Run the Post Cost to G/L Routine
• Run the /Departments/Financial Management/Inventory/Inventory – G/L Reconciliation By Inventory Posting Group
• Inventory Valuation>Include Expected Costs Ticked = Inventory including Received not Invoiced
• Inventory Valuation>Include Expected Costs UnTicked = Only Invoiced Inventory
• Reconcile the Received Not Invoiced Posted to G/L to your Inventory Account (Interim)
• Reconcile the Shipped Not Invoiced Posted to G/L to your Accrued COGS Account
• Reconcile the Net Inventory Interim Posted to G/L to your Inventory Account (Interim)
•  Reconcile the Inventory Posted to G/L to the Inventory Asset Account
• INVENTORY REVALUATION
• Depending on your Industry or to correct errors, you may be able to re-value inventory as part of the year end process. Now is the time.
• Departments/Warehouse/Inventory/Revaluation Journals
• ACCOUNTS RECEIVABLE RECONCILIATION
• Reconcile the Customer Trial Balance Report to the A/R Account By Customer Posting Group (This report Reconciles to the G/L Aged A/R may not Reconcile if you use Foreign Currency) 
• ACCOUNTS PAYABLE RECONCILIATION
• Reconcile the Vendor Trial Balance Report to the A/P Account
• By Vendor Posting Group (This report Reconciles to the G/L
• Aged A/P may not Reconcile of you use Foreign Currency)
•  FIXED ASSETS 
• Calculate Depreciation and Post Depreciation to the G/L
•  MANUFACTURING
• Complete all “Finished” Production Orders and reconcile WIP values
• BANK ACCOUNTS 
• Reconcile each Bank Account using the Bank Reconciliation Module. Compare the Book Values to the Chart of Accounts for each Bank Account Posting Groups G/L Account
• MULTI-CURRENCY
•  Enter the Currency Conversion Rates that will be used for gain/loss valuation at the end of the fiscal year
• Currencies>Exch. Rates
• Enter rate for last day of fiscal year if required
• LOCK OUT POSTING TO YEAR THAT WILL BE CLOSED
• Change the Posting Dates in General Ledger Setup
• Change User Setup>Set Allow Posting From and Allow Posting To
• Only Set Values for users that are Exceptions
•  YEAR END CLOSE
• Verify that the upcoming Fiscal Year calendar exists in the Accounting Periods setups, and that it is correct. If it is not there, or not correct- create the periods for the next year or two
• Departments/Financial Management/Periodic Activities/Fiscal Year/Accounting Periods
• Create Year
•  Close the Accounting Periods for the Year to be closed
• Departments > Financial Management > Periodic Activities > Fiscal Year>Accounting Periods>Close Year
•  Close the Income Statement By Dimension
• Departments > Financial Management > Periodic Activities > Fiscal Year>Accounting Periods>Close Income Statement
• Enter all fields
• Mark all dimensions
• Open the General Journal and Enter the Retained Earnings Account(s) if necessary, Post the Closing Entry using a Closing Date
• Verify the P&L Accounts have zero balance for new Fiscal period
•  FINANCIAL STATEMENTS
• Account Schedules
• Run the Year End Balance Sheet
• Run the Year End Income Statement
• Run any Statements of Operations
• Run Statement of Cash Flows
• Run the Trial Balance Report and verify balances
• Departments/Financial Management/General Ledger/Reports/Financial Statement
•  BUDGETS
• Create the Budget for the upcoming Fiscal Year 

Dynamics GP 2013R2 - Scheduling a macro that prints to screen does not print anything

• A Macro has been created to autologin and run check links using a macro, batch file and windows task scheduler.
• The check links report is expected to display on the screen when completed, however, this does not happen. No report comes up, but check links is completed.
• When the job is run manually, everything works fine
• When it runs on the schedule, no report comes up

ISSUE

• This issue occurs when the user session is inactive, and the user is not actively logged in.

RESOLUTION

• Update the macro to send the print to a file instead of screen

Dynamics GP - Create Return Window - Create RMA checkbox default

https://community.dynamics.com/gp/f/32/t/29127

To uncheck it by default you must disable the Field Service Returns Management

• Go to the Registration window from Tools >> Setup >> System >> Registration
• Uncheck the Returns Management module
• Click OK

Dynamics GP HITB Reset Tool

https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/MDGP10_HITB


http://mahmoudsaadi.blogspot.com/2014/04/hitb-essentials-series-hitb-reset-tool.html

• Get an Inventory Offset Account to post the GL-Subledger variances to
• Run the SQL script to create the objects
• Copy the cnk file into the GP folder and launch GP
• Launch GP>Tools>Utilities>Inventory>HITB Reset Tool

LS Retail - NAV - Change Line color/font based on infocode subcode

C99001565 POS Dataset Utility
Add the following code to assign a skin or font to an array number. Call on the array value if your infocode subcode is found on the pos line.

SETJOURNALMODE

  COL0 := 14;
  COL1 := 15;

INITJOURNALMENULINES

JournalLineFonts[COL0] := '#SL_COL0';
JournalLineFonts[COL1] := '#SL_COL1';


JournalLineButtonSkins[COL0] := '#SL_COL0';
JournalLineButtonSkins[COL1] := '#SL_COL1';

GETJOURNALLINECOLOR


    InfCd.RESET;
    InfCd.SETFILTER("Receipt No.","Receipt No.");
    InfCd.SETRANGE("Line No.","Line No.");
    IF InfCd.FIND('-') THEN BEGIN
      IF InfCd.Information = '10' THEN BEGIN
          CurrColor := COL0;
        EXIT;
      END;
      IF InfCd.Information = '20' THEN BEGIN
          CurrColor := COL1;
        EXIT;
      END;
     END;

Dynamics GP 2010 - Restore Copy of Live Over Test database

BACKUP DATABASE [TWO] TO  DISK = N'F:\DBS\TWO_temp.bak' WITH NOFORMAT, INIT,  NAME = N'TWO-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



Use Master
Alter Database TEST
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE [TEST] FROM  DISK = N'F:\DBS\TWO_temp.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

USE TEST

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end

sp_changedbowner 'DYNSA'

SSRS Keeps prompting for username and password when trying to access the website

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ef2f893-648b-46d3-9339-9fd06075570a/ssrs-prompting-for-usernamepassword-when-trying-to-access-the-report-installed-on-a-server-from?forum=sqlreportingservices

1. Open the rsreportserver.config(By default, it can be found from C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer)
2. Comment the <RSWindowsNegotiate/> under <AuthenticationTypes> element. 

Dynamics NAV CAL - How to use Special Character ' in text string outputs

4 single quotes will turn into 1 single quote

Example

Message('''' + MyWord + '''');

will display  'MyWord'

Dynamics GP - Macro REM or Comment a line

You can just use the ' character at the beginning of lines to stop them from running

Also include this line at the beginning to suppress all error messages

logging file 'macro.log'

Dynamics GP - Customer Phone number does not print on SOP Invoice

• In the Customer address card drill into the address itself
• In the area for "Ship To", Print Phone/Fax Number
• select "Phone1"

Or use this script to set all customer addresses to "Phone1"

• update rm00102 set print_phone_numbergb = 1

• Neither of the above options will update existing SOP documents.
• After making the change on the address card, you must navigate to the sales document, reselect the ship to address id, and roll down the address changes for it to pick up the new address option.

Dynamics GP - VBA cannot be initialized

Your dynamics GP Registration does not have Modifier enabled.
Check your keys and confirm the module is enabled.

Dynamics GP - Modifier and VBA Visual Basic - ODBC Connection - GP2015 After 14.00.066

• Before starting, ensure you check this
• Tools > References > Check the checkbox in front of "Microsoft ActiveX Data Objects 2.5 Library"

'---------------------------------------------------------------------------------------------------
Option Explicit

Private Sub CheckAccounts_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
Dim RetVal As String
On Error GoTo CheckAccounts_BeforeUserChanged_Err

    Dim IntercompanyID As String
    Dim SystemDatabase As String
    Dim StockCountNumber As String
    Dim GetNOAccountResult As String
 
    'Retrieve and display the UserInfo
    IntercompanyID = GETRunningUser("IntercompanyID")
    SystemDatabase = GETRunningUser("SystemDatabaseName")

Exit Sub

Private Function GetNOAccount(ByVal coName As String, ByVal StockID As String) As String
    Dim cn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim SqlStr As String

    On Error GoTo GetNOAccount_Err

    'Retrieve an ADO connection for the current user
    Set cn = UserInfoGet.CreateADOConnection()

    'Set the connection properties
    cn.CursorLocation = adUseClient

    'Set the current database, using the IntercompanyID property
    cn.DefaultDatabase = UserInfoGet.IntercompanyID

    SqlStr = ""

    'Create a command to select result
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = SqlStr
    Set rst = cmd.Execute

    SqlStr = " select top 1 * from dbo.ResultFile"
    cmd.CommandText = SqlStr
    Set rst = cmd.Execute

    'Display the rows retrieved
'rst![ColumnName]
'    MsgBox ">>" + rst!ResultText
    GetNOAccount = rst!ResultText
 
    'Close the connection
    cn.Close
Exit Function

GetNOAccount_Err:

    Select Case Err.Number
    Case 91
        'Object variable or With block variable not set (Error 91)
        MsgBox "Error 91:" + Str(Err.Number) + "<" + Err.Description + ">"
        Resume
    Case Else
        MsgBox "Unknown Error:" + Str(Err.Number) + "<" + Err.Description + ">"
        Exit Function
    End Select
 
End Function

Private Function GETRunningUser(ByVal request As String) As String
    Dim UserInfoObj As UserInfo

    Dim CompanyName As String
    Dim IntercompanyID As String
    Dim SystemDatabase As String
    Dim UserDate As Date
    Dim UserID As String
    Dim UserName As String

    'Get the UserInfo object
    Set UserInfoObj = VbaGlobal.UserInfoGet()

    'Retrieve and display the UserInfo
    Select Case request
       Case "CompanyName"
            GETRunningUser = UserInfoObj.CompanyName
       Case "IntercompanyID"
            GETRunningUser = UserInfoObj.IntercompanyID
       Case "SystemDatabaseName"
            GETRunningUser = UserInfoObj.SystemDatabaseName
       Case "UserDate"
            GETRunningUser = UserInfoObj.UserDate
       Case "UserID"
            GETRunningUser = UserInfoObj.UserID
       Case "UserName"
            GETRunningUser = UserInfoObj.UserName
      Case Else
            GETRunningUser = ""
    End Select

End Function

'--------------------------------------------------------------------------------------------------------------------

Dynamics GP - MBs File Exchange does not prompt for Active X or install File Transfer Manager

https://mbs.microsoft.com/customersource/northamerica/AX/support/support-news/fileexchangeissue

• Press F12
• switch Emulation to 10

Dynamics GP - VBA - Microsoft Visual Basic - Run-time error '70': Permission denied

This error occurs when trying to use the ADODB UserinfoGet function.

This issue is resolved if any hotfix after and including Hotfix GP2015 (14.00.0661)
https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/MDGP2015_PatchReleases

Dynamics GP - Web Client prints extra header and footer

https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/09/13/additional-lines-added-to-page-when-printing-from-dynamics-gp-web-client

• Click on the Gear>Print>Page Setup
• Set all header and footer options to -Empty-

SQL - View to Determine Table Sizes. Get Table Sizes in SQL.

Original Post
http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

-------------------------------------------------------------------------------------------------------
SELECT        TableName, SchemaName, RowCounts, TotalSpaceKB, UsedSpaceKB, UnusedSpaceKB
FROM            (SELECT        TOP (100) PERCENT t.name AS TableName, s.name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages)
                                                    - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
                          FROM            sys.tables AS t INNER JOIN
                                                    sys.indexes AS i ON t.object_id = i.object_id INNER JOIN
                                                    sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN
                                                    sys.allocation_units AS a ON p.partition_id = a.container_id LEFT OUTER JOIN
                                                    sys.schemas AS s ON t.schema_id = s.schema_id
                          WHERE        (t.name NOT LIKE 'dt%') AND (t.is_ms_shipped = 0) AND (i.object_id > 255)
                          GROUP BY t.name, s.name, p.rows
                          ORDER BY TableName) AS Size
ORDER BY UsedSpaceKB desc
--------------------------------------------------------------------------------------------------

Dynamics GP - Manufacturing Account Movement - Accounts Required

• Minimum Accounts Required for Manufacturing
• RM Inventory Accounts
• FG Inventory Accounts
• FG WIP Material Accounts
• Mfg Costing>Rounding Difference Account
• Damages account
• Overhead Applied accounts
• Examples include:
• Labor
• Admin
• Line
• Machine
• Electricity
• Maintenance
• Full Manufacturing Process
• Create MO
• Schedule
• Build Picklist
• Allocate Raw Materials
• Issue Raw Materials and other costs
• Create Finished Goods Receipt
• Data Entry
• Close MO
• Quick MO with Backflush
• Create MO
• Save and Build Picklist
• Automatically Schedule and build picklist
• Automatically Allocate and Issue Raw Materials (Mfg Setup)
• Close MO
• Automatically receive all Finished Goods
• Automatically Backflush all raw materials and costs
• Credit RM Inventory Account, Debit FG WIP Material Account
• Credit FG WIP Material Account, Debit FG Inventory Account

Dynamics NAV - LS Retail - Windows 7 - Error when pressing hotkey - search:crumb=locationC%3A%5CUser%5C...........%5CDesktop

• These errors occur on Windows 7 due to broken search indexes.
• Follow this guide to repair it 
• http://www.guidingtech.com/18966/fix-windows-7-search-indexing-issues/
• Then do a clean boot

For LS NAV 2017, if the hotkeys do not work, you have to set the rows and columns for the Fixed menu. It has to be a fixed key menu.

Dynamics GP - Automated Check Links - Automatic Login and Check Links Macro and Login and Reconcile Macro

http://mohdaoud.blogspot.com/2008/10/auto-login-for-microsoft-dynamics-gp_2192.html

Copy the macro into the C:\Program Files (x86)\Microsoft Dynamics\GP2015\ Folder

Create a Batch file with the following line. Schedule the Batch file to be executed using windows scheduler, or system scheduler
-----------------------------------------------------------------
cd C:\Program Files (x86)\Microsoft Dynamics\GP2015\
"C:\Program Files (x86)\Microsoft Dynamics\GP2015\Dynamics.exe" Dynamics.set Loginchecklinks.mac
-----------------------------------------------------------------

Autologin Macro GP2015
-----------------------------
Logging file 'macro.log'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'User ID'
TypeTo field 'User ID' , 'sa'
MoveTo field Password
TypeTo field Password , 'password'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # ''
MoveTo field 'OK Button'
ClickHit field 'OK Button'
CommandExec dictionary 'default' form 'Command_System' command CloseAllWindows
ActivateWindow dictionary 'default' form Toolbar window 'Main_Menu_1'

------------------------------

Check Links Macro

-------------------------------
CheckActiveWin dictionary 'default'  form 'SY_Check_Links' window 'Check Links'
NewActiveWin dictionary 'default'  form sheLL window sheLL
  CommandExec dictionary 'default'  form 'Command_System' command 'SY_Check_Links'
ActivateWindow dictionary 'default'  form 'SY_Check_Links' window 'Check Links'
ActivateWindow dictionary 'default'  form 'SY_Check_Links' window 'Check Links'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 1  # 'Financial'
  ClickHit field 'File Series' item 2  # 'Sales'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 2  # 'Sales'
  ClickHit field 'File Series' item 3  # 'Purchasing'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 3  # 'Purchasing'
  ClickHit field 'File Series' item 4  # 'Inventory'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 4  # 'Inventory'
  ClickHit field 'File Series' item 7  # 'System'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 7  # 'System'
  ClickHit field 'File Series' item 8  # 'Company'
  MoveTo field 'Insert All Button'
  ClickHit field 'Insert All Button'
  MoveTo field 'File Series' item 8  # 'Company'
  CommandExec dictionary 'default'  form 'SY_Check_Links' command 'OK Button_w_Check Links_f_SY_Check_Links'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'
  MoveTo field 'OK Button'
  ClickHit field 'OK Button'
-------------------------------------------------------

Inventory Reconcile Macro

------------------------------------------------------
NewActiveWin dictionary 'default'  form sheLL window sheLL
  CommandExec dictionary 'default'  form 'Command_Inventory' command 'IV_Reconcile'
ActivateWindow dictionary 'default'  form 'IV_Reconcile' window 'IV_Reconcile'
ActivateWindow dictionary 'default'  form 'IV_Reconcile' window 'IV_Reconcile'
  CommandExec dictionary 'default'  form 'IV_Reconcile' command 'Process Button P_w_IV_Reconcile_f_IV_Reconcile'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'
  MoveTo field 'OK Button'
  ClickHit field 'OK Button'
NewActiveWin dictionary 'DEX.DIC'  form 'Report Destination' window 'Report Type'
  MoveTo field '(L) OK'
  ClickHit field '(L) OK'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'
NewActiveWin dictionary 'DEX.DIC'  form 'Report Destination' window 'Report Type'
  MoveTo field '(L) OK'
  ClickHit field '(L) OK'
NewActiveWin dictionary 'default'  form 'Report_Destination' window 'Report_Destination'

Dynamics GP Manufacturing - Prevent MO Receipts from posting if there is insufficient Raw Material or component stock

Add This code to the Manufacturing Order Receipt Entry window.
This only works if Inventory Override Adjustments are disabled

------------------------------------------------------------------------------------------------
Private Sub Window_BeforeModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)
   
'View>Immediate to capture exact wording of error prompts
'Debug.Print PromptString
'Debug.Print "Button 1: " & Control1String
'Debug.Print "Button 2: " & Control2String
'Debug.Print "Button 3: " & Control3String
   
   
    Dim ErrMsg
    ErrMsg = "There is insufficient stock of at least one component. Ensure that adequate component stock is available before posting a receipt."
   
    If PromptString = "You haven't backflushed the planned quantity for at least one component.  Do you want to continue?" Then
        MsgBox ErrMsg, vbExclamation
        Answer = dcButton2
    End If
   
    If PromptString = "At least one component has a shortage that has been overridden.  Do you want to continue?" Then
    MsgBox ErrMsg, vbExclamation
    Answer = dcButton2
    End If
   
    If PromptString = "A quantity shortage exists for this item.  Would you like to use the available quantity or cancel?" Then
    MsgBox ErrMsg, vbExclamation
    Answer = dcButton2
    End If
End Sub

Dynamics GP - Purchasing Tables

• POP10100 - PO Hdr
• POP10110 - PO Line
• Source_Document_Number - reqlogic number

Dynamics GP - Inventory - Assembly Bill of Materials Tables

• BM00101 - BOM Header
• BM00111 - BOM Detail
• BM10200 - Work Assembly Trx Hdr
• BM10300 - Work Assembly Trx Dtl
• BM30200 - Posted Assembly Trx Hdr
• BM30300 - Posted Assembly Trx Dtl

SSRS Training Topics

• Basic
• Setup
• How to install SSRS
• How to Manage SSRS
• 
  
• Security
• Data Sources
• SQL Views
• Reports
• Advanced
• Advanced SQL Views
• Stored Procedures
• Reporting Tricks
• Exercises
• Build a Sales Report
• Group and filter by Customer, Item
• Show Sales, Vat, Profit

Implementation Methodology

• Definition - What are we doing? Is this realistic?
• Project Planning
• Business Objectives
• Feasibility
• Time
• Resources
• Budget
• Analysis - What do you REALLY want? in Detail
• Business Requirements (Fit gap) - How are we meeting each requirement?
• Workarounds
• Screen Layouts - Do any screens need to be modified?
• Report Layouts - Do any reports need to be modified?
• System Architecture Requirements - What will we need to run this?
• Future Business Processes - What processes will change?
• Transition Strategy - What has to start changing from now
• Reconciliation plan - Internal audit before and after go live
• Data Migration Map/Template Workshops - What data is going into the new system?
• Install Software
• Install sample data
• Create Live and Test environments
• Configure Backups
• Enable Logging
• Enable Archiving
• Design - This is how we're gonna do it, it's going to cost more
• Rapid Process Test
• Run through all future processes in Test system
• Confirm Forms and Reports changes in Test system
• Finalize Customizations - Signoff
• Define all scope changes required to meet Final Requirements
• Module Configurations - Signoff
• FDD
• Develop - Build it. Test it. Test it. Test it some more.
• Build Customizations
• Test Customizations
• Configure System
• Test Import Data
• Historical Data
• Current Data
• Confirm GL, AR, AP, INV MUST reconcile
• Pilot Test Entire Integrated System
• Test Scripts
• Test Security
• Test Reconcile
• Mock Go-Live
• Go Live Readiness Review and Discussion
• Post mortem on all issues during mock go-live
• Agree on a realistic Go-Live date
• Deploy- This is how you use it
• End User Training
• User Manuals
• Change Management
• Final Data Migration
• Current Data
• Confirm GL, AR, AP, INV MUST reconcile
• Take Final Backups of all Customizations
• Operate - Start using it
• Go-Live
• Support - Use it better
• Support
• Resolve Red Flag Issues Immediately
• Refine Configurations
• Refine Modifications
• Additional Modifications

SSRS Data Source Connection String

Data Source= SRV-GPS;Initial Catalog=TWO;Integrated Security=False


SRV-GPS = SQL Server name\instance
TWO = Database name

eOne Smartconnect - Scheduled maps stop running for no reason

This is an issue that they are currently working on 

1.  Scheduling the SmartConnect scheduling service to restart once a day.  This will solve any sort of "out of memory" issues, or issues where the scheduler shows it's running, but it won't pick up any maps.  I wrote a blog article on how to use a batch file to restart the scheduling service using the Windows task scheduler, you can find it here: http://www.eonesolutions.com/blog-post/restart-smartconnect-scheduling-service-using-batch-file/

Create a batch file with these lines
---------------------------------------------------
taskkill /F /IM eOne.SmartConnect.WindowsService.exe
timeout 30
sc start eOne.SmartConnect.WindowsService.exe

----------------------------------------------------

2.  Creating a SQL job to delete any users that have been stuck in maps longer than 15 minutes.  So if you have a map that is repeating say every 10 minutes for the entire day, and a user gets stuck in it for longer than 15 minutes, then you know the map is stuck.  So then you create a SQL job that deletes any activity records older than 15 minutes, and then have the job run every 15 minutes.  

delete MapActivity where CheckType='Service' and DateTime <=dateadd(minute,-15,getdate())

This can be created in a sql stored procedure in the master db and be called from anywhere

A sql job can also be created to restart the smartconnect service

-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO
xp_cmdshell 'NET STOP eOne.SmartConnect.WindowsService.exe'

xp_cmdshell 'NET START eOne.SmartConnect.WindowsService.exe'

3. The schedule may be set badly causing it to not repeat correctly.
Avoid the following situations.

• Do not schedule multiple maps to begin at the same time
• Set all daily repeating schedules as follows
• start time is 12am
• duration is 1440 = 24hrs
• If you need to run multiple maps, make one map as master, let it run all other maps so they don't bump into each other

A - b
A - C
A - D


don't daisy chain maps

Data Director - Conversion Overflows Error

Change the option in the DD server under App Config>Decimal Fix to "BIG"
Send Config
Restart DD service

Dynamics GP - Manufacturing - Understanding how Manufacturing Calculates FG Costs - Why costing errors occur - Costing is higher than expected

• From a Recipe Level - Direct Costs Come From
• Machine Setup
• Labor Setup
• Routing Setup
• Component Costs on Manufacturing Bill of Materials
• From a Production Level - Costs are affected by
• Final Costs are calculated when the MO is closed
• Using Item Cost at the time it was issued to the MO
• Machine Costs Issued /backflushed to the MO
• Labor Costs issued/backflushed to the MO
• Minus Cost of all Reverse Issued raw materials
• If Backflushing
• Raw Material is not issued, and costs are not reflected in pick tickets or in WIP
• Raw Material is adjusted out of stock when the Finished Good Receipt is posted
• Causes of Costing errors
• Rounding errors due to UofM factors that may go beyond 5 decimal places
• When setting up a BOM, notice that quantities may automatically adjust themselves on the BOM when first entered due to rounding limitations of the UofM selected
• Changing Default Qty to Backflush
• If your raw materials are set to backflush, changing the quantity of raw materials used "consumes" from the total quantity of raw materials GP thinks you should be using for that entire MO, even though it will cost that receipt correctly
• Example - Bom that uses 1 unit of rm to produce 1 unit of fg
• I do an MO for 2 units of FG
• I do a receipt for 1 unit of FG, but change the RM from 1 unit of RM to 2 units of rm
• GP will cost that 1 fg at the cost of the 2 units of rm consumed to produce it
• However, if i try to receive 1 more fg, GP suggests 0 rm because it assumes you have already consumed all the rm required for this MO
• At this point, i have to manually enter the additional RM i am using
• This is a problem if calculating the RM is complicated
• Receiving additional Finished Goods against a Backflush MO that is "Completed"
• GP will not automatically backflush more Raw materials to the fully received MO
• If you do not manually add more raw materials to the MO, your costing will come out incorrect
• If using Average Costing for Inventory, Finished Goods and Raw Material Costs can fluctuate with each issue and receipt depending on what other transactions may have occurred between the Manufacturing transactions
• Guidelines to prevent Costing errors
• Ensure that the Base UofM of the Raw Material is in the smallest quantities being used
• Instead of using 0.00001 kgs, your base unit should be in grams
• Do not receive against "Completed"  MO's

Dynamics GP - Posting Setup Tables - How to change the File Export Destination Name on the fly

How to get posting reports to print to a file including the audit trail code in the file name

select * from sy02200 - Posting Destination file name
select * from sy01000 - Next Audit Trail Code

Change the Filexpnm field just before printing using vbscript and a sql update query on the print button

Dynamics GP - Manufacturing - Receiving Additional Finished goods Against a Completed MO - Backflush Costs go to 0

Problem:

• Create a BOM, set it to Backflush
• Create an MO for 100, Release it
• Create a receipt for 100, increase all backflush quantities
• Attempt to receive another 1 against the MO, note the component quantities and costs
• Attempt to receive ANOTHER 1 against the MO, note the component quantities and costs
• The costs and quantities start calculating incorrectly when receiving additional quantities against a completed MO
• Closing the MO results in incorrect Item Costs and Component Usage

Resolution:

• Do not receive against completed MO's if using Backflush

Dynamics GP - How to Reset the System Password

https://community.dynamics.com/gp/b/gpfrankhamelly/archive/2010/01/20/gptip42day-resetting-the-gp-system-password


   UPDATE DYNAMICS.SY02400 
           SET PASSWORD = 0x0202020202020202020202020202020 

Dynamics GP - Error after upgrade "Trigger is not registered"

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2012/01/31/quick-tip-solving-dexterity-trigger-clashes/

• This problem occurs due to GP customizations that are triggering multiple code units on the same event
• Changing the order of the customized dictionaries in the Dynamics.set file will ensure the triggers are loaded in the correct order
• Disable dictionaries in your dynamics.set one by one and try launching GP to determine which dictionary is causing the trigger error
• Once you figure out which dictionary is causing the error, Edit your Dynamics.set file, and move the offending dictionary up so that it loads first

Dynamics GP - Extended Pricing Views

/****** Object:  View [dbo].[BI_BookSheets]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_BookSheets]
AS
SELECT     dbo.RM00500.PRCSHID, dbo.RM00500.PRODTCOD, dbo.RM00500.LINKCODE, dbo.RM00500.SEQNUMBR, dbo.RM00500.PSSEQNUM, dbo.RM00500.DEX_ROW_ID,
                      dbo.SOP10109.DESCEXPR AS PrcBookDesc, CASE WHEN sop10109.isbase = 1 THEN 'Basebook' ELSE '' END AS BaseBkLbl, dbo.SOP10109.ISBASE
FROM         dbo.RM00500 LEFT OUTER JOIN
                      dbo.SOP10109 ON dbo.RM00500.LINKCODE = dbo.SOP10109.PRCBKID
WHERE     (dbo.RM00500.PRODTCOD = 'P')

GO
/****** Object:  View [dbo].[BI_Items]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Items]
AS
SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.ITMCLSCD, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3,
                      dbo.IV00101.USCATVLS_4, dbo.IV00101.USCATVLS_5, dbo.IV00101.USCATVLS_6, dbo.IV00101.ITEMSHWT / 100 AS Wgt, dbo.IV40201.BASEUOFM,
                      dbo.IV00101.DECPLCUR, dbo.IV00101.DECPLQTY, dbo.IVR10015.ITEMSTATUS_I, dbo.IV00101.ITMTSHID AS ItmTaxSched, dbo.IV00101.ITEMTYPE,
                      CASE WHEN itemstatus_i = 1 THEN 'Item Engineering Active' ELSE 'Item Engrineering INACTIVE' END AS IEStatusLbl,
                      CASE WHEN itemtype = 1 THEN 'Sales Inventory' WHEN itemtype = 2 THEN 'Discontinued' WHEN itemtype = 3 THEN 'Kit' WHEN itemtype = 4 THEN 'Misc Charges' WHEN
                       itemtype = 5 THEN 'Services' WHEN itemtype = 6 THEN 'Flat Fee' END AS ItemTypeLbl
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      dbo.IVR10015 ON dbo.IV00101.ITEMNMBR = dbo.IVR10015.ITEMNMBR

GO
/****** Object:  View [dbo].[BI_ExtPrcGrpDetails]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcGrpDetails]
AS
SELECT     dbo.IV10402.PRCSHID AS PriceSheet, dbo.IV10402.EPITMTYP AS PriceType, dbo.BI_Items.ITEMDESC, dbo.IV10402.UOFM, dbo.IV10402.QTYFROM,
                      dbo.IV10402.QTYTO, dbo.IV10402.PSITMVAL AS Price, dbo.IV10402.EQUOMQTY, dbo.IV10402.QTYBSUOM, dbo.IV10402.SEQNUMBR, dbo.IV10402.DEX_ROW_ID,
                      dbo.IV10401.PRODTCOD, dbo.BI_Items.ITMCLSCD, dbo.BI_Items.USCATVLS_1, dbo.BI_Items.USCATVLS_2, dbo.BI_Items.USCATVLS_3, dbo.BI_Items.USCATVLS_4,
                      dbo.BI_Items.USCATVLS_5, dbo.BI_Items.USCATVLS_6, dbo.BI_Items.Wgt, dbo.BI_Items.BASEUOFM, dbo.BI_Items.DECPLCUR, dbo.BI_Items.DECPLQTY,
                      dbo.BI_Items.ITEMSTATUS_I AS IEItemStatus, dbo.BI_Items.ItmTaxSched, dbo.IV10401.ACTIVE AS PriceIsActive, dbo.IV10401.PROMOTYP, dbo.IV10401.PROMOLVL,
                      dbo.SOP10110.ACTIVE AS PriceShtIsActive, dbo.SOP10110.STRTDATE AS PriceShtStart, dbo.SOP10110.ENDDATE AS PriceShtEnd,
                      dbo.SOP10110.CURNCYID AS PriceShtCurr, dbo.SOP10110.PROMO AS PriceShtShtPromo, dbo.SOP10110.DESCEXPR AS PriceShtDesc,
                      dbo.SOP10110.NTPRONLY AS IsNetPrice, dbo.BI_BookSheets.LINKCODE AS PriceBook, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)
                      AS PrcShtLbl, dbo.BI_Items.ITEMTYPE, dbo.BI_Items.IEStatusLbl, dbo.BI_Items.ItemTypeLbl,
                      CASE WHEN sop10110.active = 1 THEN 'Price Sheet is Active' ELSE 'Price Sheet INACTIVE' END AS PrcShtActiveLbl,
                      CASE WHEN IV10401.active = 1 THEN 'Price Line is Active' ELSE 'Price Line INACTIVE' END AS PrcLnActiveLbl,
                      CASE WHEN Promo = 1 THEN 'Promotional Sheet' ELSE '' END AS PrcShtPromoLbl, dbo.BI_BookSheets.PrcBookDesc, dbo.BI_BookSheets.BaseBkLbl,
                      dbo.BI_BookSheets.ISBASE, dbo.SOP10110.PRCSHID, dbo.BI_Items.ITEMNMBR, dbo.IV10400.PRCGRPID
FROM         dbo.IV10402 INNER JOIN
                      dbo.IV10401 ON dbo.IV10402.PRCSHID = dbo.IV10401.PRCSHID AND dbo.IV10402.ITEMNMBR = dbo.IV10401.ITEMNMBR INNER JOIN
                      dbo.SOP10110 ON dbo.IV10401.PRCSHID = dbo.SOP10110.PRCSHID INNER JOIN
                      dbo.IV10400 ON dbo.IV10401.ITEMNMBR = dbo.IV10400.PRCGRPID INNER JOIN
                      dbo.BI_Items ON dbo.IV10400.ITEMNMBR = dbo.BI_Items.ITEMNMBR LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.SOP10110.PRCSHID = dbo.BI_BookSheets.PRCSHID
WHERE     (dbo.BI_Items.ITEMTYPE <> 2)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails]
AS
SELECT     dbo.IV10402.PRCSHID AS PriceSheet, dbo.IV10402.EPITMTYP AS PriceType, dbo.BI_Items.ITEMDESC, dbo.IV10402.UOFM, dbo.IV10402.QTYFROM,
                      dbo.IV10402.QTYTO, dbo.IV10402.PSITMVAL AS Price, dbo.IV10402.EQUOMQTY, dbo.IV10402.QTYBSUOM, dbo.IV10402.SEQNUMBR, dbo.IV10402.DEX_ROW_ID,
                      dbo.IV10401.PRODTCOD, dbo.BI_Items.ITMCLSCD, dbo.BI_Items.USCATVLS_1, dbo.BI_Items.USCATVLS_2, dbo.BI_Items.USCATVLS_3, dbo.BI_Items.USCATVLS_4,
                      dbo.BI_Items.USCATVLS_5, dbo.BI_Items.USCATVLS_6, dbo.BI_Items.Wgt, dbo.BI_Items.BASEUOFM, dbo.BI_Items.DECPLCUR, dbo.BI_Items.DECPLQTY,
                      dbo.BI_Items.ITEMSTATUS_I AS IEItemStatus, dbo.BI_Items.ItmTaxSched, dbo.IV10401.ACTIVE AS PriceIsActive, dbo.IV10401.PROMOTYP, dbo.IV10401.PROMOLVL,
                      dbo.SOP10110.ACTIVE AS PriceShtIsActive, dbo.SOP10110.STRTDATE AS PriceShtStart, dbo.SOP10110.ENDDATE AS PriceShtEnd,
                      dbo.SOP10110.CURNCYID AS PriceShtCurr, dbo.SOP10110.PROMO AS PriceShtShtPromo, dbo.SOP10110.DESCEXPR AS PriceShtDesc,
                      dbo.SOP10110.NTPRONLY AS IsNetPrice, dbo.BI_BookSheets.LINKCODE AS PriceBook, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)
                      AS PrcShtLbl, dbo.BI_Items.ITEMTYPE, dbo.BI_Items.IEStatusLbl, dbo.BI_Items.ItemTypeLbl,
                      CASE WHEN sop10110.active = 1 THEN 'Price Sheet is Active' ELSE 'Price Sheet INACTIVE' END AS PrcShtActiveLbl,
                      CASE WHEN IV10401.active = 1 THEN 'Price Line is Active' ELSE 'Price Line INACTIVE' END AS PrcLnActiveLbl,
                      CASE WHEN Promo = 1 THEN 'Promotional Sheet' ELSE '' END AS PrcShtPromoLbl, dbo.BI_BookSheets.PrcBookDesc, dbo.BI_BookSheets.BaseBkLbl,
                      dbo.BI_BookSheets.ISBASE, dbo.SOP10110.PRCSHID, dbo.BI_Items.ITEMNMBR, '' AS PrcGrpID
FROM         dbo.IV10402 INNER JOIN
                      dbo.IV10401 ON dbo.IV10402.PRCSHID = dbo.IV10401.PRCSHID AND dbo.IV10402.ITEMNMBR = dbo.IV10401.ITEMNMBR INNER JOIN
                      dbo.BI_Items ON dbo.IV10402.ITEMNMBR = dbo.BI_Items.ITEMNMBR INNER JOIN
                      dbo.SOP10110 ON dbo.IV10401.PRCSHID = dbo.SOP10110.PRCSHID LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.SOP10110.PRCSHID = dbo.BI_BookSheets.PRCSHID
WHERE     (dbo.BI_Items.ITEMTYPE <> 2)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails_All]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails_All]
AS
SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD, USCATVLS_1,
                      USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus, ItmTaxSched, PriceIsActive,
                      PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc, IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE,
                      IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl, ISBASE, PRCSHID, ITEMNMBR, PrcGrpID
FROM         (SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD,
                                              USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus,
                                              ItmTaxSched, PriceIsActive, PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc,
                                              IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE, IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl,
                                              ISBASE, PRCSHID, ITEMNMBR, PrcGrpID
                       FROM          dbo.BI_ExtPrcDetails
                       UNION
                       SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD,
                                             USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus,
                                             ItmTaxSched, PriceIsActive, PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc,
                                             IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE, IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl,
                                             ISBASE, PRCSHID, ITEMNMBR, PRCGRPID
                       FROM         dbo.BI_ExtPrcGrpDetails) AS AllPrc

GO
/****** Object:  View [dbo].[BI_SheetCus]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SheetCus]
AS
SELECT     PRCSHID, PRODTCOD, LINKCODE, SEQNUMBR, PSSEQNUM, DEX_ROW_ID
FROM         dbo.RM00500
WHERE     (PRODTCOD = 'C')

GO
/****** Object:  View [dbo].[BI_PriceAssign]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PriceAssign]
AS
SELECT     dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.BI_SheetCus.PRCSHID AS PrcSht, dbo.BI_BookSheets.PRCSHID AS PrcBook,
                      dbo.SOP10110.STRTDATE AS PrcShtStart, dbo.SOP10110.ENDDATE AS PrcShtEnd, dbo.BI_SheetCus.DEX_ROW_ID, dbo.SOP10110.DESCEXPR AS PrcShtDesc,
                      RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR) AS PrcShtLBl
FROM         dbo.SOP10110 RIGHT OUTER JOIN
                      dbo.BI_SheetCus ON dbo.SOP10110.PRCSHID = dbo.BI_SheetCus.PRCSHID LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.BI_SheetCus.PRCSHID = dbo.BI_BookSheets.PRCSHID LEFT OUTER JOIN
                      dbo.RM00101 ON dbo.BI_SheetCus.LINKCODE = dbo.RM00101.CUSTNMBR
GROUP BY dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.BI_SheetCus.PRCSHID, dbo.BI_BookSheets.PRCSHID, dbo.SOP10110.STRTDATE,
                      dbo.SOP10110.ENDDATE, dbo.BI_SheetCus.DEX_ROW_ID, dbo.SOP10110.DESCEXPR, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails_Bycus]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails_Bycus]
AS
SELECT     dbo.BI_PriceAssign.CUSTNMBR, dbo.BI_PriceAssign.CUSTNAME, dbo.BI_ExtPrcDetails_All.PriceSheet, dbo.BI_ExtPrcDetails_All.PriceType,
                      dbo.BI_ExtPrcDetails_All.ITEMNMBR, dbo.BI_ExtPrcDetails_All.ITEMDESC, dbo.BI_ExtPrcDetails_All.UOFM, dbo.BI_ExtPrcDetails_All.QTYFROM,
                      dbo.BI_ExtPrcDetails_All.QTYTO, dbo.BI_ExtPrcDetails_All.Price, dbo.BI_ExtPrcDetails_All.EQUOMQTY, dbo.BI_ExtPrcDetails_All.QTYBSUOM,
                      dbo.BI_ExtPrcDetails_All.SEQNUMBR, dbo.BI_ExtPrcDetails_All.DEX_ROW_ID, dbo.BI_ExtPrcDetails_All.PRODTCOD, dbo.BI_ExtPrcDetails_All.ITMCLSCD,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_1, dbo.BI_ExtPrcDetails_All.USCATVLS_2, dbo.BI_ExtPrcDetails_All.USCATVLS_3, dbo.BI_ExtPrcDetails_All.USCATVLS_4,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_5, dbo.BI_ExtPrcDetails_All.USCATVLS_6, dbo.BI_ExtPrcDetails_All.Wgt, dbo.BI_ExtPrcDetails_All.BASEUOFM,
                      dbo.BI_ExtPrcDetails_All.DECPLCUR, dbo.BI_ExtPrcDetails_All.DECPLQTY, dbo.BI_ExtPrcDetails_All.IEItemStatus, dbo.BI_ExtPrcDetails_All.ItmTaxSched,
                      dbo.BI_ExtPrcDetails_All.PriceIsActive, dbo.BI_ExtPrcDetails_All.PROMOTYP, dbo.BI_ExtPrcDetails_All.PROMOLVL, dbo.BI_ExtPrcDetails_All.PriceShtIsActive,
                      dbo.BI_ExtPrcDetails_All.PriceShtStart, dbo.BI_ExtPrcDetails_All.PriceShtEnd, dbo.BI_ExtPrcDetails_All.PriceShtCurr, dbo.BI_ExtPrcDetails_All.PriceShtShtPromo,
                      dbo.BI_ExtPrcDetails_All.PriceShtDesc, dbo.BI_ExtPrcDetails_All.IsNetPrice, dbo.BI_ExtPrcDetails_All.PriceBook, dbo.BI_ExtPrcDetails_All.PrcShtLbl,
                      dbo.BI_ExtPrcDetails_All.ITEMTYPE, dbo.BI_ExtPrcDetails_All.IEStatusLbl, dbo.BI_ExtPrcDetails_All.ItemTypeLbl, dbo.BI_ExtPrcDetails_All.PrcShtActiveLbl,
                      dbo.BI_ExtPrcDetails_All.PrcLnActiveLbl, dbo.BI_ExtPrcDetails_All.PrcShtPromoLbl, dbo.BI_ExtPrcDetails_All.PrcBookDesc, dbo.BI_ExtPrcDetails_All.BaseBkLbl,
                      dbo.BI_ExtPrcDetails_All.ISBASE, dbo.BI_ExtPrcDetails_All.PRCSHID, dbo.BI_ExtPrcDetails_All.PrcGrpID
FROM         dbo.BI_ExtPrcDetails_All INNER JOIN
                      dbo.BI_PriceAssign ON dbo.BI_ExtPrcDetails_All.PriceSheet = dbo.BI_PriceAssign.PrcSht
GROUP BY dbo.BI_PriceAssign.CUSTNMBR, dbo.BI_PriceAssign.CUSTNAME, dbo.BI_ExtPrcDetails_All.PriceSheet, dbo.BI_ExtPrcDetails_All.PriceType,
                      dbo.BI_ExtPrcDetails_All.ITEMNMBR, dbo.BI_ExtPrcDetails_All.ITEMDESC, dbo.BI_ExtPrcDetails_All.UOFM, dbo.BI_ExtPrcDetails_All.QTYFROM,
                      dbo.BI_ExtPrcDetails_All.QTYTO, dbo.BI_ExtPrcDetails_All.Price, dbo.BI_ExtPrcDetails_All.EQUOMQTY, dbo.BI_ExtPrcDetails_All.QTYBSUOM,
                      dbo.BI_ExtPrcDetails_All.SEQNUMBR, dbo.BI_ExtPrcDetails_All.DEX_ROW_ID, dbo.BI_ExtPrcDetails_All.PRODTCOD, dbo.BI_ExtPrcDetails_All.ITMCLSCD,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_1, dbo.BI_ExtPrcDetails_All.USCATVLS_2, dbo.BI_ExtPrcDetails_All.USCATVLS_3, dbo.BI_ExtPrcDetails_All.USCATVLS_4,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_5, dbo.BI_ExtPrcDetails_All.USCATVLS_6, dbo.BI_ExtPrcDetails_All.Wgt, dbo.BI_ExtPrcDetails_All.BASEUOFM,
                      dbo.BI_ExtPrcDetails_All.DECPLCUR, dbo.BI_ExtPrcDetails_All.DECPLQTY, dbo.BI_ExtPrcDetails_All.IEItemStatus, dbo.BI_ExtPrcDetails_All.ItmTaxSched,
                      dbo.BI_ExtPrcDetails_All.PriceIsActive, dbo.BI_ExtPrcDetails_All.PROMOTYP, dbo.BI_ExtPrcDetails_All.PROMOLVL, dbo.BI_ExtPrcDetails_All.PriceShtIsActive,
                      dbo.BI_ExtPrcDetails_All.PriceShtStart, dbo.BI_ExtPrcDetails_All.PriceShtEnd, dbo.BI_ExtPrcDetails_All.PriceShtCurr, dbo.BI_ExtPrcDetails_All.PriceShtShtPromo,
                      dbo.BI_ExtPrcDetails_All.PriceShtDesc, dbo.BI_ExtPrcDetails_All.IsNetPrice, dbo.BI_ExtPrcDetails_All.PriceBook, dbo.BI_ExtPrcDetails_All.PrcShtLbl,
                      dbo.BI_ExtPrcDetails_All.ITEMTYPE, dbo.BI_ExtPrcDetails_All.IEStatusLbl, dbo.BI_ExtPrcDetails_All.ItemTypeLbl, dbo.BI_ExtPrcDetails_All.PrcShtActiveLbl,
                      dbo.BI_ExtPrcDetails_All.PrcLnActiveLbl, dbo.BI_ExtPrcDetails_All.PrcShtPromoLbl, dbo.BI_ExtPrcDetails_All.PrcBookDesc, dbo.BI_ExtPrcDetails_All.BaseBkLbl,
                      dbo.BI_ExtPrcDetails_All.ISBASE, dbo.BI_ExtPrcDetails_All.PRCSHID, dbo.BI_ExtPrcDetails_All.PrcGrpID

GO

/****** Object:  View [dbo].[BI_ExtPrc_BasePrices]    Script Date: 03/29/2017 10:20:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ExtPrc_BasePrices]
AS
SELECT     ITEMNMBR, MAX(Price) AS Price, QTYFROM, ISBASE, PriceShtIsActive, PriceShtCurr, MAX(PriceShtStart) AS PriceShtStart, MAX(PriceShtEnd) AS PriceShtEnd,
                      UOFM
FROM         dbo.BI_ExtPrcDetails_All
GROUP BY ITEMNMBR, QTYFROM, ISBASE, PriceShtIsActive, PriceShtCurr, UOFM
HAVING      (QTYFROM <= 1) AND (ISBASE = 1) AND (PriceShtIsActive = 1) AND (PriceShtCurr = 'TTD') AND (MAX(PriceShtStart) <= GETDATE()) AND (MAX(PriceShtEnd)
                      >= GETDATE())

GO

/****** Object:  View [dbo].[BI_ItemMaster]    Script Date: 12/02/2019 09:26:55 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ItemMaster]
AS
SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.UOMSCHDL, dbo.IV40201.BASEUOFM, dbo.IV40201.UMDPQTYS, dbo.IV00101.ITMCLSCD,
                      dbo.IV00101.CURRCOST, dbo.IV00101.ITMTRKOP, dbo.IV00101.LOTTYPE, dbo.IV00101.CREATDDT, dbo.IV00101.MODIFDT, DATEDIFF(d, dbo.IV00101.CREATDDT,
                      GETDATE()) AS AgeDays, dbo.IV40400.ITMCLSDC, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.USCATVLS_4,
                      dbo.IV00101.USCATVLS_5, dbo.IV00101.USCATVLS_6, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + RTRIM(dbo.IV00101.ITEMDESC) AS ItemLbl,
                      LEFT(dbo.IV00101.ITEMNMBR, 1) AS A, CASE WHEN LEFT(dbo.IV00101.ITEMNMBR, 1) = 'a' THEN 'Matouks' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1)
                      = 'b' THEN 'MP' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1) IN ('c', 'r') THEN 'Mabel' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1)
                      = 'e' THEN 'Eve' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1) = 'i' THEN 'National' WHEN LEFT(dbo.IV00101.ITEMNMBR, 1)
                      = 'h' THEN 'Home' ELSE '(NONE)' END AS Brand
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD

GO

/****** Object:  View [dbo].[BI_ItemMasterFG]    Script Date: 12/02/2019 09:26:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ItemMasterFG]
AS
SELECT     ITEMNMBR, ITEMDESC, UOMSCHDL, BASEUOFM, UMDPQTYS, ITMCLSCD, CURRCOST, ITMTRKOP, LOTTYPE, CREATDDT, MODIFDT, AgeDays, ITMCLSDC,
                      USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, ItemLbl, ITEMNMBR AS [Item Number], ItemLbl AS ItemIDName, A, Brand
FROM         dbo.BI_ItemMaster
WHERE     (ITMCLSCD LIKE '%FG%') OR
                      (ITMCLSCD IN ('ADVERTISIN', 'CREDITMEMO'))

GO