Saturday, January 31, 2015

Friday, January 30, 2015

Windows Server - Install Dot Net 3.5

You cannot install Dot Net 3.5 from any redistributable or downloadable source.

You must have a Windows Server 2012 Install Media
You cannot just copy the sources\sxs folder

To install it go to

  • Load your windows Server 2012 Install Media
  • Server Manager>Add Roles>Add Feature>Dot Net 3.5
  • Click on Alternate path at the bottom
  • enter the path for your install media
    • E:\sources\sxs
  • complete the install

Dynamics NAV - Training Topics and Sessions


  • Foundation
    • Login
      • Work Date
      • Company
    • Window and Layout
      • Back, Forward
      • Search Bar
      • Tool Ribbon
      • How to Personalize Ribbons
      • Customize Navigation Pane
    • Function Keys and shortcuts
      • NAV Blue Drop down options 
      • Copy Link to
      • F1 Help
      • About this Page
      • Ctrl + E - Export to excel
      • F8 to copy above row value
    • Menu Terminology
      • List Pages
      • Card Pages
        • Fast tabs
          • Promoted Fields
    • Drilldowns
    • Lookups
    • Filters
    • Views
    • Exports
    • Reporting & BI
      • Excel
      • Charts
  • IT Admin
    • NAV Client Installation
    • User Maintenance
      • User Creation
      • User Personalization
      • Security
        • Permission Sets
          • Super
          • Record Level security
          • Field Level security
            • Dev>Table> Field>Properties>Accessbypermission
        • LS Retail - Staff
          • Staff creation
          • Staff permission group
            • Manager, Lead Cashier, Cashier
            • Modify Staff permissions
        • LS Retail - Commands, Buttons
          • Set staff permissions on POS Commands, Buttons
      • Global Menu Configuration / Role Center
        • Configure default menus and nav bar layout for a specific profile
        • Microsoft.Dynamics.Nav.Client.exe -configure -profile:"Accounting Manager"
      • Audit Trail and Change Log
    • Job Queue
      • NAV Scheduler-Used to schedule Nav Reports or Codeunits
        • Job Queue - Service that controls all jobs connected to it
        • Job Queue Category List - Categorize Jobs
        • Job Queue Entries - Tasks to fire
        • Job Queue Log - Log of tasks that have run
    • Scheduler
      • LS Scheduler-Used to schedule DD Jobs
    • Development Environment
    • Insert Registration Keys
    • Backup & Restore
  • Workflows & Approval
    • Workflow Setup - Enable
    • Approval Setup - set workflow admin, unlimited approval
  • Finance - Manager Training
    • Setup General Ledger
    • Define Accounting Periods
      • Cannot lock a single period, only an entire year
      • Once a period is closed ,it cannot be opened
      • You cannot close a fiscal year until a new one is opened
      • When a fiscal year is closed, you cannot change the starting date of the new fiscal year
      • You can post to closed years, but the transactions is flagged as a "prior-year"
      • Run "Close Income Statement" job to close the year
        • This can be run every time posting to a closed year is done
    • User Setup - Control posting date ranges for each user

    • Setup Chart of Accounts
      • Reconciliation Accounts - Generally for cash accounts
      • VAT Posting Groups - Enter if you want VAT to be calculated on these GL transactions
      • Relation to Chart of Cost Types for Cost Accounting (RM, Labour, Overhead, etc.)
    • Create and post journal entries
      • Multi-line journals
      • Balancing account
      • Save standard journals
      • Allocated Amounts
      • If posting date is later than the work date, the transaction will not post
      • Correcting Entry - Switches debits and credits
    • Review recurring journals
      • Fixed Amount, Variable Amount
      • Recurring = 1M + CM (ensures recurrence on the last day of the current month)
      • Balance (Lets you post the entire balance of an account to an allocation list)
      • Reversing - Will post one journal on the current date, and another reversing journal on the day after
      • Must create 2-line journal with Credit and Debit amounts

    • Create a bank account
      • Bank account will only post transactions in the Bank Account Currency
      • Journal will automatically convert currency exchange between accounts
    • Bank Reconciliation
      • Suggest Lines
      • Flag lines as Differences
      • Delete lines
      • Import Lines
    • AR
      • Customer Card-Payment Options
      • Enter Cash Receipt Journal 
        • Payment, Customer, Apply to doc, -ve amt for pmts Balancing Bank Act
      • Enter AR Journals
      • Receive Cash
      • Prepayments
        • Setup to check prepayments before posting
        • Posting>Post prepayment invoice
        • View prepayment invoices
        • Create payment and apply to prepayment invoice
        • Prepayment shows on transaction
      • Check a customer balance
      • Run a Trial Balance
    • AP
      • Vendor Card - Payment Options
      • Enter Vendor Payment Journal
        • Computer Check, 
          • Select Bank Batch (with predefined bank account)
          • Payment, Vendor, Apply to doc, +ve for payments
          • Applying Credit memos to a check will cause blank lines to appear on your remittance. Do not do this.
        • Void Checks
          • Can reprint if you void a check
          • Void check from the bank account to actually void the check
      • Enter AP Journals
      • Enter Invoices
        • Get Receipt Lines Function on line item entry
          • This will find all receipts for all uninvoiced purchase orders for this vendor
      • Enter Checks
      • Prepayments-from order stage
        • Setup to check prepayments before posting
        • Posting>Post prepayment invoice
        • View prepayment invoices
        • Create payment and apply to prepayment invoice
        • Prepayment shows on transaction
      • Check a Vendor Balance
      • Run a Trial Balance
    • Apply payments
      • Payments must be negative
      • Can define oldest apply or manual apply from customer card
      • Can define specific bank accounts for Customers or Vendors
      • Customer Ledger Entries has all transactions
    • Payment Registration
      • Select Outstanding invoices and automatically create payments against them
    • Set up and assign reminder terms
    • Set up and assign finance charge terms
    • Calculate VAT
    • Define VAT calculation types
    • Setup prepayments
    • Set up Multicurrency
    • Process Multicurrency Payments using the Payments Journal
    • Set up and use an additional reporting currency
    • Financial Reporting
      • Understand financial reporting options
        • Balance by account
        • GL Balance
        • Balance by dimension
        • Balance vs budget
      • Account Schedules
        • Create / Edit Account Schedule
          • Create Row Layout
            • Row No - References in calculations
            • Totaling - Account values
              • R1|R2|R3
              • >R30
              • R3*
            • Dimension code totaling
          • Create Column Layout
            • Column No - References in calculations
            • Column Type - Can define if it's a formula
            • Formula - Calculations
              • A10-A30
              • *A*
            • Comparison
              • -1M - Display last month's figures
              • -1Y - Display last year's figures
            • Dimension code totaling
          • Analysis View
            • Generates analysis entries that are copies of actual transactions that meet specified criteria
            • Allows to view columns and rows by dimensions instead of accounts
        • Overview

    • Budgets
      • GL Budgets
        • Can create multiple budgets
        • Budget Layout
          • Can display the budget is different ways
          • Can enter budget information from any dimension
          • Can enter budgets against headers
        • ddmmyy
        • Sales - credit -ve
        • Budget Entries
          • Entering a value creates a budget entry
          • Deleting a value creates another opposing budget entry
          • Budget entries can be manually deleted
    • Finance Performance Charts
      • Only available on certain Roles
      • Customize>This Page>Finance Performance
      • Configure Chart options
    • VAT statement
      • Create VAT Statement
      • Setup report format
      • VAT Settlement
    • Cash Flow Forecast
      • Estimate cash flow based on expected pmt dates
    • Fixed Assets
      • Fixed Asset Setups
        • FA Setup
          • Allow Posting to Main Assets
            • Checked - can post depreciation directly to main asset
            • Unchecked - must post depreciation through component depreciation. Cannot depreciate main asset directly.
          • Allow from/To - limit dates that can be posted to FA
        • FA Classes - Tangible-depreciated, Intangible-amortized
        • FA Subclasses - Plant, Property, Machine & Equip, etc.
        • FA Locations - Location, or position of asset
        • Insurance types - Theft, Fire, etc.
        • FA Books
          • FA Posting Type Setup - Controls how values are displayed on reports
          • FA Journal Setup - Set templates to use by default for each FA User
          • Integrated to GL - Use FA GL Journal window
          • Not integrated to GL - Use FA Journal window
        • FA Posting Groups - Control which accounts are affected for FA transactions
        • Allocation Keys - Per Posting Group. Can be used to split amounts to different accounts in a ratio. Eg. Allocate 60% depr to one account, 40% to another
        • FA Journal Templates - Used to auto-generate FA transactions when calculating depreciation
          • FA Journal Batches - Controls numbering sequence of the journals in the batch
      • Fixed Asset Cards
        • Can assign multiple Depreciation Books
          • Duplication
            • Can duplicate transactions to a second book that is not connected to the GL
            • Book>Actions>Copy
      • FA Journals
        • FA GL Journals
          • Use these if you have integration to GL turned on
          • Affect the FA register and GL
        • FA Journals
          • Use these if you have integration to GL turned off
          • Affect only the FA register
        • FA Reclassification Journal - Move depreciation from one FA to another FA. Can split, combine or transfer. This journal generates FA journals that require balancing accounts before posting.
        • Insurance Journals
          • Setup insurance cards
          • Assign insurance policy to a Fixed Asset
            • Add insurance policy to line at purchase of asset
            • Use Insurance Journal
      • FA Extend Life
        • Depreciation Ending Date can be changed at any time to extend the life of an asset
        • Future Depreciation will be calculated based on current net book value
        • If you want the next depreciation calculation to be recalculated life to date, you must pass a depreciation journal to the previous period to set the net book value of the asset to a value that will allow it to calculate the correct depreciation going forward
      • FA Acquisitions
        • Purchase Order Line - Fixed asset - Will create acquisition cost entry on fixed asset
      • FA Disposals
        • Sell
          • Net disposal-just diff
          • Gross Disposal - orig value and final sales value
        • Scrap
          • FA Journal Disposal entry of 0
      • Main Assets
        • Asset Components
      • Insurance
        • Set up insurance information.
        • Assign an asset to an insurance policy.
        • Monitor insurance coverage.
        • Update, correct, and delete insurance information.
        • Set up insurance indexing.
    • Intercompany Transactions
      • Setup IC Partner Codes
        • You will need a code for every Customer-Vendor relationship between companies you wish to maintain
      • Company Information-Connect companies
        • IC Partner Codes
          • In each company, create codes that link to the other companies by database
      • Setup IC Chart of Accounts Map
      • Enter Default IC Partner GL Account code
      • Setup IC Dimension Map
      • Create Customer Accounts for the other companies
        • Assign the corresponding IC partner codes to those customers
      • Create Vendor Accounts for the other companies
        • Assign the corresponding IC partner codes to those Vendors
      • IC Transactions
        • Sales - Purchases
        • IC Journals
      • When an IC Transaction is posted
        • Go to IC General Journals > Post
        • Transaction shows up in IC Outbox
        • Click on Set Line Action > Send 
        • Complete Line Action
          • This will send to Destination company IC Inbox
          • Click on Set Line Action>Accept
          • Complete Line Action
            • This will generate the opposing transaction in the current (destination) company
      • When doing a transaction, specify "Send to IC Partner" to send to Parner's Inbox

    • Year End Closing Process
      • Accounting Periods > Close Year
        • Can still post to closed years
          • Entry is flagged as a "prior year" entry
      • Close Income Statement Batch Job
        • Will create journals to transfer balances to Retained Earnings
        • Review the journals
        • Post the Batch
      • This batch job should be re-run if any postings are done to the closed year
    • Cash Flow Forecasts
      • Chart of Cash Flow Accounts
      • Perform Setup for Cash Flow Functionality
      • Create Cash Flow Forecast
      • Create Manual Entries
      • Create Cash Flow Worksheet
      • Register Cash Flow Worksheet
  • Finance - Clerk Training
    • Create and post journal entries
      • Multi-line journals
      • Balancing account
      • Save standard journals
      • Allocated Amounts
      • If posting date is later than the work date, the transaction will not post
      • Correcting Entry - Switches debits and credits
    • Review recurring journals
      • Fixed Amount, Variable Amount
      • Recurring = 1M + CM (ensures recurrence on the last day of the current month)
      • Balance (Lets you post the entire balance of an account to an allocation list)
      • Reversing - Will post one journal on the current date, and another reversing journal on the day after
      • Must create 2-line journal with Credit and Debit amounts
    • Bank Reconciliation
      • Suggest Lines
      • Flag lines as Differences
      • Delete lines
      • Import Lines
    • AR
      • Customer Card-Payment Options
      • Enter Cash Receipt Journal 
        • Payment, Customer, Apply to doc, -ve amt for pmts Balancing Bank Act
      • Enter AR Journals
      • Receive Cash
      • Prepayments
        • Setup to check prepayments before posting
        • Posting>Post prepayment invoice
        • View prepayment invoices
        • Create payment and apply to prepayment invoice
        • Prepayment shows on transaction
      • Check a customer balance
      • Run a Trial Balance
    • AP
      • Vendor Card - Payment Options
      • Enter Vendor Payment Journal
        • Computer Check, 
          • Select Bank Batch (with predefined bank account)
          • Payment, Vendor, Apply to doc, +ve for payments
          • Applying Credit memos to a check will cause blank lines to appear on your remittance. Do not do this.
        • Void Checks
          • Can reprint if you void a check
          • Void check from the bank account to actually void the check
      • Enter AP Journals
      • Enter Invoices
        • Get Receipt Lines Function on line item entry
          • This will find all receipts for all uninvoiced purchase orders for this vendor
      • Enter Checks
      • Prepayments-from order stage
        • Setup to check prepayments before posting
        • Posting>Post prepayment invoice
        • View prepayment invoices
        • Create payment and apply to prepayment invoice
        • Prepayment shows on transaction
      • Check a Vendor Balance
      • Run a Trial Balance
    • Apply payments
      • Payments must be negative
      • Can define oldest apply or manual apply from customer card
      • Can define specific bank accounts for Customers or Vendors
      • Customer Ledger Entries has all transactions
    • Payment Registration
      • Select Outstanding invoices and automatically create payments against them
    • Set up and assign reminder terms
    • Set up and assign finance charge terms
    • Process Multicurrency Payments using the Payments Journal
    • Fixed Assets
      • Fixed Asset Cards
        • Can assign multiple Depreciation Books
          • Duplication
            • Can duplicate transactions to a second book that is not connected to the GL
            • Book>Actions>Copy
      • FA Journals
        • FA GL Journals
          • Use these if you have integration to GL turned on
          • Affect the FA register and GL
        • FA Journals
          • Use these if you have integration to GL turned off
          • Affect only the FA register
        • FA Reclassification Journal - reclassify one FA to another FA
        • Insurance Journals
          • Setup insurance cards
          • Assign insurance policy to a Fixed Asset
            • Add insurance policy to line at purchase of asset
            • Use Insurance Journal
      • FA Disposals
        • Sell
          • Net disposal-just diff
          • Gross Disposal - orig value and final sales value
        • Scrap
          • FA Journal Disposal entry of 0
      • Main Assets
        • Asset Components
    • Intercompany Transactions
      • Setup IC Partner Codes
        • You will need a code for every Customer-Vendor relationship between companies you wish to maintain
      • Company Information-Connect companies
        • IC Partner Codes
          • In each company, create codes that link to the other companies by database
      • Setup IC Chart of Accounts Map
      • Enter Default IC Partner GL Account code
      • Setup IC Dimension Map
      • Create Customer Accounts for the other companies
        • Assign the corresponding IC partner codes to those customers
      • Create Vendor Accounts for the other companies
        • Assign the corresponding IC partner codes to those Vendors
      • IC Transactions
        • Purchase Order/Receipt -> Sales Orders
        • Sales Invoices -> Purchase Invoices
          • Invoice in Company A > A Outbox > B Inbox> PO In Company B
        • IC General Journals
          • IC GL In Company A to IC Partner Acc. no and GL
          • On Same Line, Balancing Account to local account
          • GL In Company A > A Outbox > B Inbox > GL in Company B
      • When an IC Transaction is posted
        • Go to IC General Journals > Post
        • Transaction shows up in IC Outbox
        • Click on Set Line Action > Send 
        • Complete Line Action
          • This will send to Destination company IC Inbox
          • Click on Set Line Action>Accept
          • Complete Line Action
            • This will generate the opposing transaction in the current (destination) company
      • When doing a transaction, specify "Send to IC Partner" to send to Partner's Inbox
  • Purchasing
    • Create Purchase Order
    • Post Prepayment
    • Apply Payment to Prepayment Invoice
    • Release PO
    • Print Purchasing Documents
    • Add Charge Items
    • Assign Charge Items
    • Post Receipt
    • Post Invoice for received items
  • Warehouse
    • Purchase Order
      • Release
      • Warehouse Receipt
        • Put-Away
          • Assign Put Away
          • Register Put Away
    • Transfer Order
      • Warehouse Shipment
        • Pick Ticket
          • Assign Pick Ticket
          • Register Pick Ticket
    • Movements (Bin Transfers)
      • Bin Contents
      • Movement Worksheet 
        • Create movement
      • Whse. Worksheet Templates
        • Names (Create new templates)
  • Inventory
    • Inventory Periods
      • Controls posting of inventory value transactions for that period
      • Cannot close period if there are any outstanding entries to be posted, or negative inventory
      • An entry is created with the last transaction number and user that closed the period
      • Can reopen period if necessary
    • Inventory Costing
      • Value Entries
      • Last direct cost is used as the default cost in the next transaction
      • Cost of Goods Sold 
        • Inventory application of inbound and outbound
        • Can specify Apply-to-item entry for item transaction to force link an outbound to an inbound
        • Can remove existing applications to fix item cost applications if necessary
      • Click the drilldown on an item to get the average cost overview
      • Inventory setup controls how average costs are calculated
    • Inventory Control
      • Analyze Item Availability
      • Adjust Inventory
        • Stock Counting worksheet - retail store
        • If Directed Pick and Put away is in use
          • Whse. Item Journal
            • Register- posts changes to WH
          • Item Journal
            • Calculate Whse Adjustments - posts changes to system
      • Count Inventory
      • Reclassify Inventory
    • Item Reservations and Order Tracking
      • Reserve Items
      • Track Supply and Demand
      • Item Reservations versus Order Tracking
    • Use Multiple Locations
      • Set Up a Company with Multiple Locations
      • Link Customers and Vendors to Locations
      • Set Up Responsibility Centers
      • Link Responsibility Centers
      • Guidelines for Setting Up a Company with Multiple Locations
      • CRONUS Organizational Setup Overview
      • Sell and Purchase in a Company with Multiple Locations
      • Control Inventory at Multiple Locations
    • Transfer between Locations
      • Set Up Transfers
      • Setup Transfer Routes
      • Use Transfer Orders
      • View Items in Transit
    • Basic Warehouse Tasks
      • Set Up Basic Warehousing
      • Receive and Put Away Items
      • Pick and Ship Items
      • Move and Adjust Items in Warehouse
    • Serial/Lot Numbers
      • Set Up Item Tracking
      • When to Use Item Tracking
      • Handle Inbound Serial/Lot Numbers
      • Handle Serial/Lot Numbers on Inventory
      • Handle Outbound Serial/Lot Numbers
      • Trace Serial/Lot Numbers
      • Navigate with Serial/Lot Numbers
      • Print Serial/Lot Numbers in Documents and Reports
  • Replenishment
  • Office Sales
    • Sales Order
      • Create Whse. Shipment
        • Release
        • Create Pick ticket

  • POS
    • Send & Receive Data from Terminals
      • Setup Web Service
        • Test Web Connection from Terminal
        • Test Web connection from Distribution location
      • Setup a separate instance for NAS in NAV Server Admin to run LS Scheduler
        • Codeunit 99001468
        • Startup Argument: NASID,REPEAT=1,LOG=1
        • Startup Method: LSRSCHEDULER
      • Setup Jobs
        • Preload, Storinf
        • codeunit 99001474
        • From HO to Terminal
        • Use Scheduler Job Record, Use Job ID = job name
      • Set Default Store Logo
      • Open Port 16860 on firewalls at HO and terminals
      • Send from Head Office
        • Distribution Location>Test Connection
          • Confirm username, password, remove connection string, let it rebuild
        • Location Settings>Define Destination
        • Jobs>Select Job>Data Replication
    • Discount Offers

    • Setup
      • Setup Staff accounts and passwords
      • Setup Bank accounts on tender types
      • Setup Store Tender Type Safe management
      • Set Default Business Posting Groups - Local
      • Set Default Posting Type - Sales
      • Set Accounts on Bank
    • Start Day
      • Login
        • Cashier Functions
          • Enter Float
          • Or use Fixed Float
        • Manager Functions
          • End of Day
          • Voids
          • Discounts
    • General POS Functions
      • Select customer
        • Use to put sale against a specific customer account
        • AR Charge
          • Charges transaction to customer account
      • Transactions - shows historical trx
        • Lookup
          • Inventory - view stock on hand, on order
        • Filter - filters entire list
        • Find - finds the item in the current list
      • Prices & Discount
        • Check Price - price check only
        • Discount% - per line
        • Discount Reset - reset default discounts
        • Change Price - change price for this trx
      • Tender
        • Cash
        • Card
          • Linx
          • Credit Card
        • Cheque
          • Cheque
            • Chq no.
          • Bonus
          • Magna
          • Gift Certificate
        • Currency
          • US
          • GBP
          • etc.
        • Prompts for e-mail address at end of transaction if you want to print on receipt and e-mail receipt to person

      • Hold
        • Suspends transaction
        • Prints a barcode
        • Barcode can be used to pull up transaction and continue
      • Recall Hold
        • Shows all transactions on hold
        • Retrieve - pull up transaction and continue
      • Refund
      • Tender Operations
        • Declare Tender - end of day tender declaration
        • Float entry - Manager
    • Printing
      • Receipts print with barcode
        • Receipt can be scanned to pull up trx, and do a return
    • End of Day
      • Z-Report-marks all transactions and calculates total of marked transactions
      • X-Report-Gives a total of all unmarked transactions and does nothing
      • Manager Function>Tender operations
        • End of Day
          • Select tender category
            • Float - Amount of float
            • Bank - Trx that go directly to bank (linx, cc, etc.)
            • Safe - Amount to be deposited to bank
          • Select tender type line
          • Enter Float amount
          • Enter denominations 
          • Print Closing Report
        • Post
          • If Transactions have been reconciled and counted at the terminal, use this option to submit the Transactions and the count
          • The statement can be posted without any further changes
        • Post Uncounted
          • Use this option if transactions have not been reconciled to the cash received
          • Transactions must be reconciled to the cash before the statement can be posted through the back office
    • POS Statements
      • Search for Open Statements
      • Create New Statement
        • Enter Date, Store, Staff
        • Calculate Statement
        • All tender type lines will be summarized
          • Can drill into each one to show breakdown
        • Deal with any warnings
          • Clear Statement
          • Calculate Statement
        • Accept Statement
  • Safe Management
    • Setup Safe
    • Assign Safe to POS Terminal
    • Modify Store Tender Type to allow use with Safe
      • Store>Cash Management>Tender Type
        • Select Tender Type>Edit
          • Declaration Fasttab
            • Tick Use Bags
            • Set number series
            • Tick Sent to Safe
    • Cashier must enter bag number when closing off
      • Bag number gets sent with statement
  • Shelf Label Printing
    • Shelf Label Functions
      • Printer Selection
        • Assign User to Printer
  • LS Gift Registration
    • User must be assigned to a Store and terminal
    • Gift Registrations
      • Setup Registry Items and Qty
      • Define any specific discounts
    • POS Interface Design
      • Add button with "Gift Assign" command
    • On POS
      • Customer requests to purchase item for gift registry
      • Click Button to open gift registry
      • Select Items on Gift Registry
      • Checkout
      • This updates the "Purchased Qty" on the gift registry list and consumes stock


