diff options
| author | stephanchrst <stephanchrst@gmail.com> | 2025-07-05 10:05:51 +0700 |
|---|---|---|
| committer | stephanchrst <stephanchrst@gmail.com> | 2025-07-05 10:05:51 +0700 |
| commit | e0baab61816dcc3309ebcfb574f01b3dcee65cce (patch) | |
| tree | 1e1bc2e6c307f00559db689df974ee44abb31a1d | |
| parent | 7e117f18ce8728ff0c39e9dc2f04b3a83af809bc (diff) | |
update combine deadstock
| -rw-r--r-- | calculate_no_sales_stock.ktr | 1652 | ||||
| -rw-r--r-- | combine_deadstock.ktr | 535 | ||||
| -rw-r--r-- | deadstock_job.kjb | 98 | ||||
| -rw-r--r-- | delete_useless_data_deadstock.ktr | 1256 | ||||
| -rw-r--r-- | update_list_deadstock_opening.ktr | 93 |
5 files changed, 2781 insertions, 853 deletions
diff --git a/calculate_no_sales_stock.ktr b/calculate_no_sales_stock.ktr index 0973a22..37943d8 100644 --- a/calculate_no_sales_stock.ktr +++ b/calculate_no_sales_stock.ktr @@ -659,11 +659,6 @@ <enabled>Y</enabled> </hop> <hop> - <from>Merge join 2</from> - <to>If field value is null</to> - <enabled>Y</enabled> - </hop> - <hop> <from>If field value is null</from> <to>Calculator</to> <enabled>Y</enabled> @@ -678,8 +673,59 @@ <to>Insert / update</to> <enabled>Y</enabled> </hop> + <hop> + <from>Merge join 2</from> + <to>Calculator 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Calculator 2</from> + <to>If field value is null</to> + <enabled>Y</enabled> + </hop> </order> <step> + <name>Calculator</name> + <type>Calculator</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <failIfNoFile>Y</failIfNoFile> + <calculation> + <field_name>total_cost</field_name> + <calc_type>MULTIPLY</calc_type> + <field_a>new_qty</field_a> + <field_b>last_cost</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>480</xloc> + <yloc>368</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> <name>Filter rows</name> <type>FilterRows</type> <description/> @@ -750,8 +796,8 @@ </GUI> </step> <step> - <name>Stream lookup</name> - <type>StreamLookup</type> + <name>If field value is null</name> + <type>IfNull</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -760,23 +806,39 @@ <method>none</method> <schema_name/> </partitioning> - <from>dead stock</from> - <input_sorted>N</input_sorted> - <preserve_memory>Y</preserve_memory> - <sorted_list>N</sorted_list> - <integer_pair>N</integer_pair> - <lookup> - <key> - <name>product_id</name> - <field>product_id</field> - </key> - <value> - <name>product_id</name> - <rename>in_deadstock</rename> - <default/> - <type>Integer</type> - </value> - </lookup> + <replaceAllByValue>0</replaceAllByValue> + <replaceAllMask/> + <selectFields>Y</selectFields> + <selectValuesType>N</selectValuesType> + <setEmptyStringAll>N</setEmptyStringAll> + <valuetypes> + </valuetypes> + <fields> + <field> + <name>aging</name> + <value>0</value> + <mask/> + <set_empty_string>N</set_empty_string> + </field> + <field> + <name>last_cost</name> + <value>0</value> + <mask/> + <set_empty_string>N</set_empty_string> + </field> + <field> + <name>qty</name> + <value>0</value> + <mask/> + <set_empty_string>N</set_empty_string> + </field> + <field> + <name>qty_outgoing</name> + <value>0</value> + <mask/> + <set_empty_string>N</set_empty_string> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -786,14 +848,14 @@ </output> </remotesteps> <GUI> - <xloc>224</xloc> - <yloc>144</yloc> + <xloc>608</xloc> + <yloc>368</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Stream lookup 2</name> - <type>StreamLookup</type> + <name>Insert / update</name> + <type>InsertUpdate</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -802,21 +864,67 @@ <method>none</method> <schema_name/> </partitioning> - <from>movement</from> - <input_sorted>N</input_sorted> - <preserve_memory>Y</preserve_memory> - <sorted_list>N</sorted_list> - <integer_pair>N</integer_pair> + <connection>dw bi indoteknik (localhost)</connection> + <commit>100</commit> + <update_bypassed>N</update_bypassed> <lookup> + <schema>public</schema> + <table>list_new_deadstock</table> <key> <name>product_id</name> <field>product_id</field> + <condition>=</condition> + <name2/> </key> <value> <name>product_id</name> - <rename>in_movement</rename> - <default/> - <type>None</type> + <rename>product_id</rename> + <update>N</update> + </value> + <value> + <name>item_code</name> + <rename>item_code</rename> + <update>Y</update> + </value> + <value> + <name>product_name</name> + <rename>product_name</rename> + <update>Y</update> + </value> + <value> + <name>brand</name> + <rename>brand</rename> + <update>Y</update> + </value> + <value> + <name>qty</name> + <rename>qty</rename> + <update>Y</update> + </value> + <value> + <name>date_added</name> + <rename>date_added</rename> + <update>N</update> + </value> + <value> + <name>last_in</name> + <rename>last_in</rename> + <update>Y</update> + </value> + <value> + <name>aging</name> + <rename>aging</rename> + <update>Y</update> + </value> + <value> + <name>last_cost</name> + <rename>last_cost</rename> + <update>Y</update> + </value> + <value> + <name>total_cost</name> + <rename>total_cost</rename> + <update>Y</update> </value> </lookup> <attributes/> @@ -828,46 +936,14 @@ </output> </remotesteps> <GUI> - <xloc>560</xloc> - <yloc>144</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>400</xloc> - <yloc>224</yloc> + <xloc>208</xloc> + <yloc>368</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Write to log 2</name> - <type>WriteToLog</type> + <name>Merge join</name> + <type>MergeJoin</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -876,13 +952,15 @@ <method>none</method> <schema_name/> </partitioning> - <loglevel>log_level_basic</loglevel> - <displayHeader>Y</displayHeader> - <limitRows>N</limitRows> - <limitRowsNumber>0</limitRowsNumber> - <logmessage/> - <fields> - </fields> + <join_type>LEFT OUTER</join_type> + <step1>Sort rows</step1> + <step2>Sort rows 2</step2> + <keys_1> + <key>product_id</key> + </keys_1> + <keys_2> + <key>product_id</key> + </keys_2> <attributes/> <cluster_schema/> <remotesteps> @@ -892,14 +970,14 @@ </output> </remotesteps> <GUI> - <xloc>720</xloc> - <yloc>224</yloc> + <xloc>1072</xloc> + <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>dead stock</name> - <type>TableInput</type> + <name>Merge join 2</name> + <type>MergeJoin</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -908,39 +986,15 @@ <method>none</method> <schema_name/> </partitioning> - <connection>dw bi indoteknik (localhost)</connection> - <sql>select distinct product_id from list_deadstock</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>Integer</type> - <storagetype>normal</storagetype> - <name>product_id</name> - <length>9</length> - <precision>0</precision> - <origin>Table input</origin> - <comments>product_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> + <join_type>LEFT OUTER</join_type> + <step1>Sort rows 3</step1> + <step2>Sort rows 4</step2> + <keys_1> + <key>product_id</key> + </keys_1> + <keys_2> + <key>product_id</key> + </keys_2> <attributes/> <cluster_schema/> <remotesteps> @@ -950,61 +1004,61 @@ </output> </remotesteps> <GUI> - <xloc>224</xloc> - <yloc>48</yloc> + <xloc>880</xloc> + <yloc>368</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>movement</name> - <type>TableInput</type> + <name>Select values</name> + <type>SelectValues</type> <description/> - <distribute>Y</distribute> + <distribute>N</distribute> <custom_distribution/> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> - <connection>dw bi indoteknik (localhost)</connection> - <sql>select distinct sm.product_id -from stock_move sm -join stock_location sl on sl.id = sm.location_dest_id -where 1=1 -and sl.name in ('Customers', 'Production') -and sm.state = 'done' -and sm.create_date >= (now() - '180 days'::interval)</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>Integer</type> - <storagetype>normal</storagetype> + <fields> + <field> <name>product_id</name> - <length>9</length> - <precision>0</precision> - <origin>movement</origin> - <comments>product_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/> + </field> + <field> + <name>item_code</name> + <rename/> + </field> + <field> + <name>product_name</name> + <rename/> + </field> + <field> + <name>brand</name> + <rename/> + </field> + <field> + <name>qty</name> + <rename/> + </field> + <field> + <name>date_added</name> + <rename/> + </field> + <field> + <name>in_deadstock</name> + <rename/> + </field> + <field> + <name>in_movement</name> + <rename/> + </field> + <field> + <name>qty_outgoing</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1014,14 +1068,14 @@ and sm.create_date >= (now() - '180 days'::interval)</sql> </output> </remotesteps> <GUI> - <xloc>560</xloc> - <yloc>48</yloc> + <xloc>864</xloc> + <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>stock quant</name> - <type>TableInput</type> + <name>Select values 2</name> + <type>SelectValues</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1030,144 +1084,45 @@ and sm.create_date >= (now() - '180 days'::interval)</sql> <method>none</method> <schema_name/> </partitioning> - <connection>erp indoteknik production (localhost)</connection> - <sql>select sq.product_id, coalesce(pp.default_code, pt.default_code) as item_code, pt.name as product_name, xm.x_name as brand, -sum(quantity) as qty, now()::timestamp as date_added -from stock_quant sq -join stock_location sl on sl.id = sq.location_id -join product_product pp on pp.id = sq.product_id -join product_template pt on pt.id = pp.product_tmpl_id -left join x_manufactures xm on xm.id = pt.x_manufacture -where 1=1 -and sq.location_id in(select id from stock_location where usage = 'internal' and active = true) -and pt.name not ilike '%promotion%' and pt.name not ilike '%asset%mesin%peralatan%' -and pt.name not ilike '%asset%inventaris%' and pt.name not ilike '%asset%kendaraan%' -group by sq.product_id, pp.default_code, pt.default_code, pt.name, xm.x_name -having sum(quantity) > 0 -order by sq.product_id</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>Integer</type> - <storagetype>normal</storagetype> + <fields> + <field> <name>product_id</name> - <length>9</length> - <precision>0</precision> - <origin>stock quant</origin> - <comments>product_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>String</type> - <storagetype>normal</storagetype> + <rename/> + </field> + <field> <name>item_code</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>item_code</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> + <rename/> + </field> + <field> <name>product_name</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>product_name</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> + <rename/> + </field> + <field> <name>brand</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>brand</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>Number</type> - <storagetype>normal</storagetype> + <rename/> + </field> + <field> <name>qty</name> - <length>-1</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>qty</comments> - <conversion_Mask>####0.0#########;-####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/> + </field> + <field> + <name>date_added</name> + <rename/> + </field> + <field> + <name>in_deadstock</name> + <rename/> + </field> + <field> + <name>in_movement</name> + <rename/> + </field> + <field> + <name>qty_outgoing</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1177,13 +1132,13 @@ order by sq.product_id</sql> </output> </remotesteps> <GUI> - <xloc>80</xloc> - <yloc>144</yloc> + <xloc>864</xloc> + <yloc>256</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values</name> + <name>Select values 3</name> <type>SelectValues</type> <description/> <distribute>N</distribute> @@ -1226,6 +1181,22 @@ order by sq.product_id</sql> <name>in_movement</name> <rename/> </field> + <field> + <name>product_id_1</name> + <rename/> + </field> + <field> + <name>last_in</name> + <rename/> + </field> + <field> + <name>aging</name> + <rename/> + </field> + <field> + <name>qty_outgoing</name> + <rename/> + </field> <select_unspecified>N</select_unspecified> </fields> <attributes/> @@ -1237,13 +1208,13 @@ order by sq.product_id</sql> </output> </remotesteps> <GUI> - <xloc>864</xloc> + <xloc>1216</xloc> <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>param product id</name> + <name>Select values 4</name> <type>SelectValues</type> <description/> <distribute>Y</distribute> @@ -1258,6 +1229,50 @@ order by sq.product_id</sql> <name>product_id</name> <rename/> </field> + <field> + <name>item_code</name> + <rename/> + </field> + <field> + <name>product_name</name> + <rename/> + </field> + <field> + <name>brand</name> + <rename/> + </field> + <field> + <name>qty</name> + <rename/> + </field> + <field> + <name>date_added</name> + <rename/> + </field> + <field> + <name>in_deadstock</name> + <rename/> + </field> + <field> + <name>in_movement</name> + <rename/> + </field> + <field> + <name>product_id_1</name> + <rename/> + </field> + <field> + <name>last_in</name> + <rename/> + </field> + <field> + <name>aging</name> + <rename/> + </field> + <field> + <name>qty_outgoing</name> + <rename/> + </field> <select_unspecified>N</select_unspecified> </fields> <attributes/> @@ -1269,13 +1284,13 @@ order by sq.product_id</sql> </output> </remotesteps> <GUI> - <xloc>864</xloc> - <yloc>48</yloc> + <xloc>1072</xloc> + <yloc>256</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values 2</name> + <name>Select values 5</name> <type>SelectValues</type> <description/> <distribute>Y</distribute> @@ -1303,19 +1318,27 @@ order by sq.product_id</sql> <rename/> </field> <field> - <name>qty</name> - <rename/> + <name>new_qty</name> + <rename>qty</rename> </field> <field> <name>date_added</name> <rename/> </field> <field> - <name>in_deadstock</name> + <name>last_in</name> <rename/> </field> <field> - <name>in_movement</name> + <name>aging</name> + <rename/> + </field> + <field> + <name>last_cost</name> + <rename/> + </field> + <field> + <name>total_cost</name> <rename/> </field> <select_unspecified>N</select_unspecified> @@ -1329,14 +1352,14 @@ order by sq.product_id</sql> </output> </remotesteps> <GUI> - <xloc>864</xloc> - <yloc>256</yloc> + <xloc>352</xloc> + <yloc>368</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>get last in</name> - <type>TableInput</type> + <name>Sort rows</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1345,94 +1368,23 @@ order by sq.product_id</sql> <method>none</method> <schema_name/> </partitioning> - <connection>dw bi indoteknik (localhost)</connection> - <sql>select sm.product_id, sm.create_date as last_in, -(date_part('epoch'::text, age(now()::timestamp, sm.create_date)) / 3600) / 24::numeric as aging -from stock_move sm -join stock_location sl on sl.id = sm.location_dest_id -where 1=1 -and sl.name in ('Input', 'Stock') -and sm.state = 'done' -and sm.product_id = ? -order by sm.create_date desc -limit 1</sql> - <limit>0</limit> - <lookup>param product id</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> + <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>product_id</name> - <length>9</length> - <precision>0</precision> - <origin>get last in</origin> - <comments>product_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>Timestamp</type> - <storagetype>normal</storagetype> - <name>last_in</name> - <length>6</length> - <precision>-1</precision> - <origin>get last in</origin> - <comments>last_in</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>Number</type> - <storagetype>normal</storagetype> - <name>aging</name> - <length>-1</length> - <precision>-1</precision> - <origin>get last in</origin> - <comments>aging</comments> - <conversion_Mask>####0.0#########;-####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> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> <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> + <presorted>N</presorted> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1443,12 +1395,12 @@ limit 1</sql> </remotesteps> <GUI> <xloc>976</xloc> - <yloc>48</yloc> + <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows</name> + <name>Sort rows 2</name> <type>SortRows</type> <description/> <distribute>Y</distribute> @@ -1484,13 +1436,13 @@ limit 1</sql> </output> </remotesteps> <GUI> - <xloc>976</xloc> - <yloc>144</yloc> + <xloc>1072</xloc> + <yloc>48</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 2</name> + <name>Sort rows 3</name> <type>SortRows</type> <description/> <distribute>Y</distribute> @@ -1508,14 +1460,6 @@ limit 1</sql> <compress_variable/> <unique_rows>N</unique_rows> <fields> - <field> - <name>product_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/> @@ -1526,14 +1470,14 @@ limit 1</sql> </output> </remotesteps> <GUI> - <xloc>1072</xloc> - <yloc>48</yloc> + <xloc>1008</xloc> + <yloc>336</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Merge join</name> - <type>MergeJoin</type> + <name>Sort rows 4</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1542,15 +1486,15 @@ limit 1</sql> <method>none</method> <schema_name/> </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Sort rows</step1> - <step2>Sort rows 2</step2> - <keys_1> - <key>product_id</key> - </keys_1> - <keys_2> - <key>product_id</key> - </keys_2> + <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> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1560,69 +1504,81 @@ limit 1</sql> </output> </remotesteps> <GUI> - <xloc>1072</xloc> + <xloc>1120</xloc> + <yloc>368</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Stream lookup</name> + <type>StreamLookup</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <from>dead stock</from> + <input_sorted>N</input_sorted> + <preserve_memory>Y</preserve_memory> + <sorted_list>N</sorted_list> + <integer_pair>N</integer_pair> + <lookup> + <key> + <name>product_id</name> + <field>product_id</field> + </key> + <value> + <name>product_id</name> + <rename>in_deadstock</rename> + <default/> + <type>Integer</type> + </value> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>224</xloc> <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values 3</name> - <type>SelectValues</type> + <name>Stream lookup 2</name> + <type>StreamLookup</type> <description/> - <distribute>N</distribute> + <distribute>Y</distribute> <custom_distribution/> <copies>1</copies> <partitioning> <method>none</method> <schema_name/> </partitioning> - <fields> - <field> + <from>movement</from> + <input_sorted>N</input_sorted> + <preserve_memory>Y</preserve_memory> + <sorted_list>N</sorted_list> + <integer_pair>N</integer_pair> + <lookup> + <key> <name>product_id</name> - <rename/> - </field> - <field> - <name>item_code</name> - <rename/> - </field> - <field> - <name>product_name</name> - <rename/> - </field> - <field> - <name>brand</name> - <rename/> - </field> - <field> - <name>qty</name> - <rename/> - </field> - <field> - <name>date_added</name> - <rename/> - </field> - <field> - <name>in_deadstock</name> - <rename/> - </field> - <field> - <name>in_movement</name> - <rename/> - </field> - <field> - <name>product_id_1</name> - <rename/> - </field> - <field> - <name>last_in</name> - <rename/> - </field> - <field> - <name>aging</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> + <field>product_id</field> + </key> + <value> + <name>product_id</name> + <rename>in_movement</rename> + <default/> + <type>None</type> + </value> + </lookup> <attributes/> <cluster_schema/> <remotesteps> @@ -1632,14 +1588,14 @@ limit 1</sql> </output> </remotesteps> <GUI> - <xloc>1216</xloc> + <xloc>560</xloc> <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>param product id 2</name> - <type>SelectValues</type> + <name>Write to log</name> + <type>WriteToLog</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1648,13 +1604,13 @@ limit 1</sql> <method>none</method> <schema_name/> </partitioning> + <loglevel>log_level_basic</loglevel> + <displayHeader>Y</displayHeader> + <limitRows>N</limitRows> + <limitRowsNumber>0</limitRowsNumber> + <logmessage/> <fields> - <field> - <name>product_id</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1664,14 +1620,14 @@ limit 1</sql> </output> </remotesteps> <GUI> - <xloc>1216</xloc> - <yloc>256</yloc> + <xloc>400</xloc> + <yloc>224</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values 4</name> - <type>SelectValues</type> + <name>Write to log 2</name> + <type>WriteToLog</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1680,53 +1636,13 @@ limit 1</sql> <method>none</method> <schema_name/> </partitioning> + <loglevel>log_level_basic</loglevel> + <displayHeader>Y</displayHeader> + <limitRows>N</limitRows> + <limitRowsNumber>0</limitRowsNumber> + <logmessage/> <fields> - <field> - <name>product_id</name> - <rename/> - </field> - <field> - <name>item_code</name> - <rename/> - </field> - <field> - <name>product_name</name> - <rename/> - </field> - <field> - <name>brand</name> - <rename/> - </field> - <field> - <name>qty</name> - <rename/> - </field> - <field> - <name>date_added</name> - <rename/> - </field> - <field> - <name>in_deadstock</name> - <rename/> - </field> - <field> - <name>in_movement</name> - <rename/> - </field> - <field> - <name>product_id_1</name> - <rename/> - </field> - <field> - <name>last_in</name> - <rename/> - </field> - <field> - <name>aging</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1736,8 +1652,8 @@ limit 1</sql> </output> </remotesteps> <GUI> - <xloc>1072</xloc> - <yloc>256</yloc> + <xloc>720</xloc> + <yloc>224</yloc> <draw>Y</draw> </GUI> </step> @@ -1781,59 +1697,13 @@ order by pp.id</sql> <name>product_id</name> <length>9</length> <precision>0</precision> - <origin>stock quant</origin> + <origin>avg hpp</origin> <comments>product_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>Y</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>item_code</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>item_code</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>product_name</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>product_name</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> @@ -1845,39 +1715,16 @@ order by pp.id</sql> <lenient_string_to_number>N</lenient_string_to_number> </value-meta> <value-meta> - <type>String</type> + <type>BigNumber</type> <storagetype>normal</storagetype> - <name>brand</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>stock quant</origin> - <comments>brand</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>Number</type> - <storagetype>normal</storagetype> - <name>qty</name> + <name>last_cost</name> <length>-1</length> <precision>-1</precision> - <origin>stock quant</origin> - <comments>qty</comments> - <conversion_Mask>####0.0#########;-####0.0#########</conversion_Mask> + <origin>avg hpp</origin> + <comments>last_cost</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> @@ -1890,13 +1737,48 @@ order by pp.id</sql> <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> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>1216</xloc> + <yloc>368</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>dead stock</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>dw bi indoteknik (localhost)</connection> + <sql>select distinct product_id from list_deadstock</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>Integer</type> <storagetype>normal</storagetype> - <name>in_deadstock</name> + <name>product_id</name> <length>9</length> <precision>0</precision> - <origin>Stream lookup</origin> + <origin>dead stock</origin> <comments>product_id</comments> <conversion_Mask>####0;-####0</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -1913,13 +1795,57 @@ order by pp.id</sql> <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> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>224</xloc> + <yloc>48</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>get last in</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production (localhost)</connection> + <sql>select sm.product_id, sm.create_date as last_in, +(date_part('epoch'::text, age(now()::timestamp, sm.create_date)) / 3600) / 24::numeric as aging +from stock_move sm +join stock_location sl on sl.id = sm.location_dest_id +where 1=1 +and sl.name in ('Input', 'Stock') +and sm.state = 'done' +and sm.product_id = ? +order by sm.create_date desc +limit 1</sql> + <limit>0</limit> + <lookup>param product id</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>in_movement</name> + <name>product_id</name> <length>9</length> <precision>0</precision> - <origin>Stream lookup 2</origin> + <origin>get last in</origin> <comments>product_id</comments> <conversion_Mask>####0;-####0</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -1937,19 +1863,19 @@ order by pp.id</sql> <lenient_string_to_number>N</lenient_string_to_number> </value-meta> <value-meta> - <type>Integer</type> + <type>Timestamp</type> <storagetype>normal</storagetype> - <name>product_id_1</name> - <length>9</length> - <precision>0</precision> - <origin>Merge join</origin> - <comments>product_id</comments> - <conversion_Mask>####0;-####0</conversion_Mask> + <name>last_in</name> + <length>6</length> + <precision>-1</precision> + <origin>get last in</origin> + <comments>last_in</comments> + <conversion_Mask/> <decimal_symbol>.</decimal_symbol> <grouping_symbol>,</grouping_symbol> <currency_symbol/> <trim_type>none</trim_type> - <case_insensitive>Y</case_insensitive> + <case_insensitive>N</case_insensitive> <collator_disabled>Y</collator_disabled> <collator_strength>0</collator_strength> <sort_descending>N</sort_descending> @@ -1960,14 +1886,14 @@ order by pp.id</sql> <lenient_string_to_number>N</lenient_string_to_number> </value-meta> <value-meta> - <type>Timestamp</type> + <type>Number</type> <storagetype>normal</storagetype> - <name>last_in</name> - <length>6</length> + <name>aging</name> + <length>-1</length> <precision>-1</precision> <origin>get last in</origin> - <comments>last_in</comments> - <conversion_Mask/> + <comments>aging</comments> + <conversion_Mask>####0.0#########;-####0.0#########</conversion_Mask> <decimal_symbol>.</decimal_symbol> <grouping_symbol>,</grouping_symbol> <currency_symbol/> @@ -1992,14 +1918,14 @@ order by pp.id</sql> </output> </remotesteps> <GUI> - <xloc>1216</xloc> - <yloc>368</yloc> + <xloc>976</xloc> + <yloc>48</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 3</name> - <type>SortRows</type> + <name>movement</name> + <type>TableInput</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -2008,15 +1934,45 @@ order by pp.id</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> - </fields> + <connection>dw bi indoteknik (localhost)</connection> + <sql>select distinct sm.product_id +from stock_move sm +join stock_location sl on sl.id = sm.location_dest_id +where 1=1 +and sl.name in ('Customers', 'Production') +and sm.state = 'done' +and sm.create_date >= (now() - '180 days'::interval)</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>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>movement</origin> + <comments>product_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> @@ -2026,14 +1982,14 @@ order by pp.id</sql> </output> </remotesteps> <GUI> - <xloc>1008</xloc> - <yloc>336</yloc> + <xloc>560</xloc> + <yloc>48</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 4</name> - <type>SortRows</type> + <name>param product id</name> + <type>SelectValues</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -2042,14 +1998,11 @@ order by pp.id</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>product_id</name> + </field> + <select_unspecified>N</select_unspecified> </fields> <attributes/> <cluster_schema/> @@ -2060,47 +2013,13 @@ order by pp.id</sql> </output> </remotesteps> <GUI> - <xloc>1120</xloc> - <yloc>368</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Merge join 2</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 3</step1> - <step2>Sort rows 4</step2> - <keys_1> - <key>product_id</key> - </keys_1> - <keys_2> - <key>product_id</key> - </keys_2> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>880</xloc> - <yloc>368</yloc> + <xloc>864</xloc> + <yloc>48</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values 5</name> + <name>param product id 2</name> <type>SelectValues</type> <description/> <distribute>Y</distribute> @@ -2113,43 +2032,6 @@ order by pp.id</sql> <fields> <field> <name>product_id</name> - <rename/> - </field> - <field> - <name>item_code</name> - <rename/> - </field> - <field> - <name>product_name</name> - <rename/> - </field> - <field> - <name>brand</name> - <rename/> - </field> - <field> - <name>qty</name> - <rename/> - </field> - <field> - <name>date_added</name> - <rename/> - </field> - <field> - <name>last_in</name> - <rename/> - </field> - <field> - <name>aging</name> - <rename/> - </field> - <field> - <name>last_cost</name> - <rename/> - </field> - <field> - <name>total_cost</name> - <rename/> </field> <select_unspecified>N</select_unspecified> </fields> @@ -2162,14 +2044,14 @@ order by pp.id</sql> </output> </remotesteps> <GUI> - <xloc>432</xloc> - <yloc>368</yloc> + <xloc>1216</xloc> + <yloc>256</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>If field value is null</name> - <type>IfNull</type> + <name>stock quant</name> + <type>TableInput</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -2178,27 +2060,191 @@ order by pp.id</sql> <method>none</method> <schema_name/> </partitioning> - <replaceAllByValue>0</replaceAllByValue> - <replaceAllMask/> - <selectFields>Y</selectFields> - <selectValuesType>N</selectValuesType> - <setEmptyStringAll>N</setEmptyStringAll> - <valuetypes> - </valuetypes> - <fields> - <field> - <name>aging</name> - <value>0</value> - <mask/> - <set_empty_string>N</set_empty_string> - </field> - <field> - <name>last_cost</name> - <value>0</value> - <mask/> - <set_empty_string>N</set_empty_string> - </field> - </fields> + <connection>erp indoteknik production (localhost)</connection> + <sql>select sq.product_id, coalesce(pp.default_code, pt.default_code) as item_code, pt.name as product_name, xm.x_name as brand, +sum(quantity) as qty, now()::timestamp as date_added, +get_qty_outgoing(sq.product_id) as qty_outgoing +from stock_quant sq +join stock_location sl on sl.id = sq.location_id +join product_product pp on pp.id = sq.product_id +join product_template pt on pt.id = pp.product_tmpl_id +left join x_manufactures xm on xm.id = pt.x_manufacture +where 1=1 +and sq.location_id in(select id from stock_location where usage = 'internal' and active = true) +and pt.name not ilike '%promotion%' and pt.name not ilike '%asset%mesin%peralatan%' +and pt.name not ilike '%asset%inventaris%' and pt.name not ilike '%asset%kendaraan%' +group by sq.product_id, pp.default_code, pt.default_code, pt.name, xm.x_name +having sum(quantity) > 0 +order by sq.product_id</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>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>stock quant</origin> + <comments>product_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>String</type> + <storagetype>normal</storagetype> + <name>item_code</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>stock quant</origin> + <comments>item_code</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>product_name</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>stock quant</origin> + <comments>product_name</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>brand</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>stock quant</origin> + <comments>brand</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>Number</type> + <storagetype>normal</storagetype> + <name>qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>stock quant</origin> + <comments>qty</comments> + <conversion_Mask>####0.0#########;-####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>Timestamp</type> + <storagetype>normal</storagetype> + <name>date_added</name> + <length>6</length> + <precision>-1</precision> + <origin>stock quant</origin> + <comments>date_added</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>BigNumber</type> + <storagetype>normal</storagetype> + <name>qty_outgoing</name> + <length>-1</length> + <precision>-1</precision> + <origin>stock quant</origin> + <comments>qty_outgoing</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/> <remotesteps> @@ -2208,13 +2254,13 @@ order by pp.id</sql> </output> </remotesteps> <GUI> - <xloc>752</xloc> - <yloc>368</yloc> + <xloc>80</xloc> + <yloc>144</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Calculator</name> + <name>Calculator 2</name> <type>Calculator</type> <description/> <distribute>Y</distribute> @@ -2226,12 +2272,12 @@ order by pp.id</sql> </partitioning> <failIfNoFile>Y</failIfNoFile> <calculation> - <field_name>total_cost</field_name> - <calc_type>MULTIPLY</calc_type> + <field_name>new_qty</field_name> + <calc_type>SUBTRACT</calc_type> <field_a>qty</field_a> - <field_b>last_cost</field_b> + <field_b>qty_outgoing</field_b> <field_c/> - <value_type>Number</value_type> + <value_type>None</value_type> <value_length>-1</value_length> <value_precision>-1</value_precision> <remove>N</remove> @@ -2249,95 +2295,7 @@ order by pp.id</sql> </output> </remotesteps> <GUI> - <xloc>608</xloc> - <yloc>368</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Insert / update</name> - <type>InsertUpdate</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <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>list_new_deadstock</table> - <key> - <name>product_id</name> - <field>product_id</field> - <condition>=</condition> - <name2/> - </key> - <value> - <name>product_id</name> - <rename>product_id</rename> - <update>N</update> - </value> - <value> - <name>item_code</name> - <rename>item_code</rename> - <update>Y</update> - </value> - <value> - <name>product_name</name> - <rename>product_name</rename> - <update>Y</update> - </value> - <value> - <name>brand</name> - <rename>brand</rename> - <update>Y</update> - </value> - <value> - <name>qty</name> - <rename>qty</rename> - <update>Y</update> - </value> - <value> - <name>date_added</name> - <rename>date_added</rename> - <update>Y</update> - </value> - <value> - <name>last_in</name> - <rename>last_in</rename> - <update>Y</update> - </value> - <value> - <name>aging</name> - <rename>aging</rename> - <update>Y</update> - </value> - <value> - <name>last_cost</name> - <rename>last_cost</rename> - <update>Y</update> - </value> - <value> - <name>total_cost</name> - <rename>total_cost</rename> - <update>Y</update> - </value> - </lookup> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>288</xloc> + <xloc>736</xloc> <yloc>368</yloc> <draw>Y</draw> </GUI> diff --git a/combine_deadstock.ktr b/combine_deadstock.ktr new file mode 100644 index 0000000..c333f83 --- /dev/null +++ b/combine_deadstock.ktr @@ -0,0 +1,535 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>combine_deadstock</name> + <description/> + <extended_description/> + <trans_version/> + <trans_type>Normal</trans_type> + <directory>/</directory> + <parameters> + </parameters> + <log> + <trans-log-table> + <connection/> + <schema/> + <table/> + <size_limit_lines/> + <interval/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>TRANSNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>STATUS</id> + <enabled>Y</enabled> + <name>STATUS</name> + </field> + <field> + <id>LINES_READ</id> + <enabled>Y</enabled> + <name>LINES_READ</name> + <subject/> + </field> + <field> + <id>LINES_WRITTEN</id> + <enabled>Y</enabled> + <name>LINES_WRITTEN</name> + <subject/> + </field> + <field> + <id>LINES_UPDATED</id> + <enabled>Y</enabled> + <name>LINES_UPDATED</name> + <subject/> + </field> + <field> + <id>LINES_INPUT</id> + <enabled>Y</enabled> + <name>LINES_INPUT</name> + <subject/> + </field> + <field> + <id>LINES_OUTPUT</id> + <enabled>Y</enabled> + <name>LINES_OUTPUT</name> + <subject/> + </field> + <field> + <id>LINES_REJECTED</id> + <enabled>Y</enabled> + <name>LINES_REJECTED</name> + <subject/> + </field> + <field> + <id>ERRORS</id> + <enabled>Y</enabled> + <name>ERRORS</name> + </field> + <field> + <id>STARTDATE</id> + <enabled>Y</enabled> + <name>STARTDATE</name> + </field> + <field> + <id>ENDDATE</id> + <enabled>Y</enabled> + <name>ENDDATE</name> + </field> + <field> + <id>LOGDATE</id> + <enabled>Y</enabled> + <name>LOGDATE</name> + </field> + <field> + <id>DEPDATE</id> + <enabled>Y</enabled> + <name>DEPDATE</name> + </field> + <field> + <id>REPLAYDATE</id> + <enabled>Y</enabled> + <name>REPLAYDATE</name> + </field> + <field> + <id>LOG_FIELD</id> + <enabled>Y</enabled> + <name>LOG_FIELD</name> + </field> + <field> + <id>EXECUTING_SERVER</id> + <enabled>N</enabled> + <name>EXECUTING_SERVER</name> + </field> + <field> + <id>EXECUTING_USER</id> + <enabled>N</enabled> + <name>EXECUTING_USER</name> + </field> + <field> + <id>CLIENT</id> + <enabled>N</enabled> + <name>CLIENT</name> + </field> + </trans-log-table> + <perf-log-table> + <connection/> + <schema/> + <table/> + <interval/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>SEQ_NR</id> + <enabled>Y</enabled> + <name>SEQ_NR</name> + </field> + <field> + <id>LOGDATE</id> + <enabled>Y</enabled> + <name>LOGDATE</name> + </field> + <field> + <id>TRANSNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>STEPNAME</id> + <enabled>Y</enabled> + <name>STEPNAME</name> + </field> + <field> + <id>STEP_COPY</id> + <enabled>Y</enabled> + <name>STEP_COPY</name> + </field> + <field> + <id>LINES_READ</id> + <enabled>Y</enabled> + <name>LINES_READ</name> + </field> + <field> + <id>LINES_WRITTEN</id> + <enabled>Y</enabled> + <name>LINES_WRITTEN</name> + </field> + <field> + <id>LINES_UPDATED</id> + <enabled>Y</enabled> + <name>LINES_UPDATED</name> + </field> + <field> + <id>LINES_INPUT</id> + <enabled>Y</enabled> + <name>LINES_INPUT</name> + </field> + <field> + <id>LINES_OUTPUT</id> + <enabled>Y</enabled> + <name>LINES_OUTPUT</name> + </field> + <field> + <id>LINES_REJECTED</id> + <enabled>Y</enabled> + <name>LINES_REJECTED</name> + </field> + <field> + <id>ERRORS</id> + <enabled>Y</enabled> + <name>ERRORS</name> + </field> + <field> + <id>INPUT_BUFFER_ROWS</id> + <enabled>Y</enabled> + <name>INPUT_BUFFER_ROWS</name> + </field> + <field> + <id>OUTPUT_BUFFER_ROWS</id> + <enabled>Y</enabled> + <name>OUTPUT_BUFFER_ROWS</name> + </field> + </perf-log-table> + <channel-log-table> + <connection/> + <schema/> + <table/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>LOG_DATE</id> + <enabled>Y</enabled> + <name>LOG_DATE</name> + </field> + <field> + <id>LOGGING_OBJECT_TYPE</id> + <enabled>Y</enabled> + <name>LOGGING_OBJECT_TYPE</name> + </field> + <field> + <id>OBJECT_NAME</id> + <enabled>Y</enabled> + <name>OBJECT_NAME</name> + </field> + <field> + <id>OBJECT_COPY</id> + <enabled>Y</enabled> + <name>OBJECT_COPY</name> + </field> + <field> + <id>REPOSITORY_DIRECTORY</id> + <enabled>Y</enabled> + <name>REPOSITORY_DIRECTORY</name> + </field> + <field> + <id>FILENAME</id> + <enabled>Y</enabled> + <name>FILENAME</name> + </field> + <field> + <id>OBJECT_ID</id> + <enabled>Y</enabled> + <name>OBJECT_ID</name> + </field> + <field> + <id>OBJECT_REVISION</id> + <enabled>Y</enabled> + <name>OBJECT_REVISION</name> + </field> + <field> + <id>PARENT_CHANNEL_ID</id> + <enabled>Y</enabled> + <name>PARENT_CHANNEL_ID</name> + </field> + <field> + <id>ROOT_CHANNEL_ID</id> + <enabled>Y</enabled> + <name>ROOT_CHANNEL_ID</name> + </field> + </channel-log-table> + <step-log-table> + <connection/> + <schema/> + <table/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>LOG_DATE</id> + <enabled>Y</enabled> + <name>LOG_DATE</name> + </field> + <field> + <id>TRANSNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>STEPNAME</id> + <enabled>Y</enabled> + <name>STEPNAME</name> + </field> + <field> + <id>STEP_COPY</id> + <enabled>Y</enabled> + <name>STEP_COPY</name> + </field> + <field> + <id>LINES_READ</id> + <enabled>Y</enabled> + <name>LINES_READ</name> + </field> + <field> + <id>LINES_WRITTEN</id> + <enabled>Y</enabled> + <name>LINES_WRITTEN</name> + </field> + <field> + <id>LINES_UPDATED</id> + <enabled>Y</enabled> + <name>LINES_UPDATED</name> + </field> + <field> + <id>LINES_INPUT</id> + <enabled>Y</enabled> + <name>LINES_INPUT</name> + </field> + <field> + <id>LINES_OUTPUT</id> + <enabled>Y</enabled> + <name>LINES_OUTPUT</name> + </field> + <field> + <id>LINES_REJECTED</id> + <enabled>Y</enabled> + <name>LINES_REJECTED</name> + </field> + <field> + <id>ERRORS</id> + <enabled>Y</enabled> + <name>ERRORS</name> + </field> + <field> + <id>LOG_FIELD</id> + <enabled>N</enabled> + <name>LOG_FIELD</name> + </field> + </step-log-table> + <metrics-log-table> + <connection/> + <schema/> + <table/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>LOG_DATE</id> + <enabled>Y</enabled> + <name>LOG_DATE</name> + </field> + <field> + <id>METRICS_DATE</id> + <enabled>Y</enabled> + <name>METRICS_DATE</name> + </field> + <field> + <id>METRICS_CODE</id> + <enabled>Y</enabled> + <name>METRICS_CODE</name> + </field> + <field> + <id>METRICS_DESCRIPTION</id> + <enabled>Y</enabled> + <name>METRICS_DESCRIPTION</name> + </field> + <field> + <id>METRICS_SUBJECT</id> + <enabled>Y</enabled> + <name>METRICS_SUBJECT</name> + </field> + <field> + <id>METRICS_TYPE</id> + <enabled>Y</enabled> + <name>METRICS_TYPE</name> + </field> + <field> + <id>METRICS_VALUE</id> + <enabled>Y</enabled> + <name>METRICS_VALUE</name> + </field> + </metrics-log-table> + </log> + <maxdate> + <connection/> + <table/> + <field/> + <offset>0.0</offset> + <maxdiff>0.0</maxdiff> + </maxdate> + <size_rowset>10000</size_rowset> + <sleep_time_empty>50</sleep_time_empty> + <sleep_time_full>50</sleep_time_full> + <unique_connections>N</unique_connections> + <feedback_shown>Y</feedback_shown> + <feedback_size>50000</feedback_size> + <using_thread_priorities>Y</using_thread_priorities> + <shared_objects_file/> + <capture_step_performance>N</capture_step_performance> + <step_performance_capturing_delay>1000</step_performance_capturing_delay> + <step_performance_capturing_size_limit>100</step_performance_capturing_size_limit> + <dependencies> + </dependencies> + <partitionschemas> + </partitionschemas> + <slaveservers> + </slaveservers> + <clusterschemas> + </clusterschemas> + <created_user>-</created_user> + <created_date>2025/07/04 10:32:24.680</created_date> + <modified_user>-</modified_user> + <modified_date>2025/07/04 10:32:24.680</modified_date> + <key_for_session_key/> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <connection> + <name>dw bi indoteknik (localhost)</name> + <server>localhost</server> + <type>POSTGRESQL</type> + <access>Native</access> + <database>dw_bi_indoteknik</database> + <port>5432</port> + <username>odoo</username> + <password>Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3</password> + <servername/> + <data_tablespace/> + <index_tablespace/> + <attributes> + <attribute> + <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>IS_CLUSTERED</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>PORT_NUMBER</code> + <attribute>5432</attribute> + </attribute> + <attribute> + <code>PRESERVE_RESERVED_WORD_CASE</code> + <attribute>Y</attribute> + </attribute> + <attribute> + <code>QUOTE_ALL_FIELDS</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>SUPPORTS_BOOLEAN_DATA_TYPE</code> + <attribute>Y</attribute> + </attribute> + <attribute> + <code>SUPPORTS_TIMESTAMP_DATA_TYPE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>USE_POOLING</code> + <attribute>N</attribute> + </attribute> + </attributes> + </connection> + <order> + </order> + <step> + <name>Execute SQL script</name> + <type>ExecSQL</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>dw bi indoteknik (localhost)</connection> + <execute_each_row>N</execute_each_row> + <single_statement>N</single_statement> + <replace_variables>N</replace_variables> + <quoteString>N</quoteString> + <sql>refresh materialized view mv_combine_deadstock;</sql> + <set_params>N</set_params> + <insert_field/> + <update_field/> + <delete_field/> + <read_field/> + <arguments> + </arguments> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>240</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step_error_handling> + </step_error_handling> + <slave-step-copy-partition-distribution> + </slave-step-copy-partition-distribution> + <slave_transformation>N</slave_transformation> + <attributes/> +</transformation> diff --git a/deadstock_job.kjb b/deadstock_job.kjb index 89ce902..e269e17 100644 --- a/deadstock_job.kjb +++ b/deadstock_job.kjb @@ -394,7 +394,85 @@ <parallel>N</parallel> <draw>Y</draw> <nr>0</nr> - <xloc>736</xloc> + <xloc>1168</xloc> + <yloc>96</yloc> + <attributes_kjc/> + </entry> + <entry> + <name>delete useless deadstock</name> + <description/> + <type>TRANS</type> + <attributes/> + <specification_method>filename</specification_method> + <trans_object_id/> + <filename>/home/stephan/transformation-job-bi/delete_useless_data_deadstock.ktr</filename> + <transname/> + <arg_from_previous>N</arg_from_previous> + <params_from_previous>N</params_from_previous> + <exec_per_row>N</exec_per_row> + <clear_rows>N</clear_rows> + <clear_files>N</clear_files> + <set_logfile>N</set_logfile> + <logfile/> + <logext/> + <add_date>N</add_date> + <add_time>N</add_time> + <loglevel>Basic</loglevel> + <cluster>N</cluster> + <slave_server_name/> + <set_append_logfile>N</set_append_logfile> + <wait_until_finished>Y</wait_until_finished> + <follow_abort_remote>N</follow_abort_remote> + <create_parent_folder>N</create_parent_folder> + <logging_remote_work>N</logging_remote_work> + <run_configuration>Pentaho local</run_configuration> + <suppress_result_data>N</suppress_result_data> + <parameters> + <pass_all_parameters>Y</pass_all_parameters> + </parameters> + <parallel>N</parallel> + <draw>Y</draw> + <nr>0</nr> + <xloc>784</xloc> + <yloc>96</yloc> + <attributes_kjc/> + </entry> + <entry> + <name>combine deadstock</name> + <description/> + <type>TRANS</type> + <attributes/> + <specification_method>filename</specification_method> + <trans_object_id/> + <filename>/home/stephan/transformation-job-bi/combine_deadstock.ktr</filename> + <transname/> + <arg_from_previous>N</arg_from_previous> + <params_from_previous>N</params_from_previous> + <exec_per_row>N</exec_per_row> + <clear_rows>N</clear_rows> + <clear_files>N</clear_files> + <set_logfile>N</set_logfile> + <logfile/> + <logext/> + <add_date>N</add_date> + <add_time>N</add_time> + <loglevel>Basic</loglevel> + <cluster>N</cluster> + <slave_server_name/> + <set_append_logfile>N</set_append_logfile> + <wait_until_finished>Y</wait_until_finished> + <follow_abort_remote>N</follow_abort_remote> + <create_parent_folder>N</create_parent_folder> + <logging_remote_work>N</logging_remote_work> + <run_configuration>Pentaho local</run_configuration> + <suppress_result_data>N</suppress_result_data> + <parameters> + <pass_all_parameters>Y</pass_all_parameters> + </parameters> + <parallel>N</parallel> + <draw>Y</draw> + <nr>0</nr> + <xloc>992</xloc> <yloc>96</yloc> <attributes_kjc/> </entry> @@ -420,6 +498,24 @@ </hop> <hop> <from>calculate no sales stock</from> + <to>delete useless deadstock</to> + <from_nr>0</from_nr> + <to_nr>0</to_nr> + <enabled>Y</enabled> + <evaluation>Y</evaluation> + <unconditional>N</unconditional> + </hop> + <hop> + <from>delete useless deadstock</from> + <to>combine deadstock</to> + <from_nr>0</from_nr> + <to_nr>0</to_nr> + <enabled>Y</enabled> + <evaluation>Y</evaluation> + <unconditional>N</unconditional> + </hop> + <hop> + <from>combine deadstock</from> <to>Success</to> <from_nr>0</from_nr> <to_nr>0</to_nr> diff --git a/delete_useless_data_deadstock.ktr b/delete_useless_data_deadstock.ktr new file mode 100644 index 0000000..d1991e4 --- /dev/null +++ b/delete_useless_data_deadstock.ktr @@ -0,0 +1,1256 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>delete_useless_data_deadstock</name> + <description/> + <extended_description/> + <trans_version/> + <trans_type>Normal</trans_type> + <directory>/</directory> + <parameters> + </parameters> + <log> + <trans-log-table> + <connection/> + <schema/> + <table/> + <size_limit_lines/> + <interval/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>TRANSNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>STATUS</id> + <enabled>Y</enabled> + <name>STATUS</name> + </field> + <field> + <id>LINES_READ</id> + <enabled>Y</enabled> + <name>LINES_READ</name> + <subject/> + </field> + <field> + <id>LINES_WRITTEN</id> + <enabled>Y</enabled> + <name>LINES_WRITTEN</name> + <subject/> + </field> + <field> + <id>LINES_UPDATED</id> + <enabled>Y</enabled> + <name>LINES_UPDATED</name> + <subject/> + </field> + <field> + <id>LINES_INPUT</id> + <enabled>Y</enabled> + <name>LINES_INPUT</name> + <subject/> + </field> + <field> + <id>LINES_OUTPUT</id> + <enabled>Y</enabled> + <name>LINES_OUTPUT</name> + <subject/> + </field> + <field> + <id>LINES_REJECTED</id> + <enabled>Y</enabled> + <name>LINES_REJECTED</name> + <subject/> + </field> + <field> + <id>ERRORS</id> + <enabled>Y</enabled> + <name>ERRORS</name> + </field> + <field> + <id>STARTDATE</id> + <enabled>Y</enabled> + <name>STARTDATE</name> + </field> + <field> + <id>ENDDATE</id> + <enabled>Y</enabled> + <name>ENDDATE</name> + </field> + <field> + <id>LOGDATE</id> + <enabled>Y</enabled> + <name>LOGDATE</name> + </field> + <field> + <id>DEPDATE</id> + <enabled>Y</enabled> + <name>DEPDATE</name> + </field> + <field> + <id>REPLAYDATE</id> + <enabled>Y</enabled> + <name>REPLAYDATE</name> + </field> + <field> + <id>LOG_FIELD</id> + <enabled>Y</enabled> + <name>LOG_FIELD</name> + </field> + <field> + <id>EXECUTING_SERVER</id> + <enabled>N</enabled> + <name>EXECUTING_SERVER</name> + </field> + <field> + <id>EXECUTING_USER</id> + <enabled>N</enabled> + <name>EXECUTING_USER</name> + </field> + <field> + <id>CLIENT</id> + <enabled>N</enabled> + <name>CLIENT</name> + </field> + </trans-log-table> + <perf-log-table> + <connection/> + <schema/> + <table/> + <interval/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>SEQ_NR</id> + <enabled>Y</enabled> + <name>SEQ_NR</name> + </field> + <field> + <id>LOGDATE</id> + <enabled>Y</enabled> + <name>LOGDATE</name> + </field> + <field> + <id>TRANSNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>STEPNAME</id> + <enabled>Y</enabled> + <name>STEPNAME</name> + </field> + <field> + <id>STEP_COPY</id> + <enabled>Y</enabled> + <name>STEP_COPY</name> + </field> + <field> + <id>LINES_READ</id> + <enabled>Y</enabled> + <name>LINES_READ</name> + </field> + <field> + <id>LINES_WRITTEN</id> + <enabled>Y</enabled> + <name>LINES_WRITTEN</name> + </field> + <field> + <id>LINES_UPDATED</id> + <enabled>Y</enabled> + <name>LINES_UPDATED</name> + </field> + <field> + <id>LINES_INPUT</id> + <enabled>Y</enabled> + <name>LINES_INPUT</name> + </field> + <field> + <id>LINES_OUTPUT</id> + <enabled>Y</enabled> + <name>LINES_OUTPUT</name> + </field> + <field> + <id>LINES_REJECTED</id> + <enabled>Y</enabled> + <name>LINES_REJECTED</name> + </field> + <field> + <id>ERRORS</id> + <enabled>Y</enabled> + <name>ERRORS</name> + </field> + <field> + <id>INPUT_BUFFER_ROWS</id> + <enabled>Y</enabled> + <name>INPUT_BUFFER_ROWS</name> + </field> + <field> + <id>OUTPUT_BUFFER_ROWS</id> + <enabled>Y</enabled> + <name>OUTPUT_BUFFER_ROWS</name> + </field> + </perf-log-table> + <channel-log-table> + <connection/> + <schema/> + <table/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>LOG_DATE</id> + <enabled>Y</enabled> + <name>LOG_DATE</name> + </field> + <field> + <id>LOGGING_OBJECT_TYPE</id> + <enabled>Y</enabled> + <name>LOGGING_OBJECT_TYPE</name> + </field> + <field> + <id>OBJECT_NAME</id> + <enabled>Y</enabled> + <name>OBJECT_NAME</name> + </field> + <field> + <id>OBJECT_COPY</id> + <enabled>Y</enabled> + <name>OBJECT_COPY</name> + </field> + <field> + <id>REPOSITORY_DIRECTORY</id> + <enabled>Y</enabled> + <name>REPOSITORY_DIRECTORY</name> + </field> + <field> + <id>FILENAME</id> + <enabled>Y</enabled> + <name>FILENAME</name> + </field> + <field> + <id>OBJECT_ID</id> + <enabled>Y</enabled> + <name>OBJECT_ID</name> + </field> + <field> + <id>OBJECT_REVISION</id> + <enabled>Y</enabled> + <name>OBJECT_REVISION</name> + </field> + <field> + <id>PARENT_CHANNEL_ID</id> + <enabled>Y</enabled> + <name>PARENT_CHANNEL_ID</name> + </field> + <field> + <id>ROOT_CHANNEL_ID</id> + <enabled>Y</enabled> + <name>ROOT_CHANNEL_ID</name> + </field> + </channel-log-table> + <step-log-table> + <connection/> + <schema/> + <table/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>LOG_DATE</id> + <enabled>Y</enabled> + <name>LOG_DATE</name> + </field> + <field> + <id>TRANSNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>STEPNAME</id> + <enabled>Y</enabled> + <name>STEPNAME</name> + </field> + <field> + <id>STEP_COPY</id> + <enabled>Y</enabled> + <name>STEP_COPY</name> + </field> + <field> + <id>LINES_READ</id> + <enabled>Y</enabled> + <name>LINES_READ</name> + </field> + <field> + <id>LINES_WRITTEN</id> + <enabled>Y</enabled> + <name>LINES_WRITTEN</name> + </field> + <field> + <id>LINES_UPDATED</id> + <enabled>Y</enabled> + <name>LINES_UPDATED</name> + </field> + <field> + <id>LINES_INPUT</id> + <enabled>Y</enabled> + <name>LINES_INPUT</name> + </field> + <field> + <id>LINES_OUTPUT</id> + <enabled>Y</enabled> + <name>LINES_OUTPUT</name> + </field> + <field> + <id>LINES_REJECTED</id> + <enabled>Y</enabled> + <name>LINES_REJECTED</name> + </field> + <field> + <id>ERRORS</id> + <enabled>Y</enabled> + <name>ERRORS</name> + </field> + <field> + <id>LOG_FIELD</id> + <enabled>N</enabled> + <name>LOG_FIELD</name> + </field> + </step-log-table> + <metrics-log-table> + <connection/> + <schema/> + <table/> + <timeout_days/> + <field> + <id>ID_BATCH</id> + <enabled>Y</enabled> + <name>ID_BATCH</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>LOG_DATE</id> + <enabled>Y</enabled> + <name>LOG_DATE</name> + </field> + <field> + <id>METRICS_DATE</id> + <enabled>Y</enabled> + <name>METRICS_DATE</name> + </field> + <field> + <id>METRICS_CODE</id> + <enabled>Y</enabled> + <name>METRICS_CODE</name> + </field> + <field> + <id>METRICS_DESCRIPTION</id> + <enabled>Y</enabled> + <name>METRICS_DESCRIPTION</name> + </field> + <field> + <id>METRICS_SUBJECT</id> + <enabled>Y</enabled> + <name>METRICS_SUBJECT</name> + </field> + <field> + <id>METRICS_TYPE</id> + <enabled>Y</enabled> + <name>METRICS_TYPE</name> + </field> + <field> + <id>METRICS_VALUE</id> + <enabled>Y</enabled> + <name>METRICS_VALUE</name> + </field> + </metrics-log-table> + </log> + <maxdate> + <connection/> + <table/> + <field/> + <offset>0.0</offset> + <maxdiff>0.0</maxdiff> + </maxdate> + <size_rowset>10000</size_rowset> + <sleep_time_empty>50</sleep_time_empty> + <sleep_time_full>50</sleep_time_full> + <unique_connections>N</unique_connections> + <feedback_shown>Y</feedback_shown> + <feedback_size>50000</feedback_size> + <using_thread_priorities>Y</using_thread_priorities> + <shared_objects_file/> + <capture_step_performance>N</capture_step_performance> + <step_performance_capturing_delay>1000</step_performance_capturing_delay> + <step_performance_capturing_size_limit>100</step_performance_capturing_size_limit> + <dependencies> + </dependencies> + <partitionschemas> + </partitionschemas> + <slaveservers> + </slaveservers> + <clusterschemas> + </clusterschemas> + <created_user>-</created_user> + <created_date>2025/07/04 09:06:25.348</created_date> + <modified_user>-</modified_user> + <modified_date>2025/07/04 09:06:25.348</modified_date> + <key_for_session_key/> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <connection> + <name>dw bi indoteknik (localhost)</name> + <server>localhost</server> + <type>POSTGRESQL</type> + <access>Native</access> + <database>dw_bi_indoteknik</database> + <port>5432</port> + <username>odoo</username> + <password>Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3</password> + <servername/> + <data_tablespace/> + <index_tablespace/> + <attributes> + <attribute> + <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>IS_CLUSTERED</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>PORT_NUMBER</code> + <attribute>5432</attribute> + </attribute> + <attribute> + <code>PRESERVE_RESERVED_WORD_CASE</code> + <attribute>Y</attribute> + </attribute> + <attribute> + <code>QUOTE_ALL_FIELDS</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>SUPPORTS_BOOLEAN_DATA_TYPE</code> + <attribute>Y</attribute> + </attribute> + <attribute> + <code>SUPPORTS_TIMESTAMP_DATA_TYPE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>USE_POOLING</code> + <attribute>N</attribute> + </attribute> + </attributes> + </connection> + <connection> + <name>erp indoteknik production (localhost)</name> + <server>10.148.0.3</server> + <type>POSTGRESQL</type> + <access>Native</access> + <database>erp_indoteknik</database> + <port>5432</port> + <username>pdi</username> + <password>Encrypted 2be989bb20084ca95810197629d9ea18e</password> + <servername/> + <data_tablespace/> + <index_tablespace/> + <attributes> + <attribute> + <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>IS_CLUSTERED</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>PORT_NUMBER</code> + <attribute>5432</attribute> + </attribute> + <attribute> + <code>PRESERVE_RESERVED_WORD_CASE</code> + <attribute>Y</attribute> + </attribute> + <attribute> + <code>QUOTE_ALL_FIELDS</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>SUPPORTS_BOOLEAN_DATA_TYPE</code> + <attribute>Y</attribute> + </attribute> + <attribute> + <code>SUPPORTS_TIMESTAMP_DATA_TYPE</code> + <attribute>N</attribute> + </attribute> + <attribute> + <code>USE_POOLING</code> + <attribute>N</attribute> + </attribute> + </attributes> + </connection> + <order> + <hop> + <from>Table input</from> + <to>Filter rows</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter rows</from> + <to>Select values</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values</from> + <to>param product id</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values</from> + <to>Select values 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>param product id</from> + <to>get last sales within 6 month before last in till now</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values 2</from> + <to>Sort rows</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>get last sales within 6 month before last in till now</from> + <to>Sort rows 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Sort rows</from> + <to>Merge join</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Sort rows 2</from> + <to>Merge join</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Merge join</from> + <to>Filter rows 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter rows 2</from> + <to>Write to log 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter rows 2</from> + <to>Delete</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter rows</from> + <to>Delete 2</to> + <enabled>Y</enabled> + </hop> + </order> + <step> + <name>Table input</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>dw bi indoteknik (localhost)</connection> + <sql>select product_id, last_in, last_in as last_in2, qty +from list_new_deadstock</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>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input</origin> + <comments>product_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>Timestamp</type> + <storagetype>normal</storagetype> + <name>last_in</name> + <length>6</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>last_in</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>last_in2</name> + <length>6</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>last_in2</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>BigNumber</type> + <storagetype>normal</storagetype> + <name>qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>qty</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/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>160</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Filter rows</name> + <type>FilterRows</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <send_true_to>Delete 2</send_true_to> + <send_false_to>Select values</send_false_to> + <compare> + <condition> + <negated>N</negated> + <leftvalue>qty</leftvalue> + <function><=</function> + <rightvalue/> + <value> + <name>constant</name> + <type>BigNumber</type> + <text>0.0</text> + <length>-1</length> + <precision>-1</precision> + <isnull>N</isnull> + <mask>######0.0###################;-######0.0###################</mask> + </value> + </condition> + </compare> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>320</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values</name> + <type>SelectValues</type> + <description/> + <distribute>N</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>product_id</name> + <rename/> + </field> + <field> + <name>last_in</name> + <rename/> + </field> + <field> + <name>last_in2</name> + <rename/> + </field> + <field> + <name>qty</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>320</xloc> + <yloc>208</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values 2</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>last_in</name> + <rename/> + </field> + <field> + <name>last_in2</name> + <rename/> + </field> + <field> + <name>qty</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>320</xloc> + <yloc>304</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>param product id</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>last_in</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>464</xloc> + <yloc>208</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>get last sales within 6 month before last in till now</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production (localhost)</connection> + <sql>select sm.product_id, sm.create_date as last_sales +from stock_move sm +join stock_location sl on sl.id = sm.location_dest_id +where 1=1 +and sl.name in ('Customers', 'Production') +and sm.state = 'done' +and sm.product_id = ? +and sm.create_date >= (?::timestamp - '180 days'::interval) --and sm.create_date <= ?</sql> + <limit>0</limit> + <lookup>param product id</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>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input 2</origin> + <comments>product_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>Timestamp</type> + <storagetype>normal</storagetype> + <name>last_sales</name> + <length>6</length> + <precision>-1</precision> + <origin>Table input 2</origin> + <comments>last_sales</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> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>736</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Sort rows</name> + <type>SortRows</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <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>product_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/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>528</xloc> + <yloc>304</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Sort rows 2</name> + <type>SortRows</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <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>product_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/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>736</xloc> + <yloc>208</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>product_id</key> + </keys_1> + <keys_2> + <key>product_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>736</xloc> + <yloc>304</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Filter rows 2</name> + <type>FilterRows</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <send_true_to>Delete</send_true_to> + <send_false_to>Write to log 2</send_false_to> + <compare> + <condition> + <negated>N</negated> + <leftvalue>product_id_1</leftvalue> + <function>IS NULL</function> + <rightvalue/> + </condition> + </compare> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>864</xloc> + <yloc>304</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Write to log 2</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>864</xloc> + <yloc>416</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Delete</name> + <type>Delete</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>dw bi indoteknik (localhost)</connection> + <commit>100</commit> + <lookup> + <schema>public</schema> + <table>list_new_deadstock</table> + <key> + <name>product_id</name> + <field>product_id</field> + <condition>=</condition> + <name2/> + </key> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>1008</xloc> + <yloc>304</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Delete 2</name> + <type>Delete</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>dw bi indoteknik (localhost)</connection> + <commit>100</commit> + <lookup> + <schema>public</schema> + <table>list_new_deadstock</table> + <key> + <name>product_id</name> + <field>product_id</field> + <condition>=</condition> + <name2/> + </key> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>448</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step_error_handling> + </step_error_handling> + <slave-step-copy-partition-distribution> + </slave-step-copy-partition-distribution> + <slave_transformation>N</slave_transformation> + <attributes/> +</transformation> diff --git a/update_list_deadstock_opening.ktr b/update_list_deadstock_opening.ktr index 354b1d8..55f9883 100644 --- a/update_list_deadstock_opening.ktr +++ b/update_list_deadstock_opening.ktr @@ -571,6 +571,11 @@ </hop> <hop> <from>Calculator</from> + <to>If field value is null 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>If field value is null 2</from> <to>Update</to> <enabled>Y</enabled> </hop> @@ -602,6 +607,21 @@ <grouping_symbol/> <currency_symbol/> </calculation> + <calculation> + <field_name>new_qty_available</field_name> + <calc_type>SUBTRACT</calc_type> + <field_a>qty</field_a> + <field_b>qty_outgoing</field_b> + <field_c/> + <value_type>None</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> <attributes/> <cluster_schema/> <remotesteps> @@ -957,7 +977,7 @@ order by product_id</sql> </value> <value> <name>qty_available_current</name> - <rename>qty</rename> + <rename>new_qty_available</rename> </value> <value> <name>brand</name> @@ -977,7 +997,7 @@ order by product_id</sql> </output> </remotesteps> <GUI> - <xloc>640</xloc> + <xloc>816</xloc> <yloc>304</yloc> <draw>Y</draw> </GUI> @@ -1022,7 +1042,7 @@ order by pp.id</sql> <name>product_id</name> <length>9</length> <precision>0</precision> - <origin>Table input 2</origin> + <origin>avg hpp</origin> <comments>product_id</comments> <conversion_Mask>####0;-####0</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -1045,7 +1065,7 @@ order by pp.id</sql> <name>last_cost</name> <length>-1</length> <precision>-1</precision> - <origin>Table input 2</origin> + <origin>avg hpp</origin> <comments>last_cost</comments> <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -1089,7 +1109,7 @@ order by pp.id</sql> <schema_name/> </partitioning> <connection>erp indoteknik production (localhost)</connection> - <sql>select sq.product_id, sum(quantity) as qty + <sql>select sq.product_id, sum(quantity) as qty, get_qty_outgoing(sq.product_id) as qty_outgoing from stock_quant sq join stock_location sl on sl.id = sq.location_id where sq.location_id in(select id from stock_location where usage = 'internal' and active = true) @@ -1148,6 +1168,29 @@ order by sq.product_id</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>qty_outgoing</name> + <length>-1</length> + <precision>-1</precision> + <origin>current qty</origin> + <comments>qty_outgoing</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/> @@ -1163,6 +1206,46 @@ order by sq.product_id</sql> <draw>Y</draw> </GUI> </step> + <step> + <name>If field value is null 2</name> + <type>IfNull</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <replaceAllByValue>0</replaceAllByValue> + <replaceAllMask/> + <selectFields>Y</selectFields> + <selectValuesType>N</selectValuesType> + <setEmptyStringAll>N</setEmptyStringAll> + <valuetypes> + </valuetypes> + <fields> + <field> + <name>new_qty_available</name> + <value>0</value> + <mask/> + <set_empty_string>N</set_empty_string> + </field> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>656</xloc> + <yloc>304</yloc> + <draw>Y</draw> + </GUI> + </step> <step_error_handling> </step_error_handling> <slave-step-copy-partition-distribution> |