August 18, 2011 at 1:22 pm
OK something like this should give you a decent example. This will keep your entries grouped together but you WILL have to figure out what column in your source table you want to sort by.
--This is sample table to demonstrate the concept.
--This would be your source table in your code.
create table #Vals
(
Val varchar(25),
SortColumn int
)
insert #Vals (Val, SortColumn)
Values ('Requisition 1' ,1),
('Requisition 2' ,2),
('Requisition 3' ,3),
('Requisition 4' ,4),
('Requisition 5' ,5),
('Requisition 6' ,6),
('Requisition 7' ,7),
('Requisition 8' ,8),
('Requisition 9' ,9),
('Requisition 10' ,10),
('Requisition 11' ,11),
('Requisition 12' ,12),
('Requisition 13' ,13),
('Requisition 14' ,14),
('Requisition 15' ,15)
--this is a shortened version of Chris' temp table
create table #Requisition
(
SequenceID int,
SomeValue varchar(25)
)
--this effectively makes the ROW_NUMBER() function return every other odd number
insert #Requisition
select ROW_NUMBER() over (order by SortColumn) * 2 - 1, Val + ' Table 1'
from #Vals
--this effectively makes the ROW_NUMBER() function return every other even number
insert #Requisition
select ROW_NUMBER() over (order by SortColumn) * 2, Val + ' Table 2'
from #Vals
select * from #Requisition order by SequenceID
drop table #Vals
drop table #Requisition
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 2:46 am
troe_atl (8/18/2011)
Hi guys. I really appreciate all of the help in optimizing this sproc. Chris, I've tested the code you submitted and everything works fine but I need the results to look like the results below. I got the following results by using the cursor in the code I posted. Notice how the sequence_id increments for each record. Also notice how the account_code doesn't get truncated and the 2700000 account number follows each account code....
This mod should fix both:
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
SET NOCOUNT ON
--DECLARE @OrderID Varchar(16)
--DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
--DECLARE @account VARCHAR(10)
--DECLARE @BudgetUnit VARCHAR(10)
--DECLARE @AccountCode VARCHAR(20)
--DECLARE @ShortDescription VARCHAR(16)
--DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id
FROM PLT_DBOH_DATA..smspiduser_vw spid
WHERE global_user = 1
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
------------------------------------------------------------------
--Obtain the next control number
--SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask, 1, 12-LEN(next_jrnl_ctrl_code))
+ CAST(next_jrnl_ctrl_code AS VARCHAR(16))
FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
------------------------------------------------------------------
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
-- replaced by #Requisition below
--SELECT
-- @OrderID = REQ.RequisitionNumber,
-- @VendorName = REQ.SupplierName
--FROM Requisition REQ
--WHERE REQ.POID = @PO_NUMBER
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
--------------------------------
-- get the matching rows from Requisition into a local temporary table (query from toddasd)
-- drop the temp table if it already exists
IF OBJECT_ID (N'tempdb..#Requisition') IS NOT NULL DROP TABLE #Requisition
-- Introduces Sean's row numbering to replace hard-coded sequence_id
-- and a cross apply of two rows to double-up the output
SELECT
sequence_id = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
x.RowPair,
BudgetUnit= CAST(BudgetUnit AS VARCHAR(10)),-- @BudgetUnit
POChartOfAccount= CAST(POChartOfAccount AS VARCHAR(10)),-- @account
ShortDescription= 'Encumbrance',-- @ShortDescription
ExtendedPrice= SUM(UnitPrice * UnitsOrdered),-- @ExtendedPrice
OrderID= CAST(MAX(RequisitionNumber) AS Varchar(16)), -- @OrderID Varchar(16)
VendorName= CAST(MAX(SupplierName) AS VARCHAR(40))-- @VendorName VARCHAR(40)
INTO #Requisition -- local temporary table
FROM Requisition r
INNER JOIN PurchaseOrder po
ON r.POID = po.POID
INNER JOIN RequisitionLineItems rli
ON r.RequisitionNumber = rli.RequisitionNumber
CROSS JOIN (SELECT RowPair = CAST(1 AS TINYINT) UNION ALL SELECT 2) x -- double-up the output rows
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY BudgetUnit, POChartOfAccount
-------------------------------
-- first insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id, --@SequenceID,
rec_company_code = @company_code,
company_id= @company_id,
account_code = POChartOfAccount + BudgetUnit, --@AccountCode = @account + @BudgetUnit
[description] = @PO_NUMBER + ' | ' + VendorName, -- @VendorName
document_1= ShortDescription, --@ShortDescription,
document_2= CONVERT(VARCHAR(16), ''),
reference_code = '',
balance= ExtendedPrice, --@ExtendedPrice,
nat_balance= ExtendedPrice, --@ExtendedPrice,
nat_cur_code = 'USD',
rate= 1,
posted_flag= 0,
date_posted= 0,
trx_type= 111,
offset_flag= 0,
seg1_code= POChartOfAccount, --@Account VARCHAR(10)
seg2_code= BudgetUnit, --@BudgetUnit,
seg3_code= '',
seg4_code= '',
seq_ref_id= 0,
balance_oper = ExtendedPrice, --@ExtendedPrice,
rate_oper= 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id= @org_id
FROM #Requisition WHERE RowPair = 1
-- second insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id,--@SequenceID ,
rec_company_code = @company_code,
company_id = @company_id,
account_code = '2700000',
[description] = @PO_NUMBER + ' | ' + VendorName, -- @VendorName
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted= 0,
trx_type= 111,
offset_flag= 0,
seg1_code= '2700',
seg2_code= '000',
seg3_code= '',
seg4_code= '',
seq_ref_id= 0,
balance_oper = (ExtendedPrice)-1, -- (@ExtendedPrice)-1
rate_oper= 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id= @org_id
FROM #Requisition WHERE RowPair = 2
-------------------------------------------------------------------------
-------------------------------------------------------------------------
GO
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2011 at 7:14 am
Hi guys. Everything is working perfectly now!! Special thanks to Sean, Chris, Todd and all those that contributed. I really appreciate the time and effort you guys put into helping me with this procedure. I have also learned a few things in the process. Thanks again!!
August 19, 2011 at 7:17 am
Not so fast! We're not done yet! It still requires a transaction for those table updates, and a try-catch block to make it all work nicely....
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2011 at 7:42 am
I've added the transaction and try-catch block. Let me know if this will suffice.
Thanks.
USE [ISPurchasing]
GO
/****** Object: StoredProcedure [dbo].[cust_CreateJournalEntrymod] Script Date: 08/19/2011 09:44:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CreateJournalEntrymod]
@PO_NUMBER VARCHAR(10)
AS
SET NOCOUNT ON
--DECLARE @OrderID Varchar(16)
--DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
--DECLARE @account VARCHAR(10)
--DECLARE @BudgetUnit VARCHAR(10)
--DECLARE @AccountCode VARCHAR(20)
--DECLARE @ShortDescription VARCHAR(16)
--DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id
FROM PLT_DBOH_DATA..smspiduser_vw spid
WHERE global_user = 1
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
------------------------------------------------------------------
--Obtain the next control number
--SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask, 1, 12-LEN(next_jrnl_ctrl_code))
+ CAST(next_jrnl_ctrl_code AS VARCHAR(16))
FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
------------------------------------------------------------------
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
-- replaced by #Requisition below
--SELECT
-- @OrderID = REQ.RequisitionNumber,
-- @VendorName = REQ.SupplierName
--FROM Requisition REQ
--WHERE REQ.POID = @PO_NUMBER
BEGIN TRY
BEGIN TRAN
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
--------------------------------
-- get the matching rows from Requisition into a local temporary table (query from toddasd)
-- drop the temp table if it already exists
IF OBJECT_ID (N'tempdb..#Requisition') IS NOT NULL DROP TABLE #Requisition
-- Introduces Sean's row numbering to replace hard-coded sequence_id
-- and a cross apply of two rows to double-up the output
SELECT
sequence_id = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
x.RowPair,
BudgetUnit = CAST(BudgetUnit AS VARCHAR(10)), -- @BudgetUnit
POChartOfAccount = CAST(POChartOfAccount AS VARCHAR(10)), -- @account
ShortDescription = 'Encumbrance', -- @ShortDescription
ExtendedPrice = SUM(UnitPrice * UnitsOrdered), -- @ExtendedPrice
OrderID = CAST(MAX(r.RequisitionNumber) AS Varchar(16)), -- @OrderID Varchar(16)
VendorName = CAST(MAX(SupplierName) AS VARCHAR(40)) -- @VendorName VARCHAR(40)
INTO #Requisition -- local temporary table
FROM Requisition r
INNER JOIN PurchaseOrder po
ON r.POID = po.POID
INNER JOIN RequisitionLineItems rli
ON r.RequisitionNumber = rli.RequisitionNumber
CROSS JOIN (SELECT RowPair = CAST(1 AS TINYINT) UNION ALL SELECT 2) x -- double-up the output rows
WHERE po.POID = @PO_NUMBER
GROUP BY BudgetUnit, POChartOfAccount,x.RowPair
-------------------------------
-- first insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id, --@SequenceID,
rec_company_code = @company_code,
company_id = @company_id,
account_code = POChartOfAccount + BudgetUnit, --@AccountCode = @account + @BudgetUnit
[description] = @PO_NUMBER + ' | ' + VendorName, -- @VendorName
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = ExtendedPrice, --@ExtendedPrice,
nat_balance = ExtendedPrice, --@ExtendedPrice,
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = POChartOfAccount, --@Account VARCHAR(10)
seg2_code = BudgetUnit, --@BudgetUnit,
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = ExtendedPrice, --@ExtendedPrice,
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition WHERE RowPair = 1
-- second insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id, --@SequenceID ,
rec_company_code = @company_code,
company_id = @company_id,
account_code = '2700000',
[description] = @PO_NUMBER + ' | ' + VendorName, -- @VendorName
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = '2700',
seg2_code = '000',
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = (ExtendedPrice)-1, -- (@ExtendedPrice)-1
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition WHERE RowPair = 2
-------------------------------------------------------------------------
-------------------------------------------------------------------------
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
August 19, 2011 at 7:55 am
Close. I've moved some workings to outside of the transaction to mimimise the transaction duration (and any resources which might be unnecessarily affected).
USE [ISPurchasing]
GO
/****** Object: StoredProcedure [dbo].[cust_CreateJournalEntrymod] Script Date: 08/19/2011 09:44:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CreateJournalEntrymod]
@PO_NUMBER VARCHAR(10)
AS
SET NOCOUNT ON
--DECLARE @OrderID Varchar(16)
--DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
--DECLARE @account VARCHAR(10)
--DECLARE @BudgetUnit VARCHAR(10)
--DECLARE @AccountCode VARCHAR(20)
--DECLARE @ShortDescription VARCHAR(16)
--DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
BEGIN TRY
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id
FROM PLT_DBOH_DATA..smspiduser_vw spid
WHERE global_user = 1
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
------------------------------------------------------------------
--Obtain the next control number
--SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask, 1, 12-LEN(next_jrnl_ctrl_code))
+ CAST(next_jrnl_ctrl_code AS VARCHAR(16))
FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
------------------------------------------------------------------
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
--------------------------------------------------------------------------------------------
-- get the matching rows from Requisition into a local temporary table (query from toddasd)
-- drop the temp table if it already exists
IF OBJECT_ID (N'tempdb..#Requisition') IS NOT NULL DROP TABLE #Requisition
-- Introduces Sean's row numbering to replace hard-coded sequence_id
-- and a cross apply of two rows to double-up the output
SELECT
sequence_id = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
x.RowPair,
BudgetUnit = CAST(BudgetUnit AS VARCHAR(10)), -- @BudgetUnit
POChartOfAccount = CAST(POChartOfAccount AS VARCHAR(10)), -- @account
ShortDescription = 'Encumbrance', -- @ShortDescription
ExtendedPrice = SUM(UnitPrice * UnitsOrdered), -- @ExtendedPrice
OrderID = CAST(MAX(r.RequisitionNumber) AS Varchar(16)), -- @OrderID Varchar(16)
VendorName = CAST(MAX(SupplierName) AS VARCHAR(40)) -- @VendorName VARCHAR(40)
INTO #Requisition -- local temporary table
FROM Requisition r
INNER JOIN PurchaseOrder po
ON r.POID = po.POID
INNER JOIN RequisitionLineItems rli
ON r.RequisitionNumber = rli.RequisitionNumber
CROSS JOIN (SELECT RowPair = CAST(1 AS TINYINT) UNION ALL SELECT 2) x -- double-up the output rows
WHERE po.POID = @PO_NUMBER
GROUP BY BudgetUnit, POChartOfAccount,x.RowPair
--------------------------------------------------------------------------------------------
BEGIN TRAN
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
-- first insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id, --@SequenceID,
rec_company_code = @company_code,
company_id = @company_id,
account_code = POChartOfAccount + BudgetUnit, --@AccountCode = @account + @BudgetUnit
[description] = @PO_NUMBER + ' | ' + VendorName, -- @VendorName
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = ExtendedPrice, --@ExtendedPrice,
nat_balance = ExtendedPrice, --@ExtendedPrice,
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = POChartOfAccount, --@Account VARCHAR(10)
seg2_code = BudgetUnit, --@BudgetUnit,
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = ExtendedPrice, --@ExtendedPrice,
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition WHERE RowPair = 1
-- second insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id, --@SequenceID ,
rec_company_code = @company_code,
company_id = @company_id,
account_code = '2700000',
[description] = @PO_NUMBER + ' | ' + VendorName, -- @VendorName
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = '2700',
seg2_code = '000',
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = (ExtendedPrice)-1, -- (@ExtendedPrice)-1
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition WHERE RowPair = 2
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 -- no active user transaction (1 = open, committable; -1 = open, uncommittable )
ROLLBACK TRAN
-- put some sensible error trapping / rethrowing logic here
RETURN -1
END CATCH
RETURN 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2011 at 8:08 am
Looking good. One last suggestion. There is still the CONVERT(VARCHAR(16), '') Nitpicky I know but every little ounce of performance you can pick up from low hanging fruit is worth it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 8:11 am
What kind of performance gains have you seen? I bet with all the great stuff Chris put out there this thing is running a LOT faster??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 8:49 am
Thanks Sean. I will make the adjustment.
CONVERT(VARCHAR(16), '')
The modifications made by Chris have increased the performance of the procedure tremendously. You guys were very helpful. By the way, are there any tools, training, books, etc.. you guys suggest that will help me become more efficient?
August 19, 2011 at 9:18 am
There may be some formal training or books but nothing will give you better actual knowledge than getting your hands dirty. I am guessing you picked up way more knowledge in the last couple days about a lot of things you can take to your next project than you possibly could have gleaned from the same amount of time with books. This site is a tremendous wealth of knowledge and really helpful people. Hang out here on the forums, play with stuff. Make mistakes!!! The best learning tools are our mistakes. Glad to hear that you were able to make your process run faster.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 9:53 am
Thanks for the generous feedback, troe_atl. I'd agree with Sean's comments; lurk here from time to time, try resolving a few issues yourself - you don't have to post until you feel confident - and make a point of reading a few of the articles. Those written by Jeff Moden are particularly good because he explains exactly why he chooses a particular method, and the explanations are often more enlightening than the focus of the article. You could also follow members who are inclined to work on jobs which are broadly within your frame of interest, such as Sean, todd_asd, Mark squiggle (you'll know), Ninja, Lowell, Swepeso, Gsquared, Eugene Elutin, ALZDBA, John Mitchell, cadavre.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2011 at 3:01 am
One last point - the sequence table which holds the next sequence number for table gltrx_all. Below is a rewrite which will accelerate the update/read:
-- read the current control number, set the next
DECLARE @next_jrnl_ctrl_code INT
UPDATE PLT_DBOH_DATA..glnumber SET
@next_jrnl_ctrl_code = next_jrnl_ctrl_code,
next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
-- format it
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask, 1, 12-LEN(@next_jrnl_ctrl_code))
+ CAST(@next_jrnl_ctrl_code AS VARCHAR(16))
And here's an authoritative article explaining how to minimise contention when using sequence tables - and why I didn't put this section of code inside the transaction.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2011 at 6:19 am
Thanks Chris. That was a good article. Now I have another site to add to my favorites!!
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply