CREATE OR REPLACE PROCEDURE public.sp_deposits(var_storeid int8, var_depositdate varchar, var_filename varchar)
	LANGUAGE plpgsql
AS $$	
	DECLARE var_Completed boolean;
	DECLARE var_SelectTransactionsOnLoad boolean;
	DECLARE var_MoneyCollected decimal(16,4);
	DECLARE var_RebankMoneyCollected decimal(16,4);
	DECLARE var_AmountDeposit decimal(16,4);
	DECLARE var_BankingID bigint;
BEGIN  	
	 var_MoneyCollected := 0;
	 var_RebankMoneyCollected := 0;
	 var_AmountDeposit := 0;
	 var_BankingID := 0;

	SELECT TOP 1 SelectTransactionsOnLoad 
		INTO var_SelectTransactionsOnLoad
	FROM banksetting 
	WHERE (storeid = var_storeid ) OR (storeid = 0)   
	ORDER BY storeid DESC
	;
			        
	SELECT TOP 1 completed into var_Completed FROM bankdeposit b  WHERE b.storeid = var_storeid  AND b.businessday =  var_depositdate ;                           
	SELECT TOP 1 bankingid into var_BankingID FROM bankdeposit b  WHERE b.storeid =  var_storeid  AND b.businessday = var_depositdate  ; 
			
	SELECT CASE WHEN var_SelectTransactionsOnLoad = 'true' THEN 
		       SUM(ISNULL(ExpectedDeposit, 0))
			ELSE
				SUM(ISNULL(ActualDeposit, 0))
			END 
		INTO var_MoneyCollected
	FROM BankDepositDetail 
	WHERE (BankingID = var_BankingID OR (BankingID = 0 AND var_Completed=0))
			AND storeid = var_storeid
			AND BusinessDay = var_depositdate;  
		
		
			SELECT CASE WHEN var_SelectTransactionsOnLoad = 'true' THEN 
			            sum(ISNULL(ExpectedDeposit, 0))
			       ELSE
			            sum(ISNULL(ActualDeposit, 0))
			       END INTO var_RebankMoneyCollected
			FROM rebankdepositdetails 
			WHERE (BankingID = var_BankingID OR (BankingID = 0 AND var_Completed=0))
			AND storeid = var_storeid
			AND BusinessDay = var_depositdate; 
			
			SELECT ISNULL(SUM(ISNULL(amountdeposited , 0)), 0) into var_AmountDeposit
			                         FROM bankdeposit  
			                         WHERE BankingID = var_BankingID;
	
	EXECUTE 'DROP TABLE IF EXISTS ' || var_filename;
	EXECUTE 'CREATE TEMP TABLE ' || var_filename || ' AS
		                      
			 SELECT  
			     ' || ISNULL(var_MoneyCollected, 0) || ' as TotalCashierDeposit,
			     ' || ISNULL(var_RebankMoneyCollected, 0) || 'as ChangeFundDeposit, 
			     (' || ISNULL(var_MoneyCollected, 0) || ' + ' || ISNULL(var_RebankMoneyCollected, 0) || ' - ' || ISNULL(var_AmountDeposit, 0) || ' ) as MoniesToBeDeposited, 
			     ' || ISNULL(var_AmountDeposit, 0) || ' as TotalDeposit,
			     BillsAcceptedBySafe,
			     ManualCashDrop,
			     ManualCheckDrop,
			     ThirdPartyACH,
			     BillsAcceptedBySafe +  ManualCashDrop +  ManualCheckDrop + isnull(ThirdPartyACH,0) as [TotalBills]    
			                         FROM bankdeposit  
			                         WHERE BankingID = ' || var_BankingID || '';
	END;
$$;




