ALTER PROCEDURE TEST_DATES
AS
CREATE TABLE TEMP_TABLE_DATES(
WONUM			VARCHAR(10),
STK_KEY			VARCHAR(11),
FSC			VARCHAR(4),
PONUM			VARCHAR(20),
REQUEST_QTY		FLOAT,
BAL_DUE_QTY		FLOAT,
EST_PARTS_COST		MONEY,
REQUEST_DATE		VARCHAR(20),
PO_DATE			DATETIME,
PR_DATE			DATETIME,
EXPECTED_DLVR_DATE	DATETIME,
REQUIRED_DLVR_DATE	VARCHAR(3),
REQUESTOR		VARCHAR(25),
STK_KEY_DESC		VARCHAR(25),
DOC_NR_DATE 		VARCHAR(3),
DOC_NR_YEAR		VARCHAR(1),
NEW_DATE		DATETIME)

INSERT INTO TEMP_TABLE_DATES(
	WONUM,
	STK_KEY,
	FSC,
	PONUM,
	REQUEST_QTY,
	BAL_DUE_QTY,
	EST_PARTS_COST,
	REQUEST_DATE,
	PO_DATE,
	PR_DATE,
	EXPECTED_DLVR_DATE,
	REQUIRED_DLVR_DATE,
	REQUESTOR,
	STK_KEY_DESC,
	DOC_NR_DATE,
	DOC_NR_YEAR,
	NEW_DATE)
SELECT DO.WRK_ORD AS WORK_ORDER, 
	DO.STK_KEY AS STOCK_KEY,
	PD.FSC AS FED_STOCK_CODE,
	PR.PONUM AS PO_NUM,
	DO.D_O_ORIG_QTY AS REQ_QTY,
	DO.DUE_OUT_QTY AS BAL_DUE,
	CONVERT(DECIMAL(10,2),PM.UNIT_PRC * DO.D_O_ORIG_QTY) AS EST_COST,
	SUBSTRING(REQ_TM_DT,1,8) AS REQ_DATE,
	PO.ORDERDATE AS PURCHASE_ORD_DATE,
	PR.DATEGENERATED AS PURCHASE_REQ_DATE,
	PR.DUEDATE AS EXP_DLVR_DATE,
	CONVERT(INT,D_O_REQ_DLV_DT) AS REQ_DLVR_DATE,
	SUBSTRING(REQ_TM_DT,10,24) AS REQUESTOR_NAME,
	PD.STK_KEY_DESC AS STOCK_KEY_DESC,
	CONVERT(INT,SUBSTRING(DUE_OUT_DOC_NR,8,3)) AS DOC_NR_DATE,
	CONVERT(INT,SUBSTRING(DUE_OUT_DOC_NR,7,1)) AS DOC_NR_YEAR,
	NULL
FROM LOSCSOCS..SC.PMDUEOUT DO 
	LEFT JOIN LOSCMP2.MP2.dbo.PURREQ PR
	ON DO.DUE_IN_DOC_NR = PR.REQUISITIONNUM 
		JOIN LOSCSOCS..SC.PMISSUE PM
		ON DO.DUE_OUT_DOC_NR = PM.DOC_NR 
			JOIN PART_DESCRIPTIONS PD
			ON PD.STK_KEY = DO.STK_KEY
				LEFT JOIN LOSCMP2.MP2.dbo.POHEADER PO
				ON PO.PONUM = PR.PONUM

DECLARE TEST_DATE CURSOR
FOR
SELECT 	WONUM,
	STK_KEY,
	FSC,
	PONUM,
	REQUEST_QTY,
	BAL_DUE_QTY,
	EST_PARTS_COST,
	REQUEST_DATE,
	PO_DATE,
	PR_DATE,
	EXPECTED_DLVR_DATE,
	REQUIRED_DLVR_DATE,
	REQUESTOR,
	STK_KEY_DESC,
	DOC_NR_DATE,
	DOC_NR_YEAR,
	NEW_DATE 
FROM TEMP_TABLE_DATES 
FOR UPDATE

/*OPEN THE CURSOR */
OPEN TEST_DATE
DECLARE
@WONUM 			VARCHAR(10),		
@STK_KEY		VARCHAR(11),
@FSC			VARCHAR(4),
@PONUM			VARCHAR(20),			
@REQUEST_QTY		FLOAT,
@BAL_DUE_QTY		FLOAT,		
@EST_PARTS_COST		MONEY,
@REQUEST_DATE		VARCHAR(20),	
@PO_DATE		DATETIME,	
@PR_DATE		DATETIME,		
@EXPECTED_DLVR_DATE 	DATETIME,
@RDD			INT,
@REQUESTOR		VARCHAR(25),
@STK_KEY_DESC		VARCHAR(24),
@DOC_NR_DATE		INT,	
@DOC_NR_YEAR		INT,
@NEW_DATE		DATETIME

FETCH NEXT FROM TEST_DATE INTO 
	@WONUM,
	@STK_KEY,	
	@FSC,		
	@PONUM,			
	@REQUEST_QTY,
	@BAL_DUE_QTY,		
	@EST_PARTS_COST,
	@REQUEST_DATE,	
	@PO_DATE,	
	@PR_DATE,		
	@EXPECTED_DLVR_DATE,
	@RDD,
	@REQUESTOR,
	@STK_KEY_DESC,
	@DOC_NR_DATE, 
	@DOC_NR_YEAR,
	@NEW_DATE
WHILE @@FETCH_STATUS = 0 BEGIN

IF @RDD = 999
	SET @NEW_DATE = CONVERT(DATETIME,'01/01/1900')
	
ELSE IF @RDD >= @DOC_NR_DATE
	SET @NEW_DATE = DATEADD(DAY,@RDD,'12/31/' + 
	CONVERT(VARCHAR,@DOC_NR_YEAR + 1999))
ELSE 
	SET @NEW_DATE = DATEADD(DAY,@RDD,'12/31/' + 
	CONVERT(VARCHAR,@DOC_NR_YEAR + 2000))

UPDATE TEMP_TABLE_DATES 
SET NEW_DATE = @NEW_DATE

FETCH NEXT FROM TEST_DATE INTO 
	@WONUM,
	@STK_KEY,	
	@FSC,		
	@PONUM,			
	@REQUEST_QTY,
	@BAL_DUE_QTY,		
	@EST_PARTS_COST,
	@REQUEST_DATE,	
	@PO_DATE,	
	@PR_DATE,		
	@EXPECTED_DLVR_DATE,
	@RDD,
	@REQUESTOR,
	@STK_KEY_DESC,
	@DOC_NR_DATE, 
	@DOC_NR_YEAR,
	@NEW_DATE
END

CLOSE TEST_DATE
DEALLOCATE TEST_DATE

update temp_table_dates
set new_date = 
case when rdd = 999 then convert(datetime,'01/01/1900')
when rdd<> 999 and rdd >= doc_nr_date then dateadd(day,rdd,'12/31/' + convert(varchar,doc_nr_year + 1999))
else and dateadd(day,rdd,'12/31/' + convert(varchar,doc_nr_year + 2000))
end

select do.wrk_ord as wonum, 
	do.stk_key as stk_key,
	pd.fsc as fsc,
	pr.ponum as po_num,
	do.d_o_orig_qty as request_qty,
	do.due_out_qty as bal_due_qty,
	convert(decimal(10,2),pm.unit_prc * do.d_o_orig_qty) as est_parts_cost,
	substring(req_tm_dt,1,8) as request_date,
	po.orderdate as po_date,
	pr.dategenerated as pr_date,
	pr.duedate as expected_dlvr_date,
	convert(int,d_o_req_dlv_dt) as required_dlvr_date,
	substring(req_tm_dt,10,24) as requestor,
	pd.stk_key_desc as stk_key_desc,
	convert(int,substring(due_out_doc_nr,8,3)) as doc_nr_date,
	convert(int,substring(due_out_doc_nr,7,1)) as doc_nr_year,

new_date = case when rdd = 999 then convert(datetime,'01/01/1900')
when rdd<> 999 and rdd >= doc_nr_date then dateadd(day,@rdd,'12/31/' + convert(varchar,@doc_nr_year + 1999))
else and dateadd(day,@rdd,'12/31/' + convert(varchar,@doc_nr_year + 2000))
end

into temp_table_dates

from loscsocs..sc.pmdueout do 
	left join loscmp2.mp2.dbo.purreq pr
	on do.due_in_doc_nr = pr.requisitionnum 
		join loscsocs..sc.pmissue pm
		on do.due_out_doc_nr = pm.doc_nr 
			join part_descriptions pd
			on pd.stk_key = do.stk_key
				left join loscmp2.mp2.dbo.poheader po
				on po.ponum = pr.ponum