July 21, 2006 at 11:20 am
Hi
i want to enter data in invoice payment table.. payment type is cheque; cash and credit card depending on the user. i've writter followin procedure but if i use cheque; my application says u must enter data for credit card. but i don't want to do that. i just want to enter the data depending on my needs that's whether payment is cheque, cash or credit card
CREATE TABLE [Payment] (
[mobileNo] [bigint] NOT NULL ,
[customerId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[referenceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[payCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoicePaymentId] [int] NULL ,
[securityPaymentId] [int] NULL ,
[billPaymentId] [int] NULL ,
CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED
(
[mobileNo]
) ON [PRIMARY] ,
CONSTRAINT [FK_Payment_BillPayment] FOREIGN KEY
(
[billPaymentId]
) REFERENCES [BillPayment] (
[billpaymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Payment_InvoicePayment] FOREIGN KEY
(
[invoicePaymentId]
) REFERENCES [InvoicePayment] (
[invoicePaymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Payment_paymentTypes] FOREIGN KEY
(
[payCode]
) REFERENCES [paymentTypes] (
[payCode]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Payment_SecurityPayment] FOREIGN KEY
(
[securityPaymentId]
) REFERENCES [SecurityPayment] (
[securityPaymentId]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [paymentTypes] (
[payCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[paymentId] [int] NULL ,
CONSTRAINT [PK_paymentTypes] PRIMARY KEY CLUSTERED
(
[payCode]
) ON [PRIMARY] ,
CONSTRAINT [FK_paymentTypes_CashPayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [CashPayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_paymentTypes_ChequePayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [ChequePayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_paymentTypes_CreditCardPayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [CreditCardPayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [CashPayment] (
[paymentId] [int] NOT NULL ,
[cashPaymentDate] [datetime] NULL ,
[cashReceiptNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CashPayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ChequePayment] (
[paymentId] [int] NOT NULL ,
[locOut] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bankName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[branchName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ChequePayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CreditCardPayment] (
[paymentId] [int] NOT NULL ,
[expirationDate] [datetime] NULL ,
[cardName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[authorizationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CreditCardPayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [BillPayment] (
[billpaymentId] [int] NOT NULL ,
[billPaymentDate] [datetime] NULL ,
[newBillBalance] [decimal](18, 0) NULL ,
[billAmount] [decimal](18, 0) NULL ,
CONSTRAINT [PK_BillPayment] PRIMARY KEY CLUSTERED
(
[billpaymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [InvoicePayment] (
[invoicePaymentId] [int] NOT NULL ,
[invoiceBalance] [decimal](18, 0) NULL ,
[invoicePaymentDate] [datetime] NULL ,
[invoiceAmount] [decimal](18, 0) NULL ,
CONSTRAINT [PK_InvoicePayment] PRIMARY KEY CLUSTERED
(
[invoicePaymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SecurityPayment] (
[securityPaymentId] [int] NOT NULL ,
[currentDeposit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additionalDeposit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amount] [decimal](19, 0) NULL ,
[securityBalance] [decimal](18, 0) NULL ,
[securityDepositDate] [datetime] NULL ,
CONSTRAINT [PK_SecurityPayment] PRIMARY KEY CLUSTERED
(
[securityPaymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here it is Invoice Store Procedure:
CREATE PROCEDURE dbo.SP_InsertNewInvoice
@mobileNo bigInt,
@invoicePaymentId int,
@customerName nvarchar(50),
@invoiceAmount decimal,
@invoiceBalance decimal,
@invoicePaymentDate DateTime,
@payCode nvarchar(50),
@referenceNo nvarchar(50),
@locOut nvarchar(50),
@bankName nvarchar(50),
@branchName nvarchar(50),
@cardName nvarchar(50),
@expirationDate DateTime,
@authorizationCode nvarchar(50)
as
Begin
Declare @customerId nvarchar(50)
Select @customerId = customerId From Customer where customerName = @customerName AND customer.mobileNo = @mobileNo
INSERT INTO InvoicePayment(invoicePaymentId, invoiceAmount, invoiceBalance, invoicePaymentDate)
VALUES (@invoicePaymentId, @invoiceAmount, @invoiceBalance, @invoicePaymentDate)
INSERT INTO Payment(mobileNo, customerId, invoicePaymentId, payCode, referenceNo)
VALUES (@mobileNo, @customerId, @invoicePaymentId, @payCode, @referenceNo)
INSERT INTO paymentTypes(paymentId, payCode)
VALUES (@invoicePaymentId, @payCode)
If @payCode = 'Cheque'
INSERT INTO ChequePayment(paymentId, locOut, bankName, branchName)
VALUES(@invoicePaymentId, @locOut, @bankName, @branchName)
Else If @payCode = 'CreditCard'
INSERT INTO CreditCardPayment(paymentId, authorizationCode, expirationDate, cardName)
VALUES(@invoicePaymentId, @authorizationCode, @expirationDate, @cardName)
Else
INSERT INTO CashPayment(paymentId, cashReceiptNo)
VALUES(@invoicePaymentId, @referenceNo)
End
GO
July 24, 2006 at 8:00 am
This was removed by the editor as SPAM
July 26, 2006 at 7:28 pm
hi
Any one help mee ????
July 31, 2006 at 4:19 pm
Change the order of the insert statements. Note the paymentType insert is last.
If @payCode = 'Cheque'
INSERT INTO ChequePayment(paymentId, locOut, bankName, branchName)
VALUES(@invoicePaymentId, @locOut, @bankName, @branchName)
Else If @payCode = 'CreditCard'
INSERT INTO CreditCardPayment(paymentId, authorizationCode, expirationDate, cardName)
VALUES(@invoicePaymentId, @authorizationCode, @expirationDate, @cardName)
Else
INSERT INTO CashPayment(paymentId, cashReceiptNo)
VALUES(@invoicePaymentId, @referenceNo)
End
INSERT INTO paymentTypes(paymentId, payCode)
VALUES (@invoicePaymentId, @payCode)
Dan Pitta
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply