diff options
| author | stephanchrst <stephanchrst@gmail.com> | 2023-08-25 15:54:45 +0700 |
|---|---|---|
| committer | stephanchrst <stephanchrst@gmail.com> | 2023-08-25 15:54:45 +0700 |
| commit | 57b5844479d269a0a940f9bd4a1d28bef0cbb060 (patch) | |
| tree | ca6969e7cf88cecd5ddf987101fdfa4babebaba1 | |
| parent | 644d994308d095e36c41dbf9f9834abbdecf49fe (diff) | |
temporary remove new concept of purchase pricelist and sales pricelist
| -rw-r--r-- | import_all_data_bi_indoteknik.kjb | 336 | ||||
| -rw-r--r-- | import_purchase_order.ktr | 330 |
2 files changed, 235 insertions, 431 deletions
diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb index 6b3f602..e73e8c9 100644 --- a/import_all_data_bi_indoteknik.kjb +++ b/import_all_data_bi_indoteknik.kjb @@ -956,279 +956,6 @@ <yloc>560</yloc> <attributes_kjc/> </entry> - <entry> - <name>import purchase pricelist</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/import_purchase_pricelist.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>704</xloc> - <yloc>560</yloc> - <attributes_kjc/> - </entry> - <entry> - <name>update count trx po</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/update_count_trx_po_product.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>480</xloc> - <yloc>560</yloc> - <attributes_kjc/> - </entry> - <entry> - <name>update count po vendor</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/update_count_trx_po_vendor.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>256</xloc> - <yloc>560</yloc> - <attributes_kjc/> - </entry> - <entry> - <name>refresh purchase price</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/refresh_materialized_view_purchase_price.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>256</xloc> - <yloc>704</yloc> - <attributes_kjc/> - </entry> - <entry> - <name>import price group</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/import_price_group.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>480</xloc> - <yloc>704</yloc> - <attributes_kjc/> - </entry> - <entry> - <name>calculate price</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/calculate_price_after_disc.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>624</xloc> - <yloc>704</yloc> - <attributes_kjc/> - </entry> - <entry> - <name>refresh mv pricelist</name> - <description/> - <type>TRANS</type> - <attributes/> - <specification_method>filename</specification_method> - <trans_object_id/> - <filename>/home/superman/runapps/refresh_materialized_view_pricelist.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>816</xloc> - <yloc>704</yloc> - <attributes_kjc/> - </entry> </entries> <hops> <hop> @@ -1384,69 +1111,6 @@ <evaluation>Y</evaluation> <unconditional>N</unconditional> </hop> - <hop> - <from>import purchase order</from> - <to>import purchase pricelist</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> - <hop> - <from>import purchase pricelist</from> - <to>update count trx po</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> - <hop> - <from>update count trx po</from> - <to>update count po vendor</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> - <hop> - <from>update count po vendor</from> - <to>refresh purchase price</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> - <hop> - <from>refresh purchase price</from> - <to>import price group</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> - <hop> - <from>import price group</from> - <to>calculate price</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> - <hop> - <from>calculate price</from> - <to>refresh mv pricelist</to> - <from_nr>0</from_nr> - <to_nr>0</to_nr> - <enabled>Y</enabled> - <evaluation>Y</evaluation> - <unconditional>N</unconditional> - </hop> </hops> <notepads> </notepads> diff --git a/import_purchase_order.ktr b/import_purchase_order.ktr index afb9963..ee85cad 100644 --- a/import_purchase_order.ktr +++ b/import_purchase_order.ktr @@ -430,7 +430,7 @@ <created_date>2023/08/18 10:27:05.868</created_date> <modified_user>-</modified_user> <modified_date>2023/08/18 10:27:05.868</modified_date> - <key_for_session_key/> + <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key> <is_key_private>N</is_key_private> </info> <notepads> @@ -545,6 +545,119 @@ </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>dw bi indoteknik</connection> + <commit>100</commit> + <update_bypassed>N</update_bypassed> + <lookup> + <schema>public</schema> + <table>purchase_order_dw</table> + <key> + <name>line_id</name> + <field>line_id</field> + <condition>=</condition> + <name2/> + </key> + <value> + <name>po_date</name> + <rename>po_date</rename> + <update>Y</update> + </value> + <value> + <name>line_date</name> + <rename>line_date</rename> + <update>Y</update> + </value> + <value> + <name>documentno</name> + <rename>documentno</rename> + <update>Y</update> + </value> + <value> + <name>state</name> + <rename>state</rename> + <update>Y</update> + </value> + <value> + <name>partner_id</name> + <rename>partner_id</rename> + <update>Y</update> + </value> + <value> + <name>vendor</name> + <rename>vendor</rename> + <update>Y</update> + </value> + <value> + <name>product_id</name> + <rename>product_id</rename> + <update>Y</update> + </value> + <value> + <name>qty</name> + <rename>qty</rename> + <update>Y</update> + </value> + <value> + <name>price_unit</name> + <rename>price_unit</rename> + <update>Y</update> + </value> + <value> + <name>price_subtotal</name> + <rename>price_subtotal</rename> + <update>Y</update> + </value> + <value> + <name>line_id</name> + <rename>line_id</rename> + <update>N</update> + </value> + <value> + <name>year_month</name> + <rename>year_month</rename> + <update>Y</update> + </value> + <value> + <name>year_date</name> + <rename>year_date</rename> + <update>Y</update> + </value> + <value> + <name>month_date</name> + <rename>month_date</rename> + <update>Y</update> + </value> + <value> + <name>day_date</name> + <rename>day_date</rename> + <update>Y</update> + </value> + </lookup> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>432</xloc> + <yloc>96</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> <name>Table input</name> <type>TableInput</type> <description/> @@ -558,7 +671,12 @@ <connection>erp indoteknik production</connection> <sql>select po.write_date as po_date, pol.write_date as line_date, po.name as documentno, po.state, po.partner_id, rp.name as vendor, pol.product_id, pol.product_uom_qty as qty, -pol.price_unit, pol.price_subtotal, pol.id as line_id +pol.price_unit, pol.price_subtotal, pol.id as line_id, +to_char(pol.write_date::timestamp with time zone, 'YYYY-MM'::text) AS year_month, +to_char(pol.write_date::timestamp with time zone, 'YYYY'::text) as year_date, +to_char(pol.write_date::timestamp with time zone, 'MM'::text) as month_date, +to_char(pol.write_date::timestamp with time zone, 'DD'::text) as day_date, +get_quarter(to_char(pol.write_date::timestamp with time zone, 'MM'::text)) from purchase_order po join res_partner rp on rp.id = po.partner_id join purchase_order_line pol on pol.order_id = po.id @@ -823,6 +941,121 @@ where pol.write_date >= (now() - '5 days'::interval)</sql> <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>year_month</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>year_month</comments> + <conversion_Mask/> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>String</type> + <storagetype>normal</storagetype> + <name>year_date</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>year_date</comments> + <conversion_Mask/> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>String</type> + <storagetype>normal</storagetype> + <name>month_date</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>month_date</comments> + <conversion_Mask/> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>String</type> + <storagetype>normal</storagetype> + <name>day_date</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>day_date</comments> + <conversion_Mask/> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>String</type> + <storagetype>normal</storagetype> + <name>get_quarter</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>Table input</origin> + <comments>get_quarter</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/> @@ -838,99 +1071,6 @@ where pol.write_date >= (now() - '5 days'::interval)</sql> <draw>Y</draw> </GUI> </step> - <step> - <name>Insert / update</name> - <type>InsertUpdate</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <connection>dw bi indoteknik</connection> - <commit>100</commit> - <update_bypassed>N</update_bypassed> - <lookup> - <schema>public</schema> - <table>purchase_order_dw</table> - <key> - <name>line_id</name> - <field>line_id</field> - <condition>=</condition> - <name2/> - </key> - <value> - <name>po_date</name> - <rename>po_date</rename> - <update>Y</update> - </value> - <value> - <name>line_date</name> - <rename>line_date</rename> - <update>Y</update> - </value> - <value> - <name>documentno</name> - <rename>documentno</rename> - <update>Y</update> - </value> - <value> - <name>state</name> - <rename>state</rename> - <update>Y</update> - </value> - <value> - <name>partner_id</name> - <rename>partner_id</rename> - <update>Y</update> - </value> - <value> - <name>vendor</name> - <rename>vendor</rename> - <update>Y</update> - </value> - <value> - <name>product_id</name> - <rename>product_id</rename> - <update>Y</update> - </value> - <value> - <name>qty</name> - <rename>qty</rename> - <update>Y</update> - </value> - <value> - <name>price_unit</name> - <rename>price_unit</rename> - <update>Y</update> - </value> - <value> - <name>price_subtotal</name> - <rename>price_subtotal</rename> - <update>Y</update> - </value> - <value> - <name>line_id</name> - <rename>line_id</rename> - <update>N</update> - </value> - </lookup> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>432</xloc> - <yloc>96</yloc> - <draw>Y</draw> - </GUI> - </step> <step_error_handling> </step_error_handling> <slave-step-copy-partition-distribution> |
