-- FUNCTION: issue40019_updatedata(character varying, character varying) -- DROP FUNCTION issue40019_updatedata(character varying, character varying); CREATE OR REPLACE FUNCTION issue40019_updatedata(p_datefrom character varying, p_dateto character varying) RETURNS character varying AS $BODY$ DECLARE v_datefrom TIMESTAMP without time zone; v_dateto TIMESTAMP without time zone; v_amounttokeep NUMERIC:=0; Cur_rec RECORD; BEGIN IF p_datefrom IS NULL OR p_dateto IS NULL THEN RAISE EXCEPTION '%', 'Please provide dates in valid format'; END IF; BEGIN select p_datefrom::timestamp into v_datefrom from dual; select p_dateto::timestamp + INTERVAL '1' DAY - INTERVAL '1' SECOND into v_dateto from dual; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION '%', 'Please provide dates in valid format : '|| SQLERRM; --OBTG:-20000-- END; IF v_datefrom <= TO_DATE('01-01-2017', 'dd-MM-yyyy') OR v_dateto > TO_DATE('01-01-2020', 'dd-MM-yyyy') OR v_datefrom > v_dateto THEN RAISE EXCEPTION '%', 'Please provide dates in valid range'; END IF; -- RAISE NOTICE 'Updating PaymentMethod Cashup from : % to %', v_datefrom, v_dateto; FOR Cur_rec in (select oac.obpos_applications_id, oac.cashupdate, opc.obpos_paymentmethodcashup_id, opc.obpos_app_payment_id, opc.totalcounted from obpos_app_cashup oac join obpos_paymentmethodcashup opc on opc.obpos_app_cashup_id = oac.obpos_app_cashup_id where oac.isprocessed = 'Y' and oac.cashupdate >= v_datefrom and oac.cashupdate <= v_dateto order by oac.obpos_applications_id asc, oac.cashupdate asc, opc.name asc) LOOP select coalesce(startingcash, 0) into v_amounttokeep from obpos_paymentmethodcashup where obpos_app_payment_id = Cur_rec.obpos_app_payment_id and obpos_app_cashup_id = (select obpos_app_cashup_id from obpos_app_cashup where obpos_applications_id = Cur_rec.obpos_applications_id and cashupdate > Cur_rec.cashupdate order by cashupdate asc limit 1); CONTINUE WHEN Cur_rec.totalcounted IS not NULL and Cur_rec.totalcounted > 0; update obpos_paymentmethodcashup set amounttokeep = (CASE WHEN amounttokeep = 0 THEN v_amounttokeep ELSE amounttokeep END), totalcounted = startingcash+totalsales-totalreturns+totaldeposits-totaldrops where obpos_paymentmethodcashup_id = Cur_rec.obpos_paymentmethodcashup_id; END LOOP; RETURN 'SUCCESS'; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION '%', SQLERRM; --OBTG:-20000-- END ; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION issue40019_updatedata(character varying, character varying) OWNER TO tad; /* -- Please provide date in the format MM-dd-yyyy -- Call function select issue40019_updatedata('01-01-2018', '12-31-2019'); -- Drop function DROP FUNCTION issue40019_updatedata(character varying, character varying); */