From 10225d3a01dd5ca934e5db7ea9146301f2adfd2d Mon Sep 17 00:00:00 2001
From: FIN-IT_AndriFP <it@fixcomart.co.id>
Date: Mon, 17 Nov 2025 10:51:48 +0700
Subject: (andri) ubah status so_to_po leadtime_so_to_po_working_hour dari 3
 jam ke 6 jam

---
 calculate_leadtime_so_to_po.ktr | 851 ++++++++++++++++++++--------------------
 1 file changed, 416 insertions(+), 435 deletions(-)

diff --git a/calculate_leadtime_so_to_po.ktr b/calculate_leadtime_so_to_po.ktr
index fe76ef8..931bf2e 100644
--- a/calculate_leadtime_so_to_po.ktr
+++ b/calculate_leadtime_so_to_po.ktr
@@ -431,7 +431,7 @@
     <created_date>2025/07/14 14:00:11.471</created_date>
     <modified_user>-</modified_user>
     <modified_date>2025/07/14 14:00:11.471</modified_date>
-    <key_for_session_key/>
+    <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key>
     <is_key_private>N</is_key_private>
   </info>
   <notepads>
@@ -555,10 +555,10 @@
     </hop>
   </order>
   <step>
-    <name>Table input</name>
-    <type>TableInput</type>
+    <name>Insert / update</name>
+    <type>InsertUpdate</type>
     <description/>
-    <distribute>N</distribute>
+    <distribute>Y</distribute>
     <custom_distribution/>
     <copies>1</copies>
     <partitioning>
@@ -566,110 +566,48 @@
       <schema_name/>
     </partitioning>
     <connection>dw bi indoteknik (localhost)</connection>
-    <sql>select so_confirm_date, po_confirm_date, sale_order_line_id, sale_order_id
-from mv_leadtime_so_to_delivered
-where 1=1
-and so_to_po >= 0</sql>
-    <limit>0</limit>
-    <lookup/>
-    <execute_each_row>N</execute_each_row>
-    <variables_active>N</variables_active>
-    <lazy_conversion_active>N</lazy_conversion_active>
-    <cached_row_meta_active>N</cached_row_meta_active>
-    <row-meta>
-      <value-meta>
-        <type>Timestamp</type>
-        <storagetype>normal</storagetype>
+    <commit>100</commit>
+    <update_bypassed>N</update_bypassed>
+    <lookup>
+      <schema>public</schema>
+      <table>leadtime_so_to_po_working_hour</table>
+      <key>
+        <name>sale_order_line_id</name>
+        <field>sale_order_line_id</field>
+        <condition>=</condition>
+        <name2/>
+      </key>
+      <value>
         <name>so_confirm_date</name>
-        <length>6</length>
-        <precision>-1</precision>
-        <origin>Table input</origin>
-        <comments>so_confirm_date</comments>
-        <conversion_Mask/>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>Timestamp</type>
-        <storagetype>normal</storagetype>
+        <rename>so_confirm_date</rename>
+        <update>Y</update>
+      </value>
+      <value>
         <name>po_confirm_date</name>
-        <length>6</length>
-        <precision>-1</precision>
-        <origin>Table input</origin>
-        <comments>po_confirm_date</comments>
-        <conversion_Mask/>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>Integer</type>
-        <storagetype>normal</storagetype>
+        <rename>po_confirm_date</rename>
+        <update>Y</update>
+      </value>
+      <value>
         <name>sale_order_line_id</name>
-        <length>9</length>
-        <precision>0</precision>
-        <origin>Table input</origin>
-        <comments>sale_order_line_id</comments>
-        <conversion_Mask>####0;-####0</conversion_Mask>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>Integer</type>
-        <storagetype>normal</storagetype>
+        <rename>sale_order_line_id</rename>
+        <update>N</update>
+      </value>
+      <value>
         <name>sale_order_id</name>
-        <length>9</length>
-        <precision>0</precision>
-        <origin>Table input</origin>
-        <comments>sale_order_id</comments>
-        <conversion_Mask>####0;-####0</conversion_Mask>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>
+        <rename>sale_order_id</rename>
+        <update>Y</update>
+      </value>
+      <value>
+        <name>working_hours</name>
+        <rename>working_hours</rename>
+        <update>Y</update>
+      </value>
+      <value>
+        <name>so_to_po_status</name>
+        <rename>so_to_po_status</rename>
+        <update>Y</update>
+      </value>
+    </lookup>
     <attributes/>
     <cluster_schema/>
     <remotesteps>
@@ -679,7 +617,94 @@ and so_to_po >= 0</sql>
       </output>
     </remotesteps>
     <GUI>
-      <xloc>96</xloc>
+      <xloc>816</xloc>
+      <yloc>144</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Merge join</name>
+    <type>MergeJoin</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <join_type>LEFT OUTER</join_type>
+    <step1>Sort rows</step1>
+    <step2>Sort rows 2</step2>
+    <keys_1>
+      <key>sale_order_line_id</key>
+    </keys_1>
+    <keys_2>
+      <key>sale_order_line_id</key>
+    </keys_2>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>384</xloc>
+      <yloc>240</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Modified JavaScript value</name>
+    <type>ScriptValueMod</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <compatible>N</compatible>
+    <optimizationLevel>9</optimizationLevel>
+    <jsScripts>
+      <jsScript>
+        <jsScript_type>0</jsScript_type>
+        <jsScript_name>Script 1</jsScript_name>
+        <jsScript_script>//Script here
+var so_to_po_status = 'not achieve';
+if (working_hours &lt;= 6)
+{
+	so_to_po_status = 'achieve'
+}
+else{
+	so_to_po_status = 'not achieve'
+}
+</jsScript_script>
+      </jsScript>
+    </jsScripts>
+    <fields>
+      <field>
+        <name>so_to_po_status</name>
+        <rename>so_to_po_status</rename>
+        <type>String</type>
+        <length>-1</length>
+        <precision>-1</precision>
+        <replace>N</replace>
+      </field>
+    </fields>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>656</xloc>
       <yloc>48</yloc>
       <draw>Y</draw>
     </GUI>
@@ -698,19 +723,15 @@ and so_to_po >= 0</sql>
     <fields>
       <field>
         <name>so_confirm_date</name>
-        <rename/>
       </field>
       <field>
         <name>po_confirm_date</name>
-        <rename/>
       </field>
       <field>
         <name>sale_order_line_id</name>
-        <rename/>
       </field>
       <field>
         <name>sale_order_id</name>
-        <rename/>
       </field>
       <select_unspecified>N</select_unspecified>
     </fields>
@@ -742,19 +763,15 @@ and so_to_po >= 0</sql>
     <fields>
       <field>
         <name>so_confirm_date</name>
-        <rename/>
       </field>
       <field>
         <name>po_confirm_date</name>
-        <rename/>
       </field>
       <field>
         <name>sale_order_line_id</name>
-        <rename/>
       </field>
       <field>
         <name>sale_order_id</name>
-        <rename/>
       </field>
       <select_unspecified>N</select_unspecified>
     </fields>
@@ -773,8 +790,8 @@ and so_to_po >= 0</sql>
     </GUI>
   </step>
   <step>
-    <name>Table input 2</name>
-    <type>TableInput</type>
+    <name>Select values 3</name>
+    <type>SelectValues</type>
     <description/>
     <distribute>Y</distribute>
     <custom_distribution/>
