Metasfresh Documentation

https://docs.metasfresh.org/
Admin
Metasfresh documentation provides comprehensive guides for users, administrators, and developers,...
Tokens:22,316
Snippets:318
Trust Score:6
Update:4 months ago
Show doc for...
Tokens:
Raw
### Example: Get Table ID for C_Invoice_Candidate

Source: https://docs.metasfresh.org/sql_collection/tips_get_table_id.html

This example demonstrates how to call the `get_table_id` function with a specific table name, 'C_Invoice_Candidate', and shows the expected integer result.

```sql
select   get_table_id('C_Invoice_Candidate')
```

--------------------------------

### INSERT Example Creating Inventory Record

Source: https://docs.metasfresh.org/sql_collection/m_inventory.html

Use the I_Inventory table to import inventory records. This SQL statement inserts a new record into the m_inventory table.

```sql
INSERT INTO m_inventory

              (
              m_inventory_id,
              ad_client_id,
              ad_org_id,
              created,
              createdby,
              updated,
              updatedby,
              documentno,
              description,
              m_warehouse_id,
              movementdate,
              posted,
              processed,
              processing,
              updateqty,
              isapproved,
              docstatus,
              docaction,
              c_doctype_id
              )
VALUES (nextval('m_inventory_seq'),
        1000000,
        1000000,
        now(),
        100,
        now(),
        100,
        nextval('m_inventory_seq'),
        null,
        1000012,
       now()::date,
        'N',
        'N',
        'N',
        NULL, -- updateqty
        'Y',
        'DR',
        'CO',
        1000023
        );
  

```

--------------------------------

### Find Product Updates

Source: https://docs.metasfresh.org/sql_collection/ad_changelog.html

This example shows how to find changes specifically for products, filtering by table ID and creation date. It retrieves the column name, creation timestamp, old value, and new value for product updates.

```sql
select adc.columnname,adcl.created,adcl.oldvalue,adcl.newvalue from ad_changelog adcl
left join m_product mp on mp.m_product_id = adcl.record_id
    left join ad_column adc on adc.ad_column_id=adcl.ad_column_id
where adcl.ad_table_id = get_table_id('m_product')
and adcl.created >= '2020-12-01'

```

--------------------------------

### Get Stock Quantity by Product and UOM

Source: https://docs.metasfresh.org/sql_collection/md_stock_from_hus_v.html

Retrieve the quantity on stock for a given product and unit of measure. Joins with m_product and c_uom tables to display product name and UOM symbol.

```sql
select mp.value, mp.name,uom.uomsymbol, qtyonhand
from md_stock_from_hus_v stock
        join m_product mp on mp.m_product_id = stock.m_product_id
        join c_uom uom on stock.c_uom_id = uom.c_uom_id

```

--------------------------------

### Example: Prevent Duplicate Fiscal Year Entry

Source: https://docs.metasfresh.org/sql_collection/smart_migration_script.html

This SQL example ensures that the year 2018 is not already present in the fiscal year table before attempting to insert it. This prevents hitting a unique constraint violation.

```sql
DO $$
BEGIN
	IF NOT EXISTS( SELECT 1 FROM fiscalyear WHERE "year" = 2018 AND "calendar_id" = 1 ) THEN
		INSERT INTO fiscalyear ("calendar_id", "year", "name", "start_date", "end_date", "active") VALUES (1, 2018, '2018', '2018-01-01', '2018-12-31', true);
	END IF;
END;
$$;
```

--------------------------------

### Get Work Package Status by Table and Record ID

Source: https://docs.metasfresh.org/sql_collection/c_queue_element.html

Retrieves the status of work packages for queue elements, filtered by table and record ID. Use this to check the processing state of specific records.

```sql
SELECT
qw.created,
qw.c_queue_workpackage_id,
qw.processed,
iserror,
errormsg
FROM c_queue_element qe
JOIN c_queue_workpackage qw ON qe.c_queue_workpackage_id = qw.c_queue_workpackage_id
JOIN ad_table t ON t.ad_table_id = qe.ad_table_id
AND qe.record_id IN (<id>)
AND t.tablename = 'C_Invoice_Candidate'
ORDER BY qw.c_queue_workpackage_id

```

--------------------------------

### Get Table ID Function Signature

Source: https://docs.metasfresh.org/sql_collection/tips_get_table_id.html

This shows the basic signature for the `get_table_id` function. You need to provide the table name as a string argument.

```sql
get_table_id('<name of table')
```

--------------------------------

### Count Changelogs Per Table (Top 10)

Source: https://docs.metasfresh.org/sql_collection/ad_changelog.html

Use this query to identify the top 10 tables with the most recorded changes in the changelog. It joins AD_Changelog with AD_Table to get table names.

```sql
select tablename, count(*) from ad_changelog
join ad_table on ad_table.ad_table_id=ad_changelog.ad_table_id
group by tablename
order by count(*) desc
limit 10

```

--------------------------------

### Get UI Element Field Name

Source: https://docs.metasfresh.org/sql_collection/ad_ui_element.html

Retrieves the field name of a UI element within a specific window. Use this to identify the underlying database field for a UI component.

```sql
SELECT
  uie.AD_UI_Element_ID,
  f.name
FROM ad_ui_element uie
  JOIN ad_field f ON uie.ad_field_id = f.ad_field_id
  JOIN ad_tab t ON f.ad_tab_id = t.ad_tab_id
  JOIN ad_window w ON t.ad_window_id = w.ad_window_id
WHERE TRUE
      AND uie.AD_UI_Element_ID = 544809
      AND w.name = 'Auftrag'
```

--------------------------------

### Connect to PostgreSQL Database

Source: https://docs.metasfresh.org/sql_collection/tips_postgres_import.html

Connect to your PostgreSQL database using the `psql` command-line utility. Replace `metasfresh` with your actual database name.

```bash
psql -d metasfresh
```

--------------------------------

### Select BOM and Product Details

Source: https://docs.metasfresh.org/sql_collection/pp_product_bom.html

