despadvicedtl

# despadvicedtl
 
 ### 1) Overview
 The `despadvicedtl` table serves as the line-item repository for Dispatch Advice notes. It records specific items, quantities, part numbers, and packaging details associated with a dispatch request before the final invoice is generated. It acts as a bridge between production/order management and the commercial shipping process, often used to calculate pending advice balances against actual sales.
 
 ### 2) Column Dictionary
 | Column | Type | Nullable | Meaning | Allowed Values | SQL Usage |
 | :--- | :--- | :--- | :--- | :--- | :--- |
 | **index** | integer | No | Primary key for record identification | | PK |
 | **compcode** | string | No | Unique identifier for the company | e.g., "DAS" | FK |
 | **adviceno** | integer | No | Link to the header Dispatch Advice number | | FK |
 | **custcode** | string | Yes | Unique identifier for the customer | | FK |
 | **itemcode** | string | No | Internal identifier for the product | | FK |
 | **partno** | string | Yes | Customer or manufacturer part number | | Data |
 | **lotno** | string | Yes | Manufacturing lot or batch number | | Data |
 | **adviceqty** | number | No | Total quantity planned for dispatch | | Calculation |
 | **uom** | string | Yes | Unit of Measure | PCS, KGS | Data |
 | **desptype** | string | Yes | Classification of dispatch | | Data |
 | **modepack** | string | Yes | Packaging configuration | | Data |
 | **packqty** | integer | Yes | Number of packages/boxes | | Data |
 | **srno** | integer | No | Sequence number for items within the advice | | Data |
 | **qtyperbox** | string | Yes | Standard quantity contained in one box | | Data |
 | **pono** | string | Yes | Customer Purchase Order number | | FK |
 | **location** | string | Yes | Physical storage or warehouse location | | FK |
 | **PDIRNo** | string | Yes | Pre-Dispatch Inspection Report Number | | Data |
 | **HeatNo** | string | Yes | Material Heat Number (Traceability) | | Data |
 | **Remarks** | string | Yes | Item-specific instructions or notes | | Data |
 | **LockItem** | string | Yes | Flag to prevent modifications to the item | 'Y', 'N' | Filter |
 | **pItemCode** | string | Yes | Parent Item Code (if part of an assembly) | | Likely FK |
 | **packingmode** | string | Yes | Detailed description of packing requirements | | Data |
 
 ### 3) Relationships & Join Map
 
 #### Parent Tables (Strict Joins)
 * **despadvicehdr**: Joined via `adviceno` and `compcode`. This provides advice dates and shipping headers.
 * **saleitemmas**: Joined via `itemcode` and `compcode`. Used to retrieve `salesname` and master part details.
 * **cust**: Joined via `custcode` and `compcode`. Provides customer master information.
 
 #### Transactional Relationships
 * **invdtl**: Historically linked via `adviceno`, `itemcode`, and `compcode` (as seen in `PendingAdvice` logic) to reconcile advised quantities against actual invoiced quantities.
 * **pdiinspectiondtl**: (Likely) Linked via `PDIRNo` for quality clearance verification.
 * **itemmas**: (Likely) Linked via `itemcode` for physical item properties.
 
 #### Join Logic Recommendation
 ```sql
 SELECT 
  d.adviceno, 
  d.itemcode, 
  d.adviceqty, 
  h.advicedate 
 FROM despadvicedtl d
 INNER JOIN despadvicehdr h ON d.adviceno = h.adviceno AND d.compcode = h.compcode
 INNER JOIN saleitemmas s ON d.itemcode = s.itemcode AND d.compcode = s.compcode
 ```