Items
Retrieve information related to Items.Query, add, update and delete products.
Select
The connector 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 connector.
- ItemCode column supports the = and LIKE operators.
- ItemName column supports the LIKE operator.
- LotNumber, ShopId, SellMethodSegment, PostageSegment, Postage, CategoryId columns support the = operator.
- LimitedPassword column supports the IS NULL, IS NOT NULL operators.
- StockCount, PointRate pseudo-column support the =,<,<=,>,>= operators.
- HasTags, DeliveryId, DeliveryMethodId pseudo-columns support the = operator.
SELECT * FROM Items WHERE ItemCode LIKE 'CD001' AND StockCount > 12 AND StockCount <= 30 AND PointRate = 15
SELECT * FROM Items WHERE LotNumber = 1234455 AND ShopId = '123' AND StockCount = 12 AND PointRate > 12 AND PointRate <= 30 AND HasTags = 2 AND DeliveryId = 'devID' AND DeliveryMethodId = 'devMetID'
SELECT * FROM Items WHERE ItemName LIKE 'name' SellMethodSegment = 1 AND PostageSegment = 'abc' AND Postage = 'cde' AND CategoryId = 'catID' AND LimitedPassword IS NOT NULL
Insert
You can specify all columns that are not readonly. ItemCode is required. ItemSpecifications, ItemOptions, ItemOptionCommissions, ItemImages, ItemDeliveries, ItemDeliveryMethods, Inventory contain information that has also been exposed in subtables with matching names. These values can be specified as aggregated data or taken from temporary tables.
INSERT INTO ItemDeliveryMethods#TEMP (ItemCode, LotNumber, DeliveryMethodId, DeliveryMethodSeq, DeliveryMethodName) VALUES ('itCode5', '1', '11', 1, 'methodName')
INSERT INTO ItemDeliveryMethods#TEMP (ItemCode, LotNumber, DeliveryMethodId, DeliveryMethodSeq, DeliveryMethodName) VALUES ('itCode5', '1', '13', 2, 'methodName2')
INSERT INTO ItemDeliveries#TEMP (ItemCode, LotNumber, DeliveryId, DeliverySeq) VALUES ('itCode5', '1', '11', 1)
INSERT INTO ItemDeliveries#TEMP (ItemCode, LotNumber, DeliveryId, DeliverySeq) VALUES ('itCode5', '1', '12', 2)
INSERT INTO ItemOptions#TEMP (ItemCode, LotNumber, ItemOptionSeq, ItemOptionTitle, ItemOption) VALUES ('itCode5', '1', '1', 'title1', 'option1')
INSERT INTO ItemOptions#TEMP (ItemCode, LotNumber, ItemOptionSeq, ItemOptionTitle, ItemOption) VALUES ('itCode5', '1', '2', 'title2', 'option2')
INSERT INTO ItemOptions#TEMP (ItemCode, LotNumber, ItemOptionSeq, ItemOptionTitle, ItemOption) VALUES ('itCode5', '1', '3', 'title3', 'option3')
INSERT INTO ItemSpecifications#TEMP (ItemCode, LotNumber, SpecificationSeq, SpecificationName, SpecificationValue) VALUES ('itCode5', '1', '1', 'specname1', 'specvalue1')
INSERT INTO ItemSpecifications#TEMP (ItemCode, LotNumber, SpecificationSeq, SpecificationName, SpecificationValue) VALUES ('itCode5', '1', '2', 'specname2', 'specvalue2')
INSERT INTO ItemSpecifications#TEMP (ItemCode, LotNumber, SpecificationSeq, SpecificationName, SpecificationValue) VALUES ('itCode5', '1', '3', 'specname3', 'specvalue3')
INSERT INTO InventoryChoices#TEMP (ChoicesHorizontalCode, ChoicesVerticalCode, ChoicesCount) VALUES ('M', 'Green', 5)
INSERT INTO InventoryChoices#TEMP (ChoicesHorizontalCode, ChoicesVerticalCode, ChoicesCount) VALUES ('S', 'Green', 2)
INSERT INTO InventoryChoices#TEMP (ChoicesHorizontalCode, ChoicesVerticalCode, ChoicesCount) VALUES ('M', 'Red', 4)
INSERT INTO InventoryChoices#TEMP (ChoicesHorizontalCode, ChoicesVerticalCode, ChoicesCount) VALUES ('S', 'Red', 0)
INSERT INTO InventoryVerticalChoices#TEMP (ChoicesVerticalCode, ChoicesVerticalName, ChoicesVerticalSeq) VALUES ('Red', 'Red', 1)
INSERT INTO InventoryVerticalChoices#TEMP (ChoicesVerticalCode, ChoicesVerticalName, ChoicesVerticalSeq) VALUES ('Green', 'Green', 2)
INSERT INTO InventoryHorizontalChoices#TEMP (ChoicesHorizontalCode, ChoicesHorizontalName, ChoicesHorizontalSeq) VALUES ('S', 'S', 1)
INSERT INTO InventoryHorizontalChoices#TEMP (ChoicesHorizontalCode, ChoicesHorizontalName, ChoicesHorizontalSeq) VALUES ('M', 'M', 2) ;
INSERT INTO Inventory#TEMP ( StockSegment, InventoryChoices, InventoryVerticalChoices, InventoryHorizontalChoices, ChoicesStockVerticalItemName, ChoicesStockHorizontalItemName ) VALUES ('2', 'InventoryChoices#TEMP', 'InventoryVerticalChoices#TEMP', 'InventoryHorizontalChoices#TEMP', 'Color', 'Size')
INSERT INTO Items (ItemCode, LotNumber, ItemName, ItemOptions, ItemSpecifications, ItemPrice, TaxSegment, PostageSegment, Postage, CategoryId, SaleStatus, ItemDeliveries, ItemDeliveryMethods, description, Inventory) VALUES ('itCode5', '1', 'name', 'ItemOptions#TEMP', 'ItemSpecifications#TEMP', 2345, 1, 1, '', 37020203, 1, 'ItemDeliveries#TEMP', 'ItemDeliveryMethods#TEMP', 'desc', 'Inventory#TEMP' )
Update
au PAY Market allows UPDATEs for all columns that are not readonly. ItemCode is required. The values for linked subtables can be specified as aggregated data or taken from temporary tables.
UPDATE Items SET ItemName = 'name', ItemOptions = 'ItemOptions#TEMP', ItemSpecifications = 'ItemSpecifications#TEMP', ItemPrice = 2345, ItemDeliveries = 'ItemDeliveries#TEMP', ItemDeliveryMethods = 'ItemDeliveryMethods#TEMP', description = 'Desc', Inventory = 'Inventory#TEMP' WHERE ItemCode = 'itCode5'
Batch Update
au PAY Market allows UPDATEs in batches for all columns that are not readonly except ItemSpecifications, ItemOptions, ItemOptionCommissions, ItemImages, ItemDeliveries, ItemDeliveryMethods, Inventory. ItemCode is required.
INSERT INTO Items#TEMP (ItemCode, ItemName, SearchKeywords, PointRate) VALUES ('MD001', 'name1', '<searchKeywords><searchKeyword>abc</searchKeyword><searchKeywordSeq>1</searchKeywordSeq></searchKeywords><searchKeywords><searchKeyword>test</searchKeyword> <searchKeywordSeq>2</searchKeywordSeq></searchKeywords><searchKeywords><searchKeyword>lolo</searchKeyword> <searchKeywordSeq>3</searchKeywordSeq></searchKeywords>', 1)
INSERT INTO Items#TEMP (ItemCode, ItemName, SearchKeywords, PointRate) VALUES ('Code0001', 'name2', '<searchKeywords><searchKeyword>abc</searchKeyword><searchKeywordSeq>1</searchKeywordSeq></searchKeywords><searchKeywords><searchKeyword>test</searchKeyword> <searchKeywordSeq>2</searchKeywordSeq></searchKeywords><searchKeywords><searchKeyword>lolo</searchKeyword><searchKeywordSeq>3</searchKeywordSeq></searchKeywords>', 1)
Update Items (ItemCode, ItemName, SearchKeywords, PointRate) SELECT ItemCode, ItemName, SearchKeywords, PointRate FROM Items#TEMP
Delete
To delete items singlularly, provide the ItemCode and/or LotNumber
DELETE FROM Items WHERE ItemCode = 'itemCode' AND LotNumber = '123'
Batch Delete
To delete items in batches, provide the ItemCode and/or LotNumber
Insert INTO Items#TEMP (ItemCode) VALUES ('it1')
INSERT INTO Items#TEMP (ItemCode) VALUES ('it2')
INSERT INTO Items#TEMP (ItemCode) VALUES ('it3')
INSERT INTO Items#TEMP (LotNumber) VALUES ('lot1')
INSERT INTO Items#TEMP (ItemCode, LotNumber) VALUES ('it4', 'lot4')
INSERT INTO Items#TEMP (ItemCode, LotNumber) VALUES ('it5', 'lot5')
INSERT INTO Items#TEMP (ItemCode, LotNumber) VALUES ('MD001', '9808451')
INSERT INTO Items#TEMP (ItemCode, LotNumber) VALUES ('MD002', '9808452')
INSERT INTO Items#TEMP (ItemCode, LotNumber) VALUES ('MD003', '9808453')
DELETE FROM Items WHERE EXISTS (SELECT LotNumber, ItemCode FROM Items#TEMP)
Columns
| Name | Type | ReadOnly | References | Description |
| ItemCode [KEY] | String | False |
Product code. | |
| LotNumber | Long | False |
Lot number. | |
| ItemName | String | False |
Product name. | |
| ItemManagementId | String | False |
Product id for management. | |
| ItemManagementName | String | False |
Product name for management. | |
| ItemPrice | Decimal | False |
Selling price. | |
| SellMethodSegment | Integer | False |
Sales methods. 1: Normal sale 2: Reservation sale. 使用できる値は次のとおりです。1, 2 | |
| ReleaseDate | Date | False |
Selling date. | |
| ReserveRegistrationDate | Date | True |
Registration Date. | |
| MakerRetailPrice | Decimal | False |
Manufacturer suggested retail price | |
| MakerRetailPriceUrl | String | False |
Manufacturer suggested retail price URL | |
| TaxSegment | Integer | False |
Tax consumtion. 使用できる値は次のとおりです。1, 2, 3 | |
| ReducedTax | Integer | False |
Reduced tax rate setting. 使用できる値は次のとおりです。1, 2 | |
| PostageSegment | String | False |
Shipping classification. | |
| Postage | String | False |
Extra Shipping. | |
| SellStartDate | Datetime | False |
Sales start date and time. | |
| SellEndDate | Datetime | False |
End of sale date. | |
| CountdownTimerConfig | Integer | False |
Reduced tax rate setting. 使用できる値は次のとおりです。1, 2 | |
| SellNumberDispConfig | Integer | False |
Reduced tax rate setting. 使用できる値は次のとおりです。1, 2 | |
| BuyNumLimtConfig | Integer | False |
Reduced tax rate setting. 使用できる値は次のとおりです。1, 2 | |
| BuyNumMax | Integer | False |
Reduced tax rate setting. 使用できる値は次のとおりです。1, 2 | |
| PublicStartDate | Datetime | True |
Publication date and time information. | |
| LimitedOrderSegment | String | False |
The maximum purchase settings category. | |
| LimitedOrderCount | Integer | False |
The maximum purchase count. | |
| Description | String | False |
Product Description. | |
| DescriptionForSP | String | False |
Product description for SP. | |
| DescriptionForPC | String | False |
Product description for PC. | |
| DetailTitle | String | False |
Item details title. | |
| DetailDescription | String | False |
Item details description. | |
| SearchKeywords | String | False |
Search keywords related to the item. | |
| CategoryId | String | True |
Category ID. | |
| CategoryName | String | True |
Category name. | |
| Tags | String | False |
Search tags related to the item. | |
| Jan | String | False |
JAN code. | |
| Isbn | String | False |
ISBN code. | |
| ItemModel | String | False |
Model number. | |
| LimitedPassword | String | False |
Password restrictions setting entry field. | |
| LimitedPasswordPageTitle | String | False |
Password restrictions page title. | |
| LimitedPasswordPageMessage | String | False |
Password restrictions page message. | |
| SaleStatus | String | False |
Sale status. | |
| PointRate | Integer | False |
Point magnification. | |
| FavoriteCount | Integer | True |
My favorite number. | |
| ReceiptRequestCount | Integer | True |
Stock count requested. | |
| StockRequestConfig | Integer | False |
Incoming request settings. 使用できる値は次のとおりです。1, 2 | |
| ReturnRequestConfig | Integer | False |
Return application. 使用できる値は次のとおりです。1, 2 | |
| StockRequestCount | Integer | False |
Number of arrival requests. | |
| DeliveryLeadtimeId | String | False |
Delivery lead time ID. | |
| ItemSpecifications | String | False |
Aggregated data for item specifications. Readable data found at sub-table with the same name. | |
| ItemOptions | String | False |
Aggregated data for item options. Readable data found at sub-table with the same name. | |
| ItemOptionCommissions | String | False |
Aggregated data for item option commissions. Readable data found at sub-table with the same name. | |
| ItemImages | String | False |
Aggregated data for item images. Readable data found at sub-table with the same name. | |
| ItemDeliveries | String | False |
Aggregated data for item deliveries. Readable data found at sub-table with the same name. | |
| ItemDeliveryMethods | String | False |
Aggregated data for item delivery methods. Readable data found at sub-table with the same name. | |
| Inventory | String | False |
Aggregated data for item stock information. Readable data found at sub-table with the same name. | |
| ShopId | String | False |
Store account. |
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 |
| StockCount | Integer |
Number of items in stock. |
| HasTags | Integer |
The presence or absence of tags 1: No tags set 2: Tags have been set. 使用できる値は次のとおりです。1, 2 |
| DeliveryId | String |
Identifier of the item delivery. |
| DeliveryMethodId | String |
Identifier of the item delivery method |