ALTER PROC PURCHASE_HISTORY1          
(          
@item_code char(16)          
)          
AS          
BEGIN          
      
CREATE TABLE #PUR_HIS1        
(        
SL_NUM  INT,        
VENDOR_CODE CHAR(18),        
VENDOR_DESC VARCHAR(55),        
PO_NO  CHAR(18),      
RATE  NUMERIC(28,8),        
ORD_QTY  NUMERIC(28,8),        
RECV_QTY NUMERIC(28,8),        
CR_DATE  DATETIME      
)        
        
INSERT INTO #PUR_HIS1(SL_NUM,VENDOR_CODE,PO_NO,RATE,ORD_QTY,RECV_QTY,CR_DATE)        
        
SELECT ROW_NUMBER() OVER (ORDER BY HDR.PO_DATE desc) AS NUMBER,      
HDR.VENDOR_CODE, DTL.PO_NO, DTL.RATE, DTL.ORDERED_QTY_PUOM, DTL.RECEIVED_QTY_PUOM, HDR.PO_DATE     
FROM PUR_PO_DETAIL DTL, PUR_PO_HEADER HDR          
WHERE DTL.STOCK_NO = @ITEM_CODE          
AND DTL.PO_NO = HDR.PO_NO          
ORDER BY HDR.PO_DATE desc        
          
UPDATE #PUR_HIS1          
SET VENDOR_DESC = a.VENDOR_NAME        
FROM COMMON..PUR_COMPANY_VENDOR_MASTER a, #PUR_HIS1 b        
WHERE a.VENDOR_CODE = b.VENDOR_CODE     
    
SELECT * FROM #PUR_HIS1     

CREATE TABLE #PUR_HIS_FNL        
(       
VENDOR_CODE CHAR(18),        
VENDOR_DESC VARCHAR(55),        
RATE  NUMERIC(28,8),        
DATE  DATETIME,    
ORD_QTY  NUMERIC(28,8),        
RECV_QTY NUMERIC(28,8)    
)        
    
INSERT INTO #PUR_HIS_FNL(VENDOR_CODE, VENDOR_DESC, RATE, DATE, ORD_QTY, RECV_QTY)     
    
SELECT a.VENDOR_CODE, a.VENDOR_DESC, a.RATE, a.CR_DATE, 0, 0
FROM #PUR_HIS1 a LEFT OUTER JOIN #PUR_HIS1 b ON a.SL_NUM = b.SL_NUM + 1    
WHERE b.SL_NUM IS NULL OR b.RATE <> a.RATE    
    
SELECT * FROM #PUR_HIS_FNL     
     
END