invenotry sql

select
VE.[Entry No_],
H.[Document Type],
H.[Document No_],
IL.[Document No_] [Invoice_Shipment No_],
H.[State],
H.[City],
H.[Sell-to Customer No_],
H.[Customer Posting Group],
'Item Ledger' Source,
VE.[Salespers__Purch_ Code] [Salesperson Code],
C.[Territory Code],
VE.[Posting Date],
'Item' Type,
1 [Type No_],
IL.[Item No_] [No_],
-VE.[Invoiced Quantity] Quantity,
VE.[Sales Amount (Actual)] [Sales Amount],
VE.[Cost Amount (Actual)] [Cost Amount],
YEAR(VE.[Posting Date]) AS [Posting Year],
(CASE WHEN MONTH(VE.[Posting Date]) = 1 THEN 'January'
WHEN MONTH(VE.[Posting Date]) = 2 THEN 'February'
WHEN MONTH(VE.[Posting Date]) = 3 THEN 'March'
WHEN MONTH(VE.[Posting Date]) = 4 THEN 'April'
WHEN MONTH(VE.[Posting Date]) = 5 THEN 'May'
WHEN MONTH(VE.[Posting Date]) = 6 THEN 'June'
WHEN MONTH(VE.[Posting Date]) = 7 THEN 'July'
WHEN MONTH(VE.[Posting Date]) = 8 THEN 'August'
WHEN MONTH(VE.[Posting Date]) = 9 THEN 'September'
WHEN MONTH(VE.[Posting Date]) = 10 THEN 'October'
WHEN MONTH(VE.[Posting Date]) = 11 THEN 'November'
WHEN MONTH(VE.[Posting Date]) = 12 THEN 'December'
END) AS [Posting Month Text],
(CASE
WHEN MONTH(VE.[Posting Date]) <= 3 THEN '1'
WHEN MONTH(VE.[Posting Date]) > 3 AND MONTH(VE.[Posting Date]) <= 6 THEN '2'
WHEN MONTH(VE.[Posting Date]) > 6 AND MONTH(VE.[Posting Date]) <= 9 THEN '3'
WHEN MONTH(VE.[Posting Date]) > 9 AND MONTH(VE.[Posting Date]) <= 12 THEN '4' END) AS [Posting Quarter],
MONTH(VE.[Posting Date]) AS [Posting Month Number],
IL.[Product Group Code],
IL.[Item Category Code],
IL.[Variant Code],
IL.[Drop Shipment],
IL.[Location Code],
IL.[Source No_] Customer,
IL.[External Document No_],
IL.[Unit of Measure Code],
IL.[Purchasing Code],
VE.[Inventory Posting Group],
VE.[Source Posting Group],
VE.[Gen_ Bus_ Posting Group],
VE.[Gen_ Prod_ Posting Group],
VE.[Global Dimension 1 Code],
VE.[Global Dimension 2 Code]
from [CRONUS USA, Inc_$Value Entry] VE left outer join [CRONUS USA, Inc_$Item Ledger Entry] IL on VE.[Item Ledger Entry No_] = IL.[Entry No_] left outer join [View_CRONUS USA, Inc_$Sales Document Headers] H on H.[Document No_] = VE.[Document No_] left join [CRONUS USA, Inc_$Customer] C on C.[No_] = H.[Sell-to Customer No_] WHERE IL.[Entry Type] = 1 and VE.[Invoiced Quantity] <> 0 and
IL.[Document No_] not in ((select [Document No_] from [CRONUS USA, Inc_$Item Ledger Entry] group by [Document No_] having SUM(Quantity) = 0))
UNION ALL
select
R.[Entry No_],
H.[Document Type],
H.[Document No_],
R.[Document No_] [Invoice No_],
H.[State],
H.[City],
H.[Sell-to Customer No_],
H.[Customer Posting Group],
'Resource Ledger' Source,
C.[Salesperson Code],
C.[Territory Code],
R.[Posting Date],
'Resource' [Type],
3 [Type No_],
R.[Resource No_] [No_],
-R.[Quantity] Quantity,
-R.[Total Price] [Sales Amount],
-R.[Total Cost] [Cost Amount],
YEAR([Posting Date]) AS [Posting Year],
(CASE WHEN MONTH([Posting Date]) = 1 THEN 'January'
WHEN MONTH([Posting Date]) = 2 THEN 'February'
WHEN MONTH([Posting Date]) = 3 THEN 'March'
WHEN MONTH([Posting Date]) = 4 THEN 'April'
WHEN MONTH([Posting Date]) = 5 THEN 'May'
WHEN MONTH([Posting Date]) = 6 THEN 'June'
WHEN MONTH([Posting Date]) = 7 THEN 'July'
WHEN MONTH([Posting Date]) = 8 THEN 'August'
WHEN MONTH([Posting Date]) = 9 THEN 'September'
WHEN MONTH([Posting Date]) = 10 THEN 'October'
WHEN MONTH([Posting Date]) = 11 THEN 'November'
WHEN MONTH([Posting Date]) = 12 THEN 'December'
END) AS [Posting Month Text],
(CASE
WHEN MONTH([Posting Date]) <= 3 THEN '1'
WHEN MONTH([Posting Date]) > 3 AND MONTH([Posting Date]) <= 6 THEN '2'
WHEN MONTH([Posting Date]) > 6 AND MONTH([Posting Date]) <= 9 THEN '3'
WHEN MONTH([Posting Date]) > 9 AND MONTH([Posting Date]) <= 12 THEN '4' END) AS [Posting Quarter],
MONTH([Posting Date]) AS [Posting Month Number], '', '', '', 0, '',
H.[Sell-to Customer No_], [External Document No_], [Unit of Measure Code], '',
--[Purchasing Code], ''
--[Inventory Posting Group],
C.[Customer Posting Group],
R.[Gen_ Bus_ Posting Group],
R.[Gen_ Prod_ Posting Group],
R.[Global Dimension 1 Code],
R.[Global Dimension 2 Code]
from [CRONUS USA, Inc_$Res_ Ledger Entry] R
left outer join [View_CRONUS USA, Inc_$Sales Document Headers] H on H.[Document No_] = R.[Document No_]
left join [CRONUS USA, Inc_$Customer] C on C.[No_] = H.[Sell-to Customer No_]
WHERE [Entry Type] = 1
UNION ALL select J.[Entry No_], H.[Document Type], H.[Document No_],
J.[Document No_] [Invoice No_], H.[State], H.[City], H.[Sell-to Customer No_],
H.[Customer Posting Group], 'Job Ledger' Source, C.[Salesperson Code],
C.[Territory Code], [Posting Date], 'Item' Type, 1 [Type No_],
J.[No_], -[Quantity] Quantity, -[Total Price] [Sales Amount], [Total Cost] [Cost Amount],
YEAR([Posting Date]) AS [Posting Year], (
CASE
WHEN MONTH([Posting Date]) = 1 THEN 'January'
WHEN MONTH([Posting Date]) = 2 THEN 'February'
WHEN MONTH([Posting Date]) = 3 THEN 'March'
WHEN MONTH([Posting Date]) = 4 THEN 'April'
WHEN MONTH([Posting Date]) = 5 THEN 'May'
WHEN MONTH([Posting Date]) = 6 THEN 'June'
WHEN MONTH([Posting Date]) = 7 THEN 'July'
WHEN MONTH([Posting Date]) = 8 THEN 'August'
WHEN MONTH([Posting Date]) = 9 THEN 'September'
WHEN MONTH([Posting Date]) = 10 THEN 'October'
WHEN MONTH([Posting Date]) = 11 THEN 'November'
WHEN MONTH([Posting Date]) = 12 THEN 'December' END) AS [Posting Month Text],
(CASE
WHEN MONTH([Posting Date]) <= 3 THEN '1'
WHEN MONTH([Posting Date]) > 3 AND MONTH([Posting Date]) <= 6 THEN '2'
WHEN MONTH([Posting Date]) > 6 AND MONTH([Posting Date]) <= 9 THEN '3'
WHEN MONTH([Posting Date]) > 9 AND MONTH([Posting Date]) <= 12 THEN '4' END) AS [Posting Quarter],
MONTH([Posting Date]) AS [Posting Month Number], I.[Product Group Code],
I.[Item Category Code], [Variant Code], 0,
--[Drop Shipment],
J.[Location Code],
C.[No_] Customer, [
External Document No_],
[Unit of Measure Code], '',
--[Purchasing Code],
I.[Inventory Posting Group],
H.[Customer Posting Group],
J.[Gen_ Bus_ Posting Group],
.[Gen_ Prod_ Posting Group],
J.[Global Dimension 1 Code],
J.[Global Dimension 2 Code]
from [CRONUS USA, Inc_$Job Ledger Entry] J
left outer join [View_CRONUS USA, Inc_$Sales Document Headers] H on H.[Document No_] = J.[Document No_]
left outer join [CRONUS USA, Inc_$Item] I on I.[No_] = J.[No_]
left join [CRONUS USA, Inc_$Customer] C on C.[No_] = H.[Sell-to Customer No_] WHERE [Entry Type] = 1