add count product and vendor in purchase order

1 files changed, 1216 insertions, 235 deletions

diff --git a/calculate_price_after_disc.ktr b/calculate_price_after_disc.ktr
index f3798dd..86705b1 100644
--- a/calculate_price_after_disc.ktr
+++ b/calculate_price_after_disc.ktr
@@ -499,6 +499,101 @@
     </hop>
     <hop>
       <from>Calculator 2</from>
+      <to>filter group1</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group1</from>
+      <to>select group1</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group1</from>
+      <to>filter group2</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group2</from>
+      <to>select group2</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group2</from>
+      <to>filter group3</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group3</from>
+      <to>select group3</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group3</from>
+      <to>filter group4</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group4</from>
+      <to>select group4</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group4</from>
+      <to>filter group5</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group5</from>
+      <to>select group5</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>filter group5</from>
+      <to>Write to log</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>select group1</from>
+      <to>Append streams</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>select group2</from>
+      <to>Append streams</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>select group3</from>
+      <to>Append streams 2</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>Append streams</from>
+      <to>Append streams 2</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>select group4</from>
+      <to>Append streams 3</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>Append streams 2</from>
+      <to>Append streams 3</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>select group5</from>
+      <to>Append streams 4</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>Append streams 3</from>
+      <to>Append streams 4</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>Append streams 4</from>
       <to>Insert / update</to>
       <enabled>Y</enabled>
     </hop>
@@ -1044,263 +1139,73 @@
         <update>Y</update>
       </value>
       <value>
-        <name>purchase_price</name>
-        <rename>purchase_price</rename>
-        <update>Y</update>
-      </value>
-      <value>
         <name>pricing_group</name>
         <rename>pricing_group</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>markup_std1</name>
-        <rename>markup_std1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_std2</name>
-        <rename>markup_std2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_std3</name>
-        <rename>markup_std3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_std4</name>
-        <rename>markup_std4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_std5</name>
-        <rename>markup_std5</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_group1</name>
-        <rename>markup_group1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_group2</name>
-        <rename>markup_group2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_group3</name>
-        <rename>markup_group3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_group4</name>
-        <rename>markup_group4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_group5</name>
-        <rename>markup_group5</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier2_1</name>
-        <rename>markup_tier2_1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier2_2</name>
-        <rename>markup_tier2_2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier2_3</name>
-        <rename>markup_tier2_3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier2_4</name>
-        <rename>markup_tier2_4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier2_5</name>
-        <rename>markup_tier2_5</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier3_1</name>
-        <rename>markup_tier3_1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier3_2</name>
-        <rename>markup_tier3_2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier3_3</name>
-        <rename>markup_tier3_3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier3_4</name>
-        <rename>markup_tier3_4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>markup_tier3_5</name>
-        <rename>markup_tier3_5</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_after_disc_1</name>
-        <rename>price_after_disc_1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_after_disc_2</name>
-        <rename>price_after_disc_2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_after_disc_3</name>
-        <rename>price_after_disc_3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_after_disc_4</name>
-        <rename>price_after_disc_4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_after_disc_5</name>
-        <rename>price_after_disc_5</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_before_disc1</name>
-        <rename>price_before_disc1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_before_disc2</name>
-        <rename>price_before_disc2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_before_disc3</name>
-        <rename>price_before_disc3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_before_disc4</name>
-        <rename>price_before_disc4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_before_disc5</name>
-        <rename>price_before_disc5</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier2_1</name>
-        <rename>price_tier2_1</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier2_2</name>
-        <rename>price_tier2_2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier2_3</name>
-        <rename>price_tier2_3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier2_4</name>
-        <rename>price_tier2_4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier2_5</name>
-        <rename>price_tier2_5</rename>
+        <name>purchase_price</name>
+        <rename>purchase_price</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier3_1</name>
-        <rename>price_tier3_1</rename>
+        <name>markup_std</name>
+        <rename>markup_std</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier3_2</name>
-        <rename>price_tier3_2</rename>
+        <name>markup_group</name>
+        <rename>markup_group</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier3_3</name>
-        <rename>price_tier3_3</rename>
+        <name>markup_tier2</name>
+        <rename>markup_tier2</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier3_4</name>
-        <rename>price_tier3_4</rename>
+        <name>markup_tier3</name>
+        <rename>markup_tier3</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier3_5</name>
-        <rename>price_tier3_5</rename>
+        <name>markup_tier4</name>
+        <rename>markup_tier4</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier4_1</name>
-        <rename>price_tier4_1</rename>
+        <name>markup_tier5</name>
+        <rename>markup_tier5</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier4_2</name>
-        <rename>price_tier4_2</rename>
+        <name>price_after_disc</name>
+        <rename>price_after_disc</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier4_3</name>
-        <rename>price_tier4_3</rename>
+        <name>price_before_disc</name>
+        <rename>price_before_disc</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier4_4</name>
-        <rename>price_tier4_4</rename>
+        <name>price_tier2</name>
+        <rename>price_tier2</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier4_5</name>
-        <rename>price_tier4_5</rename>
+        <name>price_tier3</name>
+        <rename>price_tier3</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier5_1</name>
-        <rename>price_tier5_1</rename>
+        <name>price_tier4</name>
+        <rename>price_tier4</rename>
         <update>Y</update>
       </value>
       <value>
-        <name>price_tier5_2</name>
-        <rename>price_tier5_2</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier5_3</name>
-        <rename>price_tier5_3</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier5_4</name>
-        <rename>price_tier5_4</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>price_tier5_5</name>
-        <rename>price_tier5_5</rename>
+        <name>price_tier5</name>
+        <rename>price_tier5</rename>
         <update>Y</update>
       </value>
     </lookup>
@@ -1313,8 +1218,8 @@
       </output>
     </remotesteps>
     <GUI>
-      <xloc>720</xloc>
-      <yloc>64</yloc>
+      <xloc>1312</xloc>
+      <yloc>544</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
@@ -1330,7 +1235,14 @@
       <schema_name/>
     </partitioning>
     <connection>dw bi indoteknik</connection>
-    <sql>select mpp.product_id, p.item_code, p."name", mpp.purchase_price, b.pricing_group, 
+    <sql>select ppd.product_id, p.item_code, p.name, b.pricing_group,
+case 
+	when human_last_update is null then coalesce(system_price, 0)
+	when system_last_update is null then coalesce(product_price, 0)
+	when system_last_update >= human_last_update then coalesce(system_price, 0)
+	when b.pricing_group is null then 0
+	else coalesce(product_price, 0)
+end as purchase_price,
 (select group1 from price_group_dw pgd where id = 2) as markup_std1,
 (select group2 from price_group_dw pgd where id = 2) as markup_std2,
 (select group3 from price_group_dw pgd where id = 2) as markup_std3,
@@ -1361,10 +1273,9 @@
 (select group3 from price_group_dw pgd where id = 6) as markup_tier5_3,
 (select group4 from price_group_dw pgd where id = 6) as markup_tier5_4,
 (select group5 from price_group_dw pgd where id = 6) as markup_tier5_5
-from mv_purchase_pricelist mpp
-join product p on p.product_id = mpp.product_id
-left join brand b on b.id = p.brand_id
-where b.id in(10, 122, 89)</sql>
+from purchase_pricelist_dw ppd
+join product p on ppd.product_id = p.product_id
+left join brand b on b.id = p.brand_id</sql>
     <limit>0</limit>
     <lookup/>
     <execute_each_row>N</execute_each_row>
@@ -1442,16 +1353,16 @@ where b.id in(10, 122, 89)</sql>
         <lenient_string_to_number>N</lenient_string_to_number>
       </value-meta>
       <value-meta>
-        <type>BigNumber</type>
+        <type>String</type>
         <storagetype>normal</storagetype>
-        <name>purchase_price</name>
-        <length>-1</length>
+        <name>pricing_group</name>
+        <length>2147483647</length>
         <precision>-1</precision>
         <origin>Table input</origin>
