{"count":46,"path":"/opt/ppc-bot/learned_facts.json","prompt":"LEARNED FACTS (auto-discovered from past failed queries — treat as high-confidence):\n  1. [table_schema] PPC's vendor purchase orders are stored in `cpohdr` (Consumable Purchase Order Header) table, not `pohdr` (which is for customer purchase orders). Join `cpohdr` to `suppmas` on compcode+supcode to get supplier names.\n     e.g.: \nSELECT \n    s.supcode,\n    s.suppname,\n    COUNT(DISTINCT c.pono) as total_orders,\n    SUM(c.povalue) as total_purchase_value\nFROM cpohdr c\nINNER JOIN suppmas s ON c.compcode = s.compcode AND c.supcode = s.supcode\nWHERE c.compcode = 'DAS' \n    AND YEAR(c.podate) = 2025\nGROUP BY s.supcode, s.suppname\nORDER BY total_purchase_value DESC\n\n  2. [column_names] Vendor/supplier identifier is `supcode` (not vendcode) in purchase order tables. Supplier master is `suppmas` with columns: supcode (PK), suppname, compcode (PK).\n     e.g.: \nSELECT supcode, suppname FROM suppmas WHERE compcode='DAS' AND lock='N' LIMIT 10\n\n  3. [table_schema] PPC PO tables: `cpohdr` (consumables/general items, type='CS'), `pohdr` (customer purchase orders), `suppohdr` (different supplier PO context). For vendor purchase value reporting, use `cpohdr` with column `povalue` for total PO amount.\n     e.g.: \nSELECT podate, pono, supcode, povalue FROM cpohdr WHERE compcode='DAS' AND povalue > 0\n\n  4. [column_naming] Dispatch Advice header table uses `advicedate` (not despdate) for the document date, and the detail table uses `adviceqty` (not despqty) for quantities dispatched.\n     e.g.: SELECT h.adviceno, h.advicedate, d.adviceqty FROM despadvicehdr h INNER JOIN despadvicedtl d ON h.compcode=d.compcode AND h.adviceno=d.adviceno WHERE h.compcode='DAS' AND YEAR(h.advicedate)=2022\n  5. [table_structure] Dispatch advice data is stored across two tables: despadvicehdr (header with date/customer) and despadvicedtl (detail with items/quantities), joined on compcode and adviceno.\n     e.g.: SELECT h.adviceno, h.advicedate, c.custname, d.itemcode, im.itemname, d.adviceqty FROM despadvicehdr h INNER JOIN despadvicedtl d ON h.compcode=d.compcode AND h.adviceno=d.adviceno INNER JOIN cust c ON h.custcode=c.custcode AND c.compcode=h.compcode INNER JOIN itemmas im ON d.itemcode=im.itemcode AND d.compcode=im.compcode WHERE h.compcode='DAS'\n  6. [table_schema] The schedule date range for OAs is stored in oahdr.schdatefrom and oahdr.schdateto columns (not in a separate schedule table like oasch for this context), and invhdr.oano links invoices to the original order advice.\n     e.g.: SELECT oh.oano, oh.schdatefrom, oh.schdateto, ih.invdate FROM oahdr oh LEFT JOIN invhdr ih ON oh.compcode = ih.compcode AND oh.oano = ih.oano WHERE oh.compcode='DAS' AND oh.custcode='SKSSL(A)'\n  7. [column_values] When user says \"KOYO\", check BOTH custcode='KOYO' (JTEKT BEARINGS INDIA PVT LTD — active, has recent invoices) AND custname LIKE '%KOYO%' (Sona Koyo Steering entities — inactive since 2018). Always search by custcode first: SELECT custcode, custname FROM cust WHERE compcode='DAS' AND (custcode='KOYO' OR custname LIKE '%KOYO%') AND lock='N'.\n     e.g.: SELECT custcode, custname FROM cust WHERE compcode='DAS' AND (custcode='KOYO' OR custname LIKE '%KOYO%') AND lock='N'\n  8. [join_key] OAs are linked to invoices via invhdr.oano = oahdr.oano (and compcode), allowing schedule vs. dispatch tracking by comparing oahdr.schdateto with MAX(invhdr.invdate) grouped by oano.\n     e.g.: SELECT oh.oano, oh.schdateto, MAX(ih.invdate) as last_invdate, DATEDIFF(DAY, oh.schdateto, MAX(ih.invdate)) as days_late FROM oahdr oh LEFT JOIN invhdr ih ON oh.compcode=ih.compcode AND oh.oano=ih.oano WHERE oh.compcode='DAS' GROUP BY oh.oano, oh.schdateto\n  9. [column_values] The invoice amount column in invhdr is `inv_amt` (numeric, with underscore), not `invamt`. All financial queries referencing total invoice value must use inv_amt.\n     e.g.: SELECT invno, inv_amt FROM invhdr WHERE compcode='DAS' AND inv_amt > 100000 ORDER BY inv_amt DESC\n  10. [schedule_adherence] Schedule adherence analysis requires LEFT JOINing oahdr→oadtl→invhdr→invdtl, comparing oadtl.qty (ordered) vs SUM(invdtl.itemqty) (dispatched), and filtering invhdr.invtype=0 (sales only) with cancel_flag='N'.\n     e.g.: SELECT oh.oano, SUM(od.qty) AS ordered_qty, SUM(ISNULL(id.itemqty,0)) AS dispatched_qty, CASE WHEN SUM(od.qty)>0 THEN ROUND(100.0*SUM(ISNULL(id.itemqty,0))/SUM(od.qty),2) ELSE 0 END AS adherence_pct FROM oahdr oh INNER JOIN oadtl od ON oh.compcode=od.compcode AND oh.oano=od.oano LEFT JOIN invhdr ih ON oh.compcode=ih.compcode AND oh.oano=ih.oano AND ih.invtype=0 AND ih.cancel_flag='N' LEFT JOIN invdtl id ON ih.compcode=id.compcode AND ih.invno=id.invno AND ih.invyear=id.invyear AND od.itemcode=id.itemcode WHERE oh.compcode='DAS' GROUP BY oh.oano\n  11. [business_rule] Sona Koyo Steering entities (SKSSL(A), SKSSL-G, SONA-2, SONA39, SONAC, SONAS) have no OAs/invoices after 2018. But custcode='KOYO' (JTEKT Bearings India) IS active with recent invoices — do NOT confuse them.\n     e.g.: SELECT custcode, custname FROM cust WHERE compcode='DAS' AND custname LIKE '%KOYO%' AND lock='N'\n  12. [column_values] CS Plate product is itemcode='CSPLATE' with itemname='C S PLATE (3341003000)' in itemmas table. Use this code to filter dispatch queries for CS Plate.\n     e.g.: SELECT itemcode, itemname FROM itemmas WHERE compcode='DAS' AND itemcode='CSPLATE'\n  13. [join_key] Dispatch item details live in despadvicedtl.itemcode, joined to despadvicehdr on (compcode, adviceno). Always join despadvicehdr → despadvicedtl before filtering by itemcode.\n     e.g.: SELECT dah.advicedate, dad.itemcode, dad.adviceqty FROM despadvicehdr dah INNER JOIN despadvicedtl dad ON dah.compcode=dad.compcode AND dah.adviceno=dad.adviceno WHERE dah.compcode='DAS' AND dad.itemcode='CSPLATE'\n  14. [query_pattern] To filter dispatch advices for a specific date range, use DATEADD(DAY, -N, (SELECT MAX(advicedate) FROM despadvicehdr WHERE compcode='DAS')) to get last N days from latest system date.\n     e.g.: SELECT * FROM despadvicehdr WHERE compcode='DAS' AND advicedate >= DATEADD(DAY, -7, (SELECT MAX(advicedate) FROM despadvicehdr WHERE compcode='DAS'))\n  15. [customer_identification] JTEKT Bearing is custcode 'KOYO' (JTEKT BEARINGS INDIA PVT LTD). Other JTEKT entities use different codes (JTEKT(A), JTEKT(C), JTEKT(D-19), JTEKT(D32), JTEKT(G), JTEKTREWAR) with their own invoice streams.\n     e.g.: SELECT custcode, custname FROM cust WHERE compcode='DAS' AND custname LIKE '%JTEKT%' ORDER BY custname\n  16. [table_lifecycle] despadvicehdr (dispatch advices) for KOYO last updated July 2022. For dispatch data after 2022, use invhdr + invdtl (sales invoices) instead. Dispatch advices are stale/inactive for this customer.\n     e.g.: SELECT MAX(advicedate) FROM despadvicehdr WHERE compcode='DAS' AND custcode='KOYO'\n  17. [column_naming] invhdr uses column 'inv_amt' (NOT 'invamt'). invdtl has NO 'invtype' column — invtype is in invhdr only. Join invhdr to invdtl on (compcode, invno, invyear) WITHOUT invtype.\n     e.g.: SELECT h.invno, h.inv_amt FROM invhdr h INNER JOIN invdtl d ON h.compcode=d.compcode AND h.invno=d.invno AND h.invyear=d.invyear WHERE h.compcode='DAS'\n  18. [column_values] despadvicedtl.custcode is the correct location for customer filter in dispatch advices; despadvicehdr.custcode is NULL for all records since mid-2022. Always join despadvicedtl and filter by d.custcode for dispatch queries.\n     e.g.: SELECT dah.adviceno, dd.custcode, dd.adviceqty FROM despadvicehdr dah INNER JOIN despadvicedtl dd ON dah.compcode=dd.compcode AND dah.adviceno=dd.adviceno WHERE dah.compcode='DAS' AND dd.custcode='SONA-2'\n  19. [column_values] despadvicedtl quantity column is 'adviceqty', NOT 'itemvalue'. invdtl quantity is 'itemqty' and amount is 'itemamt', NOT 'itemvalue'.\n     e.g.: SELECT adviceqty FROM despadvicedtl WHERE compcode='DAS'; SELECT itemqty, itemamt FROM invdtl WHERE compcode='DAS'\n  20. [business_rule] For KOYO (customer entities: SKSSL(A), SONA-2, SONAC, SONA39, SONAS), last invoice activity was May 2018. They have no dispatch advices or sales invoices in the last year; for recent data, verify with customer service whether they remain active.\n     e.g.: SELECT MAX(invdate) FROM invhdr WHERE compcode='DAS' AND custcode IN ('SKSSL(A)','SONA-2','SONAC','SONA39','SONAS')\n  21. [sql_compatibility] SQL Server 2008 R2 does NOT support FORMAT() function (added in SQL Server 2012). Use CAST(column AS DATE) or CONVERT() instead for date formatting.\n     e.g.: -- SQL Server 2008 R2 compatible:\nDECLARE @OneMonthAgo DATETIME = DATEADD(MONTH, -1, CAST(GETDATE() AS DATE))\nSELECT CAST(h.advicedate AS DATE) AS advice_date FROM despadvicehdr h WHERE h.advicedate >= @OneMonthAgo\n  22. [join_key] despadvicehdr.custcode is NULL for most recent records (post mid-2022). Always use despadvicedtl.custcode for filtering by customer in dispatch queries.\n     e.g.: SELECT h.adviceno, h.advicedate, d.custcode FROM despadvicehdr h INNER JOIN despadvicedtl d ON h.compcode=d.compcode AND h.adviceno=d.adviceno WHERE h.compcode='DAS' AND d.custcode='KOYO'\n  23. [column_values] partno in saleitemmas is stored with prefix 'Item Code-' (e.g., 'Item Code-1000294'). When searching by partno, use LIKE '%1000294%' or prepend 'Item Code-' to exact searches.\n     e.g.: SELECT itemcode, salesname, partno FROM saleitemmas WHERE compcode='DAS' AND partno LIKE '%1000294%'\n  24. [column_mapping] saleitemmas.salesname is the product commercial name (replaces itemname). Do NOT attempt to join itemmas for item name when working with saleitemmas.\n     e.g.: SELECT itemcode, salesname, partno, sale_rate FROM saleitemmas WHERE compcode='DAS' AND partno LIKE '%1000294%'\n  25. [table_structure] pohdr tracks CUSTOMER purchase orders (not supplier orders). Use custcode to join to cust table, not a supplier master.\n     e.g.: SELECT p.PONO, p.POdate, c.custname FROM pohdr p INNER JOIN cust c ON p.compcode=c.compcode AND p.custcode=c.custcode WHERE p.compcode='DAS'\n  26. [join_key] invhdr has a composite primary key of (compcode, invtype, invno, invyear). When joining with invdtl or grouping, all four columns must be included in GROUP BY when selecting non-aggregated columns.\n     e.g.: SELECT h.invno, h.invdate, SUM(d.itemamt) AS total_amt FROM invhdr h INNER JOIN invdtl d ON h.compcode=d.compcode AND h.invno=d.invno AND h.invyear=d.invyear WHERE h.compcode='DAS' GROUP BY h.compcode, h.invtype, h.invno, h.invyear, h.invdate\n  27. [column_values] EXEDY INDIA LIMITED has custcode='EIL' in the customer master (cust table).\n     e.g.: SELECT custcode, custname FROM cust WHERE compcode='DAS' AND custname LIKE '%EXEDY%'\n  28. [column_values] itemledger table has column `trndate` (not `trandate`). Use this for transaction dates when tracking item movement history.\n     e.g.: SELECT itemcode, trndate, production, sale FROM itemledger WHERE compcode='DAS' AND trndate >= DATEADD(MONTH, -6, GETDATE())\n  29. [inventory_pattern] To find stock that hasn't moved in 6 months, use itemledger with GROUP BY itemcode and HAVING MAX(trndate) < DATEADD(MONTH, -6, GETDATE()), then JOIN to itemmas to get item names.\n     e.g.: SELECT il.itemcode, im.itemname, MAX(il.trndate) AS last_move FROM itemledger il JOIN itemmas im ON il.itemcode=im.itemcode AND il.compcode=im.compcode WHERE il.compcode='DAS' GROUP BY il.itemcode, im.itemname HAVING MAX(il.trndate) < DATEADD(MONTH,-6,GETDATE())\n  30. [table_structure] rmstock and rmstock1 tables are deprecated/stale with no recent data. Use itemledger instead for current inventory transaction tracking.\n     e.g.: SELECT itemcode, trndate FROM itemledger WHERE compcode='DAS' ORDER BY trndate DESC\n  31. [column_values] invdtl table uses `itemqty` for line-item quantity, NOT `inv_qty`, `trn_qty`, or `qty`. All variations are wrong.\n     e.g.: SELECT d.invno, d.itemcode, d.itemqty FROM invdtl AS d WHERE d.compcode='DAS' AND d.itemqty > 0\n  32. [table_structure] invlotdetail stores batch traceability (advdespqty = dispatch qty) but has NO trn_qty, trndate, or stock movement columns. For stock transactions, query itemstock or itemstock_erp instead.\n     e.g.: SELECT ild.itemcode, ild.lotno, ild.advdespqty FROM invlotdetail AS ild WHERE ild.compcode='DAS' AND ild.itemcode='CP-042'\n  33. [table_mapping] Inventory ledger is stored in itemledger table (not \"item\"). Use itemledger.opbal, itemledger.production, itemledger.sale, itemledger.trndate for stock movement tracking.\n     e.g.: SELECT itemcode, SUM(opbal) AS opening, SUM(production) AS prod, SUM(sale) AS sales FROM itemledger WHERE compcode='DAS' GROUP BY itemcode\n  34. [column_names] itemledger closing stock calculation: opbal + sfgpur + production - jobist - jobiind - custrej - fgtowip - sale - sfgsale - olscrap - jobout - rgpout. Use SUM() of these columns grouped by itemcode.\n     e.g.: SELECT itemcode, SUM(opbal + sfgpur + production - jobist - jobiind - custrej - fgtowip - sale - sfgsale - olscrap - jobout - rgpout) AS closing_qty FROM itemledger WHERE compcode='DAS' GROUP BY itemcode\n  35. [business_rule] Non-moving stock = items with opening balance (opbal) but zero sales activity. Filter: SUM(sale + sfgsale) = 0 AND opbal > 0. Join itemledger to itemmas for item names and UOM.\n     e.g.: SELECT il.itemcode, im.itemname, im.uom, MAX(CASE WHEN il.type='OPBAL' THEN il.opbal ELSE 0 END) AS opening_qty FROM itemledger il LEFT JOIN itemmas im ON il.compcode=im.compcode AND il.itemcode=im.itemcode WHERE il.compcode='DAS' GROUP BY il.itemcode, im.itemname, im.uom HAVING SUM(il.sale + il.sfgsale) = 0\n  36. [column_values] Machine \"CNC-TSUGAMI (PDF-362)\" has mccode='PDF362' in the machine table. Use this code to join with product1.machine_cd.\n     e.g.: SELECT mccode, mcname FROM machine WHERE compcode='DAS' AND mcname LIKE '%TSUGAMI%' AND mcname LIKE '%362%'\n  37. [column_values] product1 does NOT have a 'targetqty' column. For OEE/production analysis, use: qtyinpic (planned qty), acptqty (accepted), rejqty (rejected), scrapqty (scrap), shifthrs (shift hours).\n     e.g.: SELECT qtyinpic, acptqty, rejqty, scrapqty, shifthrs FROM product1 WHERE compcode='DAS' LIMIT 1\n  38. [join_key] Downtime analysis requires three-table join: product1 → viewproductionDownTime (on prodno) → downtimemas (on downcode). Use downtimemas.downreason (NOT downtimename) for downtime reason description.\n     e.g.: SELECT dm.downcode, dm.downreason, SUM(vdt.TotalDownTimeinMinutes) FROM viewproductionDownTime vdt JOIN downtimemas dm ON vdt.downcode=dm.downcode AND vdt.compcode=dm.compcode WHERE vdt.compcode='DAS' GROUP BY dm.downcode, dm.downreason\n  39. [business_rule] OEE components: Quality% = (acptqty / qtyinpic) × 100; Availability% = ((shifthrs - downtime_hours) / shifthrs) × 100. Use viewproductionDownTime.TotalDownTimeinMinutes (divide by 60 for hours).\n     e.g.: SELECT COUNT(DISTINCT prodno) AS runs, SUM(acptqty) AS accepted, SUM(qtyinpic) AS planned, SUM(shifthrs) AS shift_hrs, SUM(downtime)/60.0 AS downtime_hrs FROM product1 WHERE compcode='DAS' AND machine_cd='PDF362'\n  40. [table_name] Machine registry is in table `machine` (NOT `machinemas` or `mactypemas`). Columns: `mccode` (machine code PK), `mcname` (machine name), `compcode` (company), `macgroup` (machine group like 'FORG' for forging), `Include_In_OEE` (Y/N flag).\n     e.g.: SELECT mccode, mcname, macgroup FROM machine WHERE compcode='DAS' AND macgroup='FORG' ORDER BY mcname\n  41. [join_key] Production (product1) table joins to machine via: `product1.machine_cd = machine.mccode`. The column `machine_cd` in product1 stores the mccode reference, not a separate machine table ID.\n     e.g.: SELECT p.proddate, m.mcname, SUM(p.acptqty) FROM product1 p JOIN machine m ON p.compcode=m.compcode AND p.machine_cd=m.mccode WHERE p.compcode='DAS' AND YEAR(p.proddate)=2026 GROUP BY p.proddate, m.mcname\n  42. [column_values] Hot Forging presses in machine table are identified by mcname containing 'H F' (e.g., 'H F 100 TON (PDF-145)', 'H F 160 TON (PDF-124/185)', 'H F 1000 TON (PDF-141/146)') and macgroup='FORG'. Search with LIKE '%H F%' to find them.\n     e.g.: SELECT mccode, mcname FROM machine WHERE compcode='DAS' AND macgroup='FORG' AND mcname LIKE '%H F%'\n  43. [column_values] saleitemmas does NOT have a 'itemname' column. Use 'salesname' (string) for the product display name instead. saleitemmas.salesname is the commercial nomenclature (e.g., 'GEAR PRIMARY DRIVEN').\n     e.g.: SELECT itemcode, salesname, partno, uom FROM saleitemmas WHERE compcode='DAS' AND partno='23111ACL0000010'\n  44. [column_values] product1 does NOT have 'prodqty' column. Use 'acptqty' (accepted quantity, numeric) for production quantity accepted. Also available: rejqty (rejected), scrapqty (scrap), qtyinpic (in process).\n     e.g.: SELECT itemcode, lotno, proddate, acptqty, rejqty, cycle, machine_cd FROM product1 WHERE compcode='DAS' ORDER BY proddate DESC\n  45. [column_values] partno (OEM/vendor part number) is NOT in itemmas. It is stored in saleitemmas.partno or podtl.partno. Join saleitemmas to itemcode to find part numbers.\n     e.g.: SELECT si.itemcode, si.partno, si.salesname FROM saleitemmas si WHERE si.compcode='DAS' AND si.partno='23111ACL0000010'\n  46. [join_key] invhdr and invdtl must join on THREE columns: compcode, invno, AND invyear. Using only compcode+invno will cause incorrect grouping or binding errors.\n     e.g.: SELECT ih.invno, ih.invyear, COUNT(*) FROM invhdr ih INNER JOIN invdtl id ON ih.compcode=id.compcode AND ih.invno=id.invno AND ih.invyear=id.invyear WHERE ih.compcode='DAS' GROUP BY ih.invno, ih.invyear","facts":[{"domain":"table_schema","fact":"PPC's vendor purchase orders are stored in `cpohdr` (Consumable Purchase Order Header) table, not `pohdr` (which is for customer purchase orders). Join `cpohdr` to `suppmas` on compcode+supcode to get supplier names.","example_sql":"\nSELECT \n    s.supcode,\n    s.suppname,\n    COUNT(DISTINCT c.pono) as total_orders,\n    SUM(c.povalue) as total_purchase_value\nFROM cpohdr c\nINNER JOIN suppmas s ON c.compcode = s.compcode AND c.supcode = s.supcode\nWHERE c.compcode = 'DAS' \n    AND YEAR(c.podate) = 2025\nGROUP BY s.supcode, s.suppname\nORDER BY total_purchase_value DESC\n","discovered":"2026-05-03T22:37:59.953402"},{"domain":"column_names","fact":"Vendor/supplier identifier is `supcode` (not vendcode) in purchase order tables. Supplier master is `suppmas` with columns: supcode (PK), suppname, compcode (PK).","example_sql":"\nSELECT supcode, suppname FROM suppmas WHERE compcode='DAS' AND lock='N' LIMIT 10\n","discovered":"2026-05-03T22:37:59.956426"},{"domain":"table_schema","fact":"PPC PO tables: `cpohdr` (consumables/general items, type='CS'), `pohdr` (customer purchase orders), `suppohdr` (different supplier PO context). For vendor purchase value reporting, use `cpohdr` with column `povalue` for total PO amount.","example_sql":"\nSELECT podate, pono, supcode, povalue FROM cpohdr WHERE compcode='DAS' AND povalue > 0\n","discovered":"2026-05-03T22:37:59.959850"},{"domain":"column_naming","fact":"Dispatch Advice header table uses `advicedate` (not despdate) for the document date, and the detail table uses `adviceqty` (not despqty) for quantities dispatched.","example_sql":"SELECT h.adviceno, h.advicedate, d.adviceqty FROM despadvicehdr h INNER JOIN despadvicedtl d ON h.compcode=d.compcode AND h.adviceno=d.adviceno WHERE h.compcode='DAS' AND YEAR(h.advicedate)=2022","discovered":"2026-05-04T10:31:42.257513"},{"domain":"table_structure","fact":"Dispatch advice data is stored across two tables: despadvicehdr (header with date/customer) and despadvicedtl (detail with items/quantities), joined on compcode and adviceno.","example_sql":"SELECT h.adviceno, h.advicedate, c.custname, d.itemcode, im.itemname, d.adviceqty FROM despadvicehdr h INNER JOIN despadvicedtl d ON h.compcode=d.compcode AND h.adviceno=d.adviceno INNER JOIN cust c ON h.custcode=c.custcode AND c.compcode=h.compcode INNER JOIN itemmas im ON d.itemcode=im.itemcode AND d.compcode=im.compcode WHERE h.compcode='DAS'","discovered":"2026-05-04T10:31:42.260589"},{"domain":"table_schema","fact":"The schedule date range for OAs is stored in oahdr.schdatefrom and oahdr.schdateto columns (not in a separate schedule table like oasch for this context), and invhdr.oano links invoices to the original order advice.","example_sql":"SELECT oh.oano, oh.schdatefrom, oh.schdateto, ih.invdate FROM oahdr oh LEFT JOIN invhdr ih ON oh.compcode = ih.compcode AND oh.oano = ih.oano WHERE oh.compcode='DAS' AND oh.custcode='SKSSL(A)'","discovered":"2026-05-05T18:20:26.722155"},{"domain":"column_values","fact":"When user says \"KOYO\", check BOTH custcode='KOYO' (JTEKT BEARINGS INDIA PVT LTD — active, has recent invoices) AND custname LIKE '%KOYO%' (Sona Koyo Steering entities — inactive since 2018). Always search by custcode first: SELECT custcode, custname FROM cust WHERE compcode='DAS' AND (custcode='KOYO' OR custname LIKE '%KOYO%') AND lock='N'.","example_sql":"SELECT custcode, custname FROM cust WHERE compcode='DAS' AND (custcode='KOYO' OR custname LIKE '%KOYO%') AND lock='N'","discovered":"2026-05-05T18:20:26.722871"},{"domain":"join_key","fact":"OAs are linked to invoices via invhdr.oano = oahdr.oano (and compcode), allowing schedule vs. dispatch tracking by comparing oahdr.schdateto with MAX(invhdr.invdate) grouped by oano.","example_sql":"SELECT oh.oano, oh.schdateto, MAX(ih.invdate) as last_invdate, DATEDIFF(DAY, oh.schdateto, MAX(ih.invdate)) as days_late FROM oahdr oh LEFT JOIN invhdr ih ON oh.compcode=ih.compcode AND oh.oano=ih.oano WHERE oh.compcode='DAS' GROUP BY oh.oano, oh.schdateto","discovered":"2026-05-05T18:20:26.723272"},{"domain":"column_values","fact":"The invoice amount column in invhdr is `inv_amt` (numeric, with underscore), not `invamt`. All financial queries referencing total invoice value must use inv_amt.","example_sql":"SELECT invno, inv_amt FROM invhdr WHERE compcode='DAS' AND inv_amt > 100000 ORDER BY inv_amt DESC","discovered":"2026-05-05T21:49:25.957972"},{"domain":"schedule_adherence","fact":"Schedule adherence analysis requires LEFT JOINing oahdr→oadtl→invhdr→invdtl, comparing oadtl.qty (ordered) vs SUM(invdtl.itemqty) (dispatched), and filtering invhdr.invtype=0 (sales only) with cancel_flag='N'.","example_sql":"SELECT oh.oano, SUM(od.qty) AS ordered_qty, SUM(ISNULL(id.itemqty,0)) AS dispatched_qty, CASE WHEN SUM(od.qty)>0 THEN ROUND(100.0*SUM(ISNULL(id.itemqty,0))/SUM(od.qty),2) ELSE 0 END AS adherence_pct FROM oahdr oh INNER JOIN oadtl od ON oh.compcode=od.compcode AND oh.oano=od.oano LEFT JOIN invhdr ih ON oh.compcode=ih.compcode AND oh.oano=ih.oano AND ih.invtype=0 AND ih.cancel_flag='N' LEFT JOIN invdtl id ON ih.compcode=id.compcode AND ih.invno=id.invno AND ih.invyear=id.invyear AND od.itemcode=id.itemcode WHERE oh.compcode='DAS' GROUP BY oh.oano","discovered":"2026-05-05T21:49:25.961359"},{"domain":"business_rule","fact":"Sona Koyo Steering entities (SKSSL(A), SKSSL-G, SONA-2, SONA39, SONAC, SONAS) have no OAs/invoices after 2018. But custcode='KOYO' (JTEKT Bearings India) IS active with recent invoices — do NOT confuse them.","example_sql":"SELECT custcode, custname FROM cust WHERE compcode='DAS' AND custname LIKE '%KOYO%' AND lock='N'","discovered":"2026-05-05T21:49:25.961749"},{"domain":"column_values","fact":"CS Plate product is itemcode='CSPLATE' with itemname='C S PLATE (3341003000)' in itemmas table. Use this code to filter dispatch queries for CS Plate.","example_sql":"SELECT itemcode, itemname FROM itemmas WHERE compcode='DAS' AND itemcode='CSPLATE'","discovered":"2026-05-06T08:46:51.075130"},{"domain":"join_key","fact":"Dispatch item details live in despadvicedtl.itemcode, joined to despadvicehdr on (compcode, adviceno). Always join despadvicehdr → despadvicedtl before filtering by itemcode.","example_sql":"SELECT dah.advicedate, dad.itemcode, dad.adviceqty FROM despadvicehdr dah INNER JOIN despadvicedtl dad ON dah.compcode=dad.compcode AND dah.adviceno=dad.adviceno WHERE dah.compcode='DAS' AND dad.itemcode='CSPLATE'","discovered":"2026-05-06T08:46:51.079003"},{"domain":"query_pattern","fact":"To filter dispatch advices for a specific date range, use DATEADD(DAY, -N, (SELECT MAX(advicedate) FROM despadvicehdr WHERE compcode='DAS')) to get last N days from latest system date.","example_sql":"SELECT * FROM despadvicehdr WHERE compcode='DAS' AND advicedate >= DATEADD(DAY, -7, (SELECT MAX(advicedate) FROM despadvicehdr WHERE compcode='DAS'))","discovered":"2026-05-06T08:46:51.079848"},{"domain":"customer_identification","fact":"JTEKT Bearing is custcode 'KOYO' (JTEKT BEARINGS INDIA PVT LTD). Other JTEKT entities use different codes (JTEKT(A), JTEKT(C), JTEKT(D-19), JTEKT(D32), JTEKT(G), JTEKTREWAR) with their own invoice streams.","example_sql":"SELECT custcode, custname FROM cust WHERE compcode='DAS' AND custname LIKE '%JTEKT%' ORDER BY custname","discovered":"2026-05-06T08:49:46.589781"},{"domain":"table_lifecycle","fact":"despadvicehdr (dispatch advices) for KOYO last updated July 2022. For dispatch data after 2022, use invhdr + invdtl (sales invoices) instead. Dispatch advices are stale/inactive for this customer.","example_sql":"SELECT MAX(advicedate) FROM despadvicehdr WHERE compcode='DAS' AND custcode='KOYO'","discovered":"2026-05-06T08:49:46.591347"},{"domain":"column_naming","fact":"invhdr uses column 'inv_amt' (NOT 'invamt'). invdtl has NO 'invtype' column — invtype is in invhdr only. Join invhdr to invdtl on (compcode, invno, invyear) WITHOUT invtype.","example_sql":"SELECT h.invno, h.inv_amt FROM invhdr h INNER JOIN invdtl d ON h.compcode=d.compcode AND h.invno=d.invno AND h.invyear=d.invyear WHERE h.compcode='DAS'","discovered":"2026-05-06T08:49:46.592154"},{"domain":"column_values","fact":"despadvicedtl.custcode is the correct location for customer filter in dispatch advices; despadvicehdr.custcode is NULL for all records since mid-2022. Always join despadvicedtl and filter by d.custcode for dispatch queries.","example_sql":"SELECT dah.adviceno, dd.custcode, dd.adviceqty FROM despadvicehdr dah INNER JOIN despadvicedtl dd ON dah.compcode=dd.compcode AND dah.adviceno=dd.adviceno WHERE dah.compcode='DAS' AND dd.custcode='SONA-2'","discovered":"2026-05-06T16:12:30.166880"},{"domain":"column_values","fact":"despadvicedtl quantity column is 'adviceqty', NOT 'itemvalue'. invdtl quantity is 'itemqty' and amount is 'itemamt', NOT 'itemvalue'.","example_sql":"SELECT adviceqty FROM despadvicedtl WHERE compcode='DAS'; SELECT itemqty, itemamt FROM invdtl WHERE compcode='DAS'","discovered":"2026-05-06T16:12:30.167921"},{"domain":"business_rule","fact":"For KOYO (customer entities: SKSSL(A), SONA-2, SONAC, SONA39, SONAS), last invoice activity was May 2018. They have no dispatch advices or sales invoices in the last year; for recent data, verify with customer service whether they remain active.","example_sql":"SELECT MAX(invdate) FROM invhdr WHERE compcode='DAS' AND custcode IN ('SKSSL(A)','SONA-2','SONAC','SONA39','SONAS')","discovered":"2026-05-06T16:12:30.168843"},{"domain":"sql_compatibility","fact":"SQL Server 2008 R2 does NOT support FORMAT() function (added in SQL Server 2012). Use CAST(column AS DATE) or CONVERT() instead for date formatting.","example_sql":"-- SQL Server 2008 R2 compatible:\nDECLARE @OneMonthAgo DATETIME = DATEADD(MONTH, -1, CAST(GETDATE() AS DATE))\nSELECT CAST(h.advicedate AS DATE) AS advice_date FROM despadvicehdr h WHERE h.advicedate >= @OneMonthAgo","discovered":"2026-05-06T16:39:11.679643"},{"domain":"join_key","fact":"despadvicehdr.custcode is NULL for most recent records (post mid-2022). Always use despadvicedtl.custcode for filtering by customer in dispatch queries.","example_sql":"SELECT h.adviceno, h.advicedate, d.custcode FROM despadvicehdr h INNER JOIN despadvicedtl d ON h.compcode=d.compcode AND h.adviceno=d.adviceno WHERE h.compcode='DAS' AND d.custcode='KOYO'","discovered":"2026-05-06T16:39:11.680245"},{"domain":"column_values","fact":"partno in saleitemmas is stored with prefix 'Item Code-' (e.g., 'Item Code-1000294'). When searching by partno, use LIKE '%1000294%' or prepend 'Item Code-' to exact searches.","example_sql":"SELECT itemcode, salesname, partno FROM saleitemmas WHERE compcode='DAS' AND partno LIKE '%1000294%'","discovered":"2026-05-06T16:43:19.729453"},{"domain":"column_mapping","fact":"saleitemmas.salesname is the product commercial name (replaces itemname). Do NOT attempt to join itemmas for item name when working with saleitemmas.","example_sql":"SELECT itemcode, salesname, partno, sale_rate FROM saleitemmas WHERE compcode='DAS' AND partno LIKE '%1000294%'","discovered":"2026-05-06T16:43:19.729928"},{"domain":"table_structure","fact":"pohdr tracks CUSTOMER purchase orders (not supplier orders). Use custcode to join to cust table, not a supplier master.","example_sql":"SELECT p.PONO, p.POdate, c.custname FROM pohdr p INNER JOIN cust c ON p.compcode=c.compcode AND p.custcode=c.custcode WHERE p.compcode='DAS'","discovered":"2026-05-06T16:43:19.730417"},{"domain":"join_key","fact":"invhdr has a composite primary key of (compcode, invtype, invno, invyear). When joining with invdtl or grouping, all four columns must be included in GROUP BY when selecting non-aggregated columns.","example_sql":"SELECT h.invno, h.invdate, SUM(d.itemamt) AS total_amt FROM invhdr h INNER JOIN invdtl d ON h.compcode=d.compcode AND h.invno=d.invno AND h.invyear=d.invyear WHERE h.compcode='DAS' GROUP BY h.compcode, h.invtype, h.invno, h.invyear, h.invdate","discovered":"2026-05-06T18:38:06.285235"},{"domain":"column_values","fact":"EXEDY INDIA LIMITED has custcode='EIL' in the customer master (cust table).","example_sql":"SELECT custcode, custname FROM cust WHERE compcode='DAS' AND custname LIKE '%EXEDY%'","discovered":"2026-05-06T18:38:06.286194"},{"domain":"column_values","fact":"itemledger table has column `trndate` (not `trandate`). Use this for transaction dates when tracking item movement history.","example_sql":"SELECT itemcode, trndate, production, sale FROM itemledger WHERE compcode='DAS' AND trndate >= DATEADD(MONTH, -6, GETDATE())","discovered":"2026-05-08T13:02:56.526992"},{"domain":"inventory_pattern","fact":"To find stock that hasn't moved in 6 months, use itemledger with GROUP BY itemcode and HAVING MAX(trndate) < DATEADD(MONTH, -6, GETDATE()), then JOIN to itemmas to get item names.","example_sql":"SELECT il.itemcode, im.itemname, MAX(il.trndate) AS last_move FROM itemledger il JOIN itemmas im ON il.itemcode=im.itemcode AND il.compcode=im.compcode WHERE il.compcode='DAS' GROUP BY il.itemcode, im.itemname HAVING MAX(il.trndate) < DATEADD(MONTH,-6,GETDATE())","discovered":"2026-05-08T13:02:56.532123"},{"domain":"table_structure","fact":"rmstock and rmstock1 tables are deprecated/stale with no recent data. Use itemledger instead for current inventory transaction tracking.","example_sql":"SELECT itemcode, trndate FROM itemledger WHERE compcode='DAS' ORDER BY trndate DESC","discovered":"2026-05-08T13:02:56.533064"},{"domain":"column_values","fact":"invdtl table uses `itemqty` for line-item quantity, NOT `inv_qty`, `trn_qty`, or `qty`. All variations are wrong.","example_sql":"SELECT d.invno, d.itemcode, d.itemqty FROM invdtl AS d WHERE d.compcode='DAS' AND d.itemqty > 0","discovered":"2026-05-08T13:07:07.016942"},{"domain":"table_structure","fact":"invlotdetail stores batch traceability (advdespqty = dispatch qty) but has NO trn_qty, trndate, or stock movement columns. For stock transactions, query itemstock or itemstock_erp instead.","example_sql":"SELECT ild.itemcode, ild.lotno, ild.advdespqty FROM invlotdetail AS ild WHERE ild.compcode='DAS' AND ild.itemcode='CP-042'","discovered":"2026-05-08T13:07:07.017530"},{"domain":"table_mapping","fact":"Inventory ledger is stored in itemledger table (not \"item\"). Use itemledger.opbal, itemledger.production, itemledger.sale, itemledger.trndate for stock movement tracking.","example_sql":"SELECT itemcode, SUM(opbal) AS opening, SUM(production) AS prod, SUM(sale) AS sales FROM itemledger WHERE compcode='DAS' GROUP BY itemcode","discovered":"2026-05-08T13:08:53.027536"},{"domain":"column_names","fact":"itemledger closing stock calculation: opbal + sfgpur + production - jobist - jobiind - custrej - fgtowip - sale - sfgsale - olscrap - jobout - rgpout. Use SUM() of these columns grouped by itemcode.","example_sql":"SELECT itemcode, SUM(opbal + sfgpur + production - jobist - jobiind - custrej - fgtowip - sale - sfgsale - olscrap - jobout - rgpout) AS closing_qty FROM itemledger WHERE compcode='DAS' GROUP BY itemcode","discovered":"2026-05-08T13:08:53.028289"},{"domain":"business_rule","fact":"Non-moving stock = items with opening balance (opbal) but zero sales activity. Filter: SUM(sale + sfgsale) = 0 AND opbal > 0. Join itemledger to itemmas for item names and UOM.","example_sql":"SELECT il.itemcode, im.itemname, im.uom, MAX(CASE WHEN il.type='OPBAL' THEN il.opbal ELSE 0 END) AS opening_qty FROM itemledger il LEFT JOIN itemmas im ON il.compcode=im.compcode AND il.itemcode=im.itemcode WHERE il.compcode='DAS' GROUP BY il.itemcode, im.itemname, im.uom HAVING SUM(il.sale + il.sfgsale) = 0","discovered":"2026-05-08T13:08:53.028994"},{"domain":"column_values","fact":"Machine \"CNC-TSUGAMI (PDF-362)\" has mccode='PDF362' in the machine table. Use this code to join with product1.machine_cd.","example_sql":"SELECT mccode, mcname FROM machine WHERE compcode='DAS' AND mcname LIKE '%TSUGAMI%' AND mcname LIKE '%362%'","discovered":"2026-05-11T15:44:55.834824"},{"domain":"column_values","fact":"product1 does NOT have a 'targetqty' column. For OEE/production analysis, use: qtyinpic (planned qty), acptqty (accepted), rejqty (rejected), scrapqty (scrap), shifthrs (shift hours).","example_sql":"SELECT qtyinpic, acptqty, rejqty, scrapqty, shifthrs FROM product1 WHERE compcode='DAS' LIMIT 1","discovered":"2026-05-11T15:44:55.836440"},{"domain":"join_key","fact":"Downtime analysis requires three-table join: product1 → viewproductionDownTime (on prodno) → downtimemas (on downcode). Use downtimemas.downreason (NOT downtimename) for downtime reason description.","example_sql":"SELECT dm.downcode, dm.downreason, SUM(vdt.TotalDownTimeinMinutes) FROM viewproductionDownTime vdt JOIN downtimemas dm ON vdt.downcode=dm.downcode AND vdt.compcode=dm.compcode WHERE vdt.compcode='DAS' GROUP BY dm.downcode, dm.downreason","discovered":"2026-05-11T15:44:55.837283"},{"domain":"business_rule","fact":"OEE components: Quality% = (acptqty / qtyinpic) × 100; Availability% = ((shifthrs - downtime_hours) / shifthrs) × 100. Use viewproductionDownTime.TotalDownTimeinMinutes (divide by 60 for hours).","example_sql":"SELECT COUNT(DISTINCT prodno) AS runs, SUM(acptqty) AS accepted, SUM(qtyinpic) AS planned, SUM(shifthrs) AS shift_hrs, SUM(downtime)/60.0 AS downtime_hrs FROM product1 WHERE compcode='DAS' AND machine_cd='PDF362'","discovered":"2026-05-11T15:44:55.838271"},{"domain":"table_name","fact":"Machine registry is in table `machine` (NOT `machinemas` or `mactypemas`). Columns: `mccode` (machine code PK), `mcname` (machine name), `compcode` (company), `macgroup` (machine group like 'FORG' for forging), `Include_In_OEE` (Y/N flag).","example_sql":"SELECT mccode, mcname, macgroup FROM machine WHERE compcode='DAS' AND macgroup='FORG' ORDER BY mcname","discovered":"2026-05-14T08:54:21.914194"},{"domain":"join_key","fact":"Production (product1) table joins to machine via: `product1.machine_cd = machine.mccode`. The column `machine_cd` in product1 stores the mccode reference, not a separate machine table ID.","example_sql":"SELECT p.proddate, m.mcname, SUM(p.acptqty) FROM product1 p JOIN machine m ON p.compcode=m.compcode AND p.machine_cd=m.mccode WHERE p.compcode='DAS' AND YEAR(p.proddate)=2026 GROUP BY p.proddate, m.mcname","discovered":"2026-05-14T08:54:21.919111"},{"domain":"column_values","fact":"Hot Forging presses in machine table are identified by mcname containing 'H F' (e.g., 'H F 100 TON (PDF-145)', 'H F 160 TON (PDF-124/185)', 'H F 1000 TON (PDF-141/146)') and macgroup='FORG'. Search with LIKE '%H F%' to find them.","example_sql":"SELECT mccode, mcname FROM machine WHERE compcode='DAS' AND macgroup='FORG' AND mcname LIKE '%H F%'","discovered":"2026-05-14T08:54:21.919635"},{"domain":"column_values","fact":"saleitemmas does NOT have a 'itemname' column. Use 'salesname' (string) for the product display name instead. saleitemmas.salesname is the commercial nomenclature (e.g., 'GEAR PRIMARY DRIVEN').","example_sql":"SELECT itemcode, salesname, partno, uom FROM saleitemmas WHERE compcode='DAS' AND partno='23111ACL0000010'","discovered":"2026-05-18T07:32:31.505198"},{"domain":"column_values","fact":"product1 does NOT have 'prodqty' column. Use 'acptqty' (accepted quantity, numeric) for production quantity accepted. Also available: rejqty (rejected), scrapqty (scrap), qtyinpic (in process).","example_sql":"SELECT itemcode, lotno, proddate, acptqty, rejqty, cycle, machine_cd FROM product1 WHERE compcode='DAS' ORDER BY proddate DESC","discovered":"2026-05-18T07:32:31.509797"},{"domain":"column_values","fact":"partno (OEM/vendor part number) is NOT in itemmas. It is stored in saleitemmas.partno or podtl.partno. Join saleitemmas to itemcode to find part numbers.","example_sql":"SELECT si.itemcode, si.partno, si.salesname FROM saleitemmas si WHERE si.compcode='DAS' AND si.partno='23111ACL0000010'","discovered":"2026-05-18T07:32:31.510541"},{"domain":"join_key","fact":"invhdr and invdtl must join on THREE columns: compcode, invno, AND invyear. Using only compcode+invno will cause incorrect grouping or binding errors.","example_sql":"SELECT ih.invno, ih.invyear, COUNT(*) FROM invhdr ih INNER JOIN invdtl id ON ih.compcode=id.compcode AND ih.invno=id.invno AND ih.invyear=id.invyear WHERE ih.compcode='DAS' GROUP BY ih.invno, ih.invyear","discovered":"2026-05-18T18:41:03.883174"}]}