Bulk Price Code Insert

[ChrisP520]

Greetings:

We are running Progression 7.6.300C on MSSQL Server 2000.

I am trying to do a bulk insert of Type 4 price codes into the OEPRCFIL_SQL table. I am trying to do this because Macola's COPY PRICE CODES application doesn't work with Type 4 Price codes. The goal is to create price codes for a new customer type.

We currently have a customer type of G which is a reseller customer that gets discounts based on quantity purchased. We have a a fairly large group of customers that we offer "End Column" pricing to due to the large amount of business that they do with us. So, I want to create a Customer Type of EC (End Column) that has ALL of the Type 4 Codes that the Type G customer has, with a few differences.

The EC Customer Type would only have a List Price, or minimum_qty_1, prc_or_disc_1 and the end column price which would live in minimum_qty_2, prc_or_disc_2. The minimum_qty_2, prc_or_disc_2 would have the last minimum_qty and prc_or_disc that is greater than zero for the Customer Type G customers. Therefore this is our end column price code. The only real difference between what exists and what is inserted is the left substring of the filler_0001 field which would change from 'G' to 'EC' and the end column attribute.

So, the plan is to run an insert statement in QA, then run an update statement in QA to create the End Column structure that I mentioned above.

My problem stems from the insert statement that I created in Query Analyzer. I was getting the following error:

Server: Msg 2601, Level 14, State 3, Line 3
Cannot insert duplicate key row in object 'OEPRCFIL_SQL' with unique index 'Ioeprcfil_sql0'.
The statement has been terminated.

Below is the insert statement that I used.

BEGIN TRAN
Insert into oeprcfil_sql
	(cd_tp,
	curr_cd,
	filler_0001,
	start_dt,
	end_dt,
	cd_prc_basis,
	minimum_qty_1,
	prc_or_disc_1,
	minimum_qty_2,
	prc_or_disc_2,
	minimum_qty_3,
	prc_or_disc_3,
	minimum_qty_4,
	prc_or_disc_4,
	minimum_qty_5,
	prc_or_disc_5,
	minimum_qty_6,
	prc_or_disc_6,
	minimum_qty_7,
	prc_or_disc_7,
	minimum_qty_8,
	prc_or_disc_8,
	minimum_qty_9,
	prc_or_disc_9,
	minimum_qty_10,
	prc_or_disc_10,
	comm_pct,
	filler_0004)
SELECT 
	cd_tp,
	curr_cd,
	'EC   '+LTRIM(SUBSTRING(filler_0001, 6, 3)),
	start_dt,
	end_dt,
	cd_prc_basis,
	minimum_qty_1,
	prc_or_disc_1,
	minimum_qty_2,
	prc_or_disc_2,
	minimum_qty_3,
	prc_or_disc_3,
	minimum_qty_4,
	prc_or_disc_4,
	minimum_qty_5,
	prc_or_disc_5,
	minimum_qty_6,
	prc_or_disc_6,
	minimum_qty_7,
	prc_or_disc_7,
	minimum_qty_8,
	prc_or_disc_8,
	minimum_qty_9,
	prc_or_disc_9,
	minimum_qty_10,
	prc_or_disc_10,
	comm_pct,
	filler_0004
FROM OEPRCFIL_SQL
WHERE cd_tp = '4' AND LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'G'

After that, I use the following update statement:

BEGIN TRAN
UPDATE OEPRCFIL_SQL
SET prc_or_disc_2 = 
	CASE 
		WHEN prc_or_disc_10 >0 THEN prc_or_disc_10
		WHEN prc_or_disc_9 >0 THEN prc_or_disc_9
		WHEN prc_or_disc_8 >0 THEN prc_or_disc_8
		WHEN prc_or_disc_7 >0 THEN prc_or_disc_7
		WHEN prc_or_disc_6 >0 THEN prc_or_disc_6
		WHEN prc_or_disc_5 >0 THEN prc_or_disc_5
		WHEN prc_or_disc_4 >0 THEN prc_or_disc_4
		WHEN prc_or_disc_3 >0 THEN prc_or_disc_3
		WHEN prc_or_disc_2 >0 THEN prc_or_disc_2
		ELSE 0
	END,
prc_or_disc_3 = 0,
minimum_qty_3 = 0,
prc_or_disc_4 = 0,
minimum_qty_4 = 0,
prc_or_disc_5 = 0,
minimum_qty_5 = 0,
prc_or_disc_6 = 0,
minimum_qty_6 = 0,
prc_or_disc_7 = 0,
minimum_qty_7 = 0
FROM OEPRCFIL_SQL
WHERE     LTRIM(SUBSTRING(filler_0001, 1, 5))='EC'

The update statement works beautifully. Can anyone tell me what is wrong with my insert statement? I DEFINITELY want to make sure that I don't violate primary key rules. I occurs to me that I need to have some sort of NOT EXISTS code in there but I don't know how to do that when you are inserting records into the same table you are basing the insert on.

Thanks in advance for your help.

Christopher J. Poulin

 

[kscheeler]

The clustered index that has to be unique is based on the fields _tp, curr_cd, filler_0001, start_dt, end_dt. I would first verify that you don't have any records in the system with the new "G" code that you want. If you do and you would be replacing it with the insert statement you could delete the current G codes first and then run the insert statment.


Kevin Scheeler

 

[ChrisP520]

Hi Kvin:

Thanks for the speedy reply. I'm not looking to get rid of the G Customer Type codes. I want them to live harmoniously with the EC type price codes. The G type codes handle the bulk of my customer base. But there is a substatial enough number of End Column customers (EC type) to warrant this. Another benefit of this is that these End Column customers will be able to see their end column pricing on our web site (done through Birddog eCommerce).

So, obviously this means that I must have had an EC type code already living in the table and THAT was the duplicate I was seeing. Is that the way you see it?

Regards,

Chris Poulin

 

[kscheeler]

Yes, I think you have one in there already. My suggestion was to delete that one (or a few) and then rerun the insert statment

Kevin Scheeler

 

[ChrisP520]

Hello Kevin:

Thank you. I did what you suggested and it worked perfectly. Doing some record counts confirmed your suspicions. Although I don't recall having inserted a row of Type EC or creating through Macola (Thank god for test companies)

Anyway, one more question. I will of course need to keep the EC customer type codes synchronized with the G customer type codes. Is there a way for me to modify my query so that it only inserts NEWLY inserted codes? For instance, if I enter a new price code type 4 for a new product category, can I modify the query so that this new record can be copied to an EC customer type price code?

The eventuality of all this is that I'll proabably want to create a trigger or a combo of trigger and SP that will create a Self-Maintaining price code.

In this way, my people will only actually manage the Type 3 and Type 4 Customer Type G price codes and based on that, the Customer Type EC price codes will automatically maintain themselves based on the trigger. New inserts of G customer type codes will automatically insert an EC type code and G type updates will automatically update EC type codes.

It would be a very neat solution.

Regards,

Chris Poulin

 

[abaldwin]

If you are wanting to take the brain matter out of maintaining these price codes then might I suggest a trigger or three.

Trigger the price code table to update or insert records of the EC codes when a change or insert is done with a G type.

Should keep them synced.

Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[ChrisP520]

Hello Andy:

You have hit the nail right on the head. The ultimate goal has always been to create self maintaining Type 3 and Type 4 price codes for a new customer type called EC that would be based on Type 3 and Type 4 price codes for our Customer Type G.

This will really cut down on price code maintenance time as well as order entry time. In addition, it will allow our web customers to see their correct pricing on the web. We actually have a customer that will have this EC customer type that has over 700 locations that actively purchase from us. I'm sure you can imagine the time savings this will generate.

With that said, I have already created these triggers and have been testing them in a test company. If you wouldn't mind commenting, I have included those triggers below. If you see any glaring errors or ommissions in the code, I would appreciate your thoughts.

Best regards,


Christopher Poulin

----DELETE Trigger----

CREATE TRIGGER [dEndColDelete] ON [dbo].[OEPRCFIL_SQL] 
FOR DELETE 
AS
Delete from OEPRCFIL_SQL
where LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'EC' AND  
LTRIM(SUBSTRING(filler_0001, 6, 3)) = (select  LTRIM(SUBSTRING(filler_0001, 6, 3)) from deleted WHERE LTRIM(SUBSTRING(filler_0001, 1, 5))='G') AND
start_dt = (select start_dt from deleted WHERE start_dt >= 19900101) and
end_dt = (select end_dt from deleted) and
cd_tp = 4
OR
LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'EC' AND  
LTRIM(SUBSTRING(filler_0001, 6, 15)) = (select  LTRIM(SUBSTRING(filler_0001, 6, 15)) from deleted WHERE LTRIM(SUBSTRING(filler_0001, 1, 5))='G') AND
start_dt = (select start_dt from deleted WHERE start_dt >= 19900101) and
end_dt = (select end_dt from deleted) and
cd_tp = 3

----INSERT TRIGGER----

CREATE TRIGGER [iEndColInsert] ON [dbo].[OEPRCFIL_SQL] 
FOR INSERT 
AS
Insert into oeprcfil_sql
	(cd_tp,
	curr_cd,
	filler_0001,
	start_dt,
	end_dt,
	cd_prc_basis,
	minimum_qty_1,
	prc_or_disc_1,
	minimum_qty_2,
	prc_or_disc_2,
	minimum_qty_3,
	prc_or_disc_3,
	minimum_qty_4,
	prc_or_disc_4,
	minimum_qty_5,
	prc_or_disc_5,
	minimum_qty_6,
	prc_or_disc_6,
	minimum_qty_7,
	prc_or_disc_7,
	minimum_qty_8,
	prc_or_disc_8,
	minimum_qty_9,
	prc_or_disc_9,
	minimum_qty_10,
	prc_or_disc_10,
	comm_pct,
	filler_0004)
SELECT 
	cd_tp,
	curr_cd,
	CASE
		WHEN cd_tp = 3 AND LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'G'
			THEN 'EC   '+ LTRIM(SUBSTRING(filler_0001, 6, 15))
		WHEN cd_tp = 4 AND LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'G'
			THEN 'EC   '+ LTRIM(SUBSTRING(filler_0001, 6, 3))
	END,
	start_dt,
	end_dt,
	cd_prc_basis,
	minimum_qty_1,
	prc_or_disc_1,
	minimum_qty_2,
	CASE 
		WHEN prc_or_disc_10 >0 THEN prc_or_disc_10
		WHEN prc_or_disc_9 >0 THEN prc_or_disc_9
		WHEN prc_or_disc_8 >0 THEN prc_or_disc_8
		WHEN prc_or_disc_7 >0 THEN prc_or_disc_7
		WHEN prc_or_disc_6 >0 THEN prc_or_disc_6
		WHEN prc_or_disc_5 >0 THEN prc_or_disc_5
		WHEN prc_or_disc_4 >0 THEN prc_or_disc_4
		WHEN prc_or_disc_3 >0 THEN prc_or_disc_3
		WHEN prc_or_disc_2 >0 THEN prc_or_disc_2
	END,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	comm_pct,
	filler_0004
FROM INSERTED
WHERE cd_tp = 4 AND LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'G' OR cd_tp = 3 AND 
LTRIM(SUBSTRING(filler_0001, 1, 5)) = 'G' AND 
start_dt >= 19900101

----UPDATE TRIGGER----

CREATE TRIGGER [uEndColUpdate] ON [dbo].[OEPRCFIL_SQL] 
FOR UPDATE
AS
IF UPDATE (prc_or_disc_10) OR
UPDATE (prc_or_disc_9) OR
UPDATE (prc_or_disc_8) OR
UPDATE (prc_or_disc_7) OR
UPDATE (prc_or_disc_6) OR
UPDATE (prc_or_disc_5) OR
UPDATE (prc_or_disc_4) OR
UPDATE (prc_or_disc_3) OR
UPDATE (prc_or_disc_2) OR
UPDATE (prc_or_disc_1)

BEGIN
UPDATE OEPRCFIL_SQL
SET OEPRCFIL_SQL.prc_or_disc_2 = 
	CASE 
		WHEN inserted.prc_or_disc_10 >0 THEN inserted.prc_or_disc_10
		WHEN inserted.prc_or_disc_9 >0 THEN inserted.prc_or_disc_9
		WHEN inserted.prc_or_disc_8 >0 THEN inserted.prc_or_disc_8
		WHEN inserted.prc_or_disc_7 >0 THEN inserted.prc_or_disc_7
		WHEN inserted.prc_or_disc_6 >0 THEN inserted.prc_or_disc_6
		WHEN inserted.prc_or_disc_5 >0 THEN inserted.prc_or_disc_5
		WHEN inserted.prc_or_disc_4 >0 THEN inserted.prc_or_disc_4
		WHEN inserted.prc_or_disc_3 >0 THEN inserted.prc_or_disc_3
		WHEN inserted.prc_or_disc_2 >0 THEN inserted.prc_or_disc_2
		ELSE 0
	END,
OEPRCFIL_SQL.minimum_qty_2 = 1,
OEPRCFIL_SQL.prc_or_disc_3 = 0,
OEPRCFIL_SQL.minimum_qty_3 = 0,
OEPRCFIL_SQL.prc_or_disc_4 = 0,
OEPRCFIL_SQL.minimum_qty_4 = 0,
OEPRCFIL_SQL.prc_or_disc_5 = 0,
OEPRCFIL_SQL.minimum_qty_5 = 0,
OEPRCFIL_SQL.prc_or_disc_6 = 0,
OEPRCFIL_SQL.minimum_qty_6 = 0,
OEPRCFIL_SQL.prc_or_disc_7 = 0,
OEPRCFIL_SQL.minimum_qty_7 = 0,
OEPRCFIL_SQL.prc_or_disc_8 = 0,
OEPRCFIL_SQL.minimum_qty_8 = 0,
OEPRCFIL_SQL.prc_or_disc_9 = 0,
OEPRCFIL_SQL.minimum_qty_9 = 0,
OEPRCFIL_SQL.prc_or_disc_10 = 0,
OEPRCFIL_SQL.minimum_qty_10 = 0
FROM INSERTED
WHERE OEPRCFIL_SQL.start_dt = inserted.start_dt AND 
OEPRCFIL_SQL.end_dt = inserted.end_dt AND 
LTRIM(SUBSTRING(inserted.filler_0001, 1, 5)) = 'G' AND
LTRIM(SUBSTRING(OEPRCFIL_SQL.filler_0001, 1, 5)) = 'EC' AND
LTRIM(SUBSTRING(OEPRCFIL_SQL.filler_0001, 6, 3)) = LTRIM(SUBSTRING(inserted.filler_0001, 6, 3)) AND
inserted.cd_tp = 4
OR
OEPRCFIL_SQL.start_dt = inserted.start_dt AND 
OEPRCFIL_SQL.end_dt = inserted.end_dt AND 
LTRIM(SUBSTRING(inserted.filler_0001, 1, 5)) = 'G' AND
LTRIM(SUBSTRING(OEPRCFIL_SQL.filler_0001, 1, 5)) = 'EC' AND
LTRIM(SUBSTRING(OEPRCFIL_SQL.filler_0001, 6, 15)) = LTRIM(SUBSTRING(inserted.filler_0001, 6, 15)) AND
inserted.cd_tp = 3
END