-        <comments>purchase_price</comments>
-        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <comments>pricing_group</comments>
+        <conversion_Mask/>
         <decimal_symbol>.</decimal_symbol>
-        <grouping_symbol/>
+        <grouping_symbol>,</grouping_symbol>
         <currency_symbol/>
         <trim_type>none</trim_type>
         <case_insensitive>N</case_insensitive>
@@ -1465,16 +1376,16 @@ where b.id in(10, 122, 89)</sql>
         <lenient_string_to_number>N</lenient_string_to_number>
       </value-meta>
       <value-meta>
-        <type>String</type>
+        <type>BigNumber</type>
         <storagetype>normal</storagetype>
-        <name>pricing_group</name>
-        <length>2147483647</length>
+        <name>purchase_price</name>
+        <length>-1</length>
         <precision>-1</precision>
         <origin>Table input</origin>
-        <comments>pricing_group</comments>
-        <conversion_Mask/>
+        <comments>purchase_price</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
         <decimal_symbol>.</decimal_symbol>
-        <grouping_symbol>,</grouping_symbol>
+        <grouping_symbol/>
         <currency_symbol/>
         <trim_type>none</trim_type>
         <case_insensitive>N</case_insensitive>
@@ -1947,6 +1858,236 @@ where b.id in(10, 122, 89)</sql>
         <date_format_timezone>Asia/Bangkok</date_format_timezone>
         <lenient_string_to_number>N</lenient_string_to_number>
       </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier4_1</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier4_1</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier4_2</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier4_2</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier4_3</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier4_3</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier4_4</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier4_4</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier4_5</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier4_5</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier5_1</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier5_1</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier5_2</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier5_2</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier5_3</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier5_3</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier5_4</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier5_4</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>markup_tier5_5</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>markup_tier5_5</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_US</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
     </row-meta>
     <attributes/>
     <cluster_schema/>
@@ -1962,6 +2103,846 @@ where b.id in(10, 122, 89)</sql>
       <draw>Y</draw>
     </GUI>
   </step>
+  <step>
+    <name>filter group1</name>
+    <type>FilterRows</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <send_true_to>select group1</send_true_to>
+    <send_false_to>filter group2</send_false_to>
+    <compare>
+      <condition>
+        <negated>N</negated>
+        <leftvalue>pricing_group</leftvalue>
+        <function>=</function>
+        <rightvalue/>
+        <value>
+          <name>constant</name>
+          <type>String</type>
+          <text>group1</text>
+          <length>-1</length>
+          <precision>-1</precision>
+          <isnull>N</isnull>
+          <mask/>
+        </value>
+      </condition>
+    </compare>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>704</xloc>
+      <yloc>64</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>select group1</name>
+    <type>SelectValues</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <fields>
+      <field>
+        <name>product_id</name>
+        <rename/>
+      </field>
+      <field>
+        <name>item_code</name>
+        <rename/>
+      </field>
+      <field>
+        <name>name</name>
+        <rename/>
+      </field>
+      <field>
+        <name>pricing_group</name>
+        <rename/>
+      </field>
+      <field>
+        <name>purchase_price</name>
+        <rename/>
+      </field>
+      <field>
+        <name>markup_std1</name>
+        <rename>markup_std</rename>
+      </field>
+      <field>
+        <name>markup_group1</name>
+        <rename>markup_group</rename>
+      </field>
+      <field>
+        <name>markup_tier2_1</name>
+        <rename>markup_tier2</rename>
+      </field>
+      <field>
+        <name>markup_tier3_1</name>
+        <rename>markup_tier3</rename>
+      </field>
+      <field>
+        <name>markup_tier4_1</name>
+        <rename>markup_tier4</rename>
+      </field>
+      <field>
+        <name>markup_tier5_1</name>
+        <rename>markup_tier5</rename>
+      </field>
+      <field>
+        <name>price_after_disc_1</name>
+        <rename>price_after_disc</rename>
+      </field>
+      <field>
+        <name>price_before_disc1</name>
+        <rename>price_before_disc</rename>
+      </field>
+      <field>
+        <name>price_tier2_1</name>
+        <rename>price_tier2</rename>
+      </field>
+      <field>
+        <name>price_tier3_1</name>
+        <rename>price_tier3</rename>
+      </field>
+      <field>
+        <name>price_tier4_1</name>
+        <rename>price_tier4</rename>
+      </field>
+      <field>
+        <name>price_tier5_1</name>
+        <rename>price_tier5</rename>
+      </field>
+      <select_unspecified>N</select_unspecified>
+    </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>864</xloc>
+      <yloc>64</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>filter group2</name>
+    <type>FilterRows</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <send_true_to>select group2</send_true_to>
+    <send_false_to>filter group3</send_false_to>
+    <compare>
+      <condition>
+        <negated>N</negated>
+        <leftvalue>pricing_group</leftvalue>
+        <function>=</function>
+        <rightvalue/>
+        <value>
+          <name>constant</name>
+          <type>String</type>
+          <text>group2</text>
+          <length>-1</length>
+          <precision>-1</precision>
+          <isnull>N</isnull>
+          <mask/>
+        </value>
+      </condition>
+    </compare>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>704</xloc>
+      <yloc>176</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>select group2</name>
+    <type>SelectValues</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <fields>
+      <field>
+        <name>product_id</name>
+        <rename/>
+      </field>
+      <field>
+        <name>item_code</name>
+        <rename/>
+      </field>
+      <field>
+        <name>name</name>
+        <rename/>
+      </field>
+      <field>
+        <name>pricing_group</name>
+        <rename/>
+      </field>
+      <field>
+        <name>purchase_price</name>
+        <rename/>
+      </field>
+      <field>
+        <name>markup_std2</name>
+        <rename>markup_std</rename>
+      </field>
+      <field>
+        <name>markup_group2</name>
+        <rename>markup_group</rename>
+      </field>
+      <field>
+        <name>markup_tier2_2</name>
+        <rename>markup_tier2</rename>
+      </field>
+      <field>
+        <name>markup_tier3_2</name>
+        <rename>markup_tier3</rename>
+      </field>
+      <field>
+        <name>markup_tier4_2</name>
+        <rename>markup_tier4</rename>
+      </field>
+      <field>
+        <name>markup_tier5_2</name>
+        <rename>markup_tier5</rename>
+      </field>
+      <field>
+        <name>price_after_disc_2</name>
+        <rename>price_after_disc</rename>
+      </field>
+      <field>
+        <name>price_before_disc2</name>
+        <rename>price_before_disc</rename>
+      </field>
+      <field>
+        <name>price_tier2_2</name>
+        <rename>price_tier2</rename>
+      </field>
+      <field>
+        <name>price_tier3_2</name>
+        <rename>price_tier3</rename>
+      </field>
+      <field>
+        <name>price_tier4_2</name>
+        <rename>price_tier4</rename>
+      </field>
+      <field>
+        <name>price_tier5_2</name>
+        <rename>price_tier5</rename>
+      </field>
+      <select_unspecified>N</select_unspecified>
+    </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>864</xloc>
+      <yloc>176</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>filter group3</name>
+    <type>FilterRows</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <send_true_to>select group3</send_true_to>
+    <send_false_to>filter group4</send_false_to>
+    <compare>
+      <condition>
+        <negated>N</negated>
+        <leftvalue>pricing_group</leftvalue>
+        <function>=</function>
+        <rightvalue/>
+        <value>
+          <name>constant</name>
+          <type>String</type>
+          <text>group3</text>
+          <length>-1</length>
+          <precision>-1</precision>
+          <isnull>N</isnull>
+          <mask/>
+        </value>
+      </condition>
+    </compare>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>704</xloc>
+      <yloc>320</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>select group3</name>
+    <type>SelectValues</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <fields>
+      <field>
+        <name>product_id</name>
+        <rename/>
+      </field>
+      <field>
+        <name>item_code</name>
+        <rename/>
+      </field>
+      <field>
+        <name>name</name>
+        <rename/>
+      </field>
+      <field>
+        <name>pricing_group</name>
+        <rename/>
+      </field>
+      <field>
+        <name>purchase_price</name>
+        <rename/>
+      </field>
+      <field>
+        <name>markup_std3</name>
+        <rename>markup_std</rename>
+      </field>
+      <field>
+        <name>markup_group3</name>
+        <rename>markup_group</rename>
+      </field>
+      <field>
+        <name>markup_tier2_3</name>
+        <rename>markup_tier2</rename>
+      </field>
+      <field>
+        <name>markup_tier3_3</name>
+        <rename>markup_tier3</rename>
+      </field>
+      <field>
+        <name>markup_tier4_3</name>
+        <rename>markup_tier4</rename>
+      </field>
+      <field>
+        <name>markup_tier5_3</name>
+        <rename>markup_tier5</rename>
+      </field>
+      <field>
+        <name>price_after_disc_3</name>
+        <rename>price_after_disc</rename>
+      </field>
+      <field>
+        <name>price_before_disc3</name>
+        <rename>price_before_disc</rename>
+      </field>
+      <field>
+        <name>price_tier2_3</name>
+        <rename>price_tier2</rename>
+      </field>
+      <field>
+        <name>price_tier3_3</name>
+        <rename>price_tier3</rename>
+      </field>
+      <field>
+        <name>price_tier4_3</name>
+        <rename>price_tier4</rename>
+      </field>
+      <field>
+        <name>price_tier5_3</name>
+        <rename>price_tier5</rename>
+      </field>
+      <select_unspecified>N</select_unspecified>
+    </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>864</xloc>
+      <yloc>320</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>filter group4</name>
+    <type>FilterRows</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <send_true_to>select group4</send_true_to>
+    <send_false_to>filter group5</send_false_to>
+    <compare>
+      <condition>
+        <negated>N</negated>
+        <leftvalue>pricing_group</leftvalue>
+        <function>=</function>
+        <rightvalue/>
+        <value>
+          <name>constant</name>
+          <type>String</type>
+          <text>group4</text>
+          <length>-1</length>
+          <precision>-1</precision>
+          <isnull>N</isnull>
+          <mask/>
+        </value>
+      </condition>
+    </compare>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>704</xloc>
+      <yloc>432</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>select group4</name>
+    <type>SelectValues</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <fields>
+      <field>
+        <name>product_id</name>
+        <rename/>
+      </field>
+      <field>
+        <name>item_code</name>
+        <rename/>
+      </field>
+      <field>
+        <name>name</name>
+        <rename/>
+      </field>
+      <field>
+        <name>pricing_group</name>
+        <rename/>
+      </field>
+      <field>
+        <name>purchase_price</name>
+        <rename/>
+      </field>
+      <field>
+        <name>markup_std4</name>
+        <rename>markup_std</rename>
+      </field>
+      <field>
+        <name>markup_group4</name>
+        <rename>markup_group</rename>
+      </field>
+      <field>
+        <name>markup_tier2_4</name>
+        <rename>markup_tier2</rename>
+      </field>
+      <field>
+        <name>markup_tier3_4</name>
+        <rename>markup_tier3</rename>
+      </field>
+      <field>
+        <name>markup_tier4_4</name>
+        <rename>markup_tier4</rename>
+      </field>
+      <field>
+        <name>markup_tier5_4</name>
+        <rename>markup_tier5</rename>
+      </field>
+      <field>
+        <name>price_after_disc_4</name>
+        <rename>price_after_disc</rename>
+      </field>
+      <field>
+        <name>price_before_disc4</name>
+        <rename>price_before_disc</rename>
+      </field>
+      <field>
+        <name>price_tier2_4</name>
+        <rename>price_tier2</rename>
+      </field>
+      <field>
+        <name>price_tier3_4</name>
+        <rename>price_tier3</rename>
+      </field>
+      <field>
+        <name>price_tier4_4</name>
+        <rename>price_tier4</rename>
+      </field>
+      <field>
+        <name>price_tier5_4</name>
+        <rename>price_tier5</rename>
+      </field>
+      <select_unspecified>N</select_unspecified>
+    </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>864</xloc>
+      <yloc>432</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>filter group5</name>
+    <type>FilterRows</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <send_true_to>select group5</send_true_to>
+    <send_false_to>Write to log</send_false_to>
+    <compare>
+      <condition>
+        <negated>N</negated>
+        <leftvalue>pricing_group</leftvalue>
+        <function>=</function>
+        <rightvalue/>
+        <value>
+          <name>constant</name>
+          <type>String</type>
+          <text>group5</text>
+          <length>-1</length>
+          <precision>-1</precision>
+          <isnull>N</isnull>
+          <mask/>
+        </value>
+      </condition>
+    </compare>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>704</xloc>
+      <yloc>544</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>select group5</name>
+    <type>SelectValues</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <fields>
+      <field>
+        <name>product_id</name>
+        <rename/>
+      </field>
+      <field>
+        <name>item_code</name>
+        <rename/>
+      </field>
+      <field>
+        <name>name</name>
+        <rename/>
+      </field>
+      <field>
+        <name>pricing_group</name>
+        <rename/>
+      </field>
+      <field>
+        <name>purchase_price</name>
+        <rename/>
+      </field>
+      <field>
+        <name>markup_std5</name>
+        <rename>markup_std</rename>
+      </field>
+      <field>
+        <name>markup_group5</name>
+        <rename>markup_group</rename>
+      </field>
+      <field>
+        <name>markup_tier2_5</name>
+        <rename>markup_tier2</rename>
+      </field>
+      <field>
+        <name>markup_tier3_5</name>
+        <rename>markup_tier3</rename>
+      </field>
+      <field>
+        <name>markup_tier4_5</name>
+        <rename>markup_tier4</rename>
+      </field>
+      <field>
+        <name>markup_tier5_5</name>
+        <rename>markup_tier5</rename>
+      </field>
+      <field>
+        <name>price_after_disc_5</name>
+        <rename>price_after_disc</rename>
+      </field>
+      <field>
+        <name>price_before_disc5</name>
+        <rename>price_before_disc</rename>
+      </field>
+      <field>
+        <name>price_tier2_5</name>
+        <rename>price_tier2</rename>
+      </field>
+      <field>
+        <name>price_tier3_5</name>
+        <rename>price_tier3</rename>
+      </field>
+      <field>
+        <name>price_tier4_5</name>
+        <rename>price_tier4</rename>
+      </field>
+      <field>
+        <name>price_tier5_5</name>
+        <rename>price_tier5</rename>
+      </field>
+      <select_unspecified>N</select_unspecified>
+    </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>864</xloc>
+      <yloc>544</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Write to log</name>
+    <type>WriteToLog</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <loglevel>log_level_basic</loglevel>
+    <displayHeader>Y</displayHeader>
+    <limitRows>N</limitRows>
+    <limitRowsNumber>0</limitRowsNumber>
+    <logmessage/>
+    <fields>
+      </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>544</xloc>
+      <yloc>544</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Append streams</name>
+    <type>Append</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <head_name>select group1</head_name>
+    <tail_name>select group2</tail_name>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>1104</xloc>
+      <yloc>176</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Append streams 2</name>
+    <type>Append</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <head_name>Append streams</head_name>
+    <tail_name>select group3</tail_name>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>1104</xloc>
+      <yloc>320</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Append streams 3</name>
+    <type>Append</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <head_name>Append streams 2</head_name>
+    <tail_name>select group4</tail_name>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>1104</xloc>
+      <yloc>432</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Append streams 4</name>
+    <type>Append</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <head_name>Append streams 3</head_name>
+    <tail_name>select group5</tail_name>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>1104</xloc>
+      <yloc>544</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
   <step_error_handling>
   </step_error_handling>
   <slave-step-copy-partition-distribution>