Performance Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance Issue

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2003-04-07 : 11:27:20

Hello, our invoicing application is taking around 2 minutes to create an invoice via an SP. I have been asked to look into this. One of the commands i have come across is DBCC SHOWCONTIG. I ran this for our main invoice table called ominvoicedetail. The out put is returned as follows

DBCC SHOWCONTIG scanning 'omInvoiceDetail' table...
Table: 'omInvoiceDetail' (674921576); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 28127
- Extents Scanned..............................: 3525
- Extent Switches..............................: 3528
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.63% [3516:3529]
- Logical Scan Fragmentation ..................: 0.60%
- Extent Scan Fragmentation ...................: 9.67%
- Avg. Bytes Free per Page.....................: 747.3
- Avg. Page Density (full).....................: 90.77%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Can anybody tell me if this table is OK or does it need reindexing.
If not are there other thing i can look at increasin the speed of invoicing SP

Thanks for your help in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-07 : 11:46:47

Which copy of the question do you want answered (hint).

Doubt it.
Try looking at the query plan for the SP.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

miranwar
Posting Yak Master

125 Posts

Posted - 2003-04-07 : 11:58:44

quote:

Which copy of the question do you want answered (hint).
Should the table be reindexed
Doubt it.
Try looking at the query plan for the SP.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-07 : 12:29:45

I'd say the sproc has more going on than the need to worry about the table...2 minutes is an awfully long time. How big is the sproc? Can you post it?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-07 : 12:43:34

quote:

Can anybody tell me if this table is OK or does it need reindexing.
If not are there other thing i can look at increasin the speed of invoicing SP

The table is not fragmented, so it does not need reindexing at this time (although this probably should be run occassionally.)

Yes there are other things to look at. But we would need to see the code in order to help. One thing that you can do is look at the execution plan of your queries.

Tara

miranwar
Posting Yak Master

125 Posts

Posted - 2003-04-07 : 13:05:55

here is the Sproc its quite big. I shall check the execution plan anyway

create procedure prSiRegularInvoice @ShowCd char(5) = null,
@ShowYear smallint = null,
@CurrencyCd char(10) = null,
@OwnerId int = null,
@IndivId int = null,
@AddrTypeId int = null,
@CopyToId int = null,
@CopyAddrTypeId int = null,
@OrderNumber int = null,
@OrderLineNumber smallint = null,
@PaymentTermId smallint = null,
@ScheduleId int = null,
@InvoicePrintDate datetime = null,
@AttentionFlag bit = 0,
@PrintMode char(1),
@PrinterName varchar(30) = null,
@DriverName varchar(30) = null,
@PortName varchar(30) = null,
@FaxNo varchar(20) = null,
@InvHdr char(1) = null,
@PrintInv char(1) = null,
@AttnStat char(1) = null,
@Deposit bit = 0,
@ErrorFlag bit = 0,
@Debug bit = 0

AS

BEGIN

/* declaring variables */
DECLARE @OrderLineStatus char(1)
DECLARE @InvoiceStatus char(1)
DECLARE @RevTypeCd char(15)
DECLARE @RevItemCd char(15)
DECLARE @InvoiceNumber int
DECLARE @CNInvoiceNumber int
DECLARE @InvoiceLineNumber smallint
DECLARE @CNInvoiceLineNumber smallint
DECLARE @PrevInvAmt money
DECLARE @OrderTotal money
DECLARE @PaidAmount money
DECLARE @PaymentPercent float(6)
DECLARE @PrevPaymentPercent float(6)
DECLARE @BasePrice money
DECLARE @TaxTotal money
DECLARE @AdjTotal money
DECLARE @TotalPrice money
DECLARE @ReUnallocAmt money
DECLARE @InvoiceAmount money
DECLARE @Count int
DECLARE @CurrentRow int
DECLARE @PaymentId int
DECLARE @TotalUnallocated money
DECLARE @PmtCount int
DECLARE @Site char(30)
DECLARE @RTAccCode char(11)
DECLARE @ARAccCode char(11)
DECLARE @RTDeptCode char(11)
DECLARE @ARDeptCode char(11)
DECLARE @ProductCd char(10)
DECLARE @DocumentType int
DECLARE @InvThreshold money
DECLARE @Today datetime
DECLARE @UserName varchar(30)
DECLARE @BatchInd bit
DECLARE @GLCode char(11)
DECLARE @InvCount int
DECLARE @InvNo int
DECLARE @NextVal int
DECLARE @LanguageCd int
DECLARE @TaxId smallint
DECLARE @CurrRound tinyint
DECLARE @InvoiceTransCd char(10)
DECLARE @ForfeitRevType T_chRevTypeCd
DECLARE @rc int
DECLARE @DueDate datetime
DECLARE @TmpDate datetime
DECLARE @UnallocTotal money
DECLARE @ForfTotal money
DECLARE @InvoiceTotal money
DECLARE @TotalPmt money
DECLARE @UnallocAmt money
DECLARE @PaymentFlag bit
DECLARE @PaySeqTrFrom int
DECLARE @RowSeq int
DECLARE @InvTotal money
DECLARE @PayTotal money
DECLARE @InvPaid money
DECLARE @OrderRevNum int
DECLARE @InvoiceType char(2)
DECLARE @InvoiceAmountSite money
DECLARE @InvoiceAmountTriang money
DECLARE @OrderDummy money
DECLARE @MaxVersionId Integer /* Latest Version of payment terms for order line */
DECLARE @TaxInvoiceAmount money
DECLARE @TaxPercent decimal(5,2)
DECLARE @TotalPercent decimal(5,2)
DECLARE @PaymentAmountOrder money
DECLARE @PaymentAmountSite money
DECLARE @PaymentAmountTriang money
DECLARE @InvDemand char
DECLARE @PayStatus char
DECLARE @PayShowCd char(5)
DECLARE @PayShowYear T_iShowYear
DECLARE @PaidAmtTotal money
DECLARE @TotalExTax money
DECLARE @NewPaymentID int
DECLARE @MaxTaxRevNum int

SELECT @MaxVersionId = MAX(iVersionId)
FROM omOrderPaymentTerms
WHERE iOrderNumber = @OrderNumber
AND iOrderLineNumber = @OrderLineNumber

If @Debug = 1
Select 'starting now'

/* Temporarily hard-code language english 2/3/98 */

SELECT @LanguageCd = 1

/* get current date and user name */
SELECT @Today = getdate(),
@UserName = suser_sname()

IF @Deposit = 1
SELECT @UserName = 'IOD'

select @InvoiceType = CASE @AttnStat
WHEN ' ' then '1'
WHEN 'V' then '1'
WHEN 'U' then '21'
WHEN 'D' then '22'
WHEN 'C' then '19'
END

/* Get forfeiture revtypecode for determining if this is a forfeiture */
SELECT @ForfeitRevType = RT.chRevTypeCd
FROM omRevType RT
WHERE RT.bForfeitureFlag = 1

/* Get # of decimal places in currency */
SELECT @CurrRound = iNoDecimalDigits
FROM omCurrency
WHERE chCurrencyCd = @CurrencyCd

/* Get chSite */
SELECT @Site = chSite,
@ProductCd = chShowGL,
@InvThreshold = isnull(mInvoiceThreshold,0)
FROM omShow
WHERE chShowCd = @ShowCd
AND iShowYear = @ShowYear

/* Get payment percent */
SELECT @PaymentPercent = (dPaymentPercent / 100),
@DueDate = dtTransactionDate
FROM omOrderPaymentTerms
WHERE iOrderNumber = @OrderNumber
AND iOrderLineNumber = @OrderLineNumber
AND iVersionId = @MaxVersionId
AND iScheduleId = @ScheduleId

select @DueDate = isnull(@DueDate, @Today)

SELECT @PrevPaymentPercent = max(dPaymentPercent)/100
FROM omOrderPaymentTerms
WHERE iOrderNumber = @OrderNumber
AND iOrderLineNumber = @OrderLineNumber
AND iVersionId = @PaymentTermId
AND iScheduleID < @ScheduleId

SELECT @PaymentPercent = @PaymentPercent - isnull(@PrevPaymentPercent, 0)

IF datediff(dd,@DueDate, @Today) >= 0
SELECT @DueDate = @Today

/* get revtypecd and revitemcd */
SELECT @RevTypeCd = chRevTypeCd,
@RevItemCd = chRevItemCd
FROM omOrderDetail
WHERE iOrderNumber = @OrderNumber
AND iOrderLineNumber = @OrderLineNumber

/* get chGlcode for revtype */
SELECT @RTAccCode = SUBSTRING(chGlCode,1,6),
@RTDeptCode = SUBSTRING(chGlCode,7,5)
FROM omShowRevItems
WHERE chRevTypeCd = @RevTypeCd
AND chRevItemCd = @RevItemCd
AND chShowCd = @ShowCd
AND iShowYear = @ShowYear

IF @RTAccCode IS NULL OR LEN(RTRIM(@RTAccCode)) = 0
BEGIN

/* get chGlcode for revtype */
SELECT @RTAccCode = SUBSTRING(chGlCode,1,6),
@RTDeptCode = SUBSTRING(chGlCode,7,5)
FROM omRevType
WHERE chRevTypeCd = @RevTypeCd

END

/* get chGlcode for account recievables */
SELECT @InvoiceTransCd = chInvoiceTransCd
FROM omSite
WHERE chSite = @Site

IF (SELECT Ltrim(chARControlCd) FROM omShow
WHERE chShowCd = @ShowCd
AND iShowYear = @ShowYear) IS NULL OR
(SELECT LTRIM(chARControlCd) FROM omShow
WHERE chShowCd = @ShowCd
AND iShowYear = @ShowYear) = ''

BEGIN
SELECT @ARAccCode = SUBSTRING(chARControlCd,1,6),
@ARDeptCode = SUBSTRING(chARControlCd,7,5)
FROM omSite
WHERE chSite = @Site
END
ELSE
BEGIN
SELECT @ARAccCode = SUBSTRING(chARControlCd,1,6),
@ARDeptCode = SUBSTRING(chARControlCd,7,5)
FROM omShow
WHERE chShowCd = @ShowCd
AND iShowYear = @ShowYear
END

SELECT @UnallocTotal = isnull(sum(mPaidAmount), 0)
FROM caPaymentDetails PD,
caPaymentHeader PH
WHERE PD.chShowCd = @ShowCd and
PD.iShowYear = @ShowYear and
PD.iInvoiceNumber = 0 and
PD.iInvoiceLineNumber = 0 and
PD.iPaymentId = PH.iPaymentId and
PH.iOwnerId = @OwnerId and
PD.chStatus = 'A' and
PH.chPaymentStatus = 'A'

select @OrderRevNum = max(iOrderRevNum)
from omOrderDetailHistory
where iOrderNumber = @OrderNumber
and iOrderLineNumber = @OrderLineNumber

if @UnallocTotal > 0
SELECT @PaymentFlag = 1
else
SELECT @PaymentFlag = 0

/* for regular and forfeiture lines */

BEGIN TRANSACTION

/*set invoice status */
if @RevTypeCd = @ForfeitRevType
BEGIN
SELECT @InvoiceStatus = 'E'
SELECT @PaymentPercent = 1.0

/* This code was added for SIR 1435 - need to ignore payment terms for forfeitures
and make them 100% due immediately - MH 3/6/98 */
/* Delete all omPreInvoice lines for that order line - MH - 3/6/98 */
DELETE FROM omPreInvoice
WHERE iOrderNumber = @OrderNumber and
iOrderLineNumber = @OrderLineNumber and
iPaymentTermId = @PaymentTermId and
iScheduleId <> @ScheduleId

SELECT @PrevInvAmt = isnull(sum(mInvoiceAmount), 0)
FROM omInvoiceDetail
WHERE iOrderNumber = @OrderNumber
AND iOrderLineNumber = @OrderLineNumber

SELECT @ForfTotal = mTotalPrice
FROM omOrderDetail
WHERE iOrderNumber = @OrderNumber and
iOrderLineNumber = @OrderLineNumber

if @PrevInvAmt = 0
BEGIN

select @InvoiceAmount = mBasePrice + mAdjTotal
from omOrderDetail
where iOrderNumber = @OrderNumber
and iOrderLineNumber = @OrderLineNumber

GOTO create_invoice

END

ELSE
BEGIN
select @PaymentPercent = 0
goto FORFEIT_END
END

IF @@ERROR <> 0
GOTO INSERT_ERR

END
ELSE
BEGIN
SELECT @InvoiceStatus = 'A'
END

/* Do not invoice cancelled orders */
if (select chOrderLineStatus
from omOrderDetail
where iOrderNumber = @OrderNumber
and iOrderLineNumber = @OrderLineNumber) > 'C'
begin
goto FORFEIT_END
end

/* If this is an invoice on demand, then calculate all lines
based on the payment amount */

/* Calculate invoice amount from IOD payment amount if there is only one
line, or this is > first invoice line for deposit */

If @Deposit = 1
Begin

Select @PaymentID = iPaymentID
From omPreinvoice pr
Where pr.iOrderNumber = @OrderNumber
AND pr.iOrderLineNumber = @OrderLineNumber
And pr.iScheduleID = @ScheduleID
And pr.iPaymentTermID = @PaymentTermID

End

If @Debug = 1
Select 'starting to do it now'

/* This will deal with rounding for the last payment, so we don't miss any cents */
IF (@PaymentPercent + isnull(@PrevPaymentPercent, 0)) = 1.0
Begin

select @PrevInvAmt = isnull(sum(mInvoiceAmount), 0)
from omInvoiceDetail
where iOrderNumber = @OrderNumber and
iOrderLineNumber = @OrderLineNumber and
isnull(iTaxId, 0) = 0

select @OrderTotal = isnull(mBasePrice, 0) + isnull(mAdjTotal, 0)
from omOrderDetail
where iOrderNumber = @OrderNumber and
iOrderLineNumber = @OrderLineNumber

select @InvoiceAmount = @OrderTotal - @PrevInvAmt

end

ELSE
BEGIN

SELECT @InvoiceAmount = isnull(((isnull(mBasePrice,0) + isnull(mAdjTotal,0)) * @PaymentPercent), 0)
FROM omOrderDetail
WHERE iOrderNumber = @OrderNumber
AND iOrderLineNumber = @OrderLineNumber

SELECT @InvoiceAmount = round(@InvoiceAmount, @CurrRound)

END

create_invoice:

IF @InvoiceAmount > 0
BEGIN /* Invoice amount is > 0 */

IF @InvHdr = 'N'
BEGIN /* Create new invoice header */

SELECT @NextVal = ISNULL(MAX(iInvoiceNumber),0) + 1
FROM omInvoiceHeader

/* insert invoice header */
INSERT omInvoiceHeader
(iInvoiceNumber,
dtDueDate,
chStatus,
chInsertBy,
dtInsertDate,
chInvoiceType)
VALUES
(@NextVal,
@DueDate,
@InvoiceStatus,
@UserName,
@Today,
@InvoiceType)

IF @@ERROR <> 0
GOTO INSERT_ERR

SELECT @InvoiceNumber = @NextVal
SELECT @InvoiceLineNumber = 1

END /* New Invoice Header */
ELSE
BEGIN /* No new inv header */

SELECT @InvoiceNumber = MAX(IH.iInvoiceNumber)
FROM omInvoiceHeader IH,
omOrderHeader OH,
omInvoiceDetail ID
WHERE OH.iOrderNumber = ID.iOrderNumber
AND ID.iInvoiceNumber = IH.iInvoiceNumber
AND OH.iOwnerId = @OwnerId
AND OH.chShowCd = @ShowCd
AND OH.iShowYear = @ShowYear
AND OH.chCurrencyCd = @CurrencyCd
AND isnull(ISNULL(OH.iBillerId,OH.iSignerId), OH.iOwnerId)= @IndivId
/*** Begin Remove - CSD Log #4481 - Fiaz Ali - 10 Feb 2003 - Remove the check for existing invoice header on the same day only. ***/
-- AND DATEPART(year,IH.dtInsertDate) = DATEPART(year,getdate())
-- AND DATEPART(month,IH.dtInsertDate) = DATEPART(month,getdate())
-- AND DATEPART(day,IH.dtInsertDate) = DATEPART(day,getdate())
/*** End Remove - CSD Log #4481 - Fiaz Ali - 10 Feb 2003 - Remove the check for existing invoice header on the same day only. ***/

SELECT @InvoiceLineNumber = MAX(iInvoiceLineNumber) + 1
FROM omInvoiceDetail
WHERE iInvoiceNumber = @InvoiceNumber

END /* No new inv header */

/* Clean up any invoice line number 0 for this invoice # */
if exists (select iInvoiceLineNumber
from omInvoiceDetail
where iInvoiceNumber = @InvoiceNumber and
iInvoiceLineNumber = 0)
BEGIN

delete from omInvoiceDetail
where iInvoiceNumber = @InvoiceNumber and
iInvoiceLineNumber = 0

if @@error <> 0
GOTO INSERT_ERR

END

execute @rc = prSgConvertCurrency @ShowCd,
@ShowYear,
@CurrencyCd,
@InvoiceAmount,
@CurrencyCd,
@OrderDummy OUTPUT,
@InvoiceAmountSite OUTPUT,
@InvoiceAmountTriang OUTPUT

SELECT @InvoiceTotal = isnull(@InvoiceTotal,0) + @InvoiceAmount

IF @Deposit = 1
Begin

/* insert invoice detail */
INSERT omInvoiceDetail
(iInvoiceNumber,
iInvoiceLineNumber,
iOrderNumber,
iOrderLineNumber,
mInvoiceAmount,
mInvoiceAmountSite,
mInvoiceAmountTriang,
mBalanceAmount,
mBalanceAmountSite,
mBalanceAmountTriang,
bARTransferFlag,
iPaymentTermId,
iScheduleId,
iOrderRevNum,
chInvDemand,
iTaxRevNum)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@OrderNumber,
@OrderLineNumber,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
0,
@PaymentTermId,
@ScheduleId,
@OrderRevNum,
'Y',
0)

If @Debug = 1
select 'created invoice detail line'

End
Else
Begin

/* insert invoice detail */
INSERT omInvoiceDetail
(iInvoiceNumber,
iInvoiceLineNumber,
iOrderNumber,
iOrderLineNumber,
mInvoiceAmount,
mInvoiceAmountSite,
mInvoiceAmountTriang,
mBalanceAmount,
mBalanceAmountSite,
mBalanceAmountTriang,
bARTransferFlag,
iPaymentTermId,
iScheduleId,
iOrderRevNum,
chInvDemand,
iTaxRevNum)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@OrderNumber,
@OrderLineNumber,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
0,
@PaymentTermId,
@ScheduleId,
@OrderRevNum,
null,
0)

END

IF @@ERROR <> 0
GOTO INSERT_ERR

/* insert invoiceGL for revtypecd */
INSERT inInvoiceGL
(iInvoiceNumber,
iInvoiceLineNumber,
chAccountCd,
chShowCd,
iShowYear,
chSite,
chCurrencyCd,
chTransCd,
chDepartmentCd,
chProductCd,
mTransactionAmt,
mTransactionAmtSite,
mTransactionAmtTriang,
dtTransactionDate,
chInsertBy,
dtInsertDate)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@RTAccCode,
@ShowCd,
@ShowYear,
@Site,
@CurrencyCd,
@InvoiceTransCd,
@RTDeptCode,
@ProductCd,
-1 * @InvoiceAmount,
-1 * @InvoiceAmountSite,
-1 * @InvoiceAmountTriang,
@Today,
@UserName,
@Today)

IF @@ERROR <> 0
GOTO INSERT_ERR

/* insert invoiceGL for accounts receivables */
INSERT inInvoiceGL
(iInvoiceNumber,
iInvoiceLineNumber,
chAccountCd,
chShowCd,
iShowYear,
chSite,
chCurrencyCd,
chTransCd,
chDepartmentCd,
chProductCd,
mTransactionAmt,
mTransactionAmtSite,
mTransactionAmtTriang,
dtTransactionDate,
chInsertBy,
dtInsertDate)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@ARAccCode,
@ShowCd,
@ShowYear,
@Site,
@CurrencyCd,
@InvoiceTransCd,
@ARDeptCode,
@ProductCd,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
@Today,
@UserName,
@Today)

IF @@ERROR <> 0
GOTO INSERT_ERR

IF EXISTS(SELECT iOwnerId
FROM caAccountInfo
WHERE iOwnerId = @OwnerId AND
chShowCd = @ShowCd AND
iShowYear = @ShowYear )
BEGIN

UPDATE caAccountInfo
SET mTotalInvoice = isnull(mTotalInvoice,0) + isnull(@InvoiceAmount,0),
mTotalInvoiceSite = isnull(mTotalInvoiceSite,0) + isnull(@InvoiceAmountSite,0),
mTotalInvoiceTriang = isnull(mTotalInvoiceTriang,0) + isnull(@InvoiceAmountTriang,0)
WHERE iOwnerId = @OwnerId
AND chShowCd = @ShowCd
AND iShowYear = @ShowYear

END
ELSE
BEGIN

INSERT caAccountInfo
(iOwnerId,
chShowCd,
iShowYear,
mTotalInvoice,
mTotalInvoiceSite,
mTotalInvoiceTriang,
mTotalPaymentRecvd,
mTotalPaymentRecvdSite,
mTotalPaymentRecvdTriang,
mTotalUnallocated,
mTotalUnallocatedSite,
mTotalUnallocatedTriang)
VALUES
(@OwnerId,
@ShowCd,
@ShowYear,
isnull(@InvoiceAmount,0),
isnull(@InvoiceAmountSite,0),
isnull(@InvoiceAmountTriang,0),
0,
0,
0,
0,
0,
0)

END

IF @@ERROR <> 0
GOTO INSERT_ERR

/* Now, create payment detail line for this invoice, and set payment header to 'applied'
If the invoice is and IOD */

If @Debug = 1
Select 'Now paying off invoice'

If @Deposit = 1
Begin

If @Debug = 1
Select 'Paying - ', @ShowCd, @ShowYear, @OwnerId,
@InvoiceNumber, @InvoiceLineNumber, @PaymentID

If @Debug = 1
select 'Payed off invoice'

End
Else
Begin

if @PaymentFlag = 1
Begin /* Pay Invoice */

exec @rc = prSuPayInvoice
@ShowCd,
@ShowYear,
@OwnerId,
@InvoiceNumber,
@InvoiceLineNumber

if @rc <> 0
goto INSERT_ERR

End /* Pay Invoice */

End

If @Debug = 1
select 'starting taxes'

/* Taxes */
DECLARE tax_cursor CURSOR FOR
SELECT ODT.mTaxAmount,
ODT.iTaxId,
ST.chGLCode
FROM omOrderDetailTaxes ODT,
omSiteTaxes ST
WHERE ODT.iOrderNumber = @OrderNumber
AND ODT.iOrderLineNumber = @OrderLineNumber
AND ODT.chRevTypeCd = @RevTypeCd
AND ODT.chRevItemCd = @RevItemCd
AND ODT.iTaxId = ST.iTaxId
AND ST.chSite = @Site
AND ST.iRevNum = ODT.iRevNum
AND ODT.iRevNum = (Select max(iRevNum) From omOrderDetailTaxes ODT2
Where iOrderNumber = @OrderNumber
And iOrderLineNumber = @OrderLineNumber
And iTaxID = ODT.iTaxID)

OPEN tax_cursor

FETCH NEXT
FROM tax_cursor
INTO @InvoiceAmount, @TaxId, @GLCode

WHILE @@FETCH_STATUS = 0
BEGIN /* Tax loop */

SELECT @InvoiceLineNumber = @InvoiceLineNumber + 1

Select @MaxTaxRevNum = max(iRevNum)
From omSiteTaxes
Where iTaxID = @TaxID

/* This will deal with rounding for the last payment, so we don't miss any cents */
if @PaymentPercent <> 1.0 and (@PaymentPercent + isnull(@PrevPaymentPercent, 0)) = 1.0
Begin

select @PrevInvAmt = isnull(sum(mInvoiceAmount), 0)
from omInvoiceDetail
where iOrderNumber = @OrderNumber and
iOrderLineNumber = @OrderLineNumber and
isnull(iTaxId, 0) = @TaxId

select @OrderTotal = @InvoiceAmount

select @InvoiceAmount = @OrderTotal - @PrevInvAmt

End
else
BEGIN

SELECT @InvoiceAmount = (@InvoiceAmount * @PaymentPercent)
SELECT @InvoiceAmount = round(@InvoiceAmount, @CurrRound)

END

if @InvoiceAmount > 0
BEGIN /* Invoiceamount > 0 taxes */

execute @rc = prSgConvertCurrency
@ShowCd,
@ShowYear,
@CurrencyCd,
@InvoiceAmount,
@CurrencyCd,
@OrderDummy OUTPUT,
@InvoiceAmountSite OUTPUT,
@InvoiceAmountTriang OUTPUT

SELECT @InvoiceTotal = isnull(@InvoiceTotal,0) + @InvoiceAmount

IF @Deposit = 1
BEGIN

--Select @InvDemand = 'Y'
INSERT omInvoiceDetail
(iInvoiceNumber,
iInvoiceLineNumber,
iOrderNumber,
iOrderLineNumber,
mInvoiceAmount,
mInvoiceAmountSite,
mInvoiceAmountTriang,
mBalanceAmount,
mBalanceAmountSite,
mBalanceAmountTriang,
bARTransferFlag,
iPaymentTermId,
iScheduleId,
iTaxId,
iOrderRevNum,
chInvDemand,
iTaxRevNum)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@OrderNumber,
@OrderLineNumber,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
0,
@PaymentTermId,
@ScheduleId,
@TaxId,
@OrderRevNum,
'Y',
@MaxTaxRevNum)

END
ELSE
BEGIN

