USE [P21]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[nutech_fn_inv_usage]
(@customer_id AS VARCHAR(40),
@usage_start_date AS DATETIME,
@usage_end_date AS DATETIME
)
RETURNS @nutech_inv_usage TABLE (
		customer_id varchar(12),
		invoice_no varchar(10),
		invoice_date datetime,
		bill2_name varchar(50),
		po_no varchar(255),
		line_no decimal(19, 0),
		item_id varchar(40),
		item_desc varchar(40),
		customer_part_number varchar(40),
		unit_price decimal(19, 9),
		extended_price decimal(19, 4),
		qty_shipped decimal(19, 9),
		ship_to_id decimal(19, 0),
		cogs_amount decimal(19, 4),
		invoice_line_type int
	)
AS
BEGIN
	INSERT INTO @nutech_inv_usage
	SELECT p21_view_invoice_hdr.customer_id, p21_view_invoice_hdr.invoice_no, p21_view_invoice_hdr.invoice_date, 
		p21_view_invoice_hdr.bill2_name, p21_view_invoice_hdr.po_no, p21_view_invoice_line.line_no, p21_view_invoice_line.item_id, 
		p21_view_invoice_line.item_desc, p21_view_invoice_line.customer_part_number, p21_view_invoice_line.unit_price, 
		p21_view_invoice_line.extended_price, p21_view_invoice_line.qty_shipped, p21_view_invoice_hdr.ship_to_id, 
		p21_view_invoice_line.cogs_amount, p21_view_invoice_line.invoice_line_type
	FROM p21_view_invoice_hdr INNER JOIN p21_view_invoice_line ON p21_view_invoice_hdr.invoice_no = p21_view_invoice_line.invoice_no
	WHERE (((p21_view_invoice_hdr.customer_id)=@customer_id) AND ((p21_view_invoice_hdr.invoice_date) Between @usage_start_date And @usage_end_date) 
		AND ((p21_view_invoice_hdr.po_no)<>'CONSOLIDATED INVOICE') AND ((p21_view_invoice_line.extended_price)<>0) 
		AND ((p21_view_invoice_line.qty_shipped)<>0) AND ((p21_view_invoice_line.invoice_line_type)=0))
	ORDER BY p21_view_invoice_hdr.invoice_date DESC;

	RETURN;

END;
GO