Retrieves distinct Bill of Material names, product values, product names, and product descriptions. It joins the product table with pp_product_bomline and pp_product_bom to link products to their BOM structures.

```sql
SELECT DISTINCT
  bom.name,
  p.value,
  p.name,
  p.description
FROM m_product p
  LEFT JOIN pp_product_bomline boml ON boml.m_product_id = p.m_product_id
  LEFT JOIN pp_product_bom bom ON bom.pp_product_bom_id = boml.pp_product_bom_id
  
ORDER BY bom.name,p.name

```

--------------------------------

### SQL Query for Full Product and Pricing Details

Source: https://docs.metasfresh.org/sql_collection/m_pricelist.html

This snippet retrieves detailed information about products, their pricing, categories, units of measure, and tax details from various tables including M_Pricelist, M_Product, and C_Tax. It filters results based on a specific pricelist ID.

```sql
SELECT
    p.value as product_no,
    p.name as product_name,
    p.isactive as product_active,
    p.upc as product_ean,
    p.weight as product_weight,
    p.description as product_description,
    p.documentnote as product_description_long,
    uomsymbol as uom_name,
    pc.name as category_name,
    pcp.name as parent_category_name,
    t.name as tax_name,
    t.rate as tax_percentage,
    pl.name as pricelist_name,
    plv.validfrom as pricelistversion_validfrom,
    pp.pricestd as price

  from m_pricelist pl
  join m_pricelist_version plv on plv.m_pricelist_id = pl.m_pricelist_id
  join m_productprice pp on pp.m_pricelist_version_id = plv.m_pricelist_version_id
  join m_product p on pp.m_product_id = p.m_product_id
  join m_product_category pc on pc.m_product_category_id = p.m_product_category_id
  left join m_product_category  pcp on pcp.m_product_category_id = pc.m_product_category_parent_id
  left join c_taxcategory tc on tc.c_taxcategory_id = pp.c_taxcategory_id
  left join c_tax t on t.c_taxcategory_id = tc.c_taxcategory_id and t.c_tax_id in (1000023,1000022,1000024)
  join c_uom uom on uom.c_uom_id = pp.c_uom_id
  where pl.m_pricelist_id =2008396

```

--------------------------------

### Insert Records into i_bpartner Import Table

Source: https://docs.metasfresh.org/sql_collection/i_bpartner.html

This SQL snippet demonstrates how to insert new business partner records into the i_bpartner import table. It maps columns from a source migration table to the import table, generating a new ID and setting default values for various business partner attributes. Ensure the 'migration_data.<yourtable>' is replaced with your actual source table name.

```sql
INSERT INTO i_bpartner (i_bpartner_id, ad_client_id, ad_org_id, created, updated, createdby, updatedby, bpvalue, name,companyname, name2, bpcontactgreeting, firstname, lastname, address4, address2, address3, countrycode, postal, city
,isbillto,isshipto,isbilltodefault,isshiptodefault,isdefaultcontact,isbilltocontact_default,isshiptocontact_default)

  SELECT
  nextval('i_bpartner_seq'), 
  1000000,
  1000000,
  now(),
  now(),
  100,
  100,
  newpartnerid::VARCHAR(10)    AS value,
  neu_firmenname::VARCHAR(60)  AS name,
  neu_firmenname::VARCHAR(60)  AS companyname,
  neu_firmenname2::VARCHAR(60) AS name2,
  anrede::VARCHAR(10)          AS greeting,
  vorname::VARCHAR(60)         AS firstname,
  name::VARCHAR(60)            AS lastname,
  strasse::VARCHAR(60)         AS address4,
  strasse_zusatz::VARCHAR(60)  AS address2,
  postfach::VARCHAR(60)        AS address3,
  neu_land        as countrycode,
  trim(plz)::VARCHAR(10)             as postal,
  trim(ort)::VARCHAR(10)             as city,
    'Y','Y','Y','Y','Y','Y','Y'

FROM migration_data.<yourtable>
WHERE true

```

--------------------------------

### Export Binary Data from AD_Archive to File

Source: https://docs.metasfresh.org/sql_collection/ad_archive.html

This snippet demonstrates how to export binary data from the ad_archive table to a file using the PostgreSQL COPY command. It then shows how to fix the zip file using the zip command and provides steps to prepare the file for viewing as a PDF.

```sql
metasfresh=# COPY (SELECT binarydata from ad_archive limit 1) TO E'/tmp/youroutputfile.zip' (FORMAT binary);
```

```bash
zip -FFv youroutputfile.zip --out fixed.zip
```

--------------------------------

### Insert Product Records into Discount Schema Break

Source: https://docs.metasfresh.org/sql_collection/m_discountschemabreak.html

This snippet demonstrates how to insert product records into the M_DiscountSchemaBreak table. It selects products from the m_product table based on a product category ID and associates them with a specific discount schema.

```sql
INSERT INTO public.M_DiscountSchemaBreak
SELECT
nextval('m_discountschemabreak_seq'),
1000000,
1000000,
  'Y',
now(),
100,
now(),
100,
540006, --ID of Discount SChema
m_product_id,
1,
0,
  null,
  m_product_id,
'N'

FROM m_product
where m_product_category_id = 540008; --ID of Product Category we want to add
  

```

--------------------------------

### Insert Pricing Records with Subqueries

Source: https://docs.metasfresh.org/sql_collection/m_productprice.html

This SQL statement inserts new records into the m_productprice table. It uses subqueries to dynamically fetch IDs for pricelist versions, organizations, and units of measure, and joins with migration data to link to existing products.

```sql
INSERT INTO public.m_productprice
(m_pricelist_version_id, m_product_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,
 pricelist, pricestd, pricelimit, usescaleprice, m_productprice_id, c_taxcategory_id,
 isattributedependant, isseasonfixedprice, c_uom_id, seqno, c_uom_id_pre_08147, m_attributesetinstance_id, isdefault,
 m_discountschemaline_id, m_productprice_base_id, matchseqno,
 ishuprice, m_hu_pi_item_product_id)
  (SELECT (SELECT m_pricelist_version_id
           FROM m_pricelist_version
           WHERE name = '<your pricelist name>'),
          data.m_product_id,
          1000000,
          (Select ad_org_id from ad_org where name ilike '<name of your org>'),
          'Y',
          now(),
          100,
          now(),
          100,
          0, --price list
          <name of your price column in migration data> ,
          0,
          'N',
          nextval('m_productprice_seq'),
          <put your tax category id here>,
          'N',
          'N',
          (select c_uom_id from c_uom where c_uom.name = <uom column in your migration data> ),
          10, --seq shall not be null
          NULL,
          NULL,
          'N',
          NULL,
          NULL,
          0,
          'N',
          NULL
   FROM (SELECT *
         FROM migration_data.product_master_data_old_system
         JOIN m_product ON value = oldsystem.value -- you need to join so we can provide data.m_product_id in the lines above
        ) AS data
  );
```

--------------------------------

### Server Log File Path

Source: https://docs.metasfresh.org/howto_collection/EN_tbd/Where_to_find_the_Serverlog.html

The primary server log file is located at this path on the server. Access it for troubleshooting.

```text
/opt/metasfresh/log/metasfresh_server.log
```

--------------------------------

### Insert Product UOM Conversion Record

Source: https://docs.metasfresh.org/sql_collection/c_uom_conversion.html

Use this snippet to insert a new record into the c_uom_conversion table. Ensure that the multiply rate and dividerate are correlated and that the correct UOM, target UOM, and product IDs are provided. The ad_org_id should be replaced with your organization's ID.

```sql
INSERT INTO c_uom_conversion (
              c_uom_conversion_id,
              ad_client_id,
              ad_org_id,
              isactive,
              created,
              createdby,
              updated,
              updatedby,
              c_uom_id,
              c_uom_to_id,
              multiplyrate,
              dividerate,
              m_product_id)

	(
        select
        nextval('c_uom_conversion_seq'),
        1000000,
        <your ad_org_id>,
        'Y',
        now(), --timestamp
        100, --SuperUser ID. Could be other user ID.
        now(), --timestamp
        100, --SuperUser ID. Could be other user ID.
        100, --Source UOM
        1000000, --Target UOM
        0.25, --Multiply Rate !! Needs to correlate with Divide Rate !!
        4, -- Divide Rate !! Needs to correlate with Multiply Rate !!
        data.m_product_id -- Product ID
        from
          (SELECT *
               FROM migration_data.product_master_data_old_system
               JOIN m_product ON value = oldsystem.value -- you need to join so we can provide data.m_product_id in the lines above
              ) AS data
       );
```

--------------------------------

### Delete Pricing System and Product Price Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes pricing system configurations and associated product prices, excluding specific systems.

```sql
--DELETE FROM M_PricingSystem WHERE Name NOt IN ('Divers','Standardpreis System');
--DELETE FROM M_PriceList c WHERE NOT EXISTS (select 1 from M_PricingSystem p where p.M_PricingSystem_ID=c.M_PricingSystem_ID);
--DELETE FROM M_PriceList_Version c WHERE NOT EXISTS (select 1 from M_PriceList p where p.M_PriceList_ID=c.M_PriceList_ID);
--DELETE FROM M_ProductPrice c WHERE NOT EXISTS (select 1 from M_PriceList_Version p where p.M_PriceList_Version_id=c.M_PriceList_Version_id)
	OR NOT EXISTS (select 1 from M_Product p where p.M_Product_id=c.M_Product_id)
;
--DELETE FROM m_productscaleprice; -- we don't have scaleprices anyways
--DELETE FROM M_ProductPrice_Attribute;
--DELETE FROM m_productprice_attribute_line;

--DELETE FROM m_discountschemaBreak;
--DELETE FROM m_discountschemaline;
```

--------------------------------

### Insert Changelog Entry

Source: https://docs.metasfresh.org/sql_collection/ad_changelog.html

This SQL statement demonstrates how to insert a new record into the AD_Changelog table. It dynamically retrieves table and column IDs and populates fields like old and new values, user, and timestamps. Ensure to replace `fix.<your table>` with the actual source table.

```sql
INSERT INTO public.ad_changelog (ad_changelog_id, ad_session_id, ad_table_id, ad_column_id, ad_client_id, ad_org_id,
                                 isactive, created, createdby, updated, updatedby, record_id, oldvalue, newvalue, undo,
                                 redo, iscustomization, trxname, description, eventchangelog, ad_pinstance_id)
select nextval('ad_changelog_seq'),
       1000000,
       get_table_id('c_bp_bankaccount'),
       (select ad_column_id from ad_column where ad_table_id = get_table_id('c_bp_bankaccount') and columnname ilike 'accountno'),
       1000000,
       1000000,
       'Y',
       now(),
       2200950,
       now(),
       2200950,
       c_bp_bankaccount_id,
       accountno,
       new_accountno,
       null,
       null,
       'N',
       'gh553',
       'Info why it was changed',
       'U',
       null
from  (select * from fix.<your table>) data
;

```

--------------------------------

### Select Table and Column Names

Source: https://docs.metasfresh.org/sql_collection/ad_table.html

Retrieve the table name and column name for a specific table, 'm_shipmentschedule'. This snippet demonstrates joining AD_Table and AD_Column entities.

```sql
SELECT
  t.tablename,
  t.name,
  c.columnname  
FROM ad_table t
  JOIN ad_column c ON c.ad_table_id = t.ad_table_id
WHERE t.tablename ILIKE 'm_shipmentschedule'


```

--------------------------------

### Export View to CSV

Source: https://docs.metasfresh.org/sql_collection/tips_postgres_misc.html

Use the COPY command to export data from a specific view to a CSV file with tab delimiter and header.

```sql
copy (select * from public.rv_your_export_view) to '/tmp/ex2.csv' with delimiter E'\t' CSV HEADER;
```

--------------------------------

### Select Unprocessed Events by Topic

Source: https://docs.metasfresh.org/sql_collection/ad_eventlog.html

Retrieves unprocessed events from the AD_Eventlog table, filtered by creation date and topic. Joins with AD_Eventlog_Entry to include error status.

```sql
select eventtopicname, eventtime,processed,msgtext,entry.iserror from ad_eventlog event
left join ad_eventlog_entry entry ON event.ad_eventlog_id = entry.ad_eventlog_id
where true
--and eventtopicname='de.metas.acct.handler.DocumentPostRequest'
and event.created::date=now()::date
and processed= 'N'
order by event.eventtime desc

```

--------------------------------

### Export All Document Actions (en_US)

Source: https://docs.metasfresh.org/sql_collection/export_translation_document_actions.html

Retrieves all active document actions and their translations for the 'en_US' locale. This query is useful for generating a reference list of document actions and their English names.

```sql
SELECT
  rl.ad_ref_list_id,
  rl.value,
  rl.name as name_de,
  rltrlen.name as name_en,
  '' as name_trl
FROM ad_ref_list rl
  JOIN ad_ref_list_trl rltrlen ON rl.ad_ref_list_id = rltrlen.ad_ref_list_id AND rltrlen.ad_language = 'en_US'
WHERE rl.ad_reference_id = 135
and rl.isactive = 'Y'
```

--------------------------------

### Count Unprocessed Packages per Queue Processor

Source: https://docs.metasfresh.org/sql_collection/c_queue_workpackage.html

Counts the number of unprocessed, errored, ready, and active work packages for each queue processor. Use this to monitor queue backlog and identify potential issues.

```sql
select  qp.name,count(*) 
from c_queue_workpackage qw
	join c_queue_block qb on qb.c_queue_block_id = qw.c_queue_block_id 
		join c_queue_processor qp on qp.c_queue_processor_id = qb.c_queue_packageprocessor_id 
where qw.processed = 'N' AND qw.IsError = 'Y'  AND qw.IsReadyForProcessing='Y' AND qw.IsActive='Y'
group by qp.name

```

--------------------------------

### Delete Tour and Package Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes tour, tour version, tour version line, package, package line, and related packaging data.

```sql
--DELETE FROM m_tour;
--DELETE FROM m_tourversion;
--DELETE FROM m_tourversionline;

--DELETE FROM m_package;
--DELETE FROM m_packageLine;
--DELETE FROM m_packagingcontainer;
--DELETE FROM m_packagingtree;
--DELETE FROM m_packagingtreeitem;
--DELETE FROM m_packagingtreeitemsched;
```

--------------------------------

### Retrieve Purchase Order Details with Purchase Candidate

Source: https://docs.metasfresh.org/sql_collection/c_order.html

This query retrieves details of completed purchase orders that are linked to purchase candidates. It joins several tables to fetch product names, quantities, and prices.

```sql
select
       po.documentno,
       po.docstatus,
       po.totallines,
       mp.name as product_name,
       pol.qtyordered,
       pol.pricestd       
from c_order po
    join c_orderline pol on po.c_order_id = pol.c_order_id
    join c_purchasecandidate_alloc pca on pol.c_orderline_id = pca.c_orderlinepo_id
    join c_purchasecandidate cp on pca.c_purchasecandidate_id=cp.c_purchasecandidate_id
    join m_product mp on pol.m_product_id = mp.m_product_id

where true
and po.issotrx = 'N' --only purchase orders
and po.docstatus='CO' --only completed ones



```

--------------------------------

### Active Schedulers with Last Run Logs

Source: https://docs.metasfresh.org/sql_collection/ad_scheduler.html

Retrieves active schedulers, their cron patterns, next and last run times, status, and a summary of the last log entry. Useful for monitoring scheduler activity.

```sql
SELECT
  name,
  sched.cronpattern,
  datenextrun,
  datelastrun,
  status,
  sched.isactive,
  schedl.summary

FROM ad_scheduler sched
JOIN ad_schedulerlog schedl on sched.ad_scheduler_id =schedl.ad_scheduler_id
WHERE true
 AND sched.isactive='Y'
order by sched.name, schedl.created

```

--------------------------------

### Insert into M_Product_Category

Source: https://docs.metasfresh.org/sql_collection/m_product_category.html

Use this SQL statement to insert a new record into the public.m_product_category table. It includes placeholders for sequence values, IDs, timestamps, and various category attributes.

```sql
INSERT INTO PUBLIC.m_product_category
select
nextval('m_product_category_seq'),
	1000000,
    1000000,
    'Y',
	now(),
	100,
	now(),
	100,
	'Value',
	'Name',
	null,
	'N',
	0,
    null,
    'N',
    null,
    'F',
     NULL, NULL, NULL, NULL, NULL, 'N', 'N'


```

--------------------------------

### Import Tab-Delimited Data into PostgreSQL Table

Source: https://docs.metasfresh.org/sql_collection/tips_postgres_import.html

Import data from a tab-delimited text file into a specified PostgreSQL table. Ensure the file is UTF-8 encoded without BOM and located at `/tmp/yourfile.txt`. The `HEADER` option indicates that the first line of the file contains column names.

```sql
copy  migration_data.yourtable from '/tmp/yourfile.txt' with delimiter E'\t' CSV HEADER;
```

--------------------------------

### Export Window and Tab Translations (en_US)

Source: https://docs.metasfresh.org/sql_collection/export_translation_window_tabs.html

Exports all windows used in the menu and their active tabs with their German and English names. This query joins multiple tables including ad_menu, ad_window, ad_window_trl, ad_tab, and ad_tab_trl to retrieve the translated names based on the 'en_US' locale.