/* insert invoice detail */
INSERT omInvoiceDetail
(iInvoiceNumber,
iInvoiceLineNumber,
iOrderNumber,
iOrderLineNumber,
mInvoiceAmount,
mInvoiceAmountSite,
mInvoiceAmountTriang,
mBalanceAmount,
mBalanceAmountSite,
mBalanceAmountTriang,
bARTransferFlag,
iPaymentTermId,
iScheduleId,
iTaxId,
iOrderRevNum,
iTaxRevNum)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@OrderNumber,
@OrderLineNumber,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
0,
@PaymentTermId,
@ScheduleId,
@TaxId,
@OrderRevNum,
@MaxTaxRevNum)

End

IF @@ERROR <> 0
GOTO INSERT_ERR

/* insert invoiceGL for revtypecd */
INSERT inInvoiceGL
(iInvoiceNumber,
iInvoiceLineNumber,
chAccountCd,
chShowCd,
iShowYear,
chSite,
chCurrencyCd,
chTransCd,
chDepartmentCd,
chProductCd,
mTransactionAmt,
mTransactionAmtSite,
mTransactionAmtTriang,
dtTransactionDate,
chInsertBy,
dtInsertDate)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
SUBSTRING(@GLCode,1,6),
@ShowCd,
@ShowYear,
@Site,
@CurrencyCd,
@InvoiceTransCd,
SUBSTRING(@GLCode,7,5),
@ProductCd,
-1 * @InvoiceAmount,
-1 * @InvoiceAmountSite,
-1 * @InvoiceAmountTriang,
@Today,
@UserName,
@Today)

IF @@ERROR <> 0
GOTO INSERT_ERR

/* insert invoiceGL for accounts receivables */
INSERT inInvoiceGL
(iInvoiceNumber,
iInvoiceLineNumber,
chAccountCd,
chShowCd,
iShowYear,
chSite,
chCurrencyCd,
chTransCd,
chDepartmentCd,
chProductCd,
mTransactionAmt,
mTransactionAmtSite,
mTransactionAmtTriang,
dtTransactionDate,
chInsertBy,
dtInsertDate)
VALUES
(@InvoiceNumber,
@InvoiceLineNumber,
@ARAccCode,
@ShowCd,
@ShowYear,
@Site,
@CurrencyCd,
@InvoiceTransCd,
@ARDeptCode,
@ProductCd,
@InvoiceAmount,
@InvoiceAmountSite,
@InvoiceAmountTriang,
@Today,
@UserName,
@Today)

IF EXISTS(SELECT iOwnerId
FROM caAccountInfo
WHERE iOwnerId = @OwnerId AND
chShowCd = @ShowCd AND
iShowYear = @ShowYear )
BEGIN

UPDATE caAccountInfo
SET mTotalInvoice = isnull(mTotalInvoice,0) + isnull(@InvoiceAmount,0),
mTotalInvoiceSite = isnull(mTotalInvoiceSite,0) + isnull(@InvoiceAmountSite,0),
mTotalInvoiceTriang = isnull(mTotalInvoiceTriang,0) + isnull(@InvoiceAmountTriang,0)
WHERE iOwnerId = @OwnerId
AND chShowCd = @ShowCd
AND iShowYear = @ShowYear

END
ELSE
BEGIN

INSERT caAccountInfo
(iOwnerId,
chShowCd,
iShowYear,
mTotalInvoice,
mTotalInvoiceSite,
mTotalInvoiceTriang,
mTotalPaymentRecvd,
mTotalPaymentRecvdSite,
mTotalPaymentRecvdTriang,
mTotalUnallocated,
mTotalUnallocatedSite,
mTotalUnallocatedTriang)
VALUES
(@OwnerId,
@ShowCd,
@ShowYear,
isnull(@InvoiceAmount,0),
isnull(@InvoiceAmountSite,0),
isnull(@InvoiceAmountTriang,0),
0,
0,
0,
0,
0,
0)

END

IF @@ERROR <> 0
BEGIN
CLOSE tax_cursor
DEALLOCATE tax_cursor
GOTO INSERT_ERR
END

/* Now, pay off Tax FOR IOD */
If @Deposit = 1
Begin

If @Debug = 1
select 'inserted tax IOD ok'

End
else

if @PaymentFlag = 1
begin /* Pay Invoice */

exec @rc = prSuPayInvoice
@ShowCd,
@ShowYear,
@OwnerId,
@InvoiceNumber,
@InvoiceLineNumber

if @rc <> 0
goto INSERT_ERR

end /* Pay Invoice */

END /* Invoiceamount > 0 taxes */

FETCH NEXT
FROM tax_cursor
INTO @InvoiceAmount, @TaxId, @GLCode

END /* Tax loop */

CLOSE tax_cursor
DEALLOCATE tax_cursor

END /* If invoiceamount > 0 */
ELSE IF (isnull(@InvoiceAmount, 0) <= 0 and @InvHdr = 'N' and @PrintInv = 'N')
BEGIN /* New invoice header */

/* This was added to handle invoices of zero amount, in the case that the next
omPreInvoice line to be processed is intended to be on the same invoice. The
invoice handler will be looking for an invoice number for the next line, and now
it will find the one we're inserting, and delete the invoice detail line of $0 amount.
*/

SELECT @NextVal = ISNULL(MAX(iInvoiceNumber),0) + 1
FROM omInvoiceHeader

