/****** Object:  StoredProcedure [dbo].[GetPendingCbucksAndConsumableStockReceipts]    Script Date: 2025-06-27 13:53:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE OR 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()) >= 21
        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()) >= 21
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