Untitled

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$;
;