Cowmaster's Corner: January 2015

Saturday, January 31, 2015

Dynamics NAV - Windows Group users cannot access NAV

Set the license type on the NAV user to Windows Group instead of Full license

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

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

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

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
Select Bank Account (ensure Bank account Currency matches your check currency)
Select "Computer Check"
Applying Credit memos to a check will cause blank lines to appear on your remittance. Do not do this.
Click "Print Check"
Select Bank Account

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

Used for defining levels of escalation with different messages, does not affect AR (but charges can be added)
Requires code to be fully automated

Set up and assign finance charge terms

Will create finance charges that affect customer AR
Requires code to be fully automated

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 Depreciation Types

Straight Line

Requires Begin and End Date

Declining Balance 1

Requires Annual % in Declining-Balance % field
*This is the most commonly used method

Declining Balance 2

Requires Annual % in Declining-Balance % field
Prorates each month's depreciation so each month has a different declining depreciation that adds up to the annual %

From FA List, Click Calculate Depreciation

Enter Date to calculate depreciation to
The rate used is based on the number of depreciation periods or % defined on the asset book card
Entering an Ending Book Value will stop depreciation at that value, and calculate at 0 from that point on
***Depreciation will still be calculated on the TOTAL value of the asset, including the Ending Book Value, it will just stop calculating depreciation when the value of the asset reaches the ending Book Value***

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

Enter Bank Statement Info

Import from File
Copy/Paste
Manual Entry
Bank Statement Line Type

Bank Account Ledger Entry - Should match AP and AR
Check Ledger Entry - Should match checks written directly from Bank
Difference - Rounding, Errors, or known differences in bank statement and internal trx

Suggest Lines
Flag lines as Differences
Delete lines
Import Lines

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

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
Enter and apply payments without posting to GL

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
- 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
- Transfer Stock Worksheet - Immediate transfer
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

Assembly Orders

Assembly Setup

Number series
Create Movements Automatically: For assemble-to-order only

Retail Item>Assembly BOM

Define Assembly Components

Assembly Orders

Choose item to assemble
Post

Replenishment

Office Sales

Sales Order

Create Whse. Shipment

Release
Create Pick ticket

Store 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
Profiles
Statement Options

Terminal Setup

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

Start Day

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

Declare Tender
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
Enter valid starting and ending date
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:

Run the Dynamics NAV Administration Console
Select your instance
Click Edit
Look in Client Services Fast Tab
Change the Max Upload Size field to the largest amount of data you want to be able to transfer in MB
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

Friday, January 16, 2015

Dynamics NAV - Filter Criteria Syntax

http://msdn.microsoft.com/en-us/library/hh879066(v=nav.71).aspx

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

Monday, January 12, 2015

Dynamics GP - Tables involved in Inventory to GL Reconcile

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:
Change default connection string on nav shortcut

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"/>

Change it to the name of the instance you are trying to connect to

OR

Windows user link to the nav account has been broken due to server migration or upgrade.
From user account, re-select windows account to re-link to new windows id