@@ -783,109 +800,24 @@ and so_to_po >= 0</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik (localhost)</connection>
-    <sql>WITH params AS (
-    SELECT
-        ?::timestamp AS start_time,
-        ?::timestamp AS end_time
-),
-hours AS (
-    SELECT generate_series(
-        (SELECT start_time FROM params),
-        (SELECT end_time FROM params),
-        interval '1 hour'
-    ) AS ts
-),
-filtered_hours AS (
-    SELECT ts
-    FROM hours
-    WHERE
-        EXTRACT(HOUR FROM ts) BETWEEN 8 AND 16
-        AND (
-            EXTRACT(HOUR FROM ts) > 8 OR EXTRACT(MINUTE FROM ts) >= 30
-        )
-        AND EXTRACT(DOW FROM ts) NOT IN (0)
-        AND DATE(ts) NOT IN (SELECT holiday_date FROM holidays)
-)
-SELECT COUNT(*) AS working_hours, ? as sale_order_line_id, ? as sale_order_id
-FROM filtered_hours;</sql>
-    <limit>0</limit>
-    <lookup>Select values</lookup>
-    <execute_each_row>Y</execute_each_row>
-    <variables_active>Y</variables_active>
-    <lazy_conversion_active>N</lazy_conversion_active>
-    <cached_row_meta_active>N</cached_row_meta_active>
-    <row-meta>
-      <value-meta>
-        <type>Integer</type>
-        <storagetype>normal</storagetype>
-        <name>working_hours</name>
-        <length>15</length>
-        <precision>0</precision>
-        <origin>Table input 2</origin>
-        <comments>working_hours</comments>
-        <conversion_Mask>####0;-####0</conversion_Mask>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>String</type>
-        <storagetype>normal</storagetype>
+    <fields>
+      <field>
+        <name>so_confirm_date</name>
+      </field>
+      <field>
+        <name>po_confirm_date</name>
+      </field>
+      <field>
         <name>sale_order_line_id</name>
-        <length>2147483647</length>
-        <precision>-1</precision>
-        <origin>Table input 2</origin>
-        <comments>sale_order_line_id</comments>
-        <conversion_Mask/>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>String</type>
-        <storagetype>normal</storagetype>
+      </field>
+      <field>
         <name>sale_order_id</name>
-        <length>2147483647</length>
-        <precision>-1</precision>
-        <origin>Table input 2</origin>
-        <comments>sale_order_id</comments>
-        <conversion_Mask/>
-        <decimal_symbol>.</decimal_symbol>
-        <grouping_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>
+      </field>
+      <field>
+        <name>working_hours</name>
+      </field>
+      <select_unspecified>N</select_unspecified>
+    </fields>
     <attributes/>
     <cluster_schema/>
     <remotesteps>
@@ -895,14 +827,14 @@ FROM filtered_hours;</sql>
       </output>
     </remotesteps>
     <GUI>
-      <xloc>384</xloc>
-      <yloc>48</yloc>
+      <xloc>496</xloc>
+      <yloc>240</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
   <step>
-    <name>Sort rows</name>
-    <type>SortRows</type>
+    <name>Select values 4</name>
+    <type>SelectValues</type>
     <description/>
     <distribute>Y</distribute>
     <custom_distribution/>
@@ -911,22 +843,26 @@ FROM filtered_hours;</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <directory>%%java.io.tmpdir%%</directory>
-    <prefix>out</prefix>
-    <sort_size>1000000</sort_size>
-    <free_memory/>
-    <compress>N</compress>
-    <compress_variable/>
-    <unique_rows>N</unique_rows>
     <fields>
+      <field>
+        <name>so_confirm_date</name>
+      </field>
+      <field>
+        <name>po_confirm_date</name>
+      </field>
       <field>
         <name>sale_order_line_id</name>
-        <ascending>Y</ascending>
-        <case_sensitive>N</case_sensitive>
-        <collator_enabled>N</collator_enabled>
-        <collator_strength>0</collator_strength>
-        <presorted>N</presorted>
       </field>
+      <field>
+        <name>sale_order_id</name>
+      </field>
+      <field>
+        <name>working_hours</name>
+      </field>
+      <field>
+        <name>so_to_po_status</name>
+      </field>
+      <select_unspecified>N</select_unspecified>
     </fields>
     <attributes/>
     <cluster_schema/>
@@ -937,13 +873,13 @@ FROM filtered_hours;</sql>
       </output>
     </remotesteps>
     <GUI>
-      <xloc>96</xloc>
-      <yloc>240</yloc>
+      <xloc>816</xloc>
+      <yloc>48</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
   <step>
-    <name>Sort rows 2</name>
+    <name>Sort rows</name>
     <type>SortRows</type>
     <description/>
     <distribute>Y</distribute>
@@ -979,96 +915,14 @@ FROM filtered_hours;</sql>
       </output>
     </remotesteps>
     <GUI>
-      <xloc>384</xloc>
-      <yloc>144</yloc>
-      <draw>Y</draw>
-    </GUI>
-  </step>
-  <step>
-    <name>Merge join</name>
-    <type>MergeJoin</type>
-    <description/>
-    <distribute>Y</distribute>
-    <custom_distribution/>
-    <copies>1</copies>
-    <partitioning>
-      <method>none</method>
-      <schema_name/>
-    </partitioning>
-    <join_type>LEFT OUTER</join_type>
-    <step1>Sort rows</step1>
-    <step2>Sort rows 2</step2>
-    <keys_1>
-      <key>sale_order_line_id</key>
-    </keys_1>
-    <keys_2>
-      <key>sale_order_line_id</key>
-    </keys_2>
-    <attributes/>
-    <cluster_schema/>
-    <remotesteps>
-      <input>
-      </input>
-      <output>
-      </output>
-    </remotesteps>
-    <GUI>
-      <xloc>384</xloc>
-      <yloc>240</yloc>
-      <draw>Y</draw>
-    </GUI>
-  </step>
-  <step>
-    <name>Select values 3</name>
-    <type>SelectValues</type>
-    <description/>
-    <distribute>Y</distribute>
-    <custom_distribution/>
-    <copies>1</copies>
-    <partitioning>
-      <method>none</method>
-      <schema_name/>
-    </partitioning>
-    <fields>
-      <field>
-        <name>so_confirm_date</name>
-        <rename/>
-      </field>
-      <field>
-        <name>po_confirm_date</name>
-        <rename/>
-      </field>
-      <field>
-        <name>sale_order_line_id</name>
-        <rename/>
-      </field>
-      <field>
-        <name>sale_order_id</name>
-        <rename/>
-      </field>
-      <field>
-        <name>working_hours</name>
-        <rename/>
-      </field>
-      <select_unspecified>N</select_unspecified>
-    </fields>
-    <attributes/>
-    <cluster_schema/>
-    <remotesteps>
-      <input>
-      </input>
-      <output>
-      </output>
-    </remotesteps>
-    <GUI>
-      <xloc>496</xloc>
+      <xloc>96</xloc>
       <yloc>240</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
   <step>
-    <name>Unique rows</name>
-    <type>Unique</type>
+    <name>Sort rows 2</name>
+    <type>SortRows</type>
     <description/>
     <distribute>Y</distribute>
     <custom_distribution/>
@@ -1077,18 +931,21 @@ FROM filtered_hours;</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <count_rows>N</count_rows>
-    <count_field/>
-    <reject_duplicate_row>N</reject_duplicate_row>
-    <error_description/>
+    <directory>%%java.io.tmpdir%%</directory>
+    <prefix>out</prefix>
+    <sort_size>1000000</sort_size>
+    <free_memory/>
+    <compress>N</compress>
+    <compress_variable/>
+    <unique_rows>N</unique_rows>
     <fields>
       <field>
         <name>sale_order_line_id</name>
-        <case_insensitive>N</case_insensitive>
-      </field>
-      <field>
-        <name>sale_order_id</name>
-        <case_insensitive>N</case_insensitive>
+        <ascending>Y</ascending>
+        <case_sensitive>N</case_sensitive>
+        <collator_enabled>N</collator_enabled>
+        <collator_strength>0</collator_strength>
+        <presorted>N</presorted>
       </field>
     </fields>
     <attributes/>
@@ -1100,8 +957,8 @@ FROM filtered_hours;</sql>
       </output>
     </remotesteps>
     <GUI>
-      <xloc>496</xloc>
-      <yloc>48</yloc>
+      <xloc>384</xloc>
+      <yloc>144</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
@@ -1156,44 +1013,121 @@ FROM filtered_hours;</sql>
     </GUI>
   </step>
   <step>
-    <name>Modified JavaScript value</name>
-    <type>ScriptValueMod</type>
+    <name>Table input</name>
+    <type>TableInput</type>
     <description/>
-    <distribute>Y</distribute>
+    <distribute>N</distribute>
     <custom_distribution/>
     <copies>1</copies>
     <partitioning>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <compatible>N</compatible>
-    <optimizationLevel>9</optimizationLevel>
-    <jsScripts>
-      <jsScript>
-        <jsScript_type>0</jsScript_type>
-        <jsScript_name>Script 1</jsScript_name>
-        <jsScript_script>//Script here
-var so_to_po_status = 'not achieve';
-if (working_hours &lt;= 3)
-{
-	so_to_po_status = 'achieve'
-}
-else{
-	so_to_po_status = 'not achieve'
-}
-</jsScript_script>
-      </jsScript>
-    </jsScripts>
-    <fields>
-      <field>
-        <name>so_to_po_status</name>
-        <rename>so_to_po_status</rename>
-        <type>String</type>
-        <length>-1</length>
+    <connection>dw bi indoteknik (localhost)</connection>
+    <sql>select so_confirm_date, po_confirm_date, sale_order_line_id, sale_order_id
+from mv_leadtime_so_to_delivered
+where 1=1
+and so_to_po >= 0</sql>
+    <limit>0</limit>
+    <lookup/>
+    <execute_each_row>N</execute_each_row>
+    <variables_active>N</variables_active>
+    <lazy_conversion_active>N</lazy_conversion_active>
+    <cached_row_meta_active>N</cached_row_meta_active>
+    <row-meta>
+      <value-meta>
+        <type>Timestamp</type>
+        <storagetype>normal</storagetype>
+        <name>so_confirm_date</name>
+        <length>6</length>
         <precision>-1</precision>
-        <replace>N</replace>
-      </field>
-    </fields>
+        <origin>Table input</origin>
+        <comments>so_confirm_date</comments>
+        <conversion_Mask/>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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>Timestamp</type>
+        <storagetype>normal</storagetype>
+        <name>po_confirm_date</name>
+        <length>6</length>
+        <precision>-1</precision>
+        <origin>Table input</origin>
+        <comments>po_confirm_date</comments>
+        <conversion_Mask/>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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>Integer</type>
+        <storagetype>normal</storagetype>
+        <name>sale_order_line_id</name>
+        <length>9</length>
+        <precision>0</precision>
+        <origin>Table input</origin>
+        <comments>sale_order_line_id</comments>
+        <conversion_Mask>####0;-####0</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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>Integer</type>
+        <storagetype>normal</storagetype>
+        <name>sale_order_id</name>
+        <length>9</length>
+        <precision>0</precision>
+        <origin>Table input</origin>
+        <comments>sale_order_id</comments>
+        <conversion_Mask>####0;-####0</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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/>
     <remotesteps>
@@ -1203,14 +1137,14 @@ else{
       </output>
     </remotesteps>
     <GUI>
-      <xloc>656</xloc>
+      <xloc>96</xloc>
       <yloc>48</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
   <step>
-    <name>Select values 4</name>
-    <type>SelectValues</type>
+    <name>Table input 2</name>
+    <type>TableInput</type>
     <description/>
     <distribute>Y</distribute>
     <custom_distribution/>
@@ -1219,33 +1153,109 @@ else{
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <fields>
-      <field>
-        <name>so_confirm_date</name>
-        <rename/>
-      </field>
-      <field>
-        <name>po_confirm_date</name>
-        <rename/>
-      </field>
-      <field>
+    <connection>dw bi indoteknik (localhost)</connection>
+    <sql>WITH params AS (
+    SELECT
+        ?::timestamp AS start_time,
+        ?::timestamp AS end_time
+),
+hours AS (
+    SELECT generate_series(
+        (SELECT start_time FROM params),
+        (SELECT end_time FROM params),
+        interval '1 hour'
+    ) AS ts
+),
+filtered_hours AS (
+    SELECT ts
+    FROM hours
+    WHERE
+        EXTRACT(HOUR FROM ts) BETWEEN 8 AND 16
+        AND (
+            EXTRACT(HOUR FROM ts) > 8 OR EXTRACT(MINUTE FROM ts) >= 30
+        )
+        AND EXTRACT(DOW FROM ts) NOT IN (0)
+        AND DATE(ts) NOT IN (SELECT holiday_date FROM holidays)
+)
+SELECT COUNT(*) AS working_hours, ? as sale_order_line_id, ? as sale_order_id
+FROM filtered_hours;</sql>
+    <limit>0</limit>
+    <lookup>Select values</lookup>
+    <execute_each_row>Y</execute_each_row>
+    <variables_active>Y</variables_active>
+    <lazy_conversion_active>N</lazy_conversion_active>
+    <cached_row_meta_active>N</cached_row_meta_active>
+    <row-meta>
+      <value-meta>
+        <type>Integer</type>
+        <storagetype>normal</storagetype>
+        <name>working_hours</name>
+        <length>15</length>
+        <precision>0</precision>
+        <origin>Table input 2</origin>
+        <comments>working_hours</comments>
+        <conversion_Mask>####0;-####0</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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>String</type>
+        <storagetype>normal</storagetype>
         <name>sale_order_line_id</name>
-        <rename/>
-      </field>
-      <field>
+        <length>2147483647</length>
+        <precision>-1</precision>
+        <origin>Table input 2</origin>
+        <comments>sale_order_line_id</comments>
+        <conversion_Mask/>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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>String</type>
+        <storagetype>normal</storagetype>
         <name>sale_order_id</name>
-        <rename/>
-      </field>
-      <field>
-        <name>working_hours</name>
-        <rename/>
-      </field>
-      <field>
-        <name>so_to_po_status</name>
-        <rename/>
-      </field>
-      <select_unspecified>N</select_unspecified>
-    </fields>
+        <length>2147483647</length>
+        <precision>-1</precision>
+        <origin>Table input 2</origin>
+        <comments>sale_order_id</comments>
+        <conversion_Mask/>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_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/>
     <remotesteps>
@@ -1255,14 +1265,14 @@ else{
       </output>
     </remotesteps>
     <GUI>
-      <xloc>816</xloc>
+      <xloc>384</xloc>
       <yloc>48</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
   <step>
-    <name>Insert / update</name>
-    <type>InsertUpdate</type>
+    <name>Unique rows</name>
+    <type>Unique</type>
     <description/>
     <distribute>Y</distribute>
     <custom_distribution/>
@@ -1271,49 +1281,20 @@ else{
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik (localhost)</connection>
-    <commit>100</commit>
-    <update_bypassed>N</update_bypassed>
-    <lookup>
-      <schema>public</schema>
-      <table>leadtime_so_to_po_working_hour</table>
-      <key>
-        <name>sale_order_line_id</name>
-        <field>sale_order_line_id</field>
-        <condition>=</condition>
-        <name2/>
-      </key>
-      <value>
-        <name>so_confirm_date</name>
-        <rename>so_confirm_date</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>po_confirm_date</name>
-        <rename>po_confirm_date</rename>
-        <update>Y</update>
-      </value>
-      <value>
+    <count_rows>N</count_rows>
+    <count_field/>
+    <reject_duplicate_row>N</reject_duplicate_row>
+    <error_description/>
+    <fields>
+      <field>
         <name>sale_order_line_id</name>
-        <rename>sale_order_line_id</rename>
-        <update>N</update>
-      </value>
-      <value>
+        <case_insensitive>N</case_insensitive>
+      </field>
+      <field>
         <name>sale_order_id</name>
-        <rename>sale_order_id</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>working_hours</name>
-        <rename>working_hours</rename>
-        <update>Y</update>
-      </value>
-      <value>
-        <name>so_to_po_status</name>
-        <rename>so_to_po_status</rename>
-        <update>Y</update>
-      </value>
-    </lookup>
+        <case_insensitive>N</case_insensitive>
+      </field>
+    </fields>
     <attributes/>
     <cluster_schema/>
     <remotesteps>
@@ -1323,8 +1304,8 @@ else{
       </output>
     </remotesteps>
     <GUI>
-      <xloc>816</xloc>
-      <yloc>144</yloc>
+      <xloc>496</xloc>
+      <yloc>48</yloc>
       <draw>Y</draw>
     </GUI>
   </step>
-- 
cgit v1.2.3