SSIS Components for au PAY Market

Build 24.0.9062

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

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062