1. Original Excel Format. Fig1
2. Import
Excel File into table of VFFSalesBudgetRawIn by SSMS . Fig2 & 3
3. After import, use select command to check. Fig4
4. Run Stored Procedures to convert data format from columns to rows table of VFFSalesBudgetOut
USE [VFFnav2013]
GO
/****** Object: Convert the table of
dbo.VFFSalesBudgetRawIn to dbo.VFFSalesBudgetOut, then copy & Paste into
Navision ****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Delete from dbo.[VFFSalesBudgetOut]
Go
Declare @id
int,
@Item Char(20),
@Region Char(20),
@SalesCode Char(10),
@Customer Char(10),
@M1 Decimal(18,4),
@M2 Decimal(18,4),
@M3 Decimal(18,4),
@M4 Decimal(18,4),
@M5 Decimal(18,4),
@M6 Decimal(18,4),
@M7 Decimal(18,4),
@M8 Decimal(18,4),
@M9 Decimal(18,4),
@M10 Decimal(18,4),
@M11 Decimal(18,4),
@M12 Decimal(18,4),
@M1Q Decimal(18,4),
@M2Q Decimal(18,4),
@M3Q Decimal(18,4),
@M4Q Decimal(18,4),
@M5Q Decimal(18,4),
@M6Q Decimal(18,4),
@M7Q Decimal(18,4),
@M8Q Decimal(18,4),
@M9Q Decimal(18,4),
@M10Q Decimal(18,4),
@M11Q Decimal(18,4),
@M12Q Decimal(18,4),
@Descrip Char(100),
@UPrice Decimal(18,
4),
@IDCount Int
Set @id
= 1
Set @M1
= 0
set @M2
= 0
Set @M3
= 0
Set
@M4 = 0
Set @M5
= 0
Set @M6
= 0
set @M7
= 0
Set @M8
= 0
Set
@M9 = 0
Set @M10
= 0
Set
@M11 = 0
Set @M12
= 0
Set @M1Q
= 0
set @M2Q
= 0
Set @M3Q
= 0
Set
@M4Q = 0
Set @M5Q
= 0
Set @M6Q
= 0
set @M7Q
= 0
Set @M8Q
= 0
Set
@M9Q = 0
Set @M10Q
= 0
Set
@M11Q = 0
Set @M12Q
= 0
Set
@UPrice = 0
Select @IDCount = Max(IDNo) from
dbo.[VFFSalesBudgetRawIn]
WHILE @id <= @IDCount
Begin
Select
@Item = Item,
@Region = Region, @SalesCode =
SalesCode, @Customer = Customer
@M1 = M1Amt, @M2 = M2Amt, @M3 = M3Amt,
@M4 = M4Amt, @M5 = M5Amt, @M6 = M6Amt, @M7 = M7Amt, @M8 = M8Amt, @M9 = M9Amt,
@M10 = M10Amt, @M11 = M11Amt, @M12 = M12Amt,
@M1Q = M1Qty, @M2Q = M2Qty, @M3Q =
M3Qty, @M4Q = M4Qty, @M5Q = M5Qty, @M6Q = M6Qty, @M7Q = M7Qty, @M8Q = M8Qty,
@M9Q = M9Qty, @M10Q = M10Qty, @M11Q = M11Qty, @M12Q = M12Qty,
@Descrip = Description, @UPrice =
Price
From
dbo.[VFFSalesBudgetRawIn]
where IDNo = @id
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, @Customer, '1/1/2015', @M1, @M1Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, @Customer, '2/1/2015', @M2, @M2Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region, SalesCode,
Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '3/1/2015', @M3, @M3Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region, SalesCode,
Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '4/1/2015', @M4, @M4Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region, SalesCode,
Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '5/1/2015', @M5, @M5Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '6/1/2015', @M6, @M6Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '7/1/2015', @M7, @M7Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '8/1/2015', @M8, @M8Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '9/1/2015', @M9, @M9Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '10/1/2015', @M10, @M10Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, '11/1/2015', @M11, @M11Q, @Descrip, @UPrice)
INSERT INTO
[dbo].[VFFSalesBudgetOut]
(Item, Region,
SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)
VALUES (@Item, @Region,
@SalesCode, Customer, '12/1/2015', @M12, @M12Q, @Descrip, @UPrice)
Set @id = @id + 1
Print @id
End
5. Use Select command to check. Fig5
6. Copy All data and paste into excel ;Fig6
7. In Excel, Add 1st column to SALES2017
8. Open Navision then open Sales Budget >> New >> ; Fig7
9. Select "SALES2017" and click "Edit Budget", then click the Ellipsis icon that show Fig 8
A. Click "New" in "Item Budget Entries" page, select 1st line and right button . click "Paste Rows" Fig9
B. One time copy/paste 2000 records is better.
沒有留言:
張貼留言