MantikiFeed - Sales

Standard sales


Sales are split into two tables in MantikiFeed.

dbo.sales

idstore_codesales_order_group_numbersales_order_numbershipment_reference_numbercustomer_numberorder_created_onorder_shipped_onsales_channelorder_typegoods_gross_inc_taxgoods_discount_inc_taxgoods_discount_taxwinebank_interest_inc_taxwinebank_interest_taxgoods_net_inc_taxgoods_taxgoods_net_ex_taxdelivery_gross_inc_taxdelivery_discount_inc_taxdelivery_discount_taxdelivery_net_inc_taxdelivery_taxdelivery_net_ex_taxUser_Field_1User_Field_2User_Field_3Descriptionpromotion_goods_discountagent_goods_discount_taxpayment_promotional_creditpayment_promotional_credit_taxpromotion_shipping_discountpromotion_shipping_discount_taxagent_shipping_discountagent_shipping_discount_taxentry_created_onpromotion_goods_discount_taxagent_goods_discount
31896vwshopV0017798V0017798AAV0017798AA-D0018400206011/10/17 12:00 AM11/13/17 12:00 AMSubscriptionstandard101.880000101.8816.9884.97.99007.991.336.6684002060[NULL]V0017798V0017798AA0000000011/13/17 03:22 PM00

dbo.sales_payment

idpayment_gatewayauthorisation_transaction_idsettlement_transaction_idgateway_authorisation_transaction_idgateway_settlement_transaction_idpayment_methodcard_typecard_numberamountsales_idUser_Field_1User_Field_2User_Field_3Descriptionentry_created_on
31960WorldPay-HCV0017798-P001V0017798-P001-S00120026161802004506280CardVisa Credit Card********1111109.8731896[NULL]2004506280[NULL][NULL]11/13/17 03:22 PM

Payment table has these specific rules to follow.

  1. If the payment method is any of these:  "exchange", "wine_bank_interest", "promotional_credit" we do not want to show the payment in the payment table.
  2. Tax rules for discounts:
    1. sales
      goods_discount_inc_tax includes promotional_credit amount
      goods_discount_tax includes promotional_credit tax amount
      promotional_credit amount is subtracted from the goods_net_inc_tax 
      promotional_credit tax amount is subtracted from the goods_tax

    2. sales_payment
      No entry for the promotional_credit amount in the sales payment table

Preorders

Preorders have some specific rules to follow.

  1. The dbo.sales_payment table must include both the initial payment of the deposit as an entry for eg £12 and upon redemption of the balance, there must be a second entry for the deposit as it is used as part payment for the balance.
  2. Where multiple items are preordered, the deposit should be credited all against the first item alphabetically, not spread evenly over the balance items:- ie the order with the suffix AA has all of the deposit credited against it.  Where the deposit paid is greater than the value of the balance of the first item, only the value of the first item is used from the pool of the deposit and the remainder is credited against the next alphabetical item and so on.  This has to be manufactured in the VW code as preorders in Mantiki work at a group level, not the level of the individual order.

Example below of a transaction where the deposit paid covers the price of one of the items fully

Stage 1, place preorder

dbo.sales

idstore_codesales_order_group_numbersales_order_numbershipment_reference_numbercustomer_numberorder_created_onorder_shipped_onsales_channelorder_typegoods_gross_inc_taxgoods_discount_inc_taxgoods_discount_taxwinebank_interest_inc_taxwinebank_interest_taxgoods_net_inc_taxgoods_taxgoods_net_ex_taxdelivery_gross_inc_taxdelivery_discount_inc_taxdelivery_discount_taxdelivery_net_inc_taxdelivery_taxdelivery_net_ex_taxUser_Field_1User_Field_2User_Field_3Descriptionpromotion_goods_discountagent_goods_discount_taxpayment_promotional_creditpayment_promotional_credit_taxpromotion_shipping_discountpromotion_shipping_discount_taxagent_shipping_discountagent_shipping_discount_taxentry_created_onpromotion_goods_discount_taxagent_goods_discount
31927vwshopV0017980V0017980AAV0017980AA-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMTelesalespresell9000009009000000084002270[NULL]V0017980V0017980AA0000000011/15/17 09:45 AM00

dbo.sales_payment

idpayment_gatewayauthorisation_transaction_idsettlement_transaction_idgateway_authorisation_transaction_idgateway_settlement_transaction_idpayment_methodcard_typecard_numberamountsales_idUser_Field_1User_Field_2User_Field_3Descriptionentry_created_on
31999WorldPay-HCV0017980-P001V0017980-P001-S00120054728202005489340CardVisa Credit Card********11119031927[NULL]2005489340[NULL][NULL]11/15/17 09:45 AM