```sql
SELECT DISTINCT
  w.name as window_name_de,
  wtrl.name as window_name_en,
  w.ad_window_id ,
  t.name as tab_name_de,
  ttrl.name as tab_name_en,
  t.ad_tab_id

FROM ad_treenodemm mm
  JOIN ad_menu M ON mm.node_id = M.ad_menu_id AND mm.parent_id = 1000007
  LEFT JOIN ad_treenodemm mm2 ON mm2.parent_id = mm.node_id
  JOIN ad_menu m2 ON mm2.node_id = m2.ad_menu_id
  JOIN ad_window w ON w.ad_window_id = m2.ad_window_id
  JOIN ad_window_trl wtrl on wtrl.ad_window_id = w.ad_window_id and wtrl.ad_language='en_US'
  JOIN ad_tab t on w.ad_window_id = t.ad_window_id and t.isactive = 'Y'
  JOIN ad_tab_trl ttrl on ttrl.ad_tab_id = t.ad_tab_id and ttrl.ad_language='en_US'

WHERE TRUE
order by w.name, ttrl.name;
	  
```

--------------------------------

### List System and Base Languages

Source: https://docs.metasfresh.org/howto_collection/EN_tbd/List_Languages

This SQL query retrieves all languages marked as system languages or base languages from the 'ad_language' table. Ensure you have the necessary database permissions to execute this query.

```sql
select ad_language, issystemlanguage, isbaselanguage from ad_language where issystemlanguage = 'Y' or isbaselanguage = 'Y';
```

--------------------------------

### Query Subscription Progress Events

Source: https://docs.metasfresh.org/sql_collection/c_flatrate_term.html

Retrieves subscription progress events from C_SubscriptionProgress, joining with C_Flatrate_Term to filter by contract status and event type for future events. Useful for tracking upcoming subscription milestones.

```sql
SELECT
  c_flatrate_term_id,
  contractstatus,
  eventdate,
  eventtype
FROM c_subscriptionprogress sp
JOIN c_flatrate_term ft ON ft.c_flatrate_term_id = sp.c_flatrate_term_id
LEFT JOIN c_bpartner bpd ON ft.dropship_bpartner_id = bpd.c_bpartner_id
WHERE TRUE
AND (ContractStatus = 'Pa' OR eventtype IN ('PB', 'PE'))
AND eventdate > now()

ORDER BY c_flatrate_term_id, eventdate


```

--------------------------------

### Delete Instance and Change Log Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes data from process instance logs and system change logs.

```sql
--DELETE FROM AD_PInstance;
--DELETE FROM AD_PInstance_Para;
--DELETE FROM ad_pinstance_log;
--DELETE FROM AD_ChangeLog;
```

--------------------------------

### Create Readonly User

Source: https://docs.metasfresh.org/sql_collection/tips_postgres_misc.html

Create a new PostgreSQL user with read-only SELECT privileges on tables in the 'public' schema.

```sql
create role <your username> with PASSWORD '<your password>';
alter role <your username> LOGIN;
GRANT CONNECT ON DATABASE metasfresh TO <your username>; 
GRANT USAGE on SCHEMA public to <your username> ;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO <your username>;

```

--------------------------------

### Set Unprocessed Work Packages to Processed

Source: https://docs.metasfresh.org/sql_collection/c_queue_workpackage.html

Updates unprocessed work packages for a specific queue processor ('M_Storage_Add') to a 'processed' state, while also marking them as errors. This is useful for clearing specific error queues.

```sql
update c_queue_workpackage 
set processed='Y', iserror ='Y' from (
  select qp.name,c_queue_workpackage_id
  from c_queue_workpackage  qw
    join c_queue_block qb on qb.c_queue_block_id = qw.c_queue_block_id 
      join c_queue_processor qp on qp.c_queue_processor_id = qb.c_queue_packageprocessor_id 
  where qw.processed = 'N' and qp.name = 'M_Storage_Add'
) data
where c_queue_workpackage.c_queue_workpackage_id = data.c_queue_workpackage_id 



```

--------------------------------

### Export Table to CSV

Source: https://docs.metasfresh.org/sql_collection/tips_postgres_misc.html

Use the COPY command to export data from a table to a CSV file with tab delimiter and header.

```sql
copy c_bpartner to '/tmp/ex2.csv' with delimiter E'\t' CSV HEADER;
```

--------------------------------

### Retrieve Invoice Details with Printing Queue Info

Source: https://docs.metasfresh.org/sql_collection/c_docoutbound_log.html

Selects distinct invoice numbers, printing queue IDs, printed status, and document outbound log IDs. Joins C_Doc_Outbound_Log with C_Invoice, AD_Archive, and C_Printing_Queue. Filters by creation date.

```sql
SELECT DISTINCT
  i.documentno as "Invoice_No",
  pq.C_Printing_Queue_id as "Printing Queue ID",
  pq.processed as "printed",
  c_doc_outbound_log_id as "Doc Outbound ID"
FROM c_doc_outbound_log dol
  LEFT JOIN c_invoice i ON dol.ad_table_id = 318 AND dol.record_id = i.c_invoice_id
  LEFT JOIN AD_Archive ar ON ar.AD_Table_ID = 318 AND ar.record_id = i.c_invoice_id
  LEFT JOIN C_Printing_Queue pq ON ar.AD_Archive_ID = pq.AD_Archive_ID
  JOIN ad_org o ON i.ad_org_id = o.ad_org_id
WHERE TRUE
      AND dol.created >= '2018-01-05'


  ;
```

--------------------------------

### Show Subset of Changelog Records

Source: https://docs.metasfresh.org/sql_collection/ad_changelog.html

This query displays a detailed subset of changelog records, including table name, column name, update timestamp, old and new values, and the user who made the change. It filters out changes to ID columns and changes made by the 'System' user, as well as records where the old value was 'NULL'.

```sql
SELECT
  t.tablename,
  c.columnname,
  cl.updated,
  oldvalue,
  newvalue,
  u.name,
  oldvalue || '=>' || newvalue
FROM ad_changelog cl
  JOIN ad_table t ON cl.ad_table_id = t.ad_table_id
  JOIN ad_column c ON c.ad_column_id = cl.ad_column_id
  JOIN ad_user u ON u.ad_user_id = cl.createdby
WHERE columnname NOT ILIKE '%ID%'
      AND u.name != 'System'
      AND cl.oldvalue != 'NULL'
	  

```

