16/08/2021 03:51:03 ***** ERROR DE SISTEMA: ***** ADMINISTRADOR ***** CLASE: sigesp_cxp_c_ncnd
METODO: ConsultaRetencionesNCD
ERROR->
Invalid query: ERROR: column reference "codemp" is ambiguous
LINE 15: WHERE codemp='0001'
^
Whole query:
SELECT rdd.*,scg.denominacion,ded.*
FROM cxp_sol_dc dc
INNER JOIN cxp_dc_retenciones rdd ON rdd.codemp=dc.codemp
AND rdd.numsol=dc.numsol
AND rdd.numrecdoc=dc.numrecdoc
AND rdd.codtipdoc=dc.codtipdoc
AND rdd.ced_bene=dc.ced_bene
AND rdd.cod_pro=dc.cod_pro
AND rdd.codope=dc.codope
AND rdd.numdc=dc.numdc
INNER JOIN sigesp_deducciones ded ON ded.codemp=rdd.codemp
AND ded.codded=rdd.codded
LEFT JOIN scg_cuentas scg ON scg.sc_cuenta = ded.sc_cuenta
WHERE codemp='0001'
AND numsol='CXPTB-000000975'
AND numrecdoc='0304'
AND codtipdoc='00002'
AND ced_bene='----------'
AND cod_pro='0000000051'
AND codope='NC'
AND numdc='222'
16/08/2021 19:21:29 ***** ERROR DE SISTEMA: ***** ADMINISTRADOR ***** CLASE->sigesp_cxp_c_cmp_retencion MÉTODO->BuscarRecepciones ERROR->Invalid query: ERROR: relation "cxp_dc_retenciones" does not exist
LINE 228: INNER JOIN cxp_dc_retenciones rdd ON rdd.codemp=dc.c...
^
Whole query: SELECT tipproben,
cod_pro,
ced_bene,
nompro,
rifpro,
numsol,
estprosol,
tipsolpag,
desc_estaprosol,
desc_estprosol,
numrecdoc,
dencondoc,
codtipdoc,
estatus_rd,
estatus_aprord,
procede_doc,
numdoccom,
codded,
dended,
numdc,
(CASE WHEN codope='FAC' THEN numrecdoc ELSE facafec END) AS numfac,
(CASE WHEN codope='NC' THEN -mondeddoc ELSE mondeddoc END) AS mondeddoc,
(CASE WHEN codope='NC' THEN -moncardoc ELSE moncardoc END) AS moncardoc,
(CASE WHEN codope='NC' THEN -monobjret ELSE monobjret END) AS monobjret,
(CASE WHEN codope='NC' THEN -monret ELSE monret END) AS monret,
porded,
(CASE WHEN codope='NC' THEN -subtotbasimp ELSE subtotbasimp END) AS subtotbasimp,
(CASE WHEN codope='NC' THEN -moncar ELSE moncar END) AS moncar,
(CASE WHEN codope='NC' THEN -subtotret ELSE subtotret END) AS subtotret,
(CASE WHEN codope='NC' THEN -montotdoc ELSE montotdoc END) AS montotdoc,
codcar,
porcar,
formula,
fechaconta,
fecemisol,
fecaprosol,
fecemidoc,
fecregdoc,
fecaprord,
sc_cuenta,
numref,
codproalt,
nomproalt,
rifproalt,
iva,
numdc,
codope,
nrocontrol,
facafec
FROM
(
SELECT rd.tipproben,so.cod_pro,rd.ced_bene,pro.nompro,pro.rifpro,
so.numsol,so.estprosol,so.tipsolpag,e_estaprosol.descequiv AS desc_estaprosol,
e_estprosol.descequiv AS desc_estprosol,
rd.numrecdoc,rd.dencondoc,rdd.codtipdoc,
e_estprodoc.descequiv AS estatus_rd,
e_estaprord.descequiv AS estatus_aprord,
rdd.procede_doc, rdd.numdoccom,
rdd.codded,ded.dended,
'' AS numdc,
/*rd.montotdoc,*/
rd.mondeddoc,
rd.moncardoc,
rdd.monobjret,
rdd.monret,
ded.porded,
sum(rdc.monobjret) AS subtotbasimp,
sum(rdc.monret) AS moncar,
round((sum(rdc.monret)*(ded.porded/100))::numeric,2) as subtotret,
(rd.montotdoc + (
SELECT COALESCE(sum(montodesc),0) AS montodesc
FROM cxp_dt_anticipos dta
INNER JOIN cxp_anticipos a ON a.nroanticipo = dta.nroanticipo
AND a.tipproben = dta.tipproben
AND a.ced_bene = dta.ced_bene
AND a.cod_pro = dta.cod_pro
WHERE dta.tipproben=rd.tipproben
AND dta.cod_pro=rd.cod_pro
AND dta.ced_bene=rd.ced_bene
AND dta.numrecdocdes=rd.numrecdoc
AND dta.estdoc!='A'
) ) AS montotdoc,
MAX(rdc.codcar) AS codcar,
rdc.porcar,
rdc.formula,
so.fechaconta,
so.fecemisol,
so.fecaprosol,rd.fecemidoc,
rd.fecregdoc,rd.fecaprord,
rdd.sc_cuenta,rd.numref,
rd.codproalt,proalt.nompro AS nomproalt,proalt.rifpro AS rifproalt,ded.iva,
'FAC' AS codope,
'' AS nrocontrol,
'' AS facafec
FROM cxp_solicitudes so
INNER JOIN cxp_dt_solicitudes ds ON so.codemp=ds.codemp
AND so.numsol=ds.numsol
INNER JOIN cxp_rd rd ON rd.codemp=ds.codemp
AND rd.numrecdoc=ds.numrecdoc
AND rd.codtipdoc=ds.codtipdoc
AND rd.ced_bene=ds.ced_bene
AND rd.cod_pro=ds.cod_pro
INNER JOIN cxp_rd_deducciones rdd ON rdd.codemp=ds.codemp
AND rdd.numrecdoc=ds.numrecdoc
AND rdd.codtipdoc=ds.codtipdoc
AND rdd.ced_bene=ds.ced_bene
AND rdd.cod_pro=ds.cod_pro
INNER JOIN sigesp_deducciones ded ON ded.codemp=rdd.codemp
AND ded.codded=rdd.codded
INNER JOIN rpc_proveedor pro ON rd.cod_pro=pro.cod_pro
LEFT JOIN rpc_proveedor proalt ON rd.codproalt=proalt.cod_pro
LEFT JOIN cxp_rd_cargos rdc ON rdc.codemp=rd.codemp
AND rdc.numrecdoc=rd.numrecdoc
AND rdc.codtipdoc=rd.codtipdoc
AND rdc.ced_bene=rd.ced_bene
AND rdc.cod_pro=rd.cod_pro
AND ded.iva=1
/*AND rdd.monobjret = rdc.monret*/
LEFT JOIN covensol_sigesp_equiv e_estprosol ON e_estprosol.dominio='cxp_solicitudes'
AND e_estprosol.campo='estprosol'
AND e_estprosol.valor= so.estprosol::text
LEFT JOIN covensol_sigesp_equiv e_estaprosol ON e_estaprosol.dominio='cxp_solicitudes'
AND e_estaprosol.campo='estaprosol'
AND e_estaprosol.valor= so.estaprosol::text
LEFT JOIN covensol_sigesp_equiv e_estprodoc ON e_estprodoc.dominio='cxp_rd'
AND e_estprodoc.campo='estprodoc'
AND e_estprodoc.valor= rd.estprodoc::text
LEFT JOIN covensol_sigesp_equiv e_estaprord ON e_estaprord.dominio='cxp_rd'
AND e_estaprord.campo='estaprord'
AND e_estaprord.valor= rd.estaprord::text
WHERE so.codemp='0001'
AND so.estprosol NOT IN ('A','N')
AND rd.estprodoc='C'
AND rdd.estcmp='0'
AND (so.fecemisol BETWEEN '2021-08-01' AND '2021-08-31') AND (so.tipsolpag ='SOPA' OR so.tipsolpag ='FOAN' OR so.tipsolpag ='FOAV' OR so.tipsolpag ='LIQA') AND rd.tipproben='P'
GROUP BY rd.tipproben,so.cod_pro,rd.cod_pro,rd.ced_bene,pro.nompro,pro.rifpro,
so.numsol,so.estprosol,so.tipsolpag,e_estaprosol.descequiv,
e_estprosol.descequiv, rd.numrecdoc,
rd.dencondoc,rdd.codtipdoc, e_estprodoc.descequiv,
e_estaprord.descequiv, rdd.procede_doc, rdd.numdoccom,
rdd.codded,ded.dended,
rd.numrecdoc,
rd.montotdoc,
rd.mondeddoc,
rd.moncardoc,
rdd.monobjret,
rdd.monret,
ded.porded,
rdc.porcar, rdc.formula, so.fechaconta,so.fecemisol, so.fecaprosol,
rd.fecemidoc,rd.fecregdoc,rd.fecaprord, rdd.sc_cuenta,rd.numref, rd.codproalt,
proalt.nompro,proalt.rifpro,ded.iva
UNION
SELECT
rd.tipproben,
so.cod_pro,
dc.ced_bene,
pro.nompro,pro.rifpro,
so.numsol,
so.estprosol,
so.tipsolpag,
e_estaprosol.descequiv AS desc_estaprosol,
e_estprosol.descequiv AS desc_estprosol,
dc.numdc as numrecdoc,
dc.desope AS dencondoc,
rdd.codtipdoc,
e_estprodoc.descequiv AS estatus_rd,
e_estaprord.descequiv AS estatus_aprord,
rdd.procede_doc,
rdd.numdoccom,
rdd.codded,
ded.dended,
dc.numdc AS numdc,
(
SELECT sum(rdd.monret)
FROM cxp_dc_retenciones ret
WHERE ret.codemp=dc.codemp
AND ret.numsol=dc.numsol
AND ret.numrecdoc=dc.numrecdoc
AND ret.codtipdoc=dc.codtipdoc
AND ret.ced_bene=dc.ced_bene
AND ret.cod_pro=dc.cod_pro
AND ret.codope=dc.codope
AND ret.numdc=dc.numdc
) AS mondeddoc,
dc.moncar AS moncardoc,
rdd.monobjret,
rdd.monret,
ded.porded,
sum(dcc.monobjret) AS subtotbasimp,
sum(dcc.monret) AS moncar,
round((sum(dcc.monret)*(ded.porded/100))::numeric,2) as subtotret,
(dc.monto) AS montotdoc,
MAX(dcc.codcar) AS codcar,
dcc.porcar,
dcc.formula,
so.fechaconta,
so.fecemisol,
so.fecaprosol,
dc.fecdocncd as fecemidoc,
dc.fecope as fecregdoc,
dc.fecaprnc as fecaprord,
rdd.sc_cuenta,
dc.nrocontrol AS numref,
rd.codproalt,
proalt.nompro AS nomproalt,
proalt.rifpro AS rifproalt,
ded.iva,
dc.codope,
dc.nrocontrol,
rd.numrecdoc as facafec
FROM cxp_solicitudes so
INNER JOIN cxp_dt_solicitudes ds ON so.codemp=ds.codemp
AND so.numsol=ds.numsol
INNER JOIN cxp_rd rd ON rd.codemp=ds.codemp
AND rd.numrecdoc=ds.numrecdoc
AND rd.codtipdoc=ds.codtipdoc
AND rd.ced_bene=ds.ced_bene
AND rd.cod_pro=ds.cod_pro
INNER JOIN cxp_sol_dc dc ON dc.codemp=ds.codemp
AND dc.numrecdoc=ds.numrecdoc
AND dc.codtipdoc=ds.codtipdoc
AND dc.ced_bene=ds.ced_bene
AND dc.cod_pro=ds.cod_pro
AND dc.numsol=ds.numsol
INNER JOIN cxp_dc_retenciones rdd ON rdd.codemp=dc.codemp
AND rdd.numsol=dc.numsol
AND rdd.numrecdoc=dc.numrecdoc
AND rdd.codtipdoc=dc.codtipdoc
AND rdd.ced_bene=dc.ced_bene
AND rdd.cod_pro=dc.cod_pro
AND rdd.codope=dc.codope
AND rdd.numdc=dc.numdc
INNER JOIN sigesp_deducciones ded ON ded.codemp=rdd.codemp
AND ded.codded=rdd.codded
INNER JOIN rpc_proveedor pro ON rd.cod_pro=pro.cod_pro
LEFT JOIN rpc_proveedor proalt ON rd.codproalt=proalt.cod_pro
LEFT JOIN cxp_dc_cargos dcc ON dcc.codemp=dc.codemp
AND dcc.numsol=dc.numsol
AND dcc.numrecdoc=dc.numrecdoc
AND dcc.codtipdoc=dc.codtipdoc
AND dcc.ced_bene=dc.ced_bene
AND dcc.cod_pro=dc.cod_pro
AND dcc.codope=dc.codope
AND dcc.numdc=dc.numdc
AND ded.iva=1
LEFT JOIN covensol_sigesp_equiv e_estprosol ON e_estprosol.dominio='cxp_solicitudes'
AND e_estprosol.campo='estprosol'
AND e_estprosol.valor= so.estprosol::text
LEFT JOIN covensol_sigesp_equiv e_estaprosol ON e_estaprosol.dominio='cxp_solicitudes'
AND e_estaprosol.campo='estaprosol'
AND e_estaprosol.valor= so.estaprosol::text
LEFT JOIN covensol_sigesp_equiv e_estprodoc ON e_estprodoc.dominio='cxp_rd'
AND e_estprodoc.campo='estprodoc'
AND e_estprodoc.valor= rd.estprodoc::text
LEFT JOIN covensol_sigesp_equiv e_estaprord ON e_estaprord.dominio='cxp_rd'
AND e_estaprord.campo='estaprord'
AND e_estaprord.valor= rd.estaprord::text
WHERE so.codemp='0001'
AND so.estprosol NOT IN ('A','N')
AND rd.estprodoc='C'
AND dc.estnotadc='C'
AND rdd.estcmpdc='0'
AND (so.fecemisol BETWEEN '2021-08-01' AND '2021-08-31') AND (so.tipsolpag ='SOPA' OR so.tipsolpag ='FOAN' OR so.tipsolpag ='FOAV' OR so.tipsolpag ='LIQA') AND rd.tipproben='P'
GROUP BY rd.tipproben,so.cod_pro,dc.cod_pro,dc.ced_bene,pro.nompro,pro.rifpro,
so.numsol,so.estprosol,so.tipsolpag,e_estaprosol.descequiv,
e_estprosol.descequiv,dc.codope, dc.numdc,
dc.desope,rdd.codtipdoc, e_estprodoc.descequiv,
e_estaprord.descequiv, rdd.procede_doc, rdd.numdoccom,
rdd.codded,ded.dended,
rd.numrecdoc,
dc.monto,
dc.moncar,
rdd.monobjret,
rdd.monret,
ded.porded,
dcc.porcar, dcc.formula, so.fechaconta,so.fecemisol, so.fecaprosol,
dc.fecdocncd,dc.fecope,dc.fecaprnc, rdd.sc_cuenta,rd.numref, rd.codproalt,
proalt.nompro,proalt.rifpro,ded.iva,dc.codemp,dc.numsol,dc.numrecdoc,dc.codtipdoc,dc.ced_bene,dc.cod_pro
ORDER BY 2,3,6,11
) AS ret