USE [P21]
GO

/****** Object:  View [dbo].[nutech_sales_history_report_view]    Script Date: 9/29/2024 9:42:42 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO








CREATE VIEW [dbo].[nutech_sales_history_report_view]
AS
SELECT	company.company_name
		,COALESCE(branch.branch_description, 'None') AS branch_description
		,ship_to.default_branch
		,ship_to_salesrep.salesrep_id ship_to_salesrep_id
		,invoice_hdr.order_no
		,invoice_hdr.invoice_date
		,invoice_hdr.sold_to_customer_id AS customer_id
		,invoice_hdr.invoice_no
		,invoice_hdr.ship2_name
		,invoice_hdr.po_no
		,invoice_hdr.brokerage
		,invoice_hdr.freight
		,invoice_hdr.period
		,invoice_hdr.year_for_period
		,invoice_hdr.invoice_type
		,invoice_hdr.ship_to_id
		,invoice_hdr.ship_date
		,invoice_hdr.amount_paid
		,invoice_hdr.company_no AS company_id
		,COALESCE(invoice_hdr.corp_address_id, address.corp_address_id)corp_address_id
		,invoice_hdr.bill2_country
	    ,invoice_hdr.ship2_city
		,invoice_hdr.ship2_state
		,invoice_hdr.ship2_postal_code
		,invoice_hdr.ship2_country
		,invoice_hdr.bad_debt_amount
		,invoice_hdr.invoice_class
		,invoice_hdr.terms_id
		,COALESCE(invoice_hdr.branch_id, 'None') branch_id
		,customer.currency_id
		,invoice_hdr.invoice_adjustment_type,
		COALESCE(invoice_line_summary.counter, 0) AS number_of_invoice_lines 
		,p21_view_invoice_line.qty_shipped,   
		p21_view_invoice_line.unit_of_measure,   
		COALESCE(inv_mast.item_id,p21_view_invoice_line.item_id) item_id, 
		p21_view_invoice_line.item_desc,
		COALESCE(p21_view_invoice_line.inv_mast_uid, 0) inv_mast_uid,
		p21_view_invoice_line.unit_price,
		CASE COALESCE (oe_line.pricing_option, 0)
			WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent 
							WHEN 0 THEN 0 
						ELSE p21_view_invoice_line.cogs_amount 
					END)
		
			WHEN 2 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent 
							WHEN 0 THEN p21_view_invoice_line.cogs_amount 
						ELSE 0 
					END)
		
			WHEN 3 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent 
						WHEN 0 THEN p21_view_invoice_line.cogs_amount 
						ELSE 0 
					END)
 


			WHEN 0 THEN ( CASE oe_hdr.order_type WHEN 1706 THEN  
                        			(CASE WHEN p21_view_invoice_line.invoice_line_type = 0 THEN 
								drv_sumlines.sum_cogs
					  	 ELSE p21_view_invoice_line.cogs_amount 	
					  	 END)
				      ELSE p21_view_invoice_line.cogs_amount	
				 END )
            		ELSE p21_view_invoice_line.cogs_amount	
		END cogs_amount,      
		CASE COALESCE (oe_line.pricing_option, 0)
		WHEN 1 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN 0 
						ELSE (p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped) 
					END)
		WHEN 2 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN (p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped) 
						ELSE 0 
					END)
		WHEN 3 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN (p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped) 
						ELSE 0 
					END)
		WHEN 4 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN 0
						ELSE (p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped) 
					END)
		WHEN 0 THEN ( CASE oe_hdr.order_type WHEN 1706 THEN 
						(CASE p21_view_invoice_line.invoice_line_type  -- GGR Scopus 1210407 Convert to negative the commission cost when is a Service Order RMA.
							WHEN 1577 THEN (CASE oe_hdr.rma_flag WHEN 'Y' THEN (-1) * ABS(p21_view_invoice_line.commission_cost * p21_view_invoice_line.hours_worked)
																 ELSE p21_view_invoice_line.commission_cost * p21_view_invoice_line.hours_worked END)
							WHEN 1719 THEN p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped
							ELSE drv_sumlines.sum_cmsn_cost 																
						 END)
			      ELSE (p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped)
			      END)
		ELSE 	(p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped)
		END line_commission_cost,
		CASE COALESCE (oe_line.pricing_option, 0)
		WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN 0 
						ELSE (p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped) 
					END)
		WHEN 2 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN (p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped)
						ELSE 0 
					END)
		WHEN 3 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN (p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped)
						ELSE 0 
					END)
		WHEN 4 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
						WHEN 0 THEN 0 
						ELSE (p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped) 
					END)
		WHEN 0 THEN  ( CASE oe_hdr.order_type WHEN 1706 THEN 
						(CASE p21_view_invoice_line.invoice_line_type
							WHEN 1577 THEN p21_view_invoice_line.other_cost * p21_view_invoice_line.hours_worked
							WHEN 1719 THEN p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped
							ELSE drv_sumlines.sum_other_cost
						 END)
			      ELSE (p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped)
			      END)
		ELSE 	(p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped)
		END line_other_cost,
		p21_view_invoice_line.customer_part_number,   
		p21_view_invoice_line.tax_item,   
		p21_view_invoice_line.line_no,   
		p21_view_invoice_line.oe_line_number,   
		--19.1 EH 11/15/18 - Scopus 1410568: MRO invoices doesn't have correct other_charge_item flag set. 
		--				Changed to get the flag from inv_mast for MRO invoice items.
		--p21_view_invoice_line.other_charge_item,   
		CASE WHEN oe_hdr.order_type=1877 THEN inv_mast.other_charge_item ELSE p21_view_invoice_line.other_charge_item END other_charge_item,
		p21_view_invoice_line.pricing_unit,  
		p21_view_invoice_line.invoice_line_uid,
		p21_view_invoice_line.invoice_line_uid_parent,
		p21_view_invoice_line.invoice_line_type,
		oe_hdr.order_date,
		COALESCE(invoice_hdr.sales_location_id, oe_hdr.location_id, 0) sales_location_id,
		oe_hdr.contact_id,
		COALESCE(oe_hdr.rma_flag, 'N') rma_flag,
		COALESCE(oe_hdr.taker, 'N/A') taker,
		oe_hdr.job_name,
		oe_line.manual_price_overide,
		COALESCE(p21_view_invoice_line.supplier_id, oe_line.supplier_id, 0) supplier_id,
		COALESCE((	CASE p21_view_invoice_line.product_group_id
					WHEN '' THEN NULL
					ELSE p21_view_invoice_line.product_group_id
				END),
			(	CASE oe_line.product_group_id
					WHEN '' THEN NULL
					ELSE oe_line.product_group_id
				END),
		'N/A') product_group_id, 
        product_group_inv_mast.product_group_id product_group_id_inv_mast,
		product_group_inv_mast.product_group_desc product_group_desc_inv_mast,
		oe_line.source_loc_id,
		oe_line.ship_loc_id,
		COALESCE (p21_view_invoice_line.sales_unit_size, oe_line.unit_size, 1) AS unit_size,	
		COALESCE(p21_view_invoice_line.pricing_unit_size,oe_line.pricing_unit_size, 1) AS pricing_unit_size,	
		customer.customer_name,
		COALESCE(customer.class_1id, 'N/A') class_1id,
		COALESCE(customer.class_2id, 'N/A') class_2id,
		COALESCE(customer.class_3id, 'N/A') class_3id,
		COALESCE(customer.class_4id, 'N/A') class_4id,
		COALESCE(customer.class_5id, 'N/A') class_5id,
		contacts.first_name,
		contacts.mi,
		contacts.last_name,
		CASE
		WHEN p21_view_invoice_line.supplier_id IS NULL THEN
			CASE 
				WHEN oe_line.supplier_id IS NULL THEN 'Unknown'
				ELSE supplier_oe.supplier_name
			END
		ELSE supplier_invoice.supplier_name
		END supplier_name,
		--Main 12.12 - 04/30/13 - JC - Case#1131595  - Add 'N/A to COALESCE to be returned if no product group info is available
		COALESCE(product_group.product_group_desc, product_group_oe.product_group_desc, 'N/A') AS product_group_desc,
		COALESCE(location.location_name,  'None') 	location_name,
		COALESCE(oe_line_source_loc.location_name, 'None') 	source_location_name,
		COALESCE(inv_sales_loc.location_name, location.location_name, 'None') sales_location_name,
		COALESCE(oe_line_ship_loc.location_name, 'None') ship_location_name,
		invoice_hdr_salesrep.salesrep_id,
		invoice_hdr_salesrep.vendor_id,
		invoice_hdr_salesrep.commission_amount,
		invoice_hdr_salesrep.delete_flag,
		invoice_hdr_salesrep.date_paid,
		invoice_hdr_salesrep.commission_paid,
		invoice_hdr_salesrep.primary_salesrep,
		COALESCE(inv_loc.stockable, 'N') 		stockable,
		COALESCE(oe_line.assembly,  'N') 		oe_line_assembly,
		COALESCE(oe_line.parent_oe_line_uid, 0) 	parent_oe_line_uid,
		oe_line.pricing_option,
		(CASE  COALESCE(oe_hdr_advance_billing.advance_bill, 'N')
		WHEN 'Y' THEN (p21_view_invoice_line.QTY_SHIPPED *
			(p21_view_invoice_line.UNIT_PRICE/
			   COALESCE(p21_view_invoice_line.pricing_unit_size,1)))
		WHEN 'N' THEN	   	
		CASE COALESCE (oe_line.pricing_option,0)			 		
		WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent	
		WHEN 0 THEN 0 
		ELSE p21_view_invoice_line.EXTENDED_PRICE END)
		WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE 
		ELSE 0 END)
		WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE 
		ELSE 0 END)
		WHEN 4 THEN  0 
		WHEN 0 THEN ( CASE oe_hdr.order_type WHEN 1706 THEN 
                      				(CASE WHEN p21_view_invoice_line.invoice_line_type = 0 THEN 
							drv_sumlines.sum_price -- + COALESCE(drv_sumtaxes.sum_taxes,0)
					  	ELSE p21_view_invoice_line.EXTENDED_PRICE	
					  	END)
                              ELSE p21_view_invoice_line.EXTENDED_PRICE 
			      END )	
		ELSE p21_view_invoice_line.EXTENDED_PRICE
		END
		END) sales_price,
		invoice_hdr.total_amount AS total_sales,
		invoice_hdr.tax_amount,
		invoice_hdr.other_charge_amount,
		CASE p21_view_invoice_line.other_charge_item
		WHEN 'Y' THEN 	   				
			(CASE COALESCE (oe_line.pricing_option,0)
			   	WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   						WHEN 0 THEN 0.00 ELSE p21_view_invoice_line.COGS_AMOUNT END)
		
		   		WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   						WHEN 0 THEN p21_view_invoice_line.COGS_AMOUNT ELSE 0.00 END)
		
		   		WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   						WHEN 0 THEN p21_view_invoice_line.COGS_AMOUNT ELSE 0.00 END)
		
		   		WHEN 4 THEN  0.00 
		   		ELSE 	p21_view_invoice_line.COGS_AMOUNT END)
			ELSE 0.00 
		END other_charge_cost,
		CASE p21_view_invoice_line.other_charge_item 	
		WHEN 'Y' THEN					   
		(CASE COALESCE (oe_line.pricing_option,0)
			WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
							WHEN 0 THEN 0.00 ELSE p21_view_invoice_line.COMMISSION_COST END)
			WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
							WHEN 0 THEN p21_view_invoice_line.COMMISSION_COST ELSE 0.00 END)
			WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
							WHEN 0 THEN p21_view_invoice_line.COMMISSION_COST ELSE 0.00 END)
			WHEN 4 THEN  0.00 
			ELSE 	p21_view_invoice_line.COMMISSION_COST END)
		ELSE 0.00 
		END commission_other_charge_cost,
		CASE p21_view_invoice_line.other_charge_item 	
		WHEN 'Y' THEN 				 
		(CASE COALESCE (oe_line.pricing_option, 0)
		   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN 0.00 ELSE p21_view_invoice_line.OTHER_COST END)
		WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN p21_view_invoice_line.OTHER_COST ELSE 0.00 END)
		WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN p21_view_invoice_line.OTHER_COST ELSE 0.00 END)
		WHEN 4 THEN  0.00 
		ELSE 	p21_view_invoice_line.OTHER_COST END)
		ELSE 0.00 
		END other_other_charge_cost,
		CASE  p21_view_invoice_line.other_charge_item 	
		WHEN 'Y' THEN 					
		CASE COALESCE (oe_line.pricing_option,0)
		   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN 0.0000 ELSE p21_view_invoice_line.EXTENDED_PRICE END)
		WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE ELSE 0.0000 END)
		WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE ELSE 0.0000 END)
		WHEN 4 THEN  0.0000 
		ELSE 	p21_view_invoice_line.EXTENDED_PRICE
		END 
		WHEN 'N' THEN  0.0000
		END other_charge_line_price,
		COALESCE(oe_hdr.projected_order, 'N') projected_order
		,CAST(CAST(invoice_hdr.year_for_period AS CHAR(4))
			+ RIGHT('00' + CAST(invoice_hdr.period AS VARCHAR(2)), 2) AS INTEGER) year_and_period,
		COALESCE(oe_hdr_advance_billing.advance_bill, 'N') advance_bill
		,oe_line.sales_cost
		,(p21_view_invoice_line.commission_cost * COALESCE(p21_view_invoice_line.sales_unit_size, 1)) as commission_cost 
		,(p21_view_invoice_line.other_cost * COALESCE(p21_view_invoice_line.sales_unit_size, 1)) as other_cost 
		,COALESCE(inv_mast.vendor_consigned, 'N') vendor_consigned,
		CASE 
		WHEN customer.currency_id <> company.home_currency_id
		THEN 'F'
		Else 'H'
		End currency_type,
		CASE COALESCE (oe_line.pricing_option, 0)
		   WHEN 1 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
		WHEN 0 THEN 0 
		ELSE (p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped) END)
		WHEN 2 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN (p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped) 
		ELSE 0 END)
		WHEN 3 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
		WHEN 0 THEN (p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped) 
		ELSE 0 END)
		WHEN 4 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
					WHEN 0 THEN 0 
					ELSE (p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped)
				END)
		WHEN 0 THEN (CASE WHEN oe_hdr.order_type = 1706 THEN 
							(CASE p21_view_invoice_line.invoice_line_type -- GGR Scopus 1210407 Convert to negative the commission cost when is a Service Order RMA.
								WHEN 1577 THEN (CASE oe_hdr.rma_flag WHEN 'Y' THEN (-1) * ABS(p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.hours_worked)
																     ELSE p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.hours_worked END)
								WHEN 1719 THEN p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped
								ELSE drv_sumlines.sum_cmsn_cost_home
							END)
    	                     ELSE (p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped)
			     END)		
		ELSE 	(p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped)
		END line_commission_cost_home,
		CASE p21_view_invoice_line.other_charge_item 	
		WHEN 'Y' THEN					   
		(CASE COALESCE (oe_line.pricing_option,0)
			   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN 0.00 ELSE p21_view_invoice_line.COMMISSION_COST_home END)
		
		   WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.COMMISSION_COST_home ELSE 0.00 END)
		
		   WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.COMMISSION_COST_home ELSE 0.00 END)
		
		   WHEN 4 THEN  0.00 
		   ELSE 	p21_view_invoice_line.COMMISSION_COST_home END)
		ELSE 0.00 
		END commission_other_charge_cost_h,
		
		
		CASE COALESCE (oe_line.pricing_option, 0)
		   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN 0 
		ELSE (p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped) END)
		
		WHEN 2 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN (p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped)
		ELSE 0 END)
		
		WHEN 3 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN (p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped)
		ELSE 0 END)
		
	   	WHEN 1 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent
					WHEN 0 THEN 0 
					ELSE (p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped)
				END)
 

		WHEN 0 THEN  (CASE WHEN oe_hdr.order_type = 1706 THEN 
							(CASE p21_view_invoice_line.invoice_line_type
								WHEN 1577 THEN p21_view_invoice_line.other_cost_home * p21_view_invoice_line.hours_worked
								WHEN 1719 THEN p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped
								ELSE drv_sumlines.sum_other_cost_home
							END)
						ELSE (p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped)
					END)
		ELSE 	(p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped)
		
		END line_other_cost_home,
		
		CASE p21_view_invoice_line.other_charge_item 	
		WHEN 'Y' THEN 				 
		(CASE COALESCE (oe_line.pricing_option, 0)
			   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN 0.00 ELSE p21_view_invoice_line.OTHER_COST_home END)
		
		   WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.OTHER_COST_home ELSE 0.00 END)
		
		   WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.OTHER_COST_home ELSE 0.00 END)
		
		   WHEN 4 THEN  0.00 
		   ELSE 	p21_view_invoice_line.OTHER_COST_home END)
		ELSE 0.00 
		END other_other_charge_cost_home,
		
		CASE COALESCE (oe_line.pricing_option, 0)
		   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent 
			WHEN 0 THEN 0 ELSE p21_view_invoice_line.cogs_amount_home END)
		
		WHEN 2 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent 
			WHEN 0 THEN p21_view_invoice_line.cogs_amount_home ELSE 0 END)
		
		WHEN 3 THEN (CASE p21_view_invoice_line.invoice_line_uid_parent 
			WHEN 0 THEN p21_view_invoice_line.cogs_amount_home ELSE 0 END)
 

		WHEN 0 THEN ( CASE oe_hdr.order_type WHEN 1706 THEN 
                      			(CASE WHEN p21_view_invoice_line.invoice_line_type = 0 THEN 
							drv_sumlines.sum_cogs_home
					  ELSE p21_view_invoice_line.cogs_amount_home 	
					  END)
                              ELSE p21_view_invoice_line.cogs_amount_home
				 END )
         	ELSE p21_view_invoice_line.cogs_amount_home 
		END cogs_amount_home,  
		CASE p21_view_invoice_line.other_charge_item
		WHEN 'Y' THEN 	   				
		(CASE COALESCE (oe_line.pricing_option,0)
			   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN 0.00 ELSE p21_view_invoice_line.COGS_AMOUNT_home END)
		
		   WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.COGS_AMOUNT_home ELSE 0.00 END)
		
		   WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.COGS_AMOUNT_home ELSE 0.00 END)
		
		   WHEN 4 THEN  0.00 
		   ELSE 	p21_view_invoice_line.COGS_AMOUNT_home END)
		ELSE 0.00 
		END other_charge_cost_home,
		
		(CASE  COALESCE(oe_hdr_advance_billing.advance_bill, 'N')
		WHEN 'Y' THEN (p21_view_invoice_line.QTY_SHIPPED *
				(p21_view_invoice_line.UNIT_PRICE_home/
				 p21_view_invoice_line.PRICING_UNIT_SIZE))	
		WHEN 'N' THEN	   	
		CASE COALESCE (oe_line.pricing_option,0)			 		
		WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent	
			WHEN 0 THEN 0 
			ELSE p21_view_invoice_line.EXTENDED_PRICE_home END)
		
		WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
			WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE_home 
			ELSE 0 END)
		
		WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		        WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE_home 
			ELSE 0 END)
		
		WHEN 4 THEN  0 

		WHEN 0 THEN ( CASE oe_hdr.order_type WHEN 1706 THEN 
                                         (CASE WHEN p21_view_invoice_line.invoice_line_type = 0 THEN 
							drv_sumlines.sum_price_home -- + COALESCE(drv_sumtaxes.sum_taxes,0)
					  ELSE p21_view_invoice_line.EXTENDED_PRICE_home	
					  END)
                              ELSE p21_view_invoice_line.EXTENDED_PRICE_home
				 END )	
		ELSE p21_view_invoice_line.EXTENDED_PRICE_home
		END
		END) sales_price_home,
		
		CASE  p21_view_invoice_line.other_charge_item 	
		WHEN 'Y' THEN 					
		CASE COALESCE (oe_line.pricing_option,0)
			   WHEN 1 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN 0.0000 ELSE p21_view_invoice_line.EXTENDED_PRICE_home END)
		
		   WHEN 2 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE_home ELSE 0.0000 END)
		
		   WHEN 3 THEN ( CASE p21_view_invoice_line.invoice_line_uid_parent
		   	WHEN 0 THEN p21_view_invoice_line.EXTENDED_PRICE_home ELSE 0.0000 END)
		
		   WHEN 4 THEN  0.0000 
		   ELSE 	p21_view_invoice_line.EXTENDED_PRICE_home
		END 
		WHEN 'N' THEN  0.0000
		END other_charge_line_price_home,
		invoice_hdr.currency_line_uid,
		currency_hdr.currency_desc,
		company.home_currency_id,
		
		CASE WHEN  (oe_line.pricing_option IN (3,2,1) AND    --If its the assembly header irrespective of the Pricing Option
		            p21_view_invoice_line.invoice_line_uid_parent = 0)--set to extended_price
			    OR
			   (oe_line.pricing_option =  0 AND
			    p21_view_invoice_line.invoice_line_uid_parent =0) --Ordinary Item being invoiced	
			    OR	
			   (oe_line.pricing_option IS NULL AND
			    p21_view_invoice_line.invoice_line_uid_parent =0)  --Manual Invoices/Credit Memos/Debit Memos
 	  		    OR
                           (oe_line.pricing_option =  0 AND
		   	    p21_view_invoice_line.invoice_line_uid_parent =0 )
	    	THEN  p21_view_invoice_line.extended_price 

		ELSE 0.00
		END assembly_sales_price,
		CASE WHEN  (oe_line.pricing_option IN (3,2,1) AND    --If its the assembly header irrespective of the Pricing Option
		            p21_view_invoice_line.invoice_line_uid_parent = 0)--set to extended_price
			    OR
			   (oe_line.pricing_option =  0 AND
			    p21_view_invoice_line.invoice_line_uid_parent =0) --Ordinary Item being invoiced	
			    OR	
			   (oe_line.pricing_option IS NULL AND
			    p21_view_invoice_line.invoice_line_uid_parent =0)  --Manual Invoices/Credit Memos/Debit Memos
 			    OR
                           (oe_line.pricing_option =  0 AND
		    	    p21_view_invoice_line.invoice_line_uid_parent =0 )
	    		THEN  p21_view_invoice_line.extended_price_home 

			ELSE 0.00
			END assembly_sales_price_home,

			CASE WHEN  (oe_line.pricing_option IN (3,2,1) AND    --If its the assembly header irrespective of the Pricing Option
			 	    p21_view_invoice_line.invoice_line_uid_parent = 0)--set to extended_price
				    OR
				   (oe_line.pricing_option =  0 AND
				    p21_view_invoice_line.invoice_line_uid_parent =0) --Ordinary Item being invoiced	
				    OR	
				   (oe_line.pricing_option IS NULL AND
				    p21_view_invoice_line.invoice_line_uid_parent =0)  --Manual Invoices/Credit Memos/Debit Memos
			 	    OR 
				    (oe_line.pricing_option =  0 AND
				     p21_view_invoice_line.invoice_line_uid_parent =0 )
				THEN  p21_view_invoice_line.cogs_amount
			
				ELSE 0.00
			END assembly_cogs_amount,


			CASE WHEN  (oe_line.pricing_option IN (3,2,1) AND    --If its the assembly header irrespective of the Pricing Option
					p21_view_invoice_line.invoice_line_uid_parent = 0)--set to extended_price
					OR
					(oe_line.pricing_option =  0 AND
					p21_view_invoice_line.invoice_line_uid_parent =0) --Ordinary Item being invoiced	
					OR	
					(oe_line.pricing_option IS NULL AND
					p21_view_invoice_line.invoice_line_uid_parent =0)  --Manual Invoices/Credit Memos/Debit Memos
					OR 
					(oe_line.pricing_option =  0 AND
					p21_view_invoice_line.invoice_line_uid_parent =0 )
				THEN  p21_view_invoice_line.cogs_amount_home
				ELSE 0.00
				END assembly_cogs_amt_home

		,CASE 
			WHEN (p21_view_invoice_line.invoice_line_uid_parent = 0) AND ((oe_line.pricing_option IN (0,1,2,3)) OR (oe_line.pricing_option IS NULL)) 
				THEN	CASE oe_line.lot_bill
							WHEN 'Y' THEN
										-- Que ZFG 09/15/09 - Scopus 815592: Only use derived table for nested lot bills.
										CASE oe_line_lot_billing.price_basis
											WHEN 'N' THEN drv_nested_lot_header_comm_cost.commission_cost
											ELSE p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped
										END
							ELSE p21_view_invoice_line.commission_cost * p21_view_invoice_line.qty_shipped
						END
				ELSE 0.00
		 END assembly_commission_cost
		,CASE 
			WHEN (p21_view_invoice_line.invoice_line_uid_parent = 0) AND ((oe_line.pricing_option IN (0,1,2,3)) OR (oe_line.pricing_option IS NULL)) 
				THEN	CASE oe_line.lot_bill
							WHEN 'Y' THEN 
										-- Que ZFG 09/15/09 - Scopus 815592: Only use derived table for nested lot bills.
										CASE oe_line_lot_billing.price_basis
											WHEN 'N' THEN drv_nested_lot_header_comm_cost.commission_cost_home
											ELSE p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped
										END
							ELSE p21_view_invoice_line.commission_cost_home * p21_view_invoice_line.qty_shipped
						END
				ELSE 0.00
		 END assembly_commission_cost_home
		,CASE WHEN  (oe_line.pricing_option IN (3,2,1) AND    --If its the assembly header irrespective of the Pricing Option
				p21_view_invoice_line.invoice_line_uid_parent = 0)--set to extended_price
				OR
				(oe_line.pricing_option =  0 AND
				p21_view_invoice_line.invoice_line_uid_parent =0) --Ordinary Item being invoiced	
				OR	
				(oe_line.pricing_option IS NULL AND
				p21_view_invoice_line.invoice_line_uid_parent =0)  --Manual Invoices/Credit Memos/Debit Memos
				OR 
				(oe_line.pricing_option =  0 AND
				p21_view_invoice_line.invoice_line_uid_parent =0 )
			THEN  p21_view_invoice_line.other_cost * p21_view_invoice_line.qty_shipped
			ELSE 0.00
		END assembly_other_cost
		,CASE WHEN  (oe_line.pricing_option IN (3,2,1) AND    --If its the assembly header irrespective of the Pricing Option
				p21_view_invoice_line.invoice_line_uid_parent = 0)--set to extended_price
				OR
				(oe_line.pricing_option =  0 AND
				p21_view_invoice_line.invoice_line_uid_parent =0) --Ordinary Item being invoiced	
				OR	
				(oe_line.pricing_option IS NULL AND
				p21_view_invoice_line.invoice_line_uid_parent =0)  --Manual Invoices/Credit Memos/Debit Memos
				OR 
				(oe_line.pricing_option =  0 AND
				p21_view_invoice_line.invoice_line_uid_parent =0 )
			THEN  p21_view_invoice_line.other_cost_home * p21_view_invoice_line.qty_shipped
			ELSE 0.00
		END assembly_other_cost_home
		
		,('Branch  : ' +RTrim(COALESCE(invoice_hdr.branch_id, 'None')) +' - '+ RTrim(branch.branch_description)) as branch_info,
		
		('Product Group  : ' +
		RTrim(
		COALESCE(
		
		(CASE p21_view_invoice_line.product_group_id
			WHEN '' THEN NULL
			ELSE p21_view_invoice_line.product_group_id
		END),
		(CASE oe_line.product_group_id
			WHEN '' THEN NULL
			ELSE oe_line.product_group_id
		END),
		'N/A'))+' - '+
		--Main 12.12 - JC - 04/30/13 Case#1131595  - Add 'N/A to COALESCE to be returned if no product group info is available
		RTrim(COALESCE(product_group.product_group_desc, product_group_oe.product_group_desc, 'N/A'))) as product_group_info,
		('Sales Location : ' +RTrim(COALESCE(invoice_hdr.sales_location_id, oe_hdr.location_id, 0))+'-'+
		-- 12.13 JVC 08/21/13 Scopus 1162712: Use sales location from invoice instead of the order
		RTrim(COALESCE(inv_sales_loc.location_name, RTrim(COALESCE(location.location_name, 'None'))))) as sales_location_info,
		('Period : '+RTrim(invoice_hdr.period)+Space(2)+'Year : '+RTrim(invoice_hdr.year_for_period)) as period_year_info,
		('Customer : ' + RTrim(invoice_hdr.sold_to_customer_id)+' - '+ RTrim(customer.customer_name)+
		  ', '+COALESCE(address_history.city, '  ') + ', '+COALESCE(address_history.state, '  ')+
		  ', '+COALESCE(address_history.postal_code,'  ')) as customer_info,
		('Supplier : ' +RTrim( COALESCE(p21_view_invoice_line.supplier_id, oe_line.supplier_id, 0)) +' - ' 
		+RTrim(
		CASE
		WHEN p21_view_invoice_line.supplier_id IS NULL THEN
			CASE 
				WHEN oe_line.supplier_id IS NULL THEN 'Unknown'
				ELSE supplier_oe.supplier_name
			END
		ELSE supplier_invoice.supplier_name
		END
		)) as supplier_info,

		('ShipTo : ' + RTrim(invoice_hdr.ship_to_id)+' - '+ RTrim(invoice_hdr.ship2_name)+ 

		', '+ COALESCE(invoice_hdr.ship2_city, ' ') + ', '+COALESCE(invoice_hdr.ship2_state, ' ')+ 

		', '+ COALESCE(invoice_hdr.ship2_postal_code,' ')) as ship_to_info,

		('Taker : ' +RTrim(COALESCE(oe_hdr.taker, 'N/A'))) as taker_info,
		('Invoice Date : ' + dbo.p21_fn_datepart(invoice_hdr.invoice_date)) as invoice_date_info,
		('SalesRep : ' +RTrim(invoice_hdr_salesrep.salesrep_id)+' - '+ 
		COALESCE(salesrep_contact.first_name, '')+' '+
		COALESCE(salesrep_contact.mi,'')+' '+
		COALESCE(salesrep_contact.last_name,'')) as salesrep_info
		--12.13 YCHEN 10/09/13 - Scopus#1151323: Choose contract_no from job_price_hdr_a, job_price_hdr_b instead. 
		--,COALESCE(job_price_hdr.contract_no,'N/A') as contract_no
		,COALESCE(job_price_hdr_a.contract_no,job_price_hdr_b.contract_no,'N/A') as contract_no
		,oe_line_lot_billing.price_basis--'I' indicates ItemizedLotBilling and 'N' indicates NestedLotBilling
		,oe_line.lot_bill
		,invoice_hdr.inv_no_display
		,invoice_hdr.ar_account_no
		,invoice_hdr.gl_freight_account_no
		,p21_view_invoice_line.gl_revenue_account_no
		,p21_view_invoice_line.gl_salse_tax_account_no
		,p21_view_invoice_line.gl_cogs
		,p21_view_invoice_line.gl_inventory
		--12.13 YCHEN 10/09/13 - Scopus#1151323: Choose consignment_flag from job_price_hdr_a, job_price_hdr_b instead. 
		--,COALESCE(job_price_hdr.consignment_flag,'N')consignment_flag
		,COALESCE(job_price_hdr_a.consignment_flag,job_price_hdr_b.consignment_flag,'N') AS consignment_flag

		,oe_hdr.source_code_no

		,COALESCE(oe_hdr.order_type, 706) AS order_type

		,oe_hdr_mfr.vendor_id as mfr_vendor_id
		,vendor.vendor_name as mfr_vendor_name
		,invoice_hdr.job_id
		,jc_job.job_description

		,drv_sumlines.sum_price extended_price

		,drv_sumlines.sum_price_home  
		,drv_sumlines.sum_cogs
		,drv_sumlines.sum_cogs_home
		,drv_sumlines.sum_cmsn_cost
		,drv_sumlines.sum_cmsn_cost_home

		,p21_view_invoice_line.extended_price as detail_price
		,p21_view_invoice_line.cogs_amount as detail_cogs

		,drv_sumlines.sum_other_cost
		,drv_sumlines.sum_other_cost_home
		,invoice_hdr.record_type_cd
		,oe_line.detail_type
		,prod_order_hdr.prod_order_number
		,prod_order_hdr.order_date AS prod_order_date
		,COALESCE(oe_hdr_progress_billing.progress_bill_flag,'N') AS progress_bill_flag
		,CASE WHEN oe_hdr_progress_billing.row_status_flag = 701
            	 THEN 'Y'
            	 ELSE 'N' 
		 END 'finalized'
        	,CASE WHEN invoice_hdr.record_type_cd = 2530
            	 THEN 'Y'
                 ELSE 'N'
                 END 'final_invoice'
		,oe_hdr_progress_billing.percent_billed
		,invoice_hdr.shipping_cost
		-- Pil RSM 06/23/09 Scopus 794907: Multiply by invoice line in order to get proper dealer_commission_ext_amt value.
		-- 12.10 JAB - 05/30/12 - Scopus 1047310 - Divide by pricing_unit_size in order to get proper dealer_commission_ext_amt value.
		,(COALESCE(oe_line_dealer_commission.dealer_commission_amt,0) * p21_view_invoice_line.qty_shipped / COALESCE(NULLIF(p21_view_invoice_line.pricing_unit_size,0), 1 )) AS dealer_commission_ext_amt
		,COALESCE(inv_mast.default_selling_unit, 'N/A') default_sales_unit_of_measure
		,COALESCE(item_uom.unit_size, 1) default_sales_unit_size

		,''  pass_through_vendor_name
		,'N' pass_through_items 	
		,item_revision.revision_level



		,CASE WHEN COALESCE(invoice_hdr.record_type_cd, 0) = 2594
			THEN 'Y'
			ELSE 'N'
		END 'rebilled_flag'
		--12.8 -JC - 10/14/11 - Scopus#994200 - Add coalesce to return non-null source_type_cd
		,COALESCE(invoice_hdr.source_type_cd, 0) source_type_cd
		--SAR 06/11/2009 - Add Oil functionality.
		,CASE COALESCE(item_uom_gallon.unit_size,0)
			WHEN 0 THEN  0
            ELSE (p21_view_invoice_line.qty_shipped / item_uom_gallon.unit_size)
	     END AS gallons_shipped
		,CASE COALESCE(item_uom_gallon.unit_size,0)
			WHEN 0 THEN 0
           	ELSE (p21_view_invoice_line.qty_requested / item_uom_gallon.unit_size)
	     END AS gallons_ordered
		,system_setting_Gallon.value AS gallons_system_setting
--JC - Projx#46532 - System pricing - Add info from order_floor_plan_xref_10002
		,order_floor_plan_xref_10002.floor_plan_approval_number
		,order_floor_plan_xref_10002.bill_to_id 
		,order_floor_plan_xref_10002.bill_to_multiplier
--ATG 04/15/2015 - Feature 58270: Adding column manufacturing_class_id
--JC 04/22/15 - Feature #58270 - Add COALESCE to eliminate NULL values
		,COALESCE(inventory_supplier.manufacturing_class_id, 'N/A') manufacturing_class_id
--AR 03/21/2019 - Scopus 1411258: Add hidden system setting for showing service parts and labor.
		,system_setting_labor.value AS labor_system_setting
FROM	invoice_hdr  WITH (NOLOCK)
INNER JOIN ship_to WITH (NOLOCK) ON (ship_to.ship_to_id = invoice_hdr.ship_to_id)
INNER JOIN ship_to_salesrep WITH (NOLOCK) on (ship_to_salesrep.ship_to_id = invoice_hdr.ship_to_id and ship_to_salesrep.delete_flag = 'N' and ship_to_salesrep.primary_salesrep = 'Y')
INNER JOIN p21_view_invoice_line  WITH (NOLOCK) ON (invoice_hdr.invoice_no = p21_view_invoice_line.invoice_no)
INNER JOIN company  WITH (NOLOCK) ON (invoice_hdr.company_no = company.company_id)
INNER JOIN customer WITH (NOLOCK) ON (invoice_hdr.sold_to_customer_id = customer.customer_id) 
				AND (invoice_hdr.company_no = customer.company_id)
INNER JOIN address_history  WITH (NOLOCK) ON invoice_hdr.sold_to_ah_uid = address_history.address_history_uid
LEFT OUTER JOIN invoice_hdr_salesrep  WITH (NOLOCK) ON invoice_hdr_salesrep.invoice_number = invoice_hdr.invoice_no
				 AND invoice_hdr_salesrep.primary_salesrep = 'Y' 
LEFT OUTER JOIN contacts AS salesrep_contact  WITH (NOLOCK)ON invoice_hdr_salesrep.salesrep_id = salesrep_contact.id
LEFT OUTER JOIN oe_hdr  WITH (NOLOCK) ON invoice_hdr.order_no = oe_hdr.order_no
LEFT OUTER JOIN oe_hdr_advance_billing  WITH (NOLOCK) ON oe_hdr_advance_billing.order_no = invoice_hdr.order_no
LEFT OUTER JOIN inv_loc  WITH (NOLOCK) ON p21_view_invoice_line.inv_mast_uid = inv_loc.inv_mast_uid
	 AND invoice_hdr.sales_location_id = inv_loc.location_id
	 AND invoice_hdr.company_no = inv_loc.company_id
LEFT OUTER JOIN oe_line  WITH (NOLOCK) ON (p21_view_invoice_line.oe_line_number = oe_line.line_no) 
	 AND (p21_view_invoice_line.order_no = oe_line.order_no)
-- 12.8 ZFG 10/04/11 - Scopus 1000981: Do not join to oe_line for service labor invoice records.
	 AND (p21_view_invoice_line.invoice_line_type <> 1577)
LEFT OUTER JOIN branch  WITH (NOLOCK) ON (invoice_hdr.company_no = branch.company_id) 
				AND (invoice_hdr.branch_id = branch.branch_id) 
LEFT OUTER JOIN contacts  WITH (NOLOCK) ON (oe_hdr.contact_id = contacts.id) 
LEFT JOIN supplier supplier_oe  WITH (NOLOCK) ON (oe_line.supplier_id = supplier_oe.supplier_id)
LEFT JOIN supplier supplier_invoice  WITH (NOLOCK) ON (p21_view_invoice_line.supplier_id = supplier_invoice.supplier_id)
LEFT JOIN product_group  WITH (NOLOCK) ON (p21_view_invoice_line.company_id = product_group.company_id
								AND p21_view_invoice_line.product_group_id = product_group.product_group_id)
LEFT OUTER JOIN inv_mast  WITH (NOLOCK) ON (p21_view_invoice_line.inv_mast_uid = inv_mast.inv_mast_uid)
LEFT JOIN product_group AS product_group_inv_mast  WITH (NOLOCK) ON (inv_mast.default_purchase_disc_group = product_group_inv_mast.product_group_id)
LEFT JOIN product_group AS product_group_oe  WITH (NOLOCK) ON (p21_view_invoice_line.company_id = product_group_oe.company_id)
													AND (oe_line.product_group_id = product_group_oe.product_group_id)
LEFT OUTER JOIN location  WITH (NOLOCK) ON (oe_hdr.location_id = location.location_id)
LEFT OUTER JOIN location AS oe_line_source_loc  WITH (NOLOCK) ON (oe_line.source_loc_id = oe_line_source_loc.location_id)
LEFT OUTER JOIN location AS oe_line_ship_loc (NOLOCK) ON (oe_line.ship_loc_id = oe_line_ship_loc.location_id)
-- 12.13 JVC 08/21/13 Scopus 1162712: Join to location to get the sales location from invoice
LEFT OUTER JOIN location AS inv_sales_loc (NOLOCK) ON (invoice_hdr.sales_location_id = inv_sales_loc.location_id)
LEFT OUTER JOIN shipping_route  WITH (NOLOCK) ON (invoice_hdr.shipping_route_uid = shipping_route.shipping_route_uid)

--12.13 YCHEN 10/09/13 - Scopus#1151323: used job_price_line_a,job_price_hdr_a ,job_price_line_b, job_price_hdr_b, added join to price_page.
--LEFT OUTER JOIN job_price_line  WITH (NOLOCK) ON 
--		    job_price_line.job_price_line_uid = p21_view_invoice_line.job_price_line_uid
--LEFT OUTER JOIN job_price_hdr  WITH (NOLOCK) ON
--		    job_price_hdr.job_price_hdr_uid = job_price_line.job_price_hdr_uid
LEFT OUTER JOIN job_price_line job_price_line_a WITH (NOLOCK) ON 
		    job_price_line_a.job_price_line_uid = p21_view_invoice_line.job_price_line_uid
LEFT OUTER JOIN job_price_hdr job_price_hdr_a WITH (NOLOCK) ON
		    job_price_hdr_a.job_price_hdr_uid = job_price_line_a.job_price_hdr_uid 
LEFT OUTER JOIN price_page WITH (NOLOCK) on price_page.price_page_uid = p21_view_invoice_line.cost_price_page_uid
LEFT OUTER JOIN job_price_line job_price_line_b WITH (NOLOCK) ON job_price_line_b.job_price_line_uid = price_page.contract_line_uid
LEFT OUTER JOIN job_price_hdr job_price_hdr_b WITH (NOLOCK) ON
		    job_price_hdr_b.job_price_hdr_uid = job_price_line_b.job_price_hdr_uid	

LEFT OUTER JOIN address  WITH (NOLOCK) ON address.id = customer.customer_id	 
INNER JOIN currency_hdr  WITH (NOLOCK) ON currency_hdr.currency_id= customer.currency_id
LEFT OUTER JOIN oe_line_lot_billing  WITH (NOLOCK) ON oe_line_lot_billing.oe_line_uid = oe_line.oe_line_uid
LEFT OUTER JOIN
(
	SELECT	
		invoice_line.invoice_no
		, COUNT(*) AS counter
	FROM	
        invoice_hdr WITH (NOLOCK) join invoice_line WITH (NOLOCK) on invoice_hdr.invoice_no = invoice_line.invoice_no
	WHERE	
        invoice_hdr.consolidated <> 'C'
		AND invoice_line.other_charge_item = 'N'
		AND invoice_line.tax_item = 'N'
		AND (invoice_line.invoice_line_type = 0
		--this is the top lot group item
		 OR	(invoice_line.invoice_line_type = 4
		AND invoice_line.invoice_line_uid_parent = 0))
		AND invoice_line.item_id NOT IN ('DOWNPAYMENT','PREPAYMENT')
		AND 
		(
			(
				invoice_line.qty_shipped > 0.0000  
				AND invoice_line.item_id <> 'ADVANCE BILL AMOUNT'
			) 
			OR
			(
				invoice_line.qty_shipped < 0.0000  
				AND invoice_line.item_id <> 'ADVANCE BILL AMOUNT'
			)
		)	
	GROUP BY 	
		invoice_line.invoice_no
) invoice_line_summary
	ON	invoice_line_summary.invoice_no = invoice_hdr.invoice_no
LEFT JOIN oe_hdr_mfr  WITH (NOLOCK) ON oe_hdr_mfr.oe_hdr_uid = oe_hdr.oe_hdr_uid
LEFT JOIN vendor  WITH (NOLOCK) ON vendor.vendor_id = oe_hdr_mfr.vendor_id
			AND  vendor.company_id = invoice_hdr.company_no
LEFT JOIN jc_job  WITH (NOLOCK) ON jc_job.job_id = invoice_hdr.job_id	

LEFT JOIN (SELECT SUM(invoice_line.cogs_amount) sum_cogs,
				SUM(invoice_line.cogs_amount_home) sum_cogs_home,
				SUM(invoice_line.extended_price) sum_price,
				SUM(invoice_line.extended_price_home) sum_price_home,
				SUM(CASE invoice_line.invoice_line_type
					WHEN 1577 THEN 
							(CASE oe_hdr.rma_flag  -- GGR Scopus 1210407 Convert to negative the commission cost when is a Service Order RMA.
								WHEN 'Y' THEN  (-1) * ABS(invoice_line.commission_cost * invoice_line.hours_worked)
								ELSE invoice_line.commission_cost * invoice_line.hours_worked
								END)
					WHEN 1719 THEN invoice_line.commission_cost*invoice_line.qty_shipped
				END) sum_cmsn_cost,

				SUM(CASE invoice_line.invoice_line_type
					WHEN 1577 THEN invoice_line.other_cost*invoice_line.hours_worked
					WHEN 1719 THEN invoice_line.other_cost*invoice_line.qty_shipped
				END) sum_other_cost,
				SUM(CASE invoice_line.invoice_line_type
					WHEN 1577 THEN invoice_line.other_cost_home*invoice_line.hours_worked
					WHEN 1719 THEN invoice_line.other_cost_home*invoice_line.qty_shipped
				END) sum_other_cost_home,
				SUM(CASE  invoice_line.invoice_line_type
					WHEN 1577 THEN (CASE oe_hdr.rma_flag  -- GGR Scopus 1210407 Convert to negative the commission cost when is a Service Order RMA.
								WHEN 'Y' THEN  (-1) * ABS(invoice_line.commission_cost_home * invoice_line.hours_worked)
								ELSE invoice_line.commission_cost_home * invoice_line.hours_worked
								END)
					WHEN 1719 THEN invoice_line.commission_cost_home*invoice_line.qty_shipped
				END) sum_cmsn_cost_home,
				invoice_line.invoice_line_uid_parent
			FROM p21_view_invoice_line invoice_line
			    -- GGR Scopus 1210407 Add the joins neded to use the oe_hdr.rma_flag.
				INNER JOIN invoice_hdr WITH (NOLOCK) ON (invoice_hdr.invoice_no = invoice_line.invoice_no)
				LEFT OUTER JOIN oe_hdr  WITH (NOLOCK) ON invoice_hdr.order_no = oe_hdr.order_no
			WHERE invoice_line.invoice_line_type IN (1719, 1577)
			GROUP BY invoice_line.invoice_line_uid_parent)
   AS drv_sumlines ON (drv_sumlines.invoice_line_uid_parent = p21_view_invoice_line.invoice_line_uid)

				
LEFT JOIN oe_hdr_progress_billing  WITH (NOLOCK)ON oe_hdr_progress_billing.order_no = oe_hdr.order_no  
                                 AND oe_hdr_progress_billing.progress_bill_flag = 'Y'    
LEFT JOIN prod_order_line_link WITH (NOLOCK) ON prod_order_line_link.transaction_uid = oe_line.oe_line_uid  
                                AND prod_order_line_link.trans_type = 'O'  
				AND oe_hdr_progress_billing.progress_bill_flag = 'Y'

LEFT JOIN prod_order_hdr  WITH (NOLOCK) ON prod_order_hdr.prod_order_number = prod_order_line_link.prod_order_number

LEFT JOIN progress_billing_x_invoice_hdr  WITH (NOLOCK) ON progress_billing_x_invoice_hdr.oe_hdr_progress_billing_uid = 	oe_hdr_progress_billing.oe_hdr_progress_billing_uid AND
 					  progress_billing_x_invoice_hdr.invoice_no= invoice_hdr.invoice_no 		

-- Pil RSM 06/23/09 Scopus 794907: Join back to the oe_line_dealer_commission table.
LEFT JOIN oe_line_dealer_commission ON oe_line_dealer_commission.oe_line_uid = oe_line.oe_line_uid
LEFT JOIN item_uom ON inv_mast.inv_mast_uid = item_uom.inv_mast_uid
				AND inv_mast.default_selling_unit = item_uom.unit_of_measure
LEFT JOIN revision_transaction  WITH (NOLOCK) ON revision_transaction.transaction_no = cast(oe_line.order_no as int)
       AND revision_transaction.transaction_line_no = oe_line.line_no
	   AND revision_transaction.transaction_code_no IN (1533, 1721)
LEFT JOIN item_revision  WITH (NOLOCK) ON item_revision.item_revision_uid = revision_transaction.item_revision_uid
-- DAK 05/29/2009 Scopus: 793123
LEFT JOIN(	SELECT	SUM(p21_view_invoice_line.commission_cost) commission_cost
					,SUM(p21_view_invoice_line.commission_cost_home) commission_cost_home
					,p21_view_invoice_line.invoice_line_uid_parent
			FROM	p21_view_invoice_line
			WHERE	p21_view_invoice_line.invoice_line_type = 4 
			  AND	p21_view_invoice_line.invoice_line_uid_parent <> 0
			GROUP BY 
				p21_view_invoice_line.invoice_line_uid_parent
         ) AS drv_nested_lot_header_comm_cost ON (drv_nested_lot_header_comm_cost.invoice_line_uid_parent = p21_view_invoice_line.invoice_line_uid)

--SAR 06/11/2009 - Add Oil functionality.
LEFT JOIN system_setting system_setting_Gallon  WITH (NOLOCK) ON (system_setting_Gallon.configuration_id = 0) 
													   AND (system_setting_Gallon.name = 'Gallon_UOM')
LEFT JOIN item_uom item_uom_gallon  WITH (NOLOCK) ON (item_uom_gallon.inv_mast_uid = p21_view_invoice_line.inv_mast_uid)
										   AND (item_uom_gallon.unit_of_measure = system_setting_Gallon.value)
-- GGR - 01/22/15 - Scopus 1276785 - Exclude deleted item_uom records.
										   AND  item_uom_gallon.delete_flag = 'N'
--JC - Projx#46532 - System pricing - Add info from order_floor_plan_xref_10002
LEFT JOIN order_floor_plan_xref_10002 ON (order_floor_plan_xref_10002.oe_hdr_uid = oe_hdr.oe_hdr_uid)
--ATG 04/15/2015 - Feature 58270: Adding Left Join with inventory_supplier, so we can access the manufacturing_class_id column
LEFT JOIN inventory_supplier WITH (NOLOCK) ON (inventory_supplier.supplier_id = p21_view_invoice_line.supplier_id)
								AND	(inventory_supplier.inv_mast_uid = p21_view_invoice_line.inv_mast_uid)
--AR 03/21/2019 - Scopus 1411258: Add hidden system setting for showing service parts and labor.
LEFT JOIN system_setting system_setting_labor  WITH (NOLOCK) ON (system_setting_labor.configuration_id = 0) 
													   AND (system_setting_labor.name = 'hide_labor_in_sales_history')
WHERE	invoice_hdr.approved = 'Y'
  AND	invoice_hdr.invoice_class <> 'FINANCE'
  AND	invoice_hdr.invoice_adjustment_type NOT IN ('B', 'T', 'X', 'R', 'W', 'A', 'P', 'Z', 'Y')
  AND	invoice_hdr.consolidated <> 'C'
  AND	p21_view_invoice_line.tax_item = 'N'
  AND 	p21_view_invoice_line.invoice_line_type NOT IN (928, 929, 930, 3)		
  AND	invoice_hdr.original_document_type <> 'T'
  AND	p21_view_invoice_line.item_id NOT IN ('DOWNPAYMENT','PREPAYMENT')
  AND	invoice_hdr_salesrep.primary_salesrep = 'Y'
  AND   (p21_view_invoice_line.qty_shipped > 0.0000
   OR	p21_view_invoice_line.qty_shipped < 0.0000
   -- JAB - 11/26/12 - Scopus 1072443 - Include line when hours_worked is on a service order line
   -- GGR - 11/06/13 - Scopus 1180138 - Include line when extended_price is on a service order line
  OR (p21_view_invoice_line.invoice_line_type = 1577 AND (COALESCE(p21_view_invoice_line.hours_worked, 0) <> 0 OR p21_view_invoice_line.extended_price <> 0)))
 -- )
  AND   p21_view_invoice_line.item_id <> 'ADVANCE BILL AMOUNT'
  AND   ((COALESCE(oe_line.lot_bill,'N') = 'N')
   OR	(oe_line.lot_bill = 'Y'
  AND	p21_view_invoice_line.invoice_line_uid_parent = 0
  AND	p21_view_invoice_line.invoice_line_type = 4)) 	
	




GO


