calculate_inventory 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 - 2023/10/20 11:20:34.675 - 2023/10/20 11:20:34.675 N dw bi indoteknik (localhost) localhost POSTGRESQL Native dw_bi_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 Add constants Y Add constants Filter rows Y Filter rows Sort rows Y Sort rows Merge join Y month1 Merge join Y month2 Merge join 2 Y Merge join Merge join 2 Y Merge join 2 Merge join 3 Y month3 Merge join 3 Y month4 Merge join 4 Y Merge join 3 Merge join 4 Y month5 Merge join 5 Y Merge join 4 Merge join 5 Y month6 Merge join 6 Y Merge join 5 Merge join 6 Y Merge join 6 Select values Y Select values If field value is null Y Filter rows Select values 2 Y Select values 2 Add constants 2 Y If field value is null Append streams Y Add constants 2 Append streams Y Append streams Add constants 3 Y Add constants 3 Calculator Y Calculator Select values 3 Y Select values 3 Insert / update Y Table input TableInput Y 1 none dw bi indoteknik (localhost) select viv.*, case when (now()::date-coalesce(viv.last_out::date, viv.last_in::date)) > 120 then 'dead' else 'good' end as status from v_inventory_value viv 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 String normal item_code 2147483647 -1 Table input item_code . , none N Y 0 N N N en_US Asia/Bangkok N String normal product 2147483647 -1 Table input product . , none N Y 0 N N N en_US Asia/Bangkok N Number normal sum -1 -1 Table input sum ####0.0#########;-####0.0######### . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal value -1 -1 Table input value ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Date normal last_out -1 -1 Table input last_out . , none N Y 0 N N N en_US Asia/Bangkok N Date normal last_in -1 -1 Table input last_in . , none N Y 0 N N N en_US Asia/Bangkok N String normal brand 2147483647 -1 Table input brand . , none N Y 0 N N N en_US Asia/Bangkok N String normal status 2147483647 -1 Table input status . , none N Y 0 N N N en_US Asia/Bangkok N

144 112 Y Add constants Constant Y 1 none constant_good String good -1 -1 N constant_dead String dead -1 -1 N

272 112 Y Filter rows FilterRows Y 1 none Sort rows Select values 2 N status = constant_good

416 112 Y Sort rows SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N product_id Y N N 0 N

560 112 Y Merge join MergeJoin Y 1 none LEFT OUTER Sort rows month1 product_id product_id

656 112 Y month1 TableInput Y 1 none dw bi indoteknik (localhost) select sm.product_id, sum(sm.product_uom_qty) as qty_month1, count(sm.product_id) as hit_month1, case when count(sm.product_id) > 0 then 1 else 0 end as month_count1 from stock_move sm join stock_picking sp on sp.id = sm.picking_id where sp.state = 'done' and sp.name like '%OUT%' and sp.date_done >= (now()-'30 days'::interval) group by sm.product_id order by sm.product_id 0 N N N N Integer normal product_id 9 0 month1 product_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal qty_month1 -1 -1 month1 qty_month1 ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Integer normal hit_month1 15 0 month1 hit_month1 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal month_count1 9 0 month1 month_count1 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

656 16 Y Merge join 2 MergeJoin Y 1 none LEFT OUTER Merge join month2 product_id product_id

768 112 Y month2 TableInput Y 1 none dw bi indoteknik (localhost) select sm.product_id, sum(sm.product_uom_qty) as qty_month2, count(sm.product_id) as hit_month2, case when count(sm.product_id) > 0 then 1 else 0 end as month_count2 from stock_move sm join stock_picking sp on sp.id = sm.picking_id where sp.state = 'done' and sp.name like '%OUT%' and sp.date_done >= (now()-'60 days'::interval) and sp.date_done < (now()-'30 days'::interval) group by sm.product_id order by sm.product_id 0 N N N N Integer normal product_id 9 0 month2 product_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal qty_month2 -1 -1 month2 qty_month2 ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Integer normal hit_month2 15 0 month2 hit_month2 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal month_count2 9 0 month2 month_count2 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

768 16 Y month3 TableInput Y 1 none dw bi indoteknik (localhost) select sm.product_id, sum(sm.product_uom_qty) as qty_month3, count(sm.product_id) as hit_month3, case when count(sm.product_id) > 0 then 1 else 0 end as month_count3 from stock_move sm join stock_picking sp on sp.id = sm.picking_id where sp.state = 'done' and sp.name like '%OUT%' and sp.date_done >= (now()-'90 days'::interval) and sp.date_done < (now()-'60 days'::interval) group by sm.product_id order by sm.product_id 0 N N N N Integer normal product_id 9 0 month3 product_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal qty_month3 -1 -1 month3 qty_month3 ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Integer normal hit_month3 15 0 month3 hit_month3 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal month_count3 9 0 month3 month_count3 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

880 16 Y Merge join 3 MergeJoin Y 1 none LEFT OUTER Merge join 2 month3 product_id product_id

880 112 Y Merge join 4 MergeJoin Y 1 none LEFT OUTER Merge join 3 month4 product_id product_id

1008 112 Y month4 TableInput Y 1 none dw bi indoteknik (localhost) select sm.product_id, sum(sm.product_uom_qty) as qty_month4, count(sm.product_id) as hit_month4, case when count(sm.product_id) > 0 then 1 else 0 end as month_count4 from stock_move sm join stock_picking sp on sp.id = sm.picking_id where sp.state = 'done' and sp.name like '%OUT%' and sp.date_done >= (now()-'120 days'::interval) and sp.date_done < (now()-'90 days'::interval) group by sm.product_id order by sm.product_id 0 N N N N Integer normal product_id 9 0 month4 product_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal qty_month4 -1 -1 month4 qty_month4 ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Integer normal hit_month4 15 0 month4 hit_month4 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal month_count4 9 0 month4 month_count4 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

1008 16 Y Merge join 5 MergeJoin Y 1 none LEFT OUTER Merge join 4 month5 product_id product_id

1120 112 Y month5 TableInput Y 1 none dw bi indoteknik (localhost) select sm.product_id, sum(sm.product_uom_qty) as qty_month5, count(sm.product_id) as hit_month5, case when count(sm.product_id) > 0 then 1 else 0 end as month_count5 from stock_move sm join stock_picking sp on sp.id = sm.picking_id where sp.state = 'done' and sp.name like '%OUT%' and sp.date_done >= (now()-'150 days'::interval) and sp.date_done < (now()-'120 days'::interval) group by sm.product_id order by sm.product_id 0 N N N N Integer normal product_id 9 0 month5 product_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal qty_month5 -1 -1 month5 qty_month5 ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Integer normal hit_month5 15 0 month5 hit_month5 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal month_count5 9 0 month5 month_count5 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

1120 16 Y month6 TableInput Y 1 none dw bi indoteknik (localhost) select sm.product_id, sum(sm.product_uom_qty) as qty_month6, count(sm.product_id) as hit_month6, case when count(sm.product_id) > 0 then 1 else 0 end as month_count6 from stock_move sm join stock_picking sp on sp.id = sm.picking_id where sp.state = 'done' and sp.name like '%OUT%' and sp.date_done >= (now()-'180 days'::interval) and sp.date_done < (now()-'150 days'::interval) group by sm.product_id order by sm.product_id 0 N N N N Integer normal product_id 9 0 month6 product_id ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N BigNumber normal qty_month6 -1 -1 month6 qty_month6 ######0.0###################;-######0.0################### . none N Y 0 N N N en_US Asia/Bangkok N Integer normal hit_month6 15 0 month6 hit_month6 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N Integer normal month_count6 9 0 month6 month_count6 ####0;-####0 . , none N Y 0 N N N en_US Asia/Bangkok N

1232 16 Y Merge join 6 MergeJoin Y 1 none LEFT OUTER Merge join 5 month6 product_id product_id

1232 112 Y Select values SelectValues Y 1 none product_id item_code product sum value last_out last_in brand status qty_month1 qty_month2 qty_month3 qty_month4 qty_month5 qty_month6 hit_month1 hit_month2 hit_month3 hit_month4 hit_month5 hit_month6 month_count1 month_count2 month_count3 month_count4 month_count5 month_count6 N product_id product_id None 9 0 false false item_code item_code None 2147483647 -2 false false product product None 2147483647 -2 false false sum sum None -2 -2 false false value value None -2 -2 false false last_out last_out None -2 -2 false false last_in last_in None -2 -2 false false brand brand None 2147483647 -2 false false status status None 2147483647 -2 false false qty_month1 qty_month1 Number -2 -2 false false qty_month2 qty_month2 Number -2 -2 false false qty_month3 qty_month3 Number -2 -2 false false qty_month4 qty_month4 Number -2 -2 false false qty_month5 qty_month5 Number -2 -2 false false qty_month6 qty_month6 Number -2 -2 false false hit_month1 hit_month1 Number -2 -2 false false hit_month2 hit_month2 Number -2 -2 false false hit_month3 hit_month3 Number -2 -2 false false hit_month4 hit_month4 Number -2 -2 false false hit_month5 hit_month5 Number -2 -2 false false hit_month6 hit_month6 Number -2 -2 false false month_count1 month_count1 Number -2 -2 false false month_count2 month_count2 Number -2 -2 false false month_count3 month_count3 Number -2 -2 false false month_count4 month_count4 Number -2 -2 false false month_count5 month_count5 Number -2 -2 false false month_count6 month_count6 Number -2 -2 false false

1232 208 Y If field value is null IfNull Y 1 none 0 Y N N qty_month1 0 N qty_month2 0 N qty_month3 0 N qty_month4 0 N qty_month5 0 N qty_month6 0 N hit_month1 0 N hit_month2 0 N hit_month3 0 N hit_month4 0 N hit_month5 0 N hit_month6 0 N month_count1 0 N month_count2 0 N month_count3 0 N month_count4 0 N month_count5 0 N month_count6 0 N

1120 208 Y Append streams Append Y 1 none If field value is null Add constants 2

768 208 Y Select values 2 SelectValues Y 1 none product_id item_code product sum value last_out last_in brand status N

416 208 Y Add constants 2 Constant Y 1 none qty_month1 Number 0 -1 -1 N qty_month2 Number 0 -1 -1 N qty_month3 Number 0 -1 -1 N qty_month4 Number 0 -1 -1 N qty_month5 Number 0 -1 -1 N qty_month6 Number 0 -1 -1 N hit_month1 Number 0 -1 -1 N hit_month2 Number 0 -1 -1 N hit_month3 Number 0 -1 -1 N hit_month4 Number 0 -1 -1 N hit_month5 Number 0 -1 -1 N hit_month6 Number 0 -1 -1 N month_count1 Number 0 -1 -1 N month_count2 Number 0 -1 -1 N month_count3 Number 0 -1 -1 N month_count4 Number 0 -1 -1 N month_count5 Number 0 -1 -1 N month_count6 Number 0 -1 -1 N

560 208 Y Add constants 3 Constant Y 1 none diff_month Number 6 -1 -1 N

768 304 Y Calculator Calculator Y 1 none Y sum_hit1 ADD3 hit_month1 hit_month2 hit_month3 None -1 -1 N sum_hit2 ADD3 hit_month4 hit_month5 hit_month6 None -1 -1 N total_hit ADD sum_hit1 sum_hit2 None -1 -1 N avg_per_month DIVIDE total_hit diff_month None -1 -1 N sum_mc1 ADD3 month_count1 month_count2 month_count3 None -1 -1 N sum_mc2 ADD3 month_count4 month_count5 month_count6 None -1 -1 N total_mc ADD sum_mc1 sum_mc2 None -1 -1 N

880 304 Y Select values 3 SelectValues Y 1 none product_id item_code product sum onhand value last_out last_in brand status qty_month1 qty_month2 qty_month3 qty_month4 qty_month5 qty_month6 hit_month1 hit_month2 hit_month3 hit_month4 hit_month5 hit_month6 total_hit avg_per_month total_mc N

992 304 Y Insert / update InsertUpdate Y 1 none dw bi indoteknik (localhost) 100 N public inventory_compute product_id product_id = product_id product_id N item_code item_code Y product product Y onhand onhand Y value value Y last_out last_out Y last_in last_in Y brand brand Y status status Y qty_month1 qty_month1 Y qty_month2 qty_month2 Y qty_month3 qty_month3 Y qty_month4 qty_month4 Y qty_month5 qty_month5 Y qty_month6 qty_month6 Y hit_month1 hit_month1 Y hit_month2 hit_month2 Y hit_month3 hit_month3 Y hit_month4 hit_month4 Y hit_month5 hit_month5 Y hit_month6 hit_month6 Y total_hit total_hit Y avg_per_month avg_per_month Y total_mc total_mc Y

1120 304 Y N