Orders

Retrieve and query information related to orders.

Select

The 本製品 will use the au PAY Market API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the 本製品.

OrderId, ShopId, SellMethodSegment, Nickname, DeliveryMethodId and DeliveryId columns support the = operator.
OrderDate column supports the =,<,<=,>,>= operators.
MailAddress, OrdererName, OrdererKana, OrdererPhoneNumber1, SenderName, SenderKana, SenderPhoneNumber1 and DeliveryId columns support the LIKE operator.
UserComment, Memo, DeliveryRequestDay columns support the IS NULL, IS NOT NULL operator.
LotNumber, ItemCode, SettlementId, CardStatus, AtmStatus, NetBankingStatus, CarrerStatus, AuPaymentStatus, CvsStatus, CvsExpire, Device, HasUsePoint, HasCoupon, GiftPointStatus, GetRawMailAddress, IsWowmaMember pseudo-columns support the = operator.
ItemManagementId, ItemName pseudo-columns support the LIKE operator.

For example, the following queries are processed server side:

SELECT * FROM Orders WHERE OrderId = 1234

SELECT * FROM Orders WHERE OrderDate > '2018/12/13' AND OrderDate < '2019/12/13'

SELECT * FROM Orders WHERE MailAddress LIKE 'address' AND OrdererName LIKE 'name' AND OrdererKana LIKE 'kana' AND OrdererPhoneNumber1 LIKE '123456789' AND SenderName LIKE 'sender name' AND SenderKana LIKE 'sender kana' AND SenderPhoneNumber1 LIKE '123456789'

SELECT * FROM Orders WHERE SellMethodSegment = 1 AND DeliveryId = 1 AND DeliveryMethodId = 'method id' AND Nickname = 'nickname' AND LotNumber = 12345 AND ItemManagementId = 'manage id' AND ItemCode = 'CD001' AND ItemName = 'item name' AND SettlementId = 'settle id' AND CardStatus = 'NL' AND AtmStatus = 'ND' AND NetBankingStatus = 'WT' AND CarrerStatus = 'NR' AND AuPaymentStatus = 'we' AND CvsStatus = 'ED' AND CvsExpire = 12 AND Device = 'P' AND HasUsePoint = 2 AND HasCoupon = 2 AND GiftPointStatus = 1 AND GetRawMailAddress = 1 AND IsWowmaMember = 1

SELECT * FROM Orders WHERE DeliveryRequestDay IS NULL AND Memo IS NOT NULL AND UserComment IS NULL AND OrderDate = '01/01/2019'

Insert

Insert is not supported for this table

Update

au PAY Market allows updates for the OrdererPhoneNumber1, OrderDate, ReleaseDate, OrdererName, OrdererKana, OrdererZipCode, OrdererAddress, OrdererPhoneNumber2, SenderName, SenderKana, SenderZipCode, SenderAddress, SenderPhoneNumber1, SenderPhoneNumber2, SenderShopCode, SettlementName, Memo, PostagePrice, ChargePrice, DeliveryMethodId, DeliveryId, DeliveryRequestTime, ShippingDate, ShippingCarrier, ShippingNumber, OrderItems columns. OrderId is required.

UPDATE Orders SET OrdererPhoneNumber1 = '090-1111-2223', OrderDate = '2019-01-15', ReleaseDate = '2019/01/15', OrdererName = 'orderer name', OrdererKana = 'orderer kana', OrdererZipCode = '192-0002', OrdererAddress = 'orderer address', OrdererPhoneNumber2 = '3462256264', SenderName = 'sender name', SenderKana = 'sender phonetic', SenderZipCode = '192-0002', SenderAddress = 'address', SenderPhoneNumber1 = '24624621-2222', SenderPhoneNumber2 = '24665226', SenderShopCode = '123', SettlementName = 'settle name', Memo = 'THE MEMO', PostagePrice = 0, ChargePrice = 0, DeliveryMethodId = '234', DeliveryId = 'id', DeliveryRequestTime = 'morning', ShippingDate = '2018-05-04', ShippingCarrier = 'the carrier', ShippingNumber = '1234' WHERE OrderId = 12345

OrderItems column contains information on the OrderItems subtable. This field can be updated as an aggregate, or by using temporary tables.

UPDATE Orders SET OrderItems = '<detail> <orderDetailId>211700</orderDetailId> <unit>12</unit> </detail> <detail> <orderDetailId>211701</orderDetailId> <unit>13</unit> </detail> <detail> <orderDetailId>211702</orderDetailId> <unit>14</unit> </detail>' WHERE OrderId = 12345

INSERT INTO OrderItems#TEMP (Unit, OrderItemId)  VALUES (12, 211700)

INSERT INTO OrderItems#TEMP (Unit, OrderItemId, OrderId)  VALUES (13, 211701, 12345)

INSERT INTO OrderItems#TEMP (Unit, OrderItemId, OrderId)  VALUES (14, 211702, 12345)

UPDATE Orders SET OrderItems = 'OrderItems#TEMP' WHERE OrderId = 12345

Delete

Delete is not supported for this table

Columns


Name	Type	ReadOnly	References	Description
OrderId [KEY]	Long	False		Order Id.
OrderDate	Datetime	False		Date of the order.
OrderStatus	String	True		Order status. New acceptance, before shipping, payment waiting for credit, waiting ships, waiting ships after a payment, waiting completion, pending, cancellation, various custom statuses. (Possible status names have been registered by Takashi store in the sales order management).
SellMethodSegment	Integer	True		Sales methods. : Normal sale 2: Reservation sale.
ReleaseDate	Date	False		Order release date.
SiteAndDevice	String	True		Purchased the site name (Wowma! / Wowma! For au) and device type (PC / SP / KT).
MailAddress	String	True		Order mail address.
OrdererName	String	False		Name of the orderer.
OrdererKana	String	False		Phonetic name of the orderer.
OrdererZipCode	String	False		Zip code of the orderer.
OrdererAddress	String	False		Address of the orderer.
OrdererPhoneNumber1	String	False		Phone number of the orderer.
OrdererPhoneNumber2	String	False		Second phone number of the orderer.
Nickname	String	True		This is blank for non-members at the time of purchase.
SenderName	String	False		Name of the sender.
SenderKana	String	False		Phonetic name of the sender.
SenderZipCode	String	False		Zip code of the sender.
SenderAddress	String	False		Address of the sender.
SenderPhoneNumber1	String	False		Phone number of the sender.
SenderPhoneNumber2	String	False		Second phone number of the sender.
SenderShopCode	String	False		Sender shop code.
OrderOption	String	True		Order option.
SettlementName	String	False		Name of the settlement. Bank transfer, postal transfer, cash on delivery, credit card settlement, etc.
UserComment	String	True		Comments the user leaves for the order.
Memo	String	False		Memo user leaves for the order.
ContactStatus	String	True		Indicating whether the e-mail has been sent. Y: done N: pending
ContactDate	Datetime	True		Date that the e-mail was sent.
AuthorizationStatus	String	True		Indicating whether credit has already been authorized. Y: done N: pending
AuthorizationDate	Datetime	True		Date the credit authorization.
PaymentStatus	String	True		Indicating whether a payment has already been done. Y: done N: pending
PaymentDate	Datetime	True		Payment date.
ShipStatus	String	True		Indicating whether shipping has already been processed. Y: done N: pending
ShipDate	Datetime	True		Date the item will be shipped.
PrintStatus	String	True		Indicating whether the invoice has already been printed. Y: done N: pending
PrintDate	Datetime	True		The date the invoice has been printed.
CancelStatus	String	True		Cancellation status. N = Sold, C = Cancel
CancelReason	Integer	True		Cancellation reason. 1: No Contact (purchaser), 2: Absence of return (purchaser), 3: Refusal (purchaser), 4: Payment deadline over (purchaser), 5: Card settlement examination not possible (purchaser), 6: Others (purchaser), 7: Settlement method change (store), 8: Missing item (store), 9: Other (store)
CancelComment	String	True		Cancellation comments.
TotalSalePrice	Decimal	True		The sum of the contract prices from the order.
TotalSalePriceNormalTax	Long	True		[Breakdown] Total sales (10%)
TotalSalePriceReducedTax	Long	True		[Breakdown] Total sales (8%)
TotalSalePriceNoTax	Long	True		[Breakdown] Total sales (0%)
TotalSaleUnit	Integer	True		The sum of the contract prices from the number of items sold.
PostagePrice	Decimal	False		Price of the postage.
PostagePriceTaxRate	String	True		Postage tax rate
ChargePrice	Decimal	False		Charge price.
ChargePriceTaxRate	String	True		Fee tax rate
TotalItemOptionPrice	Decimal	True		Option fee (total)
TotalItemOptionPriceTaxRate	String	True		Option fee (total) tax rate
TotalGiftWrappingPrice	Decimal	True		Gift fee (total)
TotalGiftWrappingPriceTaxRate	String	True		Gift fee (total) tax rate
TotalPrice	Decimal	True		The sum of the total sales concluded in the same cart, adding consumption tax, commission and postage.
TotalPriceNormalTax	Long	True		[Breakdown] Subtotal billing amount (10%)
TotalPriceReducedTax	Long	True		[Breakdown] Subtotal billing amount (8%)
TotalPriceNoTax	Long	True		[Breakdown] Subtotal billing amount (0%)
PremiumType	String	True		au Smart Pass Premium Award Program application categories
PremiumIssuePrice	Decimal	True		au Smart Pass Premium Benefit Program application amount
PremiumMallPrice	Decimal	True		Mall burden postage
PremiumShopPrice	Decimal	True		Store burden postage
CouponTotalPrice	Decimal	True		The total price of coupons.
CouponTotalPriceNormalTax	Long	True		[Breakdown] Coupon usage (10%)
CouponTotalPriceReducedTax	Long	True		[Breakdown] Coupon usage (8%)
CouponTotalPriceNoTax	Long	True		[Breakdown] Coupon usage (0%)
UsePoint	Long	True		Points used.
UsePointNormalTax	Long	True		[Breakdown] Points used (10%)
UsePointReducedTax	Long	True		[Breakdown] Points used (8%)
UsePointNoTax	Long	True		[Breakdown] Points used (0%)
UsePointCancel	String	True		Indicator of use-point cancellation. Y = use point cancellation, N = Use point without cancellation, null = No points available.
UseAuPointPrice	Long	True		Price of using AuPoint.
UseAuPointPriceNormalTax	Long	True		[Breakdown] Usage of au points and au WALLET points (including au Wowma!) (10%)
UseAuPointPriceReducedTax	Long	True		[Breakdown] Usage of au points and au WALLET points (including au Wowma!) (8%)
UseAuPointPriceNoTax	Long	True		[Breakdown] Usage of au points and au WALLET points (including au Wowma!) (0%)
UseAuPoint	Long	True		Indicator of the use of AuPoint.
UseAuPointCancel	String	True		Indicator of the use of AuPoint cancellation.
RequestPrice	Decimal	True		Requested price.
RequestPriceNormalTax	Long	True		[Breakdown] Billing amount (10%)
RequestPriceReducedTax	Long	True		[Breakdown] Billing amount (8%)
RequestPriceNoTax	Long	True		[Breakdown] Billing amount (0%)
PointFixedDate	Date	True		Fixed date if the order has not been finalized.
PointFixedStatus	String	True		Point fixed date status. Y = definite, N = undetermined, null = No points given
SettleStatus	String	True		Credit card settlement. NL: Not Credit AD: Credit success TD: Sales success AE: Credit failed TE: Sale Failure CD: Settlement cancel , convenience store settlement, NL: Billing information has not been acquired ED: Claim waiting ND: Awaiting payment EX: Payment expired CD: Paid TC: Payment Stop, ATM settlement, NL: Billing information has not been acquired ED: Blaim waiting ND: Awaiting payment EX: Payment expired CD: Paid TC: Payment Stop , bank net banking settlement, NL: Claim waiting ND: aAaiting payment WT: Payment Checking CD: Paid TC: Payment Stop , carrier settlement (docomo Mobile payment / au simple settlement / Softbank collectively payment), NR: Billable outside the UC: Checking AD: Provisional billing CD: Billing cancel ER: The settlement process fails TD: Settlement processing completion , collectively au payment, NR: Billable outside TS: Provisional billing TC: Billing cancel ER: The settlement process fails TD: Settlement process is completed.
AuthoriTimelimitDate	String	True		Credit Date + payment processing period of each settlement method (according to the settlement method).
PageantResult	Integer	True		0: Success 1: Failure
PageantResponseCode	String	True		Response code that is returned from the Pageant
PageantResponseDetail	String	True		Response code is returned from the Pageant Details
PageantOrderId	String	True		ID to put string the order information of the settlement information and the mall side.
PageantRequestPrice	Decimal	True		Amount of money that was charged from the purchaser in the settlement.
PageantRequestPriceNormalTax	Long	True		[Breakdown] Settlement charge amount (10%)
PageantRequestPriceReducedTax	Long	True		[Breakdown] Amount charged for settlement (8%)
PageantRequestPriceNoTax	Long	True		[Breakdown] Settlement charge amount (0%)
CouponType	String	True		Funds type of coupon which was used at the time of purchase.
CouponKey	String	True		Unique string that indicates the type of coupon which was used at the time of purchase.
CardJudgement	String	True		Value indicating the result of the examination from the order information at the time of purchase. 1 = Examination completed, fraud likelihood is low, 2 = Examination completed, fraud likelihood is high, 3 = Examination completed, suspicious points, 0 = system error, null = examination excluded.
DeliveryName	String	True		Delivery name. Mail, courier, cool flight, a small courier, large courier, convenience store receipt, over-the-counter receipt, other (delivery method name Takashi store has registered at the store).
DeliveryMethodId	String	False		Delivery method ID that Takashi store has registered at the store building.
DeliveryId	String	False		Delivery id. M: mail T: courier
DeliveryRequestDay	Date	False		Requested day of the delivery.
DeliveryRequestTime	String	False		Requested time of the delivery.
ShippingDate	Date	False		Shipping date.
ShippingCarrier	Integer	False		Shipping carrier. 1: Black cat Yamato, 2: Sagawa Express, 3: JP Express (formerly Nippon Express), 4: Fukuyama Transporting, 5: Seino Transportation, 6: Japan Post, 7: Rakuten EXPRESS
ShippingNumber	String	False		Shipping number.
YamatoLnkMgtNo	String	True		In B2 cloud of Yamato Transport, number required when capturing a convenience store receipt of order data.
OrderItems	String	False		Aggregated data for inventory choices. Readable data found at sub-table with the same name.
ShopId	String	False		Store account.
ElecReceiptIssueStatus	Integer	False		Elec Receipt Issue Status
ElecReceiptIssueTimes	Integer	False		Elec Receipt Issue Times

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
LotNumber	Long	Lot number.
ItemManagementId	String	Administration id.
ItemCode	String	Item code in the order items.
ItemName	String	Item name.
SettlementId	String	Settlement id.
CardStatus	String	Card settlement status. 使用できる値は次のとおりです。NL, AD, TD, AE, TE, CD
AtmStatus	String	Atm settlement status. 使用できる値は次のとおりです。NL, ED, ND, EX, CD, TC
NetBankingStatus	String	Net banking settlement status. 使用できる値は次のとおりです。NL, ND, WT, CD, TC
CarrerStatus	String	Carrer settlement status. 使用できる値は次のとおりです。NR, AD, CD, ER, TD
AuPaymentStatus	String	Au payment settlement status. 使用できる値は次のとおりです。NR, TS, TC, ER, TD
CvsStatus	String	Cvs settlement status. 使用できる値は次のとおりです。NL, ED, ND, EX, CD
CvsExpire	Integer	Csv expiration.
Device	String	Device type. P: PC K: mobile S: smartphone 使用できる値は次のとおりです。P, K, S
HasUsePoint	Integer	Integer filtering by use points availability. 0: No use points 1: Existing 2: Pre-cancellation. 使用できる値は次のとおりです。0, 1, 2
HasCoupon	Integer	0: No 1: There are 2: pre-cancellation 使用できる値は次のとおりです。0, 1, 2
GiftPointStatus	Integer	0: undefined 1: confirmed 使用できる値は次のとおりです。0, 1
GetRawMailAddress	Integer	0: do not get 1: get 使用できる値は次のとおりです。0, 1
IsWowmaMember	Integer	0: undefined 1: confirmed 使用できる値は次のとおりです。0, 1