我正在Oracle 12c中开发一个函数,该函数从多个表中获取数据并返回一个标题行,并在每个标题之后返回一个与之相关的值的列表。更好地解释一下,我有几个地方或城市,其中有用电者,根据用电量和用电量(以千瓦时为单位),按类别将其分类。该函数必须返回标题行中的每个城镇(加上标题),然后返回该城镇每个类别的分组;以此类推。我以为我可以用两(2)个游标解决这个问题,一个游标按位置分组,另一个游标在第一个游标内部按类别分组。当我编译时,没有错误出现,但是在执行时,Oracle警告我该错误:
ORA-06502:PL / SQL:错误:数字或值字符串缓冲区太小
ORA-06512:在“ CEMDO_DB.XXLOCALIDADPORCATEGORIA1”中,第87行
ORA-06512:在“ CEMDO_DB.XXLOCALIDADPORCATEGORIA1”中,第87行
- 00000-“ PL / SQL:数字或值错误%s”
- 原因:发生算术,数字,字符串,转换或约束错误。例如,如果尝试将值NULL分配给声明为NOT NULL的变量,或者试图将大于99的整数分配给声明为NUMBER(2)的变量,则会发生此错误。
- 行动:更改数据,如何操纵数据或如何声明数据,以使值不违反约束。*
它并不能弄清原因,因为我在值类型或可变容量方面没有问题。我在错误行上唯一拥有的是PIPE ROW。在该函数的调试中,错误消息如下:
ORA-06550:第10行,第3列:
PL / SQL:ORA-00904:“ XXLXC1_ROW”:无效的标识符
ORA-06550:第9行,第5列:
PL / SQL:忽略SQL语句
在这里,错误已明确显示,但我找不到解决方法,因为已声明PIPE ROW xxLxC1类型,并且该类型存在于架构对象中。
抱歉给您带来不便,但这是我为Oracle编写的第三个功能,因为我已经与Informix合作了20年。
从现在开始,我感谢您的关注。
我复制该函数的代码:
CREATE OR REPLACE FUNCTION xxLocalidadPorCategoria1( pAnio NUMBER, pNroPer NUMBER ) RETURN xxLxC1_tab PIPELINED IS
CURSOR curLoc IS
SELECT cc.IdCategoria, cat.Descripcion, f.IdLocalidad, l.Descripcion Localidad
FROM Cbtes_Coop cc, Cbtes_Cptos ccp, Suministros s, SubFincas sf, Fincas f, Categorias cat,
Localidades l -----, OUTER( Sim_Cbtes sc, OUTER( Categorias cat, Suministros s, Localidades l ))
WHERE cc.Anio = pAnio
AND cc.NroPer = pNroPer
AND cc.Estado != 'X'
AND cc.IdTipo_Srv = 1
----AND cc.IdSuministro = 1078 ----Agregada para control
AND cc.IdEmpresa = ccp.IdEmpresa
AND cc.IdSucursal = ccp.IdSucursal
AND cc.Tipo_Cbte = ccp.Tipo_Cbte
AND cc.Grupo_Cbte = ccp.Grupo_Cbte
AND cc.Letra_Cbte = ccp.Letra_Cbte
AND cc.NroCbte = ccp.NroCbte
AND ccp.IdConcepto IN ( SELECT IdConcepto FROM Conceptos WHERE IdTipo_Srv = 1 )
AND NOT EXISTS ( SELECT IdEmpresa
FROM Cbtes_Coop
WHERE IdCbte_Padre = cc.IdCbte
AND IdSucursal IN ( 4, 47, 48 )
AND Tipo_Cbte = 'NC'
AND IdTipo_ModoFac > 0
AND Estado != 'X'
AND ABS( Totalimp ) = ABS( cc.Totalimp ))
AND cc.IdSuministro = s.IdSuministro
AND s.IdSubFinca = sf.IdSubFinca
AND sf.IdFinca = f.IdFinca
AND f.IdLocalidad = l.IdLocalidad
AND cc.IdTipo_Srv = cat.IdTipo_Srv
AND cc.IdCategoria = cat.IdCategoria
GROUP BY cc.IdCategoria, cat.Descripcion, f.IdLocalidad, l.Descripcion
ORDER BY 1;
CURSOR curTemp (pIdLocalidad NUMBER) IS
SELECT cc.IdCategoria, cat.Descripcion, f.IdLocalidad, l.Descripcion Localidad, SUM( CASE WHEN
ccp.IdConcepto = 5 THEN 1 END ) Cargo_Fijo, SUM( CASE WHEN ccp.IdConcepto = 5 THEN ccp.Importe
END ) Importe_C_Fijo, SUM( CASE WHEN ccp.IdConcepto = 10 THEN ROUND( ccp.Unidades, 2 ) END ) kWh,
SUM( CASE WHEN ccp.IdConcepto = 10 THEN ccp.Importe END ) Importe_Consumo, SUM( CASE WHEN
ccp.IdConcepto IN( 35, 38 ) THEN 1 END) Cargo_Fijo_GC, SUM( CASE WHEN ccp.IdConcepto IN( 35, 38 )
THEN ccp.Importe END ) Imp_C_Fijo_GC, SUM( CASE WHEN ccp.IdConcepto IN( 30, 31, 32 ) THEN ROUND(
ccp.Unidades, 2 ) END ) Energia_GC, SUM( CASE WHEN ccp.IdConcepto IN( 30, 31, 32 ) THEN
ccp.Importe END ) Importe_GC, SUM( CASE WHEN ccp.IdConcepto IN( 40, 41 ) THEN ROUND(
ccp.Unidades, 2 ) END ) Unidades_Demanda, SUM( CASE WHEN ccp.IdConcepto IN( 40, 41 ) THEN
ccp.Importe END ) Importe_Demanda, SUM( CASE WHEN ccp.IdConcepto IN( 101, 109 ) THEN 1 END )
Cant_BEN, SUM( CASE WHEN ccp.IdConcepto IN( 101, 109 ) THEN ccp.Importe END ) BEN, SUM( CASE WHEN
ccp.IdConcepto = 62 AND ccp.Importe != 0 THEN 1 END ) Ajuste_Coseno, SUM(CASE WHEN ccp.IdConcepto
= 62 AND ccp.Importe != 0 THEN ccp.Importe END ) Imp_Ajuste_Coseno, SUM(CASE WHEN ccp.IdConcepto
IN ( 25, 26, 27, 29, 291,292, 293, 294, 295 ) THEN 1 END )
Cant_Cargos_Ersep, SUM( CASE WHEN ccp.IdConcepto IN ( 25, 26, 27, 29, 291,292, 293, 294, 295 )
THEN ccp.Importe END ) Imp_Cargos_Ersep, SUM( CASE WHEN ccp.IdConcepto IN ( 20, 21, 23, 24, 64,
100, 432, 433, 434 ) THEN 1 END ) Cant_Ajustes_Ersep, SUM( CASE WHEN ccp.IdConcepto IN ( 20, 21,
23, 24, 64, 100, 432, 433, 434 ) THEN ccp.Importe END ) Imp_Ajustes_Ersep, SUM( CASE WHEN
ccp.IdConcepto IN( 28, 97, 98 ) AND ccp.IdSubConcepto IN( 0,1,2,3 ) THEN 1 END ) Unid_Bonif_Exc,
SUM( CASE WHEN ccp.IdConcepto IN( 28, 97, 98 ) AND ccp.IdSubConcepto IN( 0,1,2,3 ) THEN
ccp.Importe END ) Imp_Bonif_Exc, SUM( CASE WHEN ccp.IdConcepto IN( 438, 439 ) AND
ccp.IdSubConcepto = 0 THEN 1 END ) Unid_Reajustes, SUM( CASE WHEN
ccp.IdConcepto IN( 438, 439 ) AND ccp.IdSubConcepto = 0 THEN ccp.Importe END ) Imp_Reajustes, SUM(
CASE WHEN ccp.IdConcepto = 58 AND ccp.IdSubConcepto = 0 THEN 1 END ) Unid_Coie, SUM( CASE WHEN
ccp.IdConcepto = 58 AND ccp.IdSubConcepto = 0 THEN ccp.Importe END ) Imp_Coie, SUM( CASE WHEN
ccp.IdConcepto = 296 AND ccp.IdSubConcepto = 0 THEN 1 END ) Unid_Coie_Dem, SUM( CASE WHEN
ccp.IdConcepto = 296 AND ccp.IdSubConcepto = 0 THEN ccp.Importe END ) Imp_Coie_Dem, SUM( CASE WHEN
ccp.IdConcepto = 103 AND ccp.Importe < 0 THEN 1 END ) Unid_TRA1, SUM( CASE WHEN ccp.IdConcepto =
103 AND ccp.Importe < 0 THEN ccp.Importe END ) Imp_TRA1, SUM( CASE WHEN ccp.IdConcepto = 104 AND
ccp.Importe < 0 THEN 1 END ) Unid_TRA2, SUM( CASE WHEN ccp.IdConcepto = 104 AND ccp.Importe < 0
THEN ccp.Importe END ) Imp_TRA2, SUM( CASE WHEN ccp.IdConcepto = 102 AND ccp.Importe < 0 THEN 1
END ) Unid_TSCA, SUM( CASE WHEN ccp.IdConcepto = 102 AND ccp.Importe < 0 THEN ccp.Importe END )
Imp_TSCA, SUM( CASE WHEN ccp.IdConcepto = 105 AND ccp.Importe < 0 THEN 1 END ) Unid_TSSA, SUM(
CASE WHEN ccp.IdConcepto = 105 AND ccp.Importe < 0 THEN ccp.Importe END ) Imp_TSSA, SUM( CASE WHEN
ccp.IdConcepto = 443 AND ccp.Importe < 0 THEN 1 END ) UnidBonifCemdo, SUM( CASE WHEN
ccp.IdConcepto = 443 AND ccp.Importe < 0 THEN ccp.Importe END ) ImpBonifCemdo, SUM( CASE WHEN
ccp.IdConcepto = 121 AND ccp.Importe < 0 THEN 1 END ) UnidTSPC, SUM( CASE WHEN ccp.IdConcepto =
121 AND ccp.Importe < 0 THEN ccp.Importe END ) ImpTSPC, SUM( CASE WHEN ccp.IdConcepto = 122 AND
ccp.Importe < 0 THEN 1 END ) UnidTSPI, SUM( CASE WHEN ccp.IdConcepto = 122 AND ccp.Importe < 0
THEN ccp.Importe END ) ImpTSPI
FROM Cbtes_Coop cc, Cbtes_Cptos ccp, Suministros s, SubFincas sf, Fincas f, Categorias cat,
Localidades l
WHERE cc.Anio = pAnio
AND cc.NroPer = pNroPer
AND cc.Estado != 'X'
AND cc.IdTipo_Srv = 1
----AND cc.IdSuministro = 1078 ----Agregada para control
AND cc.IdEmpresa = ccp.IdEmpresa
AND cc.IdSucursal = ccp.IdSucursal
AND cc.Tipo_Cbte = ccp.Tipo_Cbte
AND cc.Grupo_Cbte = ccp.Grupo_Cbte
AND cc.Letra_Cbte = ccp.Letra_Cbte
AND cc.NroCbte = ccp.NroCbte
AND ccp.IdConcepto IN ( SELECT IdConcepto FROM Conceptos WHERE IdTipo_Srv = 1 )
AND NOT EXISTS ( SELECT IdEmpresa
FROM Cbtes_Coop
WHERE IdCbte_Padre = cc.IdCbte
AND IdSucursal IN ( 4, 47, 48 )
AND Tipo_Cbte = 'NC'
AND IdTipo_ModoFac > 0
AND Estado != 'X'
AND ABS( Totalimp ) = ABS( cc.Totalimp ))
AND cc.IdSuministro = s.IdSuministro
AND s.IdSubFinca = sf.IdSubFinca
AND sf.IdFinca = f.IdFinca
AND f.IdLocalidad = l.IdLocalidad
AND cc.IdTipo_Srv = cat.IdTipo_Srv
AND cc.IdCategoria = cat.IdCategoria
AND f.IdLocalidad = pIdLocalidad
GROUP BY f.IdLocalidad, l.Descripcion, cc.IdCategoria, cat.Descripcion
/*GROUPING SETS((f.IdLocalidad, l.Descripcion, cc.IdCategoria, cat.Descripcion))*/
ORDER BY f.IdLocalidad, cc.IdCategoria;
BEGIN
FOR regLoc IN curLoc LOOP
PIPE ROW (xxLxC1_row( TO_CHAR(regLoc.IdLocalidad) || '-' || regLoc.Localidad, 'Cargo Fijo',
'Imp. Cargo Fijo', 'Consumo', 'Imp. Consumo', 'C. Fijo GC', 'Imp. C. Fijo GC', 'Consumo GC', 'Imp.
Consumo GC', 'Unid. Demanda', 'Imp. Demanda', 'Bonificaciones', 'Imp. Bonificaciones', 'Coseno PHI',
'Imp. Coseno PHI', 'Cargos ERSeP', 'Imp. Cargos ERSeP',
'Ajustes ERSeP', 'Imp. Ajustes ERSeP', 'Unid. Bonif. Exc', 'Imp. Bonif. Exc.', 'Unid. Reajustes',
'Imp. Reajustes', 'Unid. COIE', 'Imp. COIE', 'Unid. COIE Dem.', 'Imp. COIE Dem', 'Unid. TRA1', 'Imp.
TRA1', 'Unid. TRA2', 'Imp. TRA2', 'Unid. TSCA', 'Imp. TSCA', 'Unid. TSSA', 'Imp. TSSA', 'Unid. Bonif.
Cemdo', 'Imp. Bonif. Cemdo', 'Unid. TSPC', 'Imp. TSPC', 'Unid. TSPI', 'Imp. TSPI'));
FOR regTemp IN curTemp(regLoc.IdLocalidad) LOOP
DBMS_OUTPUT.PUT_LINE(regLoc.IdLocalidad);
PIPE ROW (xxLxC1_row(regTemp.IdCategoria || '-' || regTemp.Descripcion, regTemp.Cargo_Fijo,
regTemp.Importe_C_Fijo, regTemp.kWh, regTemp.Importe_Consumo, regTemp.Cargo_Fijo_GC,
regTemp.Imp_C_Fijo_GC, regTemp.Energia_GC, regTemp.Importe_GC, regTemp.Unidades_Demanda,
regTemp.Importe_Demanda, regTemp.Cant_BEN, regTemp.BEN, regTemp.Ajuste_Coseno,
regTemp.Imp_Ajuste_Coseno, regTemp.Cant_Cargos_Ersep, regTemp.Imp_Cargos_Ersep,
regTemp.Cant_Ajustes_Ersep, regTemp.Imp_Ajustes_Ersep, regTemp.Unid_Bonif_Exc,
regTemp.Imp_Bonif_Exc, regTemp.Unid_Reajustes, regTemp.Imp_Reajustes, regTemp.Unid_Coie,
regTemp.Imp_Coie, regTemp.Unid_Coie_Dem, regTemp.Imp_Coie_Dem, regTemp.Unid_TRA1,
regTemp.Imp_TRA1, regTemp.Unid_TRA2, regTemp.Imp_TRA2, regTemp.Unid_TSCA, regTemp.Imp_TSCA,
regTemp.Unid_TSSA, regTemp.Imp_TSSA, regTemp.UnidBonifCemdo, regTemp.ImpBonifCemdo,
regTemp.UnidTSPC, regTemp.ImpTSPC, regTemp.UnidTSPI, regTemp.ImpTSPI));
END LOOP;
END LOOP;
END;
/
以及PIPE ROW和VIRTUAL TABLE的代码:
CREATE OR REPLACE TYPE xxLxC1_row AS OBJECT (c1 Varchar2(50), c2 Varchar2(20), c3 Varchar2(20), c4 Varchar2(20), c5 Varchar2(20), c6 Varchar2(20), c7 Varchar2(20), c8 Varchar2(20), c9 Varchar2(20), c10 Varchar2(20), c11 Varchar2(20), c12 Varchar2(20), c13 Varchar2(20), c14 Varchar2(20), c15 Varchar2(20), c16 Varchar2(20), c17 Varchar2(20), c18 Varchar2(20), c19 Varchar2(20), c20 Varchar2(20), c21 Varchar2(20), c22 Varchar2(20), c23 Varchar2(20), c24 Varchar2(20), c25 Varchar2(20), c26 Varchar2(20), c27 Varchar2(20), c28 Varchar2(20), c29 Varchar2(20), c30 Varchar2(20), c31 Varchar2(20), c32 Varchar2(20), c33 Varchar2(20), c34 Varchar2(20), c35 Varchar2(20), c36 Varchar2(20), c37 Varchar2(20), c38 Varchar2(20), c39 Varchar2(20), c40 Varchar2(20), c41 Varchar2(20));
/
CREATE OR REPLACE TYPE xxLxC1_tab AS TABLE OF xxLxC1_row;
/
对象类型的每个元素都是VARCHAR2(20),因此要传递的值之一很可能大于20个字节长。
对于第一个管道行,假设您要传递的所有值都是常量,并且这些常量小于20个字符,则唯一的错误候选项是
TO_CHAR(regLoc.IdLocalidad) || '-' || regLoc.Localidad
超过20个字符。但是,对于第二个管道行,这些值中的每个值都是从游标提取出来的,因此这些值中的任何一个都可能是罪魁祸首。
循环访问数据的一种简单方法是稍微更改一下功能,使其暂时成为非流水线功能,然后循环访问数据并执行对象分配进行检查,即
CREATE OR REPLACE FUNCTION xxLocalidadPorCategoria1( pAnio NUMBER, pNroPer NUMBER ) RETURN number IS
my_row xxLxC1_row;
CURSOR curLoc IS
SELECT cc.IdCategoria, cat.Descripcion, f.IdLocalidad, l.Descripcion Localidad
FROM Cbtes_Coop cc, Cbtes_Cptos ccp, Suministros s, SubFincas sf, Fincas f, Categorias cat,
Localidades l -----, OUTER( Sim_Cbtes sc, OUTER( Categorias cat, Suministros s, Localidades l ))
WHERE cc.Anio = pAnio
AND cc.NroPer = pNroPer
AND cc.Estado != 'X'
AND cc.IdTipo_Srv = 1
----AND cc.IdSuministro = 1078 ----Agregada para control
AND cc.IdEmpresa = ccp.IdEmpresa
AND cc.IdSucursal = ccp.IdSucursal
AND cc.Tipo_Cbte = ccp.Tipo_Cbte
AND cc.Grupo_Cbte = ccp.Grupo_Cbte
AND cc.Letra_Cbte = ccp.Letra_Cbte
AND cc.NroCbte = ccp.NroCbte
AND ccp.IdConcepto IN ( SELECT IdConcepto FROM Conceptos WHERE IdTipo_Srv = 1 )
AND NOT EXISTS ( SELECT IdEmpresa
FROM Cbtes_Coop
WHERE IdCbte_Padre = cc.IdCbte
AND IdSucursal IN ( 4, 47, 48 )
AND Tipo_Cbte = 'NC'
AND IdTipo_ModoFac > 0
AND Estado != 'X'
AND ABS( Totalimp ) = ABS( cc.Totalimp ))
AND cc.IdSuministro = s.IdSuministro
AND s.IdSubFinca = sf.IdSubFinca
AND sf.IdFinca = f.IdFinca
AND f.IdLocalidad = l.IdLocalidad
AND cc.IdTipo_Srv = cat.IdTipo_Srv
AND cc.IdCategoria = cat.IdCategoria
GROUP BY cc.IdCategoria, cat.Descripcion, f.IdLocalidad, l.Descripcion
ORDER BY 1;
CURSOR curTemp (pIdLocalidad NUMBER) IS
SELECT cc.IdCategoria, cat.Descripcion, f.IdLocalidad, l.Descripcion Localidad, SUM( CASE WHEN
ccp.IdConcepto = 5 THEN 1 END ) Cargo_Fijo, SUM( CASE WHEN ccp.IdConcepto = 5 THEN ccp.Importe
END ) Importe_C_Fijo, SUM( CASE WHEN ccp.IdConcepto = 10 THEN ROUND( ccp.Unidades, 2 ) END ) kWh,
SUM( CASE WHEN ccp.IdConcepto = 10 THEN ccp.Importe END ) Importe_Consumo, SUM( CASE WHEN
ccp.IdConcepto IN( 35, 38 ) THEN 1 END) Cargo_Fijo_GC, SUM( CASE WHEN ccp.IdConcepto IN( 35, 38 )
THEN ccp.Importe END ) Imp_C_Fijo_GC, SUM( CASE WHEN ccp.IdConcepto IN( 30, 31, 32 ) THEN ROUND(
ccp.Unidades, 2 ) END ) Energia_GC, SUM( CASE WHEN ccp.IdConcepto IN( 30, 31, 32 ) THEN
ccp.Importe END ) Importe_GC, SUM( CASE WHEN ccp.IdConcepto IN( 40, 41 ) THEN ROUND(
ccp.Unidades, 2 ) END ) Unidades_Demanda, SUM( CASE WHEN ccp.IdConcepto IN( 40, 41 ) THEN
ccp.Importe END ) Importe_Demanda, SUM( CASE WHEN ccp.IdConcepto IN( 101, 109 ) THEN 1 END )
Cant_BEN, SUM( CASE WHEN ccp.IdConcepto IN( 101, 109 ) THEN ccp.Importe END ) BEN, SUM( CASE WHEN
ccp.IdConcepto = 62 AND ccp.Importe != 0 THEN 1 END ) Ajuste_Coseno, SUM(CASE WHEN ccp.IdConcepto
= 62 AND ccp.Importe != 0 THEN ccp.Importe END ) Imp_Ajuste_Coseno, SUM(CASE WHEN ccp.IdConcepto
IN ( 25, 26, 27, 29, 291,292, 293, 294, 295 ) THEN 1 END )
Cant_Cargos_Ersep, SUM( CASE WHEN ccp.IdConcepto IN ( 25, 26, 27, 29, 291,292, 293, 294, 295 )
THEN ccp.Importe END ) Imp_Cargos_Ersep, SUM( CASE WHEN ccp.IdConcepto IN ( 20, 21, 23, 24, 64,
100, 432, 433, 434 ) THEN 1 END ) Cant_Ajustes_Ersep, SUM( CASE WHEN ccp.IdConcepto IN ( 20, 21,
23, 24, 64, 100, 432, 433, 434 ) THEN ccp.Importe END ) Imp_Ajustes_Ersep, SUM( CASE WHEN
ccp.IdConcepto IN( 28, 97, 98 ) AND ccp.IdSubConcepto IN( 0,1,2,3 ) THEN 1 END ) Unid_Bonif_Exc,
SUM( CASE WHEN ccp.IdConcepto IN( 28, 97, 98 ) AND ccp.IdSubConcepto IN( 0,1,2,3 ) THEN
ccp.Importe END ) Imp_Bonif_Exc, SUM( CASE WHEN ccp.IdConcepto IN( 438, 439 ) AND
ccp.IdSubConcepto = 0 THEN 1 END ) Unid_Reajustes, SUM( CASE WHEN
ccp.IdConcepto IN( 438, 439 ) AND ccp.IdSubConcepto = 0 THEN ccp.Importe END ) Imp_Reajustes, SUM(
CASE WHEN ccp.IdConcepto = 58 AND ccp.IdSubConcepto = 0 THEN 1 END ) Unid_Coie, SUM( CASE WHEN
ccp.IdConcepto = 58 AND ccp.IdSubConcepto = 0 THEN ccp.Importe END ) Imp_Coie, SUM( CASE WHEN
ccp.IdConcepto = 296 AND ccp.IdSubConcepto = 0 THEN 1 END ) Unid_Coie_Dem, SUM( CASE WHEN
ccp.IdConcepto = 296 AND ccp.IdSubConcepto = 0 THEN ccp.Importe END ) Imp_Coie_Dem, SUM( CASE WHEN
ccp.IdConcepto = 103 AND ccp.Importe < 0 THEN 1 END ) Unid_TRA1, SUM( CASE WHEN ccp.IdConcepto =
103 AND ccp.Importe < 0 THEN ccp.Importe END ) Imp_TRA1, SUM( CASE WHEN ccp.IdConcepto = 104 AND
ccp.Importe < 0 THEN 1 END ) Unid_TRA2, SUM( CASE WHEN ccp.IdConcepto = 104 AND ccp.Importe < 0
THEN ccp.Importe END ) Imp_TRA2, SUM( CASE WHEN ccp.IdConcepto = 102 AND ccp.Importe < 0 THEN 1
END ) Unid_TSCA, SUM( CASE WHEN ccp.IdConcepto = 102 AND ccp.Importe < 0 THEN ccp.Importe END )
Imp_TSCA, SUM( CASE WHEN ccp.IdConcepto = 105 AND ccp.Importe < 0 THEN 1 END ) Unid_TSSA, SUM(
CASE WHEN ccp.IdConcepto = 105 AND ccp.Importe < 0 THEN ccp.Importe END ) Imp_TSSA, SUM( CASE WHEN
ccp.IdConcepto = 443 AND ccp.Importe < 0 THEN 1 END ) UnidBonifCemdo, SUM( CASE WHEN
ccp.IdConcepto = 443 AND ccp.Importe < 0 THEN ccp.Importe END ) ImpBonifCemdo, SUM( CASE WHEN
ccp.IdConcepto = 121 AND ccp.Importe < 0 THEN 1 END ) UnidTSPC, SUM( CASE WHEN ccp.IdConcepto =
121 AND ccp.Importe < 0 THEN ccp.Importe END ) ImpTSPC, SUM( CASE WHEN ccp.IdConcepto = 122 AND
ccp.Importe < 0 THEN 1 END ) UnidTSPI, SUM( CASE WHEN ccp.IdConcepto = 122 AND ccp.Importe < 0
THEN ccp.Importe END ) ImpTSPI
FROM Cbtes_Coop cc, Cbtes_Cptos ccp, Suministros s, SubFincas sf, Fincas f, Categorias cat,
Localidades l
WHERE cc.Anio = pAnio
AND cc.NroPer = pNroPer
AND cc.Estado != 'X'
AND cc.IdTipo_Srv = 1
----AND cc.IdSuministro = 1078 ----Agregada para control
AND cc.IdEmpresa = ccp.IdEmpresa
AND cc.IdSucursal = ccp.IdSucursal
AND cc.Tipo_Cbte = ccp.Tipo_Cbte
AND cc.Grupo_Cbte = ccp.Grupo_Cbte
AND cc.Letra_Cbte = ccp.Letra_Cbte
AND cc.NroCbte = ccp.NroCbte
AND ccp.IdConcepto IN ( SELECT IdConcepto FROM Conceptos WHERE IdTipo_Srv = 1 )
AND NOT EXISTS ( SELECT IdEmpresa
FROM Cbtes_Coop
WHERE IdCbte_Padre = cc.IdCbte
AND IdSucursal IN ( 4, 47, 48 )
AND Tipo_Cbte = 'NC'
AND IdTipo_ModoFac > 0
AND Estado != 'X'
AND ABS( Totalimp ) = ABS( cc.Totalimp ))
AND cc.IdSuministro = s.IdSuministro
AND s.IdSubFinca = sf.IdSubFinca
AND sf.IdFinca = f.IdFinca
AND f.IdLocalidad = l.IdLocalidad
AND cc.IdTipo_Srv = cat.IdTipo_Srv
AND cc.IdCategoria = cat.IdCategoria
AND f.IdLocalidad = pIdLocalidad
GROUP BY f.IdLocalidad, l.Descripcion, cc.IdCategoria, cat.Descripcion
/*GROUPING SETS((f.IdLocalidad, l.Descripcion, cc.IdCategoria, cat.Descripcion))*/
ORDER BY f.IdLocalidad, cc.IdCategoria;
BEGIN
FOR regLoc IN curLoc LOOP
--
-- now we make sure this assignment works ok
--
DBMS_OUTPUT.PUT_LINE(TO_CHAR(regLoc.IdLocalidad));
my_row := xxLxC1_row( TO_CHAR(regLoc.IdLocalidad) || '-' || regLoc.Localidad, 'Cargo Fijo',
'Imp. Cargo Fijo', 'Consumo', 'Imp. Consumo', 'C. Fijo GC', 'Imp. C. Fijo GC', 'Consumo GC', 'Imp.
Consumo GC', 'Unid. Demanda', 'Imp. Demanda', 'Bonificaciones', 'Imp. Bonificaciones', 'Coseno PHI',
'Imp. Coseno PHI', 'Cargos ERSeP', 'Imp. Cargos ERSeP',
'Ajustes ERSeP', 'Imp. Ajustes ERSeP', 'Unid. Bonif. Exc', 'Imp. Bonif. Exc.', 'Unid. Reajustes',
'Imp. Reajustes', 'Unid. COIE', 'Imp. COIE', 'Unid. COIE Dem.', 'Imp. COIE Dem', 'Unid. TRA1', 'Imp.
TRA1', 'Unid. TRA2', 'Imp. TRA2', 'Unid. TSCA', 'Imp. TSCA', 'Unid. TSSA', 'Imp. TSSA', 'Unid. Bonif.
Cemdo', 'Imp. Bonif. Cemdo', 'Unid. TSPC', 'Imp. TSPC', 'Unid. TSPI', 'Imp. TSPI');
FOR regTemp IN curTemp(regLoc.IdLocalidad) LOOP
--
-- now we make sure this assignment works ok
--
DBMS_OUTPUT.PUT_LINE(regTemp.IdCategoria);
my_row :=xxLxC1_row(regTemp.IdCategoria || '-' || regTemp.Descripcion, regTemp.Cargo_Fijo,
regTemp.Importe_C_Fijo, regTemp.kWh, regTemp.Importe_Consumo, regTemp.Cargo_Fijo_GC,
regTemp.Imp_C_Fijo_GC, regTemp.Energia_GC, regTemp.Importe_GC, regTemp.Unidades_Demanda,
regTemp.Importe_Demanda, regTemp.Cant_BEN, regTemp.BEN, regTemp.Ajuste_Coseno,
regTemp.Imp_Ajuste_Coseno, regTemp.Cant_Cargos_Ersep, regTemp.Imp_Cargos_Ersep,
regTemp.Cant_Ajustes_Ersep, regTemp.Imp_Ajustes_Ersep, regTemp.Unid_Bonif_Exc,
regTemp.Imp_Bonif_Exc, regTemp.Unid_Reajustes, regTemp.Imp_Reajustes, regTemp.Unid_Coie,
regTemp.Imp_Coie, regTemp.Unid_Coie_Dem, regTemp.Imp_Coie_Dem, regTemp.Unid_TRA1,
regTemp.Imp_TRA1, regTemp.Unid_TRA2, regTemp.Imp_TRA2, regTemp.Unid_TSCA, regTemp.Imp_TSCA,
regTemp.Unid_TSSA, regTemp.Imp_TSSA, regTemp.UnidBonifCemdo, regTemp.ImpBonifCemdo,
regTemp.UnidTSPC, regTemp.ImpTSPC, regTemp.UnidTSPI, regTemp.ImpTSPI);
END LOOP;
END LOOP;
END;
/
variable x number
set serverout on
exec :x := xxLocalidadPorCategoria1(...)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句