get_ppn_dpp_jasa

 avatar
unknown
mysql
5 months ago
2.2 kB
3
Indexable
CREATE OR REPLACE FUNCTION public.get_ppn_dpp_jasa(nomorwo character varying, packagecode character varying)
RETURNS TABLE(
    wo_id character varying, 
    totaljasa double precision, 
    totaldiskonjasa double precision, 
    dppasli double precision, 
    dppdisc double precision, 
    dppjasa double precision, 
    ppnasli double precision, 
    ppndisc double precision, 
    ppnjasa double precision
)
LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT
        woid as wo_id,
        CAST(SUM(CAST(harga * qty AS numeric)) AS double precision) AS totaljasa, 
        
        CAST(SUM(CAST(discount AS numeric)) AS double precision) AS totaldiskonjasa, 
        
        CAST(SUM(FLOOR(CAST((harga * qty) AS numeric) - FLOOR((CAST(harga * qty AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100)))) AS double precision) AS dppasli, 
        
        CAST(SUM(FLOOR(CAST(discount AS numeric) - FLOOR((CAST(discount AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100)))) AS double precision) AS dppdisc, 
        
        CAST(
            SUM(FLOOR(CAST((harga * qty) AS numeric) - FLOOR((CAST(harga * qty AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100)))) - 
            SUM(FLOOR(CAST(discount AS numeric) - FLOOR((CAST(discount AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100))))
        AS double precision) AS dppjasa, 
        
        CAST(SUM(FLOOR((CAST(harga * qty AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100))) AS double precision) AS ppnasli, 
        
        CAST(SUM(FLOOR((CAST(discount AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100))) AS double precision) AS ppndisc, 
        
        CAST(
            SUM(FLOOR((CAST(harga * qty AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100))) - 
            SUM(FLOOR((CAST(discount AS numeric) / ((ppnrate + 100)::numeric / 100)) * (ppnrate::numeric / 100)))
        AS double precision) AS ppnjasa
    FROM 
        faktur_wo_jasa_v3(nomorwo, packagecode)
    GROUP BY woid; 
END;
$function$;
;
Editor is loading...
Leave a Comment