# Sales Report - Table Structures

## Overview

Dokumen ini berisi struktur dari 3 tabel utama yang menjadi sumber data report sales:

| # | Tabel | Sumber Data | Keterangan |
|---|---|---|---|
| 1 | `trs_header_data` | SAP `SALESDOCUMENT` (SO header) | Header dokumen penjualan (Sales Order), relasi ke `trs_so_document` |
| 2 | `rpt_ET_ZVBILLING` | SAP `ZVBILLING` | Data billing/invoice per item dari SAP |
| 3 | `rpt_et_getonhand` | SAP `ZFM_RF_GET_ONHAND` | Data sales on hand (schedule line delivery order) |

Struktur diambil langsung dari database produksi (read-only) via MCP, bukan dari migration/seeder.

---

## 1. Tabel `trs_header_data`

Tabel header **Sales Order** yang menyimpan data dokumen penjualan dari SAP. Setiap baris terhubung ke satu `trs_so_document` (soft delete dihapus otomatis via `ON DELETE CASCADE`). Mayoritas kolom disimpan sebagai `varchar(255)` karena merupakan hasil serialisasi field SAP.

### DDL

```sql
CREATE TABLE `trs_header_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `trs_so_document_id` bigint(20) unsigned NOT NULL,
  `MANDT` varchar(255) DEFAULT NULL,
  `SALESDOCUMENT` varchar(255) DEFAULT NULL,
  `SALESDOCTYPE` varchar(255) DEFAULT NULL,
  `DOCUMENTDATE` varchar(255) DEFAULT NULL,
  `REQUESTEDDELIVDATE` varchar(255) DEFAULT NULL,
  `CURRENCY` varchar(255) DEFAULT NULL,
  `PRICINGNUMBER` varchar(255) DEFAULT NULL,
  `SOLDTO` varchar(255) DEFAULT NULL,
  `CUSTOMERGROUP` varchar(255) DEFAULT NULL,
  `CUSTOMERPO` varchar(255) DEFAULT NULL,
  `CUSTOMERPODATE` varchar(255) DEFAULT NULL,
  `PAYMENTTERM` varchar(255) DEFAULT NULL,
  `PAYMENTTERMTEXT` varchar(255) DEFAULT NULL,
  `INCOTERM1` varchar(255) DEFAULT NULL,
  `INCOTERM2LOCATION` varchar(255) DEFAULT NULL,
  `OURREFERENCE` varchar(255) DEFAULT NULL,
  `LASTCHANGEDBY` varchar(255) DEFAULT NULL,
  `LASTCHANGEDATE` varchar(255) DEFAULT NULL,
  `CREATEDBY` varchar(255) DEFAULT NULL,
  `CREATEDDATE` date DEFAULT NULL,
  `CONSIGNEETITLE` varchar(255) DEFAULT NULL,
  `CONSIGNEENAME1` varchar(255) DEFAULT NULL,
  `CONSIGNEENAME2` varchar(255) DEFAULT NULL,
  `CONSIGNEESTREET` varchar(255) DEFAULT NULL,
  `CONSIGNEESTRSUPPL1` varchar(255) DEFAULT NULL,
  `CONSIGNEESTRSUPPL2` varchar(255) DEFAULT NULL,
  `CONSIGNEESTRSUPPL3` varchar(255) DEFAULT NULL,
  `CONSIGNEECITY` varchar(255) DEFAULT NULL,
  `CONSIGNEECOUNTRY` varchar(255) DEFAULT NULL,
  `BUYERTITLE` varchar(255) DEFAULT NULL,
  `BUYERNAME1` varchar(255) DEFAULT NULL,
  `BUYERNAME2` varchar(255) DEFAULT NULL,
  `BUYERSTREET` varchar(255) DEFAULT NULL,
  `BUYERSTRSUPPL1` varchar(255) DEFAULT NULL,
  `BUYERSTRSUPPL2` varchar(255) DEFAULT NULL,
  `BUYERSTRSUPPL3` varchar(255) DEFAULT NULL,
  `BUYERCITY` varchar(255) DEFAULT NULL,
  `BUYERCOUNTRY` varchar(255) DEFAULT NULL,
  `ZTERMTXT` varchar(255) DEFAULT NULL,
  `PRICECOND` varchar(255) DEFAULT NULL,
  `LOADPORT` varchar(255) DEFAULT NULL,
  `DESTINATION` varchar(255) DEFAULT NULL,
  `SHIPMENTBY` varchar(255) DEFAULT NULL,
  `PACKING` varchar(255) DEFAULT NULL,
  `SHIPMENTDATE` varchar(255) DEFAULT NULL,
  `ENDUSER` varchar(255) DEFAULT NULL,
  `MWSK1` varchar(255) DEFAULT NULL,
  `VATRATE` varchar(255) DEFAULT NULL,
  `VATRATEUNIT` varchar(255) DEFAULT NULL,
  `VATTEXTT` varchar(255) DEFAULT NULL,
  `REMARK` varchar(255) DEFAULT NULL,
  `TOTALAMOUNT` varchar(255) DEFAULT NULL,
  `DISCOUNTAMT` varchar(255) DEFAULT NULL,
  `TRANSCOSTAMT` varchar(255) DEFAULT NULL,
  `DPPAMOUNT` varchar(255) DEFAULT NULL,
  `NETAMOUNT` varchar(255) DEFAULT NULL,
  `VATAMOUNT` varchar(255) DEFAULT NULL,
  `GRANDTOTAL` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `account_info_PI` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `trs_header_data_trs_so_document_id_foreign` (`trs_so_document_id`),
  CONSTRAINT `trs_header_data_trs_so_document_id_foreign`
    FOREIGN KEY (`trs_so_document_id`) REFERENCES `trs_so_document` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4585 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

### Kolom Kunci

| Kolom | Tipe | Keterangan |
|---|---|---|
| `id` | bigint unsigned | Primary key |
| `trs_so_document_id` | bigint unsigned | FK ke `trs_so_document.id` (ON DELETE CASCADE) |
| `SALESDOCUMENT` | varchar(255) | Nomor dokumen penjualan SAP (dipakai untuk mapping ke `rpt_ET_ZVBILLING.SALESDOC`) |
| `SOLDTO` | varchar(255) | Kode sold-to customer |
| `DOCUMENTDATE` | varchar(255) | Tanggal dokumen |
| `REQUESTEDDELIVDATE` | varchar(255) | Tanggal delivery diminta |
| `CURRENCY` | varchar(255) | Mata uang dokumen |
| `CUSTOMERPO` / `CUSTOMERPODATE` | varchar(255) | Nomor & tanggal PO customer |
| `PAYMENTTERM` / `PAYMENTTERMTEXT` | varchar(255) | Term pembayaran |
| `INCOTERM1` / `INCOTERM2LOCATION` | varchar(255) | Incoterm & lokasi |
| `GRANDTOTAL` / `NETAMOUNT` / `VATAMOUNT` | varchar(255) | Nilai nominal (disimpan sebagai string) |
| `account_info_PI` | text | Info akun (PI) tambahan |

> Catatan: `CREATEDDATE` satu-satunya kolom bertipe `date` asli; kolom tanggal lain disimpan sebagai `varchar(255)` mengikuti format string SAP.

---

## 2. Tabel `rpt_ET_ZVBILLING`

Tabel hasil sync **billing** dari endpoint SAP `ZVBILLING` (lihat `docs/rptsap.md`). Berisi data per item billing/invoice. Memiliki **unique key** `(MANDT, BILLDOC, ITMNO)` sehingga tidak ada duplikasi dokumen-item.

### DDL

```sql
CREATE TABLE `rpt_ET_ZVBILLING` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `MANDT` varchar(3) DEFAULT NULL,
  `BILLDOC` varchar(20) DEFAULT NULL,
  `ITMNO` varchar(10) DEFAULT NULL,
  `ITEMD` text DEFAULT NULL,
  `MATNR` varchar(40) DEFAULT NULL,
  `MATGR` varchar(20) DEFAULT NULL,
  `MARAMATKL` varchar(20) DEFAULT NULL,
  `SLOC` varchar(10) DEFAULT NULL,
  `FISYR` varchar(4) DEFAULT NULL,
  `PERIOD` varchar(3) DEFAULT NULL,
  `BILLDATE` date DEFAULT NULL,
  `BUDAT` date DEFAULT NULL,
  `ZZGSM` decimal(18,2) DEFAULT NULL,
  `ZZWIDTHROLL` decimal(18,2) DEFAULT NULL,
  `ZZLENGTHROLL` decimal(18,2) DEFAULT NULL,
  `BILLQTY` decimal(18,4) DEFAULT NULL,
  `SALESU` varchar(10) DEFAULT NULL,
  `NUM` int(11) DEFAULT NULL,
  `DENOM` int(11) DEFAULT NULL,
  `UNITOM` varchar(10) DEFAULT NULL,
  `FKLMG` decimal(18,4) DEFAULT NULL,
  `ACTWEIGHT` decimal(18,4) DEFAULT NULL,
  `LMENG` decimal(18,4) DEFAULT NULL,
  `GROSSWT` decimal(18,4) DEFAULT NULL,
  `UNIT` varchar(10) DEFAULT NULL,
  `NETPR` decimal(18,4) DEFAULT NULL,
  `PER` int(11) DEFAULT NULL,
  `MEAS` varchar(10) DEFAULT NULL,
  `NUMCONV` int(11) DEFAULT NULL,
  `DENOMCONV` int(11) DEFAULT NULL,
  `UNITCONV` varchar(10) DEFAULT NULL,
  `NETPRKG` decimal(18,4) DEFAULT NULL,
  `WAERK` varchar(5) DEFAULT NULL,
  `MEINS` varchar(10) DEFAULT NULL,
  `NETVAL` decimal(18,2) DEFAULT NULL,
  `TAX` decimal(18,2) DEFAULT NULL,
  `TOTVAL` decimal(18,2) DEFAULT NULL,
  `CRCY` varchar(5) DEFAULT NULL,
  `PAYER` varchar(20) DEFAULT NULL,
  `PAYERD` text DEFAULT NULL,
  `SOLDTO` varchar(20) DEFAULT NULL,
  `SOLDTOD` text DEFAULT NULL,
  `CITY` varchar(100) DEFAULT NULL,
  `INCO1` varchar(10) DEFAULT NULL,
  `INCO2` varchar(100) DEFAULT NULL,
  `SHIPPOINT` varchar(10) DEFAULT NULL,
  `ORG` varchar(10) DEFAULT NULL,
  `CNCL` varchar(5) DEFAULT NULL,
  `BILLTYPE` varchar(10) DEFAULT NULL,
  `BILLCAT` varchar(10) DEFAULT NULL,
  `DOCCAT` varchar(10) DEFAULT NULL,
  `DISTCH` varchar(10) DEFAULT NULL,
  `DISTCHD` varchar(100) DEFAULT NULL,
  `DIVISION` varchar(10) DEFAULT NULL,
  `PPROC` varchar(20) DEFAULT NULL,
  `CUSTGRP` varchar(20) DEFAULT NULL,
  `CUSTGRPD` varchar(100) DEFAULT NULL,
  `BEZEI` varchar(100) DEFAULT NULL,
  `STATUS` varchar(20) DEFAULT NULL,
  `INVLISTTYP` varchar(20) DEFAULT NULL,
  `ORCOUNTRYTX` varchar(5) DEFAULT NULL,
  `ORTXNO` varchar(50) DEFAULT NULL,
  `DSCOUNTRYTX` varchar(5) DEFAULT NULL,
  `JOURVALUSD` decimal(18,2) DEFAULT NULL,
  `CRCYUSD` varchar(5) DEFAULT NULL,
  `JOURVALIDR` decimal(18,2) DEFAULT NULL,
  `CRCYIDR` varchar(5) DEFAULT NULL,
  `ACTJOURVALUSD` decimal(18,2) DEFAULT NULL,
  `ACTCRCYUSD` varchar(5) DEFAULT NULL,
  `ACTJOURVALIDR` decimal(18,2) DEFAULT NULL,
  `ACTCRCYIDR` varchar(5) DEFAULT NULL,
  `DOCNO` varchar(30) DEFAULT NULL,
  `RFDOC` varchar(30) DEFAULT NULL,
  `SALESDOC` varchar(30) DEFAULT NULL,
  `SALESDOCITEM` varchar(10) DEFAULT NULL,
  `CRTDBY` varchar(30) DEFAULT NULL,
  `TIME` time DEFAULT NULL,
  `CRTDON` date DEFAULT NULL,
  `SLSMAN` varchar(50) DEFAULT NULL,
  `VMSL` varchar(50) DEFAULT NULL,
  `LAND1` varchar(5) DEFAULT NULL,
  `LANDX` varchar(100) DEFAULT NULL,
  `REGIO` varchar(20) DEFAULT NULL,
  `BEZEI2` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rpt_et_zvbilling_uniq` (`MANDT`,`BILLDOC`,`ITMNO`),
  KEY `rpt_et_zvbilling_billdoc_itmno_idx` (`BILLDOC`,`ITMNO`),
  KEY `rpt_et_zvbilling_fisyr_period_idx` (`FISYR`,`PERIOD`)
) ENGINE=InnoDB AUTO_INCREMENT=1114978 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

### Kolom Kunci

| Kolom | Tipe | Keterangan |
|---|---|---|
| `MANDT` | varchar(3) | Client SAP |
| `BILLDOC` | varchar(20) | Nomor billing/invoice |
| `ITMNO` | varchar(10) | Nomor item billing |
| `MATNR` | varchar(40) | Nomor material |
| `FISYR` / `PERIOD` | varchar(4) / varchar(3) | Tahun & periode fiskal (untuk filtering tahunan) |
| `BILLDATE` / `BUDAT` | date | Tanggal billing / posting |
| `BILLQTY` | decimal(18,4) | Quantity billing |
| `ZZGSM` / `ZZWIDTHROLL` / `ZZLENGTHROLL` | decimal(18,2) | Field khusus (GSM / lebar / panjang roll) |
| `NETPR` / `NETVAL` / `TAX` / `TOTVAL` | decimal | Nilai harga & total |
| `SOLDTO` / `SOLDTOD` | varchar(20) / text | Sold-to customer + deskripsi |
| `SALESDOC` / `SALESDOCITEM` | varchar(30) / varchar(10) | Referensi Sales Order (mapping ke `trs_header_data.SALESDOCUMENT`) |
| `JOURVALIDR` / `JOURVALUSD` | decimal(18,2) | Nilai journal dalam IDR / USD |
| `SLSMAN` / `VMSL` | varchar(50) | Salesman / sales group |
| `STATUS` / `CNCL` | varchar | Status billing / indikator batal |

### Index

- `PRIMARY` — `id`
- `UNIQUE rpt_et_zvbilling_uniq` — `(MANDT, BILLDOC, ITMNO)` mencegah duplikat dokumen-item
- `rpt_et_zvbilling_billdoc_itmno_idx` — `(BILLDOC, ITMNO)` untuk lookup per dokumen
- `rpt_et_zvbilling_fisyr_period_idx` — `(FISYR, PERIOD)` untuk filter tahun-periode

---

## 3. Tabel `rpt_et_getonhand`

Tabel hasil sync **sales on hand** dari endpoint SAP `ZFM_RF_GET_ONHAND` (lihat `docs/rptptr_onhand.md`). Berisi schedule line delivery order yang belum terkirim. Dipakai untuk mengurangi Sales Actual di Salesperson Dashboard (`docs/sales_dashboard.md`).

### DDL

```sql
CREATE TABLE `rpt_et_getonhand` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `MANDT` varchar(3) DEFAULT NULL COMMENT 'Client',
  `DOCUMENTNUMBER` varchar(20) DEFAULT NULL COMMENT 'Document Number',
  `CUSTOMERCODE` varchar(20) DEFAULT NULL COMMENT 'Customer Code',
  `DELIVERYDATE` date DEFAULT NULL COMMENT 'Delivery Date',
  `QUANTITY` decimal(20,3) DEFAULT NULL COMMENT 'Quantity',
  `REFERENCE_DOCUMENT` varchar(20) DEFAULT NULL,
  `FISCALYEAR` varchar(4) DEFAULT NULL COMMENT 'Fiscal Year',
  `FISCALMONTH` varchar(6) DEFAULT NULL COMMENT 'Fiscal Month YYYYMM from API filter',
  `FISYR` varchar(4) DEFAULT NULL COMMENT 'Fiscal Year of Sync',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_onhand_custcode` (`CUSTOMERCODE`),
  KEY `idx_onhand_fiscalmonth` (`FISCALMONTH`),
  KEY `idx_onhand_fisyr` (`FISYR`)
) ENGINE=InnoDB AUTO_INCREMENT=94754 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

### Kolom Kunci

| Kolom | Tipe | Keterangan |
|---|---|---|
| `MANDT` | varchar(3) | Client SAP |
| `DOCUMENTNUMBER` | varchar(20) | Nomor dokumen (delivery order) — dipakai untuk mapping ke Sales Actual |
| `CUSTOMERCODE` | varchar(20) | Kode customer |
| `DELIVERYDATE` | date | Tanggal delivery |
| `QUANTITY` | decimal(20,3) | Quantity |
| `REFERENCE_DOCUMENT` | varchar(20) | Dokumen referensi |
| `FISCALYEAR` | varchar(4) | Tahun fiskal |
| `FISCALMONTH` | varchar(6) | Bulan fiskal YYYYMM (dari filter API) |
| `FISYR` | varchar(4) | Tahun fiskal saat sync (untuk simple-replace per tahun) |

### Index

- `PRIMARY` — `id`
- `idx_onhand_custcode` — `(CUSTOMERCODE)`
- `idx_onhand_fiscalmonth` — `(FISCALMONTH)`
- `idx_onhand_fisyr` — `(FISYR)`

---

## Relasi Antar Tabel

```
rpt_ET_ZVBILLING.SALESDOC ──────────────► trs_header_data.SALESDOCUMENT
rpt_et_getonhand.DOCUMENTNUMBER ────────► (mapping ke SALESDOC/SALESDOCUMENT)
trs_header_data.trs_so_document_id ─────► trs_so_document.id (FK CASCADE)
```

Mapping antar dokumen bersifat **logical (string matching)** antara `SALESDOC` (billing) dan `SALESDOCUMENT` (header SO), sedangkan on-hand dimapping lewat `DOCUMENTNUMBER`. Penggunaan lengkap lihat `docs/sales_dashboard.md`.

## Sumber

Struktur diambil langsung dari database produksi via MySQL MCP (read-only) dengan `SHOW CREATE TABLE`, tanggal 2026-08-05.