viewsobalance

### 1. Overview
 The `viewsobalance` table provides a denormalized view of Sales Order (SO) fulfillment, specifically tracking the relationship between Customer Purchase Orders (PO) and subsequent Invoices. It allows for auditing quantity and price variances between order placement and billing.
 
 ### 2. Grain & Lifecycle
 **Grain:** Company + Customer PO + Item + Invoice Number.
 **Lifecycle:** A record is generated when an invoice is issued against a specific item line of a customer's purchase order. It persists as a historical record of fulfillment.
 
 ### 3. Column Dictionary
 | Column | Type | Nullable | Meaning | Allowed Values | SQL Usage |
 | :--- | :--- | :--- | :--- | :--- | :--- |
 | compcode | string | No | Company Identifier | e.g., "DAS" | Filter/Join Key |
 | pocust | string | Yes | Concatenated PO and Customer Code | | Reference |
 | custcode | string | No | Unique Customer Identifier | | Join to `suppmas` (Likely) |
 | pono | string | No | Customer Purchase Order Number | | Join to `pohdr` |
 | podate | datetime | Yes | Date the Customer PO was issued | | Temporal Analysis |
 | itemcode | string | No | Internal Item Identifier | | Join to `itemmas` |
 | partno | string | Yes | Customer/Manufacturer Part Number | | Display |
 | itemqty | number | Yes | Total Quantity ordered on the PO line | | Capacity planning |
 | invno | integer | No | Invoice Number | | Join to `invhdr` |
 | invdate | datetime | Yes | Date of Billing | | Aging/Revenue |
 | Invqty | number | Yes | Quantity billed in this specific invoice | | Sum for fulfillment % |
 | InvRate | number | Yes | Actual rate applied on the invoice | | Pricing Audit |
 | porate | number | Yes | Original rate agreed in the PO | | Price Variance |
 | povalue | number | Yes | Line value calculated as `Invqty * porate` | | Financial Reporting |
 | lock | string | Yes | Row status or posting lock | 'Y', 'N' | Filter active rows |
 
 ### 4. Relationships & Join Map
 #### Confirmed Joins
 * **itemmas**: `JOIN itemmas ON viewsobalance.compcode = itemmas.compcode AND viewsobalance.itemcode = itemmas.itemcode`
 * **pohdr**: `JOIN pohdr ON viewsobalance.compcode = pohdr.compcode AND viewsobalance.pono = pohdr.pono`
 
 #### Likely Joins
 * **invhdr**: `JOIN invhdr ON viewsobalance.compcode = invhdr.compcode AND viewsobalance.invno = invhdr.invno` (Note: `invtype` and `invyear` should be inferred from `invdate` for a unique join).
 * **saleitemmas**: `JOIN saleitemmas ON viewsobalance.compcode = saleitemmas.compcode AND viewsobalance.itemcode = saleitemmas.itemcode`
 * **suppmas**: `JOIN suppmas ON viewsobalance.compcode = suppmas.compcode AND viewsobalance.custcode = suppmas.suppcode` (Used if customers are stored in the supplier master table).
 
 ### 5. Metadata Joins
 | Metadata Table | JOIN ON | When to Use | Columns Added |
 | :--- | :--- | :--- | :--- |
 | `itemmas` | `compcode, itemcode` | To get item descriptions or categories. | `itemname`, `category`, `uom` |
 | `invhdr` | `compcode, invno` | To check if invoice is cancelled or get IRN. | `cancel_flag`, `irn_no`, `party_name` |
 | `pohdr` | `compcode, pono` | To get PO-level approval status or terms. | `po_status`, `payment_terms` |
 | `itemcustrate`| `compcode, custcode, itemcode` | To validate if `porate` matches master rates. | `rate_contract_no`, `valid_to` |
 
 ### 6. Derived Fields & Transformations
 * **Price Variance**: `InvRate - porate`. Positive indicates upselling/price hikes; negative indicates discounts/errors.
 * **Invoice Extension**: `Invqty * InvRate` (Actual billing value).
 * **PO Value Extension**: `povalue` (Fulfillment at PO price).
 
 ### 7. Query Patterns & Performance Notes
 * **Fulfillment Tracking**: Filter by `pono` and `itemcode` to calculate total `Invqty` vs `itemqty`.
 * **Aging**: Filter by `podate` vs `invdate` to measure lead time from order to billing.
 * **Audit**: Filter where `InvRate != porate` to find billing discrepancies.
 * **Indexing**: Queries should always include `compcode` and `custcode` for partition pruning.
 
 ### 8. Example Queries
 
 -- 1. Calculate total fulfillment percentage for a specific PO
 SELECT 
  pono, 
  itemcode, 
  MAX(itemqty) as OrderedQty, 
  SUM(Invqty) as TotalInvoicedQty,
  (SUM(Invqty) / NULLIF(MAX(itemqty), 0)) * 100 as FulfillmentPct
 FROM viewsobalance
 WHERE compcode = 'DAS' AND pono = 'HEIL1763201301'
 GROUP BY pono, itemcode;
 
 -- 2. Identify price variances between Order and Invoice
 SELECT 
  invno, 
  invdate, 
  itemcode, 
  porate, 
  InvRate, 
  (InvRate - porate) as PriceDiff
 FROM viewsobalance
 WHERE InvRate <> porate 
  AND compcode = 'DAS';
 
 -- 3. Monthly Sales Value based on PO Rates (Standardized Revenue)
 SELECT 
  FORMAT(invdate, 'yyyy-MM') as SalesMonth, 
  SUM(povalue) as RevenueAtPORate
 FROM viewsobalance
 WHERE compcode = 'DAS'
 GROUP BY FORMAT(invdate, 'yyyy-MM');
 
 ### SUMMARY_START ###
 {"one_line_description": "A denormalized view linking Customer Purchase Orders to Invoice line items for fulfillment and price variance auditing.", "grain": "Company, PO, Item, and Invoice Number.", "potential_joins": "itemmas, pohdr, invhdr, saleitemmas, itemcustrate", "key_columns": "compcode, custcode, pono, itemcode, invno, Invqty, porate"}
 ### SUMMARY_END ###

-- Find specific items and quantities for customer 'HEMA(GUR) ' that are not locked
 SELECT
  vb.pono,
  vb.podate,
  vb.itemcode,
  vb.partno,
  vb.Invqty,
  vb.InvRate
 FROM viewsobalance AS vb
 WHERE vb.custcode = 'HEMA(GUR) '
  AND vb.lock = 'N';
 
 -- Aggregate total invoiced quantity and value by item for all locked records in 2015
 SELECT
  vb.itemcode,
  vb.partno,
  COUNT(vb.invno) AS total_invoices,
  SUM(vb.Invqty) AS total_inv_qty,
  SUM(vb.povalue) AS total_po_value
 FROM viewsobalance AS vb
 WHERE vb.lock = 'Y'
  AND vb.invdate BETWEEN '2015-01-01' AND '2015-12-31'
 GROUP BY
  vb.itemcode,
  vb.partno;
 
 -- Join with item master to identify item names and tax rates for specific item 'AAW '
 SELECT
  vb.invno,
  vb.invdate,
  vb.itemcode,
  im.itemname,
  im.uom,
  im.taxrate,
  vb.Invqty,
  vb.InvRate
 FROM viewsobalance AS vb
 INNER JOIN itemmas AS im
  ON vb.compcode = im.compcode
  AND vb.itemcode = im.itemcode
 WHERE vb.itemcode = 'AAW '
  AND im.taxrate = 18.0;
 
 -- Combined join with sales item master and purchase order header to check pricing tier and PO metadata
 SELECT
  vb.compcode,
  vb.pono,
  ph.POdate,
  vb.itemcode,
  sim.salesname,
  sim.hsn,
  vb.porate AS balance_po_rate,
  sim.sale_rate AS master_sale_rate,
  vb.Invqty
 FROM viewsobalance AS vb
 INNER JOIN saleitemmas AS sim
  ON vb.compcode = sim.compcode
  AND vb.itemcode = sim.itemcode
 INNER JOIN pohdr AS ph
  ON vb.compcode = ph.compcode
  AND vb.pono = ph.PONO
 WHERE vb.compcode = 'DAS'
  AND sim.hsn = '87141090 '
  AND ph.locked = 'Y';