/* insert invoice header */
INSERT omInvoiceHeader
(iInvoiceNumber,
dtDueDate,
chStatus,
chInsertBy,
dtInsertDate,
chInvoiceType)
VALUES
(@NextVal,
@DueDate,
'A',
@UserName,
@Today,
@InvoiceType)

IF @@ERROR <> 0
GOTO INSERT_ERR

/* insert invoice detail */

INSERT omInvoiceDetail
(iInvoiceNumber,
iInvoiceLineNumber,
iOrderNumber,
iOrderLineNumber,
mInvoiceAmount,
mBalanceAmount,
bARTransferFlag,
iPaymentTermId,
iScheduleId,
iOrderRevNum,
iTaxRevNum)
VALUES
(@NextVal,
0,
@OrderNumber,
@OrderLineNumber,
0,
0,
0,
@PaymentTermId,
@ScheduleId,
@OrderRevNum,
0)

END /* new invoice header */
ELSE IF (isnull(@InvoiceAmount, 0) <= 0 and @InvHdr = 'Y' and @PrintInv = 'Y') /* Cleanup */
BEGIN

SELECT @InvoiceNumber = MAX(IH.iInvoiceNumber)
FROM omInvoiceHeader IH,
omOrderHeader OH,
omInvoiceDetail ID
WHERE OH.iOrderNumber = @OrderNumber
AND ID.iOrderNumber = OH.iOrdernumber
AND ID.iInvoiceNumber = IH.iInvoiceNumber
AND OH.iOwnerId = @OwnerId
AND OH.chShowCd = @ShowCd
AND OH.iShowYear = @ShowYear
AND OH.chCurrencyCd = @CurrencyCd
AND isnull(ISNULL(OH.iBillerId,OH.iSignerId), OH.iOwnerId)= @IndivId
AND DATEPART(year,IH.dtInsertDate) = DATEPART(year,getdate())
AND DATEPART(month,IH.dtInsertDate) = DATEPART(month,getdate())
AND DATEPART(day,IH.dtInsertDate) = DATEPART(day,getdate())

/* Clean up any invoice line number 0 for this invoice # */
if exists (select iInvoiceLineNumber
from omInvoiceDetail
where iInvoiceNumber = @InvoiceNumber and
iInvoiceLineNumber = 0)
BEGIN

delete from omInvoiceDetail
where iInvoiceNumber = @InvoiceNumber and
iInvoiceLineNumber = 0

if @@error <> 0
GOTO INSERT_ERR

delete from omInvoiceHeader
where iInvoiceNumber = @InvoiceNumber

if @@error <> 0
GOTO INSERT_ERR

END

END /* Cleanup */

If @PrintInv = 'Y' and @Deposit = 1
Begin

If @Debug = 1
Begin
Select 'Pre paying ', @InvoiceNumber, @PaymentID, @Debug
Select * from omInvoicedetail where iInvoicenumber = @InvoiceNumber
select * from capaymentdetails where ipaymentid = @PaymentID
End

/* If last invoice was ordinary, try and pay off */
If (Select chInvoiceType
From omInvoiceHeader
where iInvoiceNumber = @InvoiceNumber) in ('1','21')
Begin

If exists(select *
From caPaymentDetails
Where iPaymentID = @PaymentID
and chStatus = 'T')

Select @PaymentID = PD2.iPaymentID
From caPaymentDetails PD, caPaymentTransfer PT, caPaymentdetails PD2
Where PD.iPaymentID = @PaymentID
And PD.iPaymtDetailSeqno = PT.iPaymtTransferFrom
And PD2.iPaymentID = PT.iPaymentID

Execute @rc = prsuPayWholeInvoice @InvoiceNumber, @PaymentID, @Debug

/*** Begin Modify CSD Log #4425 - Fiaz Ali - 15 Jan 2003 - Return from SP without closing transaction ***/
-- We do not need to verify the outcome of the previous SP as it will rollback the child transaction if
-- matching payments are not found anyway. Invoice should still be created. Code commented out.
/***
IF @rc < 0
return -1
***/
/*** End Modify CSD Log #4425 - Fiaz Ali - 15 Jan 2003 - Return from SP without closing transaction ***/

End

End

/* Doc type depends upon invoice type */
select @DocumentType = R.iParameterId
from reference R,
reference_fields RF
where RF.chFieldName = 'prism.DocumentTypes'
and R.iReferenceId = RF.iReferenceId
and R.vchCharacterCode = @InvoiceType

/* Print only when ready to be printed */
if @PrintInv = 'Y'
BEGIN /* PrintInv Y */

if @PrintMode = 'A' OR @PrintMode = 'B'
BEGIN /* @PrintMode */

create table #InvOrder (iRowSeq int not null identity,
iOrderNumber int not null,
iOrderLineNumber int not null)

insert #InvOrder
select distinct iOrderNumber,
iOrderLineNumber
from omInvoiceDetail
where iInvoiceNumber = @InvoiceNumber

select @Count = 1

select @RowSeq = max(iRowSeq)
from #InvOrder

while @Count <= @RowSeq
BEGIN

select @OrderNumber = iOrderNumber,
@OrderLineNumber = iOrderLineNumber
from #InvOrder
where iRowSeq = @Count

/* Get Total Invoiced on the order lines on this invoice */
select @InvTotal = isnull(@InvTotal, 0) + isnull(sum(ID.mInvoiceAmount), 0)
from omInvoiceDetail ID
where ID.iOrderNumber = @OrderNumber
and ID.iOrderLineNumber = @OrderLineNumber

/* Get Total Paid on the order lines on this invoice */
select @PayTotal = isnull(@PayTotal, 0) + isnull(sum(PD.mPaidAmount),0)
from caPaymentDetails PD,
omInvoiceDetail ID
where ID.iOrderNumber = @OrderNumber
and ID.iOrderLineNumber = @OrderLineNumber
and PD.iInvoiceNumber = ID.iInvoiceNumber
and PD.iInvoiceLineNumber = ID.iInvoiceLineNumber
and PD.chStatus = 'A'

select @Count = @Count + 1

END

select @PayTotal = isnull(@PayTotal, 0) + isnull(sum(PD.mPaidAmount),0)
from caPaymentDetails PD,
caPaymentHeader PH
where PH.iOwnerId = @OwnerId
and PD.iPaymentId = PH.iPaymentId
and PD.iInvoiceNumber = 0
and PD.iInvoiceLineNumber = 0
and PD.chShowCd = @ShowCd
and PD.iShowYear = @ShowYear
and PD.chStatus = 'A'
and PH.chPaymentStatus = 'A'

select @InvoiceAmount = @InvTotal - @PayTotal

if (@InvoiceAmount >= @InvThreshold)
BEGIN /* InvoiceAmount */

if @PrintMode = 'A'
SELECT @BatchInd = CONVERT(bit,'1')
else
SELECT @BatchInd = CONVERT(bit,'0')

EXECUTE @rc =prsiDocumentRequest @Today,
@UserName,
@BatchInd,
@DocumentType,
@LanguageCd,
@ShowCd,
@ShowYear,
@IndivId,
@AddrTypeId,
NULL,
@InvoiceNumber,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
@PrinterName,
@PortName,
@DriverName,
@FaxNo

/*** Begin Modify CSD Log #4425 - Fiaz Ali - 15 Jan 2003 - Return from SP without closing transaction ***/
-- We do not need to verify the outcome of the previous SP as it will rollback the child transaction if
-- the request was not inserted. Invoice should still be created as it is better to be able to reprint
-- invoices than to re-create invoice lines. Code commented out.
/***
IF @rc < 0
return -1
***/
/*** End Modify CSD Log #4425 - Fiaz Ali - 15 Jan 2003 - Return from SP without closing transaction ***/

if isnull(@CopyToId, 0) > 0 /* This is a COPY */
BEGIN

EXECUTE @rc =prsiDocumentRequest @Today,
@UserName,
@BatchInd,
@DocumentType,
@LanguageCd,
@ShowCd,
@ShowYear,
@CopyToId,
@CopyAddrTypeId,
NULL,
@InvoiceNumber,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
@PrinterName,
@PortName,
@DriverName,
@FaxNo,
1 /* Copy to flag */

/*** Begin Modify CSD Log #4425 - Fiaz Ali - 15 Jan 2003 - Return from SP without closing transaction ***/
-- We do not need to verify the outcome of the previous SP as it will rollback the child transaction if
-- the request was not inserted. Invoice should still be created as it is better to be able to reprint
-- invoices than to re-create invoice lines. Code commented out.
/***
IF @rc < 0
return -1
***/
/*** End Modify CSD Log #4425 - Fiaz Ali - 15 Jan 2003 - Return from SP without closing transaction ***/

END

END /* InvoiceAmount */

END /* @PrintMode */

END /* PrintInv = Y */

FORFEIT_END:
commit transaction
return 0

INSERT_ERR:
Rollback transaction
raiserror (50002,1,1)
return -1

END

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-07 : 13:22:51

In general using variables for values forces procedural code whereas relational databases are optimised for set based operations.
Use a temp table and do as much processing as you can in a single statement.
Get rid of the cursor also - don't know how long that takes but is a strong candidate.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-07 : 13:44:38

What nr said....

That has got to be the largest stored proc I've ever seen!!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-08 : 09:28:38

Well....modular is a word that doesn't come to mind. I know the way it is with code (if it aint broke...), but this is doing a lot of things...Inserts, updates, ect. Also there are many different accesses to data...you set a local variable to a value from a table, then go back again...why?

If you can re-work it, take Nigels advice

I swear though, I feel like I'm looking at a early 80's cobol batch program.

How many places is this spoc called from? If it's not too many maybe it can be re-tooled. It seems very specific. I wouldn't imagine it being called from many locations.

Your cursors have to be the highest overhead though...but I just did a count of all the keywords of "FROM" (ie datta access) and there are 56 of them! I doubt looking at the query plan will help. I would suggest you need to limit the number of accesses.

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-08 : 20:41:58

quote:
I swear though, I feel like I'm looking at a early 80's cobol batch program.

I doubt that any 80's era computer had enough RAM to run a program that big.

miranwar
Posting Yak Master

125 Posts

Posted - 2003-04-09 : 04:06:18

Hey Thanks for your help doods....I swear this code aint mine!!!...Unfortunately Its something i have to support. Anyway I think I have found the problem for some reason all stats in the database have been removed. Can you tell me how I can recreate the stats for all indexes in the database and how they might of gone missing.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources