Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='PendingCbucksProcUpdate170425';

if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO

/****** Object:  StoredProcedure [dbo].[GetPendingCbucksAndConsumableStockReceipts]    Script Date: 2025-04-17 08:24:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[GetPendingCbucksAndConsumableStockReceipts]
AS
BEGIN
    SET NOCOUNT ON;

SELECT 
Created, 
'Crusaders Corporate Wholesale' as Supplier, 
Invoice_Number, 
'Pending' as [Status],
'Consumable' as ReceiptType, 
Online_Invoice_ID
FROM ConsumableReceipt
WHERE 
    Stock_Receipt_Status_ID = 1 AND 
    Online_Invoice_ID IS NOT NULL AND 
    Supplier_ID = 1 AND 
    Created >= '2024-10-01'  and DATEDIFF(DAY,CCWInvoiceDate,getdate()) >= 13
        and online_invoice_id > 250451    and Online_Invoice_ID not in (select Online_Invoice_ID from ConsumableReceipt where Stock_Receipt_Status_ID in (2))
UNION
SELECT 
Created, 
'Crusaders Corporate Wholesale' as Supplier, 
Invoice_Number, 
'Pending' as [Status],
'CBucks' as ReceiptType, 
Online_Invoice_ID
FROM Stock_Receipt
WHERE 
    Supplier_ID = 1 AND 
    Stock_Receipt_Status_ID = 1 AND 
    Online_Invoice_ID IS NOT NULL AND 
    IsCbucksInvoice = 1     AND 
    Created >= '2024-10-01' and DATEDIFF(DAY,CCWInvoiceDate,getdate()) >= 13
and online_invoice_id > 250451   and Online_Invoice_ID not in (select Online_Invoice_ID from Stock_Receipt where IsCbucksInvoice =1 and Stock_Receipt_Status_ID in (2))
END

Print 'Insert into script history if everything else is cool';

	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'PendingCbucksProcUpdate170425');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO