USE [P21]
GO

/****** Object:  View [dbo].[nutech_view_purchase_order_quote_report]    Script Date: 9/29/2024 9:44:34 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






 CREATE VIEW [dbo].[nutech_view_purchase_order_quote_report]
 AS
 SELECT po_hdr.po_no,
    po_hdr.company_no AS company_id,
	po_hdr.vendor_id,
	vendor_address.name AS vendor_name,
    vendor_address.central_phone_number AS vendor_phone_number,
	vendor_address.email_address AS vendor_email,
	po_hdr.date_created,
	po_hdr.order_date,
	po_hdr.date_due AS required_date,
    po_hdr.external_po_no,
	po_hdr_ud.confirmed AS po_confirmed,
	po_line.line_no,
	inv_mast.item_id,
	po_line.item_description,
	po_line.unit_quantity AS unit_qty_ordered,
	po_line.qty_ordered,
	po_line.unit_of_measure,
	po_line.unit_size,
    po_line.unit_price,
	po_line.unit_price_display,
	po_line.unit_price	/ po_line.pricing_unit_size AS price,
	po_line.pricing_unit,
	po_line.pricing_unit_size,
	po_line.qty_received / po_line.unit_size AS unit_qty_received,
	po_line.qty_received,
	po_hdr.location_id,
    po_hdr.branch_id,
	vendor.vendor_type_cd,
	code_p21.code_description,
	po_hdr.po_type,
	upper(replace(po_hdr.created_by,'MIDSTATESBOLT\','')) as created_by,
	CASE 
		WHEN COALESCE(drv_inventory_receipts_line.qty_vouched, 0) >= drv_inventory_receipts_line.qty_received
			THEN drv_inventory_receipts_line.qty_received
		ELSE COALESCE(drv_inventory_receipts_line.qty_vouched, 0)
		END / po_line.unit_size as unit_qty_vouched,
	CASE 
		WHEN COALESCE(drv_inventory_receipts_line.qty_vouched, 0) >= drv_inventory_receipts_line.qty_received
			THEN drv_inventory_receipts_line.qty_received
		ELSE COALESCE(drv_inventory_receipts_line.qty_vouched, 0)
		END as qty_vouched,
	CASE WHEN po_line.complete = 'N'
		  THEN po_line.qty_ordered - (CASE WHEN COALESCE(drv_inventory_receipts_line.qty_vouched, 0) >= drv_inventory_receipts_line.qty_received
										   THEN drv_inventory_receipts_line.qty_received
										   ELSE COALESCE(drv_inventory_receipts_line.qty_vouched, 0)
										END)
		ELSE COALESCE(drv_inventory_receipts_line.qty_available, 0)
		END / po_line.unit_size as unit_qty_available,
	CASE WHEN po_line.complete = 'N'
		  THEN po_line.qty_ordered - (CASE WHEN COALESCE(drv_inventory_receipts_line.qty_vouched, 0) >= drv_inventory_receipts_line.qty_received
										   THEN drv_inventory_receipts_line.qty_received
										   ELSE COALESCE(drv_inventory_receipts_line.qty_vouched, 0)
										END)
		ELSE COALESCE(drv_inventory_receipts_line.qty_available, 0)
		END as qty_available,
	COALESCE(drv_vendor_invoice_line.invoice_count, 0) as invoice_count,
	COALESCE(drv_vendor_invoice_line.quantity_invoiced, 0) / po_line.unit_size as unit_qty_invoiced,
	COALESCE(drv_vendor_invoice_line.quantity_invoiced, 0) as qty_invoiced
FROM po_line
INNER JOIN po_hdr ON po_hdr.po_no = po_line.po_no
INNER JOIN vendor ON vendor.vendor_id = po_hdr.vendor_id and vendor.company_id=po_hdr.company_no 
INNER JOIN code_p21 ON vendor.vendor_type_cd  = code_p21.code_no
INNER JOIN inv_mast ON inv_mast.inv_mast_uid = po_line.inv_mast_uid
INNER JOIN address vendor_address ON vendor_address.id = po_hdr.vendor_id
LEFT JOIN (	SELECT inventory_receipts_hdr.po_number as po_no
				  ,inventory_receipts_line.po_line_number
				  ,SUM(inventory_receipts_line.qty_received) as qty_received
				  ,SUM(inventory_receipts_line.qty_vouched * inventory_receipts_line.unit_size) as qty_vouched
				  ,SUM(CASE WHEN inventory_receipts_line.vouch_complete = 'N'
							THEN inventory_receipts_line.qty_received - (inventory_receipts_line.qty_vouched * inventory_receipts_line.unit_size)
							ELSE 0
						END) as qty_available
				FROM inventory_receipts_line
				INNER JOIN inventory_receipts_hdr ON inventory_receipts_hdr.receipt_number = inventory_receipts_line.receipt_number
				WHERE inventory_receipts_hdr.approved = 'Y' AND
	                  inventory_receipts_hdr.delete_flag <> 'Y' AND
	                  inventory_receipts_hdr.vouch_complete = 'Y' AND
	                  inventory_receipts_hdr.receipt_type = 'P' 
				GROUP BY inventory_receipts_hdr.po_number
					,inventory_receipts_line.po_line_number	) AS drv_inventory_receipts_line ON drv_inventory_receipts_line.po_no = po_line.po_no
																							AND drv_inventory_receipts_line.po_line_number = po_line.line_no
LEFT JOIN (SELECT vendor_invoice_line.po_no
				 ,vendor_invoice_line.po_line_number
				 ,COUNT(1) as invoice_count
				 ,SUM(vendor_invoice_line.quantity_invoiced) as quantity_invoiced
            FROM vendor_invoice_line
           WHERE vendor_invoice_line.row_status_flag IN (1245, 1246)
        GROUP BY vendor_invoice_line.po_no
				 ,vendor_invoice_line.po_line_number) AS drv_vendor_invoice_line ON drv_vendor_invoice_line.po_no = po_line.po_no 
																			    AND drv_vendor_invoice_line.po_line_number = po_line.line_no
LEFT JOIN po_hdr_ud on
	   po_hdr.po_no = po_hdr_ud.po_no
WHERE po_line.delete_flag = 'N'AND
      ((po_line.complete = 'N'
         AND po_line.qty_ordered - (CASE WHEN COALESCE(drv_inventory_receipts_line.qty_vouched, 0) >= drv_inventory_receipts_line.qty_received
								  THEN drv_inventory_receipts_line.qty_received
								  ELSE COALESCE(drv_inventory_receipts_line.qty_vouched, 0)
							 END) > 0)
	      OR (po_line.complete = 'Y'
         AND COALESCE(drv_inventory_receipts_line.qty_available, 0) > 0)) AND  po_hdr.company_no  IN ('10')




GO