--------------------------------

### Calculate Invoicing Performance Per Day

Source: https://docs.metasfresh.org/sql_collection/c_queue_element.html

Calculates the average and weighted average duration per line for invoicing, based on processed and error-free queue work packages. This helps in analyzing the efficiency of the invoicing process.

```sql
select tag,avg(durationperline)::integer,
  (sum(durationperline*count_qe)/sum(count_qe))::integer as weighted

from (

  SELECT
    qw.created :: date as tag,
    qe.c_queue_workpackage_id,
    qw.lastdurationmillis,
    count(c_queue_element_id) count_qe,
    (qw.lastdurationmillis / count(c_queue_element_id) ) :: integer as durationperline

  FROM c_queue_element qe
    JOIN c_queue_workpackage qw ON qe.c_queue_workpackage_id = qw.c_queue_workpackage_id

    where true
          and qw.processed = 'Y'
          and qw.iserror = 'N'
          and qe.ad_table_id=540270 --only invoice candidates

  group by
    qw.created :: date,
    qe.c_queue_workpackage_id,
    qw.lastdurationmillis

  ORDER BY qw.created :: date desc  

) data
group by tag
order by tag desc


```

--------------------------------

### Reopen Production Order

Source: https://docs.metasfresh.org/sql_collection/tips_reopen_processed_document.html

Resets the planning status of a Production Order to 'R' (Released) using its document number. This allows for reprocessing or modification.

```sql
update pp_order
set planningstatus = 'R'
where documentno = 'XYZ'
;
```

--------------------------------

### View Changelog Counts View

Source: https://docs.metasfresh.org/sql_collection/ad_changelog.html

This query selects all records from the `dlm.ad_changelog_counts_v` view, which likely provides aggregated counts or statistics related to changelog entries.

```sql
select * from dlm.ad_changelog_counts_v;

```

--------------------------------

### SQL Query for Address/Location Translation Elements

Source: https://docs.metasfresh.org/sql_collection/export_translation_elements.html

Retrieves translation elements for specified address and location columns in the en_US locale. Use this query to fetch the original element name and its translated name.

```sql
SELECT
    e.ad_element_id,
    e.columnname,
    e.name as element_name,
    etrl.name as element_trl_name
  FROM ad_element e
    JOIN ad_element_trl etrl ON etrl.ad_element_id = e.ad_element_id AND ad_language = 'en_US'
  WHERE e.columnname IN ('Address1', 'Address2', 'Address3', 'Address4','City', 'C_Country_ID')
```

--------------------------------

### Insert Request Records into I_Request Table

Source: https://docs.metasfresh.org/sql_collection/i_request.html

Use this SQL statement to efficiently insert a large number of request records from a migration table into the I_Request import table. This method is preferred over the Text File Loader for datasets exceeding 100,000 records.

```sql
INSERT INTO public.i_request (ad_client_id, ad_org_id, created, createdby, datetrx, i_errormsg, i_isimported, i_request_id, isactive, processed, r_status_id, result, status, summary, updated, updatedby,
                              value, c_bpartner_id, requesttype, r_requesttype_id, r_request_id, datenextaction, documentno)
  SELECT
    1000000, 
    1000000, 
    now(), 
    100, 
    erfassungsdatum :: TIMESTAMP,
    NULL, 
    'N', 
    nextval('i_request_seq'), 
    'Y', 
    'N', 
    1000000, 
    NULL, 
    NULL, 
    details, 
    now(), 
    100, 
    'G0001', 
    NULL, 
    vorgangsartneu, 
    NULL, 
    NULL, 
    nachfassdatum :: TIMESTAMP,
    NULL
  FROM migration_data.<yourtablename> 
  

```

--------------------------------

### Truncate Material Dispo Tables

Source: https://docs.metasfresh.org/sql_collection/material_dispo.html

Use this command to remove all data from the specified Material Dispo tables. This action is irreversible.

```sql
truncate
    md_candidate,
    md_candidate_demand_detail, 
    md_candidate_dist_detail,
    md_candidate_prod_detail,
    md_candidate_purchase_detail,
    md_candidate_transaction_detail,
    md_candidate_stockchange_detail


```

--------------------------------

### Delete User Preferences and Access Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes user preferences and organization access records for users who no longer exist.

```sql
--DELETE FROM AD_Preference p WHERE NOT EXISTS (select 1 from AD_User u where u.AD_User_ID=p.AD_User_ID);
--DELETE FROM AD_User_OrgAccess uoa WHERE NOT EXISTS (select 1 from AD_User u where u.AD_User_ID=uoa.AD_User_ID);

--DELETE FROM ad_userquery;

--DELETE FROM ad_treebar tb WHERE NOT EXISTS (select 1 from AD_User u where u.AD_User_ID=tb.AD_User_ID);
```

--------------------------------

### Retrieve Menu Node Names

Source: https://docs.metasfresh.org/sql_collection/ad_menu.html

This query retrieves the names of menu nodes and their associated child node names. It joins the AD_TreenodeMM table with AD_Menu twice to establish parent-child relationships.

```sql
select m.name,m2.name from ad_treenodemm mm
join ad_menu m on mm.node_id = m.ad_menu_id and mm.parent_id = 1000007
LEFT JOIN ad_treenodemm mm2 on mm2.parent_id = mm.node_id
join ad_menu m2 on mm2.node_id = m2.ad_menu_id
order by m.name,m2.name
```

--------------------------------

### Update User Language to German

Source: https://docs.metasfresh.org/howto_collection/EN_tbd/Change_user_language_database.html

Use this command to change the language for a specific user (ad_user_id = '100') to German ('de_DE') in the metasfresh database. A restart of the docker stack may be required after execution.

```bash
docker exec -u postgres metasfresh-docker_db_1 psql -d metasfresh -c "UPDATE ad_user SET ad_language = 'de_DE' WHERE ad_user_id = '100';"
```

--------------------------------

### Insert Flatrate Term Records

Source: https://docs.metasfresh.org/sql_collection/i_flatrate_term.html

Use this SQL statement to copy existing flatrate term records from another table into the i_flatrate_term table. This is useful for reapplying configurations to different systems.

```sql
INSERT INTO i_flatrate_term 
SELECT
  i_flatrate_term_id,
  ad_client_id,
  ad_org_id,
  bpartnervalue,
  c_flatrate_conditions_value,
  created,
  createdby,
  enddate,
  i_errormsg,
  isactive,
  startdate,
  updated,
  updatedby,
  productvalue,
  price,
  dropship_bpartner_value,
  masterstartdate,
  masterenddate,
  qty
FROM other_table


```

--------------------------------

### Delete M_Product Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Deletes product records, excluding standard products and shipping cost items. Also removes related conversion, accounting, costing, and translation data for non-standard products.

```sql
--DELETE FROM M_Product WHERE M_Product_ID NOT IN (1000000, 1000001); -- Standardprodukt, Versandkostenpauschale
--DELETE fROM c_uom_conversion c WHERE M_Product_ID IS NOT NULL AND NOT EXISTS (select 1 from M_Product p where p.M_Product_ID=c.M_Product_ID);

--UPDATE M_Product SET m_locator_ID=NULL;
--DELETE FROM M_Product_Acct WHERE NOT M_Product_ID IN (1000000, 1000001);
--DELETE FROM M_Product_Costing WHERE M_Product_ID NOT IN (1000000, 1000001);
--DELETE FROM m_product_trl WHERE M_Product_ID NOT IN (1000000, 1000001);
```

--------------------------------

### Delete Log and Attachment Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Use these scripts to clear out old log and attachment data from the system.

```sql
--DELETE FROM C_Doc_Outbound_Log;
--DELETE FROM C_Doc_Outbound_Log_Line;

--DELETE FROM AD_Archive;
--DELETE FROM AD_Attachment;
```

--------------------------------

### SQL Query for Exporting Field Translations (en_US)

Source: https://docs.metasfresh.org/sql_collection/export_field_translations.html

This query retrieves translated names for windows, tabs, fields, and elements for the 'en_US' locale. It filters for active fields displayed in the grid, on forms, or as advanced fields, and ensures the associated window is accessible through the main menu. Use this to generate a comprehensive list of translatable field information.

```sql
select DISTINCT * from (
  SELECT
    wtrl.name AS window_name_en,
    w.name AS window_name_de,
    ttrl.name AS tab_name_en,
    t.name AS tab_name_de,
    ftrl.name as field_name_en,
    f.name    AS field_name_de,
    c.columnname as db_column,
	  f.ad_field_id,
    e.ad_element_id,
    etrl.name as  element_en,
    e.name as  element_de

  FROM ad_window w
    LEFT JOIN ad_window_trl wtrl ON wtrl.ad_window_id = w.ad_window_id AND wtrl.ad_language = 'en_US'
    JOIN ad_tab t ON t.ad_window_id = w.ad_window_id
    LEFT JOIN ad_tab_trl ttrl ON ttrl.ad_tab_id = t.ad_tab_id AND ttrl.ad_language = 'en_US'
    JOIN ad_field f ON f.ad_tab_id = t.ad_tab_id
    LEFT JOIN ad_field_trl ftrl ON ftrl.ad_field_id = f.ad_field_id AND ftrl.ad_language = 'en_US'
    JOIN ad_column c ON c.ad_column_id = f.ad_column_id
    JOIN ad_element e ON e.ad_element_id = c.ad_element_id
    JOIN ad_element etrl ON etrl.ad_element_id = e.ad_element_id AND ftrl.ad_language = 'en_US'
    JOIN ad_ui_element uie ON uie.ad_field_id = f.ad_field_id

  WHERE TRUE
      -- and w.name = 'Geschäftspartner'
        AND f.isactive = 'Y'
        AND (uie.isdisplayedgrid = 'Y' OR uie.isdisplayed = 'Y' OR isadvancedfield = 'Y')
        AND w.isactive = 'Y'
        AND t.isactive = 'Y'
        AND exists
        (SELECT m2.ad_window_id
         FROM ad_treenodemm mm
           JOIN ad_menu M ON mm.node_id = M.ad_menu_id AND mm.parent_id = 1000007
           LEFT JOIN ad_treenodemm mm2 ON mm2.parent_id = mm.node_id
           JOIN ad_menu m2 ON mm2.node_id = m2.ad_menu_id
         WHERE m2.ad_window_id IS NOT NULL
               AND w.ad_window_id = m2.ad_window_id
        )

) 
as foo
order by window_name_en, tab_name_en, field_name_en

```

--------------------------------

### Conditional SQL Migration Script Structure

Source: https://docs.metasfresh.org/sql_collection/smart_migration_script.html

Use this construct to add an IF condition around your migration statement. It checks if the migration can be executed without problems, preventing issues with existing data.

```sql
DO $$
BEGIN
	IF NOT EXISTS( <select statement> ) THEN
		<migration statement like update, insert, create>...
	END IF;
END;
$$;
```

--------------------------------

### Insert with Next ID using Sequence

Source: https://docs.metasfresh.org/sql_collection/insert_using_id_column.html

Use nextval('<sequence name>') to retrieve and set the next available ID from a sequence when inserting a new record into a table.

```sql
insert into c_invoice (C_Invoice_ID, ....) select nextval('c_invoice_seq'), ... from ....


```

--------------------------------

### Query Flatrate Term with Related Data

Source: https://docs.metasfresh.org/sql_collection/c_flatrate_term.html

Selects various attributes from the C_Flatrate_Term table, joining with related tables for product, organization, partner, condition, and transition data. Useful for retrieving comprehensive subscription details.

```sql
select
	p.value as product,
	o.value as org,
	bp.value as partner_value,
	ft.contractstatus as "ft.contractstatus",
	ft.noticedate as extensiondate,
	ft.startdate as ft_startdate,
	ft.enddate as ft_enddate,
	ft.masterstartdate as masterstartdate,
	ft.masterenddate as masterenddate,
	fc.name as condition_name,
	ftt.name as transition_name,
	ft.plannedqtyperunit as ft_qty,
  	term_ref.name as terminationreason,
	ft.terminationmemo,

	''

from c_flatrate_term ft
--left join c_subscriptionprogress sp on ft.c_flatrate_term_id = sp.c_flatrate_term_id
left join c_flatrate_conditions fc on fc.c_flatrate_conditions_id = ft.c_flatrate_conditions_id
left join c_flatrate_transition ftt on fc.c_flatrate_transition_id = ftt.c_flatrate_transition_id
left join c_invoice_candidate ic on ic.ad_table_id = 540320 and ic.record_id = ft.c_flatrate_term_id
left join ad_org o on ft.ad_org_id = o.ad_org_id
left join m_product p on ft.m_product_id = p.m_product_id
left join c_bpartner bp on ft.dropship_bpartner_id = bp.c_bpartner_id
left join c_bpartner bpbill on ft.bill_bpartner_id = bpbill.c_bpartner_id  -- Rechnungsempfänger
  left join ad_ref_list term_ref on term_ref.ad_reference_id = 540761 and term_ref.value = ft.terminationreason

where true
 -- and terminationreason is not null


```

--------------------------------

### Delete Allocation and Queue Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes allocation header and line records, as well as queue block, work package, element, parameter, and log data.

```sql
--DELETE FROM C_AllocationHdr;
--DELETE FROM C_AllocationLine;

--DELETE FROM C_Queue_Block;
--DELETE FROM C_Queue_Workpackage;
--DELETE FROM C_Queue_Element;
--DELETE FROM c_queue_workpackage_param;
--DELETE FROM c_queue_workpackage_Log;
```

--------------------------------

### Retrieve File Attachment of a Record

Source: https://docs.metasfresh.org/sql_collection/ad_archive.html

Use this query to retrieve file attachments stored in ad_attachmententry that are linked to records in AD_WF_Node_Template via ad_attachment_multiref. It selects the workflow node template ID, filename, and binary data.

```sql
select ad_wf_node_template_id,e.filename,e.binarydata from AD_WF_Node_Template adwftemp
left join ad_attachment_multiref fileref on fileref.ad_table_id=get_table_id('AD_WF_Node_Template') and fileref.record_id= adwftemp.ad_wf_node_template_id
left JOIN ad_attachmententry e ON e.ad_attachmententry_id = fileref.ad_attachmententry_id
```

--------------------------------

### Delete Organizations (Except Specific IDs)

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

This script deletes organizations but excludes specific IDs (0 and 1000000) to preserve essential data.

```sql
--DELETE FROM AD_Org WHERE AD_Org_ID NOT IN (0, 1000000);
--DELETE FROM AD_OrgInfo WHERE AD_Org_ID NOT IN (0, 1000000);
```

--------------------------------

### Update Business Partner Contact ID

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Sets the C_bpartner_id to NULL for all AD_User records.

```sql
--UPDATE AD_User SET C_bpartner_id=NULL;
```

--------------------------------

### Delete Storage and Costing Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes storage, cost detail, and transaction records.

```sql
--DELETE FROM m_storage;
--DELETE FROM m_costdetail where AD_Org_ID>0;
--DELETE FROM m_transaction;
```

--------------------------------

### Reopen Flatrate Condition

Source: https://docs.metasfresh.org/sql_collection/tips_reopen_processed_document.html

Resets the document status and processed flag for a Flatrate Condition to allow reopening. Use with caution in test environments.

```sql
update c_flatrate_conditions
set docstatus = 'DR' , processed = 'N'
where c_flatrate_conditions_id in (?);
```

--------------------------------

### Delete Business Partner Account Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes account information for business partners if the business partner record no longer exists.

```sql
--DELETE FROM c_bp_customer_acct a WHERE NOT EXISTS (select 1 from C_BPartner bp where a.C_BPartner_ID=bp.C_BPartner_ID);
--DELETE FROM c_bp_employee_acct a WHERE NOT EXISTS (select 1 from C_BPartner bp where a.C_BPartner_ID=bp.C_BPartner_ID);
--DELETE FROM c_bp_vendor_acct a WHERE NOT EXISTS (select 1 from C_BPartner bp where a.C_BPartner_ID=bp.C_BPartner_ID);
```

--------------------------------

### Delete Non-Org Business Partners

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes business partners that are not associated with any organization and cleans up associated price list and tax ID information.

```sql
-- Deleting non-org bpartners
--DELETE FROM C_BPartner WHERE C_BPartner_ID!=1000000 AND COALESCE(AD_OrgBP_ID, 0)=0;
--UPDATE C_BPartner SET M_PriceList_ID=null, VaTaxID=null;
```

--------------------------------

### Delete Flatrate and Subscription Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes flatrate terms, matching, data entries, data, subscription progress, conditions, transitions, and contract changes.

```sql
--DELETE FROM c_flatrate_term;
--DELETE FROM c_flatrate_matching;
--DELETE FROM c_flatrate_dataentry;
--DELETE FROM c_flatrate_data;
--DELETE FROM c_subscriptionprogress;

--DELETE FROM C_Flatrate_Conditions; 
--DELETE FROM c_flatrate_transition; 
--DELETE FROM C_contract_change;
```

--------------------------------

### Delete Sponsor Data

Source: https://docs.metasfresh.org/howto_collection/datacleanup.html

Removes sponsor and sponsor sales representative records.

```sql
--DELETE FROM C_Sponsor;	
--DELETE FROM C_Sponsor_Salesrep;
```

--------------------------------

### Mark Flatrate Condition as Processed

Source: https://docs.metasfresh.org/sql_collection/tips_reopen_processed_document.html

Marks a Flatrate Condition as processed without actual system processing. This is for special cases where manual intervention is understood.

```sql
update c_flatrate_conditions
set docstatus = 'CO' , processed = 'Y'
where c_flatrate_conditions_id in (?);
```
Tokens:
Raw