retention_query.sql

hola
Retenciones automaticas que no tienen tax_app_id
================================================
test_arg=> SELECT rtl.name, r.type, round(((round(rtl.amount,2)/round(rtl.base,2))*100),2) as percentage
FROM retention_tax_line rtl
JOIN retention_retention r
ON rtl.retention_id=r.id
JOIN account_tax at
ON r.tax_id=at.id
WHERE rtl.manual=false /* Solo automaticas */
AND rtl.tax_app_id IS null /* Sin tax.application */
AND at.type_tax_use LIKE 'purchase' /* Retenciones efectuadas */
ORDER BY percentage;


Seleccion de retenciones efectuadas automaticas de IIBB que no tienen tax_app_id
========================================================================
test_arg=> SELECT rtl.id, rtl.name FROM retention_tax_line rtl
JOIN retention_retention ret
ON rtl.retention_id=ret.id
JOIN account_tax at
ON ret.tax_id=at.id
WHERE ret.name ILIKE '%IIBB%' /* Tipo Ingresos brutos */
AND at.type_tax_use='purchase' /* Efectuadas */
AND rtl.manual=false /* Automaticas */
AND rtl.tax_app_id is null /* Sin tax aplication */;

Obtener tax aplications disponibles para una retencion (En este caso IIBB)
======================================================
test_arg=> SELECT ret.name ret_name, rta.name tax_app_name, rta.id tax_app_id
FROM retention_retention ret
JOIN retention_tax_application rta 
ON rta.retention_id=ret.id
WHERE ret.name ILIKE '%IIBB%'; 

Update para setear una tax_app_id en la RTL
===========================================
test_arg=> UPDATE retention_tax_line
SET tax_app_id=9 /* Seguro que este es el tax_app_id a setear? Hizo query previa? */
WHERE id IN
(SELECT rtl.id FROM retention_tax_line rtl
JOIN retention_retention ret
ON rtl.retention_id=ret.id
JOIN account_tax at
ON ret.tax_id=at.id
WHERE ret.name ILIKE '%IIBB%' /* Tipo Ingresos brutos */
AND at.type_tax_use='purchase' /* Efectuadas */
AND rtl.manual=false /* Automaticas */
AND rtl.tax_app_id is null /* Sin tax aplication */);

Query para obtener