Tuesday, January 27, 2015

Dynamics NAV - "The file that you are trying to use is too large" - Error when export Config. Package or importing Excel file.

Original Post
https://community.dynamics.com/nav/f/34/t/142468.aspx

Solution:

  1. Run the Dynamics NAV Administration Console
  2. Select your instance
  3. Click Edit
  4. Look in Client Services Fast Tab
  5. Change the Max Upload Size field to the largest amount of data you want to be able to transfer in MB
  6. Restart Instance

Monday, January 26, 2015

Excel - Comma is missing from million number

Related issues
Cannot see comma in millions
Millions do not show comma separator
Custom number format comma separator for millions

Solution:
Change your regional settings to US

Go to control panel, regional settings, change to US.

Tuesday, January 20, 2015

SQL Stored Procedure - Dynamics GP Historical Aged RM Trial Balance for Tableau

--Run all scripts to create all objects
-- exec BI_HARTB_RMFinal_Load '2017-04-19'


/****** Object:  StoredProcedure [dbo].[BI_HARTB_RMFinal_Load]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BI_HARTB_RMFinal_Load] (@AgeDate as datetime)

AS
BEGIN

truncate table BI_HARTB_RMFinal_Temp
truncate table BI_HARTB_RMFinal_Temp3

insert into BI_HARTB_RMFinal_Temp
SELECT @AgeDate, [CUSTNMBR]
      ,[CPRCSTNM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[SLPRSNID]
      ,[VOIDSTTS]
      ,[VOIDDATE]
      ,[APFRDCNM]
      ,[APFRDCDT]
      ,[ApplyFromGLPostDate]
      ,[APFRMAPLYAMT]
      ,[ActualApplyToAmount]
      ,[ApplyFromPostDate]
      ,[LastDate]
      ,[LastStatus]
      ,[LastAmt]
      ,[LastApplyToDoc]
      ,[DocType],wrofamnt
  FROM [TWO].[dbo].[BI_HARTB_RMFinal] where docdate <= @agedate

  --Load all Invoices and applications
insert into BI_HARTB_RMFinal_Temp3
Select [AgeDate]
      ,[CUSTNMBR]
      ,[CPRCSTNM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[SLPRSNID]
      ,[VOIDSTTS]
      ,[VOIDDATE]
      ,[APFRDCNM]
      ,[APFRDCDT]
      ,[ApplyFromGLPostDate]
      ,[APFRMAPLYAMT]
      ,[ActualApplyToAmount]
      ,[ApplyFromPostDate]
      ,[LastDate]
      ,[LastStatus]
      ,[LastAmt]
      ,[LastApplyToDoc]
      ,[DocType]
      ,[DocDateDiff]
      ,[AgedLastDate]
      ,[AgedLastStatus]
      ,[AgedLastAmt]
 ,[isfullypaid]
  FROM [TWO].[dbo].[BI_HARTB_RMFinal_Temp2A]

  --Add partial unapplied credits
  insert into [BI_HARTB_RMFinal_Temp3]
  select [AgeDate]
      ,[CUSTNMBR]
      ,[CPRCSTNM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DOCDATE]
      ,[GLPOSTDT]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[SLPRSNID]
      ,[VOIDSTTS]
      ,[VOIDDATE]
      ,[APFRDCNM]
      ,[APFRDCDT]
      ,[ApplyFromGLPostDate]
      ,[APFRMAPLYAMT]
      ,[ActualApplyToAmount]
      ,[ApplyFromPostDate]
      ,[LastDate]
      ,[LastStatus]
      ,[LastAmt]
      ,[LastApplyToDoc]
      ,[DocType]
      ,[DocDateDiff]
      ,[AgedLastDate]
      ,[AgedLastStatus]
      ,[AgedLastAmt]
 ,isfullypaid
  FROM [TWO].[dbo].[BI_HARTB_RMFinal_Temp2b]

  select * from BI_HARTB_RMFinal_temp3
END

GO
/****** Object:  Table [dbo].[BI_HARTB_RMFinal_Temp]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_HARTB_RMFinal_Temp](
[AgeDate] [datetime] NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[CPRCSTNM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[VOIDSTTS] [smallint] NOT NULL,
[VOIDDATE] [datetime] NOT NULL,
[APFRDCNM] [char](21) NULL,
[APFRDCDT] [datetime] NULL,
[ApplyFromGLPostDate] [datetime] NULL,
[APFRMAPLYAMT] [numeric](19, 5) NULL,
[ActualApplyToAmount] [numeric](19, 5) NULL,
[ApplyFromPostDate] [datetime] NULL,
[LastDate] [datetime] NULL,
[LastStatus] [varchar](9) NOT NULL,
[LastAmt] [numeric](21, 5) NULL,
[LastApplyToDoc] [char](21) NULL,
[DocType] [varchar](14) NULL,
[wrofamnt] [numeric](19, 5) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BI_HARTB_RMFinal_Temp3]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_HARTB_RMFinal_Temp3](
[AgeDate] [datetime] NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[CPRCSTNM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[VOIDSTTS] [smallint] NOT NULL,
[VOIDDATE] [datetime] NOT NULL,
[APFRDCNM] [char](21) NULL,
[APFRDCDT] [datetime] NULL,
[ApplyFromGLPostDate] [datetime] NULL,
[APFRMAPLYAMT] [numeric](19, 5) NULL,
[ActualApplyToAmount] [numeric](19, 5) NULL,
[ApplyFromPostDate] [datetime] NULL,
[LastDate] [datetime] NULL,
[LastStatus] [varchar](9) NOT NULL,
[LastAmt] [numeric](21, 5) NULL,
[LastApplyToDoc] [char](21) NULL,
[DocType] [varchar](14) NULL,
[DocDateDiff] [int] NULL,
[AgedLastDate] [datetime] NULL,
[AgedLastStatus] [varchar](9) NOT NULL,
[AgedLastAmt] [numeric](21, 5) NULL,
[IsfullyPaid] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[BI_HARTB_RMApply]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMApply]
AS
SELECT        CUSTNMBR, CPRCSTNM, TRXSORCE, DATE1, TIME1, GLPOSTDT, POSTED, TAXDTLID, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, DISTKNAM, DISAVTKN,
                         WROFAMNT, ORAPTOAM, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, APFRDCNM, APFRDCTY, APFRDCDT, ApplyFromGLPostDate, FROMCURR,
                         CASE WHEN APFRMAPLYAMT = 0 THEN ActualWriteOffAmount ELSE APFRMAPLYAMT - actualwriteoffamount END AS APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT,
                         CASE WHEN ActualApplyToAmount = 0 THEN ActualWriteOffAmount ELSE actualapplytoamount - ActualWriteOffAmount END AS ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken,
                         ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT, OAPYTORNDDISC,
                         GSTDSAMT, PPSAMDED, RLGANLOS, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv, Settled_Loss_DebitDiscAv,
                         Revaluation_Status
FROM            (SELECT        CUSTNMBR, CPRCSTNM, TRXSORCE, DATE1, TIME1, GLPOSTDT, POSTED, TAXDTLID, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, DISTKNAM,
                                                    DISAVTKN, WROFAMNT, ORAPTOAM, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, APFRDCNM, APFRDCTY, APFRDCDT,
                                                    ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT, ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken,
                                                    ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT,
                                                    OAPYTORNDDISC, GSTDSAMT, PPSAMDED, RLGANLOS, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv,
                                                    Settled_Loss_DebitDiscAv, Revaluation_Status
                          FROM            dbo.RM20201
                          UNION
                          SELECT        CUSTNMBR, CPRCSTNM, TRXSORCE, DATE1, TIME1, GLPOSTDT, POSTED, TAXDTLID, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, DISTKNAM,
                                                   DISAVTKN, WROFAMNT, ORAPTOAM, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, APFRDCNM, APFRDCTY, APFRDCDT,
                                                   ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT, ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken,
                                                   ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT,
                                                   OAPYTORNDDISC, GSTDSAMT, PPSAMDED, RLGANLOS, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv,
                                                   Settled_Loss_DebitDiscAv, Revaluation_Status
                          FROM            dbo.RM30201) AS derivedtbl_1

GO
/****** Object:  View [dbo].[BI_HARTB_RMTrx]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMTrx]
AS
SELECT        CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, TRXSORCE, RMDTYPAL, CSHRCTYP, DUEDATE, DOCDATE, POSTDATE, GLPOSTDT, ORTRXAMT, CURTRXAM, SLSAMNT,
                         TAXAMNT, COSTAMNT, FRTAMNT, MISCAMNT, CASHAMNT, DISTKNAM, TRXDSCRN, SLPRSNID, SLSTERCD, TAXSCHID, CURNCYID, PYMTRMID, SHIPMTHD, NOTEINDX, ADRSCODE, VOIDSTTS, VOIDDATE
FROM            (SELECT        VOIDSTTS, VOIDDATE, CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, TRXSORCE, RMDTYPAL, CSHRCTYP, DUEDATE, DOCDATE, POSTDATE, GLPOSTDT,
                                                    ORTRXAMT, CURTRXAM, SLSAMNT, TAXAMNT, COSTAMNT, FRTAMNT, MISCAMNT, CASHAMNT, DISTKNAM, TRXDSCRN, SLPRSNID, SLSTERCD, TAXSCHID, CURNCYID, PYMTRMID, SHIPMTHD,
                                                    NOTEINDX, ADRSCODE
                          FROM            dbo.RM20101
                          UNION
                          SELECT        VOIDSTTS, VOIDDATE, CUSTNMBR, CPRCSTNM, DOCNUMBR, CHEKNMBR, BACHNUMB, BCHSOURC, TRXSORCE, RMDTYPAL, CSHRCTYP, DUEDATE, DOCDATE, POSTDATE, GLPOSTDT,
                                                   ORTRXAMT, CURTRXAM, SLSAMNT, TAXAMNT, COSTAMNT, FRTAMNT, MISCAMNT, CASHAMNT, DISTKNAM, TRXDSCRN, SLPRSNID, SLSTERCD, TAXSCHID, CURNCYID, PYMTRMID, SHIPMTHD,
                                                   NOTEINDX, ADRSCODE
                          FROM            dbo.RM30101) AS derivedtbl_1

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal]
AS
SELECT DISTINCT
                         dbo.BI_HARTB_RMTrx.CUSTNMBR, dbo.BI_HARTB_RMTrx.CPRCSTNM, dbo.BI_HARTB_RMTrx.DOCNUMBR, dbo.BI_HARTB_RMTrx.RMDTYPAL, dbo.BI_HARTB_RMTrx.DOCDATE,
                         dbo.BI_HARTB_RMTrx.GLPOSTDT, CASE WHEN ORTRXAMT = 0 THEN isnull(actualwriteoffamount, 0) ELSE ortrxamt END AS ORTRXAMT, dbo.BI_HARTB_RMTrx.CURTRXAM, dbo.BI_HARTB_RMTrx.SLPRSNID,
                         dbo.BI_HARTB_RMTrx.VOIDSTTS, dbo.BI_HARTB_RMTrx.VOIDDATE, dbo.BI_HARTB_RMApply.APFRDCNM, dbo.BI_HARTB_RMApply.APFRDCDT, dbo.BI_HARTB_RMApply.ApplyFromGLPostDate,
                         dbo.BI_HARTB_RMApply.APFRMAPLYAMT, dbo.BI_HARTB_RMApply.ActualApplyToAmount, dbo.BI_HARTB_RMApply.DATE1 AS ApplyFromPostDate,
                         CASE WHEN voidstts = 1 THEN voiddate ELSE CASE WHEN applyfromglpostdate IS NOT NULL THEN applyfromglpostdate ELSE docdate END END AS LastDate,
                         CASE WHEN voidstts = 1 THEN 'Voided' ELSE CASE WHEN applyfromglpostdate IS NOT NULL THEN 'ApplyDate' ELSE 'DocDate' END END AS LastStatus, CASE WHEN actualapplytoamount IS NOT NULL
                         THEN actualapplytoamount * - 1 ELSE CASE WHEN rmdtypal IN (2, 7, 8, 9) THEN ortrxamt * - 1 ELSE ortrxamt END END AS LastAmt, CASE WHEN APTODCNM IS NULL
                         THEN docnumbr ELSE aptodcnm END AS LastApplyToDoc,
                         CASE rmdtypal WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Scheduled Pmt' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo'
                          WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS DocType, dbo.BI_HARTB_RMApply.WROFAMNT
FROM            dbo.BI_HARTB_RMApply RIGHT OUTER JOIN
                         dbo.BI_HARTB_RMTrx ON dbo.BI_HARTB_RMApply.CUSTNMBR = dbo.BI_HARTB_RMTrx.CUSTNMBR AND dbo.BI_HARTB_RMApply.APFRDCNM = dbo.BI_HARTB_RMTrx.DOCNUMBR

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp1]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp1]
AS
SELECT        AgeDate, CUSTNMBR, CPRCSTNM, DOCNUMBR, RMDTYPAL, DOCDATE, GLPOSTDT, ORTRXAMT, CURTRXAM, SLPRSNID, VOIDSTTS, VOIDDATE, APFRDCNM, APFRDCDT, ApplyFromGLPostDate,
                         APFRMAPLYAMT, ActualApplyToAmount, ApplyFromPostDate, LastDate, LastStatus, LastAmt, LastApplyToDoc, DocType, CAST(AgeDate - DOCDATE AS int) AS DocDateDiff, CASE WHEN voidstts = 1 AND
                         agedate < voiddate THEN CASE WHEN agedate > applyfromglpostdate THEN applyfromglpostdate ELSE docdate END ELSE lastdate END AS AgedLastDate, CASE WHEN voidstts = 1 AND
                         agedate < voiddate THEN CASE WHEN agedate > applyfromglpostdate THEN 'ApplyDate' ELSE 'DocDate' END ELSE laststatus END AS AgedLastStatus, CASE WHEN agedate < (CASE WHEN voidstts = 1 AND
                         agedate < voiddate THEN CASE WHEN agedate > applyfromglpostdate THEN applyfromglpostdate ELSE docdate END ELSE lastdate END) THEN 0 ELSE lastamt END AS AgedLastAmt
FROM            dbo.BI_HARTB_RMFinal_Temp

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp2]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp2]
AS
SELECT        AgeDate, CUSTNMBR, CPRCSTNM, DOCNUMBR, RMDTYPAL, DOCDATE, GLPOSTDT, ORTRXAMT, CURTRXAM, SLPRSNID, VOIDSTTS, VOIDDATE, APFRDCNM, APFRDCDT, ApplyFromGLPostDate,
                         APFRMAPLYAMT, ActualApplyToAmount, ApplyFromPostDate, LastDate, LastStatus, LastAmt, LastApplyToDoc, DocType, DocDateDiff, AgedLastDate, AgedLastStatus, AgedLastAmt
FROM            dbo.BI_HARTB_RMFinal_Temp1
WHERE        (AgedLastStatus <> 'voided') AND (DocDateDiff >= 0)

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_ApplyCBal]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_ApplyCBal]
AS
SELECT        DOCNUMBR, OSBal, AppliedAmt, CURTRXAM, ORTRXAMT
FROM            (SELECT        DOCNUMBR, SUM(ORTRXAMT + AppliedAmt) AS OSBal, AppliedAmt, CURTRXAM, ORTRXAMT
                          FROM            (SELECT        DOCNUMBR, ORTRXAMT, SUM(AgedLastAmt) AS AppliedAmt, CURTRXAM
                                                    FROM            dbo.BI_HARTB_RMFinal_Temp2 AS BI_HARTB_RMFinal_Temp2_1
                                                    GROUP BY DOCNUMBR, ORTRXAMT, CURTRXAM
                                                    HAVING         (SUM(AgedLastAmt) <= 0)) AS derivedtbl_1_1
                          GROUP BY DOCNUMBR, AppliedAmt, CURTRXAM, ORTRXAMT) AS derivedtbl_2
WHERE        (OSBal > 0)

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp2B]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp2B]
AS
SELECT        dbo.BI_HARTB_RMFinal_Temp2.AgeDate, dbo.BI_HARTB_RMFinal_Temp2.CUSTNMBR, dbo.BI_HARTB_RMFinal_Temp2.CPRCSTNM, dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR,
                         dbo.BI_HARTB_RMFinal_Temp2.RMDTYPAL, dbo.BI_HARTB_RMFinal_Temp2.DOCDATE, dbo.BI_HARTB_RMFinal_Temp2.GLPOSTDT, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal AS ORTRXAMT,
                         dbo.BI_HARTB_RMFinal_Temp2.CURTRXAM, dbo.BI_HARTB_RMFinal_Temp2.SLPRSNID, dbo.BI_HARTB_RMFinal_Temp2.VOIDSTTS, dbo.BI_HARTB_RMFinal_Temp2.VOIDDATE,
                         dbo.BI_HARTB_RMFinal_Temp2.APFRDCNM, dbo.BI_HARTB_RMFinal_Temp2.APFRDCDT, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromGLPostDate,
                         dbo.BI_HARTB_RMFinal_ApplyCBal.AppliedAmt AS APFRMAPLYAMT, dbo.BI_HARTB_RMFinal_ApplyCBal.AppliedAmt AS ActualApplyToAmount, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromPostDate,
                         dbo.BI_HARTB_RMFinal_Temp2.LastDate, dbo.BI_HARTB_RMFinal_Temp2.LastStatus, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal * - 1 AS LastAmt,
                         dbo.BI_HARTB_RMFinal_ApplyCBal.DOCNUMBR AS LastApplyToDoc, dbo.BI_HARTB_RMFinal_Temp2.DocType, dbo.BI_HARTB_RMFinal_Temp2.DocDateDiff, dbo.BI_HARTB_RMFinal_Temp2.AgedLastDate,
                         dbo.BI_HARTB_RMFinal_Temp2.AgedLastStatus, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal * - 1 AS AgedLastAmt, 0 AS IsFullyPaid
FROM            dbo.BI_HARTB_RMFinal_Temp2 INNER JOIN
                         dbo.BI_HARTB_RMFinal_ApplyCBal ON dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR = dbo.BI_HARTB_RMFinal_ApplyCBal.DOCNUMBR
GROUP BY dbo.BI_HARTB_RMFinal_Temp2.AgeDate, dbo.BI_HARTB_RMFinal_Temp2.CUSTNMBR, dbo.BI_HARTB_RMFinal_Temp2.CPRCSTNM, dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR,
                         dbo.BI_HARTB_RMFinal_Temp2.RMDTYPAL, dbo.BI_HARTB_RMFinal_Temp2.DOCDATE, dbo.BI_HARTB_RMFinal_Temp2.GLPOSTDT, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal,
                         dbo.BI_HARTB_RMFinal_Temp2.CURTRXAM, dbo.BI_HARTB_RMFinal_Temp2.SLPRSNID, dbo.BI_HARTB_RMFinal_Temp2.VOIDSTTS, dbo.BI_HARTB_RMFinal_Temp2.VOIDDATE,
                         dbo.BI_HARTB_RMFinal_Temp2.APFRDCNM, dbo.BI_HARTB_RMFinal_Temp2.APFRDCDT, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromGLPostDate, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromPostDate,
                         dbo.BI_HARTB_RMFinal_Temp2.LastDate, dbo.BI_HARTB_RMFinal_Temp2.LastStatus, dbo.BI_HARTB_RMFinal_Temp2.DocType, dbo.BI_HARTB_RMFinal_Temp2.DocDateDiff,
                         dbo.BI_HARTB_RMFinal_Temp2.AgedLastDate, dbo.BI_HARTB_RMFinal_Temp2.AgedLastStatus, dbo.BI_HARTB_RMFinal_ApplyCBal.OSBal * - 1, dbo.BI_HARTB_RMFinal_ApplyCBal.AppliedAmt,
                         dbo.BI_HARTB_RMFinal_ApplyCBal.DOCNUMBR

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_FullyPaid]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_FullyPaid]
AS
SELECT        LastApplyToDoc, SUM(AgedLastAmt) AS AgedLastAmt, 1 AS IsFullyPaid
FROM            (SELECT        LastApplyToDoc, SUM(AgedLastAmt) AS AgedLastAmt, ORTRXAMT
                          FROM            dbo.BI_HARTB_RMFinal_Temp2 AS BI_HARTB_RMFinal_Temp2_1
                          GROUP BY LastApplyToDoc, ORTRXAMT) AS derivedtbl_1
GROUP BY LastApplyToDoc
HAVING        (SUM(AgedLastAmt) = 0)

GO
/****** Object:  View [dbo].[BI_HARTB_RMFinal_Temp2A]    Script Date: 2/4/2015 12:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_HARTB_RMFinal_Temp2A]
AS
SELECT        dbo.BI_HARTB_RMFinal_Temp2.AgeDate, dbo.BI_HARTB_RMFinal_Temp2.CUSTNMBR, dbo.BI_HARTB_RMFinal_Temp2.CPRCSTNM, dbo.BI_HARTB_RMFinal_Temp2.DOCNUMBR,
                         dbo.BI_HARTB_RMFinal_Temp2.RMDTYPAL, dbo.BI_HARTB_RMFinal_Temp2.DOCDATE, dbo.BI_HARTB_RMFinal_Temp2.GLPOSTDT, dbo.BI_HARTB_RMFinal_Temp2.ORTRXAMT,
                         dbo.BI_HARTB_RMFinal_Temp2.CURTRXAM, dbo.BI_HARTB_RMFinal_Temp2.SLPRSNID, dbo.BI_HARTB_RMFinal_Temp2.VOIDSTTS, dbo.BI_HARTB_RMFinal_Temp2.VOIDDATE,
                         dbo.BI_HARTB_RMFinal_Temp2.APFRDCNM, dbo.BI_HARTB_RMFinal_Temp2.APFRDCDT, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromGLPostDate, dbo.BI_HARTB_RMFinal_Temp2.APFRMAPLYAMT,
                         dbo.BI_HARTB_RMFinal_Temp2.ActualApplyToAmount, dbo.BI_HARTB_RMFinal_Temp2.ApplyFromPostDate, dbo.BI_HARTB_RMFinal_Temp2.LastDate, dbo.BI_HARTB_RMFinal_Temp2.LastStatus,
                         dbo.BI_HARTB_RMFinal_Temp2.LastAmt, dbo.BI_HARTB_RMFinal_Temp2.LastApplyToDoc, dbo.BI_HARTB_RMFinal_Temp2.DocType, dbo.BI_HARTB_RMFinal_Temp2.DocDateDiff,
                         dbo.BI_HARTB_RMFinal_Temp2.AgedLastDate, dbo.BI_HARTB_RMFinal_Temp2.AgedLastStatus, dbo.BI_HARTB_RMFinal_Temp2.AgedLastAmt, ISNULL(dbo.BI_HARTB_RMFinal_FullyPaid.IsFullyPaid, 0)
                         AS IsFullyPaid
FROM            dbo.BI_HARTB_RMFinal_Temp2 LEFT OUTER JOIN
                         dbo.BI_HARTB_RMFinal_FullyPaid ON dbo.BI_HARTB_RMFinal_Temp2.LastApplyToDoc = dbo.BI_HARTB_RMFinal_FullyPaid.LastApplyToDoc

GO

Tuesday, January 13, 2015

Dynamics GP -SQL View - WORK AP Voucher with words to numbers



/****** Object:  View [dbo].[BI_PMVoucher]    Script Date: 13/01/2015 06:23:26 PM ******/
DROP VIEW [dbo].[BI_PMVoucher]
GO

/****** Object:  View [dbo].[BI_PMVoucher]    Script Date: 13/01/2015 06:23:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_PMVoucher]
AS
SELECT     dbo.SY00500.BACHNUMB, dbo.SY00500.SERIES, dbo.SY03900.TXTFIELD, dbo.PM10000.VCHNUMWK, dbo.PM10000.VENDORID, dbo.PM10000.DOCNUMBR, dbo.PM10000.DOCTYPE, dbo.PM00200.VENDNAME, dbo.PM10000.SOURCDOC, dbo.PM10000.DOCAMNT,
                  dbo.PM10000.DOCDATE, dbo.PM10000.PSTGDATE, dbo.PM10000.VADDCDPR, dbo.PM10000.VADCDTRO, dbo.PM10000.PYMTRMID, dbo.PM10000.TAXSCHID, dbo.PM10000.DUEDATE, dbo.PM10000.DISCDATE, dbo.PM10000.PRCHAMNT, dbo.PM10000.CHRGAMNT,
                  dbo.PM10000.CASHAMNT, dbo.PM10000.CDOCNMBR, dbo.PM10000.CHEKAMNT, dbo.PM10000.CHEKNMBR, dbo.PM10000.CURNCYID, dbo.PM10000.CHEKBKID, dbo.PM10000.TRXDSCRN, dbo.PM10000.TAXAMNT, dbo.PM10000.FRTAMNT,
                  dbo.PM10000.TRDISAMT, dbo.PM10000.PORDNMBR, dbo.PM10000.SHIPMTHD, dbo.PM10000.CURTRXAM, dbo.PM10000.POSTED, dbo.PM10000.PSTGSTUS, dbo.PM10000.APPLDAMT, dbo.PM10000.VCHRNMBR, dbo.PM10000.POSTEDDT,
                  dbo.PM10000.PTDUSRID, dbo.PM10000.PCHSCHID, dbo.PM10000.FRTSCHID, dbo.PM10000.MSCSCHID, dbo.PM10000.PRINTED, RTRIM(dbo.PM10000.VCHRNMBR) + ' | ' + RTRIM(dbo.PM10000.DOCNUMBR) + ' | ' + RTRIM(dbo.PM00200.VENDNAME) AS VchLabel,
                  dbo.PM10100.DSTSQNUM, dbo.PM10100.CNTRLTYP, dbo.PM10100.CRDTAMNT, dbo.PM10100.DEBITAMT, dbo.PM10100.DSTINDX, dbo.PM10100.DISTTYPE, dbo.GL00100.ACTDESCR, dbo.GL00105.ACTNUMST,
                  CASE WHEN CRDTAMNT > 0 THEN CRDTAMNT ELSE DEBITAMT END AS DistAmt, CASE WHEN CRDTAMNT > 0 THEN 'C' ELSE 'D' END AS Distsign, dbo.PM10100.DistRef, dbo.NumberToWords(dbo.PM10100.DEBITAMT) AS NumWords
FROM        dbo.GL00100 INNER JOIN
                  dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX RIGHT OUTER JOIN
                  dbo.PM10000 INNER JOIN
                  dbo.SY00500 ON dbo.PM10000.BACHNUMB = dbo.SY00500.BACHNUMB INNER JOIN
                  dbo.PM00200 ON dbo.PM10000.VENDORID = dbo.PM00200.VENDORID INNER JOIN
                  dbo.PM10100 ON dbo.PM10000.VCHRNMBR = dbo.PM10100.VCHRNMBR ON dbo.GL00100.ACTINDX = dbo.PM10100.DSTINDX LEFT OUTER JOIN
                  dbo.SY03900 ON dbo.PM10000.NOTEINDX = dbo.SY03900.NOTEINDX

GO


/****** Object:  UserDefinedFunction [dbo].[BI_NumberToWords]    Script Date: 13/01/2015 06:34:16 PM ******/
DROP FUNCTION [dbo].[BI_NumberToWords]
GO

/****** Object:  UserDefinedFunction [dbo].[BI_NumberToWords]    Script Date: 13/01/2015 06:34:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BI_NumberToWords]
(
    @NumberString nvarchar(max)
) RETURNS VARCHAR(8000)
AS BEGIN

DECLARE @Number Numeric (38, 0)
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3)
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

set @Number = cast( @NumberString as Numeric (38, 0) )

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

    -- get chunks of 3 numbers at a time, padded with leading zeros
    SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

    IF @chunk <> '000' BEGIN
        SELECT @tensones = SUBSTRING(@chunk, 2, 2)
             , @hundreds = SUBSTRING(@chunk, 1, 1)
             , @tens = SUBSTRING(@chunk, 2, 1)
             , @ones = SUBSTRING(@chunk, 3, 1)

        -- If twenty or less, use the word directly from @NumbersTable
        IF CONVERT(INT, @tensones) <= 20 OR @Ones='0'
        BEGIN
            SET @outputString = (SELECT word
                                      FROM @NumbersTable
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
            END
         ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' '
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE @tens + '0' = number)
                             + '-'
                             + (SELECT word
                                    FROM @NumbersTable
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
        END

        -- now get the hundreds
        IF @hundreds <> '0' BEGIN
            SET @outputString  = (SELECT word
                                      FROM @NumbersTable
                                      WHERE '0' + @hundreds = number)
                                + ' hundred '
                                + @outputString
        END
    END

    SELECT @counter = @counter + 1
         , @position = @position - 3

END

SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000) + 'dollars and ' + right(@numberstring,2) + ' cents'


RETURN @outputString
END
GO


SQL - Numbers to words function

Original post here
http://stackoverflow.com/questions/4245330/sql-query-for-retrieving-numeric-value-and-printing-as-words


Slightly modified function. Fixed issues where numbers were rounding incorrectly and cents were not being displayed correctly.

/****** Object:  UserDefinedFunction [dbo].[BI_NumberToWords]    Script Date: 8/9/2016 11:43:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[BI_NumberToWords]
(
    @NumberString nvarchar(max)
) RETURNS VARCHAR(8000)
AS BEGIN

DECLARE @Number Numeric (38, 0)
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3)
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

set @Number = cast( ROUND(@NumberString,0,1) as Numeric (38, 0) )

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

    -- get chunks of 3 numbers at a time, padded with leading zeros
    SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

    IF @chunk <> '000' BEGIN
        SELECT @tensones = SUBSTRING(@chunk, 2, 2)
             , @hundreds = SUBSTRING(@chunk, 1, 1)
             , @tens = SUBSTRING(@chunk, 2, 1)
             , @ones = SUBSTRING(@chunk, 3, 1)

        -- If twenty or less, use the word directly from @NumbersTable
        IF CONVERT(INT, @tensones) <= 20 OR @Ones='0'
        BEGIN
            SET @outputString = (SELECT word
                                      FROM @NumbersTable
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
            END
         ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' '
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE @tens + '0' = number)
                             + '-'
                             + (SELECT word
                                    FROM @NumbersTable
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
        END

        -- now get the hundreds
        IF @hundreds <> '0' BEGIN
            SET @outputString  = (SELECT word
                                      FROM @NumbersTable
                                      WHERE '0' + @hundreds = number)
                                + ' hundred '
                                + @outputString
        END
    END

    SELECT @counter = @counter + 1
         , @position = @position - 3

END

SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000) + ' dollars and ' + right(CAST(CAST(@numberstring AS DECIMAL(18,2)) AS VARCHAR(MAX)),2) + ' cents'


RETURN @outputString
END


SQL - The database principal owns a schema in the database, and cannot be dropped

Cause:
This error occurs when you try to remove access to a database for a user that owns the schema.
This generally happens if the user was accidentally set as the owner of the schema

Resolution:

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('myuser')

--Will provide the schema owned by the user

ALTER AUTHORIZATION ON SCHEMA::MySchema TO dbo

--Will set the schema ownership back to dbo, and allow you to modify the security on the user normally

Sunday, January 11, 2015

Microsoft Surface - Screen is stuck at Please Wait...

Problem:
The screen is stuck at "Please Wait" while booting, and is unresponsive.

Cause:
This can occur after an update, or if you run out of space on the hard drive.

Solution:
Reset the surface to factory settings

Hold down Volume Up and Power buttons for 15 seconds until you see the Surface logo flash
Continue holding for another 10 seconds
Release both
Press the power button
This will force the surface to turn off.

If you power it on again, and it goes back to "Please wait" repeat this three times to get windows to go into recovery mode.

Once Windows detects a bad shutdown 3 times, it will launch the recovery console
Select Reset, remove files
This will wipe the surface, and set it back to it's original state.

Wednesday, January 7, 2015

Dynamics NAV - TableData 2000000001 does not exist.

The user is not marked as db_owner
give the user db_owner permissions to resolve the error

Note that for NAV to function, you do not need to add SQL users directly to the databases.
If this is what you have done, it is incorrect.
Instead, refer to this solution.

http://cowmasterscorner.blogspot.com/2015/01/dynamics-nav-you-do-not-have-access-to.html

Dynamics NAV - You do not have access to microsoft dynamics nav. Verify that you have been setup as a valid user in Microsoft Dynamics NAV.

Issue:
You get this error even after setting up the new users and giving SUPER permissions.


Cause:
The new user auto-generates default connection information in the ClientUserSettings file and attempt to connect to the original default instance and database.
If this instance is offline, it gets stuck trying to always connect to something that does not exist

Solution:

  • Navigate to C:\Users\<user>\AppData\Roaming\Microsoft\Microsoft Dynamics NAV\71
  • Open ClientUserSettings in notepad
  • Look for     <add key="ClientServicesPort" value="7046"/>
    • Change it to the port of the instance you are trying to connect to
  • Look for      <add key="ServerInstance" value="DynamicsNAV71"/>
    • Chnage it to the name of the instance you are trying to connect to