Untitled

create procedure sync_rx_tracking_transaction_details(in_source character varying)
    language plpgsql
as
$$
BEGIN

	DROP TABLE IF EXISTS RX_TRACKING_TXN_DTLS_NEW;
	DROP TABLE IF EXISTS RX_TRACKING_TXN_DTLS_CHANGED;

	IF (SELECT count(1)
	FROM public.rx_tracking_transaction_details_daily ) < 1
    THEN
	RETURN;
END
IF;

CREATE TEMPORARY TABLE RX_TRACKING_TXN_DTLS_NEW AS
( 
	  SELECT d.*
FROM public.rx_tracking_transaction_details_daily d
	left join public.rx_tracking_transaction_details rtx
	on d.escript_id = rtx.escript_id and
		d.pres_order_id = rtx.pres_order_id and
		rtx.is_latest = 1
WHERE rtx.escript_id is null and d.source = in_source
	);

insert into public.rx_tracking_transaction_details
	(txn_source, escript_id, client_id, order_id, pres_order_id, source, is_latest, upload_time, status, changed_on, latest_refill_number, tracking_number)
select txn_source, escript_id, client_id, order_id, pres_order_id, source, is_latest, upload_time, status, changed_on, latest_refill_number, tracking_number
from (
        select txn_source, escript_id, client_id, order_id, pres_order_id, source, 1 as is_latest, upload_time, status, changed_on, latest_refill_number, tracking_number,
		row_number() over (partition by escript_id, pres_order_id order by escript_id, pres_order_id desc) as rn
	from RX_TRACKING_TXN_DTLS_NEW
    ) as a
where rn = 1;

raise notice 'new transactions: %',
(select count(*)
from RX_TRACKING_TXN_DTLS_NEW);

CREATE TEMPORARY TABLE RX_TRACKING_TXN_DTLS_CHANGED AS
	(
		SELECT txn_source, escript_id, client_id, order_id, pres_order_id, source, 1 as is_latest, status, changed_on, latest_refill_number, tracking_number
	FROM public.rx_tracking_transaction_details_daily
	where source = in_source
except
	SELECT txn_source, escript_id, client_id, order_id, pres_order_id, source, 1 as is_latest, status, changed_on, latest_refill_number, tracking_number
	FROM public.rx_tracking_transaction_details
	where is_latest = 1
	);

update public.rx_tracking_transaction_details 
	set is_latest = 0 
	from RX_TRACKING_TXN_DTLS_CHANGED 
	where RX_TRACKING_TXN_DTLS_CHANGED.escript_id = public.rx_tracking_transaction_details.escript_id and
	RX_TRACKING_TXN_DTLS_CHANGED.pres_order_id = public.rx_tracking_transaction_details.pres_order_id and
	public.rx_tracking_transaction_details.is_latest = 1 and RX_TRACKING_TXN_DTLS_CHANGED.is_latest = 1;

insert into public.rx_tracking_transaction_details
	(txn_source, escript_id, client_id, order_id, pres_order_id, source, is_latest, status, changed_on, latest_refill_number, tracking_number)
select txn_source, escript_id, client_id, order_id, pres_order_id, source, is_latest, status, changed_on, latest_refill_number, tracking_number
from (
	select txn_source, escript_id, client_id, order_id, pres_order_id, source, 1 as is_latest, status, changed_on, latest_refill_number, tracking_number,
		row_number() over (partition by escript_id, pres_order_id order by escript_id, pres_order_id desc) as rn
	from RX_TRACKING_TXN_DTLS_CHANGED
		) as a
where rn = 1;

raise notice 'transactions changed: %',
(select count(*)
from RX_TRACKING_TXN_DTLS_CHANGED);

DROP TABLE IF EXISTS RX_TRACKING_TXN_DTLS_NEW;
DROP TABLE IF EXISTS RX_TRACKING_TXN_DTLS_CHANGED;

END;

$$;

alter procedure sync_rx_tracking_transaction_details(varchar) owner to postgres;

grant execute on procedure sync_rx_tracking_transaction_details(varchar) to administrator;