Untitled
unknown
mysql
4 months ago
3.2 kB
4
Indexable
CREATE OR REPLACE FUNCTION public.get_ppn_dpp_part(nomorwo character varying) RETURNS TABLE( woid_out character varying, totalpart double precision, totaldiskonpart double precision, dppasli double precision, dppdisc double precision, dpppart double precision, ppnasli double precision, ppndiskon double precision, ppnpart double precision, ppnrate_out double precision ) LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY SELECT ins.woid AS woid_out, CAST(SUM(CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) AS double precision) AS totalpart, CAST(SUM(CAST(ins.total_discount AS numeric)) AS double precision) AS totaldiskonpart, CAST(SUM(FLOOR((CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) - FLOOR((CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) / ((CAST(ins.ppnrate AS numeric) + 100) / 100) * (CAST(ins.ppnrate AS numeric) / 100)))) AS double precision) AS dppasli, -- DPP' perlines (Harga) CAST(SUM(FLOOR(CAST(ins.total_discount AS numeric) - FLOOR((CAST(ins.total_discount AS numeric) / ((CAST(ins.ppnrate AS numeric) + 100) / 100)) * (CAST(ins.ppnrate AS numeric) / 100)))) AS double precision) AS dppdisc, -- DPP' perlines (Diskon) CAST(SUM(FLOOR((CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) - FLOOR((CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) / ((CAST(ins.ppnrate AS numeric) + 100) / 100) * (CAST(ins.ppnrate AS numeric) / 100)))) - SUM(FLOOR(CAST(ins.total_discount AS numeric) - FLOOR((CAST(ins.total_discount AS numeric) / ((CAST(ins.ppnrate AS numeric) + 100) / 100)) * (CAST(ins.ppnrate AS numeric) / 100)))) AS double precision) AS dpppart, -- DPP' perlines (Harga) - DPP' perlines (Diskon) CAST(SUM(FLOOR((CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) / ((CAST(ins.ppnrate AS numeric) + 100) / 100) * (CAST(ins.ppnrate AS numeric) / 100))) AS double precision) AS ppnasli, -- PPN' perlines (Harga) CAST(SUM(FLOOR((CAST(ins.total_discount AS numeric) / ((CAST(ins.ppnrate AS numeric) + 100) / 100)) * (CAST(ins.ppnrate AS numeric) / 100))) AS double precision) AS ppndiskon, -- PPN' perlines (Diskon) CAST(SUM(FLOOR((CAST(ins.harganew AS numeric) * CAST(ins.quantity AS numeric)) / ((CAST(ins.ppnrate AS numeric) + 100) / 100) * (CAST(ins.ppnrate AS numeric) / 100))) - SUM(FLOOR((CAST(ins.total_discount AS numeric) / ((CAST(ins.ppnrate AS numeric) + 100) / 100)) * (CAST(ins.ppnrate AS numeric) / 100))) AS double precision) AS ppnpart, -- PPN' perlines (Harga) - PPN' perlines (Diskon) CAST(ins.ppnrate AS double precision) AS ppnrate_out -- PPN Rate FROM ( SELECT woid, harga AS harganew, quantity, total_discount, ppnamount, ppnrate, c2 FROM get_part_wo_exclude_bo_hotline(nomorwo) ) ins GROUP BY ins.woid, ins.ppnrate; END; $function$; ;
Editor is loading...
Leave a Comment