viewInvHDRDTL

### 1. Overview
 `viewInvHDRDTL` is a denormalized view combining invoice header and line-item detail data. It provides a flattened perspective of customer billing, linking specific items and quantities to their respective invoice numbers and purchase orders.
 
 ### 2. Grain & Lifecycle
 The grain of the table is **one row per invoice line item** (`compcode`, `invno`, `invtype`, `itemcode`). Records are generated upon invoice finalization and typically remain static unless the source invoice is modified or cancelled.
 
 ### 3. Column Dictionary
 | Column | Type | Nullable | Meaning | Allowed Values | SQL Usage |
 | :--- | :--- | :--- | :--- | :--- | :--- |
 | index | integer | NO | Unique row identifier | | Primary Key |
 | compcode | string | NO | Company identifier | e.g., 'DAS' | Join Key |
 | invtype | integer | NO | Invoice category ID | | Join Key (to `invtype`) |
 | invno | integer | NO | Invoice document number | | Join Key (to `invhdr`) |
 | invdate | datetime | NO | Date of invoice issuance | | Temporal Filtering |
 | custcode | string | NO | Customer identifier | | Join Key |
 | pono | string | YES | Customer Purchase Order number | | Join Key (to `pohdr`) |
 | itemcode | string | NO | Material or Service identifier | | Join Key (to `itemmas`) |
 | itemqty | number | NO | Billed quantity | | Aggregation |
 | itemrate | number | NO | Unit price per item | | Calculation |
 
 ### 4. Relationships & Join Map
 #### Confirmed Joins
 - **itemmas**: `JOIN itemmas ON viewInvHDRDTL.compcode = itemmas.compcode AND viewInvHDRDTL.itemcode = itemmas.itemcode`
 - **invtype**: `JOIN invtype ON viewInvHDRDTL.compcode = invtype.compcode AND viewInvHDRDTL.invtype = invtype.invtype`
 - **invhdr**: `JOIN invhdr ON viewInvHDRDTL.compcode = invhdr.compcode AND viewInvHDRDTL.invno = invhdr.invno AND viewInvHDRDTL.invtype = invhdr.invtype` (Note: `invyear` often required in `invhdr`).
 - **saleitemmas**: `JOIN saleitemmas ON viewInvHDRDTL.compcode = saleitemmas.compcode AND viewInvHDRDTL.itemcode = saleitemmas.itemcode`
 
 #### Likely Joins
 - **pohdr**: `JOIN pohdr ON viewInvHDRDTL.compcode = pohdr.compcode AND viewInvHDRDTL.pono = pohdr.pono`
 - **itemcustrate**: `JOIN itemcustrate ON viewInvHDRDTL.compcode = itemcustrate.compcode AND viewInvHDRDTL.custcode = itemcustrate.custcode AND viewInvHDRDTL.itemcode = itemcustrate.itemcode`
 
 ### 5. Metadata Joins
 | Metadata Table | JOIN ON | When to Use | Columns Added |
 | :--- | :--- | :--- | :--- |
 | `itemmas` | `compcode`, `itemcode` | Retrieve item names and categories. | `itemname`, `category`, `uom` |
 | `invtype` | `compcode`, `invtype` | Convert numeric type to readable label. | `invtype_desc` |
 | `invhdr` | `compcode`, `invno`, `invtype` | Check cancellation status or IRN details. | `cancelflag`, `irn_no` |
 | `itemcustrate`| `compcode`, `custcode`, `itemcode` | Compare actual rate vs. negotiated rate. | `rate_override` |
 | `statemas` | `compcode`, `statecode` | (Likely via `custmas`) For GST compliance. | `statename`, `gst_state_code` |
 
 ### 6. Derived Fields & Transformations
 - **LineTotal**: Calculated as `itemqty * itemrate`.
 - **InvYear**: Derived from `YEAR(invdate)` for joins with tables requiring annual partitioning.
 
 ### 7. Query Patterns & Performance Notes
 - **Filtering**: Always filter by `compcode` and `invdate` range to optimize index usage.
 - **Aggregation**: Group by `invno` and `invdate` to reconstruct header-level totals.
 - **Data Quality**: `pono` may contain filler characters (e.g., dashes or spaces) depending on the source system; use `TRIM()` when joining.
 
 ### 8. Example Queries
 
 ```sql
 -- 1. Total Sales Value by Customer for a specific period
 SELECT 
  custcode, 
  SUM(itemqty * itemrate) AS TotalInvoiceValue
 FROM viewInvHDRDTL
 WHERE compcode = 'DAS' 
  AND invdate BETWEEN '2013-08-01' AND '2013-08-31'
 GROUP BY custcode;
 
 -- 2. Item-wise sales quantity with Master Item Name
 SELECT 
  v.itemcode, 
  i.itemname, 
  SUM(v.itemqty) AS TotalQty
 FROM viewInvHDRDTL v
 JOIN itemmas i ON v.compcode = i.compcode AND v.itemcode = i.itemcode
 WHERE v.compcode = 'DAS'
 GROUP BY v.itemcode, i.itemname;
 
 -- 3. Invoices exceeding a specific rate threshold vs master rates
 SELECT 
  v.invno, 
  v.itemcode, 
  v.itemrate AS BilledRate, 
  icr.itemrate AS NegotiatedRate
 FROM viewInvHDRDTL v
 LEFT JOIN itemcustrate icr ON v.compcode = icr.compcode 
  AND v.custcode = icr.custcode 
  AND v.itemcode = icr.itemcode
 WHERE v.itemrate > icr.itemrate;
 ```
 
 ### SUMMARY_START ### {"one_line_description": "Denormalized view of invoice headers and line items for sales and item-level analysis.", "grain": "compcode, invno, invtype, itemcode", "potential_joins": "invhdr, itemmas, invtype, pohdr, itemcustrate", "key_columns": "invno, itemcode, itemqty, itemrate"} ### SUMMARY_END ###

-- Get item-level transaction details for a specific customer to review order history
 SELECT
  v.invno,
  v.invdate,
  v.itemcode,
  v.itemqty,
  v.itemrate,
  (v.itemqty * v.itemrate) AS line_total
 FROM viewInvHDRDTL AS v
 WHERE v.compcode = 'DAS'
  AND v.custcode = 'SKSSL-G ';
 
 -- Retrieve invoice details joined with item master to show human-readable item names and units of measure
 SELECT
  v.invno,
  v.invdate,
  v.itemcode,
  im.itemname,
  v.itemqty,
  im.uom,
  v.itemrate
 FROM viewInvHDRDTL AS v
 INNER JOIN itemmas AS im
  ON v.compcode = im.compcode
  AND v.itemcode = im.itemcode
 WHERE v.compcode = 'DAS'
  AND v.itemcode = 'CP-151 ';
 
 -- Aggregate total quantity and value by customer for a specific period, filtering for non-canceled invoices
 SELECT
  v.custcode,
  COUNT(DISTINCT v.invno) AS total_invoices,
  SUM(v.itemqty) AS total_qty,
  SUM(v.itemqty * v.itemrate) AS total_sales_value
 FROM viewInvHDRDTL AS v
 INNER JOIN invhdr AS h
  ON v.compcode = h.compcode
  AND v.invno = h.invno
  AND v.invtype = h.invtype
 WHERE v.compcode = 'DAS'
  AND h.cancel_flag = 'N'
  AND v.invdate BETWEEN '2013-08-01' AND '2013-08-31'
 GROUP BY v.custcode;
 
 -- Complex join to identify specific sale-type items, their master descriptions, and purchase order context
 SELECT
  v.invno,
  v.invdate,
  it.invtypename,
  v.itemcode,
  im.itemname,
  v.pono,
  ph.POdate,
  v.itemqty
 FROM viewInvHDRDTL AS v
 INNER JOIN invtype AS it
  ON v.compcode = it.compcode
  AND v.invtype = it.invtype
 INNER JOIN itemmas AS im
  ON v.compcode = im.compcode
  AND v.itemcode = im.itemcode
 LEFT JOIN pohdr AS ph
  ON v.compcode = ph.compcode
  AND v.pono = ph.PONO
 WHERE it.invtypename = 'SALE '
  AND v.custcode = 'HML-G ';