query_sql_direct

Run a read-only T-SQL SELECT directly against a SAP B1 SQL Server database, bypassing Service Layer entirely. Use this when Service Layer is unavailable/misconfigured for a company, or when a question needs a join/aggregation across tables that's awkward via OData. Requires MSSQL_HOST, MSSQL_DATABASE, MSSQL_USER, MSSQL_PASSWORD (and MSSQL_INSTANCE for a named instance like SERVER\SQLEXPRESS01) to be set in this connector's environment — if not configured, this tool will error saying so. Multiple companies can live on the same SQL Server instance — pass "database" to target a specific one instead of the connector's default. Known databases on this server: DC_LiveDB — Dewang Corporation NCPL_110126 — Neshiel Agrochem Pvt Ltd NC_LIVE — Neshiel Corporation NOCPL_LIVE — Neshiel Ventures Private Limited Only SELECT statements are allowed; anything else is rejected.

SAP Business One key tables (T-SQL / MSSQL):

SALES & QUOTATIONS: OQUT(DocEntry,DocNum,CardCode,CardName,SlpCode,DocDate,DocDueDate,DocTotal,GrossProfit,DocStatus,Comments) QUT1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode,GrssProfit) ORDR(DocEntry,DocNum,CardCode,CardName,SlpCode,DocDate,DocDueDate,DocTotal,GrossProfit,DocStatus,Comments) RDR1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode,GrssProfit)

AR INVOICES & DELIVERY: OINV(DocEntry,DocNum,CardCode,CardName,SlpCode,DocDate,DocDueDate,DocTotal,GrossProfit,PaidToDate,DocStatus) INV1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode,GrssProfit) ODLN(DocEntry,DocNum,CardCode,CardName,DocDate,DocTotal,DocStatus) DLN1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode) ORIN(DocEntry,DocNum,CardCode,CardName,DocDate,DocTotal,DocStatus) RIN1(DocEntry,LineNum,ItemCode,Dscription,Quantity,LineTotal,WhsCode)

PURCHASING: OPQT(DocEntry,DocNum,CardCode,CardName,DocDate,DocTotal,DocStatus) PQT1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode) OPOR(DocEntry,DocNum,CardCode,CardName,DocDate,DocDueDate,DocTotal,DocStatus) POR1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode) OPDN(DocEntry,DocNum,CardCode,CardName,DocDate,DocTotal,DocStatus) PDN1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode) OPCH(DocEntry,DocNum,CardCode,CardName,DocDate,DocDueDate,DocTotal,DocStatus) PCH1(DocEntry,LineNum,ItemCode,Dscription,Quantity,Price,LineTotal,WhsCode)

INVENTORY: OITM(ItemCode,ItemName,ItmsGrpCod,InvntItem,SellItem,PrchseItem,OnHand,IsCommited,OnOrder,LastPurPrc,AvgPrice) OITW(ItemCode,WhsCode,OnHand,IsCommited,OnOrder) OWHS(WhsCode,WhsName,Street,City) OITB(ItmsGrpCod,ItmsGrpNam)

BUSINESS PARTNERS: OCRD(CardCode,CardName,CardType,GroupCode,Phone1,E_Mail,CntctPrsn,Balance,SlpCode,Territory) OSLP(SlpCode,SlpName,Memo,Commission,Phone,Email) OCRG(GroupCode,GroupName,GroupType)

FINANCE: OJDT(TransId,TransType,RefDate,Memo,Ref1,Ref2,Ref3,CreatedBy) JDT1(TransId,Line_ID,Account,Debit,Credit,SYSDebit,SYSCredit,FCDebit,FCCredit,RefDate,LineMemo,ContraAct) OACT(AcctCode,AcctName,GroupMask,ActType,Blocked,CurrTotal,LocTotal)

STATUS CODES: DocStatus = 'O' (Open) | 'C' (Closed) | 'W' (Cancelled) CardType: 'C' (Customer) | 'S' (Supplier) | 'L' (Lead)

NCPL_110126 SALES ORDER BROKERAGE — field names verified against live data, do not substitute others: RDR1.U_Brok_Seller = Seller Brokerage Rate (numeric, per-unit, e.g. rupees per Ton) RDR1.U_Brok_Buyer = Buyer Brokerage Rate (numeric, per-unit) RDR1.U_Sel_Brok_AP = basis flag (observed value "Amount" = the rate above is a flat per-unit amount, not a percentage) Do NOT use U_S_BrokPerQty, U_Seller_Brok_Per, or U_SPLRBT — these are unused/always NULL in this database and will silently produce wrong or zero results. If U_Brok_Seller or U_Brok_Buyer is NULL, treat as zero unless told otherwise.

Calculation: Seller Brokerage Amount = RDR1.Quantity * RDR1.U_Brok_Seller Buyer Brokerage Amount = RDR1.Quantity * RDR1.U_Brok_Buyer Total Brokerage = Seller Brokerage Amount + Buyer Brokerage Amount

A brokerage query/report should join ORDR (DocEntry, DocNum, DocDate, CardCode, CardName) to RDR1 (ItemCode, Dscription, Quantity, Price, LineTotal, U_Brok_Seller, U_Brok_Buyer) on DocEntry, and return both rates and both calculated amounts plus Total Brokerage per line. SAP B1's financial year is April-March — "April 2026" means DocDate between 2026-04-01 and 2026-04-30 unless the user says otherwise.