August 15, 2011 at 9:01 am
Hi everyone.
I have a store procedure that has a summary query that return 4 rows but only inserts 2 rows into my table. Please take a look at the code below and tell me what I am doing wrong.
Thanks.
USE [ISPurchasing]
GO
/****** Object: StoredProcedure [dbo].[cust_CreateJournalEntry] Script Date: 08/15/2011 10:15:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
DECLARE @ORDERSTATUS VARCHAR(15)
DECLARE @NEWORDERSTATUS VARCHAR(15)
DECLARE @ReqID Varchar(16)
DECLARE @OrderItemID INT
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
--Get rate type home
SELECT @rate_type_home = rate_type_home FROM PLT_DBOH_DATA..glco
--Get rate type oper
SELECT @rate_type_oper = rate_type_oper FROM PLT_DBOH_DATA..glco
--Get Home Currency
SELECT @home_currency = home_currency FROM PLT_DBOH_DATA..glco
--Get Oper Currency
SELECT @oper_currency = oper_currency FROM PLT_DBOH_DATA..glco
--Get Company_Code
SELECT @company_code = company_code FROM PLT_DBOH_DATA..glco
--Get Company Id
SELECT @company_id = 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
--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
--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
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
SELECT @ReqID = REQ.RequisitionNumber, @ORDERSTATUS='',
@NEWORDERSTATUS = '', @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 ACCOUNT-COA CODE
SELECT @account = LTRIM(RTRIM(CONVERT(VARCHAR(10), RLI.POChartOfAccount)))
FROM RequisitionLineItems RLI
WHERE RLI.RequisitionNumber = @ReqID --AND RLI.DetailsID = @OrderItemID
--GET BUDGET UNIT-COA CODE
SELECT @BudgetUnit = RLI.BudgetUnit
FROM RequisitionLineItems RLI
WHERE RLI.RequisitionNumber = @ReqID --AND RLI.DetailsID = @OrderItemID
--GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
SELECT SUM(UnitPrice * UnitsOrdered)
AS ExtendedPrice,LTRIM(RTRIM(CONVERT(VARCHAR(10), POChartOfAccount)))
AS AccountCode,
BudgetUnit FROM RequisitionLineItems
WHERE RequisitionNumber = @ReqID
GROUP BY POChartOfAccount,BudgetUnit
SELECT @OrderItemID = @ReqID
SELECT @ExtendedPrice = SUM(UnitPrice * UnitsOrdered)FROM RequisitionLineItems WHERE RequisitionNumber = @ReqID
SELECT @ShortDescription = ProductName FROM RequisitionLineItems WHERE RequisitionNumber = @ReqID
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)
VALUES(@next_num, @SequenceID, @company_code, @company_id, @AccountCode, SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', @ExtendedPrice, @ExtendedPrice,'USD',1,0,0,3051,0, @account, @BudgetUnit,'','',0, @ExtendedPrice,1,@rate_type_home,@rate_type_oper,@org_id)
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
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)
VALUES(@next_num, @SequenceID ,@company_code, @company_id, '2700000', SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', (@ExtendedPrice)*-1, (@ExtendedPrice)*-1,'USD',1,0,0,3051,0, '2700', '000','','',0, (@ExtendedPrice)-1,1,@rate_type_home,@rate_type_oper,@org_id)
August 15, 2011 at 1:45 pm
What an awful post. There is no way to help you even if anyone wanted to. Do you expect people to read your mind?
I see two inserts into PLT_DBOH_DATA..gltrxdet
I see one insert into PLT_DBOH_DATA..gltrx_all
August 15, 2011 at 2:01 pm
Like the previous poster said, you only have two inserts.
that being said I would start with "what am I doing wrong"? You are managing your own identity type data.
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
That is nothing but a total pain in the backside and it will come back to haunt you someday. Just use an identity column and be done trying to manipulate this yourself. How can you handle more than 1 simultaneous insert? This can't. Do you also decrement this by 1 when deleting and adjust all the other records accordingly? Let's say you have records 1 - 10 and you delete record 6. Does everything that is currently >6 become one less? Do you update your control value to 1 less? What about children records? This type of thing is fraught with errors. You will spend weeks trying to manage this yourself and still miss some times where it just won't work.
Then let's looks at your variable assignment...
SELECT @rate_type_home = rate_type_home FROM PLT_DBOH_DATA..glco
--Get rate type oper
SELECT @rate_type_oper = rate_type_oper FROM PLT_DBOH_DATA..glco
--Get Home Currency
SELECT @home_currency = home_currency FROM PLT_DBOH_DATA..glco
--Get Oper Currency
SELECT @oper_currency = oper_currency FROM PLT_DBOH_DATA..glco
--Get Company_Code
SELECT @company_code = company_code FROM PLT_DBOH_DATA..glco
--Get Company Id
SELECT @company_id = company_id FROM PLT_DBOH_DATA..glco
You could do this all in one select instead of repeated trips to the same table. Like this.
SELECT @rate_type_home = rate_type_home
,@rate_type_oper = rate_type_oper
,@home_currency = home_currency
,@oper_currency = oper_currency
,@company_code = company_code,
@company_id = company_id
FROM PLT_DBOH_DATA..glco
This all assumes there is only 1 record in that table???
Let's look at Org_id. You select top 1. What is the top 1? There is no order by clause on the select. It will not always return the same record.
--get the Org_id
SELECT TOP 1 @org_id = org_id FROM PLT_DBOH_DATA..smspiduser_vw spid WHERE global_user = 1
Not trying to bust your chops but this code will be difficult to maintain, really difficult for your replacement, and incredibly expensive when your company has to hire a consultant to unravel this.
_______________________________________________________________
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 15, 2011 at 3:36 pm
Thanks Sean. I will look at a better way to code this. I actually inherited this database and I’m trying to modify it the best way possible without reinventing the wheel. I agree with you on the control number issue. I personally would have elected to have SQL Server increment the number, but this is how it was coded and I’ve been hesitant to change it to an identity column because I don’t know what effect it might have on the front end. I really appreciate your response and will look at other options.
As for nadabadan your response was awful. I don’t expect you to read my mind and I didn’t need or want your rude response. There are comments in the code so anyone that knows a little about T-SQL could have figured it out. Next time keep your useless comments to yourself and try to be more professional when posting responses.
August 15, 2011 at 3:52 pm
troe_atl (8/15/2011)
Hi everyone.I have a store procedure that has a summary query that return 4 rows but only inserts 2 rows into my table. Please take a look at the code below and tell me what I am doing wrong.
My question is what is the purpose of this procedure? Is it meant to update some summary tables or to return records to the proc caller? Most of the select statements set variables, but it the midst of all that is a random select:
SELECT SUM(UnitPrice * UnitsOrdered)
AS ExtendedPrice,LTRIM(RTRIM(CONVERT(VARCHAR(10), POChartOfAccount)))
AS AccountCode,
BudgetUnit FROM RequisitionLineItems
WHERE RequisitionNumber = @ReqID
GROUP BY POChartOfAccount,BudgetUnit
So, in your own words, what is the purpose of this sproc?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 15, 2011 at 8:15 pm
There was no purpose for this particular query other than to help me see the results I wanted. I have cleaned the code up and posted it below.
SELECT SUM(UnitPrice * UnitsOrdered)
AS ExtendedPrice,LTRIM(RTRIM(CONVERT(VARCHAR(10), POChartOfAccount)))
AS AccountCode,
BudgetUnit FROM RequisitionLineItems
WHERE RequisitionNumber = @ReqID
GROUP BY POChartOfAccount,BudgetUnit
Purpose: The purpose of this procedure is to insert purchase order records into a financial system.
How it works: Our purchasing department uses an Access front end that makes a call to this procedure through a pass-through query every time an order is created. The procedure accepts a purchase order number into the @PO_NUMBER parameter and then inserts the PO header information into the gltrx_all table and the PO details into the gltrxdet table. The procedure was initially coded with a cursor that processed each individual line item of a purchase order and created an entry into the finance system for each line item. My goal is to get the totals of each line item based on the @AccountCode and insert the records into the gltrxdet table.
This is the results I want
Total Account Code
1.99 6790283
44.97 6100383
44.97 6100483
44.97 6100583
44.97 6100683
44.97 6100783
This is the result I’m getting
Total Account Code
226.84 6790283
Thanks for your help.
USE [ISPurchasing]
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
DECLARE @ORDERSTATUS VARCHAR(15)
DECLARE @NEWORDERSTATUS VARCHAR(15)
DECLARE @ReqID Varchar(16)
DECLARE @OrderItemID INT
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
--Get rate type home
SELECT @rate_type_home = rate_type_home FROM PLT_DBOH_DATA..glco
--Get rate type oper
SELECT @rate_type_oper = rate_type_oper FROM PLT_DBOH_DATA..glco
--Get Home Currency
SELECT @home_currency = home_currency FROM PLT_DBOH_DATA..glco
--Get Oper Currency
SELECT @oper_currency = oper_currency FROM PLT_DBOH_DATA..glco
--Get Company_Code
SELECT @company_code = company_code FROM PLT_DBOH_DATA..glco
--Get Company Id
SELECT @company_id = 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
--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
--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
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
--PO header information
SELECT @ReqID = REQ.RequisitionNumber, @ORDERSTATUS='',
@NEWORDERSTATUS = '', @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)
--PO details information
--GET ACCOUNT-COA CODE
SELECT @account = LTRIM(RTRIM(CONVERT(VARCHAR(10), RLI.POChartOfAccount)))
FROM RequisitionLineItems RLI
WHERE RLI.RequisitionNumber = @ReqID --AND RLI.DetailsID = @OrderItemID
--GET BUDGET UNIT-COA CODE
SELECT @BudgetUnit = RLI.BudgetUnit
FROM RequisitionLineItems RLI
WHERE RLI.RequisitionNumber = @ReqID --AND RLI.DetailsID = @OrderItemID
--GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
--GET REQUISTION NUMBER
SELECT @OrderItemID = @ReqID
SELECT @ExtendedPrice = SUM(UnitPrice * UnitsOrdered)FROM RequisitionLineItems WHERE RequisitionNumber = @ReqID
SELECT @ShortDescription = ProductName FROM RequisitionLineItems WHERE RequisitionNumber = @ReqID
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)
VALUES(@next_num, @SequenceID, @company_code, @company_id, @AccountCode, SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', @ExtendedPrice, @ExtendedPrice,'USD',1,0,0,3051,0, @account, @BudgetUnit,'','',0, @ExtendedPrice,1,@rate_type_home,@rate_type_oper,@org_id)
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
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)
VALUES(@next_num, @SequenceID ,@company_code, @company_id, '2700000', SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', (@ExtendedPrice)*-1, (@ExtendedPrice)*-1,'USD',1,0,0,3051,0, '2700', '000','','',0, (@ExtendedPrice)-1,1,@rate_type_home,@rate_type_oper,@org_id)
August 15, 2011 at 8:31 pm
troe_atl (8/15/2011)
As for nadabadan your response was awful. I don’t expect you to read my mind and I didn’t need or want your rude response. There are comments in the code so anyone that knows a little about T-SQL could have figured it out. Next time keep your useless comments to yourself and try to be more professional when posting responses.
There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.
Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.
August 15, 2011 at 9:03 pm
There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.
Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.
I'm not arguing with you I'm just saying that it would have been better if you kept your comments to yourself. I see that you are the only one in this post that responded in a negative manner. As for your comment about not being smart enough to do my job, shows your ignorance because you've definitely needed help before and you certainly don't know everything there is to know about T-SQL. So I'm done. I won't continue to respond if choose reply. It is so easy for people to talk slick behind a computer screen so I won't waste my time.
August 15, 2011 at 10:04 pm
nadabadan (8/15/2011)
troe_atl (8/15/2011)
As for nadabadan your response was awful. I don’t expect you to read my mind and I didn’t need or want your rude response. There are comments in the code so anyone that knows a little about T-SQL could have figured it out. Next time keep your useless comments to yourself and try to be more professional when posting responses.There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.
Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.
nadabadan, what do you get out of being a second-rate Celko? You're being crass, rude, and pompous for nothing. At least JC takes the time to post some impressive code after he insults someone. You called this guy's code garden variety junk, why don't you show him how to fix it? Time to put up or shut up.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 16, 2011 at 5:43 am
troe_atl (8/15/2011)
Hi everyone.I have a store procedure that has a summary query that return 4 rows but only inserts 2 rows into my table. Please take a look at the code below and tell me what I am doing wrong.
Thanks.
Here's a few improvements, including formatting and removing unused code. If there's anything you're unsure of, just ask.
Do take on board Sean's comments - if you must use a sequence table, there are far better ways of doing it.
You will need to wrap this lot in a transaction, which means inevitably incorporating error trapping - read up on TRY-CATCH in BOL.
Ignore rude posters. Avoid replying at all. You've come here for help, not insults.
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
--DECLARE @ORDERSTATUS VARCHAR(15)
--DECLARE @NEWORDERSTATUS VARCHAR(15)
DECLARE @ReqID Varchar(16)
--DECLARE @OrderItemID INT
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
-- WHERE ??
--get the Org_id
SELECT TOP 1 @org_id = org_id
FROM PLT_DBOH_DATA..smspiduser_vw spid
WHERE global_user = 1
-- ORDER BY ??
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-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
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)
SELECT
journal_type= 'PO',
journal_ctrl_num= @next_num,
journal_description = 'From ISPurchasing PO: '+ @PO_NUMBER,
date_entered= @date,
date_applied= @date,
recurring_flag= 0,
repeating_flag= 0,
reversing_flag= 0,
hold_flag= 0,
posted_flag= 0,
date_posted= 0,
source_batch_code= '',
batch_code= '',
type_flag= 0,
intercompany_flag= 0,
company_code= @company_code,
app_id= 6000,
home_cur_code= @home_currency,
document_1= '',
trx_type= 111,
[user_id]= 7,
source_company_code = @company_code,
process_group_num= '',
oper_cur_code= @oper_currency,
org_id= @org_id,
interbranch_flag= 0
--PO header information
SELECT
@ReqID= RequisitionNumber,
--@ORDERSTATUS='',
--@NEWORDERSTATUS = '',
@VendorName= SupplierName
FROM Requisition
WHERE POID = @PO_NUMBER
--PO details information
--GET ACCOUNT-COA CODE / GET BUDGET UNIT-COA CODE
SELECT
@account= LTRIM(RTRIM(CONVERT(VARCHAR(10),POChartOfAccount))),
@BudgetUnit= BudgetUnit,
@ExtendedPrice= SUM(UnitPrice * UnitsOrdered),
@ShortDescription= ProductName
FROM RequisitionLineItems
WHERE RequisitionNumber = @ReqID --AND RLI.DetailsID = @OrderItemID
--GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
--SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
--SET @SequenceID = @SequenceID + 1
--SET REQUISTION NUMBER
--SET @OrderItemID = @ReqID
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= 1, --@SequenceID,
rec_company_code= @company_code,
company_id= @company_id,
account_code= @account + @BudgetUnit, --@AccountCode,
[description]= SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),
document_1= @ShortDescription,
document_2= CONVERT(VARCHAR(16), ''),
reference_code= '',
balance= @ExtendedPrice,
nat_balance= @ExtendedPrice,
nat_cur_code= 'USD',
rate= 1,
posted_flag= 0,
date_posted= 0,
trx_type= 3051,
offset_flag= 0,
seg1_code= @account,
seg2_code= @BudgetUnit,
seg3_code= '',
seg4_code= '',
seq_ref_id= 0,
balance_oper= @ExtendedPrice,
rate_oper= 1,
rate_type_home= @rate_type_home,
rate_type_oper= @rate_type_oper,
org_id= @org_id
UNION ALL
SELECT
journal_ctrl_num= @next_num,
sequence_id= 2, --@SequenceID ,
rec_company_code= @company_code,
company_id= @company_id,
account_code= '2700000',
[description]= SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),
document_1= @ShortDescription,
document_2= CONVERT(VARCHAR(16), ''),
reference_code= '',
balance= (@ExtendedPrice)*-1,
nat_balance= (@ExtendedPrice)*-1,
nat_cur_code= 'USD',
rate= 1,
posted_flag= 0,
date_posted= 0,
trx_type= 3051,
offset_flag= 0,
seg1_code= '2700',
seg2_code= '000',
seg3_code= '',
seg4_code= '',
seq_ref_id= 0,
balance_oper= (@ExtendedPrice)-1,
rate_oper= 1,
rate_type_home= @rate_type_home,
rate_type_oper= @rate_type_oper,
org_id= @org_id
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 16, 2011 at 7:07 am
troe_atl (8/15/2011)
Thanks Sean. I will look at a better way to code this. I actually inherited this database and I’m trying to modify it the best way possible without reinventing the wheel. I agree with you on the control number issue. I personally would have elected to have SQL Server increment the number, but this is how it was coded and I’ve been hesitant to change it to an identity column because I don’t know what effect it might have on the front end. I really appreciate your response and will look at other options....
Well I guess then since you are the replacement my comment about it being really painful for the replacement is true.
Did everybody else's responses get you close to a resolution? If not, let us know what is missing and we can help.
_______________________________________________________________
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 16, 2011 at 7:55 am
nadabadan (8/15/2011)
troe_atl (8/15/2011)
As for nadabadan your response was awful. I don’t expect you to read my mind and I didn’t need or want your rude response. There are comments in the code so anyone that knows a little about T-SQL could have figured it out. Next time keep your useless comments to yourself and try to be more professional when posting responses.There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.
Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.
The above post has been reported to forum moderators. Please ignore it till they have had a chance to review it against forum posting policies.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 16, 2011 at 2:51 pm
Thanks Chris. I will try this when I get back to my office.
August 16, 2011 at 2:53 pm
No kidding. The database I'm working with has over 900 tables with sprocs calling sprocs, it is a nightmare!! Thanks again for your help Sean.
August 16, 2011 at 3:13 pm
Happy to help. I too have been thrown into those kind of systems. The learning curve is more like a vertical wall. Painful but once you understand and are able to unravel the shredded pieces of pasta left for you patterns of suck start to emerge and you can start predicting the hideously bad code that is going to be right in front of you.
Feel free to come back and ask for help when you get stumped. There are lots of good people around here who are willing to help.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply