March 28, 2003 at 9:42 am
Hello All,
A Crystal reports writer by training, so I'm new to all this so please bear with me. I have a request to create a stored procedure that will read lines of text to determine if the invoice number, invoice amount, and customer account are valid for each line. (Only interested in the lines that start with '6' or '4'. Where the '4's' are an overflow from the line '6' above. They identify with the previous line '6' with positions 2-8.) This text file is being bulk inserted into a table as varchar(80) on the database. (See below).
50490000102182030320Company 0061000104
604900100005060551130112500003437490000011774102410 +0000506055
60490020002719536021309370601859655000013778631926 +0002719536
604900300004891460724017800096624640000013267318511 +0000489146
60490040023875174061112780099783524009630253331997 +0023527774
404900460196613 +00002316606540CM -00000694346613CM -0000007830
6JOD002000014167501107515920000156600000123461003 +0000033797
4JOD00260191004 +00000832071014 +0000024671
The lines that start with '6' have an inv num, inv amt and customer acct number. The '4' lines can have up to 3 inv numbers and amounts but no customer acct number.
I need to verify that the invoice number(s) matches an inv number in the invoice table, the invoice amounts are correct, and that the customer account number exists in the customer table. If any of the criteria is not met (e.g. inv_num <> inv_no) then notify user which criteria was not met (Print @inv_num + ' is an invalid invoice number').
Thanks for any suggestions on helping me get started because my brain is swirling,
JD
P.s. Lines '6' - cust act position 26 to 35;
inv num position 46 to 54;
inv amt position 55 to 65
Lines '4' - inv num 1 position 12 to 20;
inv amt 1 position 21 to 31;
inv num 2 position 32 to 40;
inv amt 2 position 41 to 51;
inv num 3 position 52 to 60;
inv amt 3 position 61 to 71
March 31, 2003 at 3:03 am
You can use the substring & cast functions to extract the data from the varchar columns as the positions of the data appear to be fixed. You could then extract the data into temp tables and query those against your customer & invoice tables
April 2, 2003 at 11:54 am
Thanks! Here is what I did. Probably not the best way. Created a .bat file for the end-users to get the results in an excel spreadsheet.
if object_id('dbo.test_lockbox') is not null
drop procedure dbo.test_lockbox
go
drop table jd_lockbox_table
drop table results
go
CREATE PROCEDURE test_lockbox
AS
SET NOCOUNT ON
create table jd_lockbox_table (table_key varchar(6), cus_acct varchar(10),
inv_num1 varchar(10), inv_amt1 varchar(11),
inv_num2 varchar(10), inv_amt2 varchar(11),
inv_num3 varchar(10), inv_amt3 varchar(11),
inv_num4 varchar(10), inv_amt4 varchar(11) )
insert into jd_lockbox_table
select SUBSTRING(lockbox, 2, 6),
case substring(lockbox,1,1)
when 6 then SUBSTRING(lockbox, 26, 10)
end,
case substring(lockbox,1,1)
when 6 then rtrim(substring(lockbox, 46, 9))
end,
case substring(lockbox,1,1)
when 6 then substring(lockbox, 55, 11)
end,
case substring(lockbox,1,1)
when 4 then rtrim(SUBSTRING(lockbox, 12, 9))
end,
case substring(lockbox,1,1)
when 4 then SUBSTRING(lockbox, 21, 11)
end,
case substring(lockbox,1,1)
when 4 then rtrim(SUBSTRING(lockbox, 32, 9))
end,
case substring(lockbox,1,1)
when 4 then SUBSTRING(lockbox, 41, 11)
end,
case substring(lockbox,1,1)
when 4 then rtrim(SUBSTRING(lockbox, 52, 9))
end,
case substring(lockbox,1,1)
when 4 then SUBSTRING(lockbox, 61, 11)
end
from jd_test
where substring(lockbox, 1, 1) in ('4', '6')
--update line 4's that have '' as inv_num and inv_amt's
update jd_lockbox_table
set inv_num3 = Null
where inv_num3 = ''
update jd_lockbox_table
set inv_amt3 = Null
where inv_amt3 = ''
update jd_lockbox_table
set inv_num4 = Null
where inv_num4 = ''
update jd_lockbox_table
set inv_amt4 = Null
where inv_amt4 = ''
--remove A's and CM's from Invoice numbers
update jd_lockbox_table
set inv_num1 = case right(inv_num1, 1)
when 'A' then left(rtrim(inv_num1),len(rtrim(inv_num1))-1)
when 'M' then left(rtrim(inv_num1),len(rtrim(inv_num1))-2)
end
where inv_num1 like '%A'
or inv_num1 like '%CM'
update jd_lockbox_table
set inv_num2 = case right(inv_num2, 1)
when 'A' then left(rtrim(inv_num2),len(rtrim(inv_num2))-1)
when 'M' then left(rtrim(inv_num2),len(rtrim(inv_num2))-2)
end
where inv_num2 like '%A'
or inv_num2 like '%CM'
update jd_lockbox_table
set inv_num3 = case right(inv_num3, 1)
when 'A' then left(rtrim(inv_num3),len(rtrim(inv_num3))-1)
when 'M' then left(rtrim(inv_num3),len(rtrim(inv_num3))-2)
end
where inv_num3 like '%A'
or inv_num3 like '%CM'
update jd_lockbox_table
set inv_num4 = case right(inv_num4, 1)
when 'A' then left(rtrim(inv_num4),len(rtrim(inv_num4))-1)
when 'M' then left(rtrim(inv_num4),len(rtrim(inv_num4))-2)
end
where inv_num4 like '%A'
or inv_num4 like '%CM'
create table results ( cus_acct varchar(10), inv_num varchar(10),
inv_amt varchar(11), reason varchar(80) )
--Check if Cus_Acct number is in the lockbox_customer table
insert into results
select distinct cus_acct, inv_num1, inv_amt1, 'Customer Account Number is not on file'
from jd_lockbox_table
where cus_acct not in (select cus_acct from lockbox_customers
where cus_acct is not null)
order by 1
--find line 6's that don't match invoice number or proforma numbers
insert into results
select max(cus_acct), inv_num1, inv_amt1, 'Invoice Number is not valid'
from jd_lockbox_table
where inv_num1 not in (select inv_no
from invoice )
and inv_num1 not in (select ord_no
from ord
where sxop_pk = 'Renewal')
group by inv_num1, inv_amt1
order by 1, 2
--find line 6's whose invoice status is not open
insert into results
select max(cus_acct), inv_num1, inv_amt1, 'Invoice Status is Closed'
from jd_lockbox_table
where inv_num1 in (select inv_no
from invoice
where inv_status = 'P')
or inv_num1 in (select ord_no
from ord
where sxop_pk = 'Renewal'
and ord_status = 'C')
group by inv_num1, inv_amt1
order by 1, 2
--find line 6's that don't match the invoice amt
insert into results
select max(cus_acct), inv_num1, inv_amt1, 'Invoice Amount is not valid'
from jd_lockbox_table
where (inv_num1 in (select inv_no
from invoice)
or inv_num1 in (select ord_no
from ord
where sxop_pk = 'Renewal'))
and (cast(inv_amt1 as money) not in (select inv_total_amt
from invoice )
and cast(inv_amt1 as money) not in (select ord_total_amt
from ord
where sxop_pk = 'Renewal'))
group by inv_num1, inv_amt1
order by 1, 2
--find line 4's that don't match invoice number2
insert into results
select max(p.cus_acct), c.inv_num2, c.inv_amt2, 'Invoice Number is not valid'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and c.inv_num2 not in (select inv_no
from invoice )
and c.inv_num2 not in (select ord_no
from ord
where sxop_pk = 'Renewal')
group by c.inv_num2, c.inv_amt2
order by 1, 2
--find line 4's whose invoice number2 is not open
insert into results
select max(p.cus_acct), c.inv_num2, c.inv_amt2, 'Invoice Status is Closed'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and c.inv_num2 in (select inv_no
from invoice
where inv_status = 'P')
or c.inv_num2 in (select ord_no
from ord
where sxop_pk = 'Renewal'
and ord_status = 'C')
group by c.inv_num2, c.inv_amt2
order by 1, 2
--find line 4's that don't match invoice amount2
insert into results
select max(p.cus_acct), c.inv_num2, c.inv_amt2, 'Invoice Amount is not valid'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and (c.inv_num2 in (select inv_no
from invoice)
or c.inv_num2 in (select ord_no
from ord
where sxop_pk = 'Renewal'))
and (cast(c.inv_amt2 as money) not in (select inv_total_amt
from invoice )
and cast(c.inv_amt2 as money) not in (select ord_total_amt
from ord
where sxop_pk = 'Renewal'))
group by c.inv_num2, c.inv_amt2
order by 1, 2
--find line 4's that don't match invoice number3
insert into results
select max(p.cus_acct), c.inv_num3, c.inv_amt3, 'Invoice Number is not valid'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and (c.inv_num3 not in (select inv_no
from invoice )
and c.inv_num3 not in (select ord_no
from ord
where sxop_pk = 'Renewal'))
group by c.inv_num3, c.inv_amt3
order by 1, 2
--find line 4's whose invoice number3 is not open
insert into results
select max(p.cus_acct), c.inv_num3, c.inv_amt3, 'Invoice Status is Closed'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and c.inv_num2 in (select inv_no
from invoice
where inv_status = 'P')
or c.inv_num2 in (select ord_no
from ord
where sxop_pk = 'Renewal'
and ord_status = 'C')
group by c.inv_num3, c.inv_amt3
order by 1, 2
--find line 4's that don't match invoice amount3
insert into results
select max(p.cus_acct), c.inv_num3, c.inv_amt3, 'Invoice Amount is not valid'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and (c.inv_num3 in (select inv_no
from invoice)
or c.inv_num3 in (select ord_no
from ord
where sxop_pk = 'Renewal'))
and (cast(c.inv_amt3 as money) not in (select inv_total_amt
from invoice )
and cast(c.inv_amt3 as money) not in (select ord_total_amt
from ord
where sxop_pk = 'Renewal'))
group by c.inv_num3, c.inv_amt3
order by 1, 2
--find line 4's that don't match invoice number4
insert into results
select max(p.cus_acct), c.inv_num4, c.inv_amt4, 'Invoice Number is not valid'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and (c.inv_num4 not in (select inv_no
from invoice )
and c.inv_num4 not in (select ord_no
from ord
where sxop_pk = 'Renewal'))
group by c.inv_num4, c.inv_amt4
order by 1, 2
--find line 4's whose invoice number4 is not open
insert into results
select max(p.cus_acct), c.inv_num4, c.inv_amt4, 'Invoice Status is Closed'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and c.inv_num2 in (select inv_no
from invoice
where inv_status = 'P')
or c.inv_num2 in (select ord_no
from ord
where sxop_pk = 'Renewal'
and ord_status = 'C')
group by c.inv_num4, c.inv_amt4
order by 1, 2
--find line 4's that don't match invoice amount4
insert into results
select max(p.cus_acct), c.inv_num4, c.inv_amt4, 'Invoice Amount is not valid'
from jd_lockbox_table c, jd_lockbox_table p
where c.table_key = p.table_key
and (c.inv_num4 in (select inv_no
from invoice)
or c.inv_num4 in (select ord_no
from ord
where sxop_pk = 'Renewal'))
and (cast(c.inv_amt4 as money) not in (select inv_total_amt
from invoice )
and cast(c.inv_amt4 as money) not in (select ord_total_amt
from ord
where sxop_pk = 'Renewal'))
group by c.inv_num4, c.inv_amt4
order by 1, 2
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply