Skip to content

Instantly share code, notes, and snippets.

@ssaid

ssaid/retention_query.sql

Last active March 19, 2024 13:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ssaid/abeff1c03edc89e00606 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ssaid/abeff1c03edc89e00606 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ssaid revised this gist Mar 19, 2024. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion retention_query.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    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
    @@ -47,4 +48,4 @@ 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
    Query para obtener
  2. ssaid created this gist Nov 3, 2015.
    50 changes: 50 additions & 0 deletions retention_query.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,50 @@
    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