From e0baab61816dcc3309ebcfb574f01b3dcee65cce Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Sat, 5 Jul 2025 10:05:51 +0700 Subject: update combine deadstock --- calculate_no_sales_stock.ktr | 1670 ++++++++++++++++++------------------- combine_deadstock.ktr | 535 ++++++++++++ deadstock_job.kjb | 98 ++- delete_useless_data_deadstock.ktr | 1256 ++++++++++++++++++++++++++++ update_list_deadstock_opening.ktr | 93 ++- 5 files changed, 2790 insertions(+), 862 deletions(-) create mode 100644 combine_deadstock.ktr create mode 100644 delete_useless_data_deadstock.ktr 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 @@ -658,11 +658,6 @@ Merge join 2 Y - - Merge join 2 - If field value is null - Y - If field value is null Calculator @@ -678,7 +673,58 @@ Insert / update Y + + Merge join 2 + Calculator 2 + Y + + + Calculator 2 + If field value is null + Y + + + Calculator + Calculator + + Y + + 1 + + none + + + Y + + total_cost + MULTIPLY + new_qty + last_cost + + Number + -1 + -1 + N + + + + + + + + + + + + + + + 480 + 368 + Y + + Filter rows FilterRows @@ -750,8 +796,8 @@ - Stream lookup - StreamLookup + If field value is null + IfNull Y @@ -760,23 +806,39 @@ none - dead stock - N - Y - N - N - - - product_id - product_id - - - product_id - in_deadstock - - Integer - - + 0 + + Y + N + N + + + + + aging + 0 + + N + + + last_cost + 0 + + N + + + qty + 0 + + N + + + qty_outgoing + 0 + + N + + @@ -786,14 +848,14 @@ - 224 - 144 + 608 + 368 Y - Stream lookup 2 - StreamLookup + Insert / update + InsertUpdate Y @@ -802,21 +864,67 @@ none - movement - N - Y - N - N + dw bi indoteknik (localhost) + 100 + N + public + list_new_deadstock
product_id product_id + = + product_id - in_movement - - None + product_id + N + + + item_code + item_code + Y + + + product_name + product_name + Y + + + brand + brand + Y + + + qty + qty + Y + + + date_added + date_added + N + + + last_in + last_in + Y + + + aging + aging + Y + + + last_cost + last_cost + Y + + + total_cost + total_cost + Y @@ -828,14 +936,14 @@ - 560 - 144 + 208 + 368 Y - Write to log - WriteToLog + Merge join + MergeJoin Y @@ -844,13 +952,15 @@ none - log_level_basic - Y - N - 0 - - - + LEFT OUTER + Sort rows + Sort rows 2 + + product_id + + + product_id + @@ -860,14 +970,14 @@ - 400 - 224 + 1072 + 144 Y - Write to log 2 - WriteToLog + Merge join 2 + MergeJoin Y @@ -876,13 +986,15 @@ none - log_level_basic - Y - N - 0 - - - + LEFT OUTER + Sort rows 3 + Sort rows 4 + + product_id + + + product_id + @@ -892,55 +1004,61 @@ - 720 - 224 + 880 + 368 Y - dead stock - TableInput + Select values + SelectValues - Y + N 1 none - dw bi indoteknik (localhost) - select distinct product_id from list_deadstock - 0 - - N - N - N - N - - - Integer - normal + + product_id - 9 - 0 - Table input - product_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - + + + + item_code + + + + product_name + + + + brand + + + + qty + + + + date_added + + + + in_deadstock + + + + in_movement + + + + qty_outgoing + + + N + @@ -950,14 +1068,14 @@ - 224 - 48 + 864 + 144 Y - movement - TableInput + Select values 2 + SelectValues Y @@ -966,45 +1084,45 @@ none - dw bi indoteknik (localhost) - 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) - 0 - - N - N - N - N - - - Integer - normal + + product_id - 9 - 0 - movement - product_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - + + + + item_code + + + + product_name + + + + brand + + + + qty + + + + date_added + + + + in_deadstock + + + + in_movement + + + + qty_outgoing + + + N + @@ -1014,160 +1132,73 @@ and sm.create_date >= (now() - '180 days'::interval) - 560 - 48 + 864 + 256 Y - stock quant - TableInput + Select values 3 + SelectValues - Y + N 1 none - erp indoteknik production (localhost) - 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 - 0 - - N - N - N - N - - - Integer - normal + + product_id - 9 - 0 - stock quant - product_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + + + item_code - 2147483647 - -1 - stock quant - item_code - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + + + product_name - 2147483647 - -1 - stock quant - product_name - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + + + brand - 2147483647 - -1 - stock quant - brand - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Number - normal + + + qty - -1 - -1 - stock quant - qty - ####0.0#########;-####0.0######### - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - + + + + date_added + + + + in_deadstock + + + + in_movement + + + + product_id_1 + + + + last_in + + + + aging + + + + qty_outgoing + + + N + @@ -1177,16 +1208,16 @@ order by sq.product_id - 80 + 1216 144 Y - Select values + Select values 4 SelectValues - N + Y 1 @@ -1226,36 +1257,20 @@ order by sq.product_id in_movement - N - - - - - - - - - - - 864 - 144 - Y - - - - param product id - SelectValues - - Y - - 1 - - none - - - - product_id + product_id_1 + + + + last_in + + + + aging + + + + qty_outgoing N @@ -1269,13 +1284,13 @@ order by sq.product_id - 864 - 48 + 1072 + 256 Y - Select values 2 + Select values 5 SelectValues Y @@ -1303,19 +1318,27 @@ order by sq.product_id - qty - + new_qty + qty date_added - in_deadstock + last_in - in_movement + aging + + + + last_cost + + + + total_cost N @@ -1329,14 +1352,14 @@ order by sq.product_id - 864 - 256 + 352 + 368 Y - get last in - TableInput + Sort rows + SortRows Y @@ -1345,94 +1368,23 @@ order by sq.product_id none - dw bi indoteknik (localhost) - 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 - 0 - param product id - Y - Y - N - N - - - Integer - normal + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + product_id - 9 - 0 - get last in - product_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Timestamp - normal - last_in - 6 - -1 - get last in - last_in - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Number - normal - aging - -1 - -1 - get last in - aging - ####0.0#########;-####0.0######### - . - , - - none - N - Y + Y + N + N 0 - N - N - N - en_US - Asia/Bangkok - N - - + N + + @@ -1443,12 +1395,12 @@ limit 1 976 - 48 + 144 Y - Sort rows + Sort rows 2 SortRows Y @@ -1484,13 +1436,13 @@ limit 1 - 976 - 144 + 1072 + 48 Y - Sort rows 2 + Sort rows 3 SortRows Y @@ -1508,14 +1460,6 @@ limit 1 N - - product_id - Y - N - N - 0 - N - @@ -1526,14 +1470,14 @@ limit 1 - 1072 - 48 + 1008 + 336 Y - Merge join - MergeJoin + Sort rows 4 + SortRows Y @@ -1542,15 +1486,15 @@ limit 1 none - LEFT OUTER - Sort rows - Sort rows 2 - - product_id - - - product_id - + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + @@ -1560,69 +1504,81 @@ limit 1 - 1072 + 1120 + 368 + Y + + + + Stream lookup + StreamLookup + + Y + + 1 + + none + + + dead stock + N + Y + N + N + + + product_id + product_id + + + product_id + in_deadstock + + Integer + + + + + + + + + + + + 224 144 Y - Select values 3 - SelectValues + Stream lookup 2 + StreamLookup - N + Y 1 none - - + movement + N + Y + N + N + + product_id - - - - item_code - - - - product_name - - - - brand - - - - qty - - - - date_added - - - - in_deadstock - - - - in_movement - - - - product_id_1 - - - - last_in - - - - aging - - - N - + product_id + + + product_id + in_movement + + None + + @@ -1632,14 +1588,14 @@ limit 1 - 1216 + 560 144 Y - param product id 2 - SelectValues + Write to log + WriteToLog Y @@ -1648,13 +1604,13 @@ limit 1 none + log_level_basic + Y + N + 0 + - - product_id - - - N - + @@ -1664,14 +1620,14 @@ limit 1 - 1216 - 256 + 400 + 224 Y - Select values 4 - SelectValues + Write to log 2 + WriteToLog Y @@ -1680,53 +1636,13 @@ limit 1 none + log_level_basic + Y + N + 0 + - - product_id - - - - item_code - - - - product_name - - - - brand - - - - qty - - - - date_added - - - - in_deadstock - - - - in_movement - - - - product_id_1 - - - - last_in - - - - aging - - - N - + @@ -1736,8 +1652,8 @@ limit 1 - 1072 - 256 + 720 + 224 Y @@ -1781,36 +1697,13 @@ order by pp.id product_id 9 0 - stock quant + avg hpp product_id ####0;-####0 . , none - Y - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal - item_code - 2147483647 - -1 - stock quant - item_code - - . - , - - none N Y 0 @@ -1822,62 +1715,16 @@ order by pp.id N - String + BigNumber normal - product_name - 2147483647 + last_cost + -1 -1 - stock quant - product_name - + avg hpp + last_cost + ######0.0###################;-######0.0################### . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal - brand - 2147483647 - -1 - stock quant - brand - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Number - normal - qty - -1 - -1 - stock quant - qty - ####0.0#########;-####0.0######### - . - , + none N @@ -1890,13 +1737,48 @@ order by pp.id Asia/Bangkok N + + + + + + + + + + + 1216 + 368 + Y + + + + dead stock + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select distinct product_id from list_deadstock + 0 + + N + N + N + N + Integer normal - in_deadstock + product_id 9 0 - Stream lookup + dead stock product_id ####0;-####0 . @@ -1913,13 +1795,57 @@ order by pp.id Asia/Bangkok N + + + + + + + + + + + 224 + 48 + Y + + + + get last in + TableInput + + Y + + 1 + + none + + + erp indoteknik production (localhost) + 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 + 0 + param product id + Y + Y + N + N + Integer normal - in_movement + product_id 9 0 - Stream lookup 2 + get last in product_id ####0;-####0 . @@ -1937,19 +1863,19 @@ order by pp.id N - Integer + Timestamp normal - product_id_1 - 9 - 0 - Merge join - product_id - ####0;-####0 + last_in + 6 + -1 + get last in + last_in + . , none - Y + N Y 0 N @@ -1960,14 +1886,14 @@ order by pp.id N - Timestamp + Number normal - last_in - 6 + aging + -1 -1 get last in - last_in - + aging + ####0.0#########;-####0.0######### . , @@ -1992,14 +1918,14 @@ order by pp.id - 1216 - 368 + 976 + 48 Y - Sort rows 3 - SortRows + movement + TableInput Y @@ -2008,15 +1934,45 @@ order by pp.id none - %%java.io.tmpdir%% - out - 1000000 - - N - - N - - + dw bi indoteknik (localhost) + 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) + 0 + + N + N + N + N + + + Integer + normal + product_id + 9 + 0 + movement + product_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -2026,14 +1982,14 @@ order by pp.id - 1008 - 336 + 560 + 48 Y - Sort rows 4 - SortRows + param product id + SelectValues Y @@ -2042,14 +1998,11 @@ order by pp.id none - %%java.io.tmpdir%% - out - 1000000 - - N - - N + + product_id + + N @@ -2060,14 +2013,14 @@ order by pp.id - 1120 - 368 + 864 + 48 Y - Merge join 2 - MergeJoin + param product id 2 + SelectValues Y @@ -2076,15 +2029,12 @@ order by pp.id none - LEFT OUTER - Sort rows 3 - Sort rows 4 - - product_id - - - product_id - + + + product_id + + N + @@ -2094,14 +2044,14 @@ order by pp.id - 880 - 368 + 1216 + 256 Y - Select values 5 - SelectValues + stock quant + TableInput Y @@ -2110,95 +2060,191 @@ order by pp.id none - - - product_id - - - + erp indoteknik production (localhost) + 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 + 0 + + N + N + N + N + + + Integer + normal + product_id + 9 + 0 + stock quant + product_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal item_code - - - + 2147483647 + -1 + stock quant + item_code + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal product_name - - - + 2147483647 + -1 + stock quant + product_name + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal brand - - - + 2147483647 + -1 + stock quant + brand + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Number + normal qty - - - + -1 + -1 + stock quant + qty + ####0.0#########;-####0.0######### + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal date_added - - - - last_in - - - - aging - - - - last_cost - - - - total_cost - - - N - - - - - - - - - - - 432 - 368 - Y - - - - If field value is null - IfNull - - Y - - 1 - - none - - - 0 - - Y - N - N - - - - - aging - 0 - - N - - - last_cost - 0 - - N - - + 6 + -1 + stock quant + date_added + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + qty_outgoing + -1 + -1 + stock quant + qty_outgoing + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -2208,13 +2254,13 @@ order by pp.id - 752 - 368 + 80 + 144 Y - Calculator + Calculator 2 Calculator Y @@ -2226,12 +2272,12 @@ order by pp.id Y - total_cost - MULTIPLY + new_qty + SUBTRACT qty - last_cost + qty_outgoing - Number + None -1 -1 N @@ -2249,95 +2295,7 @@ order by pp.id - 608 - 368 - Y - - - - Insert / update - InsertUpdate - - Y - - 1 - - none - - - dw bi indoteknik (localhost) - 100 - N - - public - list_new_deadstock
- - product_id - product_id - = - - - - product_id - product_id - N - - - item_code - item_code - Y - - - product_name - product_name - Y - - - brand - brand - Y - - - qty - qty - Y - - - date_added - date_added - Y - - - last_in - last_in - Y - - - aging - aging - Y - - - last_cost - last_cost - Y - - - total_cost - total_cost - Y - - - - - - - - - - - - 288 + 736 368 Y 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 @@ + + + + combine_deadstock + + + + Normal + / + + + + + + + + + + + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + TRANSNAME + Y + TRANSNAME + + + STATUS + Y + STATUS + + + LINES_READ + Y + LINES_READ + + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + + LINES_UPDATED + Y + LINES_UPDATED + + + + LINES_INPUT + Y + LINES_INPUT + + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + + LINES_REJECTED + Y + LINES_REJECTED + + + + ERRORS + Y + ERRORS + + + STARTDATE + Y + STARTDATE + + + ENDDATE + Y + ENDDATE + + + LOGDATE + Y + LOGDATE + + + DEPDATE + Y + DEPDATE + + + REPLAYDATE + Y + REPLAYDATE + + + LOG_FIELD + Y + LOG_FIELD + + + EXECUTING_SERVER + N + EXECUTING_SERVER + + + EXECUTING_USER + N + EXECUTING_USER + + + CLIENT + N + CLIENT + + + + + +
+ + + + ID_BATCH + Y + ID_BATCH + + + SEQ_NR + Y + SEQ_NR + + + LOGDATE + Y + LOGDATE + + + TRANSNAME + Y + TRANSNAME + + + STEPNAME + Y + STEPNAME + + + STEP_COPY + Y + STEP_COPY + + + LINES_READ + Y + LINES_READ + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + LINES_UPDATED + Y + LINES_UPDATED + + + LINES_INPUT + Y + LINES_INPUT + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + LINES_REJECTED + Y + LINES_REJECTED + + + ERRORS + Y + ERRORS + + + INPUT_BUFFER_ROWS + Y + INPUT_BUFFER_ROWS + + + OUTPUT_BUFFER_ROWS + Y + OUTPUT_BUFFER_ROWS + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + LOGGING_OBJECT_TYPE + Y + LOGGING_OBJECT_TYPE + + + OBJECT_NAME + Y + OBJECT_NAME + + + OBJECT_COPY + Y + OBJECT_COPY + + + REPOSITORY_DIRECTORY + Y + REPOSITORY_DIRECTORY + + + FILENAME + Y + FILENAME + + + OBJECT_ID + Y + OBJECT_ID + + + OBJECT_REVISION + Y + OBJECT_REVISION + + + PARENT_CHANNEL_ID + Y + PARENT_CHANNEL_ID + + + ROOT_CHANNEL_ID + Y + ROOT_CHANNEL_ID + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + TRANSNAME + Y + TRANSNAME + + + STEPNAME + Y + STEPNAME + + + STEP_COPY + Y + STEP_COPY + + + LINES_READ + Y + LINES_READ + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + LINES_UPDATED + Y + LINES_UPDATED + + + LINES_INPUT + Y + LINES_INPUT + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + LINES_REJECTED + Y + LINES_REJECTED + + + ERRORS + Y + ERRORS + + + LOG_FIELD + N + LOG_FIELD + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + METRICS_DATE + Y + METRICS_DATE + + + METRICS_CODE + Y + METRICS_CODE + + + METRICS_DESCRIPTION + Y + METRICS_DESCRIPTION + + + METRICS_SUBJECT + Y + METRICS_SUBJECT + + + METRICS_TYPE + Y + METRICS_TYPE + + + METRICS_VALUE + Y + METRICS_VALUE + + + + + +
+ + 0.0 + 0.0 + + 10000 + 50 + 50 + N + Y + 50000 + Y + + N + 1000 + 100 + + + + + + + + + - + 2025/07/04 10:32:24.680 + - + 2025/07/04 10:32:24.680 + + N + + + + + dw bi indoteknik (localhost) + localhost + POSTGRESQL + Native + dw_bi_indoteknik + 5432 + odoo + Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 + + + + + + FORCE_IDENTIFIERS_TO_LOWERCASE + N + + + FORCE_IDENTIFIERS_TO_UPPERCASE + N + + + IS_CLUSTERED + N + + + PORT_NUMBER + 5432 + + + PRESERVE_RESERVED_WORD_CASE + Y + + + QUOTE_ALL_FIELDS + N + + + SUPPORTS_BOOLEAN_DATA_TYPE + Y + + + SUPPORTS_TIMESTAMP_DATA_TYPE + N + + + USE_POOLING + N + + + + + + + Execute SQL script + ExecSQL + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + N + N + N + N + refresh materialized view mv_combine_deadstock; + N + + + + + + + + + + + + + + + + 240 + 96 + Y + + + + + + + N + + 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 @@ N Y 0 - 736 + 1168 + 96 + + + + delete useless deadstock + + TRANS + + filename + + /home/stephan/transformation-job-bi/delete_useless_data_deadstock.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 784 + 96 + + + + combine deadstock + + TRANS + + filename + + /home/stephan/transformation-job-bi/combine_deadstock.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 992 96 @@ -420,6 +498,24 @@ calculate no sales stock + delete useless deadstock + 0 + 0 + Y + Y + N + + + delete useless deadstock + combine deadstock + 0 + 0 + Y + Y + N + + + combine deadstock Success 0 0 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 @@ + + + + delete_useless_data_deadstock + + + + Normal + / + + + + + + +
+ + + + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + TRANSNAME + Y + TRANSNAME + + + STATUS + Y + STATUS + + + LINES_READ + Y + LINES_READ + + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + + LINES_UPDATED + Y + LINES_UPDATED + + + + LINES_INPUT + Y + LINES_INPUT + + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + + LINES_REJECTED + Y + LINES_REJECTED + + + + ERRORS + Y + ERRORS + + + STARTDATE + Y + STARTDATE + + + ENDDATE + Y + ENDDATE + + + LOGDATE + Y + LOGDATE + + + DEPDATE + Y + DEPDATE + + + REPLAYDATE + Y + REPLAYDATE + + + LOG_FIELD + Y + LOG_FIELD + + + EXECUTING_SERVER + N + EXECUTING_SERVER + + + EXECUTING_USER + N + EXECUTING_USER + + + CLIENT + N + CLIENT + + + + + +
+ + + + ID_BATCH + Y + ID_BATCH + + + SEQ_NR + Y + SEQ_NR + + + LOGDATE + Y + LOGDATE + + + TRANSNAME + Y + TRANSNAME + + + STEPNAME + Y + STEPNAME + + + STEP_COPY + Y + STEP_COPY + + + LINES_READ + Y + LINES_READ + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + LINES_UPDATED + Y + LINES_UPDATED + + + LINES_INPUT + Y + LINES_INPUT + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + LINES_REJECTED + Y + LINES_REJECTED + + + ERRORS + Y + ERRORS + + + INPUT_BUFFER_ROWS + Y + INPUT_BUFFER_ROWS + + + OUTPUT_BUFFER_ROWS + Y + OUTPUT_BUFFER_ROWS + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + LOGGING_OBJECT_TYPE + Y + LOGGING_OBJECT_TYPE + + + OBJECT_NAME + Y + OBJECT_NAME + + + OBJECT_COPY + Y + OBJECT_COPY + + + REPOSITORY_DIRECTORY + Y + REPOSITORY_DIRECTORY + + + FILENAME + Y + FILENAME + + + OBJECT_ID + Y + OBJECT_ID + + + OBJECT_REVISION + Y + OBJECT_REVISION + + + PARENT_CHANNEL_ID + Y + PARENT_CHANNEL_ID + + + ROOT_CHANNEL_ID + Y + ROOT_CHANNEL_ID + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + TRANSNAME + Y + TRANSNAME + + + STEPNAME + Y + STEPNAME + + + STEP_COPY + Y + STEP_COPY + + + LINES_READ + Y + LINES_READ + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + LINES_UPDATED + Y + LINES_UPDATED + + + LINES_INPUT + Y + LINES_INPUT + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + LINES_REJECTED + Y + LINES_REJECTED + + + ERRORS + Y + ERRORS + + + LOG_FIELD + N + LOG_FIELD + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + METRICS_DATE + Y + METRICS_DATE + + + METRICS_CODE + Y + METRICS_CODE + + + METRICS_DESCRIPTION + Y + METRICS_DESCRIPTION + + + METRICS_SUBJECT + Y + METRICS_SUBJECT + + + METRICS_TYPE + Y + METRICS_TYPE + + + METRICS_VALUE + Y + METRICS_VALUE + + + + + +
+ + 0.0 + 0.0 + + 10000 + 50 + 50 + N + Y + 50000 + Y + + N + 1000 + 100 + + + + + + + + + - + 2025/07/04 09:06:25.348 + - + 2025/07/04 09:06:25.348 + + N + + + + + dw bi indoteknik (localhost) + localhost + POSTGRESQL + Native + dw_bi_indoteknik + 5432 + odoo + Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 + + + + + + FORCE_IDENTIFIERS_TO_LOWERCASE + N + + + FORCE_IDENTIFIERS_TO_UPPERCASE + N + + + IS_CLUSTERED + N + + + PORT_NUMBER + 5432 + + + PRESERVE_RESERVED_WORD_CASE + Y + + + QUOTE_ALL_FIELDS + N + + + SUPPORTS_BOOLEAN_DATA_TYPE + Y + + + SUPPORTS_TIMESTAMP_DATA_TYPE + N + + + USE_POOLING + N + + + + + erp indoteknik production (localhost) + 10.148.0.3 + POSTGRESQL + Native + erp_indoteknik + 5432 + pdi + Encrypted 2be989bb20084ca95810197629d9ea18e + + + + + + FORCE_IDENTIFIERS_TO_LOWERCASE + N + + + FORCE_IDENTIFIERS_TO_UPPERCASE + N + + + IS_CLUSTERED + N + + + PORT_NUMBER + 5432 + + + PRESERVE_RESERVED_WORD_CASE + Y + + + QUOTE_ALL_FIELDS + N + + + SUPPORTS_BOOLEAN_DATA_TYPE + Y + + + SUPPORTS_TIMESTAMP_DATA_TYPE + N + + + USE_POOLING + N + + + + + + Table input + Filter rows + Y + + + Filter rows + Select values + Y + + + Select values + param product id + Y + + + Select values + Select values 2 + Y + + + param product id + get last sales within 6 month before last in till now + Y + + + Select values 2 + Sort rows + Y + + + get last sales within 6 month before last in till now + Sort rows 2 + Y + + + Sort rows + Merge join + Y + + + Sort rows 2 + Merge join + Y + + + Merge join + Filter rows 2 + Y + + + Filter rows 2 + Write to log 2 + Y + + + Filter rows 2 + Delete + Y + + + Filter rows + Delete 2 + Y + + + + Table input + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select product_id, last_in, last_in as last_in2, qty +from list_new_deadstock + 0 + + N + N + N + N + + + Integer + normal + product_id + 9 + 0 + Table input + product_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + last_in + 6 + -1 + Table input + last_in + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + last_in2 + 6 + -1 + Table input + last_in2 + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + qty + -1 + -1 + Table input + qty + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 160 + 96 + Y + + + + Filter rows + FilterRows + + Y + + 1 + + none + + + Delete 2 + Select values + + + N + qty + <= + + + constant + BigNumber + 0.0 + -1 + -1 + N + ######0.0###################;-######0.0################### + + + + + + + + + + + + + 320 + 96 + Y + + + + Select values + SelectValues + + N + + 1 + + none + + + + + product_id + + + + last_in + + + + last_in2 + + + + qty + + + N + + + + + + + + + + + 320 + 208 + Y + + + + Select values 2 + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + last_in + + + + last_in2 + + + + qty + + + N + + + + + + + + + + + 320 + 304 + Y + + + + param product id + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + last_in + + + N + + + + + + + + + + + 464 + 208 + Y + + + + get last sales within 6 month before last in till now + TableInput + + Y + + 1 + + none + + + erp indoteknik production (localhost) + 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 <= ? + 0 + param product id + Y + Y + N + N + + + Integer + normal + product_id + 9 + 0 + Table input 2 + product_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + last_sales + 6 + -1 + Table input 2 + last_sales + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 736 + 96 + Y + + + + Sort rows + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_id + Y + N + N + 0 + N + + + + + + + + + + + + 528 + 304 + Y + + + + Sort rows 2 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_id + Y + N + N + 0 + N + + + + + + + + + + + + 736 + 208 + Y + + + + Merge join + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Sort rows + Sort rows 2 + + product_id + + + product_id + + + + + + + + + + + 736 + 304 + Y + + + + Filter rows 2 + FilterRows + + Y + + 1 + + none + + + Delete + Write to log 2 + + + N + product_id_1 + IS NULL + + + + + + + + + + + + + 864 + 304 + Y + + + + Write to log 2 + WriteToLog + + Y + + 1 + + none + + + log_level_basic + Y + N + 0 + + + + + + + + + + + + + 864 + 416 + Y + + + + Delete + Delete + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + 100 + + public +
list_new_deadstock
+ + product_id + product_id + = + + + + + + + + + + + + + 1008 + 304 + Y + + + + Delete 2 + Delete + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + 100 + + public + list_new_deadstock
+ + product_id + product_id + = + + + + + + + + + + + + + 448 + 96 + Y + + + + + + + N + + 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 @@ Calculator + If field value is null 2 + Y + + + If field value is null 2 Update Y @@ -602,6 +607,21 @@ + + new_qty_available + SUBTRACT + qty + qty_outgoing + + None + -1 + -1 + N + + + + + @@ -957,7 +977,7 @@ order by product_id qty_available_current - qty + new_qty_available brand @@ -977,7 +997,7 @@ order by product_id - 640 + 816 304 Y @@ -1022,7 +1042,7 @@ order by pp.id product_id 9 0 - Table input 2 + avg hpp product_id ####0;-####0 . @@ -1045,7 +1065,7 @@ order by pp.id last_cost -1 -1 - Table input 2 + avg hpp last_cost ######0.0###################;-######0.0################### . @@ -1089,7 +1109,7 @@ order by pp.id erp indoteknik production (localhost) - select sq.product_id, sum(quantity) as qty + 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 Asia/Bangkok N + + BigNumber + normal + qty_outgoing + -1 + -1 + current qty + qty_outgoing + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + @@ -1163,6 +1206,46 @@ order by sq.product_id Y + + If field value is null 2 + IfNull + + Y + + 1 + + none + + + 0 + + Y + N + N + + + + + new_qty_available + 0 + + N + + + + + + + + + + + + 656 + 304 + Y + + -- cgit v1.2.3