# Plan: Sandvik SAP cXML Integration

## Overview

cXML document exchange between Sandvik (SAP Ariba) and Tekpal (NC). Existing `order-sales-xml-convert.json` handles OrderRequest in/out. New convert files needed for Confirmation, ShipNotice, Invoice.

## DTD References

| DTD | File | Key elements |
|-----|------|-------------|
| Fulfill.dtd | `/Volumes/nc/work/tekpal/Sandvik-SAP-edi/dtd/Fulfill.dtd` | ConfirmationRequest (line 6194), ShipNoticeRequest (line 6724) |
| InvoiceDetail.dtd | `/Volumes/nc/work/tekpal/Sandvik-SAP-edi/dtd/InvoiceDetail.dtd` | InvoiceDetailRequest (line 6987) |
| cXML.dtd | `/Volumes/nc/work/tekpal/Sandvik-SAP-edi/dtd/cXML.dtd` | Envelope, Header, Request, Response |

## Existing

| File | Purpose |
|------|---------|
| `save/order-sales-xml-convert.json` | OrderRequest IN/OUT |
| `save/order-sales.json` | Order save config |
| `save/order-sales-import.json` | Order import config |
| `save/order-sales-xml-import.json` | XML import config |
| `save/order-sales-xml-export.json` | XML export config |
| `column/order-sales.json` | Order grid columns |
| `column/order_row-sales.json` | Order row grid columns |

## Table fields (from table/local/order/order.json and order_row.json)

### order fields used

| Field | Type | Status |
|-------|------|--------|
| order_id | varchar(40) | exists |
| company_id | varchar(40) | exists |
| company_reference_number | varchar(80) | exists |
| confirmation_date | date | exists |
| currency_id | varchar(20) | exists |
| due_date | date | exists |
| exchange_rate | double | exists |
| info | text | exists |
| order_date | date | exists |
| order_state | varchar(20) | exists |
| our_reference | varchar(40) | exists |
| price_with_vat | double | exists |
| price_without_vat | double | exists |
| term_of_delivery | varchar(120) | exists |
| terms_of_payment_id | varchar(40) | exists |
| vat_price | double | exists |
| way_of_delivery | varchar(120) | exists |
| your_reference | varchar(40) | exists |
| json_data.mark | varchar | exists |
| json_data.shipping_date | date | exists |
| json_data.bank_reference_number | varchar(160) | exists |
| json_data.cash_discount_due_date | date | exists |
| json_data.penalty_interest_percent | double | exists |
| json_data.waybill_id | varchar(40) | exists |
| json_data.confirm_id | varchar | **ADD** |
| json_data.confirm_type | varchar | **ADD** |
| json_data.shipment_id | varchar | **ADD** |
| json_data.carrier_code | varchar | **ADD** |
| json_data.tracking_number | varchar | **ADD** |

### order_row fields used

| Field | Type | Status |
|-------|------|--------|
| order_id | varchar(40) | exists |
| product_id | varchar(80) | exists |
| delivery_amount | double | exists |
| delivery_date | date | exists |
| description | text | exists |
| order_amount | double | exists |
| row_number | integer | exists |
| unit | varchar(20) | exists |
| unit_price | double | exists |
| vat_percent | double | exists |
| vat_price | double | exists |
| price_without_vat | double | exists |
| price_with_vat | double | exists |
| dispatch_note_id | varchar(40) | exists |
| json_data.customer_row_number | varchar(20) | exists |
| json_data.supplier_product_code | varchar(100) | exists |

## New Convert Files

All in `save/`. Same file handles IN and OUT.

| # | File | cXML Type | Direction | Purpose |
|---|------|-----------|-----------|---------|
| 1 | `confirmation-sales-xml-convert.json` | ConfirmationRequest | OUT | Supplier confirms/rejects order lines to buyer |
| 2 | `ship-notice-sales-xml-convert.json` | ShipNoticeRequest | OUT | Supplier sends ASN to buyer |
| 3 | `invoice-sales-xml-convert.json` | InvoiceDetailRequest | OUT | Supplier sends invoice to buyer |

## 1. confirmation-sales-xml-convert.json

### cXML structure

```text
cXML
  Header (From/To/Sender — same as order)
  Request
    ConfirmationRequest
      ConfirmationHeader
        @noticeDate = ord.order_date
        @confirmID = ord.json_data.confirm_id
        @type = ord.json_data.confirm_type (accept|reject|detail|except|backordered|replace)
        @operation = new
      OrderReference
        @orderID = ord.company_reference_number
      ConfirmationItem (field=ordr)
        @lineNumber = ordr.json_data.customer_row_number
        @quantity = ordr.order_amount
        ConfirmationStatus
          @quantity = ordr.order_amount
          @type = ord.json_data.confirm_type
          @shipmentDate = ordr.delivery_date
          UnitPrice
            Money = ordr.unit_price @currency = ord.currency_id
          UnitOfMeasure = EA
```

### Field mapping

| cXML path | Internal field | Table exists |
|-----------|---------------|--------------|
| ConfirmationHeader/@noticeDate | ord.order_date | yes |
| ConfirmationHeader/@confirmID | ord.json_data.confirm_id | **ADD** |
| ConfirmationHeader/@type | ord.json_data.confirm_type | **ADD** |
| OrderReference/@orderID | ord.company_reference_number | yes |
| ConfirmationItem/@lineNumber | ordr.json_data.customer_row_number | yes |
| ConfirmationItem/@quantity | ordr.order_amount | yes |
| ConfirmationStatus/@quantity | ordr.order_amount | yes |
| ConfirmationStatus/@shipmentDate | ordr.delivery_date | yes |
| ConfirmationStatus/UnitPrice/Money | ordr.unit_price | yes |
| ConfirmationStatus/UnitPrice/Money/@currency | ord.currency_id | yes |

## 2. ship-notice-sales-xml-convert.json

### cXML structure

```text
cXML
  Header (From/To/Sender — same as order)
  Request
    ShipNoticeRequest
      ShipNoticeHeader
        @shipmentID = ord.json_data.shipment_id
        @noticeDate = ord.order_date
        @shipmentDate = ord.json_data.shipping_date
      ShipControl
        CarrierIdentifier @domain=SCAC = ord.json_data.carrier_code
        ShipmentIdentifier = ord.json_data.tracking_number
      ShipNoticePortion
        OrderReference
          @orderID = ord.company_reference_number
        ShipNoticeItem (field=ordr)
          @quantity = ordr.delivery_amount
          @lineNumber = ordr.json_data.customer_row_number
          ItemID
            SupplierPartID = ordr.product_id
          UnitOfMeasure = EA
```

### Field mapping

| cXML path | Internal field | Table exists |
|-----------|---------------|--------------|
| ShipNoticeHeader/@shipmentID | ord.json_data.shipment_id | **ADD** |
| ShipNoticeHeader/@noticeDate | ord.order_date | yes |
| ShipNoticeHeader/@shipmentDate | ord.json_data.shipping_date | yes |
| CarrierIdentifier | ord.json_data.carrier_code | **ADD** |
| ShipmentIdentifier | ord.json_data.tracking_number | **ADD** |
| OrderReference/@orderID | ord.company_reference_number | yes |
| ShipNoticeItem/@quantity | ordr.delivery_amount | yes |
| ShipNoticeItem/@lineNumber | ordr.json_data.customer_row_number | yes |
| SupplierPartID | ordr.product_id | yes |

## 3. invoice-sales-xml-convert.json

### cXML structure

```text
cXML
  Header (From/To/Sender — same as order)
  Request
    InvoiceDetailRequest
      InvoiceDetailRequestHeader
        @purpose = standard (standard|creditMemo|debitMemo)
        @operation = new
        @invoiceDate = ord.order_date
        @invoiceID = ord.order_id
      InvoiceDetailOrder
        InvoiceDetailOrderInfo
          OrderReference
            @orderID = ord.company_reference_number
        InvoiceDetailItem (field=ordr)
          @quantity = ordr.delivery_amount
          @invoiceLineNumber = ordr.json_data.customer_row_number
          UnitOfMeasure = EA
          UnitPrice
            Money = ordr.unit_price @currency = ord.currency_id
          ItemID
            SupplierPartID = ordr.product_id
          SubtotalAmount
            Money = ordr.price_without_vat @currency = ord.currency_id
      InvoiceDetailSummary
        SubtotalAmount
          Money = ord.price_without_vat @currency = ord.currency_id
        Tax
          Money = ord.vat_price @currency = ord.currency_id
          TaxDetail @category=NN @percentageRate
            TaxAmount
              Money = ord.vat_price @currency = ord.currency_id
        GrossAmount
          Money = ord.price_with_vat @currency = ord.currency_id
        NetAmount
          Money = ord.price_without_vat @currency = ord.currency_id
        DueAmount
          Money = ord.price_with_vat @currency = ord.currency_id
```

### Field mapping

| cXML path | Internal field | Table exists |
|-----------|---------------|--------------|
| InvoiceDetailRequestHeader/@invoiceDate | ord.order_date | yes |
| InvoiceDetailRequestHeader/@invoiceID | ord.order_id | yes |
| OrderReference/@orderID | ord.company_reference_number | yes |
| InvoiceDetailItem/@quantity | ordr.delivery_amount | yes |
| InvoiceDetailItem/@invoiceLineNumber | ordr.json_data.customer_row_number | yes |
| UnitPrice/Money | ordr.unit_price | yes |
| SupplierPartID | ordr.product_id | yes |
| SubtotalAmount/Money | ordr.price_without_vat | yes |
| InvoiceDetailSummary/SubtotalAmount | ord.price_without_vat | yes |
| InvoiceDetailSummary/Tax | ord.vat_price | yes |
| InvoiceDetailSummary/GrossAmount | ord.price_with_vat | yes |
| InvoiceDetailSummary/NetAmount | ord.price_without_vat | yes |
| InvoiceDetailSummary/DueAmount | ord.price_with_vat | yes |

## Changes Required

### table/local/order/order.json — add 5 extra_fields

```json
{ "field_name": "json_data.confirm_id", "field_type": "varchar", "field_length": 80 },
{ "field_name": "json_data.confirm_type", "field_type": "varchar", "field_length": 20 },
{ "field_name": "json_data.shipment_id", "field_type": "varchar", "field_length": 80 },
{ "field_name": "json_data.carrier_code", "field_type": "varchar", "field_length": 40 },
{ "field_name": "json_data.tracking_number", "field_type": "varchar", "field_length": 80 }
```

### New files to create

1. `save/confirmation-sales-xml-convert.json`
2. `save/ship-notice-sales-xml-convert.json`
3. `save/invoice-sales-xml-convert.json`

## Document Flow

```mermaid
graph LR
    A[Sandvik Ariba] -->|OrderRequest cXML| B[NC cxml endpoint]
    B -->|save .xml| C[import folder]
    C -->|order-sales-xml-convert| D[NC Sales Order]
    D -->|confirmation-sales-xml-convert| E[ConfirmationRequest cXML]
    D -->|ship-notice-sales-xml-convert| F[ShipNoticeRequest cXML]
    D -->|invoice-sales-xml-convert| G[InvoiceDetailRequest cXML]
    E --> A
    F --> A
    G --> A
```
