From 79cce4e9f9605d8e119c6962925f19845ac244b9 Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 26 Sep 2023 09:12:47 +0700 Subject: add account move and account move line to job --- import_account_move_line.ktr | 1896 ++++++++++++++++++------------------- import_all_data_bi_indoteknik.kjb | 96 ++ 2 files changed, 1044 insertions(+), 948 deletions(-) diff --git a/import_account_move_line.ktr b/import_account_move_line.ktr index c7c6040..383d05f 100644 --- a/import_account_move_line.ktr +++ b/import_account_move_line.ktr @@ -430,7 +430,7 @@ 2023/09/25 17:01:19.768 - 2023/09/25 17:01:19.768 - + H4sIAAAAAAAAAAMAAAAAAAAAAAA= N @@ -545,8 +545,8 @@ - Table input - TableInput + Insert / update + InsertUpdate Y @@ -555,509 +555,359 @@ none - erp indoteknik production (localhost) - select * from account_move_line - 0 - - N - N - N - N - - - Integer - normal + dw bi indoteknik (localhost) + 100 + N + + public + account_move_line
+ id - 9 - 0 - Table input - id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + id + = + + + + id + id + N + + move_id - 9 - 0 - Table input - move_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + move_id + Y + + move_name - 2147483647 - -1 - Table input - move_name - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Date - normal + move_name + Y + + date - -1 - -1 - Table input - date - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + date + Y + + ref - 2147483647 - -1 - Table input - ref - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + ref + Y + + parent_state - 2147483647 - -1 - Table input - parent_state - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + parent_state + Y + + journal_id - 9 - 0 - Table input - journal_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + journal_id + Y + + company_id - 9 - 0 - Table input - company_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + company_id + Y + + company_currency_id - 9 - 0 - Table input - company_currency_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + company_currency_id + Y + + account_id - 9 - 0 - Table input - account_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + account_id + Y + + account_root_id - 9 - 0 - Table input - account_root_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + account_root_id + Y + + sequence - 9 - 0 - Table input - sequence - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + sequence + Y + + name - 2147483647 - -1 - Table input - name - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + name + Y + + quantity - -1 - -1 - Table input - quantity - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + quantity + Y + + price_unit - -1 - -1 - Table input - price_unit - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + price_unit + Y + + discount - -1 - -1 - Table input - discount - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + discount + Y + + debit - -1 - -1 - Table input - debit - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + debit + Y + + credit - -1 - -1 - Table input - credit - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + credit + Y + + balance - -1 - -1 - Table input - balance - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + balance + Y + + amount_currency - -1 - -1 - Table input - amount_currency - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal + amount_currency + Y + + price_subtotal - -1 - -1 - Table input - price_subtotal - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - + price_subtotal + Y + + + price_total + price_total + Y + + + reconciled + reconciled + Y + + + blocked + blocked + Y + + + date_maturity + date_maturity + Y + + + currency_id + currency_id + Y + + + partner_id + partner_id + Y + + + product_uom_id + product_uom_id + Y + + + product_id + product_id + Y + + + reconcile_model_id + reconcile_model_id + Y + + + payment_id + payment_id + Y + + + statement_line_id + statement_line_id + Y + + + statement_id + statement_id + Y + + + tax_line_id + tax_line_id + Y + + + tax_group_id + tax_group_id + Y + + + tax_base_amount + tax_base_amount + Y + + + tax_exigible + tax_exigible + Y + + + tax_repartition_line_id + tax_repartition_line_id + Y + + + tax_audit + tax_audit + Y + + + amount_residual + amount_residual + Y + + + amount_residual_currency + amount_residual_currency + Y + + + full_reconcile_id + full_reconcile_id + Y + + + matching_number + matching_number + Y + + + analytic_account_id + analytic_account_id + Y + + + display_type + display_type + Y + + + is_rounding_line + is_rounding_line + Y + + + exclude_from_invoice_tab + exclude_from_invoice_tab + Y + + + create_uid + create_uid + Y + + + create_date + create_date + Y + + + write_uid + write_uid + Y + + + write_date + write_date + Y + + + is_anglo_saxon_line + is_anglo_saxon_line + Y + + + purchase_line_id + purchase_line_id + Y + + + asset_category_id + asset_category_id + Y + + + asset_start_date + asset_start_date + Y + + + asset_end_date + asset_end_date + Y + + + asset_mrr + asset_mrr + Y + + + is_landed_costs_line + is_landed_costs_line + Y + + + cost_centre_id + cost_centre_id + Y + + + + + + + + + + + + 288 + 80 + Y + + + + Table input + TableInput + + Y + + 1 + + none + + + erp indoteknik production (localhost) + select * from account_move_line where write_date >= (now() - '5 days'::interval) + 0 + + N + N + N + N + - BigNumber + Integer normal - price_total - -1 - -1 + id + 9 + 0 Table input - price_total - ######0.0###################;-######0.0################### + id + ####0;-####0 . - + , none N @@ -1071,14 +921,14 @@ N - Boolean + Integer normal - reconciled - -1 - -1 + move_id + 9 + 0 Table input - reconciled - + move_id + ####0;-####0 . , @@ -1094,13 +944,13 @@ N - Boolean + String normal - blocked - -1 + move_name + 2147483647 -1 Table input - blocked + move_name . , @@ -1119,11 +969,11 @@ Date normal - date_maturity + date -1 -1 Table input - date_maturity + date . , @@ -1140,14 +990,14 @@ N - Integer + String normal - currency_id - 9 - 0 + ref + 2147483647 + -1 Table input - currency_id - ####0;-####0 + ref + . , @@ -1163,14 +1013,14 @@ N - Integer + String normal - partner_id - 9 - 0 + parent_state + 2147483647 + -1 Table input - partner_id - ####0;-####0 + parent_state + . , @@ -1188,11 +1038,11 @@ Integer normal - product_uom_id + journal_id 9 0 Table input - product_uom_id + journal_id ####0;-####0 . , @@ -1211,11 +1061,11 @@ Integer normal - product_id + company_id 9 0 Table input - product_id + company_id ####0;-####0 . , @@ -1234,11 +1084,11 @@ Integer normal - reconcile_model_id + company_currency_id 9 0 Table input - reconcile_model_id + company_currency_id ####0;-####0 . , @@ -1257,11 +1107,11 @@ Integer normal - payment_id + account_id 9 0 Table input - payment_id + account_id ####0;-####0 . , @@ -1280,11 +1130,11 @@ Integer normal - statement_line_id + account_root_id 9 0 Table input - statement_line_id + account_root_id ####0;-####0 . , @@ -1303,11 +1153,11 @@ Integer normal - statement_id + sequence 9 0 Table input - statement_id + sequence ####0;-####0 . , @@ -1324,14 +1174,14 @@ N - Integer + String normal - tax_line_id - 9 - 0 + name + 2147483647 + -1 Table input - tax_line_id - ####0;-####0 + name + . , @@ -1347,16 +1197,16 @@ N - Integer + BigNumber normal - tax_group_id - 9 - 0 + quantity + -1 + -1 Table input - tax_group_id - ####0;-####0 + quantity + ######0.0###################;-######0.0################### . - , + none N @@ -1372,11 +1222,11 @@ BigNumber normal - tax_base_amount + price_unit -1 -1 Table input - tax_base_amount + price_unit ######0.0###################;-######0.0################### . @@ -1393,16 +1243,16 @@ N - Boolean + BigNumber normal - tax_exigible + discount -1 -1 Table input - tax_exigible - + discount + ######0.0###################;-######0.0################### . - , + none N @@ -1416,16 +1266,16 @@ N - Integer + BigNumber normal - tax_repartition_line_id - 9 - 0 + debit + -1 + -1 Table input - tax_repartition_line_id - ####0;-####0 + debit + ######0.0###################;-######0.0################### . - , + none N @@ -1439,16 +1289,16 @@ N - String + BigNumber normal - tax_audit - 2147483647 + credit + -1 -1 Table input - tax_audit - + credit + ######0.0###################;-######0.0################### . - , + none N @@ -1464,11 +1314,11 @@ BigNumber normal - amount_residual + balance -1 -1 Table input - amount_residual + balance ######0.0###################;-######0.0################### . @@ -1487,11 +1337,11 @@ BigNumber normal - amount_residual_currency + amount_currency -1 -1 Table input - amount_residual_currency + amount_currency ######0.0###################;-######0.0################### . @@ -1508,16 +1358,16 @@ N - Integer + BigNumber normal - full_reconcile_id - 9 - 0 + price_subtotal + -1 + -1 Table input - full_reconcile_id - ####0;-####0 + price_subtotal + ######0.0###################;-######0.0################### . - , + none N @@ -1531,16 +1381,16 @@ N - String + BigNumber normal - matching_number - 2147483647 + price_total + -1 -1 Table input - matching_number - + price_total + ######0.0###################;-######0.0################### . - , + none N @@ -1554,14 +1404,14 @@ N - Integer + Boolean normal - analytic_account_id - 9 - 0 + reconciled + -1 + -1 Table input - analytic_account_id - ####0;-####0 + reconciled + . , @@ -1577,13 +1427,13 @@ N - String + Boolean normal - display_type - 2147483647 + blocked + -1 -1 Table input - display_type + blocked . , @@ -1600,13 +1450,13 @@ N - Boolean + Date normal - is_rounding_line + date_maturity -1 -1 Table input - is_rounding_line + date_maturity . , @@ -1623,14 +1473,14 @@ N - Boolean + Integer normal - exclude_from_invoice_tab - -1 - -1 + currency_id + 9 + 0 Table input - exclude_from_invoice_tab - + currency_id + ####0;-####0 . , @@ -1648,11 +1498,11 @@ Integer normal - create_uid + partner_id 9 0 Table input - create_uid + partner_id ####0;-####0 . , @@ -1669,14 +1519,14 @@ N - Timestamp + Integer normal - create_date - 6 - -1 + product_uom_id + 9 + 0 Table input - create_date - + product_uom_id + ####0;-####0 . , @@ -1694,11 +1544,11 @@ Integer normal - write_uid + product_id 9 0 Table input - write_uid + product_id ####0;-####0 . , @@ -1715,14 +1565,14 @@ N - Timestamp + Integer normal - write_date - 6 - -1 + reconcile_model_id + 9 + 0 Table input - write_date - + reconcile_model_id + ####0;-####0 . , @@ -1738,14 +1588,14 @@ N - Boolean + Integer normal - is_anglo_saxon_line - -1 - -1 + payment_id + 9 + 0 Table input - is_anglo_saxon_line - + payment_id + ####0;-####0 . , @@ -1763,11 +1613,11 @@ Integer normal - purchase_line_id + statement_line_id 9 0 Table input - purchase_line_id + statement_line_id ####0;-####0 . , @@ -1786,11 +1636,11 @@ Integer normal - asset_category_id + statement_id 9 0 Table input - asset_category_id + statement_id ####0;-####0 . , @@ -1807,14 +1657,14 @@ N - Date + Integer normal - asset_start_date - -1 - -1 + tax_line_id + 9 + 0 Table input - asset_start_date - + tax_line_id + ####0;-####0 . , @@ -1830,14 +1680,14 @@ N - Date + Integer normal - asset_end_date - -1 - -1 + tax_group_id + 9 + 0 Table input - asset_end_date - + tax_group_id + ####0;-####0 . , @@ -1855,11 +1705,11 @@ BigNumber normal - asset_mrr + tax_base_amount -1 -1 Table input - asset_mrr + tax_base_amount ######0.0###################;-######0.0################### . @@ -1878,11 +1728,11 @@ Boolean normal - is_landed_costs_line + tax_exigible -1 -1 Table input - is_landed_costs_line + tax_exigible . , @@ -1901,12 +1751,35 @@ Integer normal - cost_centre_id - 9 - 0 + tax_repartition_line_id + 9 + 0 + Table input + tax_repartition_line_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal + tax_audit + 2147483647 + -1 Table input - cost_centre_id - ####0;-####0 + tax_audit + . , @@ -1921,340 +1794,467 @@ Asia/Bangkok N - - - - - - - - - - - 112 - 80 - Y - - - - Insert / update - InsertUpdate - - Y - - 1 - - none - - - dw bi indoteknik (localhost) - 100 - N - - public - account_move_line
- - id - id - = - - - - id - id - N - - - move_id - move_id - Y - - - move_name - move_name - Y - - - date - date - Y - - - ref - ref - Y - - - parent_state - parent_state - Y - - - journal_id - journal_id - Y - - - company_id - company_id - Y - - - company_currency_id - company_currency_id - Y - - - account_id - account_id - Y - - - account_root_id - account_root_id - Y - - - sequence - sequence - Y - - - name - name - Y - - - quantity - quantity - Y - - - price_unit - price_unit - Y - - - discount - discount - Y - - - debit - debit - Y - - - credit - credit - Y - - - balance - balance - Y - - - amount_currency - amount_currency - Y - - - price_subtotal - price_subtotal - Y - - - price_total - price_total - Y - - - reconciled - reconciled - Y - - - blocked - blocked - Y - - - date_maturity - date_maturity - Y - - - currency_id - currency_id - Y - - - partner_id - partner_id - Y - - - product_uom_id - product_uom_id - Y - - - product_id - product_id - Y - - - reconcile_model_id - reconcile_model_id - Y - - - payment_id - payment_id - Y - - - statement_line_id - statement_line_id - Y - - - statement_id - statement_id - Y - - - tax_line_id - tax_line_id - Y - - - tax_group_id - tax_group_id - Y - - - tax_base_amount - tax_base_amount - Y - - - tax_exigible - tax_exigible - Y - - - tax_repartition_line_id - tax_repartition_line_id - Y - - - tax_audit - tax_audit - Y - - + + BigNumber + normal amount_residual - amount_residual - Y - - + -1 + -1 + Table input + amount_residual + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal amount_residual_currency - amount_residual_currency - Y - - + -1 + -1 + Table input + amount_residual_currency + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal full_reconcile_id - full_reconcile_id - Y - - + 9 + 0 + Table input + full_reconcile_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal matching_number - matching_number - Y - - + 2147483647 + -1 + Table input + matching_number + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal analytic_account_id - analytic_account_id - Y - - + 9 + 0 + Table input + analytic_account_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal display_type - display_type - Y - - + 2147483647 + -1 + Table input + display_type + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal is_rounding_line - is_rounding_line - Y - - + -1 + -1 + Table input + is_rounding_line + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal exclude_from_invoice_tab - exclude_from_invoice_tab - Y - - + -1 + -1 + Table input + exclude_from_invoice_tab + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal create_uid - create_uid - Y - - + 9 + 0 + Table input + create_uid + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal create_date - create_date - Y - - + 6 + -1 + Table input + create_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal write_uid - write_uid - Y - - + 9 + 0 + Table input + write_uid + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal write_date - write_date - Y - - + 6 + -1 + Table input + write_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal is_anglo_saxon_line - is_anglo_saxon_line - Y - - + -1 + -1 + Table input + is_anglo_saxon_line + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal purchase_line_id - purchase_line_id - Y - - + 9 + 0 + Table input + purchase_line_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal asset_category_id - asset_category_id - Y - - + 9 + 0 + Table input + asset_category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Date + normal asset_start_date - asset_start_date - Y - - + -1 + -1 + Table input + asset_start_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Date + normal asset_end_date - asset_end_date - Y - - + -1 + -1 + Table input + asset_end_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal asset_mrr - asset_mrr - Y - - + -1 + -1 + Table input + asset_mrr + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal is_landed_costs_line - is_landed_costs_line - Y - - + -1 + -1 + Table input + is_landed_costs_line + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal cost_centre_id - cost_centre_id - Y - - + 9 + 0 + Table input + cost_centre_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -2264,7 +2264,7 @@ - 288 + 112 80 Y diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb index 25c7ce6..83fc5fd 100644 --- a/import_all_data_bi_indoteknik.kjb +++ b/import_all_data_bi_indoteknik.kjb @@ -800,6 +800,84 @@ 416 + + import account move + + TRANS + + filename + + /home/stephan/transformation-job-bi/import_account_move.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 928 + 416 + + + + import account move line + + TRANS + + filename + + /home/stephan/transformation-job-bi/import_account_move_line.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 1152 + 416 + + @@ -919,6 +997,24 @@ Y N + + import sale order + import account move + 0 + 0 + Y + Y + N + + + import account move + import account move line + 0 + 0 + Y + Y + N + -- cgit v1.2.3