I am running this script, but I keep getting map_id not found error
Movimientos:
LOAD
move_id,
date as EMISION,
Year(date) as Año,
Month(date) as Mes,
product_id as Prod,
ApplyMap('MapDescripProd', "product_id", ApplyMap('MapProducto', "product_id", Null())) as DESCRIPCIONPRODUCTO,
ApplyMap('MapIdProd', ApplyMap('MapProducto',"product_id", 'No encontrado'), 'Sin equivalente') as Prod_equiv,
ApplyMap('MapTipoProd',"product_id", ApplyMap('MapProdTipoProd', ApplyMap('MapProducto',"product_id"), 'Sin equivalente')) as Prod_tipo,
ApplyMap('MapClaseProd', "product_id", 'Sin equivalente') as Prod_categ,
ApplyMap('MapGrupoProd', "product_id", 'Sin equivalente') as Prod_grupo,
ApplyMap('MapFamiliaProd', "product_id", 'Sin equivalente') as Prod_familia,
/*
ApplyMap('MapProducto', "product_id", Null()) as DESCRIPCIONPRODUCTO,
ApplyMap('MapProdCateg', ApplyMap('MapProducto',"product_id", 'No encontrado'), 'Sin equivalente') as Prod_categ,
ApplyMap('MapProdTipoProd', ApplyMap('MapProducto',"product_id", 'No encontrado'), 'Sin equivalente') as Prod_tipo,
ApplyMap('MapProdGpo', ApplyMap('MapProducto',"product_id", 'No encontrado'), 'Sin equivalente') as Prod_grupo,
*/
ApplyMap('MapProdUM', ApplyMap('MapProducto',"product_id", 'No encontrado'), 'Sin equivalente') as Prod_UM,
ApplyMap('MapProdGpo', "product_id", Null()) as GRUPO,
ApplyMap('MapUnidad', "product_uom_id", Null()) as UNIDADMEDIDA,
ApplyMap('MapProdTpo', "product_id", Null()) as TipoProd,
ApplyMap('MapCurrency', "currency_id", Null()) as MONEDA,
account_id,
ApplyMap('MapCta', account_id) as Cta,
ApplyMap('MapCuenta', account_id) as Cuenta,
ApplyMap('MapCuentaName', account_id) as NomCta,
ApplyMap('Map_group_name', ApplyMap('MapCount_group_id',account_id)) as GpoCta,
ApplyMap('MapType', account_id) as TipoCta,
analytic_account_id,
journal_id,
company_id,
currency_id,
'ADLER' as IDEMPRESA,
ApplyMap('MapPartnerId', "commercial_partner_id") as partner,
"commercial_partner_id" as IDCLIENTE,
ApplyMap('Map_IdAlt_Cte', "commercial_partner_id", Null()) as id_cte_alt,
ApplyMap('MapClienteRef', "commercial_partner_id") as CLIENTE,
ApplyMap('MapCliente', "commercial_partner_id") as NOMBRECLIENTE,
ApplyMap('MapClienteZip', "commercial_partner_id", Null()) as CP,
ApplyMap('MapNomCorto', "commercial_partner_id", ApplyMap('MapCliente', "commercial_partner_id")) as NOMBRE,
ApplyMap('MapMercado', "commercial_partner_id", Null()) as Mercado,
/*
ApplyMap('Map_Cod_Cte', "commercial_partner_id", ApplyMap('MapClienteRef', "commercial_partner_id")) as CLIENTE,
ApplyMap('Map_Nom_Cte', "commercial_partner_id", ApplyMap('MapCliente', "commercial_partner_id")) as NOMBRECLIENTE,
ApplyMap('Map_CP_Cte', "commercial_partner_id", ApplyMap('MapClienteZip', "commercial_partner_id", Null())) as CP,
ApplyMap('Map_NomR_Cte', "commercial_partner_id", ApplyMap('MapCliente', "commercial_partner_id")) as NOMBRE,
*/
ApplyMap('WH_Code_Alt', "commercial_partner_id" & "team_id", Null()) as IdSucursal,
ApplyMap('WH_Name_Alt', "commercial_partner_id" & "team_id", Null()) as Sucursal,
ApplyMap('Map_Ruta_Cte', "commercial_partner_id", Null()) as A1_RUTA,
ApplyMap('Map_Zona_Cte', "commercial_partner_id", Null()) as A1_ZONA,
ApplyMap('Map_Gpo_Cte', "commercial_partner_id", Null()) as GRPCTE,
ApplyMap('MapClienteCountry', ApplyMap('MapClienteCountryId', "commercial_partner_id"), 'No identificado') as PaísCte,
ApplyMap('Map_Cve_Ent', "commercial_partner_id", ApplyMap('Map_Cve_Edo_Cte', ApplyMap('MapPartnerEdo', "commercial_partner_id"), 'No identificado')) as cve_ent,
// ApplyMap('Map_Nom_Ent_Ctes', "commercial_partner_id", ApplyMap('Map_Nom_Ent', ApplyMap('Map_Cve_Edo_Cte', "commercial_partner_id"), 'No identificado')) as ESTADO,
ApplyMap('MapPartnerState', ApplyMap('MapPartnerStateId', "commercial_partner_id"), 'No identificado') as ESTADO,
ApplyMap('Map_Cve_Mun', "commercial_partner_id", ApplyMap('Map_Cve_Mun_Cte', ApplyMap('Map_IdAlt_Cte', "commercial_partner_id"), 'No identificado')) as cve_mun,
ApplyMap('Map_Nom_Mun', "commercial_partner_id", ApplyMap('Map_Mun_Cte', ApplyMap('Map_IdAlt_Cte', "commercial_partner_id"), 'No identificado')) as Municipio,
ApplyMap('MapZonaDDR', ApplyMap('MapEdoMunDDR', ApplyMap('Map_Cve_Edo_Cte', "commercial_partner_id")&ApplyMap('Map_Cve_Mun_Cte', "commercial_partner_id"), Null()), Null()) as Zona,
ApplyMap('MapNomDistrito', ApplyMap('MapEdoMunDDR', ApplyMap('Map_Cve_Edo_Cte', "commercial_partner_id") &ApplyMap('Map_Cve_Mun_Cte', "commercial_partner_id")),
ApplyMap('MapNomDistrito', ApplyMap('Map_Cve_Ent', "commercial_partner_id")&ApplyMap('Map_Cve_Mun',"commercial_partner_id"),Null())) as Distrito,
ApplyMap('MapEdoMunDDR', ApplyMap('Map_Cve_Edo_Cte', "commercial_partner_id")&ApplyMap('Map_Cve_Mun_Cte', "commercial_partner_id"), Null()) as DDR,
ApplyMap('MapVendedor', "team_id", Null()) as VENDEDORNOMBRE,
name as FACTURA,
state,
type as ESPECIE,
partner_id,
invoice_user_id,
fiscal_position_id,
invoice_payment_state,
invoice_date,
invoice_date_due,
invoice_payment_term_id,
invoice_partner_bank_id,
ApplyMap('MapCosto', Year(invoice_date) &'|'& Month(invoice_date) &'|'& product_id, 0) as COSTOORIGEN,
ApplyMap('MapCosto', Year(invoice_date) &'|'& Month(invoice_date) &'|'& product_id, 0) * "quantity" as ValorCosto,
qty_cost as CANTIDAD,
residual,
amount_total,
product_uom_id,
product_categ_id,
quantity,
price_subtotal,
price_neto as PRECIOVENTA,
price_bruto as PRECIOUNITARIO,
price_cost,
price_other as TOTAL,
price_total as SUBTOTAL,
price_contrib,
marg,
price_average,
nbr_lines,
'México' as PAIS,
team_id;
SELECT "line"."id",
"line"."move_id",
"line"."product_id",
"line"."account_id",
"line"."analytic_account_id",
"line"."journal_id",
"line"."company_id",
"line"."company_currency_id" AS currency_id,
"line"."partner_id" AS commercial_partner_id,
"line"."date",
"move"."id",
"move"."name",
"move"."state",
"move"."type",
"move"."partner_id",
"move"."invoice_user_id",
"move"."fiscal_position_id",
"move"."invoice_payment_state",
"move"."invoice_date",
"move"."invoice_date_due",
"move"."invoice_payment_term_id",
"move"."invoice_partner_bank_id",
(- "line"."balance") * ("move"."amount_residual_signed" / NULLIF("move"."amount_total_signed", 0.0)) * ("line"."price_total" / NULLIF("line"."price_subtotal", 0.0)) AS residual,
(- "line"."balance") * ("line"."price_total" / NULLIF("line"."price_subtotal", 0.0)) AS amount_total,
"uom_template"."id" AS product_uom_id,
"template"."categ_id" AS product_categ_id,
"line"."quantity" / NULLIF(COALESCE("uom_line"."factor", 1::numeric) / COALESCE("uom_template"."factor", 1::numeric), 0.0) *
CASE
WHEN "move"."type"::text = ANY (ARRAY['in_invoice'::character varying::text, 'out_refund'::character varying::text, 'in_receipt'::character varying::text]) THEN '-1'::integer
ELSE 1
END::numeric AS quantity,
(- "line"."balance") AS price_subtotal,
CASE WHEN "line"."account_id" = ANY (ARRAY[1809,1810,1811,1812,1813,1814,1815,1816,1817,1819]) THEN 1 ELSE 0 END::numeric * "line"."balance" AS price_cost,
// (SELECT (b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798]) AND b.id = line.id AND b.quantity <> 0) AS price_other,
// (SELECT (b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796]) AND b.id = line.id AND b.quantity <> 0) AS price_total,
(SELECT (b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798]) AND b.id = line.id) AS price_other,
(SELECT (b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796]) AND b.id = line.id) AS price_total,
(SELECT (b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1809,1810,1811,1812,1813,1814,1815,1816,1817,1819]) AND b.id = line.id) AS price_contrib,
(SELECT Sum(b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1809,1810,1811,1812,1813,1814,1815,1816,1817,1819]) AND b.move_id = line.move_id AND b.product_id = line.product_id AND (b.credit-b.debit) <> 0 GROUP BY b.move_id, b.product_id) /
(SELECT Sum(b.credit-b.debit) FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798]) AND b.move_id = line.move_id AND b.product_id = line.product_id AND (b.credit-b.debit) <> 0 GROUP BY b.account_id, b.move_id, b.product_id) AS marg,
line.quantity / NULLIF(COALESCE(uom_line.factor, 1::numeric) / COALESCE(uom_template.factor, 1::numeric), 0.0) *
CASE WHEN debit <> 0 AND account_id = 1812 THEN 1::numeric ELSE 0 END::numeric +
line.quantity / NULLIF(COALESCE(uom_line.factor, 1::numeric) / COALESCE(uom_template.factor, 1::numeric), 0.0) *
CASE WHEN credit <> 0 AND account_id = 1812 THEN '-1'::numeric ELSE 0 END::numeric AS qty_cost,
// (SELECT (b.credit-b.debit) / b.quantity FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796]) AND b.id = line.id AND b.quantity <> 0) AS price_neto,
// (SELECT b.credit / b.quantity FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796]) AND b.id = line.id AND b.quantity <> 0) AS price_bruto,
(SELECT (b.credit-b.debit) / b.quantity FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796]) AND b.id = line.id) AS price_neto,
(SELECT b.credit / b.quantity FROM "Adler"."public"."account_move_line" b WHERE b.account_id = ANY (ARRAY[1787,1788,1789,1790,1791,1792,1793,1794,1795,1796]) AND b.id = line.id) AS price_bruto,
(- line.balance) / NULLIF(COALESCE(uom_line.factor, 1::numeric) / COALESCE(uom_template.factor, 1::numeric), 0.0) AS price_average,
COALESCE(partner.country_id, commercial_partner.country_id) AS country_id,
1 AS nbr_lines,
move.team_id
FROM account_move_line line
LEFT JOIN res_partner partner ON partner.id = line.partner_id
LEFT JOIN product_product product ON product.id = line.product_id
LEFT JOIN account_account account ON account.id = line.account_id
LEFT JOIN account_account_type user_type ON user_type.id = account.user_type_id
LEFT JOIN product_template template ON template.id = product.product_tmpl_id
LEFT JOIN uom_uom uom_line ON uom_line.id = line.product_uom_id
LEFT JOIN uom_uom uom_template ON uom_template.id = template.uom_id
JOIN account_move move ON move.id = line.move_id
LEFT JOIN res_partner commercial_partner ON commercial_partner.id = move.commercial_partner_id
WHERE line.account_id IN (1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1809,1810,1811,1812,1813,1814,1815,1816,1817,1819) AND state = 'posted' AND line.date > '2018-8-31'
GROUP BY line.id, line.move_id, line.product_id, line.account_id, line.analytic_account_id, line.journal_id, line.company_id, line.currency_id, line.partner_id, move.name, move.state, move.type, move.amount_residual_signed, move.amount_total_signed, move.partner_id, move.invoice_user_id, move.fiscal_position_id, move.invoice_payment_state, move.invoice_date, move.invoice_date_due, move.invoice_payment_term_id, move.invoice_partner_bank_id, uom_template.id, uom_line.factor, template.categ_id, (COALESCE(partner.country_id, commercial_partner.country_id)), move.team_id;
STORE Movimientos INTO [lib://DataFiles/TransVtasOdooAdlerC.qvd] (qvd);
Exit Script;
Drop Tables;
DISCONNECT;