Stage 2, charge for balance

dbo.sales

idstore_codesales_order_group_numbersales_order_numbershipment_reference_numbercustomer_numberorder_created_onorder_shipped_onsales_channelorder_typegoods_gross_inc_taxgoods_discount_inc_taxgoods_discount_taxwinebank_interest_inc_taxwinebank_interest_taxgoods_net_inc_taxgoods_taxgoods_net_ex_taxdelivery_gross_inc_taxdelivery_discount_inc_taxdelivery_discount_taxdelivery_net_inc_taxdelivery_taxdelivery_net_ex_taxUser_Field_1User_Field_2User_Field_3Descriptionpromotion_goods_discountagent_goods_discount_taxpayment_promotional_creditpayment_promotional_credit_taxpromotion_shipping_discountpromotion_shipping_discount_taxagent_shipping_discountagent_shipping_discount_taxentry_created_onpromotion_goods_discount_taxagent_goods_discount
31942vwshopV0017988V0017988AIV0017988AI-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AI0000000011/15/17 10:00 AM00
31941vwshopV0017988V0017988AHV0017988AH-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AH0000000011/15/17 10:00 AM00
31940vwshopV0017988V0017988AGV0017988AG-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AG0000000011/15/17 10:00 AM00
31939vwshopV0017988V0017988AFV0017988AF-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AF0000000011/15/17 10:00 AM00
31938vwshopV0017988V0017988AEV0017988AE-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AE0000000011/15/17 10:00 AM00
31937vwshopV0017988V0017988ADV0017988AD-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AD0000000011/15/17 09:57 AM00
31936vwshopV0017988V0017988ACV0017988AC-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AC0000000011/15/17 09:57 AM00
31935vwshopV0017988V0017988ABV0017988AB-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AB0000000011/15/17 09:57 AM00
31934vwshopV0017988V0017988AAV0017988AA-D0018400227011/15/17 12:00 AM11/15/17 12:00 AMSubscriptiongift79.99000079.9913.3366.667.997.991.3300084002270[NULL]V0017988V0017988AA0000000011/15/17 09:57 AM00

dbo.sales_payment

idpayment_gatewayauthorisation_transaction_idsettlement_transaction_idgateway_authorisation_transaction_idgateway_settlement_transaction_idpayment_methodcard_typecard_numberamountsales_idUser_Field_1User_Field_2User_Field_3Descriptionentry_created_on
32019WorldPay-HCV0017980-P002V0017980-P002-S00820054992702005501670CardVisa Credit Card********111179.9931942[NULL]2005501670[NULL][NULL]11/15/17 10:00 AM
32018WorldPay-HCV0017980-P002V0017980-P002-S00720054992702005501660CardVisa Credit Card********111179.9931941[NULL]2005501660[NULL][NULL]11/15/17 10:00 AM
32017WorldPay-HCV0017980-P002V0017980-P002-S00620054992702005501650CardVisa Credit Card********111179.9931940[NULL]2005501650[NULL][NULL]11/15/17 10:00 AM
32016WorldPay-HCV0017980-P002V0017980-P002-S00520054992702005501640CardVisa Credit Card********111179.9931939[NULL]2005501640[NULL][NULL]11/15/17 10:00 AM
32015WorldPay-HCV0017980-P002V0017980-P002-S00420054992702005501630CardVisa Credit Card********111179.9931938[NULL]2005501630[NULL][NULL]11/15/17 10:00 AM
32014WorldPay-HCV0017980-P002V0017980-P002-S00320054992702005501620CardVisa Credit Card********111179.9931937[NULL]2005501620[NULL][NULL]11/15/17 09:57 AM
32013WorldPay-HCV0017980-P002V0017980-P002-S00220054992702005501610CardVisa Credit Card********111179.9931936[NULL]2005501610[NULL][NULL]11/15/17 09:57 AM
32012PresellV0017980AA-PO-1V0017980AA-PO-1[NULL][NULL]Presell[NULL][NULL]10.0131935[NULL][NULL][NULL][NULL]11/15/17 09:57 AM
32011WorldPay-HCV0017980-P002V0017980-P002-S00120054992702005501600CardVisa Credit Card********111169.9831935[NULL]2005501600[NULL][NULL]11/15/17 09:57 AM
32010PresellV0017980AA-PO-1V0017980AA-PO-1[NULL][NULL]Presell[NULL][NULL]79.9931934[NULL][NULL][NULL][NULL]11/15/17 09:57 AM