need help on complex SP that i don't get

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)

need help on complex SP that i don't get

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-11 : 22:23:52

Hi all,
I have a rather lengthy stored procedure to write, and I am not that good at this to get it done. This is my first time posting here, and I didn't want to post a bunch of table info without a little intro and asking if it is ok. basically there are three tables that our accounting department use to request warranty from vendors: 1 to enter the original request for parts and labor credit, a second to receive the parts credit, and a third to receive the labor credit. that is the simple part! from there all of this info is manually moved to a third party app, even though there is a link between MSSQL and the app. There are two sequencial tables that this data would need to be moved to in order to automate depending on whether the warranty is received as a credit or as a refund check, and then I would like to move the 'posted' data out to a separate history table. WHEW! I have all of the table info, and I can post it with greater detail. Can anyone out there help me? I am truly at a loss with this one!
Andy

HOW DO YOU TURN OFF THE BIg letters....never mind..

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-03-12 : 03:28:45

Hi Andy and welcome to the site!

Post your DDL and some sample data (inserts) and details on what you want to do, and someone around here will surely help you. These guys don't know the words "I don't know" or "I don't want to help"

/Andraax

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 07:18:48

Ahhh,
nothing like a friendly 'hello' to start the day! I will postt all the table info and what I'm looking to do later today. I really do appreciate any help I can get, SQL is not my best subject, but I am learning quickly! This SP will need to loop through the tables several times (I think) and it is dealing with money (yikes! don't wanna just "poke and hope"!) will fill you guys and gals in later today,
Andy

HOW DO YOU TURN OFF THE BIg letters....never mind..

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-03-12 : 07:22:03

Long time no hear Andraax my friend!

________________
Make love not war!

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 09:59:20

[code]
TABLE WARR_RQST_DATA

PK COLUMN DATATYPE LENGTH ALLOW NULL
----------------------------------------------------------
3 TKT_NO T_DOC_NO 15 0
0 ORIG_PARTS_VEND T_VEND_NO 15 1
0 ORIG_PARTS_AMT T_MONEY 9 1
0 ORIG_LABOR_VEND T_VEND_NO 15 1
0 ORIG_LABOR_AMT T_MONEY 9 1
0 ORIG_RQST_DATA T_DAT 8 1
0 LABOR_POSTED T_BOOL 1 1
0 PARTS_POSTED T_BOOL 1 1

TABLE WARR_PARTS_RCVD

PK COLUMN DATATYPE LENGTH ALLOW NULL
----------------------------------------------------------
3 TKT_NO T_DOC_NO 15 0
0 DATE_PARTS_RECVD T_DAT 8 1
0 ACT_PARTS_AMT T_MONEY 9 1
0 PARTS_ARE_CREDIT T_BOOL 1 1
0 PARTS_CLAIM_DENIED T_BOOL 1 1
0 PARTS_INV_CHK_NO T_DOC_NO 15 1
0 ACT_PARTS_VEND T_VEND_NO 15 1

TABLE WARR_LABOR_RCVD

PK COLUMN DATATYPE LENGTH ALLOW NULL
----------------------------------------------------------
3 TKT_NO T_DOC_NO 15 0
0 DATE_LABOR_RECVD T_DAT 8 1
0 ACT_LABOR_AMT T_MONEY 9 1
0 LABOR_IS_CREDIT T_BOOL 1 1
0 LABOR_CLAIM_DENIED T_BOOL 1 1
0 LABOR_INV_CHK_NO T_DOC_NO 15 1
0 ACT_LABOR_VEND T_VEND_NO 15 1

TABLE WARR_HIST

PK COLUMN DATATYPE LENGTH ALLOW NULL
----------------------------------------------------------
3 TKT_NO T_DOC_NO 15 0
0 ORIG_PARTS_VEND T_VEND_NO 15 1
0 ORIG_PARTS_AMT T_MONEY 9 1
0 ORIG_LABOR_VEND T_VEND_NO 15 1
0 ORIG_LABOR_AMT T_MONEY 9 1
0 ORIG_RQST_DATA T_DAT 8 1
0 LABOR_POSTED T_BOOL 1 1
0 PARTS_POSTED T_BOOL 1 1
0 DATE_PARTS_RECVD T_DAT 8 1
0 ACT_PARTS_AMT T_MONEY 9 1
0 PARTS_ARE_CREDIT T_BOOL 1 1
0 PARTS_CLAIM_DENIED T_BOOL 1 1
0 PARTS_INV_CHK_NO T_DOC_NO 15 1
0 ACT_PARTS_VEND T_VEND_NO 15 1
0 DATE_LABOR_RECVD T_DAT 8 1
0 ACT_LABOR_AMT T_MONEY 9 1
0 LABOR_IS_CREDIT T_BOOL 1 1
0 LABOR_CLAIM_DENIED T_BOOL 1 1
0 LABOR_INV_CHK_NO T_DOC_NO 15 1
0 ACT_LABOR_VEND T_VEND_NO 15 1

TABLE PO_RECVR_HIST

PK COLUMN DATATYPE LENGTH ALLOW NULL
----------------------------------------------------------
3 RECVR_NO T_DOC_NO (varchar) 15 0
0 EVENT_NO T_DOC_NO (varchar) 15 1
0 BAT_ID T_COD (varchar) 10 1
0 VEND_NO T_VEND_NO (varchar) 15 1
0 VEND_FST_NAM T_FST_NAM (varchar) 15 1
0 VEND_FST_NAM_UPR T_FST_NAM (varchar) 15 1
0 VEND_LST_NAM T_LST_NAM (varchar) 25 1
0 VEND_LST_NAM_UPR T_LST_NAM (varchar) 25 1
0 VEND_NAM T_NAM (varchar) 40 1
0 VEND_NAM_UPR T_NAM (varchar) 40 1
0 RECVR_MISC_AMT_1 T_MONEY (decimal) 9 1
0 RECVR_MISC_AMT_2 T_MONEY (decimal) 9 1
0 RECVR_MISC_AMT_3 T_MONEY (decimal) 9 1
0 RECVR_MISC_AMT_4 T_MONEY (decimal) 9 1
0 RECVR_MISC_AMT_5 T_MONEY (decimal) 9 1
0 RECVR_TOT_MISC T_MONEY (decimal) 9 1
0 RECVR_SUB_TOT T_MONEY (decimal) 9 1
0 RECVR_TOT T_MONEY (decimal) 9 1
0 RECVR_DAT T_DAT (datetime) 8 1
0 RECVR_SHIP_VIA_COD T_COD (varchar) 10 1
0 RECVR_LOC_ID T_LOC_ID (varchar) 10 1
0 TOT_SEL_LINS T_INT (int) 4 1
0 TOT_QTY_TO_RECV T_QTY (decimal) 9 1
0 USR_ID T_USR_ID (varchar) 10 1
0 VCHR_NO T_VCHR_NO (varchar) 20 1
0 VCHR_VEND_NO T_VEND_NO (varchar) 15 1
0 VCHR_DAT T_DAT (datetime) 8 1
0 VCHR_TIM T_TIM (datetime) 8 1
0 VCHR_BY T_USR_ID (varchar) 10 1
0 VCHR_INVC_NO T_VCHR_NO (varchar) 20 1
0 VCHR_INVC_DAT T_DAT (datetime) 8 1
0 VCHR_DIST_DAT T_DAT (datetime) 8 1
0 VCHR_REF T_REF (varchar) 50 1
0 VCHR_STAT T_FLG (varchar) 1 1
0 REF T_REF (varchar) 50 1
0 COMMNT_1 T_COMMNT (varchar) 50 1
0 COMMNT_2 T_COMMNT (varchar) 50 1
0 COMMNT_3 T_COMMNT (varchar) 50 1
0 IS_ALLOC T_BOOL (varchar) 1 1
0 ALLOC_LOC_GRP T_COD (varchar) 10 1
0 LST_MAINT_DT T_DT (datetime) 8 1
0 LST_MAINT_USR_ID T_USR_ID (varchar) 10 1
0 LST_LCK_DT T_DT (datetime) 8 1
0 ROW_TS timestamp 8 1

TABLE SY_DIST

PK COLUMN DATATYPE LENGTH ALLOW NULL
----------------------------------------------------------
3 DIST_GRP T_FLG (varchar) 1 0
2 DIST_TYP T_FLG (varchar) 1 0
2 ACCT_NO T_ACCT_NO (varchar) 20 0
2 DIST_DAT T_DAT (datetime) 8 0
2 SEQ_NO T_SEQ_NO (int) 4 0
2 EVENT_NO T_DOC_NO (varchar) 15 0
0 PKG_ID T_PKG_ID (varchar) 2 1
0 ACT_TYP T_ACT_TYP (varchar) 3 1
0 AMT T_MONEY (decimal) 9 0
0 FST_DOC_NO T_DOC_NO (varchar) 15 1
0 FST_REF T_REF (varchar) 50 1
0 REC_CNT T_INT (int) 4 0
0 INTRFACD varchar 1 0
0 INTFC_DAT T_DAT (datetime) 8 1
0 INTFC_TIM T_TIM (datetime) 8 1
0 INTFC_CNT T_INT (int) 4 1
0 LST_MAINT_DT T_DT (datetime) 8 1
0 LST_MAINT_USR_ID T_USR_ID (varchar) 10 1
0 LST_LCK_DT T_DT (datetime) 8 1
0 ROW_TS timestamp 8 1
[/code]
DESCRIPTION OF DATA
-------------------
TKT_NO = an invoice #, typically 5 or 6 digits
VEND_NO = vendor ID number, typically 2 or 3 digits.
WARR_*_RCVD.*_INV_CHK_NO = invoice or check #, which is FST_DOC_NO in
SY_DIST and VCHR_INVC_NO in PO_RCVR_HIST.
WARR_*_RCVD.ACT_*_AMT = SY_DIST.AMT, and PO_RCVR_HIST.RECVR.TOT

oK,
Here we go. basically we are working with the 3 WARR_*_* tables. When a
warranty job is done, request for reimbursment is entered into the
WARR_RQST_DATA table. reimbursment for labor and parts get received at
different times, and are entered in the appropriate WARR_*_RCVD tables.
at this point all the info is moved around manually, are real Pain in the
***!! here is what I'm trying to do:
at the end of each day, run a crystal report (I can do that part) that calls
an SP. This SP needs to loop through the two WARR_*_RCVD tables, and depending
on the PARTS_ARE_CREDIT and LABOR_IS CREDIT bits, insert the data into either
SY_DIST (if it is NOT a credit) or into PO_RECVR_HIST (if it IS a credit). Also,
the appropriate LABOR_POSTED or PARTS_POSTED columns need to be updated in
WARR_RQST_DATA. Updating SY_DIST should be ok with what comes out of the WARR_*_RCVD
tables, but the PO_HIST table needs to have data joined from PO_VEND (vendor info)
to fill in the blanks. I didn't include that table here, but if someone shows me
where to insert the join, I can do that. two important notes: the column ACCT_NO
is SY_DIST needs to come from SY_COMP.VEND_REFUND_ACT, and both SY_DIST and
PO_RCVR_HIST are sequencial, with data coming in from other sources as well.
After this all updates, IF both *_POSTED columns are 'Y' in WARR_RQST_DATA, then
we want to copy the matching rowset from all 3 WARR tables into WARR_HIST, delete it
from the other 3 WARR tables, and update PS_TKT_HIST.IS_POSTED to 'Y'.
This is a lot to ask, and I will be truly indentured to anyone who can help!
Andy

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 19:40:49

Just a quick note!
when the SP checks the WARR_*_RCVD tables, it needs to filter by the WARR_RQST_DATA.*_POSTED columns so that it doesn't post money twice! (But you guys already knew I screwed that up!)

HOW DO YOU TURN OFF THE BIg letters....never mind..

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-12 : 19:47:21

Andy,
Any chance of getting those tables in CREATE TABLE and the data in INSERT INTO statements?

It's really helpful if we can do a straight-up cut and paste right into Query Analyzer. Helps you get your solution MUCH faster :)

Take a look at my post in this thread so you can see what I mean:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33312

Thanks!
Michael

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

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 20:01:12

I can do the CREATE TABLE thing no problem, I saved the scripts. I don't really have an INSERT set written, saw what was needed and it scared the beejeesus outa me, so I called in you guys! tell me what you want, and I'll do my VERY best to write it (not that it will be a masterpiece, mind you)... I'll get the table stuff up in about 1/2 an hour..

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 20:40:44

Ok Gang,
here is the CREATE TABLE code:

CREATE TABLE dbo.PO_RECVR_HIST
	(
	RECVR_NO T_DOC_NO NOT NULL,
	EVENT_NO T_DOC_NO NULL,
	BAT_NO T_COD NULL,
	VEND_NO T_VEND_NO NULL,
	VEND_FST_NAM T_FST_NAM NULL,
	VEND_FST_NAM_UPR T_FST_NAM NULL,
	VEND_LST_NAM T_LST_NAM NULL,
	VEND_LST_NAM_UPR T_LST_NAM NULL,
	VEND_NAM T_NAM NULL,
	VEND_NAM_UPR T_NAM NULL,
	RECVR_MISC_AMT_1 T_MONEY NULL,
	RECVR_MISC_AMT_2 T_MONEY NULL,
	RECVR_MISC_AMT_3 T_MONEY NULL,
	RECVR_MISC_AMT_4 T_MONEY NULL,
	RECVR_MISC_AMT_5 T_MONEY NULL,
	RECVR_TOT_MISC T_MONEY NULL,
	RECVR_SUB_TOT T_MONEY NULL,
	RECVR_TOT T_MONEY NULL,
	RECVR_DAT T_DAT NULL,
	RECVR_SHIP_VIA_COD T_COD NULL,
	RECVR_LOC_ID T_LOC_ID NULL,
	TOT_SEL_LINS T_INT NULL,
	TOT_QTY_TO_RECV T_QTY NULL,
	USR_ID varchar(10) NULL,
	VCHR_NO varchar(20) NULL,
	VCHR_VEND_NO T_VEND_NO NULL,
	VCHR_DAT T_DAT NULL,
	VCHR_TIM T_TIM NULL,
	VCHR_BY T_USR_ID NULL,
	VCHR_INVC_NO T_VCHR_NO NULL,
	VCHR_INVC_DAT T_DAT NULL,
	VCHR_DIST_DAT T_DAT NULL,
	VCHR_REF T_REF NULL,
	VCHR_STAT T_FLG NULL,
	REF T_REF NULL,
	COMMNT_1 T_COMMNT NULL,
	COMMNT_2 T_COMMNT NULL,
	COMMNT_3 T_COMMNT NULL,
	IS_ALLOC T_BOOL NULL,
	ALLOC_LOC_GRP T_COD NULL,
	LST_MAINT_DT T_DT NULL,
	LST_MAINT_USR_ID T_USR_ID NULL,
	LST_LCK_DT T_DT NULL,
	ROW_TS timestamp NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.PO_RECVR_HIST ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	RECVR_NO
	) ON [PRIMARY]

GO
COMMIT

CREATE TABLE dbo.SY_DIST
	(
	DIST_GRP T_FLG NOT NULL,
	DIST_TYP T_FLG NOT NULL,
	ACCT_NO T_ACCT_NO NOT NULL,
	DIST_DAT T_DAT NOT NULL,
	SEQ_NO T_SEQ_NO NOT NULL,
	EVENT_NO T_DOC_NO NOT NULL,
	PKG_ID T_PKG_ID NULL,
	ACT_TYP T_ACT_TYP NULL,
	AMT T_MONEY NULL,
	FST_DOC_NO T_DOC_NO NULL,
	FST_REF T_REF NULL,
	REC_CNT T_INT NULL,
	INTRFACD varchar(50) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.SY_DIST ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	DIST_GRP,
	DIST_TYP,
	ACCT_NO,
	DIST_DAT,
	SEQ_NO,
	EVENT_NO
	) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_Table1 ON dbo.Table1
	(
	DIST_GRP
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Table1_1 ON dbo.Table1
	(
	DIST_GRP
	) ON [PRIMARY]
GO
COMMIT

CREATE TABLE dbo.WARR_RQST_DATA
	(
	TKT_NO T_DOC_NO NOT NULL,
	ORIG_PARTS_VEND T_VEND_NO NULL,
	ORIG_PARTS_AMT T_MONEY NULL,
	ORIG_LABOR_VEND T_VEND_NO NULL,
	ORIG_LABOR_AMT T_MONEY NULL,
	ORIG_RQST_DATE T_DAT NULL,
	LABOR_POSTED T_BOOL NULL,
	PARTS_POSTED T_BOOL NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.WARR_RQST_DATA ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	TKT_NO
	) ON [PRIMARY]

GO
COMMIT

CREATE TABLE dbo.WARR_PARTS_RCVD
	(
	TKT_NO T_DOC_NO NOT NULL,
	DATA_PARTS_RCVD T_DAT NULL,
	ACT_PARTS_AMT T_MONEY NULL,
	PARTS_ARE_CREDIT T_BOOL NULL,
	PARTS_CLAIM_DENIED T_BOOL NULL,
	PARTS_INV_CHK_NO T_DOC_NO NULL,
	ACT_PARTS_VEND T_VEND_NO NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.WARR_PARTS_RCVD ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	TKT_NO
	) ON [PRIMARY]

GO
COMMIT

CREATE TABLE dbo.WARR_LABOR_RCVD
	(
	TKT_NO T_DOC_NO NOT NULL,
	DATA_LABOR_RCVD T_DAT NULL,
	ACT_LABOR_AMT T_MONEY NULL,
	LABOR_IS_CREDIT T_BOOL NULL,
	LABOR_CLAIM_DENIED T_BOOL NULL,
	LABOR_INV_CHK_NO T_DOC_NO NULL,
	ACT_LABOR_VEND T_VEND_NO NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.WARR_LABOR_RCVD ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	TKT_NO
	) ON [PRIMARY]

GO
COMMIT

CREATE TABLE dbo.WARR_HIST
	(
	TKT_NO T_DOC_NO NOT NULL,
	ORIG_RQST_DATE T_DAT NULL,
	ORIG_PARTS_VEND T_VEND_NO NULL,
	ACT_PARTS_VEND T_VEND_NO NULL,
	ORIG_PARTS_AMT T_MONEY NULL,
	ACT_PARTS_AMT T_MONEY NULL,
	PARTS_ARE_CREDIR T_BOOL NULL,
	PARTS_CLAIM_DENIED T_BOOL NULL,
	[PARTS-INV_CHK_NO] T_DOC_NO NULL,
	DATE_PARTS_RCVD T_DAT NULL,
	ORIG_LABOR_VEND T_VEND_NO NULL,
	ACT_LABOR_VEND T_VEND_NO NULL,
	ORIG_LABOR_AMT T_MONEY NULL,
	ACT_LABOR_AMT T_MONEY NULL,
	LABOR_IS_CREDIT T_BOOL NULL,
	LABOR_CLAIM_DENIED T_BOOL NULL,
	LABOR_INV_CHK_NO T_DOC_NO NULL,
	DATE_LABOR_RCVD T_DAT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.WARR_HIST ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	TKT_NO
	) ON [PRIMARY]

GO
COMMIT

Hope this helps! anything else I can provide? As I think about it, at this point in time there is no data in the WARR tables at all, and so long as we keep 3 separate tables (plus the HIST table), we can change these if need be. The SY_DIST and PO_RECVR_HIST tables cannot be touched, as there are critical to other apps.

(just 'cause i like the smiley!)

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 20:42:49

AH crap! I just realized that I cut and pasted the table code with PK's clustered on all tables! The only table with a cluster (it's all a cluster?) is SY_DIST. The rest just have one PK.

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 21:22:15

AH CRAP AGAIN!
The SY_DIST table is missing the end! honestly, I am SUCH a doof!

CREATE TABLE dbo.SY_DIST
	(
	DIST_GRP T_FLG NOT NULL,
	DIST_TYP T_FLG NOT NULL,
	ACCT_NO T_ACCT_NO NOT NULL,
	DIST_DAT T_DAT NOT NULL,
	SEQ_NO T_SEQ_NO NOT NULL,
	EVENT_NO T_DOC_NO NOT NULL,
	PKG_ID T_PKG_ID NULL,
	ACT_TYP T_ACT_TYP NULL,
	AMT T_MONEY NULL,
	FST_DOC_NO T_DOC_NO NULL,
	FST_REF T_REF NULL,
	REC_CNT T_INT NULL,
	INTRFACD varchar NULL,
	INTFC_DAT T_DAT NULL,
	INTFC_TIM T_TIM NULL,
	INTFC_CNT T_INT NULL,
	LST_MAINT_DT T_DT NULL,
	LST_MAINT_USR_ID T_USR_ID NULL,
	LST_LCK_DT T_DT NULL,
	ROW_TS timestamp NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.SY_DIST ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	DIST_GRP,
	DIST_TYP,
	ACCT_NO,
	DIST_DAT,
	SEQ_NO,
	EVENT_NO
	) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_Table1 ON dbo.Table1
	(
	DIST_GRP
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Table1_1 ON dbo.Table1
	(
	DIST_GRP
	) ON [PRIMARY]
GO
COMMIT

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-12 : 21:25:28

By the way,
here is the user data types that are in the tables:

EXEC sp_addtype N'T_ACCT_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_ACT_TYP', N'varchar (3)', N'null'
GO

EXEC sp_addtype N'T_ADRS', N'varchar (40)', N'null'
GO

EXEC sp_addtype N'T_AP_VEND_NO', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_AVS_ADRS', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_AVS_ZIP_COD', N'varchar (9)', N'null'
GO

EXEC sp_addtype N'T_BARCOD', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_BAT_TYP', N'varchar (5)', N'null'
GO

EXEC sp_addtype N'T_BOOL', N'varchar (1)', N'null'
GO

EXEC sp_addtype N'T_BTN_LBL', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_CELL_BARCOD_PRE', N'varchar (16)', N'null'
GO

EXEC sp_addtype N'T_CHK_ACCT_NO', N'varchar (25)', N'null'
GO

EXEC sp_addtype N'T_CHK_MICR', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_CHK_NO', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_CITY', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_CNTRY', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_COD', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_COMMNT', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_COST', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_CR_CARD_EXP_DAT', N'datetime', N'null'
GO

EXEC sp_addtype N'T_CR_CARD_NAM', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_CR_CARD_NO', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_CR_CARD_PREFIX', N'varchar (6)', N'null'
GO

EXEC sp_addtype N'T_CR_CARD_TRACK_DATA', N'varchar (100)', N'null'
GO

EXEC sp_addtype N'T_CUBE', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_CUST_NO', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_CUST_PO_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_CVV_VAL', N'varchar (4)', N'null'
GO

EXEC sp_addtype N'T_DAT', N'datetime', N'null'
GO

EXEC sp_addtype N'T_DB_SYSNAME', N'varchar (128)', N'null'
GO

EXEC sp_addtype N'T_DESCR', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_DESCR25', N'varchar (25)', N'null'
GO

EXEC sp_addtype N'T_DOC_NO', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_DRIV_LIC', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_DT', N'datetime', N'null'
GO

EXEC sp_addtype N'T_EC_AFFIL_COD', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_EC_BAT_NO', N'varchar (6)', N'null'
GO

EXEC sp_addtype N'T_EC_CUST_NO', N'varchar (9)', N'null'
GO

EXEC sp_addtype N'T_EC_DISC_COD', N'varchar (8)', N'null'
GO

EXEC sp_addtype N'T_EC_ORD_NO', N'varchar (9)', N'null'
GO

EXEC sp_addtype N'T_ECOMM_CATEG_ID', N'varchar (16)', N'null'
GO

EXEC sp_addtype N'T_EDC_AUTH_COD', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_EDC_AUTH_RESP', N'varchar (80)', N'null'
GO

EXEC sp_addtype N'T_EDC_BATCH_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_EDC_MERCH_ID', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_EDC_TERM_ID', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_EMAIL_ADRS', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_EMP_NO', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_ERR_REF', N'varchar (255)', N'null'
GO

EXEC sp_addtype N'T_EXCH_RATE', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_FILENAME', N'varchar (100)', N'null'
GO

EXEC sp_addtype N'T_FILENAME50', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_FLG', N'varchar (1)', N'null'
GO

EXEC sp_addtype N'T_FLG2', N'varchar (2)', N'null'
GO

EXEC sp_addtype N'T_FOB', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_FST_NAM', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_GL_ACCT_NO', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_GRID_DIM_1', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_GRID_DIM_2', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_GRID_DIM_3', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_GRID_DIM_TAG', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_HTML_DESCR', N'text', N'null'
GO

EXEC sp_addtype N'T_IMG', N'image', N'null'
GO

EXEC sp_addtype N'T_INT', N'int', N'null'
GO

EXEC sp_addtype N'T_ITEM_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_LOC_ID', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_LONG_DESCR', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_LOT_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_LST_NAM', N'varchar (25)', N'null'
GO

EXEC sp_addtype N'T_MON', N'varchar (5)', N'null'
GO

EXEC sp_addtype N'T_MONEY', N'decimal(15,2)', N'null'
GO

EXEC sp_addtype N'T_MULT', N'decimal(10,4)', N'null'
GO

EXEC sp_addtype N'T_NAM', N'varchar (40)', N'null'
GO

EXEC sp_addtype N'T_NOTE', N'text', N'null'
GO

EXEC sp_addtype N'T_NOTE_ID', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_PCT', N'decimal(9,3)', N'null'
GO

EXEC sp_addtype N'T_PHONE', N'varchar (25)', N'null'
GO

EXEC sp_addtype N'T_PKG_ID', N'varchar (2)', N'null'
GO

EXEC sp_addtype N'T_PLAN_BY', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_PLAN_ID', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_PO_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_PRC', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_PRC_JUST_STR', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_PWD', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_QTY', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_REF', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_RGY_ID', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_SALUTATION', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_SEAS', N'varchar (5)', N'null'
GO

EXEC sp_addtype N'T_SEQ_NO', N'int', N'null'
GO

EXEC sp_addtype N'T_SER_NO', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_SHORT_DESCR', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_SHORT_NOTE', N'varchar (250)', N'null'
GO

EXEC sp_addtype N'T_SHORTCUT', N'varchar (30)', N'null'
GO

EXEC sp_addtype N'T_SQL_COLUMN', N'varchar (100)', N'null'
GO

EXEC sp_addtype N'T_SQL_EXPRESSION', N'varchar (1000)', N'null'
GO

EXEC sp_addtype N'T_SQL_FILTER', N'varchar (2000)', N'null'
GO

EXEC sp_addtype N'T_SQL_FLG', N'int', N'null'
GO

EXEC sp_addtype N'T_SQL_QUERY', N'varchar (8000)', N'null'
GO

EXEC sp_addtype N'T_SQL_TABLE', N'varchar (100)', N'null'
GO

EXEC sp_addtype N'T_STATE', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_STR_GUID', N'varchar (40)', N'null'
GO

EXEC sp_addtype N'T_TAX_AMT_EXACT', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_TIM', N'datetime', N'null'
GO

EXEC sp_addtype N'T_TRANSIT_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_TRX_TYP', N'varchar (3)', N'null'
GO

EXEC sp_addtype N'T_UNIT', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_URL', N'varchar (100)', N'null'
GO

EXEC sp_addtype N'T_USR_DEF_NO', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_USR_ID', N'varchar (10)', N'null'
GO

EXEC sp_addtype N'T_USR_INITIALS', N'varchar (5)', N'null'
GO

EXEC sp_addtype N'T_VCHR_NO', N'varchar (20)', N'null'
GO

EXEC sp_addtype N'T_VEND_ACCT_NO', N'varchar (50)', N'null'
GO

EXEC sp_addtype N'T_VEND_NO', N'varchar (15)', N'null'
GO

EXEC sp_addtype N'T_VER', N'decimal(5,2)', N'null'
GO

EXEC sp_addtype N'T_WEEK', N'varchar (5)', N'null'
GO

EXEC sp_addtype N'T_WEIGHT', N'decimal(15,4)', N'null'
GO

EXEC sp_addtype N'T_ZIP_COD', N'varchar (15)', N'null'
GO

Hope this helps!

HOW DO YOU TURN OFF THE BIg letters....never mind..

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-03-13 : 04:52:12

Hi again Andy and hey Amethystium, nice to see you again :)

I don't have time today to look through your problem, but I will tomorrow if noone else has by then.

Have a nice weekend
/Andraax

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-13 : 06:48:33

Thank you, All.
I did not get a chance to do an INSERT for some data last night, but I have to go in to work this morning and will try to get something out to you guys. Have a great weekend!
Andy

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-13 : 09:50:21

Ok,
here we go with the basic INSERT stuff, and what needs to happen. when a request for reimbursment is made, data is inserted into WARR_RQST_DATA:

INSERT INTO WARR_RQST_DATA (TKT_NO, ORIG_PARTS_VEND, ORIG_PARTS_AMT, ORIG_LABOR_VEND, ORIG_LABOR_AMT, ORIG_RQST_DAT, LABOR_POSTED, PARTS_POSTED) VALUES(12345, 44, 25.00, 45, 70.00,12/3/2003, N, N)

At some point part of the reimbursment will come in, either parts or labor or both(we'll use parts as an example)

INSERT INTO WARR_PARTS_RVCD (TKT_NO, DAT_PARTS_RCVD, ACT_PARTS_AMT, PARTS_ARE_CREDIT, PARTS_CLAIM_DENIED, PARTS-INV_CHK_NO, ACT_PARTS_VEND) VALUES (12345,12/18/2003, 25.00, Y, N, 987654, 44)

Now, depending on the PARTS_ARE_CREDIT and PARTS_CLAIM_DENIED flag, we need to move this data and update other flags. If the CREDIT flag is set to 'Y', then this rowset needs to go to the PO_RECVR_HIST table like this:

SELECT TKT_NO + 'L' as RECVR_NO, DAT_PARTS_RCVD as RECVR_DAT, ACT_PARTS_AMT as RECVR_TOT, PARTS_INV_CHK_NO as VCHR_INVC_NO, ACT_PARTS_VEND as VEND_NO from WARR_LABOR_RCVD

This then needs to be INSERTED INTO PO_RECVR_HIST. I put the +'L' in there because we could possibly rcv the matching Parts credit on the same day, and we could put a 'P' in that doc to separate them. If the PARTS_ARE_CREDIT flag is 'N', then this data needs to be INSERTED into the SY_DIST table:

SELECT TKT_NO as EVENT_NO, DAT_PARTS_RCVD as DIST_DAT, ACT_PARTS_AMT as AMT, PARTS_INV_CHK_NO as FST_DOC_NO from WARR_PARTS_RCVD

Now, in SY_DIST there is a column ACCT_NO, and this needs to come from SY_COMP.VEND_REFUND_ACT if WARR_PARTS_RCVD.PARTS_CLAIM_DENIED is 'N', and from SY_COMP.DENIED_PTS_CLAIM if it is 'Y'.(for labor it is DENIED_LAB_CLAIM) After this, the WARR_RQST_DATA.PARTS_POSTED flag needs to be updated to 'Y'. this is identical for LABOR, just using the LABOR_RCVD table and names instead. After we loop and update, If both POSTED flags are 'Y', we need to copy all rowsets from the WARR tables to WARR_HIST, delete them from the 3 WARR tables, and update the PS_TKT_HIST.IS_POSTED flag to 'Y' (can you believe this is all manual now?) Now, the INSERTS for PO_RECVR_HIST need to have a join from table PO_VEND to fill in the rest of the vendor blanks. I will need to know where this goes, but I think I can write in myself. (yeah, right!) the SY_DIST table is sequencial, so I am guessing we will need a cursor to select and insert, and this goes in the SEQ_NO column (no kidding).The 4 required columns we are missing will be constants: DIST_GRP='1',DIST_TYP='A',REC_CNT='1' and INTRFACD='N'. If you guys pull this off I need to know where to send the suitcase full of small denomination unmarked bills! I don't know that I can ever return the favor, but if anyone ever needs Microsoft OS help (and I don't mean "click on start..") I am your guy!
one bewildered techie,
Andy

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-13 : 12:49:47

IMPORTANT NOTE!! When we SELECT records initially from the two WAR_*_RCVD tables, we need to filter by the WARR_RQST_DATA.*_POSTED columns!! otherwise we will be re-posting the same stuff over and over!

HOW DO YOU TURN OFF THE BIg letters....never mind..

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-14 : 20:51:50

Hey Andraax (and everyone else),
Hysterical little note:
I got called into work late Saturday, was told one of the exchange srvers was "flipping out". Seems that one of the SQL DB techs had inadvertently set the TCP/IP port from 3168 (which we use for SQL) to 3125 (which we use for exchange). You would think that OLEDB would just crap out, what with no SQL server, but instead I walked into an exchange box with a 930GB mailbox store!

Holy Terabit Batman!

There's never enough time to type code right,
but always enough time for a hotfix...

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-03-15 : 04:14:54

Hi Andy!

Have you given this a shot yourself? If you have, it would be nice to see what you have done. It doesn't seem too advanced, there's just a lot to be done.

/Andraax

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-15 : 06:35:04

No Andraax,
I haven't. SQL is new to me, just starting to learn. I have pretty good command of basic SELECT, JOIN, etc. but SP's and Triggers still get me all screwed up. I would love to go to school on you guys and figure this one out, but that is a lot to ask! I am certified on several different server platforms, and SQL is next in line. I know we need to declare all variables, then local variables, then write the SELECT and INSERT(UPDATE?) stuff, but I'm not quite sure how! The reason I haven't just "shot in the dark" is that the SY_DIST and PO_RECVR_HIST tables connect directly to our 3rd party accounting software, and any garbage that gets in thee will cause one hell of a mess. I wish I could ask the people that maintain the SQL servers, but they are some contract company, and don't want to know squat. That, plus this is a problem for just one department with 3 people in it, and that means it isn't worth it to the top brass to do. I volunteered to try and help (me and my big mouth!) and here I am.
Actually, I'm pretty proud of myself already; I wrote those CREATE scripts and the INSERT scripts myself, and they're right! Guess you guys are teaching me something after all!

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-15 : 07:09:10

Ok Andraax,
Thought I'd sit down before work and at least try to start this, and already I'm stumped. When we declare variables, do we declare EVERY column that will appear in the procedure? For example, do we need to declare the *_*_ACCT columns from SY_COMP, even though they are a reference? Also, what determines which variables are 'local'? I need to add a variable to select by date and a 'create' flag (these are for Crystal); are these 'local' variables because they are inherent to the SP only? Hate to be a pain, but it would be nice to get a better handle on this..
Nothing like taking the bull by the horns!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-03-16 : 19:26:29

Ok,
I'm trying my damnedest to understand this stuff (it's snowing here, so I've got a little time); in an SP, Columns that are used in SELECT to be INSERTED or UPDATED don't get declared as variables, right? It is only data that we need to define (like a parameter)that gets declared? I'm writing a little SP to try and get the hang of this! I think I'm making a big deal out of nothing, but the mental hang-up is killin' me! (I feel like a real bozo posting these questions, but hey, why not?)

There's never enough time to type code right,
but always enough time for a hotfix...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources