CREATE PROCEDURE WRT_WARR_PTS_DIST2
@Create bit,	--DISPLAYS AS CHECK BOX
@RunMode int
--DECLARE VARIABLES
AS
DECLARE
@TKT_NO T_DOC_NO,
@RTYP T_FLG2,
@ORDATE T_DAT,
@PRCVDATE T_DAT,
@ROPAMT T_MONEY,
@PAMT T_MONEY,
@P1TYP T_FLG,
@PINVCHK T_DOC_NO,
@ROPVEND T_VEND_NO,
@PVEND T_VEND_NO,
@PPOSTED T_FLG,
@PRCVDATE2 T_DAT,
@ROPAMT2 T_MONEY,
@PAMT2 T_MONEY,
@P2TYP T_FLG,
@PINVCHK2 T_DOC_NO,
@ROPVEND2 T_VEND_NO,
@PVEND2 T_VEND_NO,
@PPOSTED2 T_FLG,
@RFNDACCT T_ACCT_NO,
@PACCRUAL T_ACCT_NO,
@LACCRUAL T_ACCT_NO,
@LRCVDATE T_DAT,
@ROLAMT T_MONEY,
@LAMT T_MONEY,
@LTYP T_FLG,
@LINVCHK T_DOC_NO,
@ROLVEND T_VEND_NO,
@LVEND T_VEND_NO,
@LPOSTED T_FLG,
@VENDFNAM T_FST_NAM,
@VENDFNAMU T_FST_NAM,
@VENDLNAM T_LST_NAM,
@VENDLNAMU T_LST_NAM,
@VENDNAM T_NAM,
@VENDNAMU T_NAM,
@LOCALE T_LOC_ID,
@TODAY T_DAT,
@SEQNO T_SEQ_NO,
@SQL T_SQL_QUERY
--find today's date
set @TODAY = convert(datetime, convert(varchar(10), getdate(), 120))
--SET OUR SEQUENCE NUMBER FOR SY_DIST. THIS NUMBER IS SEQUENCIAL
--FOR EACH EVENT ID. SINCE EACH EVENT ID IS INDIVIDUAL FROM THIS SP,
--THE VALUE 1 IS ALL THAT IS NEEDED (PUT IN FOR FUTURE USE)
SET @SEQNO = 1
--SET OUR  ACCOUNTING INFO--
SET @RFNDACCT=(SELECT VEND_REFUND_ACT FROM SY_COMP)
SET @PACCRUAL=(SELECT PARTS_ACCRUAL_ACCT FROM SY_COMP)
SET @LACCRUAL=(SELECT LABOR_ACCRUAL_ACCT FROM SY_COMP)
SET NOCOUNT ON
BEGIN
SET @SQL='
SELECT
WARR_RQST_DATA.TKT_NO,WARR_RQST_DATA.RQST_TYP,WARR_RQST_DATA.ORIG_RQST_DATE,WARR_PARTS_RCVD.DATE_PARTS_RCVD,
WARR_RQST_DATA.ORIG_PARTS_AMT,WARR_PARTS_RCVD.ACT_PARTS_AMT,WARR_PARTS_RCVD.PARTS_RCV_TYP,
WARR_PARTS_RCVD.PARTS_INV_CHK_NO,WARR_RQST_DATA.ORIG_PARTS_VEND,
WARR_PARTS_RCVD.ACT_PARTS_VEND,WARR_RQST_DATA.PARTS_POSTED,WARR_PARTS_RCVD.DATE_PARTS_RCVD2,
WARR_RQST_DATA.ORIG_PARTS_AMT2,WARR_PARTS_RCVD.ACT_PARTS_AMT2,WARR_PARTS_RCVD.PARTS_RCV_TYP2,
WARR_PARTS_RCVD.PARTS_INV_CHK_NO2,WARR_RQST_DATA.ORIG_PARTS_VEND2,
WARR_PARTS_RCVD.ACT_PARTS_VEND2,WARR_RQST_DATA.PARTS_POSTED2,WARR_LABOR_RCVD.DATE_LABOR_RCVD,
WARR_RQST_DATA.ORIG_LABOR_AMT,WARR_LABOR_RCVD.ACT_LABOR_AMT,WARR_LABOR_RCVD.LABOR_RCV_TYP,
WARR_LABOR_RCVD.LABOR_INV_CHK_NO,WARR_RQST_DATA.ORIG_LABOR_VEND,
WARR_LABOR_RCVD.ACT_LABOR_VEND,WARR_RQST_DATA.LABOR_POSTED,PS_TKT_HIST.STK_LOC_ID

FROM
WARR_RQST_DATA LEFT OUTER JOIN WARR_PARTS_RCVD
ON WARR_RQST_DATA.TKT_NO=WARR_PARTS_RCVD.TKT_NO
LEFT OUTER JOIN WARR_LABOR_RCVD
ON WARR_RQST_DATA.TKT_NO=WARR_LABOR_RCVD.TKT_NO
LEFT OUTER JOIN PS_TKT_HIST
ON WARR_RQST_DATA.TKT_NO=PS_TKT_HIST.TKT_NO'
if @RunMode=0
set @SQL = @SQL + ' where 1=0'
/* ======================================================================================= */
/* Create Distributions. This is done with a cursor since the SEQ_NO must be  */
/* incremented for each row written to the table and must be unique.        */

  if @Create = 1 
  begin
    set @SEQNO = 1
    exec ('declare WARR_CURSOR cursor for ' + @SQL)
    open WARR_CURSOR
    fetch next from WARR_CURSOR 
      into @TKT_NO,@RTYP,@ORDATE,@PRCVDATE,@ROPAMT,@PAMT,@P1TYP,@PINVCHK,@ROPVEND,
@PVEND,@PPOSTED,@PRCVDATE2,@ROPAMT2,@PAMT2,@P2TYP,@PINVCHK2,@ROPVEND2,
@PVEND2,@PPOSTED2,@LRCVDATE,@ROLAMT,@LAMT,@LTYP,@LINVCHK,
@ROLVEND,@LVEND,@LPOSTED,@LOCALE

    while @@FETCH_STATUS = 0
    begin

/* set sequence number and vendor info as we loop */
      set @SEQNO = @SEQNO

-- START DOING OUR WORK

--INSERT OUR P1 CASH REFUNDS
IF @P1TYP = 1 AND @PPOSTED='N'
BEGIN
insert into SY_DIST(DIST_GRP,DIST_TYP,ACCT_NO,DIST_DAT,SEQ_NO,EVENT_NO,AMT,FST_DOC_NO,REC_CNT,PKG_ID,ACT_TYP)
VALUES (2,'B',@RFNDACCT,@PRCVDATE,@SEQNO,'P'+@TKT_NO,@PAMT,@PINVCHK,1,'AR','PMT')

insert into SY_DIST(DIST_GRP,DIST_TYP,ACCT_NO,DIST_DAT,SEQ_NO,EVENT_NO,AMT,FST_DOC_NO,REC_CNT,PKG_ID,ACT_TYP)
VALUES (2,'B',@PACCRUAL,@PRCVDATE,@SEQNO,'P'+@TKT_NO,(-@PAMT),@PINVCHK,1,'AR','PMT')
END
--INSERT OUR  P1 CREDIT REFUNDS
IF @P1TYP =2 AND @PPOSTED='N'
BEGIN
SELECT @VENDNAM= NAM FROM PO_VEND WHERE VEND_NO=@PVEND
SELECT  @VENDFNAM= FST_NAM FROM PO_VEND WHERE VEND_NO=@PVEND
SELECT @VENDFNAMU= FST_NAM_UPR FROM PO_VEND WHERE VEND_NO=@PVEND
SELECT @VENDLNAM= LST_NAM FROM PO_VEND WHERE VEND_NO=@PVEND
SELECT @VENDLNAMU= LST_NAM_UPR FROM PO_VEND WHERE VEND_NO=@PVEND
SELECT @VENDNAMU= NAM_UPR FROM PO_VEND WHERE VEND_NO=@PVEND
INSERT INTO
PO_RECVR_HIST
(RECVR_NO,EVENT_NO,BAT_ID,VEND_NO,VEND_FST_NAM,VEND_FST_NAM_UPR,VEND_LST_NAM,VEND_LST_NAM_UPR,
VEND_NAM,VEND_NAM_UPR,RECVR_SUB_TOT,RECVR_TOT,RECVR_DAT,RECVR_LOC_ID,VCHR_NO,VCHR_VEND_NO,
VCHR_INVC_NO,VCHR_INVC_DAT,VCHR_STAT,IS_ALLOC)
VALUES
('P'+@TKT_NO,'P'+@TKT_NO,'Z',@PVEND,@VENDFNAM,@VENDFNAMU,@VENDLNAM,@VENDLNAMU,@VENDNAM,
@VENDNAMU,-@PAMT,-@PAMT,@TODAY,@LOCALE,'P'+@TKT_NO,@PVEND,@PINVCHK,@PRCVDATE,'!','N')
END

--UPDATE THE P1  POSTING FLAGS
IF @P1TYP>=1
BEGIN
UPDATE WARR_RQST_DATA
SET PARTS_POSTED = 'Y'
WHERE TKT_NO=@TKT_NO
END



--INSERT OUR P2 CASH REFUNDS
IF @P2TYP = 1 AND @PPOSTED2='N'
BEGIN
insert into SY_DIST(DIST_GRP,DIST_TYP,ACCT_NO,DIST_DAT,SEQ_NO,EVENT_NO,AMT,FST_DOC_NO,REC_CNT,PKG_ID,ACT_TYP)
VALUES (2,'B',@RFNDACCT,@PRCVDATE,@SEQNO,'2P'+@TKT_NO,@PAMT2,@PINVCHK,1,'AR','PMT')

insert into SY_DIST(DIST_GRP,DIST_TYP,ACCT_NO,DIST_DAT,SEQ_NO,EVENT_NO,AMT,FST_DOC_NO,REC_CNT,PKG_ID,ACT_TYP)
VALUES (2,'B',@PACCRUAL,@PRCVDATE,@SEQNO,'2P'+@TKT_NO,(-@PAMT2),@PINVCHK,1,'AR','PMT')
END

--INSERT OUR P2 CREDIT REFUNDS
IF @P2TYP= 2 AND @PPOSTED2='N'
BEGIN
SELECT @VENDNAM= NAM FROM PO_VEND WHERE VEND_NO=@PVEND2
SELECT @VENDFNAM= FST_NAM FROM PO_VEND WHERE VEND_NO=@PVEND2
SELECT @VENDFNAMU= FST_NAM_UPR FROM PO_VEND WHERE VEND_NO=@PVEND2
SELECT @VENDLNAM= LST_NAM FROM PO_VEND WHERE VEND_NO=@PVEND2
SELECT @VENDLNAMU= LST_NAM_UPR FROM PO_VEND WHERE VEND_NO=@PVEND2
SELECT @VENDNAMU= NAM_UPR FROM PO_VEND WHERE VEND_NO=@PVEND2
INSERT INTO
PO_RECVR_HIST
(RECVR_NO,EVENT_NO,BAT_ID,VEND_NO,VEND_FST_NAM,VEND_FST_NAM_UPR,VEND_LST_NAM,VEND_LST_NAM_UPR,
VEND_NAM,VEND_NAM_UPR,RECVR_SUB_TOT,RECVR_TOT,RECVR_DAT,RECVR_LOC_ID,VCHR_NO,VCHR_VEND_NO,
VCHR_INVC_NO,VCHR_INVC_DAT,VCHR_STAT,IS_ALLOC)
VALUES
('2P'+@TKT_NO,'2P'+@TKT_NO,'Z',@PVEND2,@VENDFNAM,@VENDFNAMU,@VENDLNAM,@VENDLNAMU,@VENDNAM,
@VENDNAMU,-@PAMT,-@PAMT,@TODAY,@LOCALE,'2P'+@TKT_NO,@PVEND,@PINVCHK,@PRCVDATE,'!','N')
END

--UPDATE THE P2 POSTING FLAGS
IF @P2TYP>=1
BEGIN
UPDATE WARR_RQST_DATA
SET PARTS_POSTED2 = 'Y'
WHERE TKT_NO=@TKT_NO
END


--INSERT OUR  LABOR CASH REFUNDS
IF @LTYP=1 AND @LPOSTED='N'
BEGIN
INSERT INTO SY_DIST(DIST_GRP,DIST_TYP,ACCT_NO,DIST_DAT,SEQ_NO,EVENT_NO,AMT,FST_DOC_NO,REC_CNT,PKG_ID,ACT_TYP)
VALUES (2,'B',@RFNDACCT,@LRCVDATE,@SEQNO,'L'+@TKT_NO,@LAMT,@LINVCHK,1,'AR','PMT')

INSERT INTO SY_DIST(DIST_GRP,DIST_TYP,ACCT_NO,DIST_DAT,SEQ_NO,EVENT_NO,AMT,FST_DOC_NO,REC_CNT,PKG_ID,ACT_TYP)
VALUES (2,'B',@LACCRUAL,@LRCVDATE,@SEQNO,'L'+@TKT_NO,(-@LAMT),@LINVCHK,1,'AR','PMT')
END
--INSERT OUR  LABOR CREDIT REFUNDS
IF @LTYP=2 AND @LPOSTED = 'N'
BEGIN
SELECT @VENDNAM= NAM FROM PO_VEND WHERE VEND_NO=@LVEND
SELECT @VENDFNAM= FST_NAM FROM PO_VEND WHERE VEND_NO=@LVEND
SELECT @VENDFNAMU= FST_NAM_UPR FROM PO_VEND WHERE VEND_NO=@LVEND
SELECT @VENDLNAM= LST_NAM FROM PO_VEND WHERE VEND_NO=@LVEND
SELECT @VENDLNAMU= LST_NAM_UPR FROM PO_VEND WHERE VEND_NO=@LVEND
SELECT @VENDNAMU= NAM_UPR FROM PO_VEND WHERE VEND_NO=@LVEND
INSERT INTO
PO_RECVR_HIST
(RECVR_NO,EVENT_NO,BAT_ID,VEND_NO,VEND_FST_NAM,VEND_FST_NAM_UPR,VEND_LST_NAM,VEND_LST_NAM_UPR,
VEND_NAM,VEND_NAM_UPR,RECVR_SUB_TOT,RECVR_TOT,RECVR_DAT,RECVR_LOC_ID,VCHR_NO,VCHR_VEND_NO,
VCHR_INVC_NO,VCHR_INVC_DAT,VCHR_STAT,IS_ALLOC)
VALUES
('L'+@TKT_NO,'L'+@TKT_NO,'Z',@LVEND,@VENDFNAM,@VENDFNAMU,@VENDLNAM,@VENDLNAMU,@VENDNAM,
@VENDNAMU,-@LAMT,-@LAMT,@TODAY,@LOCALE,'L'+@TKT_NO,@LVEND,@LINVCHK,@LRCVDATE,'!','N')
END

--UPDATE THE LABOR POSTING FLAGS
IF @LTYP>=1
BEGIN
UPDATE WARR_RQST_DATA
SET LABOR_POSTED = 'Y'
WHERE TKT_NO=@TKT_NO
END

--UPDATE THE HISTORY TABLE WITH OUR COMPLETED ORDERS--
IF EXISTS(Select top 1 'fast check' from WARR_HIST 
                            where TKT_NO=@TKT_NO)
BEGIN
UPDATE WARR_HIST
SET RQST_TYP=@RTYP,ORIG_RQST_DATE=@ORDATE,ORIG_PARTS_VEND=@ROPVEND,
 ACT_PARTS_VEND=@PVEND,ORIG_PARTS_AMT=@ROPAMT,ACT_PARTS_AMT=@PAMT,PARTS_RCV_TYP=@P1TYP,
PARTS_INV_CHK_NO=@PINVCHK,DATE_PARTS_RCVD=@PRCVDATE,ORIG_LABOR_VEND=@ROLVEND,
ACT_LABOR_VEND=@LVEND,ORIG_LABOR_AMT=@ROLAMT,ACT_LABOR_AMT=@LAMT,LABOR_RCV_TYP=@LTYP,
LABOR_INV_CHK_NO=@LINVCHK,DATE_LABOR_RCVD=@LRCVDATE,ORIG_PARTS_VEND2=@ROPVEND2,
 ACT_PARTS_VEND2=@PVEND2,ORIG_PARTS_AMT2=@ROPAMT2,ACT_PARTS_AMT2=@PAMT2,
PARTS_RCV_TYP2=@P2TYP,PARTS_INV_CHK_NO2=@PINVCHK2,DATE_PARTS_RCVD2=@PRCVDATE2
where tkt_no=@tkt_no
END
else
IF NOT EXISTS(Select top 1 'fast check' from WARR_HIST 
                            where TKT_NO=@TKT_NO)
BEGIN
INSERT INTO WARR_HIST
(TKT_NO,RQST_TYP,ORIG_RQST_DATE,ORIG_PARTS_VEND, ACT_PARTS_VEND,ORIG_PARTS_AMT,ACT_PARTS_AMT,
PARTS_RCV_TYP,PARTS_INV_CHK_NO,DATE_PARTS_RCVD,ORIG_LABOR_VEND,
ACT_LABOR_VEND,ORIG_LABOR_AMT,ACT_LABOR_AMT,LABOR_RCV_TYP,LABOR_INV_CHK_NO,
DATE_LABOR_RCVD,ORIG_PARTS_VEND2, ACT_PARTS_VEND2,ORIG_PARTS_AMT2,ACT_PARTS_AMT2,
PARTS_RCV_TYP2,PARTS_INV_CHK_NO2,DATE_PARTS_RCVD2)
VALUES
( @TKT_NO,@RTYP,@ORDATE,@ROPVEND,@PVEND,@ROPAMT,@PAMT,@P1TYP,@PINVCHK,@PRCVDATE,
@ROLVEND,@LVEND,@ROLAMT,@LAMT,@LTYP,@LINVCHK,@LRCVDATE,@ROPVEND2,@PVEND2,
@ROPAMT2,@PAMT2,@P2TYP,@PINVCHK2,@PRCVDATE2)
END

--ADVANCE THE CURSOR-
fetch next from WARR_CURSOR 
        into @TKT_NO,@RTYP,@ORDATE,@PRCVDATE,@ROPAMT,@PAMT,@P1TYP,@PINVCHK,@ROPVEND,
@PVEND,@PPOSTED,@PRCVDATE2,@ROPAMT2,@PAMT2,@P2TYP,@PINVCHK2,@ROPVEND2,
@PVEND2,@PPOSTED2,@LRCVDATE,@ROLAMT,@LAMT,@LTYP,@LINVCHK,
@ROLVEND,@LVEND,@LPOSTED,@LOCALE
    end
    close WARR_CURSOR
    deallocate WARR_CURSOR
  end

if @RunMode = 1 
print @SQL
execute(@SQL)
--DELETE OUR HISTORICAL TRANSACTIONS FROM THE CURRENT TABLES--
DELETE WARR_PARTS_RCVD
	FROM
		WARR_RQST_DATA JOIN WARR_PARTS_RCVD 
			ON WARR_PARTS_RCVD.TKT_NO = WARR_RQST_DATA.TKT_NO
JOIN WARR_LABOR_RCVD ON WARR_LABOR_RCVD.TKT_NO=WARR_RQST_DATA.TKT_NO
	WHERE (RQST_TYP=10 AND PARTS_POSTED='Y') OR(RQST_TYP=20 AND PARTS_POSTED='Y' AND PARTS_POSTED2='Y')
OR(RQST_TYP=30 AND LABOR_POSTED='Y') OR(RQST_TYP=40 AND PARTS_POSTED='Y' AND LABOR_POSTED='Y')
OR(RQST_TYP=50 AND PARTS_POSTED='Y' AND PARTS_POSTED2='Y' AND LABOR_POSTED='Y')
	
	DELETE WARR_LABOR_RCVD
	FROM
		WARR_RQST_DATA
JOIN WARR_LABOR_RCVD ON WARR_LABOR_RCVD.TKT_NO=WARR_RQST_DATA.TKT_NO
	WHERE (RQST_TYP=30 AND LABOR_POSTED='Y') OR(RQST_TYP=40 AND LABOR_POSTED='Y')
OR(RQST_TYP=50 AND LABOR_POSTED='Y')
		
	DELETE WARR_RQST_DATA
	WHERE (RQST_TYP=10 AND PARTS_POSTED='Y') OR(RQST_TYP=20 AND PARTS_POSTED='Y' AND PARTS_POSTED2='Y')
OR(RQST_TYP=30 AND LABOR_POSTED='Y') OR(RQST_TYP=40 AND PARTS_POSTED='Y' AND LABOR_POSTED='Y')
OR(RQST_TYP=50 AND PARTS_POSTED='Y' AND PARTS_POSTED2='Y' AND LABOR_POSTED='Y')




END
GO

BEGIN TRANSACTION
	DELETE WARR_PARTS_RCVD
	FROM
		WARR_PARTS_RCVD JOIN WARR_RQST_DAT 
			ON WARR_PARTS_RCVD.TKT_NO = WARR_RQST_DATA.TKT_NO
	WHERE
		......
	
	DELETE WARR_LABOR_RCVD
	FROM
		WARR_PARTS_RCVD JOIN WARR_RQST_DAT 
			ON WARR_LABOR_RCVD.TKT_NO = WARR_RQST_DATA.TKT_NO
	WHERE
		......
	
	DELETE WARR_RQST_DAT
	WHERE
		......
COMMIT TRANSACTION

DELETE WARR_PARTS_RCVD
FROM
	WARR_PARTS_RCVD JOIN WARR_RQST_DAT 
		ON WARR_PARTS_RCVD.TKT_NO = WARR_RQST_DATA.TKT_NO
WHERE
	WARR_PARTS_RCVD.TKT_NO ='234-21-2009'

DELETE p
FROM
	WARR_PARTS_RCVD AS p JOIN WARR_RQST_DAT AS rqt
		ON p.TKT_NO = rqt.TKT_NO
WHERE
	p.TKT_NO ='234-21-2009'

DELETE FROM WARR_REQUEST_DATA
WHERE...

DELETE WARR_RQST_DATA
FROM...
WHERE...