Heterogeneous Connections DATE_ADD vs DATEADD and CURDATE() vs GETDATE()

Toad Data Point & Toad Intelligence Central
1

I have a query that has subqueries to Oracle and one to SQL Server. How in the heck am I supposed to subtract 12 months from the current date? If I use DATEADD and GETDATE then running the subquery alone is fine, but when I run the entire query it doesn't like that and says that the function does not exist and to use an equivalent MySQL function. So then I change it to MySQL functions and it still doesn't work.

The saddest thing of all is that it works fine if I run it in "regular" TDP, but if I create a Workflow and import the query that works, then all of the above stuff starts happening. How can this be...the query worked?

I wouldn't even be in this predicament if the original Toad Automation would work after I upgraded to 5.1. I have a separate thread on that, but there have been no responses.

1 Like
2

Hi,

can you post here some sample of your Heteregenous query which doesn't for you? I will try to reproduce it but I don't have any issue right now.

You should be definitely able to do the same query in regular TDP and in Workbook.

Only limitation is as you mentioned above. You have to use MySQL syntax and functions:
In your case -> DATE_ADD() and CURDATE() (instead of GETDATE)

3

This is the query that works in regular TDP. It was created via the Query Builder. It has several subqueries in the FROM clause. The main query and most of the subqueries connect to Oracle; only one subquery "AVOs" connects to Microsoft SQL Server. This area of the code is in italic below.

If I open a new Workflow and open this same query builder file to create a dataset, then the trouble starts.

SELECT INVENTORY_PREFIX_MASTER.PREFIX AS SKU Prefix
, INVENTORY_PREFIX_MASTER.MANUFACTURER_NAME AS Manufacturer Name
, INVENTORY_PREFIX_MASTER.FLAG_DATA_FEED AS Has Data Feed
, ISSO."IS_SO_Parts" AS IS SO Parts
, NUSKUs.NU_SKUs AS NU SKUs
, AVOs.AVO Count AS AVO Count
, PrefixMargin.Margin
, MAP."MAP"
, INVENTORY_PREFIX_MASTER.STATUS AS SKU Prefix Status
, VENDOR.VENDOR_STATUS AS Vendor Prefix Status
, VENDOR.VENDOR_MERCH_CODE AS Vendor MC
, case when INVENTORY_PREFIX_MASTER.FLAG_DATA_FEED = 'Y' then 1 else 0 end AS Score Data Feed
FROM
(((((((DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.VENDOR VENDOR
ON (INVENTORY_PREFIX_MASTER.VENDOR_PREFIX = VENDOR.VENDOR_PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY_PRFX_HIST_BY_SITE.PREFIX
, SUM(INVENTORY_PRFX_HIST_BY_SITE.SN_SUM_COST_CURR_PRICE_BASIS) AS "RYTD_COGS"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PRFX_HIST_BY_SITE INVENTORY_PRFX_HIST_BY_SITE
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY_PRFX_HIST_BY_SITE.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
( (
Inventory_Prfx_Hist_By_Site.Year_Description = (extract(year from sysdate)-1)
AND
Inventory_Prfx_Hist_By_Site.Hist_Month_Sales >= (extract(MONTH from sysdate))
)
OR
Inventory_Prfx_Hist_By_Site.Year_Description = (extract(year from sysdate))
)
GROUP BY INVENTORY_PRFX_HIST_BY_SITE.PREFIX) COGS
ON (INVENTORY_PREFIX_MASTER.PREFIX = COGS.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY.PREFIX
, MAX(INVENTORY.DATE_MFG_LAST_NOTIFIED) AS "Last_Stock_Check_Feed"
, CASE
WHEN max(Inventory.Date_Mfg_Last_Notified) >= (sysdate - 30)
THEN 'Y'
ELSE 'N'
END AS "Has_Stock_Check_Feed"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
Inventory.Type IN ('IS', 'SO')
GROUP BY INVENTORY.PREFIX
ORDER BY INVENTORY.PREFIX ASC) StockFeed
ON (INVENTORY_PREFIX_MASTER.PREFIX = StockFeed.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY.PREFIX
, COUNT(INVENTORY.PART_NUMBER) AS "IS_SO_Parts"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
Inventory.Type IN ('IS', 'SO')
GROUP BY INVENTORY.PREFIX
ORDER BY INVENTORY.PREFIX ASC) ISSO
ON (INVENTORY_PREFIX_MASTER.PREFIX = ISSO.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY_PREFIX_MASTER.PREFIX
, round((SUM(inventory.price_current_month)-SUM(inventory.cost_current_price_basis))/SUM(inventory.price_current_month), 4) AS Margin
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE Inventory_Prefix_Master.Status LIKE 'A%'
AND Inventory.TYPE IN ('IS', 'SO')
AND Inventory.Price_Current_Month <> 0
AND NOT ( Inventory.Part_Number LIKE '%CUSTOM%'
OR Inventory.Part_Number LIKE '%DEMO%'
OR Inventory.Part_Number LIKE '%DEMX%'
OR Inventory.Part_Number LIKE '%DXE-GC%'
OR Inventory.Part_Number LIKE '%MNTBAL%'
OR Inventory.Part_Number LIKE '%NEWPART%'
OR Inventory.Part_Number LIKE '%SUM-DEMS%'
OR Inventory.Part_Number LIKE '%SUM-GC%')
GROUP BY INVENTORY_PREFIX_MASTER.PREFIX
ORDER BY INVENTORY_PREFIX_MASTER.PREFIX ASC) PrefixMargin
ON (INVENTORY_PREFIX_MASTER.PREFIX = PrefixMargin.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY_PREFIX_MASTER.PREFIX
, COUNT(INVENTORY.PART_NUMBER) AS "Part_Count"
, CASE WHEN count(Inventory.part_number) > 0 THEN 'Y'
ELSE 'N'
END AS "MAP"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
Inventory.Flag_Mkt_Meth_Map IN ('B', 'C', 'F')
GROUP BY INVENTORY_PREFIX_MASTER.PREFIX
ORDER BY INVENTORY_PREFIX_MASTER.PREFIX ASC) MAP
ON (INVENTORY_PREFIX_MASTER.PREFIX = MAP.PREFIX))
LEFT OUTER JOIN
*(SELECT left(Part_Number, 3) + '-' AS Prefix *

  •   , COUNT(AVO.PK_ID) AS `AVO Count`*
  • FROM WEBSQL4, Parts DB.Feedback.dbo.AVO AVO *
  • WHERE (AVO.Category = 'Pricing')*
  •   AND (AVO.QueID = 7)*

  •   AND ( `AVO`.`Posted` >= dateadd(month, -12, curdate())) *
  • GROUP BY left(Part_Number, 3) + '-' *
  • ORDER BY 1 ASC) AVOs*
    ON (INVENTORY_PREFIX_MASTER.PREFIX = AVOs.Prefix))
    LEFT OUTER JOIN
    (SELECT INVENTORY_MANUFACTURER_FACTS.PREFIX
    , COUNT(INVENTORY_MANUFACTURER_FACTS.PART_NUMBER) AS "NU_SKUs"
    FROM DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_MANUFACTURER_FACTS INVENTORY_MANUFACTURER_FACTS
    WHERE
    Inventory_Manufacturer_Facts.Flag_Sku_Exists_In_Inventory = 'N'
    AND
    Inventory_Manufacturer_Facts.Flag_Obsolete = ' '
    AND
    Inventory_Manufacturer_Facts.Code_Emission = ' '
    GROUP BY INVENTORY_MANUFACTURER_FACTS.PREFIX
    ORDER BY INVENTORY_MANUFACTURER_FACTS.PREFIX ASC) NUSKUs
    ON (INVENTORY_PREFIX_MASTER.PREFIX = NUSKUs.PREFIX)
    WHERE (INVENTORY_PREFIX_MASTER.STATUS LIKE 'A%')
4

This is a pretty complicated query but I didn't find any issue with it. Only thing that is not clear to me is how do you import it to WF? Are you using .tsm file which you created in TDP? Or did you copy/paste your query?
When I tried it with my simple heterogeneous query it works. There is no big difference in how regular TDP and WB work when loading your tsm file. You just have to be sure that toad identify your heterogenous query correctly (you can check your bottom right corner when you select QB in WF)
image
Are you able to work with the diagram and you don't see any result or do you see some error during the loading? If it is possible you can send me your ExceptionTrace.log from AppData directory.
Filip

5

That query was created via the query builder in regular Toad and runs fine there. When I try to use the query builder file in a Workflow, I'm getting this message...

Capture
Capture.JPG1482×165 21 KB

I don't see how to get the Workflow to understand that I'm bringing in a hetero connection query.

6

And you created it in same version of Toad? So regular TDP 5.1 and open it in TWB 5.1 right?
if not, do you have both connections for this tsm ready to use?

If it is possible can you send me your ExceptionTrace.log or this tsm file?
Filip

7

The tsm was originally created in an earlier version. After upgrade to 5.1, I was able to open the tsm, update the connections, save the query, and the query works.

Here is the text from the support bundle:

NOTE: Please, review the contents and remove any files that could contain personal information before you send the Support Bundle.

We are eliminating automatic support case creation by email. Before sending your Support Bundle, ensure your subject line includes an opened CaseID:### or a Service Request ID:###. Without the CaseID or Service Request ID your Support Bundle request will be declined.

The quickest way to open a new service request is through our online Support Portal https://support.quest.com/Manage-Service-Request.

You can also contact Support for assistance to create a new case, please visit https://support.quest.com/essentials/contact-technical-support. Post case creation you will continue to have all communication options available including email, phone, or through the Support Portal to manage the lifecycle of your case.

Support Bundle: Toad Data Point 5.1.0.142 Registered Version (32 bit)
11/27/2019 02:08:56 PM

System Information

Operating System
Microsoft Windows 10 Pro 10.0.18362 AMD64
Manufacturer: Microsoft Corporation
Computer Name: MJ04UVQB
Windows Directory: C:\WINDOWS

Computer System
Computer Manufacturer Name: LENOVO
Computer Model: 10HY000GUS
System Type: x64-based PC
Total Physical Memory: 8,276,288 KB
Domain: summit.network
User Name: OPERATIONS\dshull

System Processor
Manufacturer: GenuineIntel
Computer Processor: Intel64 Family 6 Model 94 Stepping 3
CPU Speed: 2.208 GHz
L2 Cache Size: 1 KB

System Bios
BIOS: LENOVO BIOS Rev: FWKT5AA 0.0
BIOS Version: LENOVO - 15a0

System Time Zone
Time Zone: (UTC-05:00) Eastern Time (US & Canada)

Video Controller
Name: Intel(R) HD Graphics 530
Processor: Intel(R) HD Graphics Family
Mode: 3840 x 2160 x 4294967296 colors
Video Ram: 1,048,576 KB
PNP Device ID: PCI\VEN_8086&DEV_1912&SUBSYS_30D217AA&REV_06\3&11583659&1&10
Status: OK

Environment Variables
PROCESSOR_ARCHITEW6432 = AMD64
DriverData = C:\Windows\System32\Drivers\DriverData
COMPUTERNAME = MJ04UVQB
UATDATA = C:\WINDOWS\CCM\UATData\D9F8C395-CAB8-491d-B8AC-179A1FE1BE77
CommonProgramFiles(x86) = C:\Program Files (x86)\Common Files
OneDrive = C:\Users\dshull\OneDrive
HOMEPATH =
PROCESSOR_REVISION = 5e03
PATHEXT = .COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
TNS_Admin = C:\Oracle
TMP = C:\Users\dshull\AppData\Local\Temp
TEMP = C:\Users\dshull\AppData\Local\Temp
LOCALAPPDATA = C:\Users\dshull\AppData\Local
PUBLIC = C:\Users\Public
MSTR_REGISTRY_FILE = C:\PROGRA~2\COMMON~1\MICROS~2\MSIReg.reg
USERDNSDOMAIN = SUMMIT.NETWORK
USERDOMAIN = OPERATIONS
ProgramFiles(x86) = C:\Program Files (x86)
Path = C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\app\dshull\product\12.2.0\client_1;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0;C:\WINDOWS\System32\OpenSSH;C:\Program Files (x86)\Mitel\Connect;C:\Program Files (x86)\Common Files\MicroStrategy;C:\Program Files (x86)\MicroStrategy\Developer;C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_77\Win32\bin;C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_77\Win64\bin;C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_77\Win64\bin\server;C:\Program Files (x86)\Common Files\MicroStrategy\Help\English;C:\Program Files (x86)\Attachmate\Reflection;C:\Users\dshull\AppData\Local\Microsoft\WindowsApps;
PROCESSOR_LEVEL = 6
PROCESSOR_IDENTIFIER = Intel64 Family 6 Model 94 Stepping 3, GenuineIntel
ORACLE_HOME = c:\app\dshull\product\12.2.0\client_1
PSModulePath = C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules
NumberFormattingCustomization = C:\PROGRA~2\COMMON~1\MICROS~2\NUMBER~1.XML
NUMBER_OF_PROCESSORS = 4
FPS_BROWSER_USER_PROFILE_STRING = Default
CommonProgramFiles = C:\Program Files (x86)\Common Files
HOMESHARE = \sredata\home\DShull
ProgramData = C:\ProgramData
ProgramW6432 = C:\Program Files
ProgramFiles = C:\Program Files (x86)
SystemRoot = C:\WINDOWS
SESSIONNAME = Console
CommonProgramW6432 = C:\Program Files\Common Files
SYBASE = C:\Program Files (x86)\Quest Software\Toad Data Point 5.1
LOGONSERVER = \DC1-OH
USERPROFILE = C:\Users\dshull
MSTR_CLASSPATH = C:\Program Files (x86)\Common Files\MicroStrategy
USERDOMAIN_ROAMINGPROFILE = OPERATIONS
APPDATA = C:\Users\dshull\AppData\Roaming
HOMEDRIVE = Z:
USERNAME = dshull
FPS_BROWSER_APP_PROFILE_STRING = Internet Explorer
PROCESSOR_ARCHITECTURE = x86
OS = Windows_NT
ComSpec = C:\WINDOWS\system32\cmd.exe
SystemDrive = C:
windir = C:\WINDOWS
ALLUSERSPROFILE = C:\ProgramData

Connection Information

Current Connection:
DWH_LIVE_SREDWH_OC (DSHULL_HYP), DW_ORDER
Oracle database
Server version 12.2.0.1 (NONDBA)
Connected since 11/27/2019 02:07:57 PM
0 of 2 connections in pool active
Server version:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database NLS settings:
NLS_Language =WE8MSWIN1252
NLS_Territory =AMERICA
NLS_CharacterSet =AMERICAN

Instance NLS settings:
NLS_Language =AMERICA
NLS_Territory =AMERICAN

OS current culture info:
English (United States)
Connection mode:
OCI connect.
Client version: 12.2.0.0
Oracle Homes:
OraClient12Home1_32bit Path=C:\app\dshull\product\12.2.0\client_1 NLS_LANG=

Oracle Default Home:
Default Home: OraClient12Home1_32bit
Path = C:\app\dshull\product\12.2.0\client_1
NLS_LANG =

Registry Structure:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
inst_loc= C:\Program Files (x86)\Oracle\Inventory
NLS_LANG= AMERICAN_AMERICA.WE8MSWIN1252
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\JavaDeploy
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient12Home1_32bit
ORACLE_HOME= C:\app\dshull\product\12.2.0\client_1
ORACLE_HOME_NAME= OraClient12Home1_32bit
ORACLE_GROUP_NAME= Oracle - OraClient12Home1_32bit

Loaded Assemblies

Accessibility, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Accessibility\v4.0_4.0.0.0__b03f5f7f11d50a3a\Accessibility.dll
ActiproSoftware.Docking.WinForms, Version=18.1.341.0, Culture=neutral, PublicKeyToken=c27e062d3c1a4763 C:\WINDOWS\assembly\GAC_MSIL\ActiproSoftware.Docking.WinForms\18.1.341.0__c27e062d3c1a4763\ActiproSoftware.Docking.WinForms.dll
ActiproSoftware.Shared.WinForms, Version=18.1.341.0, Culture=neutral, PublicKeyToken=c27e062d3c1a4763 C:\WINDOWS\assembly\GAC_MSIL\ActiproSoftware.Shared.WinForms\18.1.341.0__c27e062d3c1a4763\ActiproSoftware.Shared.WinForms.dll
ActiproSoftware.SyntaxEditor.WinForms, Version=18.1.341.0, Culture=neutral, PublicKeyToken=c27e062d3c1a4763 C:\WINDOWS\assembly\GAC_MSIL\ActiproSoftware.SyntaxEditor.WinForms\18.1.341.0__c27e062d3c1a4763\ActiproSoftware.SyntaxEditor.WinForms.dll
Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null Location is not supported for given Assembly
BouncyCastle.Crypto, Version=1.7.4114.6375, Culture=neutral, PublicKeyToken=0e99375e54769942 C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\BouncyCastle.Crypto.dll
Devart.Data, Version=5.0.2158.0, Culture=neutral, PublicKeyToken=09af7300eec23701 C:\WINDOWS\assembly\GAC_MSIL\Devart.Data\5.0.2158.0__09af7300eec23701\Devart.Data.dll
Devart.Data.MySql, Version=8.13.1366.0, Culture=neutral, PublicKeyToken=09af7300eec23701 C:\WINDOWS\assembly\GAC_MSIL\Devart.Data.MySql\8.13.1366.0__09af7300eec23701\Devart.Data.MySql.dll
Devart.Data.Oracle, Version=9.7.734.0, Culture=neutral, PublicKeyToken=09af7300eec23701 C:\WINDOWS\assembly\GAC_MSIL\Devart.Data.Oracle\9.7.734.0__09af7300eec23701\Devart.Data.Oracle.dll
DevExpress.BonusSkins.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.BonusSkins.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.BonusSkins.v18.2.dll
DevExpress.Data.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.Data.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.Data.v18.2.dll
DevExpress.Mvvm.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.Mvvm.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.Mvvm.v18.2.dll
DevExpress.PivotGrid.v18.2.Core, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.PivotGrid.v18.2.Core\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.PivotGrid.v18.2.Core.dll
DevExpress.RichEdit.v18.2.Core, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.RichEdit.v18.2.Core\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.RichEdit.v18.2.Core.dll
DevExpress.Utils.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.Utils.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.Utils.v18.2.dll
DevExpress.XtraBars.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraBars.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraBars.v18.2.dll
DevExpress.XtraEditors.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraEditors.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraEditors.v18.2.dll
DevExpress.XtraGrid.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraGrid.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraGrid.v18.2.dll
DevExpress.XtraLayout.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraLayout.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraLayout.v18.2.dll
DevExpress.XtraPrinting.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraPrinting.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraPrinting.v18.2.dll
DevExpress.XtraReports.v18.2.Extensions, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraReports.v18.2.Extensions\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraReports.v18.2.Extensions.dll
DevExpress.XtraTreeList.v18.2, Version=18.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\DevExpress.XtraTreeList.v18.2\v4.0_18.2.8.0__b88d1754d700e49a\DevExpress.XtraTreeList.v18.2.dll
FastData, Version=6.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\FastData.dll
MailBee.NET, Version=11.2.0.590, Culture=neutral, PublicKeyToken=cd85b70fb26f9fc1 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\MailBee.NET\v4.0_11.2.0.590__cd85b70fb26f9fc1\MailBee.NET.dll
ManagedQP5, Version=5.2.5.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\ManagedQP5.dll
Microsoft.GeneratedCode, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null Location is not supported for given Assembly
mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.dll
MySql.Data, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\DataHub\guardian\MySql.Data.dll
OciCall.dll, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null Location is not supported for given Assembly
PresentationCore, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_32\PresentationCore\v4.0_4.0.0.0__31bf3856ad364e35\PresentationCore.dll
PresentationFramework, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework\v4.0_4.0.0.0__31bf3856ad364e35\PresentationFramework.dll
PresentationFramework.Aero2, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework.Aero2\v4.0_4.0.0.0__31bf3856ad364e35\PresentationFramework.Aero2.dll
PresentationFramework-SystemData, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework-SystemData\v4.0_4.0.0.0__b77a5c561934e089\PresentationFramework-SystemData.dll
PresentationFramework-SystemXml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework-SystemXml\v4.0_4.0.0.0__b77a5c561934e089\PresentationFramework-SystemXml.dll
PresentationFramework-SystemXmlLinq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework-SystemXmlLinq\v4.0_4.0.0.0__b77a5c561934e089\PresentationFramework-SystemXmlLinq.dll
Quest.FastETL, Version=7.2.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.FastETL.dll
Quest.NamedPipeRPC, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.NamedPipeRPC.dll
Quest.PersistentCache, Version=3.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.PersistentCache.dll
Quest.Reactive, Version=2.3.1.45, Culture=neutral, PublicKeyToken=adaba824f513af5f C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.Reactive.dll
Quest.SecTech, Version=4.5.0.486, Culture=neutral, PublicKeyToken=99d4728064788932 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Quest.SecTech\v4.0_4.5.0.486__99d4728064788932\Quest.SecTech.dll
Quest.ToadCommunication, Version=7.0.10.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.ToadCommunication.dll
Quest.ToadGeneral, Version=7.0.10.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.ToadGeneral.dll
Quest.VersionControl, Version=7.8.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Quest.VersionControl.dll
Quest.WorkflowEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Workflow\Quest.WorkflowEngine.dll
Quest.WorkflowImplementation, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Workflow\Quest.WorkflowImplementation.dll
Quest.WorkflowInterface, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Workflow\Quest.WorkflowInterface.dll
Quest.WorkflowUI, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Workflow\Quest.WorkflowUI.dll
QuestElevation, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\QuestElevation.exe
Sap.Data.Hana.v4.5, Version=2.2.36.0, Culture=neutral, PublicKeyToken=0326b8ea63db4bc4 C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\SAP\Sap.Data.Hana.v4.5.dll
Sybase.AdoNet4.AseClient, Version=16.0.3.6, Culture=neutral, PublicKeyToken=95d94fac46c88e1e C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\SAP\Sybase.AdoNet4.AseClient.dll
System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll
System.ComponentModel.DataAnnotations, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.ComponentModel.DataAnnotations\v4.0_4.0.0.0__31bf3856ad364e35\System.ComponentModel.DataAnnotations.dll
System.Configuration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll
System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll
System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Data.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Data.Linq.dll
System.DirectoryServices.AccountManagement, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.DirectoryServices.AccountManagement\v4.0_4.0.0.0__b77a5c561934e089\System.DirectoryServices.AccountManagement.dll
System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll
System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll
System.Management, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Management\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Management.dll
System.Numerics, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Numerics\v4.0_4.0.0.0__b77a5c561934e089\System.Numerics.dll
System.Runtime.Serialization.Formatters.Soap, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Runtime.Serialization.Formatters.Soap\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Runtime.Serialization.Formatters.Soap.dll
System.ServiceProcess, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.ServiceProcess\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.ServiceProcess.dll
System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll
System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Web\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Web.dll
System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Web.Extensions\v4.0_4.0.0.0__31bf3856ad364e35\System.Web.Extensions.dll
System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll
System.Workflow.ComponentModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Workflow.ComponentModel\v4.0_4.0.0.0__31bf3856ad364e35\System.Workflow.ComponentModel.dll
System.Xaml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Xaml\v4.0_4.0.0.0__b77a5c561934e089\System.Xaml.dll
System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll
System.Xml.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll
Toad, Version=8.5.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\toad.exe
ToadAutomation, Version=5.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Automation\ToadAutomation.dll
ToadCommon, Version=6.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Core\ToadCommon.dll
ToadConnectionProxy, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\ToadConnectionProxy.dll
ToadCore, Version=8.6.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\ToadCore.dll
ToadDB2Provider, Version=7.1.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\DB2\ToadDB2Provider.dll
ToadLocalDB, Version=5.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\LocalDB\ToadLocalDB.dll
ToadLocalDBProvider, Version=5.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\LocalDB\ToadLocalDBProvider.dll
ToadMySQL, Version=8.6.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\MySQL\ToadMySQL.dll
ToadMySQLProvider, Version=8.6.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\MySQL\ToadMySQLProvider.dll
ToadOdbcProvider, Version=5.1.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Odbc\ToadOdbcProvider.dll
ToadOracle, Version=6.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Oracle\ToadOracle.dll
ToadOracleProvider, Version=6.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Oracle\ToadOracleProvider.dll
ToadOracleProxy, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\ToadOracleProxy.dll
ToadPlugin, Version=6.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\ToadPlugin.dll
ToadPostgreSQLProvider, Version=5.0.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Postgres\ToadPostgreSQLProvider.dll
ToadSAPHanaProvider, Version=4.1.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\SAP\ToadSAPHanaProvider.dll
ToadSAPProvider, Version=4.1.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\SAP\ToadSAPProvider.dll
ToadSqlServerProvider, Version=7.1.0.142, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\SqlServer\ToadSqlServerProvider.dll
ToadTeradataProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ef645eb5653fd56a C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\Plugins\Teradata\ToadTeradataProvider.dll
UIAutomationClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\UIAutomationClient\v4.0_4.0.0.0__31bf3856ad364e35\UIAutomationClient.dll
UIAutomationProvider, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\UIAutomationProvider\v4.0_4.0.0.0__31bf3856ad364e35\UIAutomationProvider.dll
UIAutomationTypes, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\UIAutomationTypes\v4.0_4.0.0.0__31bf3856ad364e35\UIAutomationTypes.dll
WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\WindowsBase\v4.0_4.0.0.0__31bf3856ad364e35\WindowsBase.dll
WindowsFormsIntegration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\WindowsFormsIntegration\v4.0_4.0.0.0__31bf3856ad364e35\WindowsFormsIntegration.dll
QP5, Version=5.358.19260.25584, C:\Program Files (x86)\Quest Software\Toad Data Point 5.1\qp5.dll

Licensing

8

Here is the query sql:

SELECT INVENTORY_PREFIX_MASTER.PREFIX AS SKU Prefix
, INVENTORY_PREFIX_MASTER.MANUFACTURER_NAME AS Manufacturer Name
, INVENTORY_PREFIX_MASTER.FLAG_DATA_FEED AS Has Data Feed
, ISSO."IS_SO_Parts" AS IS SO Parts
, NUSKUs.NU_SKUs AS NU SKUs
, AVOs.AVO Count AS AVO Count
, PrefixMargin.Margin
, MAP."MAP"
, INVENTORY_PREFIX_MASTER.STATUS AS SKU Prefix Status
, VENDOR.VENDOR_STATUS AS Vendor Prefix Status
, VENDOR.VENDOR_MERCH_CODE AS Vendor MC
, case when INVENTORY_PREFIX_MASTER.FLAG_DATA_FEED = 'Y' then 1 else 0 end AS Score Data Feed
FROM
(((((((DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.VENDOR VENDOR
ON (INVENTORY_PREFIX_MASTER.VENDOR_PREFIX = VENDOR.VENDOR_PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY_PRFX_HIST_BY_SITE.PREFIX
, SUM(INVENTORY_PRFX_HIST_BY_SITE.SN_SUM_COST_CURR_PRICE_BASIS) AS "RYTD_COGS"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PRFX_HIST_BY_SITE INVENTORY_PRFX_HIST_BY_SITE
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY_PRFX_HIST_BY_SITE.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
( (
Inventory_Prfx_Hist_By_Site.Year_Description = (extract(year from sysdate)-1)
AND
Inventory_Prfx_Hist_By_Site.Hist_Month_Sales >= (extract(MONTH from sysdate))
)
OR
Inventory_Prfx_Hist_By_Site.Year_Description = (extract(year from sysdate))
)
GROUP BY INVENTORY_PRFX_HIST_BY_SITE.PREFIX) COGS
ON (INVENTORY_PREFIX_MASTER.PREFIX = COGS.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY.PREFIX
, MAX(INVENTORY.DATE_MFG_LAST_NOTIFIED) AS "Last_Stock_Check_Feed"
, CASE
WHEN max(Inventory.Date_Mfg_Last_Notified) >= (sysdate - 30)
THEN 'Y'
ELSE 'N'
END AS "Has_Stock_Check_Feed"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
Inventory.Type IN ('IS', 'SO')
GROUP BY INVENTORY.PREFIX
ORDER BY INVENTORY.PREFIX ASC) StockFeed
ON (INVENTORY_PREFIX_MASTER.PREFIX = StockFeed.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY.PREFIX
, COUNT(INVENTORY.PART_NUMBER) AS "IS_SO_Parts"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
Inventory.Type IN ('IS', 'SO')
GROUP BY INVENTORY.PREFIX
ORDER BY INVENTORY.PREFIX ASC) ISSO
ON (INVENTORY_PREFIX_MASTER.PREFIX = ISSO.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY_PREFIX_MASTER.PREFIX
, round((SUM(inventory.price_current_month)-SUM(inventory.cost_current_price_basis))/SUM(inventory.price_current_month), 4) AS Margin
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE Inventory_Prefix_Master.Status LIKE 'A%'
AND Inventory.TYPE IN ('IS', 'SO')
AND Inventory.Price_Current_Month <> 0
AND NOT ( Inventory.Part_Number LIKE '%CUSTOM%'
OR Inventory.Part_Number LIKE '%DEMO%'
OR Inventory.Part_Number LIKE '%DEMX%'
OR Inventory.Part_Number LIKE '%DXE-GC%'
OR Inventory.Part_Number LIKE '%MNTBAL%'
OR Inventory.Part_Number LIKE '%NEWPART%'
OR Inventory.Part_Number LIKE '%SUM-DEMS%'
OR Inventory.Part_Number LIKE '%SUM-GC%')
GROUP BY INVENTORY_PREFIX_MASTER.PREFIX
ORDER BY INVENTORY_PREFIX_MASTER.PREFIX ASC) PrefixMargin
ON (INVENTORY_PREFIX_MASTER.PREFIX = PrefixMargin.PREFIX))
LEFT OUTER JOIN
(SELECT INVENTORY_PREFIX_MASTER.PREFIX
, COUNT(INVENTORY.PART_NUMBER) AS "Part_Count"
, CASE WHEN count(Inventory.part_number) > 0 THEN 'Y'
ELSE 'N'
END AS "MAP"
FROM
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_PREFIX_MASTER INVENTORY_PREFIX_MASTER
INNER JOIN
DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY
ON (INVENTORY_PREFIX_MASTER.PREFIX = INVENTORY.PREFIX)
WHERE
Inventory_Prefix_Master.Status LIKE 'A%'
AND
Inventory.Flag_Mkt_Meth_Map IN ('B', 'C', 'F')
GROUP BY INVENTORY_PREFIX_MASTER.PREFIX
ORDER BY INVENTORY_PREFIX_MASTER.PREFIX ASC) MAP
ON (INVENTORY_PREFIX_MASTER.PREFIX = MAP.PREFIX))
LEFT OUTER JOIN
(SELECT left(Part_Number, 3) + '-' AS Prefix
, COUNT(AVO.PK_ID) AS AVO Count
FROM WEBSQL4, Parts DB.Feedback.dbo.AVO AVO
WHERE (AVO.Category = 'Pricing')
AND (AVO.QueID = 7)
AND ( AVO.Posted >= dateadd(month, -12, curdate()))
GROUP BY left(Part_Number, 3) + '-'
ORDER BY 1 ASC) AVOs
ON (INVENTORY_PREFIX_MASTER.PREFIX = AVOs.Prefix))
LEFT OUTER JOIN
(SELECT INVENTORY_MANUFACTURER_FACTS.PREFIX
, COUNT(INVENTORY_MANUFACTURER_FACTS.PART_NUMBER) AS "NU_SKUs"
FROM DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY_MANUFACTURER_FACTS INVENTORY_MANUFACTURER_FACTS
WHERE
Inventory_Manufacturer_Facts.Flag_Sku_Exists_In_Inventory = 'N'
AND
Inventory_Manufacturer_Facts.Flag_Obsolete = ' '
AND
Inventory_Manufacturer_Facts.Code_Emission = ' '
GROUP BY INVENTORY_MANUFACTURER_FACTS.PREFIX
ORDER BY INVENTORY_MANUFACTURER_FACTS.PREFIX ASC) NUSKUs
ON (INVENTORY_PREFIX_MASTER.PREFIX = NUSKUs.PREFIX)
WHERE (INVENTORY_PREFIX_MASTER.STATUS LIKE 'A%')