select 
MARKET_CODE,
	sum(Original_Order_Amt_USD)  as Original_Order_Amt_USD, -- ÃÖÃÊ ¿À´õ ±Ý¾×
	sum(Shipped_Amt_USD_Total)   as Shipped_Amt_USD_Total, -- Ãâ°í ¿Ï·á ±Ý¾×
																												
	sum(Plan_to_Ship_Amt_USD)    as Plan_to_Ship_Amt_USD,  -- Ãâ°í ´ë±â ±Ý¾×
	 100 * (SUM(Shipped_Amt_USD_Total) /  SUM(Original_Order_Amt_USD - Invoiced_Amt_USD)) as Shipped_Percentage_Total, -- Àüü ¿À´õ ´ëºñ Ãâ°í ¿Ï·áµÈ ºñÀ² (%)
	sum(Invoiced_Amt_USD) as Invoiced_Amt_USD, -- ¼ÛÀåó¸® ¿Ï·áµÈ ±Ý¾×
	sum(Plan_to_Invoice_Order_Amt_USD_Tot) as Plan_to_Invoice_Order_Amt_USD_Tot, -- Àüü ¿ÀÇ ¿À´õ ±Ý¾× (±â ¼ÛÀå󸮱ݾ×Àº Á¦¿ÜÇÑ Àܾ×)
	
	
	/* Àüü ¿ÀÇ ¿À´õ Áß ¹ßÁÖµÈ ¿À´õ  */
	SUM(Release_Plan_to_Invoice_Order_Amt_USD_Tot) AS Release_Plan_to_Invoice_Order_Amt_USD_Tot,-- Àüü ¿ÀÇ ¿À´õ Áß ¹ßÁÖ µÈ ¿À´õ ±Ý¾× 
	SUM(Release_Shipped_Amt_USD_Total) as Release_Shipped_Amt_USD_Total, -- ¹ßÁÖµÈ ¿À´õ Áß Ãâ°í ¿Ï·á ±Ý¾×(****)		
		CASE 
		WHEN SUM(Shipped_Amt_USD_Total) = 0 OR SUM(Release_Plan_to_Invoice_Order_Amt_USD_Tot) = 0 THEN 1
		ELSE 100 * (SUM(Release_Shipped_Amt_USD_Total) /  SUM(Release_Plan_to_Invoice_Order_Amt_USD_Tot)) END
	 as Release_Shipped_Percentage, -- ¹ßÁÖµÈ ¿À´õ ´ëºñ Ãâ°í ¿Ï·áµÈ ºñÀ² (%)
	  
	sum(Plan_to_Invoice_Order_Amt_USD_Outstanding) as '(Plan_to_Invoice_Order_Amt_USD_Outstanding)', -- ¿À´õ ¹ßÁÖµÈ °Í Áß¿¡ Ãâ°í ¾ÈµÈ °Ç ±Ý¾× (³³±â ±âÇÑ ³») 
	sum(Plan_to_Invoice_Order_Amt_USD_Overdue) as '(Plan_to_Invoice_Order_Amt_USD_Overdue)' -- ¿À´õ ¹ßÁÖµÈ°Í Áß¿¡ Ãâ°íÁö¿¬µÇ´Â °ÇÀÇ ±Ý¾×

	 /* ¼ö·® ±âÁØ À϶§ */
		--sum(Shipped_Amt_USD_Normal)  as '(Shipped_Amt_USD_Normal)',
		--sum(Shipped_Amt_USD_Overdue) as '(Shipped_Amt_USD_Overdue)',
		--100 * sum(Shipped_Amt_USD_Total / Shipped_Amt_USD_Normal) as OnTimeDelivery, 

from

(
	select 
	MARKET_CODE,
	sum(Original_Order_Amt_USD) as Original_Order_Amt_USD,

	sum(Shipped_Amt_USD) as Shipped_Amt_USD_Total,
	0 as Release_Shipped_Amt_USD_Total,
	
	sum(Plan_to_Ship_Amt_USD) as Plan_to_Ship_Amt_USD,
	 100 * (SUM(Shipped_Amt_USD) /  SUM(Original_Order_Amt_USD - Invoiced_Amt_USD)) as Shipped_Percentage,
	sum(Invoiced_Amt_USD) as Invoiced_Amt_USD,
	sum(Plan_to_Invoice_Order_Amt_USD) as Plan_to_Invoice_Order_Amt_USD_Tot,
	0 AS Release_Plan_to_Invoice_Order_Amt_USD_Tot,
	0 as Plan_to_Invoice_Order_Amt_USD_Outstanding,
	0 as Plan_to_Invoice_Order_Amt_USD_Overdue,

	'All' as [Status] 

	from 
	(
			SELECT     

					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation' 
									  ELSE '--None--'		 				       
								 END AS MARKET_CODE,
							--	 DIM_IN.[Dimension Value Code],
							--	 SL.[Customer Price Group],
								 SUM(SL.QUANTITY) AS Origianl_ORDER_QTY,
								 SUM(SL.[QTY_ SHIPPED (BASE)]) as [Actual Shipped],
								 SUM(SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) AS [PLAN_INV_QTY],
					
					
								 --SUM(SL.QUANTITY * SL.[UNIT PRICE]) AS Original_Order_Amt,
								 --SUM(SL.QUANTITY * SL.[UNIT PRICE] * SH.[Currency Factor]) AS Original_Order_Amt_Factor,
								 --SUM(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) AS Original_Order_Amt_Peg,


								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.QUANTITY * SL.[UNIT PRICE]) 
								END AS Original_Order_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE]) 
								END AS Shipped_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE]) 
								END AS Plan_to_Ship_Amt_USD,

			
								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE]) 
								END AS Invoiced_Amt_USD,
		
								CASE WHEN SH.[Currency Code] = '' THEN  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE])
								END AS Plan_to_Invoice_Order_Amt_USD
				  
							--  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23), CONVERT(VARCHAR(10),getdate(),23) 

			FROM        
					[ITW].[dbo].[ITW PP&F KOREA$SALES HEADER] AS SH
						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$SALES LINE] AS SL ON SH.NO_ = SL.[DOCUMENT NO_] 
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$ITEM] AS IT ON SL.NO_ = IT.NO_
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$COUNTRY_REGION] AS CNT ON SH.[SELL-TO COUNTRY_REGION CODE] = CNT.CODE 
			
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_IN ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_IN.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_IN.[DIMENSION CODE] = 'INDUSTRY' 
				
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_BR ON DIM_BR.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_BR.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_BR.[DIMENSION CODE] = 'BRAND'
		
						LEFT OUTER JOIN ITW.[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_SP ON DIM_SP.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_SP.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_SP.[DIMENSION CODE] = 'SALES PERSON' 				
			
						LEFT OUTER JOIN
						(	SELECT     [DIMENSION CODE], CODE, NAME
							FROM          [ITW].[dbo].[ITW PP&F KOREA$DIMENSION VALUE]
							WHERE      ([DIMENSION CODE] = 'INDUSTRY')
						) 		
						 AS DIM_IN_DESC ON DIM_IN.[DIMENSION VALUE CODE] = DIM_IN_DESC.CODE
			
					
						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_CUST]
							ON [8020_CUST].[Table ID] = '18' and  [8020_CUST].[Dimension Code] = '8020 CUST' and [8020_CUST].No_ = SH.[Sell-to Customer No_]


						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_ITM]
							ON [8020_ITM].[Table ID] = '27' and  [8020_ITM].[Dimension Code] = '8020 ITEM' and [8020_ITM].No_ = SL.[NO_]

						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$PEGGED RATE] AS PEG
						   ON PEG.YEAR = SUBSTRING(CONVERT(VARCHAR, SH.[Posting Date], 112), 1, 4)				
			
			WHERE     (SH.[DOCUMENT TYPE] = '1')
					 and SL.[Quantity (Base)] > SL.[Qty_ Invoiced (Base)]
					 and sh.No_ not in ('SO013002046','SO013000387','SO013000810')
					-- and SH.No_ in ('SO013009209','SO013009222','SO013009086')
		
			group by
		 
					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation'  		
								 ELSE '--None--'		       
								 END,
								 SH.[Currency Code] ,
								 SH.[Requested Delivery Date]
			-- having  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23) < CONVERT(VARCHAR(10),getdate(),23)  -- Àüü ÀÜÁ¸ ¿ÀÇ ¿À´õ ±Ý¾×
						) a 
		group by MARKET_CODE


union all

			select MARKET_CODE,
			sum(Original_Order_Amt_USD) as Original_Order_Amt_USD,
			
			0 as Shipped_Amt_USD_Total,
			0 as Release_Shipped_Amt_USD_Total,

			sum(Plan_to_Ship_Amt_USD) as Plan_to_Ship_Amt_USD,
			 100 * (SUM(Shipped_Amt_USD) /  SUM(Original_Order_Amt_USD - Invoiced_Amt_USD)) as Shipped_Percentage,
			sum(Invoiced_Amt_USD) as Invoiced_Amt_USD,
			0 as Plan_to_Invoice_Order_Amt_USD_Tot,
			0 AS Release_Plan_to_Invoice_Order_Amt_USD_Tot,
			0 as Plan_to_Invoice_Order_Amt_USD_Outstanding,
			sum(Plan_to_Invoice_Order_Amt_USD) as Plan_to_Invoice_Order_Amt_USD_Overdue,

			'Overdue' as [Status] 

			from 
			(
			SELECT     

					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation' 
									  ELSE '--None--'		 				       
								 END AS MARKET_CODE,
							--	 DIM_IN.[Dimension Value Code],
							--	 SL.[Customer Price Group],
								 SUM(SL.QUANTITY) AS Origianl_ORDER_QTY,
								 SUM(SL.[QTY_ SHIPPED (BASE)]) as [Actual Shipped],
								 SUM(SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) AS [PLAN_INV_QTY],
					
					
								 SUM(SL.QUANTITY * SL.[UNIT PRICE]) AS Original_Order_Amt,
								 SUM(SL.QUANTITY * SL.[UNIT PRICE] * SH.[Currency Factor]) AS Original_Order_Amt_Factor,
								 SUM(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) AS Original_Order_Amt_Peg,


								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.QUANTITY * SL.[UNIT PRICE]) 
								END AS Original_Order_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE]) 
								END AS Shipped_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE]) 
								END AS Plan_to_Ship_Amt_USD,

			
								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE]) 
								END AS Invoiced_Amt_USD,
		
								CASE WHEN SH.[Currency Code] = '' THEN  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE])
								END AS Plan_to_Invoice_Order_Amt_USD
				  
							--  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23), CONVERT(VARCHAR(10),getdate(),23) 

			FROM        
					[ITW].[dbo].[ITW PP&F KOREA$SALES HEADER] AS SH
						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$SALES LINE] AS SL ON SH.NO_ = SL.[DOCUMENT NO_] 
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$ITEM] AS IT ON SL.NO_ = IT.NO_
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$COUNTRY_REGION] AS CNT ON SH.[SELL-TO COUNTRY_REGION CODE] = CNT.CODE 
			
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_IN ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_IN.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_IN.[DIMENSION CODE] = 'INDUSTRY' 
				
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_BR ON DIM_BR.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_BR.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_BR.[DIMENSION CODE] = 'BRAND'
		
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_SP ON DIM_SP.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_SP.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_SP.[DIMENSION CODE] = 'SALES PERSON' 				
			
						LEFT OUTER JOIN
						(	SELECT     [DIMENSION CODE], CODE, NAME
							FROM          [ITW].[dbo].[ITW PP&F KOREA$DIMENSION VALUE]
							WHERE      ([DIMENSION CODE] = 'INDUSTRY')
						) 		
						 AS DIM_IN_DESC ON DIM_IN.[DIMENSION VALUE CODE] = DIM_IN_DESC.CODE
			
					
						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_CUST]
							ON [8020_CUST].[Table ID] = '18' and  [8020_CUST].[Dimension Code] = '8020 CUST' and [8020_CUST].No_ = SH.[Sell-to Customer No_]


						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_ITM]
							ON [8020_ITM].[Table ID] = '27' and  [8020_ITM].[Dimension Code] = '8020 ITEM' and [8020_ITM].No_ = SL.[NO_]

						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$PEGGED RATE] AS PEG
						   ON PEG.YEAR = SUBSTRING(CONVERT(VARCHAR, SH.[Posting Date], 112), 1, 4)				
			
			WHERE     (SH.[DOCUMENT TYPE] = '1')
					 and SL.[Quantity (Base)] > SL.[Qty_ Invoiced (Base)]
					 and sh.No_ not in ('SO013002046','SO013000387','SO013000810')
					-- and SH.No_ in ('SO013009209','SO013009222','SO013009086')
		
			group by
		 
					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation'  		
								 ELSE '--None--'		       
								 END,
								 SH.[Currency Code] ,
								 SH.[Requested Delivery Date],
								 Status
			having  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23) < CONVERT(VARCHAR(10),getdate(),23) and Status = '1' -- ¹ßÁÖ¿Ï·áµÇ°í Ãâ°í ³³±â ±âÀÏÀÌ Áö³­ °æ¿ì 
			) b

			group by MARKET_CODE



			union all

			select MARKET_CODE,
			sum(Original_Order_Amt_USD) as Original_Order_Amt_USD,

			0 as Shipped_Amt_USD_Total,
			0 as Release_Shipped_Amt_USD_Total,
						
			sum(Plan_to_Ship_Amt_USD) as Plan_to_Ship_Amt_USD,
			 100 * (SUM(Shipped_Amt_USD) /  SUM(Original_Order_Amt_USD - Invoiced_Amt_USD)) as Shipped_Percentage,
			sum(Invoiced_Amt_USD) as Invoiced_Amt_USD,
			0 as Plan_to_Invoice_Order_Amt_USD_Tot,
			0 AS Release_Plan_to_Invoice_Order_Amt_USD_Tot,
			sum(Plan_to_Invoice_Order_Amt_USD) as Plan_to_Invoice_Order_Amt_USD_Outstanding,
			0 as Plan_to_Invoice_Order_Amt_USD_Overdue,
			'Normal' as [Status] 

			from 
			(
			SELECT     

					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation' 
									  ELSE '--None--'		 				       
								 END AS MARKET_CODE,
							--	 DIM_IN.[Dimension Value Code],
							--	 SL.[Customer Price Group],
								 SUM(SL.QUANTITY) AS Origianl_ORDER_QTY,
								 SUM(SL.[QTY_ SHIPPED (BASE)]) as [Actual Shipped],
								 SUM(SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) AS [PLAN_INV_QTY],
					
					
								 SUM(SL.QUANTITY * SL.[UNIT PRICE]) AS Original_Order_Amt,
								 SUM(SL.QUANTITY * SL.[UNIT PRICE] * SH.[Currency Factor]) AS Original_Order_Amt_Factor,
								 SUM(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) AS Original_Order_Amt_Peg,


								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.QUANTITY * SL.[UNIT PRICE]) 
								END AS Original_Order_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE]) 
								END AS Shipped_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE]) 
								END AS Plan_to_Ship_Amt_USD,

			
								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE]) 
								END AS Invoiced_Amt_USD,
		
								CASE WHEN SH.[Currency Code] = '' THEN  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE])
								END AS Plan_to_Invoice_Order_Amt_USD
				  
							--  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23), CONVERT(VARCHAR(10),getdate(),23) 

			FROM        
					[ITW].[dbo].[ITW PP&F KOREA$SALES HEADER] AS SH
						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$SALES LINE] AS SL ON SH.NO_ = SL.[DOCUMENT NO_] 
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$ITEM] AS IT ON SL.NO_ = IT.NO_
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$COUNTRY_REGION] AS CNT ON SH.[SELL-TO COUNTRY_REGION CODE] = CNT.CODE 
			
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_IN ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_IN.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_IN.[DIMENSION CODE] = 'INDUSTRY' 
				
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_BR ON DIM_BR.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_BR.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_BR.[DIMENSION CODE] = 'BRAND'
		
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_SP ON DIM_SP.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_SP.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_SP.[DIMENSION CODE] = 'SALES PERSON' 				
			
						LEFT OUTER JOIN
						(	SELECT     [DIMENSION CODE], CODE, NAME
							FROM          [ITW].[dbo].[ITW PP&F KOREA$DIMENSION VALUE]
							WHERE      ([DIMENSION CODE] = 'INDUSTRY')
						) 		
						 AS DIM_IN_DESC ON DIM_IN.[DIMENSION VALUE CODE] = DIM_IN_DESC.CODE
			
					
						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_CUST]
							ON [8020_CUST].[Table ID] = '18' and  [8020_CUST].[Dimension Code] = '8020 CUST' and [8020_CUST].No_ = SH.[Sell-to Customer No_]


						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_ITM]
							ON [8020_ITM].[Table ID] = '27' and  [8020_ITM].[Dimension Code] = '8020 ITEM' and [8020_ITM].No_ = SL.[NO_]

						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$PEGGED RATE] AS PEG
						   ON PEG.YEAR = SUBSTRING(CONVERT(VARCHAR, SH.[Posting Date], 112), 1, 4)				
			
			WHERE     (SH.[DOCUMENT TYPE] = '1')
					 and SL.[Quantity (Base)] > SL.[Qty_ Invoiced (Base)]
					 and sh.No_ not in ('SO013002046','SO013000387','SO013000810')
					-- and SH.No_ in ('SO013009209','SO013009222','SO013009086')
		
			group by
		 
					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation'  		
								 ELSE '--None--'		       
								 END,
								 SH.[Currency Code] ,
								 SH.[Requested Delivery Date], Status
			having  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23) > CONVERT(VARCHAR(10),getdate(),23) and Status = '1'  -- ¹ßÁÖ¿Ï·áµÇ°í Ãâ°í ³³±â ±âÀÏÀÌ ³²Àº °æ¿ì 
			) c

			group by MARKET_CODE	
			
union all
			
	select 
			MARKET_CODE,
			sum(Original_Order_Amt_USD) as Original_Order_Amt_USD,
				
			0 as Shipped_Amt_USD_Total,
			sum(Shipped_Amt_USD) as Release_Shipped_Amt_USD_Total,

			sum(Plan_to_Ship_Amt_USD) as Plan_to_Ship_Amt_USD,
			 100 * (SUM(Shipped_Amt_USD) /  SUM(Original_Order_Amt_USD - Invoiced_Amt_USD)) as Shipped_Percentage,
			sum(Invoiced_Amt_USD) as Invoiced_Amt_USD,
			0 as Plan_to_Invoice_Order_Amt_USD_Tot,
			sum(Plan_to_Invoice_Order_Amt_USD) AS Release_Plan_to_Invoice_Order_Amt_USD_Tot, -- Àüü ¿ÀÇ ¿À´õ Áß ¹ßÁÖ µÈ ¿À´õ ±Ý¾×  
			0 as Plan_to_Invoice_Order_Amt_USD_Outstanding,
			0 as Plan_to_Invoice_Order_Amt_USD_Overdue,
			'All' as [Status] 
		
	from 
	(
			SELECT     

					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation' 
									  ELSE '--None--'		 				       
								 END AS MARKET_CODE,
							--	 DIM_IN.[Dimension Value Code],
							--	 SL.[Customer Price Group],
								 SUM(SL.QUANTITY) AS Origianl_ORDER_QTY,
								 SUM(SL.[QTY_ SHIPPED (BASE)]) as [Actual Shipped],
								 SUM(SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) AS [PLAN_INV_QTY],
					
					
								 --SUM(SL.QUANTITY * SL.[UNIT PRICE]) AS Original_Order_Amt,
								 --SUM(SL.QUANTITY * SL.[UNIT PRICE] * SH.[Currency Factor]) AS Original_Order_Amt_Factor,
								 --SUM(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) AS Original_Order_Amt_Peg,


								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.QUANTITY * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.QUANTITY * SL.[UNIT PRICE]) 
								END AS Original_Order_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ SHIPPED (BASE)] * SL.[UNIT PRICE]) 
								END AS Shipped_Amt_USD,

								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[QTY_ TO SHIP] * SL.[UNIT PRICE]) 
								END AS Plan_to_Ship_Amt_USD,

			
								CASE WHEN SH.[Currency Code] = '' THEN sum(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE SUM(SL.[Qty_ Invoiced (Base)] * SL.[UNIT PRICE]) 
								END AS Invoiced_Amt_USD,
		
								CASE WHEN SH.[Currency Code] = '' THEN  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE] * PEG.[EXCHANGE RATE]) ELSE  SUM((SL.QUANTITY - SL.[Qty_ Invoiced (Base)]) * SL.[UNIT PRICE])
								END AS Plan_to_Invoice_Order_Amt_USD
				  
							--  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23), CONVERT(VARCHAR(10),getdate(),23) 

			FROM        
					[ITW].[dbo].[ITW PP&F KOREA$SALES HEADER] AS SH
						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$SALES LINE] AS SL ON SH.NO_ = SL.[DOCUMENT NO_] 
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$ITEM] AS IT ON SL.NO_ = IT.NO_
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$COUNTRY_REGION] AS CNT ON SH.[SELL-TO COUNTRY_REGION CODE] = CNT.CODE 
			
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_IN ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_IN.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_IN.[DIMENSION CODE] = 'INDUSTRY' 
				
						LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_BR ON DIM_BR.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_BR.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_BR.[DIMENSION CODE] = 'BRAND'
		
						LEFT OUTER JOIN ITW.[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_SP ON DIM_SP.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_SP.[LINE NO_] = SL.[LINE NO_] AND 
							DIM_SP.[DIMENSION CODE] = 'SALES PERSON' 				
			
						LEFT OUTER JOIN
						(	SELECT     [DIMENSION CODE], CODE, NAME
							FROM          [ITW].[dbo].[ITW PP&F KOREA$DIMENSION VALUE]
							WHERE      ([DIMENSION CODE] = 'INDUSTRY')
						) 		
						 AS DIM_IN_DESC ON DIM_IN.[DIMENSION VALUE CODE] = DIM_IN_DESC.CODE
			
					
						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_CUST]
							ON [8020_CUST].[Table ID] = '18' and  [8020_CUST].[Dimension Code] = '8020 CUST' and [8020_CUST].No_ = SH.[Sell-to Customer No_]


						LEFT JOIN [ITW].[dbo].[ITW PP&F Korea$Default Dimension] AS [8020_ITM]
							ON [8020_ITM].[Table ID] = '27' and  [8020_ITM].[Dimension Code] = '8020 ITEM' and [8020_ITM].No_ = SL.[NO_]

						INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$PEGGED RATE] AS PEG
						   ON PEG.YEAR = SUBSTRING(CONVERT(VARCHAR, SH.[Posting Date], 112), 1, 4)				
			
			WHERE     (SH.[DOCUMENT TYPE] = '1')
					 and SL.[Quantity (Base)] > SL.[Qty_ Invoiced (Base)]
					 and sh.No_ not in ('SO013002046','SO013000387','SO013000810')
					-- and SH.No_ in ('SO013009209','SO013009222','SO013009086')
					and Status = '1'  -- ¹ßÁÖµÈ ¿À´õ¸¸ Filtering
		
			group by
		 
					 CASE
				 					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 
										CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids' 
						   					 WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%')  THEN 'Industrial MRO - Fluids'   
										END
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'  
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)' 
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'  	
									 WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation'  		
								 ELSE '--None--'		       
								 END,
								 SH.[Currency Code] ,
								 SH.[Requested Delivery Date]
			-- having  CONVERT(VARCHAR(10),SH.[Requested Delivery Date],23) < CONVERT(VARCHAR(10),getdate(),23) 
			) d 
		group by MARKET_CODE
			

) total 
group by MARKET_CODE