- no title specified

Index

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Client: ###### ######## Products

-- Contact: ##### #########

-- Author: John Mahady, B###### Consultant

-- Create date: 2013-Apr-22

-- Description: Used by IP Monitor custom Query

-- =============================================

-- NOTES:

-- sample to run in MS-SSMS :

-- EXEC XX_sp_ABreport_sumORdetail 'I','41336','41336','ALL','ALL','A',10,'B','S'

-- use TOP 200 in client environment to limit processing time

-- example: SELECT TOP 200 doc_id FROM docs

-- USE THIS TO SEE SQL STATEMENT ON SUMMARY PAGE (ENABLE LAST LINE IN DISPLAY SELECT ): SET @sqlTEST = @sql;

-- USE THIS TO SEE SQL ON DETAIL RESULTS: SELECT @sql AS 'test';

-- =============================================

-- MODIFICATIONS AFTER ROLLOUT:

-- 4-22-13 Changed title for 'c' row to distinguish meaning of 2c from 4c as per phone call with Glen and Garth.

-- =============================================

ALTER PROCEDURE [dbo].[XX_sp_ABreport_sumORdetail]

@invOrScanDate NVARCHAR(1) /** I=invoice_date S=scan_date **/

,@startdate INT /** number of days from 1900-january-1 **/

,@enddate INT /** number of days from 1900-january-1 **/

,@suppNbr NVARCHAR(5) /** from selection list **/

,@apteam NVARCHAR(5) /** from selection list **/

,@AOrPVMatch NVARCHAR(1) /** (A)bsolute or (P)ercent Variance **/

,@variThreshold INT /** number for BUCKET variance grouping **/

,@invStatusInclude NVARCHAR(1) /** default (B)oth,(R)eady,(T)ransferred **/

,@SumOrDetail NVARCHAR(7)

------------------------------------------------

BEGIN

------------------------------------------------

DECLARE /** private vars **/

@sql NVARCHAR(4000)

,@sqlTEST NVARCHAR(4000)

-- @vStartDT DATETIME /** NOT NEEDED FOR DYNAMIC SQL. ONLY USE WITH LITERAL SELECT STATEMENTS. **/

-- ,@vEndDT DATETIME /** NOT NEEDED FOR DYNAMIC SQL. ONLY USE WITH LITERAL SELECT STATEMENTS. **/

,@TOTrecs NVARCHAR(50)

,@TOTnbr FLOAT

,@SelCountFrom NVARCHAR(4000)

,@SelRecordsFrom NVARCHAR(4000)

,@SeldocidRecordsFrom NVARCHAR(4000)

,@SelCntDocsAndOrders NVARCHAR(4000)

,@SelRecDocsAndOrders NVARCHAR(4000)

,@SelDocRecUseOrders NVARCHAR(4000)

,@allFlds NVARCHAR(2000)

,@allFldsIncludeSumCols NVARCHAR(2000)

,@a NVARCHAR(10)

,@nolck NVARCHAR(20)

,@InvoiceTypeIS NVARCHAR(500)

,@InvoiceStatusIn NVARCHAR(500)

,@InvOrScanFldname NVARCHAR(50)

,@WhereParamsStr NVARCHAR(250)

,@PoCnt INT

,@OM100 INT ,@ExOM100 INT ,@VarOM100 INT ,@OM100Diff INT

,@whatIfTxt NVARCHAR(200)

,@WhatifSuccess INT

,@WhatifFailed INT

,@WhatifDiff INT

,@OMfailed INT

,@OMmanual INT ,@ExOMmanual INT ,@VarOMmanual INT ,@OMmanualDiff INT

,@Anomalous INT

,@nonPoCnt INT

,@ReadyInv INT

,@TransInv INT

,@OtherInv INT

,@totInvCnt INT

,@recCnt INT

,@AorP NVARCHAR(1000)

,@AorPfail NVARCHAR(1000)

,@ordByVar NVARCHAR(1000)

,@thresholdasfloat FLOAT

,@invSumEqualToOrdSum NVARCHAR(1000)

,@invSumNOTequalToOrdSum NVARCHAR(1000)

SET @whatIfTxt = 'What if '

IF (@AOrPVMatch = 'P')

SET @whatIfTxt = @whatIfTxt + CAST(@variThreshold AS NVARCHAR(500)) + ' %'

ELSE

SET @whatIfTxt = @whatIfTxt + ' $ ' + CAST(@variThreshold AS NVARCHAR(500))

/** THIS CODE SETS SUBCLAUSE FOR ABSOLUTE OR PERCENTAGE VARIANCE IN ROW d AND 2D LISTING **/

SET @thresholdasfloat = CAST(@variThreshold AS FLOAT)

SET @thresholdasfloat = 1 + (@thresholdasfloat/100)

IF (@AOrPVMatch = 'P')

BEGIN

SET @AorP = ' AND I.invoice_sum <= '+ CAST(@thresholdasfloat AS NVARCHAR(20)) + ' * O.ODA_SUM '

SET @AorPfail = ' AND I.invoice_sum > '+ CAST(@thresholdasfloat AS NVARCHAR(20)) + ' * O.ODA_SUM '

SET @ordByVar = ' ORDER BY ''DIFF'' DESC '

END

ELSE

BEGIN

SET @AorP = 'AND ABS(I.invoice_sum - O.ODA_SUM) <= ' + + CAST(@variThreshold AS NVARCHAR(20));

SET @AorPfail = 'AND ABS(I.invoice_sum - O.ODA_SUM) > ' + + CAST(@variThreshold AS NVARCHAR(20));

SET @ordByVar = ''

END

-- NOT NEEDED FOR DYNAMIC SQL. ONLY USE WITH LITERAL SELECT STATEMENTS.

-- SET @vStartDT = CONVERT(DATETIME,@startdate);

-- SET @vEndDT = CONVERT(DATETIME,@enddate);

/** REPLACE ASTERICK WITH FIELD NAMES. WHEN USING COUNT(I.*) DON'T USE @allFlds VARIABLE TO AVOID POSSIBLE ERROR **/

SET @allFlds = ' I.* '

-- SET @allFlds = ' I.supplier_num, I.invoice_num, I.invoice_date, I.invoice_sum, I.status_index, I.order_num, I.attrib_t2, I.attrib_t3, I.invoice_type, '

-- +' I.match_status_index, I.mc_match_status_index '

-- CHANGED ONLY SO DIFF SHOWS DOLLARS AND CENTS. 2013-3-18

-- SET @allFldsIncludeSumCols = ' I.invoice_sum AS ''invsum'', O.ODA_SUM AS ''ordsum'',ABS(ROUND(I.invoice_sum - O.ODA_SUM ,-1)) AS ''DIFF'' ,* '

SET @allFldsIncludeSumCols = ' I.invoice_sum AS ''invsum'', O.ODA_SUM AS ''ordsum'',ABS(ROUND(I.invoice_sum - O.ODA_SUM ,2)) AS ''DIFF'' ,* '

SET @a = ' AND '

SET @nolck = '' -- try these if heavy user traffic causes buffer or locking issues WITH(NOLOCK) OR WITH(READPAST)

SET @SelCountFrom = 'SELECT @recCnt=COUNT(I.doc_id) FROM docs I '+ @nolck; /** @recCnt = RETURN OUT VALUE FROM sp_executesql IN SUMMARY REPORTS **/

SET @SelRecordsFrom = 'SELECT '+@allFlds+' FROM docs I '+ @nolck; /** USED FOR DETAIL INVOICE LISTING OUTPUT **/

SET @SeldocidRecordsFrom = 'SELECT I.* FROM docs I '+ @nolck; /** USED FOR DETAIL INVOICE LISTING OUTPUT **/

SET @SelCntDocsAndOrders = ' SELECT @recCnt=COUNT(I.doc_id) FROM docs I, PM_ORDER_DATA O '+ @nolck;

-- SET @SelRecDocsAndOrders = ' SELECT '+@allFlds+' FROM docs I, PM_ORDER_DATA O ' /** SEE DISABLED @allFlds SETTING ABOVE **/

SET @SelRecDocsAndOrders = ' SELECT '+@allFldsIncludeSumCols+' FROM docs I, PM_ORDER_DATA O '+ @nolck;

SET @SelDocRecUseOrders = ' SELECT '+@allFlds+' FROM docs I, PM_ORDER_DATA O '+ @nolck; /** USED IN 1F **/

SET @InvoiceTypeIS = ' I.invoice_type = ''PO'' '

IF (@invStatusInclude = 'B')

SET @InvoiceStatusIn = ' I.status_index IN (2,3) '

ELSE IF (@invStatusInclude = 'R')

SET @InvoiceStatusIn = ' I.status_index IN (2) '

ELSE IF (@invStatusInclude = 'T')

SET @InvoiceStatusIn = ' I.status_index IN (3) '

ELSE

BEGIN

SELECT 'Invoice Status choices: R = ready -- T = transferred invoices -- B = both 2 and 3' AS 'ERROR'

RETURN -1

END

/** which field is date range based on? **/

IF (@invOrScanDate = 'I') SET @InvOrScanFldname = 'I.invoice_date'

ELSE IF (@invOrScanDate = 'S') SET @InvOrScanFldname = 'I.scan_date'

ELSE

BEGIN

SELECT 'Please enter I = Invoice Date OR S = Scan Date' AS 'ERROR'

RETURN -1

END

/** ON ERROR SHOW MESSAGE AND STOP PROCESSING **/

IF (@AOrPVMatch NOT IN ('A','P'))

BEGIN

SELECT 'For Variance please enter A = Absolute dollar amount or P = Percentage' AS 'ERROR'

RETURN -1

END

/** MAKE SURE VALID PERCENTAGE BETWEEN 0 AND 100 OR SHOW ERROR MESSAGE AND STOP PROCESSING **/

IF @AOrPVMatch = 'P' AND NOT (CAST(@variThreshold AS INT) >= 0 AND CAST(@variThreshold AS INT) <= 100)

BEGIN

SELECT 'For Variance bucket Percentages a valid number is between 0 and 100' AS 'ERROR'

RETURN -1

END

/** standard WHERE clause construction **/

/** BETWEEN BAD WITH DATE RANGES ON DATETIME FIELDS. USE GREATER THAN ETC. **/

-- SET @WhereParamsStr = ' '+@InvOrScanFldname+' BETWEEN '+CONVERT(NVARCHAR(10),@startdate) +@a+ CONVERT(VARCHAR(10),@enddate)+' '

SET @WhereParamsStr = ' '+@InvOrScanFldname+' >= '+CONVERT(NVARCHAR(10),@startdate) +@a+ @InvOrScanFldname+' < '+CONVERT(VARCHAR(10),@enddate+1)+' '

IF (@suppNbr <> 'ALL') SET @WhereParamsStr = @WhereParamsStr +@a+ ' I.supplier_num = '''+@suppNbr+''' '

IF (@apteam <> 'ALL') SET @WhereParamsStr = @WhereParamsStr +@a+ ' I.attrib_t3 LIKE ''%'+ @apteam +'%'' '

/** TO GET ACCURATE COMPARISON OF VALUES ITS NECESSARY TO CAST SINCE invoice_sum IS NUMERIC(19,6) AND ODA_SUM IS FLOAT **/

SET @invSumEqualToOrdSum = ' CAST(I.invoice_sum AS MONEY) = CAST(O.ODA_SUM AS MONEY) '

SET @invSumNOTequalToOrdSum = ' CAST(I.invoice_sum AS MONEY) <> CAST(O.ODA_SUM AS MONEY) '

-- THIS DOESN'T WORK YET. TO HANDLE MULTIPLE CODES NEED TO PARSE THE COMMA SEPARATED VALUES AND WRAP QUOTES AROUND EACH.

-- IF (@apteam <> 'ALL') SET @WhereParamsStr = @WhereParamsStr +@a+ ' attrib_t3 IN ''('+ @apteam +')'' '

/*************************************/

/**********************/

/** Summary Report ***/

/********************/

IF (@SumOrDetail = 'S')

BEGIN

/*** TOTAL OF ALL PO Invoices ****/

-------1a PoCnt

SET @sql = @SelCountFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@PoCnt output;

-------1b

-------1c

---------1d WhatifSuccess

SET @sql = @SelCntDocsAndOrders +

' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +

' AND I.order_num = O.ODA_ORDER_NO '+ @AorP +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@WhatifSuccess output;

---------1e WhatifFailure ONLY DIFFERENCE IS @AorP REPLACED WITH @AorPfail

SET @sql = @SelCntDocsAndOrders +

' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +

' AND I.order_num = O.ODA_ORDER_NO '+ @AorPfail +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@WhatifFailed output;

---------1f WhatifDiff

/** @WhatifDiff SEE CALC VALUES SECTION BELOW **/

/*** PO INVOICES THAT WERE 100% AUTO-MATCHED ****/

-------2a OM100

SET @sql = @SelCntDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@OM100 output;

-------2b ExOM100

SET @sql = @SelCntDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumEqualToOrdSum +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@ExOM100 output;

-------2c VarOM100

SET @sql = @SelCntDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

-- CASTING REQUIRED BECAUSE INVOICE IS NUMERIC AND ORD SUM IS FLOAT

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumNOTequalToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@VarOM100 output;

-------2d

-------2e

---------2f AutomatchDifference

/** @OM100Diff SEE CALC VALUES SECTION BELOW **/

/*******/

/*** PO INVOICES THAT FAILED AUTO-MATCHING ****/

-------3a OMfailed

SET @sql = @SelCountFrom + ' WHERE ( I.match_status_index IN ( 2, 3, 4, 5,6, 7, 8, 9, 10, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45,

46, 47, 50, 51, 60, 80, 81, 82, 83, 84, 85, 86, 87, 90, 91, 94,95, 97, 101, 102, 103, 104,106 )

OR ( I.match_status_index = 99 AND ( I.order_num = '' ''

OR I.order_num = '''' OR I.order_num IS NULL ) ) ) AND (I.mc_match_status_index = 0 or I.mc_match_status_index is NULL) '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@OMfailed output;

-------3b

-------3c

-------3e

-------3f

/*** PO INVOICES THAT WERE MATCHED MANUALLY IN THE CLIENT ****/

-------4a OMmanual

SET @sql = @SelCountFrom + ' WHERE I.mc_match_status_index = 1 '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@OMmanual output;

------- 4b ExOMmanual

SET @sql = @SelCntDocsAndOrders +

' WHERE (I.mc_match_status_index = 1) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumEqualToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@ExOMmanual output;

------- 4c VarOMmanual

SET @sql = @SelCntDocsAndOrders +

' WHERE (I.mc_match_status_index = 1) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumNOTequalToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@VarOMmanual output;

------- 4d

------- 4e

---------4f WhatifDiff

SET @OMmanualDiff = @OMmanual - (@ExOMmanual + @VarOMmanual)

/*** INVOICES UNACCOUNTED FOR OR ANOMALOUS. MAKES PO TOTAL = OM + OMFAILED + MC ****/

-------5a

/** @Anomalous SEE CALC VALUES SECTION BELOW **/

-------5b

-------5c

-------5d

-------5f

/*** INVOICES READY TO BE TRANSFERRED ****/

-------6a

IF (@invStatusInclude IN ('B','R'))

BEGIN

SET @sql = @SelCountFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL AND I.status_index = 2 '

+@a+ @InvoiceTypeIS +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@ReadyInv output;

END

ELSE

SET @ReadyInv = 0

-------6b

-------6c

-------6d

-------6e

-------6f

/*** INVOICES TRANSFERRED TO ERP ****/

-------7a

/**/

IF (@invStatusInclude IN ('B','T'))

BEGIN

SET @sql = @SelCountFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL AND I.status_index = 3 '

+@a+ @InvoiceTypeIS +@a+ @WhereParamsStr

EXEC sp_executesql @sql,N'@recCnt INT output',@TransInv output;

END

ELSE

SET @TransInv = 0

-------7b

-------7c

-------7d

-------7e

-------7f

/*** OTHER INVOICES ****/

-------8a

/** @OtherInv SEE CALC VALUES SECTION BELOW **/

-------8b

-------8c

-------8d

-------8e

-------8f

/*** TOTAL OF ALL INVOICES ****/

-------9a

SET @sql = @SelCountFrom + ' WHERE ' + @InvoiceStatusIn +@a+ @WhereParamsStr /** NO INVOICE TYPE FILTER **/

EXEC sp_executesql @sql,N'@recCnt INT output',@totInvCnt output;

-------9b

-------9c

-------9d

-------9e

-------9f

------- SET CALC VALUES FIRST BEFORE DISPLAYING

-- 1F @WhatifDiff

SET @WhatifDiff = @PoCnt - (@WhatifSuccess + @WhatifFailed)

-- 2F @OM100Diff

SET @OM100Diff = @OM100 - (@ExOM100 + @VarOM100)

-- 5A @Anomalous

SET @Anomalous = @PoCnt - @OM100 - @OMfailed - @OMmanual

-- 8A @OtherInv

SET @OtherInv = @totInvCnt - @PoCnt

------- DISPLAY VARIABLES IN THE SUMMARY GRID

SELECT

'a. Summary' AS "Title"

,REPLACE(CONVERT(VARCHAR,CAST(@PoCnt as MONEY),1), '.00','') AS "1. Total PO Invoices"

,REPLACE(CONVERT(VARCHAR,CAST(@OM100 as MONEY),1), '.00','') AS "2. Auto Match"

,REPLACE(CONVERT(VARCHAR,CAST(@OMfailed as MONEY),1), '.00','') AS "3. OM failed"

,REPLACE(CONVERT(VARCHAR,CAST(@OMmanual as MONEY),1), '.00','') AS "4. Manual Match"

,REPLACE(CONVERT(VARCHAR,CAST(@Anomalous as MONEY),1), '.00','') AS "5. Anomalous Invoices"

,REPLACE(CONVERT(VARCHAR,CAST(@ReadyInv as MONEY),1), '.00','') AS "6. PO Ready for Transfer"

,REPLACE(CONVERT(VARCHAR,CAST(@TransInv as MONEY),1), '.00','') AS "7. PO Transferred"

,REPLACE(CONVERT(VARCHAR,CAST(@OtherInv as MONEY),1), '.00','') AS "8. Other Invoices"

,REPLACE(CONVERT(VARCHAR,CAST(@totInvCnt as MONEY),1), '.00','') AS "9. Total Invoices"

-- ,@sqlTEST AS 'TEST' /** TURN THIS ON TO EXAMINE SELECT STRING CREATED FROM sp_executesql **/

UNION

SELECT

'b. Exact Match'

,''

,REPLACE(CONVERT(VARCHAR,CAST(@ExOM100 as MONEY),1), '.00','')

,''

,REPLACE(CONVERT(VARCHAR,CAST(@ExOMmanual as MONEY),1), '.00','')

,'' ,'' ,'' ,'' ,''

-- ,'' /** TURN THIS ON ONLY IF @sqlTEST TURNED ON SO COLUMNS MATCH IN 3 ROWS **/

UNION

SELECT

'c. Tolerance/Variance Match'

,''

,REPLACE(CONVERT(VARCHAR,CAST(@VarOM100 as MONEY),1), '.00','')

,''

,REPLACE(CONVERT(VARCHAR,CAST(@VarOMmanual as MONEY),1), '.00','')

,'' ,'' ,'' ,'' ,''

-- ,'' /** TURN THIS ON ONLY IF @sqlTEST TURNED ON SO COLUMNS MATCH IN 3 ROWS **/

UNION

SELECT

'd. '+@whatIfTxt+' Matched'

,REPLACE(CONVERT(VARCHAR,CAST(@WhatifSuccess as MONEY),1), '.00','')

,''

,'' ,'' ,'' ,'' ,''

-- ,'' /** TURN THIS ON ONLY IF @sqlTEST TURNED ON SO COLUMNS MATCH IN 3 ROWS **/

UNION

SELECT

'e. '+@whatIfTxt+' Failed'

,REPLACE(CONVERT(VARCHAR,CAST(@WhatifFailed as MONEY),1), '.00','')

,''

,'' ,'' ,'' ,'' ,''

-- ,'' /** TURN THIS ON ONLY IF @sqlTEST TURNED ON SO COLUMNS MATCH IN 3 ROWS **/

UNION

SELECT

'f. Difference'

,REPLACE(CONVERT(VARCHAR,CAST(@WhatifDiff as MONEY),1), '.00','')

,REPLACE(CONVERT(VARCHAR,CAST(@OM100Diff as MONEY),1), '.00','')

,''

,REPLACE(CONVERT(VARCHAR,CAST(@OMmanualDiff as MONEY),1), '.00','')

,'' ,'' ,'' ,'' ,''

-- ,'' /** TURN THIS ON ONLY IF @sqlTEST TURNED ON SO COLUMNS MATCH IN 3 ROWS **/

/**********************/

/** Detail Reports ***/

/********************/

/*******/

END ELSE IF (@SumOrDetail = '1A')

BEGIN

SET @sql = @SelRecordsFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '1D')

BEGIN

---------1d WhatifSuccess

SET @sql = @SelRecDocsAndOrders +

' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +

' AND I.order_num = O.ODA_ORDER_NO '+ @AorP +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '1E')

BEGIN

---------1e WhatifFailure ONLY DIFFERENCE IS @AorP REPLACED WITH @AorPfail

SET @sql = @SelRecDocsAndOrders +

' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +

' AND I.order_num = O.ODA_ORDER_NO '+ @AorPfail +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '1F')

BEGIN

---------1f WhatifDifference

SET @sql = ' SELECT * FROM ( '

SET @sql = @sql + @SelDocRecUseOrders + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' EXCEPT ('

SET @sql = @sql + @SelDocRecUseOrders + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +

' AND I.order_num = O.ODA_ORDER_NO '+ @AorP +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' UNION '

SET @sql = @sql + @SelDocRecUseOrders + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +

' AND I.order_num = O.ODA_ORDER_NO '+ @AorPfail +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' ) ) as TBL '

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '2A')

BEGIN

SET @sql = @SelRecDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '2B')

BEGIN

------- ExOM100

SET @sql = @SelRecDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumEqualToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '2C')

BEGIN

------- VarOM100

SET @sql = @SelRecDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

-- CASTING REQUIRED BECAUSE INVOICE IS NUMERIC AND ORD SUM IS FLOAT

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumNOTequalToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '2F')

BEGIN

---------2f AutomatchDifference

SET @sql = ' SELECT * FROM ( '

SET @sql = @sql + @SelRecDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' EXCEPT ('

SET @sql = @sql + @SelRecDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumEqualToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' UNION '

SET @sql = @sql + @SelRecDocsAndOrders +

' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumNOTequalToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' ) ) AS tbl '

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '3A')

BEGIN

SET @sql = @SelRecordsFrom +

' WHERE ( I.match_status_index IN ( 2, 3, 4, 5,6, 7, 8, 9, 10, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45,

46, 47, 50, 51, 60, 80, 81, 82, 83, 84, 85, 86, 87, 90, 91, 94,95, 97, 101, 102, 103, 104,106 )

