diff options
| author | stephanchrst <stephanchrst@gmail.com> | 2024-11-13 10:06:15 +0700 |
|---|---|---|
| committer | stephanchrst <stephanchrst@gmail.com> | 2024-11-13 10:06:15 +0700 |
| commit | 51d7a6c5aeb76ff33f8d7ffdae2bbc9e570c1367 (patch) | |
| tree | 8f398dee374ac754b76fbb026d0439571066f0d0 | |
| parent | 9af72c43ddad7df9f628ffdd75a7647ab6e50941 (diff) | |
add fulfillment
| -rw-r--r-- | calculate_pareto_status.ktr | 1 | ||||
| -rw-r--r-- | collect_fulfillment.ktr | 1506 | ||||
| -rw-r--r-- | collect_fulfillment_po.ktr | 888 | ||||
| -rw-r--r-- | example_use_param_table_input.ktr | 903 | ||||
| -rw-r--r-- | run_fulfillment_job.kjb | 413 | ||||
| -rw-r--r-- | update_null_md_vendor.ktr | 937 | ||||
| -rw-r--r-- | update_state_reserve_picking.ktr | 552 |
7 files changed, 5200 insertions, 0 deletions
diff --git a/calculate_pareto_status.ktr b/calculate_pareto_status.ktr index 1cb14ff..b636486 100644 --- a/calculate_pareto_status.ktr +++ b/calculate_pareto_status.ktr @@ -6,6 +6,7 @@ <extended_description/> <trans_version/> <trans_type>Normal</trans_type> + <trans_status>0</trans_status> <directory>/</directory> <parameters> </parameters> diff --git a/collect_fulfillment.ktr b/collect_fulfillment.ktr new file mode 100644 index 0000000..6197a8d --- /dev/null +++ b/collect_fulfillment.ktr @@ -0,0 +1,1506 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>collect_fulfillment</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>2024/11/09 08:11:14.886</created_date> + <modified_user>-</modified_user> + <modified_date>2024/11/09 08:11:14.886</modified_date> + <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <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>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> + <hop> + <from>sale order</from> + <to>so+deliv</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>delivered qty</from> + <to>so+deliv</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>so+deliv</from> + <to>so+deliv+reserv</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>reserved qty</from> + <to>so+deliv+reserv</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>so+deliv+reserv</from> + <to>so+deliv+reserv+po</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>po qty</from> + <to>so+deliv+reserv+po</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>so+deliv+reserv+po</from> + <to>so+deliv+reserv+po+received</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>received qty</from> + <to>so+deliv+reserv+po+received</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values</from> + <to>Insert / update</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>so+deliv+reserv+po+received</from> + <to>Add constants</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Add constants</from> + <to>Select values</to> + <enabled>Y</enabled> + </hop> + </order> + <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>erp indoteknik production (localhost)</connection> + <commit>100</commit> + <update_bypassed>N</update_bypassed> + <lookup> + <schema>public</schema> + <table>sales_order_fulfillment_v2</table> + <key> + <name>sale_order_line_id</name> + <field>sale_order_line_id</field> + <condition>=</condition> + <name2/> + </key> + <value> + <name>sale_order_id</name> + <rename>sale_order_id</rename> + <update>Y</update> + </value> + <value> + <name>sale_order_line_id</name> + <rename>sale_order_line_id</rename> + <update>N</update> + </value> + <value> + <name>product_id</name> + <rename>product_id</rename> + <update>Y</update> + </value> + <value> + <name>so_qty</name> + <rename>so_qty</rename> + <update>Y</update> + </value> + <value> + <name>delivered_qty</name> + <rename>delivered_qty</rename> + <update>Y</update> + </value> + <value> + <name>reserved_stock_qty</name> + <rename>reserved_stock_qty</rename> + <update>Y</update> + </value> + <value> + <name>po_qty</name> + <rename>po_qty</rename> + <update>Y</update> + </value> + <value> + <name>received_qty</name> + <rename>received_qty</rename> + <update>Y</update> + </value> + <value> + <name>create_date</name> + <rename>current_date</rename> + <update>N</update> + </value> + <value> + <name>write_date</name> + <rename>current_date</rename> + <update>Y</update> + </value> + <value> + <name>create_uid</name> + <rename>created_by_id</rename> + <update>Y</update> + </value> + <value> + <name>write_uid</name> + <rename>created_by_id</rename> + <update>Y</update> + </value> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>848</xloc> + <yloc>368</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>sale_order_id</name> + <rename/> + </field> + <field> + <name>sale_order_line_id</name> + <rename/> + </field> + <field> + <name>product_id</name> + <rename/> + </field> + <field> + <name>so_qty</name> + <rename/> + </field> + <field> + <name>current_date</name> + <rename/> + </field> + <field> + <name>delivered_qty</name> + <rename/> + </field> + <field> + <name>reserved_stock_qty</name> + <rename/> + </field> + <field> + <name>po_qty</name> + <rename/> + </field> + <field> + <name>received_qty</name> + <rename/> + </field> + <field> + <name>created_by_id</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>1008</xloc> + <yloc>368</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>delivered qty</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.sale_line_id as sale_order_line_id, sml.product_id, sum(sml.qty_done) as delivered_qty +from stock_move_line sml +join stock_move sm on sm.id = sml.move_id +join stock_picking sp on sp.id = sm.picking_id +where 1=1 +and sp.state not in ('cancel') +and sm.sale_line_id is not null +and sm.write_date >= (now() - '180 days'::interval) +group by sm.sale_line_id, sml.product_id +order by sm.sale_line_id, sml.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>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>delivered qty</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>delivered qty</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>BigNumber</type> + <storagetype>normal</storagetype> + <name>delivered_qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>delivered qty</origin> + <comments>delivered_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>288</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>po qty</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 posm.sale_line_id as sale_order_line_id, posm.product_id, sum(posm.qty_po) as po_qty +from purchase_order_sales_match posm +join purchase_order po on po.id = posm.purchase_order_id +where 1=1 +and po.state in ('done', 'purchase') +and posm.write_date >= (now() - '180 days'::interval) +group by posm.sale_line_id, posm.product_id +order by posm.sale_line_id, posm.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>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>po qty</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>po qty</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>Number</type> + <storagetype>normal</storagetype> + <name>po_qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>po qty</origin> + <comments>po_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> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>608</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>received qty</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 posm.sale_line_id as sale_order_line_id, sml.product_id, sum(sml.qty_done) as received_qty +from purchase_order_sales_match posm +join purchase_order po on po.id = posm.purchase_order_id +join purchase_order_line pol on (pol.order_id = po.id and pol.product_id = posm.product_id) +join stock_move sm on sm.purchase_line_id = pol.id +join stock_move_line sml on sml.move_id = sm.id +join stock_picking sp on sp.id = sm.picking_id +where 1=1 +and po.state in ('done', 'purchase') +and sp.state not in ('cancel') +and posm.write_date >= (now() - '180 days'::interval) +group by posm.sale_line_id, sml.product_id +order by posm.sale_line_id, sml.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>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>received qty</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>received qty</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>BigNumber</type> + <storagetype>normal</storagetype> + <name>received_qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>received qty</origin> + <comments>received_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>800</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>reserved qty</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.sale_line_id as sale_order_line_id, sml.product_id, sum(sml.product_uom_qty) as reserved_stock_qty +from stock_move_line sml +join stock_move sm on sm.id = sml.move_id +join stock_picking sp on sp.id = sm.picking_id +where 1=1 +and sp.state not in ('cancel') +and sm.sale_line_id is not null +and sm.write_date >= (now() - '180 days'::interval) +group by sm.sale_line_id, sml.product_id +order by sm.sale_line_id, sml.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>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>reserved qty</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>reserved qty</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>BigNumber</type> + <storagetype>normal</storagetype> + <name>reserved_stock_qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>reserved qty</origin> + <comments>reserved_stock_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>448</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>sale order</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 so.id as sale_order_id, sol.id as sale_order_line_id, +sol.product_id as product_id, sol.product_uom_qty as so_qty, +now()::timestamp as current_date +from sale_order_line sol +join sale_order so on so.id = sol.order_id +join product_product pp on pp.id = sol.product_id +where 1=1 +and so.state in ('sale', 'done') +and sol.product_uom_qty <> sol.qty_delivered +and sol.write_date >= (now() - '180 days'::interval) +order by sol.id, sol.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>sale_order_id</name> + <length>9</length> + <precision>0</precision> + <origin>sale order</origin> + <comments>sale_order_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>sale order</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>sale order</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>BigNumber</type> + <storagetype>normal</storagetype> + <name>so_qty</name> + <length>-1</length> + <precision>-1</precision> + <origin>sale order</origin> + <comments>so_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> + <value-meta> + <type>Timestamp</type> + <storagetype>normal</storagetype> + <name>current_date</name> + <length>6</length> + <precision>-1</precision> + <origin>sale order</origin> + <comments>current_date</comments> + <conversion_Mask/> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + </row-meta> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>144</xloc> + <yloc>224</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>so+deliv</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>sale order</step1> + <step2>delivered qty</step2> + <keys_1> + <key>sale_order_line_id</key> + </keys_1> + <keys_2> + <key>sale_order_line_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>288</xloc> + <yloc>224</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>so+deliv+reserv</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>so+deliv</step1> + <step2>reserved qty</step2> + <keys_1> + <key>sale_order_line_id</key> + </keys_1> + <keys_2> + <key>sale_order_line_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>448</xloc> + <yloc>224</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>so+deliv+reserv+po</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>so+deliv+reserv</step1> + <step2>po qty</step2> + <keys_1> + <key>sale_order_line_id</key> + </keys_1> + <keys_2> + <key>sale_order_line_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>608</xloc> + <yloc>224</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>so+deliv+reserv+po+received</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>so+deliv+reserv+po</step1> + <step2>received qty</step2> + <keys_1> + <key>sale_order_line_id</key> + </keys_1> + <keys_2> + <key>sale_order_line_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>800</xloc> + <yloc>224</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Add constants</name> + <type>Constant</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>created_by_id</name> + <type>Integer</type> + <format/> + <currency/> + <decimal/> + <group/> + <nullif>1</nullif> + <length>-1</length> + <precision>-1</precision> + <set_empty_string>N</set_empty_string> + </field> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>1008</xloc> + <yloc>224</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/collect_fulfillment_po.ktr b/collect_fulfillment_po.ktr new file mode 100644 index 0000000..6683ec9 --- /dev/null +++ b/collect_fulfillment_po.ktr @@ -0,0 +1,888 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>collect_fulfillment_po</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>2024/11/13 08:53:00.392</created_date> + <modified_user>-</modified_user> + <modified_date>2024/11/13 08:53:00.392</modified_date> + <key_for_session_key/> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <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>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> + <hop> + <from>match po so</from> + <to>match po so with fulfillment</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>fulfillment</from> + <to>match po so with fulfillment</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>match po so with fulfillment</from> + <to>Filter fulfillment is not null</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter fulfillment is not null</from> + <to>Select values</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter fulfillment is not null</from> + <to>log fulfillment null</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values</from> + <to>Insert / update</to> + <enabled>Y</enabled> + </hop> + </order> + <step> + <name>fulfillment</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 id as fulfillment_id, sale_order_line_id +from sales_order_fulfillment_v2 +where 1=1 +and write_date >= (now() - '180 days'::interval) +order by sale_order_line_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>fulfillment_id</name> + <length>9</length> + <precision>0</precision> + <origin>fulfillment</origin> + <comments>fulfillment_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>fulfillment</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + </row-meta> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>368</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>match po so</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 posm.sale_line_id as sale_order_line_id, po.id as purchase_order_id +from purchase_order_sales_match posm +join purchase_order po on po.id = posm.purchase_order_id +where 1=1 +and po.state in ('purchase', 'done') +and posm.write_date >= (now() - '180 days'::interval) +order by posm.sale_line_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>sale_order_line_id</name> + <length>9</length> + <precision>0</precision> + <origin>match po so</origin> + <comments>sale_order_line_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>purchase_order_id</name> + <length>9</length> + <precision>0</precision> + <origin>match po so</origin> + <comments>purchase_order_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + </row-meta> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>160</xloc> + <yloc>192</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>match po so with fulfillment</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>match po so</step1> + <step2>fulfillment</step2> + <keys_1> + <key>sale_order_line_id</key> + </keys_1> + <keys_2> + <key>sale_order_line_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>368</xloc> + <yloc>192</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Filter fulfillment is not null</name> + <type>FilterRows</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <send_true_to>Select values</send_true_to> + <send_false_to>log fulfillment null</send_false_to> + <compare> + <condition> + <negated>N</negated> + <leftvalue>fulfillment_id</leftvalue> + <function>IS NOT NULL</function> + <rightvalue/> + </condition> + </compare> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>592</xloc> + <yloc>192</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>sale_order_line_id</name> + <rename/> + </field> + <field> + <name>purchase_order_id</name> + <rename/> + </field> + <field> + <name>fulfillment_id</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>752</xloc> + <yloc>192</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>log fulfillment null</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>592</xloc> + <yloc>304</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>erp indoteknik production (localhost)</connection> + <commit>100</commit> + <update_bypassed>N</update_bypassed> + <lookup> + <schema>public</schema> + <table>purchase_order_sales_order_fulfillment_v2_rel</table> + <key> + <name>fulfillment_id</name> + <field>sales_order_fulfillment_v2_id</field> + <condition>=</condition> + <name2/> + </key> + <value> + <name>purchase_order_id</name> + <rename>purchase_order_id</rename> + <update>Y</update> + </value> + <value> + <name>sales_order_fulfillment_v2_id</name> + <rename>fulfillment_id</rename> + <update>N</update> + </value> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>912</xloc> + <yloc>192</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/example_use_param_table_input.ktr b/example_use_param_table_input.ktr new file mode 100644 index 0000000..c5bb577 --- /dev/null +++ b/example_use_param_table_input.ktr @@ -0,0 +1,903 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>example_use_param_table_input</name> + <description/> + <extended_description/> + <trans_version/> + <trans_type>Normal</trans_type> + <trans_status>0</trans_status> + <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>2024/11/09 07:41:25.978</created_date> + <modified_user>-</modified_user> + <modified_date>2024/11/09 07:41:25.978</modified_date> + <key_for_session_key/> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <connection> + <name>erp indoteknik production</name> + <server>localhost</server> + <type>POSTGRESQL</type> + <access>Native</access> + <database>erp_indoteknik</database> + <port>5901</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>5901</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>Y</attribute> + </attribute> + <attribute> + <code>USE_POOLING</code> + <attribute>N</attribute> + </attribute> + </attributes> + </connection> + <order> + <hop> + <from>Table input 2</from> + <to>Select values</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Table input</from> + <to>Select values 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Table input</from> + <to>Select values 3</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values 3</from> + <to>Table input 2</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values 2</from> + <to>Merge join</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values</from> + <to>Merge join</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Merge join</from> + <to>Select values 4</to> + <enabled>Y</enabled> + </hop> + </order> + <step> + <name>Table input</name> + <type>TableInput</type> + <description/> + <distribute>N</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production</connection> + <sql>select id, name from sale_order where date_order between '2023-01-01' and '2023-12-31' order by 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>id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input</origin> + <comments>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>name</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>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> + </row-meta> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>128</xloc> + <yloc>80</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>sale_order_id</name> + <rename/> + </field> + <field> + <name>po</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>544</xloc> + <yloc>80</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Table input 2</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production</connection> + <sql>select sale_order_id, name as po +from purchase_order +where sale_order_id = ? +order by sale_order_id</sql> + <limit>0</limit> + <lookup>Select values 3</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>id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input</origin> + <comments>id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>sale_order_id</name> + <length>9</length> + <precision>0</precision> + <origin>Table input 2</origin> + <comments>sale_order_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>String</type> + <storagetype>normal</storagetype> + <name>po</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input 2</origin> + <comments>po</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>352</xloc> + <yloc>80</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>id</name> + <rename/> + </field> + <field> + <name>name</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>128</xloc> + <yloc>192</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values 3</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>id</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>240</xloc> + <yloc>80</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>INNER</join_type> + <step1>Select values 2</step1> + <step2>Select values</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>sale_order_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>352</xloc> + <yloc>192</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values 4</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>id</name> + <rename/> + </field> + <field> + <name>name</name> + <rename/> + </field> + <field> + <name>sale_order_id</name> + <rename/> + </field> + <field> + <name>po</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>576</xloc> + <yloc>192</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/run_fulfillment_job.kjb b/run_fulfillment_job.kjb new file mode 100644 index 0000000..93b36a3 --- /dev/null +++ b/run_fulfillment_job.kjb @@ -0,0 +1,413 @@ +<?xml version="1.0" encoding="UTF-8"?> +<job> + <name>run_fulfillment_job</name> + <description/> + <extended_description/> + <job_version/> + <directory>/</directory> + <created_user>-</created_user> + <created_date>2024/11/13 09:55:25.528</created_date> + <modified_user>-</modified_user> + <modified_date>2024/11/13 09:55:25.528</modified_date> + <parameters> + </parameters> + <slaveservers> + </slaveservers> + <job-log-table> + <connection/> + <schema/> + <table/> + <size_limit_lines/> + <interval/> + <timeout_days/> + <field> + <id>ID_JOB</id> + <enabled>Y</enabled> + <name>ID_JOB</name> + </field> + <field> + <id>CHANNEL_ID</id> + <enabled>Y</enabled> + <name>CHANNEL_ID</name> + </field> + <field> + <id>JOBNAME</id> + <enabled>Y</enabled> + <name>JOBNAME</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> + </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>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>START_JOB_ENTRY</id> + <enabled>N</enabled> + <name>START_JOB_ENTRY</name> + </field> + <field> + <id>CLIENT</id> + <enabled>N</enabled> + <name>CLIENT</name> + </field> + </job-log-table> + <jobentry-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>JOBNAME</id> + <enabled>Y</enabled> + <name>TRANSNAME</name> + </field> + <field> + <id>JOBENTRYNAME</id> + <enabled>Y</enabled> + <name>STEPNAME</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>RESULT</id> + <enabled>Y</enabled> + <name>RESULT</name> + </field> + <field> + <id>NR_RESULT_ROWS</id> + <enabled>Y</enabled> + <name>NR_RESULT_ROWS</name> + </field> + <field> + <id>NR_RESULT_FILES</id> + <enabled>Y</enabled> + <name>NR_RESULT_FILES</name> + </field> + <field> + <id>LOG_FIELD</id> + <enabled>N</enabled> + <name>LOG_FIELD</name> + </field> + <field> + <id>COPY_NR</id> + <enabled>N</enabled> + <name>COPY_NR</name> + </field> + </jobentry-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> + <pass_batchid>N</pass_batchid> + <shared_objects_file/> + <entries> + <entry> + <name>collect fulfillment</name> + <description/> + <type>TRANS</type> + <attributes/> + <specification_method>filename</specification_method> + <trans_object_id/> + <filename>/home/stephan/transformation-job-bi/collect_fulfillment.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>384</xloc> + <yloc>80</yloc> + <attributes_kjc/> + </entry> + <entry> + <name>Start</name> + <description/> + <type>SPECIAL</type> + <attributes/> + <start>Y</start> + <dummy>N</dummy> + <repeat>N</repeat> + <schedulerType>0</schedulerType> + <intervalSeconds>0</intervalSeconds> + <intervalMinutes>60</intervalMinutes> + <hour>12</hour> + <minutes>0</minutes> + <weekDay>1</weekDay> + <DayOfMonth>1</DayOfMonth> + <parallel>N</parallel> + <draw>Y</draw> + <nr>0</nr> + <xloc>176</xloc> + <yloc>80</yloc> + <attributes_kjc/> + </entry> + <entry> + <name>collect fulfillment po</name> + <description/> + <type>TRANS</type> + <attributes/> + <specification_method>filename</specification_method> + <trans_object_id/> + <filename>/home/stephan/transformation-job-bi/collect_fulfillment_po.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>576</xloc> + <yloc>80</yloc> + <attributes_kjc/> + </entry> + </entries> + <hops> + <hop> + <from>Start</from> + <to>collect fulfillment</to> + <from_nr>0</from_nr> + <to_nr>0</to_nr> + <enabled>Y</enabled> + <evaluation>Y</evaluation> + <unconditional>Y</unconditional> + </hop> + <hop> + <from>collect fulfillment</from> + <to>collect fulfillment po</to> + <from_nr>0</from_nr> + <to_nr>0</to_nr> + <enabled>Y</enabled> + <evaluation>Y</evaluation> + <unconditional>N</unconditional> + </hop> + </hops> + <notepads> + </notepads> + <attributes/> +</job> diff --git a/update_null_md_vendor.ktr b/update_null_md_vendor.ktr new file mode 100644 index 0000000..ba50cef --- /dev/null +++ b/update_null_md_vendor.ktr @@ -0,0 +1,937 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>update_null_md_vendor</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>2024/10/17 14:45:16.821</created_date> + <modified_user>-</modified_user> + <modified_date>2024/10/17 14:45:16.821</modified_date> + <key_for_session_key/> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <connection> + <name>erp indoteknik production</name> + <server>localhost</server> + <type>POSTGRESQL</type> + <access>Native</access> + <database>erp_indoteknik</database> + <port>5901</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>5901</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>Y</attribute> + </attribute> + <attribute> + <code>USE_POOLING</code> + <attribute>N</attribute> + </attribute> + </attributes> + </connection> + <order> + <hop> + <from>sale order line</from> + <to>Merge join</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>pricelist_winner</from> + <to>Merge join</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Merge join</from> + <to>Filter rows</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter rows</from> + <to>Select values</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Filter rows</from> + <to>Write to log</to> + <enabled>Y</enabled> + </hop> + <hop> + <from>Select values</from> + <to>Update</to> + <enabled>Y</enabled> + </hop> + </order> + <step> + <name>pricelist_winner</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production</connection> + <sql>SELECT id as pricelist_id, product_id, vendor_id FROM purchase_pricelist +where is_winner = true +order by 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>pricelist_id</name> + <length>9</length> + <precision>0</precision> + <origin>pricelist_winner</origin> + <comments>pricelist_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>pricelist_winner</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>Integer</type> + <storagetype>normal</storagetype> + <name>vendor_id</name> + <length>9</length> + <precision>0</precision> + <origin>pricelist_winner</origin> + <comments>vendor_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> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>240</xloc> + <yloc>144</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Merge join</name> + <type>MergeJoin</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <join_type>LEFT OUTER</join_type> + <step1>sale order line</step1> + <step2>pricelist_winner</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>384</xloc> + <yloc>256</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>sale order line</name> + <type>TableInput</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production</connection> + <sql>select id as orderline_id, product_id, name from sale_order_line +where vendor_md_id is null and order_id = 30881 +order by 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>orderline_id</name> + <length>9</length> + <precision>0</precision> + <origin>sale order line</origin> + <comments>orderline_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_id</name> + <length>9</length> + <precision>0</precision> + <origin>sale order line</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>name</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>sale order line</origin> + <comments>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> + </row-meta> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>240</xloc> + <yloc>336</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>Select values</send_true_to> + <send_false_to>Write to log</send_false_to> + <compare> + <condition> + <negated>N</negated> + <leftvalue>vendor_id</leftvalue> + <function>IS NOT NULL</function> + <rightvalue/> + </condition> + </compare> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>528</xloc> + <yloc>256</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>orderline_id</name> + <rename/> + </field> + <field> + <name>product_id</name> + <rename/> + </field> + <field> + <name>name</name> + <rename/> + </field> + <field> + <name>pricelist_id</name> + <rename/> + </field> + <field> + <name>product_id_1</name> + <rename/> + </field> + <field> + <name>vendor_id</name> + <rename/> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>672</xloc> + <yloc>256</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>528</xloc> + <yloc>336</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Update</name> + <type>Update</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>erp indoteknik production</connection> + <skip_lookup>N</skip_lookup> + <commit>100</commit> + <use_batch>N</use_batch> + <error_ignored>N</error_ignored> + <ignore_flag_field/> + <lookup> + <schema>public</schema> + <table>sale_order_line</table> + <key> + <name>orderline_id</name> + <field>id</field> + <condition>=</condition> + <name2/> + </key> + <value> + <name>md_vendor_id</name> + <rename>vendor_id</rename> + </value> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>816</xloc> + <yloc>256</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_state_reserve_picking.ktr b/update_state_reserve_picking.ktr new file mode 100644 index 0000000..f7b17a9 --- /dev/null +++ b/update_state_reserve_picking.ktr @@ -0,0 +1,552 @@ +<?xml version="1.0" encoding="UTF-8"?> +<transformation> + <info> + <name>update_state_reserve_picking</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>2024/11/08 15:58:55.966</created_date> + <modified_user>-</modified_user> + <modified_date>2024/11/08 15:58:55.966</modified_date> + <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key> + <is_key_private>N</is_key_private> + </info> + <notepads> + </notepads> + <order> + </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/> + <sql>SELECT <values> FROM <table name> WHERE <conditions></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/> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>112</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Execute row SQL script</name> + <type>ExecSQLRow</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <commit>1</commit> + <connection/> + <sql_field/> + <insert_field/> + <update_field/> + <delete_field/> + <read_field/> + <sqlFromfile>N</sqlFromfile> + <sendOneStatement>Y</sendOneStatement> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>272</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <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/> + <execute_each_row>N</execute_each_row> + <single_statement>N</single_statement> + <replace_variables>N</replace_variables> + <quoteString>N</quoteString> + <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>304</xloc> + <yloc>256</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> |