diff options
Diffstat (limited to 'calculate_leadtime_po_to_mr.ktr')
| -rw-r--r-- | calculate_leadtime_po_to_mr.ktr | 948 |
1 files changed, 538 insertions, 410 deletions
diff --git a/calculate_leadtime_po_to_mr.ktr b/calculate_leadtime_po_to_mr.ktr index cabbe51..438a88b 100644 --- a/calculate_leadtime_po_to_mr.ktr +++ b/calculate_leadtime_po_to_mr.ktr @@ -430,7 +430,7 @@ <created_date>2025/07/28 14:26:08.522</created_date> <modified_user>-</modified_user> <modified_date>2025/07/28 14:26:08.522</modified_date> - <key_for_session_key/> + <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key> <is_key_private>N</is_key_private> </info> <notepads> @@ -554,10 +554,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> @@ -565,108 +565,65 @@ <schema_name/> </partitioning> <connection>dw bi indoteknik (localhost)</connection> - <sql>select po_confirm_date, received_good_date, purchase_line_id, purchase_id -from mv_leadtime_po_to_received</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_po_to_received_working_hour</table> + <key> + <name>purchase_line_id</name> + <field>purchase_line_id</field> + <condition>=</condition> + <name2/> + </key> + <key> + <name>purchase_id</name> + <field>purchase_id</field> + <condition>=</condition> + <name2/> + </key> + <key> + <name>received_id</name> + <field>received_id</field> + <condition>=</condition> + <name2/> + </key> + <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>Timestamp</type> - <storagetype>normal</storagetype> + <rename>po_confirm_date</rename> + <update>Y</update> + </value> + <value> <name>received_good_date</name> - <length>6</length> - <precision>-1</precision> - <origin>Table input</origin> - <comments>received_good_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>received_good_date</rename> + <update>Y</update> + </value> + <value> <name>purchase_line_id</name> - <length>9</length> - <precision>0</precision> - <origin>Table input</origin> - <comments>purchase_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>purchase_line_id</rename> + <update>N</update> + </value> + <value> <name>purchase_id</name> - <length>9</length> - <precision>0</precision> - <origin>Table input</origin> - <comments>purchase_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>purchase_id</rename> + <update>N</update> + </value> + <value> + <name>new_po_to_arrival</name> + <rename>new_po_to_arrival</rename> + <update>Y</update> + </value> + <value> + <name>po_to_arrival_status</name> + <rename>po_to_arrival_status</rename> + <update>Y</update> + </value> + <value> + <name>received_id</name> + <rename>received_id</rename> + <update>N</update> + </value> + </lookup> <attributes/> <cluster_schema/> <remotesteps> @@ -676,14 +633,14 @@ from mv_leadtime_po_to_received</sql> </output> </remotesteps> <GUI> - <xloc>176</xloc> - <yloc>80</yloc> + <xloc>768</xloc> + <yloc>304</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values</name> - <type>SelectValues</type> + <name>Merge join</name> + <type>MergeJoin</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -692,25 +649,19 @@ from mv_leadtime_po_to_received</sql> <method>none</method> <schema_name/> </partitioning> - <fields> - <field> - <name>po_confirm_date</name> - <rename/> - </field> - <field> - <name>received_good_date</name> - <rename/> - </field> - <field> - <name>purchase_line_id</name> - <rename/> - </field> - <field> - <name>purchase_id</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> + <join_type>INNER</join_type> + <step1>Sort rows</step1> + <step2>Sort rows 2</step2> + <keys_1> + <key>purchase_line_id</key> + <key>purchase_id</key> + <key>received_id</key> + </keys_1> + <keys_2> + <key>purchase_line_id</key> + <key>purchase_id</key> + <key>received_id</key> + </keys_2> <attributes/> <cluster_schema/> <remotesteps> @@ -720,14 +671,14 @@ from mv_leadtime_po_to_received</sql> </output> </remotesteps> <GUI> - <xloc>320</xloc> - <yloc>80</yloc> + <xloc>464</xloc> + <yloc>304</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Table input 2</name> - <type>TableInput</type> + <name>Modified JavaScript value</name> + <type>ScriptValueMod</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -736,109 +687,34 @@ from mv_leadtime_po_to_received</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 new_po_to_arrival, ? as purchase_line_id, ? as purchase_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>new_po_to_arrival</name> - <length>15</length> - <precision>0</precision> - <origin>Table input 2</origin> - <comments>new_po_to_arrival</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>purchase_line_id</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>Table input 2</origin> - <comments>purchase_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> + <compatible>N</compatible> + <optimizationLevel>9</optimizationLevel> + <jsScripts> + <jsScript> + <jsScript_type>0</jsScript_type> + <jsScript_name>Script 1</jsScript_name> + <jsScript_script>//Script here +var po_to_arrival_status = 'not achieve'; +if (new_po_to_arrival <= 16) +{ + po_to_arrival_status = 'achieve' +} +else{ + po_to_arrival_status = 'not achieve' +} +</jsScript_script> + </jsScript> + </jsScripts> + <fields> + <field> + <name>po_to_arrival_status</name> + <rename>po_to_arrival_status</rename> <type>String</type> - <storagetype>normal</storagetype> - <name>purchase_id</name> - <length>2147483647</length> + <length>-1</length> <precision>-1</precision> - <origin>Table input 2</origin> - <comments>purchase_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> + <replace>N</replace> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -848,13 +724,13 @@ FROM filtered_hours;</sql> </output> </remotesteps> <GUI> - <xloc>464</xloc> + <xloc>768</xloc> <yloc>80</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values 2</name> + <name>Select values</name> <type>SelectValues</type> <description/> <distribute>Y</distribute> @@ -881,6 +757,10 @@ FROM filtered_hours;</sql> <name>purchase_id</name> <rename/> </field> + <field> + <name>received_id</name> + <rename/> + </field> <select_unspecified>N</select_unspecified> </fields> <attributes/> @@ -892,14 +772,14 @@ FROM filtered_hours;</sql> </output> </remotesteps> <GUI> - <xloc>176</xloc> - <yloc>192</yloc> + <xloc>320</xloc> + <yloc>80</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows</name> - <type>SortRows</type> + <name>Select values 2</name> + <type>SelectValues</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -908,22 +788,28 @@ 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>po_confirm_date</name> + <rename/> + </field> + <field> + <name>received_good_date</name> + <rename/> + </field> + <field> <name>purchase_line_id</name> - <ascending>Y</ascending> - <case_sensitive>N</case_sensitive> - <collator_enabled>N</collator_enabled> - <collator_strength>0</collator_strength> - <presorted>N</presorted> + <rename/> </field> + <field> + <name>purchase_id</name> + <rename/> + </field> + <field> + <name>received_id</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> </fields> <attributes/> <cluster_schema/> @@ -935,13 +821,13 @@ FROM filtered_hours;</sql> </remotesteps> <GUI> <xloc>176</xloc> - <yloc>304</yloc> + <yloc>192</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 2</name> - <type>SortRows</type> + <name>Select values 3</name> + <type>SelectValues</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -950,22 +836,32 @@ 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>po_confirm_date</name> + <rename/> + </field> + <field> + <name>received_good_date</name> + <rename/> + </field> + <field> <name>purchase_line_id</name> - <ascending>Y</ascending> - <case_sensitive>N</case_sensitive> - <collator_enabled>N</collator_enabled> - <collator_strength>0</collator_strength> - <presorted>N</presorted> + <rename/> + </field> + <field> + <name>purchase_id</name> + <rename/> + </field> + <field> + <name>new_po_to_arrival</name> + <rename/> + </field> + <field> + <name>received_id</name> + <rename/> </field> + <select_unspecified>N</select_unspecified> </fields> <attributes/> <cluster_schema/> @@ -976,14 +872,14 @@ FROM filtered_hours;</sql> </output> </remotesteps> <GUI> - <xloc>464</xloc> - <yloc>192</yloc> + <xloc>592</xloc> + <yloc>304</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Merge join</name> - <type>MergeJoin</type> + <name>Select values 4</name> + <type>SelectValues</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -992,15 +888,37 @@ FROM filtered_hours;</sql> <method>none</method> <schema_name/> </partitioning> - <join_type>INNER</join_type> - <step1>Sort rows</step1> - <step2>Sort rows 2</step2> - <keys_1> - <key>purchase_line_id</key> - </keys_1> - <keys_2> - <key>purchase_line_id</key> - </keys_2> + <fields> + <field> + <name>po_confirm_date</name> + <rename/> + </field> + <field> + <name>received_good_date</name> + <rename/> + </field> + <field> + <name>purchase_line_id</name> + <rename/> + </field> + <field> + <name>purchase_id</name> + <rename/> + </field> + <field> + <name>new_po_to_arrival</name> + <rename/> + </field> + <field> + <name>po_to_arrival_status</name> + <rename/> + </field> + <field> + <name>received_id</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1010,14 +928,14 @@ FROM filtered_hours;</sql> </output> </remotesteps> <GUI> - <xloc>464</xloc> - <yloc>304</yloc> + <xloc>768</xloc> + <yloc>192</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values 3</name> - <type>SelectValues</type> + <name>Sort rows</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1026,28 +944,38 @@ 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>po_confirm_date</name> - <rename/> - </field> - <field> - <name>received_good_date</name> - <rename/> - </field> - <field> <name>purchase_line_id</name> - <rename/> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> </field> <field> <name>purchase_id</name> - <rename/> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> </field> <field> - <name>new_po_to_arrival</name> - <rename/> + <name>received_id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> </field> - <select_unspecified>N</select_unspecified> </fields> <attributes/> <cluster_schema/> @@ -1058,13 +986,13 @@ FROM filtered_hours;</sql> </output> </remotesteps> <GUI> - <xloc>592</xloc> + <xloc>176</xloc> <yloc>304</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 3</name> + <name>Sort rows 2</name> <type>SortRows</type> <description/> <distribute>Y</distribute> @@ -1098,6 +1026,14 @@ FROM filtered_hours;</sql> <collator_strength>0</collator_strength> <presorted>N</presorted> </field> + <field> + <name>received_id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> + </field> </fields> <attributes/> <cluster_schema/> @@ -1108,14 +1044,14 @@ FROM filtered_hours;</sql> </output> </remotesteps> <GUI> - <xloc>592</xloc> + <xloc>464</xloc> <yloc>192</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Unique rows</name> - <type>Unique</type> + <name>Sort rows 3</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1124,18 +1060,37 @@ 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>purchase_line_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> <field> <name>purchase_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> + <field> + <name>received_id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> </field> </fields> <attributes/> @@ -1148,49 +1103,148 @@ FROM filtered_hours;</sql> </remotesteps> <GUI> <xloc>592</xloc> - <yloc>80</yloc> + <yloc>192</yloc> <draw>Y</draw> </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 po_to_arrival_status = 'not achieve'; -if (new_po_to_arrival <= 16) -{ - po_to_arrival_status = 'achieve' -} -else{ - po_to_arrival_status = 'not achieve' -} -</jsScript_script> - </jsScript> - </jsScripts> - <fields> - <field> - <name>po_to_arrival_status</name> - <rename>po_to_arrival_status</rename> - <type>String</type> - <length>-1</length> + <connection>dw bi indoteknik (localhost)</connection> + <sql>select po_confirm_date, received_good_date, purchase_line_id, purchase_id, received_id +from mv_leadtime_po_to_received +--where purchase_id = 25089 and purchase_line_id = 141455 and received_id in (78477, 78910)</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>po_confirm_date</name> + <length>6</length> <precision>-1</precision> - <replace>N</replace> - </field> - </fields> + <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>Timestamp</type> + <storagetype>normal</storagetype> + <name>received_good_date</name> + <length>6</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>received_good_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>purchase_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input</origin> + <comments>purchase_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>purchase_id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input</origin> + <comments>purchase_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>received_id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input</origin> + <comments>received_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> @@ -1200,14 +1254,14 @@ else{ </output> </remotesteps> <GUI> - <xloc>768</xloc> + <xloc>176</xloc> <yloc>80</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/> @@ -1216,33 +1270,132 @@ else{ <method>none</method> <schema_name/> </partitioning> - <fields> - <field> - <name>po_confirm_date</name> - <rename/> - </field> - <field> - <name>received_good_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 17 + 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 new_po_to_arrival, ? as purchase_line_id, ? as purchase_id, ? as received_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>new_po_to_arrival</name> + <length>15</length> + <precision>0</precision> + <origin>Table input 2</origin> + <comments>new_po_to_arrival</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>purchase_line_id</name> - <rename/> - </field> - <field> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input 2</origin> + <comments>purchase_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>purchase_id</name> - <rename/> - </field> - <field> - <name>new_po_to_arrival</name> - <rename/> - </field> - <field> - <name>po_to_arrival_status</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input 2</origin> + <comments>purchase_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>received_id</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input 2</origin> + <comments>received_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> @@ -1252,14 +1405,14 @@ else{ </output> </remotesteps> <GUI> - <xloc>768</xloc> - <yloc>192</yloc> + <xloc>464</xloc> + <yloc>80</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/> @@ -1268,49 +1421,24 @@ 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_po_to_received_working_hour</table> - <key> - <name>purchase_line_id</name> - <field>purchase_line_id</field> - <condition>=</condition> - <name2/> - </key> - <value> - <name>po_confirm_date</name> - <rename>po_confirm_date</rename> - <update>Y</update> - </value> - <value> - <name>received_good_date</name> - <rename>received_good_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>purchase_line_id</name> - <rename>purchase_line_id</rename> - <update>N</update> - </value> - <value> + <case_insensitive>N</case_insensitive> + </field> + <field> <name>purchase_id</name> - <rename>purchase_id</rename> - <update>Y</update> - </value> - <value> - <name>new_po_to_arrival</name> - <rename>new_po_to_arrival</rename> - <update>Y</update> - </value> - <value> - <name>po_to_arrival_status</name> - <rename>po_to_arrival_status</rename> - <update>Y</update> - </value> - </lookup> + <case_insensitive>N</case_insensitive> + </field> + <field> + <name>received_id</name> + <case_insensitive>N</case_insensitive> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1320,8 +1448,8 @@ else{ </output> </remotesteps> <GUI> - <xloc>768</xloc> - <yloc>304</yloc> + <xloc>592</xloc> + <yloc>80</yloc> <draw>Y</draw> </GUI> </step> |