Need seperate company Invoice ID

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

General SQL Server Forums

New to SQL Server Programming

Need seperate company Invoice ID

Author

Topic

obaids
Starting Member

2 Posts

Posted - 2009-08-14 : 23:46:20

we had ID's issue I need TVPL, OSPL, UIB, & UC Seperate Invoices
we have a problem in TVPL invoices.UIB & UC are not mention in this script please correct this script.
when we submit TVPL new invoice and preview ,it appears ospl survey ID's in TVPL invoice preview
pleae help me
thanx ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[eisp_Oceanic_Billing_InvoiceMaster_Insert]
@MasterDoc AS VARCHAR(8000),
@ReturnID AS INT OUTPUT
AS

BEGIN

DECLARE @rowContent varchar(8000)
DECLARE @colContent varchar(200)
DECLARE @tokenContent varchar(150)
DECLARE @colCounter int
DECLARE @tokenPos int

DECLARE @Date AS VARCHAR(50)
DECLARE @Time AS VARCHAR(50)
DECLARE @CurrencyId AS VARCHAR(50)
DECLARE @Description AS VARCHAR(200)
DECLARE @InvoiceDetail AS VARCHAR(200)
DECLARE @InvoiceForId AS VARCHAR(50)
DECLARE @BranchId AS VARCHAR(50)
Declare @ChildClientID as Varchar(50)
Declare @InvoiceStatusID as Varchar(50)
Declare @DepartmentID as Varchar(50)

DECLARE @Reference AS VARCHAR(200)
DECLARE @CurrencyRate AS float

Set @rowContent = dbo.fn_GetRowFromQuery(@MasterDoc, 1)

Set @colCounter = 0
While (@colCounter < 13)
BEGIN
print @colCounter
SET @colCounter = @colCounter + 1
SET @colContent = dbo.fn_GetFieldFromRow(@rowContent, @colCounter)
SET @tokenPos = charindex('=',@colContent)
SET @tokenContent = substring(@colContent, 1, @tokenPos - 1)

print Upper(LTRIM(RTRIM(@tokenContent)))

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'ChildClientID')
BEGIN
SET @ChildClientID = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @ChildClientID = ''
SET @ChildClientID = NULL
print @ChildClientID
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'Date')
BEGIN
SET @Date = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @Date = ''
SET @Date = NULL
print @Date
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'DepartmentID')
BEGIN
SET @DepartmentID = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @DepartmentID = ''
SET @DepartmentID = NULL
print @DepartmentID
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'Time')
BEGIN
SET @Time = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @Time = ''
SET @Time = NULL
print @Time
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'InvoiceForId')
BEGIN
SET @InvoiceForId = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @InvoiceForId = ''
SET @InvoiceForId = NULL
print @InvoiceForId
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'CurrencyRate')
BEGIN
SET @CurrencyRate = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @CurrencyRate = ''
SET @CurrencyRate = NULL
print @CurrencyRate
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'Reference')
BEGIN
SET @Reference = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @Reference = ''
SET @Reference= NULL
print @Reference
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'Description')
BEGIN
SET @Description = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @Description = ''
SET @Description = NULL
print @Description
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'InvoiceStatusID')
BEGIN
SET @InvoiceStatusID = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @InvoiceStatusID = ''
SET @InvoiceStatusID = NULL
print @InvoiceStatusID
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'BranchId')
BEGIN
SET @BranchId = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @BranchId = ''
SET @BranchId = NULL
print @BranchId
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'CurrencyId')
BEGIN
SET @CurrencyId = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @CurrencyId = ''
SET @CurrencyId = NULL
END

IF (Upper(LTRIM(RTRIM(@tokenContent))) = 'InvoiceDetail')
BEGIN
SET @InvoiceDetail = substring(@colContent, @tokenPos + 1, len(@colContent) - @tokenPos)
IF @InvoiceDetail = ''
SET @InvoiceDetail = NULL
END
END

Declare @DepartmentPrefix as varchar(20)
Declare @InvoiceForPrefix as varchar(20)
Declare @ParentClientCode as varchar(20)
Declare @InvoiceCode as varchar(20)
Declare @InvId as int

set @InvId = 1
set @DepartmentPrefix = (select description from Oceanic_Department where DepartmentId = cast(@DepartmentID as varchar(20)))
set @InvoiceForPrefix = (select InvoiceForText from Oceanic_SurveyManagement_InvoiceFor where InvoiceForId = CAST(@InvoiceForId AS varchar(20)))
set @ParentClientCode = (select ParentClientId from dbo.Oceanic_Setup_ChildClient where ChildClientId = Cast(@ChildClientID as varchar(20)))
print 'sadrick'
print @InvId

IF (@CurrencyId = '8')
BEGIN
print 'IF'
if (@InvoiceForId = '4')
Begin
print 'OSPL'
set @InvId = (Select top 1 invoiceid from Oceanic_Billing_InvoiceMaster where InvoiceForId = 4 order by invoiceid desc)
if (@InvId <> '')
Begin
set @InvId = @InvId + 1
End
else
Begin
set @InvId = 1
End
print @InvId
End
Else
Begin
print 'TVPL'
set @InvId = (Select top 1 invoiceid from Oceanic_Billing_InvoiceMaster where InvoiceForId = 5 order by invoiceid desc)
if (@InvId <> '')
Begin
set @InvId = @InvId + 1
End
else
Begin
set @InvId = 1
End
print @InvId
End
END
ELSE
BEGIN
print 'ELSE'
if (@InvoiceForId = '4')
Begin
print 'OSPL'
set @InvId = (Select top 1 invoiceid from Oceanic_Billing_InvoiceMaster where InvoiceForId = 4 and CurrencyId <> '8' order by invoiceid desc)
if (@InvId <> '')
Begin
set @InvId = @InvId + 1
End
else
Begin
set @InvId = 1
End
print @InvId
End
Else
Begin
print 'TVPL'
set @InvId = (Select top 1 invoiceid from Oceanic_Billing_InvoiceMaster where InvoiceForId = 5 and CurrencyId <> '8' order by invoiceid desc)
if (@InvId <> '')
Begin
set @InvId = @InvId + 1
End
else
Begin
set @InvId = 1
End
print @InvId
End
END

print 'Invoice Code'

set @InvoiceCode = @InvoiceForPrefix + '-' + @DepartmentPrefix + '-' + cast(@InvId as varchar(20)) + '-INV-' + + substring(Cast(DATEPART(yy, Getdate())as varchar) ,3,2)
print @InvoiceCode
INSERT INTO Oceanic_Billing_InvoiceMaster
(
InvoiceId,
Date,
Time,
CurrencyId,
Description,
InvoiceForId,
BranchId,
Reference,
CurrencyRate,
ChildClientID,
InvoiceStatusID,
DepartmentID,
InvoiceCode,
InvoiceDetail
)
VALUES
(
@InvId,
CAST(@Date AS DATETIME),
CAST(@Time AS DATETIME),
CAST(@CurrencyId AS INT),
@Description,
CAST(@InvoiceForId AS INT),
CAST(@BranchId AS INT),
@Reference,
cast(@CurrencyRate as float),
Cast(@ChildClientID as numeric),
cast(@InvoiceStatusID as numeric),
cast(@DepartmentID as numeric(18,0)),
@InvoiceCode,
@InvoiceDetail
)
END

SET @ReturnID = @InvId

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 23:53:14

you need to provide more information like table structure, sample data and expected result.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:06:02

see this for guidelines on how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources