# Amazon knowledge pack: what the assistant reads before it queries an Amazon database. # loopy_skills.py serves it, filtered to the databases the caller can open. # Amazon columns are measured from each report header at sync (amazon_schema PROVENANCE), so no # static column list exists. Field keys here are names the code already types: amazon_schema # TABLE_SPECS identities and relationships, connector_spec links, and amazon_relational's # date, money and quantity name sets. verify_agent_knowledge.py gates this. # Plain ASCII only. The assistant repeats this text to users. source: amazon connector: amazon_sp title: Amazon scope: orders: database: ut_amzn_orders date_field: purchase_date rule: "For orders by day, week or month, count rows of ut_amzn_orders grouped by the day of purchase_date. One row is one order." cancelled: field: order_status values: [Cancelled] rule: "Exclude rows whose order_status is Cancelled when you count orders or sum sales." read_only: rule: "Amazon data is read only. Never create, change or send anything to Amazon." traps: - id: field_list_first text: "Read the database's field list before a query. A field missing from the list does not exist in this workspace yet, because Amazon columns appear at the first sync." - id: dates_are_text text: "Amazon dates such as purchase_date are ISO 8601 text in UTC. The day is the first 10 characters. Say the days are UTC days." - id: currency text: "Amounts are in the currency field of each row. Do not add amounts across currencies." - id: sku_keys text: "The seller SKU is sku on orders, order items, settlements and fee estimates, msku on inventory, and seller_sku on listings and referral fees. Join on these, not on the product name." - id: amazon_vs_odoo text: "Odoo also carries the Amazon channel as the GIFTWARE DEALS partner. For an Amazon order or fee question use these databases, not Odoo." databases: ut_amzn_orders: label: Amazon orders row: "One Amazon order, from the all orders report. The first line of the order supplies its item columns." fields: money: [item_price, item_tax, shipping_price] quantity: [quantity] date: [purchase_date, last_updated_date] status: [order_status] joins: - {database: ut_amzn_order_items, from: amazon_order_id, to: amazon_order_id} - {database: ut_amzn_settlements, from: amazon_order_id, to: order_id} - {database: ut_amzn_listings, from: sku, to: seller_sku} traps: - id: orders_by_day text: "This is the database for how many orders arrived per day. Count rows by the day of purchase_date and exclude order_status Cancelled." - id: first_line_only text: "item_price, quantity and sku here belong to the order's first line only. For units or sales of a whole order use ut_amzn_order_items." - id: pending_price text: "A Pending order has no item_price yet. Do not read a blank price as zero sales." ut_amzn_order_items: label: Amazon order items row: "One line of one Amazon order: one SKU with its quantity and price." fields: money: [item_price, item_tax, shipping_price, item_promotion_discount] quantity: [quantity] date: [purchase_date, last_updated_date] status: [order_status, item_status] joins: - {database: ut_amzn_orders, from: amazon_order_id, to: amazon_order_id} - {database: ut_amzn_listings, from: sku, to: seller_sku} traps: - id: line_total text: "item_price is the total for the line's quantity, not a unit price. Sum item_price for sales and quantity for units." - id: count_distinct text: "amazon_order_id repeats on every line of an order. Count distinct amazon_order_id for an order count, or use ut_amzn_orders." ut_amzn_settlements: label: Amazon settlements row: "One line of an Amazon settlement report: one charge, fee or payment inside one payout." fields: money: [amount, total_amount] quantity: [quantity_purchased] date: [posted_date, settlement_start_date, settlement_end_date, deposit_date] status: [amount_type, amount_description] joins: - {database: ut_amzn_orders, from: order_id, to: amazon_order_id} - {database: ut_amzn_listings, from: sku, to: seller_sku} traps: - id: signed_amounts text: "amount is signed. Fees and refunds are negative, so the sum of amount is net proceeds. Group by amount_type and amount_description to split sales from fees." - id: summary_line text: "The first line of each settlement carries total_amount and deposit_date and no order. Never add total_amount to amount." - id: payout_date text: "posted_date is when a line was posted. deposit_date is when the payout reached the bank. Use posted_date for activity by day." ut_amzn_inventory: label: Amazon inventory row: "One product at one disposition and location for one day, from the FBA inventory ledger summary." fields: money: [] quantity: [starting_warehouse_balance, receipts, customer_shipments, customer_returns, ending_warehouse_balance] date: [date] status: [] joins: - {database: ut_amzn_listings, from: msku, to: seller_sku} - {database: ut_amzn_listings, from: asin, to: asin1} traps: - id: one_day_only text: "Each day repeats the stock. Sum ending_warehouse_balance for one date only, never across dates." - id: sellable text: "Filter disposition SELLABLE for stock a customer can buy. Other dispositions are damaged or held stock." ut_amzn_fba_fee_estimates: label: Amazon FBA fee estimates row: "One SKU in one Amazon store on one snapshot day, with Amazon's estimated fees per unit." fields: money: [estimated_fee_total, estimated_referral_fee_per_unit, expected_fulfillment_fee_per_unit, your_price] quantity: [] date: [_snapshot_date] status: [] joins: - {database: ut_amzn_listings, from: sku, to: seller_sku} - {database: ut_amzn_listings, from: asin, to: asin1} traps: - id: latest_snapshot text: "Each sync adds a new _snapshot_date. Use the latest _snapshot_date for current fees." - id: estimate_not_charge text: "These are estimates per unit, not fees Amazon charged. Charged fees are in ut_amzn_settlements." ut_amzn_referral_fees: label: Amazon referral fee preview row: "One seller SKU on one snapshot day, with its price and Amazon's referral fee preview." fields: money: [price] quantity: [] date: [_snapshot_date] status: [] joins: - {database: ut_amzn_listings, from: seller_sku, to: seller_sku} - {database: ut_amzn_listings, from: asin, to: asin1} traps: - id: latest_snapshot text: "Each sync adds a new _snapshot_date. Use the latest _snapshot_date for current fees." ut_amzn_storage_fees: label: Amazon storage fees row: "One product at one fulfillment center for one month, with its estimated monthly storage fee." fields: money: [estimated_monthly_storage_fee] quantity: [] date: [month] status: [] joins: - {database: ut_amzn_listings, from: asin, to: asin1} traps: - id: per_center text: "A product has one row per fulfillment_center each month. Sum across centers for its monthly fee." ut_amzn_listings: label: Amazon listings row: "One active or inactive listing for one seller SKU." fields: money: [price] quantity: [quantity] date: [open_date] status: [] joins: - {database: ut_amzn_orders, from: seller_sku, to: sku} - {database: ut_amzn_inventory, from: seller_sku, to: msku} traps: - id: one_snapshot text: "Listings keeps one copy per snapshot day. Use the latest day for the current catalogue."