OR ( I.match_status_index = 99 AND ( I.order_num = '' ''

OR I.order_num = '''' OR I.order_num IS NULL ) ) ) AND (I.mc_match_status_index = 0 or I.mc_match_status_index is NULL) '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '4A')

BEGIN

SET @sql = @SelRecordsFrom + ' WHERE I.mc_match_status_index = 1 ' +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '4B')

BEGIN

------- ExOMmanual

SET @sql = @SelRecDocsAndOrders +

' WHERE (I.mc_match_status_index = 1) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumEqualToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

-- SELECT @sql AS 'test';

/*******/

END ELSE IF (@SumOrDetail = '4C')

BEGIN

------- VarOMmanual

SET @sql = @SelRecDocsAndOrders +

' WHERE (I.mc_match_status_index = 1) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumNOTequalToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '4CV')

BEGIN

------- VarOMmanualGrouping by Absolute or Percentage

SET @sql = 'SELECT TOP 200 BUCKET,COUNT(I.doc_id) AS ''Count'' FROM

(

SELECT ABS(ROUND(I.invoice_sum - O.ODA_SUM +5,-1)) AS ''BUCKET''

FROM docs I, PM_ORDER_DATA O

WHERE I.order_num = O.ODA_ORDER_NO AND I.invoice_sum - O.ODA_SUM <= 100.0 AND I.invoice_sum - O.ODA_SUM >= -10.0 '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

+ ') AS tbl

GROUP BY BUCKET

ORDER BY BUCKET ASC '

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '4F')

BEGIN

SET @sql = ' SELECT * FROM ( '

SET @sql = @sql + @SeldocidRecordsFrom + ' WHERE I.mc_match_status_index = 1 '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' EXCEPT ('

SET @sql = @sql + @SelDocRecUseOrders +

' WHERE (I.mc_match_status_index = 1) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumEqualToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' UNION '

SET @sql = @sql + @SelDocRecUseOrders +

' WHERE (I.mc_match_status_index = 1) ' +

' AND I.order_num = O.ODA_ORDER_NO ' +@a+ @invSumNOTequalToOrdSum+

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' ) ) as tbl '

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '5A')

BEGIN

/*** INVOICES UNACCOUNTED FOR OR ANOMALOUS. MAKES PO TOTAL = OM + OMFAILED + MC ****/

SET @sql = ' SELECT * FROM ( '

SET @sql = @sql + @SeldocidRecordsFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL ' +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' EXCEPT ('

SET @sql = @sql + @SeldocidRecordsFrom + ' WHERE (I.match_status_index = 1) AND (I.mc_match_status_index = 0 OR I.mc_match_status_index is NULL) ' +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' UNION '

SET @sql = @sql + @SeldocidRecordsFrom + ' WHERE ( I.match_status_index IN ( 2, 3, 4, 5,6, 7, 8, 9, 10, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45,

46, 47, 50, 51, 60, 80, 81, 82, 83, 84, 85, 86, 87, 90, 91, 94,95, 97, 101, 102, 103, 104,106 )

OR ( I.match_status_index = 99 AND ( I.order_num = '' ''

OR I.order_num = '''' OR I.order_num IS NULL ) ) ) AND (I.mc_match_status_index = 0 or I.mc_match_status_index is NULL) ' +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' UNION '

SET @sql = @sql + @SeldocidRecordsFrom + ' WHERE I.mc_match_status_index = 1 ' +@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' ) ) as TBL '

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '6A')

BEGIN

SET @sql = @SelRecordsFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL AND I.status_index = 2 ' +@a+ @InvoiceTypeIS +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '7A')

BEGIN

SET @sql = @SelRecordsFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL AND I.status_index = 3 ' +@a+ @InvoiceTypeIS +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '8A')

BEGIN

/** WAS BEFORE 2013-MAR-5

-- SET @sql = @SelRecordsFrom + ' WHERE I.match_status_index <> 1 AND I.order_num <> '''' AND I.order_num IS NOT NULL ' +

-- ' AND I.invoice_type IS NULL OR I.invoice_type IN (''CM'',''EXPB'',''EXPC'') ' +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

**/

SET @sql = @SelRecordsFrom + ' WHERE ' + @InvoiceStatusIn +@a+ @WhereParamsStr /** ALL INVOICES **/

SET @sql = @sql + ' EXCEPT ( '

SET @sql = @sql + @SelRecordsFrom + ' WHERE I.order_num <> '''' AND I.order_num IS NOT NULL '

+@a+ @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr

SET @sql = @sql + ' ) '

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = '9A')

BEGIN

SET @sql = @SelRecordsFrom + ' WHERE ' + @InvoiceStatusIn +@a+ @WhereParamsStr

EXEC sp_executesql @sql;

/*******/

END ELSE IF (@SumOrDetail = 'AP') /** acc_list_6 is a custom table for ABP client **/

BEGIN

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'acc_list_6')

SELECT 'Table NOT found = acc_list_6' AS 'ERROR'

UNION

SELECT 'Contact your Basware Consultant' AS 'To Do'

ELSE

SELECT t1 AS 'AP Team code',t2 AS 'Name of Person' FROM acc_list_6;

/*******/

END ELSE IF (@SumOrDetail = 'SU') /** supplier lookup **/

BEGIN

SELECT supplier_num,supplier_name FROM supplier ORDER BY supplier_num ASC;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'IT')

BEGIN

SET @sql = 'SELECT I.invoice_type,count(I.doc_id) FROM docs I WHERE ' + @InvoiceStatusIn +@a+ @WhereParamsStr + ' GROUP BY I.invoice_type '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'OM')

BEGIN

SET @sql = 'SELECT I.match_status_index,I.status_index,count(I.doc_id) AS "Count of OM-MSI" FROM docs I WHERE ' + @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr + ' GROUP BY I.match_status_index,I.status_index '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'MC')

BEGIN

SET @sql = 'SELECT I.mc_match_status_index,I.status_index,count(I.doc_id) AS "Count of MC-MSI" FROM docs I WHERE ' + @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr + ' GROUP BY I.mc_match_status_index,I.status_index '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'APOM')

BEGIN

SET @sql = 'SELECT I.attrib_t3,I.match_status_index,count(I.doc_id) AS "Count of AP TEAM Auto Match" FROM docs I WHERE ' + @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr + ' GROUP BY I.attrib_t3,I.match_status_index ORDER BY I.attrib_t3 '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'APMC')

BEGIN

SET @sql = 'SELECT I.attrib_t3,I.mc_match_status_index,count(I.doc_id) AS "Count of AP TEAM Manual Match" FROM docs I WHERE ' + @InvoiceTypeIS +@a+ @InvoiceStatusIn +@a+ @WhereParamsStr + ' GROUP BY I.attrib_t3,I.mc_match_status_index ORDER BY I.attrib_t3 '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'DATES')

BEGIN

SET @sql = 'SELECT I.invoice_date,I.scan_date,I.payment_date,I.entry_date,I.attrib_t3,I.stamp_uid,I.invoice_num,I.invoice_sum,I.invoice_type,I.status_index FROM docs I WHERE '

+ @WhereParamsStr + ' ORDER BY I.invoice_date DESC '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'TYPE')

BEGIN

SET @sql = 'SELECT I.invoice_type,I.status_index,COUNT(I.doc_id) AS ''Count'' FROM docs I WHERE ' + @WhereParamsStr + ' GROUP BY I.status_index,I.invoice_type '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'MEM')

BEGIN

SET @sql = 'SELECT attrib_t3 AS ''AP Team member'',COUNT(I.doc_id) AS ''Count'',SUM(invoice_sum) AS ''Invoice Amt'' FROM docs I WHERE '

+ @WhereParamsStr + ' GROUP BY attrib_t3 ORDER BY attrib_t3 ASC '

EXEC sp_executesql @sql;

/*****************************************************************************/

END ELSE IF (@SumOrDetail = 'basware')

BEGIN

SELECT 'a.' ,'List: Auto-Match Statuses','key OM' AS 'Type'

UNION

SELECT 'b.' ,'List: Matching client match Statuses' AS 'Description','key MC' AS 'Type'

UNION

SELECT 'c.' ,'List: Invoice Types' AS 'Description','key IT' AS 'Type'

UNION

SELECT 'd.' ,'List: Auto Match Statuses by AP Team member' AS 'Description','key APOM' AS 'Type'

UNION

SELECT 'e.' ,'List: Manual Match Statuses by AP Team member' AS 'Description','key APMC' AS 'Type'

/*****************************************************************************/

END ELSE

BEGIN

SELECT 'a.' ,'Summary' AS 'Description','key S' AS 'Type'

UNION

SELECT 'b.' ,'Enter column number and row letter to get invoice listing behind that columns total' AS 'Description','keys 1a-9a' AS 'Type'

UNION

SELECT 'c.' ,'Total PO Invoices' AS 'Description','key 1a' AS 'Type'

UNION

SELECT 'd.' ,'What If Theoretical Variance' AS 'Description','key 1d or 1e' AS 'Type'

UNION

SELECT 'e.' ,'Difference between 1a and 1d plus 1e' AS 'Description','key 1f' AS 'Type'

UNION

SELECT 'f.' ,'PO Invoices Auto Matched' AS 'Description','key 2a' AS 'Type'

UNION

SELECT 'g.' ,'Auto Matched Exact or with Variance' AS 'Description','key 2b or 2c' AS 'Type'

UNION

SELECT 'h.' ,'OM Failed' AS 'Description','key 3a' AS 'Type'

UNION

SELECT 'i.' ,'Order Matched with Matching Client' AS 'Description','key 4a' AS 'Type'

UNION

SELECT 'j.' ,'Matching Client Exact or with Variance' AS 'Description','key 4b or 4c' AS 'Type'

UNION

SELECT 'k.' ,'Matching Client Variance Grouping' AS 'Description','key 4cv' AS 'Type'

UNION

SELECT 'l.' ,'Anomalous PO Invoices' AS 'Description','key 5a' AS 'Type'

UNION

SELECT 'm.' ,'PO Ready for Transfer' AS 'Description','key 6a' AS 'Type'

UNION

SELECT 'n.' ,'PO Transferred' AS 'Description','key 7a' AS 'Type'

UNION

SELECT 'o.' ,'Other Invoices' AS 'Description','key 8a' AS 'Type'

UNION

SELECT 'p.' ,'Total Invoices' AS 'Description','key 9a' AS 'Type'

UNION

SELECT 'q.' ,'List: AP Team members and codes' AS 'Description','key AP' AS 'Type'

UNION

SELECT 'r.' ,'List: Suppliers and codes' AS 'Description','key SU' AS 'Type'

UNION

SELECT 's.' ,'List: Invoice Type totals' AS 'Description','key IT' AS 'Type'

UNION

SELECT 't.' ,'List: Invoice Type and Status index of invoice totals' AS 'Description','key TYPE' AS 'Type'

UNION

SELECT 'u.' ,'List: Date fields with Member and invoice amount' AS 'Description','key DATES' AS 'Type'

UNION

SELECT 'v.' ,'List: Totals of invoices touched by each AP Team member' AS 'Description','key MEM' AS 'Type'

UNION

SELECT 'w.' ,'This Help listing' AS 'Description','key H' AS 'Type'

UNION

SELECT 'x.' ,'NOTE: Summary Rows 1a = 2a + 3a + 4a + 5a AND 9a = 1a + 8a' AS 'Description','ALSO 1a = 1d + 1e + 1f AND 2a = 2b + 2c AND 4a = 4b + 4c AND 1a = 6a + 7a' AS 'Type'

END

------------------------------------------------

END

-- =============================================

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF