MSSQL Edit Script Needed
Hello All...
We're encountering errors in our Invoicing programs, that we need an EDIT to help us error trap & debug this problem with.
Currently, we have two related tables in MSSQL, namely:
PS_DOC_LIN
Primary Key:
[DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
[LIN_SEQ_NO] [dbo].[T_SEQ_NO] NOT NULL
PS_DOC_LIN_PRICE
Primary Key:
[DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
[LIN_SEQ_NO] [dbo].[T_SEQ_NO] NOT NULL,
[PRC_SEQ_NO] [dbo].[T_SMALLINT] NOT NULL
The DOC_NO and LIN_SEQ_NO for each record have to match...that's just the way the tables were designed.
What we're finding, is that in certain circumstances (very intermittent...hence, the need for this debug/edit list), there is *NO* corresponding record in the PS_DOC_LIN_PRICE table...and there *MUST* be one. So, what we need, and we've been banging our heads against the wall trying to find the right syntax to do this...is a script that simply creates a record in some EDIT table...where there is *NO* corresponding record...in the PS_DOC_LIN_PRICE table...any time a record is added to the PS_DOC_LIN table. Simple, simple. Then, we'll be in a better position, to help isolate these occurences.
In that "EDIT" table, we'll need the following fields:
DOC_ID (bigint)
LIN_SEQ_NO (int)
STR_ID (varchar(10))
STA_ID (varchar(10))
TKT_NO (varchar(15))
LIN_TYP (varchar(1))
PRC (money)
ITEM_NO (varchar(20))
CALC_PRC (money)
DESCR (varchar(50))
QTY_SOLD (decimal(15,4))
EXT_PRC (decimal(15,2))
PRC_1 (money)
ORIG_QTY (decimal(15,4))
QTY_SHIPPED (decimal(15,4))
We're not certain...if this needs to be a Trigger...or a Stored Proc (presumably a trigger?), but we need to have it pretty quickly...as our Invoicing software...is shipping out items...with a ZERO price...not good!
PLEASE HELP...we'd very much appreciate it!...Thanks!...Mark
We're encountering errors in our Invoicing programs, that we need an EDIT to help us error trap & debug this problem with.
Currently, we have two related tables in MSSQL, namely:
PS_DOC_LIN
Primary Key:
[DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
[LIN_SEQ_NO] [dbo].[T_SEQ_NO] NOT NULL
PS_DOC_LIN_PRICE
Primary Key:
[DOC_ID] [dbo].[T_DOC_ID] NOT NULL,
[LIN_SEQ_NO] [dbo].[T_SEQ_NO] NOT NULL,
[PRC_SEQ_NO] [dbo].[T_SMALLINT] NOT NULL
The DOC_NO and LIN_SEQ_NO for each record have to match...that's just the way the tables were designed.
What we're finding, is that in certain circumstances (very intermittent...hence, the need for this debug/edit list), there is *NO* corresponding record in the PS_DOC_LIN_PRICE table...and there *MUST* be one. So, what we need, and we've been banging our heads against the wall trying to find the right syntax to do this...is a script that simply creates a record in some EDIT table...where there is *NO* corresponding record...in the PS_DOC_LIN_PRICE table...any time a record is added to the PS_DOC_LIN table. Simple, simple. Then, we'll be in a better position, to help isolate these occurences.
In that "EDIT" table, we'll need the following fields:
DOC_ID (bigint)
LIN_SEQ_NO (int)
STR_ID (varchar(10))
STA_ID (varchar(10))
TKT_NO (varchar(15))
LIN_TYP (varchar(1))
PRC (money)
ITEM_NO (varchar(20))
CALC_PRC (money)
DESCR (varchar(50))
QTY_SOLD (decimal(15,4))
EXT_PRC (decimal(15,2))
PRC_1 (money)
ORIG_QTY (decimal(15,4))
QTY_SHIPPED (decimal(15,4))
We're not certain...if this needs to be a Trigger...or a Stored Proc (presumably a trigger?), but we need to have it pretty quickly...as our Invoicing software...is shipping out items...with a ZERO price...not good!
PLEASE HELP...we'd very much appreciate it!...Thanks!...Mark
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER