Reports

Import Repricer in Navicat

Get the Broken Starter files from this JIRA ticket

• download
• cut/paste into a .txt doc

Use Navicat Import Wizard

• select .txt file
• import from _location of your choosing
• follow screen grabs below

Select delimited

• tab
• “

• Field Name Row: 1
• First Data Row: 2
• Delimiter: /

Target Table: tbl_catalog_tmp

Target Field: catalog_id

Delete all records in destination

Run query & export results to .xls

1. WITH VIEW_INVENTORY_COUNT AS (
2.     SELECT
3.         TBL_CATALOG.CATALOG_ID,
4.         COUNT(TBL_INVENTORY.INVENTORY_ID) AS INVENTORY_ID_COUNT
5.     FROM
6.         TBL_CATALOG_TEMP INNER JOIN TBL_CATALOG ON TBL_CATALOG_TEMP.CATALOG_ID = TBL_CATALOG.CATALOG_ID
7.         INNER JOIN TBL_PRODUCT ON TBL_CATALOG.PRODUCT_ID = TBL_PRODUCT.PRODUCT_ID
8.         INNER JOIN TBL_INVENTORY ON TBL_INVENTORY.PRODUCT_ID = TBL_PRODUCT.PRODUCT_ID
9.         INNER JOIN TBL_INVENTORY_STATUS_LOG ON TBL_INVENTORY_STATUS_LOG.INVENTORY_ID = TBL_INVENTORY.INVENTORY_ID
10.         INNER JOIN TBL_PRODUCT_LOCATION ON TBL_PRODUCT_LOCATION.PRODUCT_LOCATION_ID = TBL_INVENTORY.PRODUCT_LOCATION_ID
11.     WHERE
12.         TBL_INVENTORY_STATUS_LOG.INVENTORY_STATUS_CODE = ‘AVAILABLE’
13.         AND TBL_INVENTORY_STATUS_LOG.INVENTORY_STATUS_LOG_EXPDATE = ‘9999-12-31’
14.         AND TBL_PRODUCT_LOCATION.PRODUCT_WAREHOUSE_ID = 12
15.     GROUP BY
16.         TBL_CATALOG.CATALOG_ID
17. )
18. SELECT
19.     TBL_CATALOG.CATALOG_ID,
20.     TBL_CATALOG.CATALOG_QTY,
21.     TBL_PRODUCT.PRODUCT_ID,
22.     TBL_PRODUCT.PRODUCT_UNIT_COST,
23.     COALESCE(VIEW_INVENTORY_COUNT.INVENTORY_ID_COUNT,0) AS INVENTORY_ID_COUNT
24. FROM
25.     TBL_CATALOG_TEMP INNER JOIN TBL_CATALOG ON TBL_CATALOG_TEMP.CATALOG_ID = TBL_CATALOG.CATALOG_ID
26.     INNER JOIN TBL_PRODUCT ON TBL_CATALOG.PRODUCT_ID = TBL_PRODUCT.PRODUCT_ID
27.     LEFT OUTER JOIN VIEW_INVENTORY_COUNT ON TBL_CATALOG_TEMP.CATALOG_ID = VIEW_INVENTORY_COUNT.CATALOG_ID
28. ORDER BY
29.     TBL_CATALOG.CATALOG_ID