Hi All,

The following SP gathers Purchase Orders data for processing them into the mainframe system.

Multiple POs could be processed in one upload file. Upload file needs to be in the following order:

record type '01' - line from PO header file (PurchaseOrders)
record type '02' - all detail lines from PO detail file (PurchaseOrderDetail)
record type '03' - all comment lines from PO comment file (PurchaseOrderComments)

As you can see from the code below the only reason I used the cursor is to order the records in the above mentioned way.
Is there a way to accomplish the same without the cursor?

DAniel

declare myCursor cursor for 
select PONo
from PurchaseOrders
where Batched = 1
and DateBatched = @ProcessDate

open myCursor

declare @PONO as bigint
declare @myTable as table(data varchar(424))

FETCH NEXT FROM myCursor into @PONO

WHILE @@FETCH_STATUS = 0
BEGIN
	insert into @myTable
	select CAST(
		cast('01' as char(2)) +
		cast(PONO as char(9)) +
		cast(replicate(space(1), 8 - LEN(LTRIM(RTRIM(VendNo)))) 
					+ LTrim(RTrim(Upper(VendNo))) as char(8)) +
		cast(space(2) as char(2)) +
		cast('01' as char(2)) +
		cast('02' as char(2)) +
		cast(replicate(0, 4 - LEN(REPLACE(RTrim(LTrim(TermPct)), '.', '')))
					+ REPLACE(RTrim(LTrim(TermPct)), '.', '') as char(4)) +
		cast(replicate(0, 4 - LEN(LTRIM(RTRIM(TermDays)))) 
					+ LTrim(RTrim(TermDays)) as char(4)) +
		cast(ShipMethod as char(2)) + 
		cast(LTrim(RTrim(Upper(City))) + replicate(space(1), 30 - LEN(LTRIM(RTRIM(City)))) as char(30)) +
		cast('19'+space(6) as char(8)) +
		convert(char(8), EntryDate ,112) +
		convert(char(8), RequiredDate ,112) +
		cast('01'+space(2) as char(4)) +
		cast('I' as char(1)) + 
		cast(LTRIM(RTRIM(STR(YEAR(GETDATE())+3))) + '0101' as char(8))  +
		cast(space(8) as char(8)) +
		cast(Warehouse as char(2)) +
		cast(space(2) as char(2)) +
		cast(space(2) as char(2)) +
		cast(space(2) as char(2)) +
		cast('01' as char(2)) +			--PAYMENT-CODE
		cast('000' as char(3)) +
		cast(space(1) as char(1)) +
		cast(space(4) as char(4)) +
		cast(space(296) as char(296))
	as char(424)) as data
	from PurchaseOrders
	where PONO = @PONO

	UNION ALL

	
	select CAST(
		cast('02' as char(2)) +
		cast(PONO as char(9)) +
		cast(replicate('0', 4 - LEN(LTRIM(RTRIM(LinNo)))) 
					+ LTrim(RTrim(Upper(LinNo))) as char(4)) +
		cast(LTrim(RTrim(Upper(ItemNo)))
					+ replicate(space(1), 20 - LEN(LTRIM(RTRIM(ItemNo)))) as char(20)) +
		cast(replicate('0', 9 - LEN(LTRIM(RTRIM(Qty)))) 
					+ LTrim(RTrim(Qty)) as char(9)) +
		convert(char(8), ReqDate ,112) +
		cast('01'+space(2) as char(4)) +
		cast(space(4) as char(4)) +
		cast('000000000' as char(9)) +
		CAST(replicate('0', 9 - len(Left(Price, CHARINDEX('.',Price)-1))) + Left(Price, CHARINDEX('.',Price)-1) +
			substring(LTrim(RTrim(Price)), CHARINDEX('.', Price) + 1, 2) + replicate('0', 6 - len(substring(LTrim(RTrim(Price)), CHARINDEX('.', Price) + 1, 2))) as char(15)) +
		cast(Replicate(0, 15) as char(15)) +	--LANDED-COST
		cast('A' as char(1)) +
		cast(space(1) as char(1)) +
		cast(space(1) as char(1)) +
		cast(space(322) as char(322))
	as char(424)) as data
	from PurchaseOrderDetail 
	where PONO = @PONO

	UNION ALL

	select CAST(
		cast('03' as char(2)) +
		cast(PONO as char(9)) +
		cast(replicate('0', 3 - LEN(LTRIM(RTRIM(LinNo)))) 
					+ LTrim(RTrim(LinNo)) as char(3)) +
		cast(replicate(space(1), 10) + LTrim(RTrim(Comment)) 
				+ replicate(space(1), 400 - LEN(LTRIM(RTRIM(Comment)))) as char(410))
	as char(424)) as data
	from PurchaseOrderComments a
	where PONO = @PONO	

   FETCH NEXT FROM myCursor into @PONO

END

select * from @myTable

CLOSE myCursor
DEALLOCATE myCursor
GO