report_penjualanjasa_by_kodejasa_v4
unknown
mysql
5 months ago
3.2 kB
4
Indexable
CREATE OR REPLACE FUNCTION public.report_penjualanjasa_by_kodejasa_v4(fromdate character varying, todate character varying, kodeahass character varying) RETURNS TABLE(no_kwitansi character varying, tgl_kwitansi date, noidentitascust character varying, custname character varying, alamat character varying, cwsattr text, hargasatuan double precision, qty integer, hargatotal double precision, discount double precision, dpp double precision, ppn double precision) LANGUAGE plpgsql AS $function$ declare -- startdate Date := fromdate::Date; -- enddate timestamp := (todate::timestamp + interval '1 day - 1 second'); -- startdate Date := fromdate::Date; -- enddate Date := todate::Date; startdate Date := to_date(fromdate, 'DD/MM/YYYY');--startdate::Date; enddate timestamp := (to_date(todate, 'DD/MM/YYYY')::timestamp + interval '1 day - 1 second'); startdate2 character varying := to_char(to_date(fromdate, 'DD/MM/YYYY'), 'YYYY-MM-DD'); enddate2 character varying := to_char(to_date(todate, 'DD/MM/YYYY'), 'YYYY-MM-DD'); ppn_rate_value double precision; begin SELECT ppn_rate INTO ppn_rate_value FROM mst_ppn WHERE status = 'Active' AND ahm_dealer_code = :kodeahass ORDER BY id DESC LIMIT 1; return QUERY select wo.invoice, Date(wo.modifiedat) tgl_kwitansi, case when wo.noktp is null then '' else wo.noktp end as noidentitascust, wo.customername custname, case when wo.lastaddress is null then '' else wo.lastaddress end as alamat, (select a.* from view_get_configworkshopservice_attribute_by_woservice(wo.configworkshopserviceid, wo.marketingcode, wo.ahassid) a) cwsattr, wo.harga hargasatuan, wo.qty, wo.total hargatotal, wo.discount, -- (select ppn_result from calculate_ppn_v1(wo.harga, wo.qty::integer, wo.discount, ppn_rate_value)) as ppn, ((wo.qty * wo.harga) - (wo.qty * wo.discount) - (select ppn_result from calculate_ppn_v1(wo.harga, wo.qty::integer, wo.discount, ppn_rate_value))) as dpp, (select ppn_result from calculate_ppn_v1(wo.harga, wo.qty::integer, wo.discount, ppn_rate_value)) as ppn from rpt_wo_monitoring_wo_v3 wo where wo.createdat between startdate and enddate and wo.ahassid = kodeahass and (select a.* from view_get_configworkshopservice_attribute_by_woservice(wo.configworkshopserviceid, wo.marketingcode, wo.ahassid) a) not like '%ASS%' and (select a.* from view_get_configworkshopservice_attribute_by_woservice(wo.configworkshopserviceid, wo.marketingcode, wo.ahassid) a) not like '%CLAIM C2%' --and rwm.wolastknownstate = 'PAYMENT' group by wo.invoice , wo.modifiedat , wo.noktp , wo.customername , wo.lastaddress, cwsattr, wo.harga, wo.qty, wo.total, wo.discount order by tgl_kwitansi asc, wo.invoice asc; -- order by wo.woid asc; end $function$ ;
Editor is loading...
Leave a Comment