USE [P21]
GO

/****** Object:  View [dbo].[nutech_view_open_orders_report]    Script Date: 9/29/2024 9:43:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


 CREATE VIEW [dbo].[nutech_view_open_orders_report]
 AS
 SELECT p21_view_oe_hdr.order_no, p21_view_oe_hdr.customer_id, p21_view_oe_hdr.order_date,
        p21_view_oe_hdr.date_created, p21_view_oe_hdr.date_last_modified,
        p21_view_oe_hdr.taker, 0 oe_line_line_no, 0 oe_line_sales_cost, 0 oe_line_extended_price,
		p21_view_oe_hdr.location_id, p21_view_oe_hdr.promise_date, p21_view_oe_hdr.pick_ticket_type,
		p21_view_oe_hdr.front_counter, p21_view_oe_hdr.invoice_batch_uid, invoice_batch.invoice_batch_desc,
		p21_view_oe_hdr.created_by, company.company_name, address.name address_name,
		users.name user_name, 0 oe_line_unit_quantity, 0 oe_line_qty_ordered,
		p21_view_oe_hdr.company_id, p21_view_oe_hdr.approved, p21_view_oe_hdr.cancel_flag,
		p21_view_oe_hdr.delete_flag,
		CASE p21_view_oe_hdr.projected_order 
		WHEN 'N' 
		THEN p21_view_oe_hdr.completed 
		ELSE quote_hdr.complete_flag 
		END completed,
		p21_view_oe_hdr.projected_order, ' ' oe_line_disposition, 0 unit_price, 0 qty_allocated,
		0 qty_on_pick_tickets, 0 qty_invoiced, 0 unit_size, 0 oe_line_qty_canceled, contacts.first_name,
		contacts.mi, contacts.last_name, p21_view_oe_hdr.validation_status, 0 oe_line_pricing_unit_size,
		p21_view_oe_hdr.rma_flag, p21_view_oe_hdr.source_code_no,
		CASE
		WHEN p21_view_oe_hdr.source_code_no = 1175 OR p21_view_oe_hdr.source_code_no = 1446
		THEN 'Y' 
		ELSE 'N'
		END pda_order,
		'N' lot_bill, 0 oe_line_lot_billing_open_price, 0 commission_cost , COUNT(*) cc_lines,
		SUM( p21_view_oe_line.unit_price * ( CASE WHEN p21_view_oe_hdr.rma_flag = 'Y' THEN (abs(p21_view_oe_line.qty_ordered) - abs(p21_view_oe_line.qty_canceled) ) * (-1) ELSE ( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_canceled ) END ) / p21_view_oe_line.pricing_unit_size + COALESCE(oe_line_service_labor.extended_price, 0) ) cc_total_price,
		SUM( CASE WHEN p21_view_oe_line.lot_bill = 'Y' THEN oe_line_lot_billing.open_price 
		ELSE CASE WHEN (p21_view_oe_hdr.rma_flag = 'N') AND ((p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_invoiced - p21_view_oe_line.qty_canceled) <= 0) THEN 0 ELSE CAST(( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_invoiced - p21_view_oe_line.qty_canceled ) * ( p21_view_oe_line.unit_price / p21_view_oe_line.pricing_unit_size ) AS Decimal(19,9)) END END + ( CASE WHEN oe_line_service.row_status_flag = 1735 THEN 0 ELSE COALESCE(oe_line_service_labor.extended_price, 0) END ) ) cc_open_value,
		SUM( CASE WHEN 'O' = 'O' THEN ( CASE WHEN (p21_view_oe_line.disposition = 'D' OR p21_view_oe_line.disposition ='S' OR ((p21_view_oe_line.disposition ='B' OR p21_view_oe_line.disposition ='T') AND p21_view_inv_loc.stockable = 'N')) AND p21_view_oe_line.qty_allocated =0 AND p21_view_oe_line.po_cost <>0 THEN dbo.p21_fn_CalcForeignAmtFromHomeAmt(p21_view_oe_line.po_cost_home, currency_line.exchange_rate) ELSE p21_view_oe_line.sales_cost END * CASE WHEN p21_view_oe_hdr.rma_flag = 'Y' THEN (abs(p21_view_oe_line.qty_ordered) - abs(p21_view_oe_line.qty_canceled) ) * (-1) ELSE ( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_canceled ) END / pricing_unit_size ) ELSE ( CASE WHEN (p21_view_oe_line.disposition = 'D' OR p21_view_oe_line.disposition ='S' OR ((p21_view_oe_line.disposition ='B' OR p21_view_oe_line.disposition ='T') AND p21_view_inv_loc.stockable = 'N')) AND p21_view_oe_line.qty_allocated =0 AND p21_view_oe_line.po_cost <>0 THEN dbo.p21_fn_CalcForeignAmtFromHomeAmt(p21_view_oe_line.po_cost_home, currency_line.exchange_rate) ELSE p21_view_oe_line.commission_cost END * CASE WHEN p21_view_oe_hdr.rma_flag = 'Y' THEN (abs(p21_view_oe_line.qty_ordered) - abs(p21_view_oe_line.qty_canceled) ) * (-1) ELSE ( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_canceled ) END / pricing_unit_size ) END + COALESCE(oe_line_service_labor.estimated_cost, 0) * COALESCE(oe_line_service_labor.hours_worked, 0) ) cc_estimated_cost,
		customer.currency_id, currency_hdr.currency_desc, p21_view_oe_hdr.currency_line_uid,
		SUM( p21_view_oe_line.unit_price_home * ( CASE WHEN p21_view_oe_hdr.rma_flag = 'Y' THEN (abs(p21_view_oe_line.qty_ordered) - abs(p21_view_oe_line.qty_canceled) ) * (-1) ELSE ( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_canceled ) END ) / p21_view_oe_line.pricing_unit_size + COALESCE(oe_line_service_labor.extended_price, 0) ) cc_total_price_home,
		SUM( CASE WHEN p21_view_oe_line.lot_bill = 'Y' THEN oe_line_lot_billing.open_price ELSE CASE WHEN (p21_view_oe_hdr.rma_flag = 'N') AND ((p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_invoiced - p21_view_oe_line.qty_canceled) <= 0) THEN 0 ELSE CAST(( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_invoiced - p21_view_oe_line.qty_canceled ) * ( p21_view_oe_line.unit_price_home / p21_view_oe_line.pricing_unit_size ) AS Decimal(19,9)) END END + ( CASE WHEN oe_line_service.row_status_flag = 1735 THEN 0 ELSE COALESCE(oe_line_service_labor.extended_price, 0) END ) ) cc_open_value_home,
		SUM( CASE WHEN 'O' = 'O' THEN ( CASE WHEN (p21_view_oe_line.disposition = 'D' OR p21_view_oe_line.disposition ='S' OR ((p21_view_oe_line.disposition ='B' OR p21_view_oe_line.disposition ='T') AND p21_view_inv_loc.stockable = 'N')) AND p21_view_oe_line.qty_allocated =0 AND p21_view_oe_line.po_cost <>0 THEN p21_view_oe_line.po_cost_home ELSE p21_view_oe_line.sales_cost_home END * CASE WHEN p21_view_oe_hdr.rma_flag = 'Y' THEN (abs(p21_view_oe_line.qty_ordered) - abs(p21_view_oe_line.qty_canceled) ) * (-1) ELSE ( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_canceled ) END / pricing_unit_size ) ELSE ( CASE WHEN (p21_view_oe_line.disposition = 'D' OR p21_view_oe_line.disposition ='S' OR ((p21_view_oe_line.disposition ='B' OR p21_view_oe_line.disposition ='T') AND p21_view_inv_loc.stockable = 'N')) AND p21_view_oe_line.qty_allocated =0 AND p21_view_oe_line.po_cost <>0 THEN p21_view_oe_line.po_cost_home ELSE p21_view_oe_line.commission_cost_home END * CASE WHEN p21_view_oe_hdr.rma_flag = 'Y' THEN (abs(p21_view_oe_line.qty_ordered) - abs(p21_view_oe_line.qty_canceled) ) * (-1) ELSE ( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_canceled ) END / pricing_unit_size ) END + COALESCE(oe_line_service_labor.estimated_cost, 0) * COALESCE(oe_line_service_labor.hours_worked,0) ) cc_estimated_cost_home,
		company.home_currency_id,
		CASE p21_view_oe_hdr.order_type WHEN 1877 THEN 'Y' ELSE 'N' END cc_mfr_rep_order,
		CASE p21_view_oe_hdr.job_control_flag WHEN 'Y' THEN p21_view_oe_hdr.job_name ELSE '' END job_name,
		quote_hdr.expiration_date
 FROM p21_view_oe_hdr Inner Join p21_view_oe_line on 
 p21_view_oe_line.order_no = p21_view_oe_hdr.order_no
	 INNER JOIN p21_view_inv_mast on
	 p21_view_inv_mast.inv_mast_uid =p21_view_oe_line.inv_mast_uid 
	 AND p21_view_inv_mast.product_type <> 'B'
	 INNER JOIN p21_view_inv_loc ON
	 p21_view_inv_loc.inv_mast_uid = p21_view_oe_line.inv_mast_uid
	 AND p21_view_inv_loc.location_id = p21_view_oe_line.source_loc_id
	 Left Join oe_line_service on p21_view_oe_line.oe_line_uid = oe_line_service.oe_line_uid
	 Left Join oe_line_service_labor on oe_line_service.oe_line_service_uid = oe_line_service_labor.oe_line_service_uid
	 Inner Join address on address.id = p21_view_oe_hdr.customer_id Inner Join company on company.company_id = p21_view_oe_hdr.company_id
	 Inner Join users on users.id = p21_view_oe_hdr.taker Left Join oe_hdr_salesrep on oe_hdr_salesrep.order_number = p21_view_oe_hdr.order_no and oe_hdr_salesrep.primary_salesrep = 'Y' Left Join contacts on contacts.id = oe_hdr_salesrep.salesrep_id
	 Left Join quote_hdr on quote_hdr.oe_hdr_uid = p21_view_oe_hdr.oe_hdr_uid Left Join oe_line_lot_billing on 
	 oe_line_lot_billing.oe_line_uid = p21_view_oe_line.oe_line_uid Inner Join customer ON
	 customer.customer_id = p21_view_oe_hdr.customer_id AND customer.company_id = p21_view_oe_hdr.company_id Inner Join currency_hdr ON
	 customer.currency_id = currency_hdr.currency_id
	 LEFT JOIN supplier ON (supplier.supplier_id = p21_view_oe_line.supplier_id)
	 LEFT JOIN currency_line ON
	 currency_line.currency_line_uid = p21_view_oe_hdr.currency_line_uid
	 INNER JOIN ship_to on
	 p21_view_oe_hdr.address_id = ship_to.ship_to_id 
	 AND p21_view_oe_hdr.company_id = ship_to.company_id
	 LEFT JOIN (SELECT DISTINCT territory_x_ship_to.territory_uid, territory_x_ship_to.company_id, territory_x_ship_to.ship_to_id
	            FROM territory_x_ship_to INNER JOIN territory ON territory.territory_uid = territory_x_ship_to.territory_uid AND territory.row_status_flag <> 700
	           WHERE (territory_x_ship_to.row_status_flag <> 700) AND (1103 = 1104)) AS drv_ship_to_territory ON drv_ship_to_territory.territory_uid IS NULL 
	             AND drv_ship_to_territory.company_id = ship_to.company_id
	             AND drv_ship_to_territory.ship_to_id = ship_to.ship_to_id
	 LEFT JOIN (SELECT DISTINCT territory_x_territory_grp.territory_grp_uid,territory_x_ship_to.company_id,territory_x_ship_to.ship_to_id
	 FROM territory_x_territory_grp
	 INNER JOIN territory ON territory.territory_uid = territory_x_territory_grp.territory_uid 
	 AND territory.row_status_flag <> 700
	 INNER JOIN territory_x_ship_to ON territory.territory_uid = territory_x_ship_to.territory_uid 
	 AND territory_x_ship_to.row_status_flag <> 700
	 WHERE (territory_x_territory_grp.row_status_flag <> 700) AND (1875 = 1104 )) AS drv_ship_to_territory_group ON drv_ship_to_territory_group.territory_grp_uid IS NULL AND drv_ship_to_territory_group.company_id = ship_to.company_id AND drv_ship_to_territory_group.ship_to_id = ship_to.ship_to_id LEFT JOIN (SELECT DISTINCT territory_x_customer.territory_uid, territory_x_customer.company_id, territory_x_customer.customer_id
	 FROM territory_x_customer
	 INNER JOIN territory ON territory.territory_uid = territory_x_customer.territory_uid 
	 AND territory.row_status_flag <> 700 WHERE (territory_x_customer.row_status_flag <> 700) 
	 AND (1103 = 1104)) AS drv_customer_territory ON drv_customer_territory.territory_uid IS NULL 
	 AND drv_customer_territory.company_id = customer.company_id AND drv_customer_territory.customer_id = customer.customer_id 
	 LEFT JOIN (SELECT DISTINCT territory_x_territory_grp.territory_grp_uid, territory_x_customer.company_id, territory_x_customer.customer_id 
	 FROM territory_x_territory_grp 
	 INNER JOIN territory ON 
	 territory.territory_uid = territory_x_territory_grp.territory_uid 
	 AND territory.row_status_flag <> 700 
	 INNER JOIN territory_x_customer ON
	 territory.territory_uid = territory_x_customer.territory_uid 
	 AND territory_x_customer.row_status_flag <> 700 
	 WHERE (territory_x_territory_grp.row_status_flag <> 700) 
	 AND (1875 = 1104 )) AS drv_customer_territory_group ON 
	 drv_customer_territory_group.territory_grp_uid IS NULL 
	 AND drv_customer_territory_group.company_id = customer.company_id 
	 AND drv_customer_territory_group.customer_id = customer.customer_id
	 LEFT JOIN job_price_hdr ON
	 job_price_hdr.job_price_hdr_uid = p21_view_oe_hdr.job_price_hdr_uid
	 LEFT JOIN invoice_batch on
	 p21_view_oe_hdr.invoice_batch_uid = invoice_batch.invoice_batch_uid
	 WHERE company.company_id = '10'
	 AND p21_view_oe_hdr.customer_id >= 0
	 AND p21_view_oe_hdr.customer_id <= 999999999
     AND ( p21_view_oe_hdr.order_no >= '0' ) 
     AND ( p21_view_oe_hdr.order_no <= '99999999' ) 
     AND ( p21_view_oe_hdr.order_date >= {ts '1990-01-01 00:00:00.000'} ) 
     AND ( p21_view_oe_hdr.order_date <= {ts '2099-12-31 23:59:59.000'} ) 
     AND ( ( oe_hdr_salesrep.salesrep_id >= '0' AND oe_hdr_salesrep.salesrep_id <= '99999999' ) OR ( oe_hdr_salesrep.salesrep_id is null)) 
     AND ( p21_view_oe_hdr.taker >= '0' ) 
     AND ( p21_view_oe_hdr.taker <= 'ZZZZZZZZ' )
	 AND p21_view_oe_hdr.date_created >= {ts '1990-01-01 00:00:00.000'}
	 AND p21_view_oe_hdr.date_created <= {ts '2099-12-31 23:59:59.000'}
	 AND p21_view_oe_hdr.date_last_modified >= {ts '1990-01-01 00:00:00.000'}
	 AND p21_view_oe_hdr.date_last_modified <= {ts '2099-12-31 23:59:59.000'}
	 AND p21_view_oe_hdr.last_maintained_by >= ' '
     AND p21_view_oe_hdr.last_maintained_by <= 'ZZZZZZZ'
     AND p21_view_oe_hdr.delete_flag = 'N'
     AND p21_view_oe_line.delete_flag = 'N'
     AND ((1417 <> 1203) OR (1103 <> 1104) OR COALESCE(drv_ship_to_territory.territory_uid, 0) = NULL )
	 AND ((1417 <> 1203) OR (1875 <> 1104) OR COALESCE(drv_ship_to_territory_group.territory_grp_uid, 0) = NULL )
     AND ((1203 <> 1203) OR (1103 <> 1104) OR COALESCE(drv_customer_territory.territory_uid, 0) = NULL )
	 AND ((1203 <> 1203) OR (1875 <> 1104) OR COALESCE(drv_customer_territory_group.territory_grp_uid, 0) = NULL )
	 AND p21_view_oe_line.parent_oe_line_uid = 0
     AND (( p21_view_oe_hdr.projected_order = 'N' 
	 AND ( p21_view_oe_hdr.completed = 'N' or p21_view_oe_hdr.completed = 'T' or p21_view_oe_hdr.completed = 'N' ))
           OR ( p21_view_oe_hdr.projected_order = 'Y' AND ( quote_hdr.complete_flag = 'N' or quote_hdr.complete_flag = 'N')) )
     AND ( ('N' = 'N') OR ('Y' = 'N' AND p21_view_oe_hdr.source_code_no IN (1175,1446)) )
     AND ( ( 'A' = 'A' ) OR ( 'A' = 'H' AND p21_view_oe_hdr.validation_status = 'HOLD' ) OR ( 'A' = 'C' AND p21_view_oe_hdr.validation_status = 'COD' ) OR ( 'A' = 'B' AND p21_view_oe_hdr.validation_status IN ( 'COD', 'HOLD' )) )
	 AND ( ( 0 = 0 AND p21_view_oe_hdr.projected_order = 'N' ) OR (0 = 1 AND p21_view_oe_hdr.projected_order = 'Y' AND (quote_hdr.expiration_date IS NULL OR quote_hdr.expiration_date >= GETDATE())))
	 AND ( 2 = 2 OR ( 2 = 0 AND p21_view_oe_hdr.approved = 'Y' ) OR (2 = 1 AND p21_view_oe_hdr.approved = 'N' ))
     AND ( 'Y' = 'Y' OR ( 'Y' = 'N' AND p21_view_oe_hdr.completed <> 'T' ) OR ('Y' = 'O' AND p21_view_oe_hdr.completed = 'T' ))
     AND ( 'Y' = 'Y' OR ( 'Y' = 'N'
     AND (p21_view_oe_hdr.order_type <> 1877 or p21_view_oe_hdr.order_type is null) ) OR ('Y' = 'O' AND p21_view_oe_hdr.order_type = 1877))
     AND ( 'Y' = 'Y' OR ( 'Y' = 'N' AND p21_view_oe_hdr.rma_flag = 'N'))
     AND customer.currency_id >= 0
     AND customer.currency_id <= 99999999
     AND (p21_view_oe_hdr.order_type <> 1343 OR p21_view_oe_hdr.order_type IS NULL)
     AND ( ('N' = 'N') OR ('N' = 'Y' 
	      AND ( ('N' = 'Y') OR ('N' = 'N' AND (job_price_hdr.consignment_flag ='N' OR job_price_hdr.consignment_flag IS NULL)) 
		  OR ('N' = 'O' AND job_price_hdr.consignment_flag ='Y') ) ) )
     AND ( ('0' = '0') 
	        OR ('0' = '1' AND p21_view_oe_hdr.quote_type = 3249) 
	        OR ('0' = '2' AND (p21_view_oe_hdr.quote_type <> 3249 OR p21_view_oe_hdr.quote_type IS NULL) ) )
     GROUP BY customer.currency_id, currency_hdr.currency_desc, p21_view_oe_hdr.order_no, p21_view_oe_hdr.customer_id,
	       p21_view_oe_hdr.order_date, p21_view_oe_hdr.date_created, p21_view_oe_hdr.date_last_modified, p21_view_oe_hdr.taker,
		   p21_view_oe_hdr.location_id, p21_view_oe_hdr.promise_date, p21_view_oe_hdr.pick_ticket_type,
	       p21_view_oe_hdr.front_counter, p21_view_oe_hdr.invoice_batch_uid, invoice_batch.invoice_batch_desc,
		   p21_view_oe_hdr.created_by, company.company_name, address.name, users.name,
		   p21_view_oe_hdr.company_id, p21_view_oe_hdr.approved, p21_view_oe_hdr.cancel_flag, 
		   p21_view_oe_hdr.delete_flag, completed,
		   p21_view_oe_hdr.projected_order, contacts.first_name, contacts.mi, contacts.last_name,
		   p21_view_oe_hdr.validation_status, p21_view_oe_hdr.rma_flag, p21_view_oe_hdr.source_code_no,
		   quote_hdr.complete_flag, p21_view_oe_hdr.currency_line_uid, company.home_currency_id,
		   p21_view_oe_hdr.job_control_flag, p21_view_oe_hdr.job_name ,quote_hdr.expiration_date,
		   p21_view_oe_hdr.order_type 
GO


