USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_inventory_received_summary_report]    Script Date: 12/4/2024 8:44:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO












-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 12/3/2023
-- Description:	
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_inventory_received_summary_report] (
    @company_id  VARCHAR(8) = '10',
    @START_RECV_DATE datetime,
    @END_RECV_DATE datetime,
    @START_PO_DATE datetime,
    @END_PO_DATE datetime,
    @START_ITEM_ID varchar(40) = ' ',
    @END_ITEM_ID varchar(40) = 'ZZZZZZZZZZZZZZZZZZZZZZ',
    @START_PO_NO  decimal(19,0) = 0,
    @END_PO_NO  decimal(19,0) = 999999999999,
    @START_LOCATION_ID decimal(19,0) = 0,
    @END_LOCATION_ID decimal(19,0) = 9999999999,
    @START_RECEIPT_NUMBER decimal(19,0) = 0,
    @END_RECEIPT_NUMBER  decimal(19,0) = 999999999999,
    @START_VENDOR_ID decimal(19,0) = 0,
    @END_VENDOR_ID  decimal(19,0) = 999999999999
)
RETURNS @nutech_inventory_received_report TABLE 
(	
	location_id [decimal](19, 0) NULL,
	po_line_unit_of_measure [varchar](8) NULL,
	po_line_sum_qty_ordered [decimal](19, 9) NULL,
    po_line_avg_unit_price [decimal](19, 6) NULL,
	inv_mast_uid [int] NOT NULL,
    item_id [varchar](40) NOT NULL,
	item_desc [varchar](40) NULL,
    inv_receipts_line_avg_unit_cost [decimal](19, 9) NULL,
    inv_receipts_line_sum_qty_received [decimal](19, 9) NULL,
    inv_receipts_line_sum_qty_vouched [decimal](19, 9) NULL,
	inv_receipts_line_sum_amount_vouched [decimal](19, 4) NULL,
	vouched_avg_unit_cost [decimal](19, 9) NULL,
	vendor_id [decimal](19, 9) NULL,
    vendor_name [varchar](255) NULL,
	product_group_desc [varchar](40) NULL,
	first_receipt_date [datetime] NULL,
	last_receipt_date [datetime] NULL,
	INDEX idx_nutech_inventory_received_report (inv_mast_uid ASC, item_id ASC)
)
AS
BEGIN 

	-- Populate table with Purchase/Received/Vouched Data.    
	INSERT INTO @nutech_inventory_received_report    
    select po_hdr.location_id, po_line.unit_of_measure,
        sum(po_line.qty_ordered) po_line_sum_qty_ordered,
        avg(po_line.unit_price) po_line_avg_unit_price,
        inv_mast.inv_mast_uid, inv_mast.item_id, inv_mast.item_desc,
        avg(inventory_receipts_line.unit_cost) inv_receipts_line_avg_unit_cost,
        sum(inventory_receipts_line.qty_received) inv_receipts_line_sum_qty_received,
        sum(inventory_receipts_line.qty_vouched) inv_receipts_line_sum_qty_vouched,
		sum(inventory_receipts_line.amount_vouched) inv_receipts_line_sum_amount_vouched,
		avg(inventory_receipts_line.amount_vouched/NULLIF(inventory_receipts_line.qty_vouched,0)) vouched_avg_unit_cost,
        vendor.vendor_id, vendor.vendor_name, product_group.product_group_desc,
		receipt_dates.first_receipt_date, receipt_dates.last_receipt_date
    from inventory_receipts_hdr with (NOLOCK) 
        left join inventory_receipts_line with (NOLOCK) on
            inventory_receipts_hdr.receipt_number = inventory_receipts_line.receipt_number
        left join po_hdr with (NOLOCK) on 
            inventory_receipts_hdr.po_number = po_hdr.po_no
        left join po_line with (NOLOCK) on
            po_hdr.po_no = po_line.po_no AND
            po_line.line_no = inventory_receipts_line.po_line_number
        left join inv_mast with (NOLOCK) on 
            inventory_receipts_line.inv_mast_uid = inv_mast.inv_mast_uid
		left join (select invrecline.inv_mast_uid, pohdr.location_id,
		                  min(invrecline.date_created) first_receipt_date,
		                  max(invrecline.date_created) last_receipt_date
		       from inventory_receipts_line invrecline with (NOLOCK)
			        left join inventory_receipts_hdr invrechdr with (NOLOCK) on
			          invrecline.receipt_number = invrechdr.receipt_number
			        left join po_hdr pohdr with (NOLOCK) on
					  invrechdr.po_number = pohdr.po_no
			   group by invrecline.inv_mast_uid, pohdr.location_id
			   ) as receipt_dates on 
			       inv_mast.inv_mast_uid = receipt_dates.inv_mast_uid and 
				   po_hdr.location_id = receipt_dates.location_id
        left join product_group WITH (NOLOCK) ON 
            po_hdr.company_no = product_group.company_id AND 
            inv_mast.default_product_group = product_group.product_group_id 
        left JOIN vendor WITH (NOLOCK) ON 
            vendor.vendor_id = po_hdr.vendor_id and 
            vendor.company_id = po_hdr.company_no 
        left JOIN code_p21 WITH (NOLOCK) ON 
            vendor.vendor_type_cd  = code_p21.code_no
        left JOIN address vendor_address WITH (NOLOCK) ON 
            vendor_address.id = po_hdr.vendor_id
    where inventory_receipts_hdr.date_created >= @START_RECV_DATE AND 
        inventory_receipts_hdr.date_created <= @END_RECV_DATE AND
--        inv_mast.item_id >= @START_ITEM_ID AND
--        inv_mast.item_id <= @END_ITEM_ID AND
	    po_hdr.date_created >= @START_PO_DATE AND
	    po_hdr.date_created <= @END_PO_DATE AND
	    po_hdr.location_id >= @START_LOCATION_ID AND
	    po_hdr.location_id <= @END_LOCATION_ID AND
		po_hdr.vendor_id >= @START_VENDOR_ID AND
		po_hdr.vendor_id <= @END_VENDOR_ID AND
        inventory_receipts_hdr.approved = 'Y' AND
        inventory_receipts_hdr.delete_flag <> 'Y' AND
--        inventory_receipts_hdr.vouch_complete = 'Y' AND
--        inventory_receipts_line.vouch_complete = 'Y' AND
        inventory_receipts_hdr.receipt_type = 'P' AND
        po_hdr.po_type <> 'X' AND
        po_hdr.approved = 'Y' AND
        po_hdr.delete_flag <> 'Y'
    group by po_hdr.location_id, po_line.unit_of_measure,
        po_line.qty_ordered,
        po_line.unit_price,
        inv_mast.inv_mast_uid, inv_mast.item_id, inv_mast.item_desc,
        inventory_receipts_line.unit_cost,
        inventory_receipts_line.qty_received,
        inventory_receipts_line.qty_vouched,
		inventory_receipts_line.amount_vouched,
        vendor.vendor_id, vendor.vendor_name, product_group.product_group_desc,
		receipt_dates.first_receipt_date, receipt_dates.last_receipt_date
    order by po_hdr.location_id, po_line.unit_of_measure,
        po_line.qty_ordered,
        po_line.unit_price,
        inv_mast.inv_mast_uid, inv_mast.item_id, inv_mast.item_desc,
        inventory_receipts_line.unit_cost,
        inventory_receipts_line.qty_received,
        inventory_receipts_line.qty_vouched,
		inventory_receipts_line.amount_vouched,
        vendor.vendor_id, vendor.vendor_name, product_group.product_group_desc,
		receipt_dates.first_receipt_date, receipt_dates.last_receipt_date

return

END





GO

