Tengo una base de datos de nómina con el siguiente script, el cual en una base de datos funciona, y lo ejecuto en otra base de datos de copia y no lo hace. No he podido encontrar el error. Agradecería cualquier sugerencia.
Código SQL:
Ver original-- View: etitc_basicpayrollsiif_vt
-- DROP VIEW etitc_basicpayrollsiif_vt;
CREATE OR REPLACE VIEW etitc_basicpayrollsiif_vt AS
SELECT DISTINCT 1000003 AS ad_client_id, 1000003 AS ad_org_id,
'es_CO'::text AS ad_language, m.hr_process_id, e.hr_payroll_id,
e.c_bpartner_id, m.validfrom, m.validto, b.name, b.VALUE,
( SELECT m.qty
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000208::NUMERIC) AS quatity,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000247::NUMERIC) AS salary,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000252::NUMERIC) AS foodsubsidy,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000143::NUMERIC) AS transportsubsidy,
( SELECT m.qty
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000215::NUMERIC) AS extrahoursquantity,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000192::NUMERIC) AS totalvalueextrahours,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000144::NUMERIC) AS servicebonus,
( SELECT SUM(m.amount) AS amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND (m.hr_concept_id = ANY (ARRAY[1000230::NUMERIC, 1000232::NUMERIC]))) AS bonustechnicalnosalary,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000228::NUMERIC) AS bonuscoordination,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000231::NUMERIC) AS bonustechnicalsalary,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000261::NUMERIC) AS totalaccrued,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000171::NUMERIC) AS employeehealthquote,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000275::NUMERIC) AS employeeboardquote,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1001154::NUMERIC) AS employeeisaquote,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000189::NUMERIC) AS pensionsolidarityfund,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000169::NUMERIC) AS coeeducol,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000276::NUMERIC) AS credivalores,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000197::NUMERIC) AS bancopopularcharge,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000279::NUMERIC) AS daviviendacharge,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000198::NUMERIC) AS compensarcharge,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000304::NUMERIC) AS complementaryplanhealth,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000168::NUMERIC) AS canapro,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000280::NUMERIC) AS afc,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000305::NUMERIC) AS bayport,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000251::NUMERIC) AS tradeunion,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000250::NUMERIC) AS bolivarinsurance,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000175::NUMERIC) AS liensandfines,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000277::NUMERIC) AS withholdingwages,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000260::NUMERIC) AS totaldeductions,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000199::NUMERIC) AS netpay,
( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000199::NUMERIC) AS pay
FROM hr_employee e
JOIN hr_payroll p ON p.hr_payroll_id = e.hr_payroll_id
JOIN c_bpartner b ON b.c_bpartner_id = e.c_bpartner_id
LEFT JOIN hr_movement m ON m.c_bpartner_id = e.c_bpartner_id;
ALTER TABLE etitc_basicpayrollsiif_vt
OWNER TO adempiere;
[B]ERROR:[/B]
ERROR: una subconsulta utilizada como expresión retornó más de un registro
********** Error **********
ERROR: una subconsulta utilizada como expresión retornó más de un registro
SQL state: 21000