# Claude Code task brief — Cegid Y2 → D365 F&O Retail POS export query ## Your role You are a Cegid Retail Y2 (v26) data-extraction specialist. You write **one** SQL query that runs inside the Cegid Y2 report/query engine and produces a pipe-delimited file. A downstream AMOUAGE middleware reads that file and assembles five D365 F&O Retail OData payloads — Header, SalesLines, TaxLines, PaymentLines, DiscountLines — and posts them in that fixed order. **You own the source extraction only:** the correct Cegid columns, signs, aggregation, and references. The middleware recomputes control totals and builds the JSON. ## Authoritative field map The complete, current field mapping lives in **`content.json`** (shipped alongside this brief). Treat it as the source of truth for every column: `d365` (target field), `source` (Cegid column), `transform`, `note`, `kind`. If this brief and `content.json` ever disagree on a mapping, `content.json` wins — except for the two **scope locks** and the **D-line rule** below, which override everything. ## Deliverable A single `.sql` file. **Pure SQL, no comments.** It must run as one statement in the Y2 query engine and return the multi-record-type result set described under *Output contract*. --- ## Hard constraints — the Y2 SQL dialect (non-negotiable) The Y2 engine runs one stored `SELECT`. It does **not** support: - `DECLARE` / variables - temp tables (`#tmp`), table variables - CTEs (`WITH`) - window functions (`ROW_NUMBER`, `OVER`, …) - `CONCAT_WS` or other helpers you might reach for Therefore: - All mapping is done **inline** via `CASE` and via `LEFT JOIN` to real tables (`CHOIXEXT`, `ETABLISS`, `DEPOTS`, `PIEDBASE`, `LIAISONPIECE`, `TIERSCOMPL`, the custom Z-tables). **Never** introduce a lookup/temp table. - All aggregation is done with **correlated subqueries** and `GROUP BY`. - The whole output is **five `SELECT` branches joined by `UNION ALL`**, each branch fully column-aligned, ending with a single `ORDER BY`. ### Column-alignment rule (critical) Every branch must emit the **exact same ordered column list** (~110 columns). The first branch (`H`) fixes the names; the others repeat the same positions and put `''` (empty string) in every column that doesn't apply to that record type. A missing or reordered column anywhere breaks the `UNION ALL`. Column 1 of every row is the record tag. --- ## Source environment (real dossier facts — do not substitute) - POS receipt nature: **`GP_NATUREPIECEG = 'FFO'`**. - Header status gate (apply in **every** branch): `GP_TICKETANNULE = '-'` · `GP_VIVANTE = 'X'` · `GP_SUPPRIME <> 'X'` · `GP_ETATEXPORT <> 'EXP'`. - Merchandise-line filter: `GL_TYPELIGNE = 'ART'` **and** `GL_TYPEARTICLE = 'MAR'`. - Idempotency comes from `GP_ETATEXPORT <> 'EXP'` within the business-date / EOD window; the query is read-only and never writes back to Y2. - Selection window: parameterised on `GP_REFINTERNE` (or the business-date field the caller passes). It is applied identically in all five branches. ### Tables and prefixes `PIECE (GP)` headers · `LIGNE (GL)` lines · `PIEDECHE (GPE)` payments · `LIGNEREMISE (MLR)` discount detail · `PIEDBASE (GPB)` VAT bases/rates · `ETABLISS (ET)` stores · `DEPOTS (GDE)` warehouses · `TIERS (T)` customers · `TIERSCOMPL (YTC)` customer supplement · `CHOIXEXT (YX)` extended code tables · `LIAISONPIECE (GLP)` document links. **Custom tables:** `ZLIGNESQRCODE (ZLQ)` line↔QR, `ZQRCODEITEMS (ZBA)` QR↔resolved batch. ### Mappings (all resolve through data, never hard-coded in the body) - `DATAAREAID ← ET_FILIALE` (Oman = `as`); `OPERATINGUNITNUMBER ← ET_CHARLIBRE1`; `TERMINAL ← GP_ETABLISSEMENT`; `WAREHOUSE ← GDE_CHARLIBRE1` (of the doc/line depot). - Tax group / code via `CHOIXEXT`: `EL4` on `ET_LIBREET4` → header tax-calc type; `EL1` on `ET_LIBREET1` → line sales-tax group; `EL3` on `ET_LIBREET3` → tax code. - Tender: `P_TENDERTYPE ← RIGHT(GPE_MODEPAIE, 1)` (1 Cash · 2 Visa · 3 Amex · 5 Customer Account). **4 = Gift Card is out of scope — see Scope lock 1.** - Identified customer: emit `GP_TIERS` only when `TIERSCOMPL.YTC_TABLELIBRETIERS2` is non-empty; otherwise blank. - Return origin store: resolve through the `LIAISONPIECE` chain back to the original receipt's `ET_CHARLIBRE1` (cross-store aware). - Batch: resolve `ZBA_BATCHNUMBER` via `ZLIGNESQRCODE → ZQRCODEITEMS`. **Export the resolved batch number, never the raw QR string.** The batch must already exist in the D365 batch master (a sync precondition, outside SQL scope). --- ## Output contract — five record types in one file Column 1 is `RECORD_TYPE`. Composite key repeats on every row: `DATAAREAID + TRANSACTIONNUMBER + OPERATINGUNITNUMBER + TERMINAL`. | Tag | Record | Cardinality | Links | |-----|--------|-------------|-------| | `H` | Header | 1 per transaction | — | | `L` | Sales line | 1..N | — | | `T` | Tax line | 1 per `L` line | `T.SALES_LINE_NUMBER → L.LINE_NUMBER` | | `P` | Payment line | 1 per distinct method | sequential within txn | | `D` | Discount line | **0 or 1 per `L` line** | `D.SALES_LINE_NUMBER → L.LINE_NUMBER` | Order the whole result by transaction then record sequence (`H < L < T < P < D`). --- ## Scope lock 1 — gift cards are fully out Exclude the **entire** gift-card treatment, not just payment quirks: - No gift-card issue and no gift-card redeem rows. - No `RetailGiftCards`, `GiftCardTransactionsV2`, `GOC_NUMBON` / `GiftCardId` handling. - Drop any transaction paid by gift card via `'004'`: `'004' NOT IN (SELECT GPE_MODEPAIE …)` in the header/line/tax/discount branches, and `GPE_MODEPAIE <> '004'` in the payment branch. - Carry **no** gift-card-conditional field anywhere in the output. ## Scope lock 2 — one aggregated discount line per sales line `D` is **0 or 1 per `L` line, never 0..N.** Even when Cegid records several discount components on the same item (e.g. a customer-category reduction plus another), emit a **single** `D` row per `L` line that sums the components. This aligns with the existing "one discount origin per transaction" rule and with `MLR_NUMLIGNE` collapsing to one line. ### Final D-line spec When any discount applies to a sales line, emit exactly one `D` row for that line: - `SALES_LINE_NUMBER ← GL_NUMLIGNE`; `LINE_NUMBER ← MLR_NUMLIGNE` (single line) - `DISCOUNTCOST / DISCOUNTAMOUNT / EFFECTIVEAMOUNT ← SUM(MLR_MONTANTHT)` for that sales line, positive absolute - `DISCOUNTORIGINTYPE ← ` map of `MLR_ORGREMISE`: `003 → Manual`, `006 / 007 → Customer` (Manual = manual/global discount; Customer = customer-category family/employee/board). No Periodic / Loyalty. - **`DISCOUNTPERCENTAGE` ← recomputed effective percentage** (confirmed): `ABS(SUM(MLR_MONTANTHT)) / NULLIF(ABS(GL_MONTANTHT), 0) * 100` — i.e. summed discount amount over the line's pre-discount VAT-excl base. In the one-origin regime this equals `MLR_REMISE`; it stays consistent with the summed amount if a second component ever appears. > Toggle: if you are certain only one percentage discount ever applies per line and you > prefer the stored value, substitute `ABS(MLR_REMISE)` (TOP 1). Default is the recompute. - Fixed: `MANUALDISCOUNTTYPE = TotalDiscountPercent`; `CUSTOMERDISCOUNTTYPE = None`; `DEALPRICE = 0.00`. - Group the discount branch by the sales-line key so exactly one row is produced per line with a discount (`SUM` collapses the components). Header consistency: `H.DISCOUNTAMOUNT = ` sum of all D-line `DISCOUNTCOST` across the transaction (equivalently `SUM(ABS(MLR_MONTANTHT))` over the receipt). --- ## Sign conventions (enforce exactly) Cegid stores sale quantities positive and returns negative; D365 expects the opposite on sales lines, so flip uniformly: - **Sale:** `L` qty/net/tax negative; `T` tax positive; `P` amounts positive. - **Return:** mirror — `L` qty/net/tax positive; `T` tax negative; `P` amounts/qty negative. - **Exchange:** one transaction with both a return `L` (+) and a sale `L` (−); header flagged `SALEISRETURNSALE = Yes`; for a clean 1:1 exchange the header/payment totals net to 0. - **Price invariant:** `L_PRICE` (unit price) is **always positive**. A negative unit price is invalid in every case. --- ## Definition of done (acceptance criteria) 1. One statement, pure SQL, no comments, runs in the Y2 engine (no `DECLARE`/temp/CTE/window). 2. Five `UNION ALL` branches, identical ordered column list in each, `''` for non-applicable columns, record tag in column 1, single trailing `ORDER BY`. 3. The status gate, `FFO` nature, ART/MAR filter, and gift-card `'004'` exclusion applied in every branch. 4. Signs correct per record and per sale/return/exchange; `L_PRICE` always positive. 5. Exactly **one `D` row per discounted sales line**, amount = `SUM(MLR_MONTANTHT)`, percentage = recomputed effective % (per above); no multi-row `D` per line. 6. Return references resolved through `LIAISONPIECE`; batch resolved through the Z-tables (resolved number, not the QR). 7. All code crosswalks resolved through joins/`CASE`, none hard-coded in the query body, so a new store/register/warehouse/entity onboards through reference data only. 8. Every field present in `content.json` is emitted with the mapping shown there, subject to the two scope locks and the D-line rule. ## Do NOT - Do not introduce `DECLARE`, temp tables, table variables, CTEs, or window functions. - Do not emit more than one `D` row per sales line. - Do not carry any gift-card field, table, or conditional. - Do not export the raw QR string in `L_IVENTBATCHID`. - Do not compute the middleware's control totals as anything other than raw supporting values; if you emit aggregates they must reconcile within rounding tolerance. Cegid Y2 → D365 F&O — SQL Sales Export Query Specification Scope: all sale types except gift card. Definitive, field-level. Role & objective You generate a SQL query against the Cegid Y2 database. Its output is a pipe-delimited multi-record file consumed by the AMOUAGE middleware, which assembles five D365 F&O Retail OData payloads (Header, SalesLines, TaxLines, PaymentLines, DiscountLines) and posts them in that fixed order. Your responsibility is the source extraction only: emit the raw H/S/T/P/D rows with correct source values, signs, consolidation, discount aggregation, and return references. The middleware recomputes control totals and builds the JSON. Anything wrong in the source values cannot be fixed downstream. 1. Output structure — one file, five record types Not one wide row per transaction. RECORD_TYPE is column 1 of every row: Type Meaning Cardinality H Header exactly 1 per transaction S Sales line 1..N T Tax line 1 per S line P Payment line 1 per distinct payment method D Discount line 0 or 1 per S line — never multiple Composite key on every row: DATAAREAID + TRANSACTIONNUMBER + OPERATINGUNITNUMBER + TERMINAL. T and D link to S via SALES_LINE_NUMBER → S.LINE_NUMBER. P.LINE_NUMBER is sequential within the transaction. 2. H — Header Column Cegid source Rule DATAAREAID ET_FILIALE → legal-entity D365 code via mapping (Oman = as) TRANSACTIONNUMBER GP_REFINTERNE preserve leading zeros OPERATINGUNITNUMBER ET_CHARLIBRE1 → D365 store code via mapping TERMINAL GP_CAISSE → D365 register code via mapping CUSTOMERACCOUNT GP_TIERS populated for employee/board/family; empty for walk-in → omit property RRECEIPTID GP_NUMERO TRANSACTIONDATE GP_DTSVCREATE POS creation datetime, UTC BUSINESSDATE GP_HEURECREATION store-local timezone TRANSACTIONORDERTYPE fixed SalesOrder TRANSACTIONTYPE fixed Sales TAXCALCULATIONTYPE fixed Regular SALEISRETURNSALE derived Sale=No, Return=Yes, Exchange=Yes TOACCOUNT fixed Yes STAFF GP_REPRESENTANT LANGUAGEID fixed en-US CURRENCY GP_DEVISE EXCHANGERATE fixed 100 WAREHOUSE GDE_CHARLIBRE1 of GP_DEPOT → D365 warehouse code via mapping NETPRICE SUM(GL_MONTANTHT) pre-discount, VAT-excl NETAMOUNT GP_TOTALHT post-discount, VAT-excl GROSSAMOUNT GP_TOTALTTC incl. tax, post-discount DISCOUNTAMOUNT SUM(ABS(MLR_MONTANTHT)) = sum of all D-line DiscountCost DISCOUNTAMOUNTWITHOUTTAX SUM(ABS(MLR_MONTANTHT)) VAT-excl TOTALDISCOUNTAMOUNT SUM(ABS(MLR_MONTANTHT)) VAT-excl TOTALMANUALDISCOUNTAMOUNT fixed 0.00 TOTALMANUALDISCOUNTPERCENTAGE ABS(MLR_REMISE) PAYMENTAMOUNT SUM(GPE_MONTANTECHE) AMOUNTPOSTEDTOACCOUNT GP_TOTALTTC 3. S — Sales line Column Cegid source Rule LINE_NUMBER GL_NUMLIGNE overridden by consolidation — see §7 ITEMID GL_CODEARTICLE the SKU; single field INVENTBATCHID ZBA_BATCHNUMBER emit when populated, omit when empty. Cegid resolves batch from scanned QR — export the resolved batch, never the raw QR QUANTITY / UNITQUANTITY GL_QTEFACT directional sign PRICE GL_PUHT unit price, always positive NETPRICE GL_QTEFACT × GL_PUHT directional NETAMOUNT / NETAMOUNTINCLUSIVETAX GL_TOTALHT VAT-excluded, post-discount (despite the field name) SALESTAXAMOUNT GL_TOTALTAXE1 directional SALESTAXGROUP / ORIGINALSALESTAXGROUP YX_LIBELLE of ET_LIBREET1 Oman = CDOM TAXCODE country mapping Oman = S5 ITEMSALESTAXGROUP / ORIGINALITEMSALESTAXGROUP fixed FULL UNIT fixed EA CATEGORYNAME / CATEGORYHIERARCHYNAME fixed Retail Catgorey KEYBOARDPRODUCTENTRY fixed Yes LINEDISCOUNT / LINEMANUALDISCOUNTAMOUNT / LINEMANUALDISCOUNTPERCENTAGE fixed 0.00 ISLINEDISCOUNTED fixed No DISCOUNTAMOUNTWITHOUTTAX / TOTALDISCOUNT ABS(SUM(MLR_MONTANTHT)) omit/0 when no discount TOTALDISCOUNTPERCENTAGE ABS(MLR_REMISE) omit/0 when no discount ISRETURNNOSALE derived Sale=No, Return=Yes RETURNOPERATINGUNITNUMBER store of the original sale (Cegid supplies) = OperatingUnitNumber for same-store returns; different for cross-store returns RETURNTRANSACTIONNUMBER GP_REFINTERNE of the original sale return/exchange-return lines only RETURNLINENUMBER original sale's GL_NUMLIGNE (Cegid supplies) return/exchange-return lines only 4. T — Tax line Column Cegid source Rule SALES_LINE_NUMBER GL_NUMLIGNE FK to S.LINE_NUMBER TAXCODE YX3.YX_LIBELLE Oman = S5 TAXPERCENTAGE GPB_TAUXTAXE TAXAMOUNT GL_TOTALTAXE1 inverse sign of S.SALESTAXAMOUNT ISTAXINCLUDEDINPRICE fixed No ISEXEMPT fixed No 5. P — Payment line (one row per distinct payment method) Column Cegid source Rule LINE_NUMBER GPE_NUMECHE STORE GPE_ETABLISSEMENT RECEIPTID GPE_NUMERO TENDERTYPE mapping of GPE_MODEPAIE Cash=1, Visa=2, Amex=3, Gift Card=4, Customer Account=5 AMOUNTTENDERED / AMOUNTINTENDEREDCURRENCY / AMOUNTINACCOUNTINGCURRENCY / REFUNDABLEAMOUNT GPE_MONTANTECHE each method its own amount; not equal to header PaymentAmount when multiple tenders QUANTITY COUNT(GPE_NUMECHE) directional; 0 for clean exchange CURRENCYCODE GPE_DEVISE EXCHANGERATEINTENDEREDCURRENCY / EXCHANGERATEINACCOUNTINGCURRENCY fixed 100 TRANSACTIONSTATUS fixed Posted STAFF GP_REPRESENTANT ISPREPAYMENT / ISCHANGELINE / ISLINKEDREFUND fixed No ISPAYMENTCAPTURED fixed Yes 6. D — Discount line (0 or 1 per sales line, never multiple) When any discount applies to a sales line, emit exactly one D row aggregating all discount components for that line: Column Cegid source Rule SALES_LINE_NUMBER GL_NUMLIGNE FK to S.LINE_NUMBER LINE_NUMBER MLR_NUMLIGNE single line DISCOUNTCOST / DISCOUNTAMOUNT / EFFECTIVEAMOUNT SUM(MLR_MONTANTHT) for that sales line positive absolute DISCOUNTORIGINTYPE mapping of MLR_ORGREMISE Manual = manual/global discount · Customer = customer-category (family/employee/board) DISCOUNTPERCENTAGE effective % = SUM(MLR_MONTANTHT) ÷ line pre-discount net under the one-origin rule this equals ABS(MLR_REMISE) MANUALDISCOUNTTYPE fixed TotalDiscountPercent CUSTOMERDISCOUNTTYPE fixed None DEALPRICE fixed 0.00 7. SKU / batch consolidation — critical Group S lines by GL_CODEARTICLE (SKU) + ZBA_BATCHNUMBER (batch): Same SKU + same batch scanned several times → one S line, quantities summed. Same SKU + different batches → separate S lines. Simple items have empty ZBA_BATCHNUMBER → grouping collapses to SKU alone. S.LINE_NUMBER is the sequence produced by this grouping — not a passthrough of the Cegid line number, and not reconciled against it. T and D reference the resulting S line numbers. Precondition, comment in code: the resolved batch must also exist in the D365 batch master, or D365 rejects the line. This is a continuous data-sync discipline (the batch/QR master must stay ahead of sales), not enforceable in SQL. 8. Discount model One discount origin per transaction, never both. Customer-group customers (family / employee / board = "known customer") do not pay the full amount — their reduction is recorded as a discount → D line present, DiscountOriginType = Customer, special/discounted price on the S line. A normal walk-in customer can also receive the global discount → D line, DiscountOriginType = Manual. Line discounts are disabled — only global discount is active. Multiple discount components on the same line are summed into a single D line (see §6). D is never 0..N. 9. Sign conventions — enforce exactly Sale: S quantity/net/tax negative · T tax positive · P amounts positive. Return: S quantity/net/tax positive · T tax negative · P amounts negative · P quantity negative. Clean 1:1 exchange (single transaction, SaleIsReturnSale=Yes): header net/gross/payment = 0 · one return S line (+) and one sale S line (−) · P amounts and P quantity = 0 · NumberOfItems counts both (+1 and −1 → 2). Price-difference exchange: S lines keep return/sale signs · P reflects only the difference collected/refunded. PRICE (unit) always positive in every case — a negative unit price is invalid (HTTP 400). 10. Multi-store — hard requirement The crosswalks ET_CHARLIBRE1→OperatingUnit, GP_CAISSE→Terminal, GDE_CHARLIBRE1→Warehouse, and ET_FILIALE→dataAreaId must resolve for every store, register, warehouse and legal entity. Isolate all mappings in a lookup layer driven by reference data, so new stores/entities are onboarded without editing the query body. 11. Do NOT compute in SQL Control totals — NUMBEROFITEMLINES (COUNT(DISTINCT GL_ARTICLE)), NUMBEROFITEMS (SUM(ABS(GL_QTEFACT))), NUMBEROFPAYMENTLINES (COUNT(DISTINCT GPE_MODEPAIE)) and header aggregates — are recomputed and validated by the middleware. Supply the raw lines; any aggregate you emit must reconcile within rounding tolerance. 12. Scope & extensibility In scope: normal sale, return, clean exchange, price-difference exchange, sale with discount, return with discount, staff/family (customer-category discount), multiple tenders, batch-managed item. Explicitly out of scope — do not emit any field or branch for these: Gift cards (issue and redeem) — no RetailGiftCards, no GiftCardTransactionsV2, no GOC_NUMBON/GiftCardId, no gift-card payment-line adjustments. Future phases — build the skeleton to absorb them without a rewrite, but do not implement now: Void transactions (TransactionType = Void), multi-currency (ExchangeRate fixed at 100 today), multi-entity/multi-market tax (dataAreaId and tax groups beyond Oman). Keep mappings, signs and scenario logic isolated so each becomes a data/mapping extension, not a structural change. Volume: SKU and batch logic is generic — it scales to the full catalogue (all SKUs, all batches) with no change. Only performance (indexing, batch windows) is affected, not logic. 13. Deliverable A parameterised, idempotent query producing H/S/T/P/D rows for a given business-date / EOD-batch window, with: SKU+batch consolidation on S (T and D following the resulting line numbers); one aggregated D line per discounted S line; signs applied per transaction nature; return references pulled from the original sale as supplied by Cegid; all code mappings isolated in a lookup layer; every fixed value and mapping dependency commented. C'est le tableau complet et final. Tout ce qu'on a tranché ensemble y est figé : les champs Cegid exacts, la consolidation SKU+batch, la remise unique sommée avec origine Manual/Customer, le prix spécial traité comme remise Customer, le multi-store avec couche de mapping obligatoire, les références retour fournies par Cegid, les dates UTC/local, un paiement par mode, gift cards totalement exclues, et void/multi-devise/multi-entité isolés comme phases futures. Le seul choix que j'ai pris à votre place, faute de confirmation : DISCOUNTPERCENTAGE = pourcentage effectif recalculé (qui égale ABS(MLR_REMISE) sous votre règle une-origine). Si vous préférez lire ABS(MLR_REMISE) brut, c'est une ligne à changer. Dites-le-moi et je l'ajuste — sinon le prompt est prêt à être donné à l'agent.