CData NetSuite Drivers はクラウドERP のOracle NetSuite に対し、標準SQL でアクセスできるインタフェースを提供します。NetSuite はマスタやトランザクション、レポートなどさまざまなタイプのテーブルを持ちます。その中で、注文書や発注書などのトランザクションテーブルはヘッダ-明細の形式でデータが保持されるため、マスタなどに比べてデータの扱いが困難です。
例えば、注文書は以下のように基本情報となるヘッダ情報と、注文製品リストなどの明細情報(ラインアイテム)を持ちます。
注文書のリストを格納するSalesOrder テーブルはこのデータを以下のように保持します。製品などラインアイテムのリストデータはフラット化されて一つのカラムに格納されます。
本記事ではトランザクションのラインアイテムを追加・更新する方法と、適切な方法を行うための設定方法について説明します。
ラインアイテムの表現形式
ラインアイテムはトランザクション内に入れ子形式で含まれる明細情報のリストですが、ドライバ*では子テーブルまたは集約リストの表現形式でこのデータにアクセスできます。
*今回の説明はSuiteTalk スキーマを前提としています。
1. 子テーブル
ラインアイテムを親テーブル(トランザクション)と切り離し、別のテーブルとしてアクセスします。この方法のメリットは何といっても操作がシンプルなことです。ラインアイテムを独立したテーブルで扱えるため、入れ子構造を意識することがありません。これを有効にするには接続プロパティのIncludeChildTables プロパティをTRUE に設定します。子テーブルを有効にすると、「トランザクションテーブル名_ラインアイテムテーブル名」 という名前の子テーブルにアクセスできるようになります。例えば注文書(SalesOrder) の商品リスト(ItemList) はSalesOrder_itemList という名前でアクセスできます。
ラインアイテム追加時のSQL 例を以下に示します。
// 注文書12に「アイテム51を価格100で10個」のラインアイテムを追加
insert into SalesOrder_itemList(InternalId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity) values(12, 51, 100, 10);
ラインアイテム更新時のSQL 例を以下に示します。ListId カラムで更新するラインアイテムを指定します。
// 上の例で追加したラインアイテムの数量を100に変更
update SalesOrder_itemList set ItemList_Quantity = 100 where InternalId = 12 and ListId = 1 ;
複数のラインアイテムを同時に追加する場合は以下のように一時テーブル(#TEMP)を使います。
// 注文書12に「アイテム51を価格200で100個」と「アイテム52を価格300で150個」のラインアイテムを追加
insert into SalesOrder_itemList#TEMP(InternalId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity) values(12, 51, 200, 100);
insert into SalesOrder_itemList#TEMP(InternalId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity) values(12, 52, 300, 150);
insert into SalesOrder_itemList(InternalId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity)
select InternalId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity from SalesOrder_itemList#TEMP;
以下は複数のラインアイテムを同時に更新する例です。ListId カラムで更新するラインアイテムを指定します。
// 上の例でアイテムを追加したトランザクションに対し、ラインアイテム1の個数を200個に、ラインアイテム2の個数を300個に変更
insert into SalesOrder_itemList#TEMP(InternalId, ListId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity) values(12, 1, 51, 200, 200);
insert into SalesOrder_itemList#TEMP(InternalId, ListId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity) values(12, 2, 52, 300, 300);
update SalesOrder_itemList(InternalId, ListId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity) select InternalId, ListId, ItemList_Item_InternalId, ItemList_Amount,ItemList_Quantity from SalesOrder_itemList#TEMP;
上記のようにSQLで一括更新するときは一時テーブルを利用します。そのため、一時テーブルが使用できない環境*では1行ずつ更新する必要があります。このような環境で一括更新を行いたい場合は次に紹介する集約カラムを利用した方法が有効です。
*例えばツールによってはスキーマに定義されているテーブル名しか利用できないものがあります。こういったツールでは#TEMPを付与したテーブルにアクセスできません。
2. 集約カラム
ラインアイテムをトランザクションの集約カラムと呼ばれるカラムでアクセスします。このカラムではラインアイテムのリストが以下のようなXML で定義されます。
51
200
100
52
300
150
この方法のメリットはラインアイテムの新規追加や更新を一つのクエリで実現できることです。子テーブルを利用する場合、当然ですが追加はINSERT 句、更新はUPDATE 句を使用するため、一つのクエリで実行できるのは追加または更新のどちらかとなります。一方集約カラムを利用した方法ではXML で定義された複数のラインアイテムの内容を一度に変更するため、追加や更新を同時に適用することができます。
AggregateColumnMode プロパティにListAndRetrieve またはList を設定すると、ItemListAggregate のような集約カラムにアクセスできるようになります。ListAndRetrieve を設定すると集約カラムでラインアイテムの取得、更新が実行できます。一方List を設定すると集約カラムは表示されますがデータは取得しません。以下は二つの設定でそれぞれselect InternalId, ItemListAggregate from SalesOrder というSQL を実行したときの結果です。
List は更新のみを集約カラムで行うときに使用します。トランザクションの一覧を取得する際、ListAndRetrieve ではトランザクションと一緒にラインアイテムのデータも取得するため時間がかかりますが、List ではラインアイテムの取得を省略するため応答時間が短くなります。ラインアイテムのデータは取得できませんが、集約カラムを指定して更新を行うことができます。
ラインアイテム追加時のSQL 例を以下に示します。
// 注文書12に1つのラインアイテムがある状態で、「アイテム52を価格300で150個」のラインアイテムを追加
update SalesOrder set ItemListAggregate = '
51
200
100
52
300
150
' where InternalId = 12;
この例では、「アイテム51を価格200で100個」というラインアイテムが存在しているトランザクションに対し、「アイテム52を価格300で150個」のラインアイテムを追加しています。集約カラムを利用する場合は追加も更新もUPDATE 文を使うことに注意してください。常にラインアイテムのリスト全体を更新するため、アイテムを新規に追加する場合は既存のアイテムリスト＋追加するアイテムをすべて記述する必要があります。既存のアイテムリストのXML は以下のクエリで取得できます。
select ItemListAggregate from SalesOrder where InternalId = 12;
ラインアイテム更新時のSQL 例を以下に示します。先に述べたように追加と更新は同じSQL になります。また、このSQL 文で追加と更新を同時に実行することもできます。
// 上の例でアイテムを追加したトランザクションに対し、ラインアイテム1の個数を200個に、ラインアイテム2の個数を300個に変更
update SalesOrder set ItemListAggregate = '
51
200
200
52
300
300
' where InternalId = 12;
設定パターン
以上を踏まえ、ラインアイテムの取得や更新を上記方法で行うときのプロパティ設定パターンを示します。
1. ラインアイテムの取得・更新を子テーブルで実行する
プロパティ設定
IncludeChildTables=TRUE
AggregateColumnMode=Ignore
特に制約が無い場合はこちらの方法をお勧めします。取得、更新を子テーブルで行うためクエリが簡単です。トランザクションとラインアイテムが別々のテーブルとなるため、これらを両方取得する際は以下のように複数のクエリを実行する必要があります。
// 注文書一覧の取得
select * from SalesOrder;
// 注文書12のラインアイテム一覧の取得
select * from SalesOrder where InternalId = 12;
2. ラインアイテムの取得・更新を集約カラムで実行する
プロパティ設定
IncludeChildTables=FALSE
AggregateColumnMode=ListAndRetrieve
集約カラムの取得や更新をXML で行います。以下のように一つのテーブルからトランザクションとラインアイテムを同時に取得できますが、トランザクションのみを取得したい場合でも集約カラムにラインアイテムのデータが含まれるため時間がかかります。
// 注文書とラインアイテム一覧の取得（時間がかかる）
select * from SalesOrder;
3. ラインアイテムの取得を子テーブルで、更新を集約カラムで実行する
プロパティ設定
IncludeChildTables=TRUE
AggregateColumnMode=List
ラインアイテムの更新のみを集約カラムで行う場合はこちらの設定をお勧めします。ラインアイテムの取得は子テーブルで行います。トランザクションの取得は集約カラムを取得しないため時間がかかりません。
おわりに
本記事ではCData NetSuite Drivers でトランザクションのラインアイテムを扱う方法を説明しました。トランザクションテーブルは項目数も多くて複雑ですが、内部のデータ構造を理解することで適切なアクセス方法が見えてくると思います。お困りの点がありましたらCData のサポートデスクにご質問ください。
関連コンテンツ
