USE [Kingethelbert]

GO

/****** Object: StoredProcedure [dbo].[bacs_list] Script Date: 05/03/2013 11:00:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- EXEC bacs_list

ALTER PROCEDURE [dbo].[bacs_list]

@company_idINT,

@statusINT,

@INT_USERIDINT = 0

AS

BEGIN

SET NOCOUNT ON;

DECLARE @INT_VIEW_OWN_FILE_ONLY INT = 0

SELECT @INT_VIEW_OWN_FILE_ONLY = view_own_file_only FROM WHERE USER_ID = @INT_USERID

-- *********** Added status column **********-------

IF (@INT_VIEW_OWN_FILE_ONLY = 0)

BEGIN

SELECT file_id as bacs_id,

listname NAME,

filename ,

filetype,

ISNULL((SELECT USER_NAME FROM userlogin WHERE user_id=createdby),'')created_by,

createdby,

validatestatus Modulas_Valid,

CONVERT(VARCHAR(15), createddate, 103) create_date,

CONVERT(DATE,createddate,103) AS Createdt,

(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)

THEN

(SELECT sum(CONVERT(float, ft.amount))

FROM filetransaction ft

WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)

--CAST((SELECT COALESCE(SUM(CONVERT(float, ft.amount))/100.00,0.00) FROM filetransaction ft

--WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('99', 'Z4', 'Z5') AND ISNUMERIC(ft.amount ) = 1) AS DECIMAL(13,2))

ELSE

CAST((SELECT COALESCE(SUM(CONVERT(float, FPSTemp.amount))/100.00,0.00) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code

WHERE FPSTemp.fileid= fb.file_id AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('99', 'Z4', 'Z5')) AS DECIMAL(13,2)) END totcreditamount

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)

THEN

CAST((SELECT COALESCE(SUM(CONVERT(float, ft.amount))/100.00,0.00) FROM filetransaction ft

WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('01', '17', '18', '19') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))

ELSE

CAST((SELECT COALESCE(SUM(CONVERT(float, FPSTemp.amount))/100.00,0.00) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code WHERE FPSTemp.fileid= fb.file_id

AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('01', '17', '18', '19')) AS DECIMAL(13,2)) END totdebitamount

FROM filebasic fb WHERE company_id=@company_id AND status=0

ORDER BY file_id DESC

END

ELSE

BEGIN

SELECTfile_id AS bacs_id,

listname NAME,

filename ,

filetype,

ISNULL((SELECT USER_NAME FROM userlogin WHERE user_id=createdby),'')created_by,

createdby,

validatestatus Modulas_Valid,

CONVERT(VARCHAR(15), createddate, 103) create_date,

CONVERT(DATE,createddate,103) AS Createdt,

(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)

THEN CAST((SELECT SUM(CONVERT(FLOAT, ft.amount))/100.00 FROM filetransaction ft

WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('99', 'Z4', 'Z5') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))

ELSE

(SELECT ROUND(SUM(CONVERT(BIGINT, FPSTemp.amount))/100,2) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code

WHERE FPSTemp.fileid= fb.file_id AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('99', 'Z4', 'Z5')) END totcreditamount

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)

THEN CAST((SELECT SUM(CONVERT(FLOAT, ft.amount))/100.00 FROM filetransaction ft

WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('01', '17', '18', '19') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))

ELSE

CAST((SELECT SUM(CONVERT(BIGINT, FPSTemp.amount))/100.00 FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code WHERE FPSTemp.fileid= fb.file_id

AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('01', '17', '18', '19')) AS DECIMAL(13,2)) END totdebitamount

FROM filebasic fb WHERE company_id=@company_id AND status=0 AND createdby = @INT_USERID

ORDER BY file_id DESC

END

END