Untitled
plain_text
a month ago
3.4 kB
1
Indexable
Never
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;