writing rank

[dsfamily]

-- Function: gtr_etd.etd_auto_exit()

-- DROP FUNCTION gtr_etd.etd_auto_exit();

CREATE OR REPLACE FUNCTION gtr_etd.etd_auto_exit()
RETURNS integer AS
$BODY$

DECLARE

p_position character varying := 'Position';
p_submitted_for character varying := 'BOTH';
p_PositionCancel character varying := 'PositionCancel';
p_Exit character varying := 'Exit';
p_AutoExit character varying := 'AutoExit';
p_EOD character varying := 'EOD';
p_New character varying := 'New';
p_EOD_update character varying := 'EOD Valuation Update';
p_active character varying := 'Active';
curr_cutofftime timestamp without time zone;
prev_cutofftime timestamp without time zone;
rep_date date;
Error_code character varying(10) :='ETD0926';
Error_Desc character varying(200);


BEGIN

-- select gtr_etd.etd_auto_exit()


truncate table gtr_etd.etd_auto_exit_temp_latest;
truncate table gtr_etd.etd_eod_position_temp;
truncate table gtr_etd.etd_matching_temp;
truncate table gtr_etd.etd_full_file_submitter;
truncate table gtr_etd.etd_auto_exit_interima;
truncate table gtr_etd.etd_auto_exit_interimb;



select parameter_val::timestamp without time zone into curr_cutofftime from gtr_etd.etd_application_parameters where parameter_code ='CURRENT_CUTOFF_TIME';
select parameter_val::timestamp without time zone into prev_cutofftime from gtr_etd.etd_application_parameters where parameter_code = 'PREVIOUS_CUTOFF_TIME';
select parameter_val::date into rep_date from gtr_etd.etd_application_parameters where parameter_code = 'PREVIOUS_BUSINESS_DATE';




------------inserting latest records into etd_auto_exit_temp_latest from etd_auto_exit_temp_submissions-----------------

insert into gtr_etd.etd_full_file_submitter
(select GTR_ACCT_ID from gtr_etd.etd_SDOB_AST_ROLE_MAP sdob,
(select party1_acct_id,submitter_acct_id from gtr_etd.etd_EOD_POSITION
where position_status = P_ACTIVE and report_date = rep_date group by 1,2) eodpos
where eodpos.PARTY1_ACCT_ID=sdob.GTR_ACCT_ID and eodpos.submitter_acct_id = sdob.prm_snap_sub_acct
and sdob.is_full_file_indicator='Y'
);


INSERT INTO gtr_etd.etd_auto_exit_temp_latest
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
-- ,submitted_for_acct_id
,data_submitter_prfx
,data_submitter_val
,submitter_acct_id
-- ,is_full_file_indicator
,created_ts
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,submitter_known
-- ,submitted_for_known
,delegation_known
)

(select
latest.uti_prfx
,latest.uti_val
,latest.trade_accept_id
,latest.party1_prfx
,latest.party1_val
,latest.party1_acct_id
,latest.party2_prfx
,latest.party2_val
,latest.party2_acct_id
-- ,latest.submitted_for_acct_id
,latest.data_submitter_prfx
,latest.data_submitter_val
,latest.submitter_acct_id
-- ,latest.is_full_file_indicator
,latest.created_ts
,latest.delegation_acct_prfx
,latest.delegation_acct_val
,latest.delegation_acct_id
,party1_known
,party2_known
,submitter_known
-- ,submitted_for_known
,delegation_known
FROM
(select
eodpos.uti_prfx
,eodpos.uti_val
,eodpos.trade_accept_id
,eodpos.party1_prfx
,eodpos.party1_val
,eodpos.party1_acct_id
,eodpos.party2_prfx
,eodpos.party2_val
,eodpos.party2_acct_id
-- ,eodpos.submitted_for_acct_id
,eodpos.data_submitter_prfx
,eodpos.data_submitter_val
,eodpos.submitter_acct_id
-- ,eodpos.is_full_file_indicator
,eodpos.created_ts
,eodpos.delegation_acct_prfx
,eodpos.delegation_acct_val
,eodpos.delegation_acct_id
,eodpos.party1_known
,eodpos.party2_known
,eodpos.submitter_known
-- ,eodpos.submitted_for_known
,eodpos.delegation_known
,row_number() over (partition by eodpos.uti_prfx,eodpos.uti_val,eodpos.party1_acct_id order by eodpos.created_ts desc) as rank
from gtr_etd.etd_EOD_POSITION eodpos
inner join
gtr_etd.etd_full_file_submitter auto
on (eodpos.party1_acct_id = auto.party1_acct_id or eodpos.delegation_acct_id = auto.party1_acct_id)
where eodpos.report_date = rep_date
)LATEST
WHERE LATEST.RANK=1);


-- INSERT INTO gtr_etd.etd_auto_exit_temp_latest
-- (
-- uti_prfx
-- ,uti_val
-- ,trade_accept_id
-- ,party1_prfx
-- ,party1_val
-- ,party1_acct_id
-- ,party2_prfx
-- ,party2_val
-- ,party2_acct_id
-- -- ,submitted_for_acct_id
-- ,data_submitter_prfx
-- ,data_submitter_val
-- ,submitter_acct_id
-- -- ,is_full_file_indicator
-- ,created_ts
-- ,delegation_acct_prfx
-- ,delegation_acct_val
-- ,delegation_acct_id
-- ,party1_known
-- ,party2_known
-- ,submitter_known
-- -- ,submitted_for_known
-- ,delegation_known
-- )
-- (select
-- latest.uti_prfx
-- ,latest.uti_val
-- ,latest.trade_accept_id
-- ,latest.party1_prfx
-- ,latest.party1_val
-- ,latest.party1_acct_id
-- ,latest.party2_prfx
-- ,latest.party2_val
-- ,latest.party2_acct_id
-- -- ,latest.submitted_for_acct_id
-- ,latest.data_submitter_prfx
-- ,latest.data_submitter_val
-- ,latest.submitter_acct_id
-- -- ,latest.is_full_file_indicator
-- ,latest.created_ts
-- ,latest.delegation_acct_prfx
-- ,latest.delegation_acct_val
-- ,latest.delegation_acct_id
-- ,party1_known
-- ,party2_known
-- ,submitter_known
-- -- ,submitted_for_known
-- ,delegation_known
-- FROM
-- (select
-- eodpos.uti_prfx
-- ,eodpos.uti_val
-- ,eodpos.trade_accept_id
-- ,eodpos.party1_prfx
-- ,eodpos.party1_val
-- ,eodpos.party1_acct_id
-- ,eodpos.party2_prfx
-- ,eodpos.party2_val
-- ,eodpos.party2_acct_id
-- -- ,eodpos.submitted_for_acct_id
-- ,eodpos.data_submitter_prfx
-- ,eodpos.data_submitter_val
-- ,eodpos.submitter_acct_id
-- -- ,eodpos.is_full_file_indicator
-- ,eodpos.created_ts
-- ,eodpos.delegation_acct_prfx
-- ,eodpos.delegation_acct_val
-- ,eodpos.delegation_acct_id
-- ,eodpos.party1_known
-- ,eodpos.party2_known
-- ,eodpos.submitter_known
-- -- ,eodpos.submitted_for_known
-- ,eodpos.delegation_known
-- ,row_number() over (partition by eodpos.uti_prfx,eodpos.uti_val,eodpos.party1_acct_id order by eodpos.created_ts desc) as rank
-- from gtr_etd.etd_EOD_POSITION eodpos
-- inner join
-- (select party1_acct_id from gtr_etd.etd_SDOB_AST_ROLE_MAP sdob
-- inner join gtr_etd.etd_EOD_POSITION eodpos
-- on (eodpos.PARTY1_ACCT_ID=sdob.GTR_ACCT_ID)
-- where sdob.activation_ts < current_timestamp
-- and sdob.termination_ts > current_timestamp
-- and eodpos.submitter_acct_id = sdob.prm_snap_sub_acct
-- and sdob.is_full_file_indicator='Y'
-- and eodpos.position_status = P_ACTIVE
-- and eodpos.created_ts BETWEEN prev_cutofftime AND curr_cutofftime
-- and sdob.asset_id = 'XT'
-- ) auto
-- on (eodpos.party1_acct_id = auto.party1_acct_id or eodpos.delegation_acct_id = auto.party1_acct_id)
-- where eodpos.created_ts BETWEEN prev_cutofftime AND curr_cutofftime
-- )LATEST
-- WHERE LATEST.RANK=1);
--

---------------------inserting into etd_auto_exit for UTI's to be auto exited-----------------------------------

insert into gtr_etd.etd_auto_exit_interima
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
,data_submitter_prfx
,data_submitter_val
-- ,created_ts
,report_date
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
)
select
pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
-- ,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
from
gtr_etd.etd_eod_position_report pos,
( SELECT party1_acct_id from gtr_etd.etd_auto_exit_temp_latest WHERE delegation_acct_id is null GROUP BY 1) lat
where pos.party1_acct_id = lat.party1_acct_id ;


insert into gtr_etd.etd_auto_exit_interima
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
,data_submitter_prfx
,data_submitter_val
-- ,created_ts
,report_date
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
)
select
pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
-- ,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
from
gtr_etd.etd_eod_position_report pos,
( SELECT party1_acct_id from gtr_etd.etd_auto_exit_temp_latest WHERE delegation_acct_id is null GROUP BY 1) lat
where pos.delegation_acct_id = lat.party1_acct_id;

insert into gtr_etd.etd_auto_exit_interima
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
,data_submitter_prfx
,data_submitter_val
-- ,created_ts
,report_date
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
)
select

pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
-- ,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
from
gtr_etd.etd_eod_position_report pos,
( SELECT delegation_acct_id from gtr_etd.etd_auto_exit_temp_latest WHERE delegation_acct_id is not null GROUP BY 1) lat
where pos.delegation_acct_id = lat.delegation_acct_id ;

insert into gtr_etd.etd_auto_exit_interima
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
,data_submitter_prfx
,data_submitter_val
-- ,created_ts
,report_date
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
)
select
pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
-- ,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
from
gtr_etd.etd_eod_position_report pos,
( SELECT delegation_acct_id from gtr_etd.etd_auto_exit_temp_latest WHERE delegation_acct_id is not null GROUP BY 1) lat
where pos.party1_acct_id = lat.delegation_acct_id;
--
-- insert into gtr_etd.etd_auto_exit_interima
-- (
-- uti_prfx
-- ,uti_val
-- ,trade_accept_id
-- ,party1_prfx
-- ,party1_val
-- ,party1_acct_id
-- ,party2_prfx
-- ,party2_val
-- ,party2_acct_id
-- ,data_submitter_prfx
-- ,data_submitter_val
-- ,created_ts
-- ,report_date
-- ,delegation_acct_prfx
-- ,delegation_acct_val
-- ,delegation_acct_id
-- ,party1_known
-- ,party2_known
-- ,delegation_known
-- )
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- FROM (
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- ,pos.position_status
-- ,row_number() over ( partition by pos.uti_prfx,pos.uti_val,pos.party1_acct_id ORDER BY UPDATED_TS DESC) AS RANK
-- from
-- gtr_etd.etd_eod_position pos
-- inner join
-- gtr_etd.etd_auto_exit_temp_latest lat
-- on ((lat.delegation_acct_id is null and (pos.party1_acct_id = lat.party1_acct_id or pos.delegation_acct_id = lat.party1_acct_id))
-- or (lat.delegation_acct_id is not null and (pos.delegation_acct_id = lat.delegation_acct_id or pos.party1_acct_id = lat.delegation_acct_id)))
-- AND POS.created_TS < prev_cutofftime) POS
-- where pos.position_status not in (p_PositionCancel,p_Exit,p_AutoExit) AND POS.RANK = 1;




insert into gtr_etd.etd_auto_exit_interimb
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
,data_submitter_prfx
,data_submitter_val
-- ,created_ts
,report_date
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
)
select
pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
-- ,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
from
(select
pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
-- ,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
,row_number() over (partition by pos.uti_prfx,pos.uti_val,pos.party1_acct_id order by pos.updated_ts desc) rank
from
gtr_etd.etd_eod_position pos inner join gtr_etd.etd_auto_exit_temp_latest c
on coalesce(pos.uti_prfx,'x') = coalesce(c.uti_prfx,'x') and pos.uti_val = c.uti_val and (pos.party1_acct_id = c.party1_acct_id or pos.delegation_acct_id = c.party1_acct_id))pos
where pos.rank = 1;



insert into gtr_etd.etd_auto_exit
(
uti_prfx
,uti_val
,trade_accept_id
,party1_prfx
,party1_val
,party1_acct_id
,party2_prfx
,party2_val
,party2_acct_id
,data_submitter_prfx
,data_submitter_val
,created_ts
,report_date
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
)
select

pos.uti_prfx
,pos.uti_val
,pos.trade_accept_id
,pos.party1_prfx
,pos.party1_val
,pos.party1_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.party2_acct_id
,pos.data_submitter_prfx
,pos.data_submitter_val
,current_timestamp as created_ts
,rep_date
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
from
gtr_etd.etd_auto_exit_interima pos
left outer join
gtr_etd.etd_auto_exit_interimb posb
on coalesce(pos.uti_prfx,'x') = coalesce(posb.uti_prfx,'x') and pos.uti_val = posb.uti_val
and pos.party1_acct_id = posb.party1_acct_id and coalesce(pos.delegation_acct_id,'') = coalesce(posb.delegation_acct_id,'')
where posb.uti_prfx is null and posb.uti_val is null and posb.party1_acct_id is null ;


-- insert into gtr_etd.etd_auto_exit
-- (
-- uti_prfx
-- ,uti_val
-- ,trade_accept_id
-- ,party1_prfx
-- ,party1_val
-- ,party1_acct_id
-- ,party2_prfx
-- ,party2_val
-- ,party2_acct_id
-- ,data_submitter_prfx
-- ,data_submitter_val
-- ,created_ts
-- ,report_date
-- ,delegation_acct_prfx
-- ,delegation_acct_val
-- ,delegation_acct_id
-- ,party1_known
-- ,party2_known
-- ,delegation_known
-- )
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- from
-- gtr_etd.etd_auto_exit_interima pos
--
-- except
--
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- from
-- gtr_etd.etd_auto_exit_interimb pos;



-- insert into gtr_etd.etd_auto_exit
-- (
-- uti_prfx
-- ,uti_val
-- ,trade_accept_id
-- ,party1_prfx
-- ,party1_val
-- ,party1_acct_id
-- ,party2_prfx
-- ,party2_val
-- ,party2_acct_id
-- ,data_submitter_prfx
-- ,data_submitter_val
-- ,created_ts
-- ,report_date
-- ,delegation_acct_prfx
-- ,delegation_acct_val
-- ,delegation_acct_id
-- ,party1_known
-- ,party2_known
-- ,delegation_known
-- )
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- FROM (
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- ,pos.position_status
-- ,row_number() over ( partition by pos.uti_prfx,pos.uti_val,pos.party1_acct_id ORDER BY UPDATED_TS DESC) AS RANK
-- from
-- gtr_etd.etd_eod_position pos
-- inner join
-- gtr_etd.etd_auto_exit_temp_latest lat
-- on ((lat.delegation_acct_id is null and (pos.party1_acct_id = lat.party1_acct_id or pos.delegation_acct_id = lat.party1_acct_id))
-- or (lat.delegation_acct_id is not null and (pos.delegation_acct_id = lat.delegation_acct_id or pos.party1_acct_id = lat.delegation_acct_id)))
-- AND POS.created_TS < prev_cutofftime) POS
-- where pos.position_status not in (p_PositionCancel,p_Exit,p_AutoExit) AND POS.RANK = 1
--
-- except
--
--
-- select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- from
-- (select
-- pos.uti_prfx
-- ,pos.uti_val
-- ,pos.trade_accept_id
-- ,pos.party1_prfx
-- ,pos.party1_val
-- ,pos.party1_acct_id
-- ,pos.party2_prfx
-- ,pos.party2_val
-- ,pos.party2_acct_id
-- ,pos.data_submitter_prfx
-- ,pos.data_submitter_val
-- ,current_timestamp as created_ts
-- ,rep_date
-- ,pos.delegation_acct_prfx
-- ,pos.delegation_acct_val
-- ,pos.delegation_acct_id
-- ,pos.party1_known
-- ,pos.party2_known
-- ,pos.delegation_known
-- ,row_number() over (partition by pos.uti_prfx,pos.uti_val,pos.party1_acct_id order by pos.updated_ts desc) rank
-- from
-- gtr_etd.etd_eod_position pos inner join gtr_etd.etd_auto_exit_temp_latest c
-- on coalesce(pos.uti_prfx,'x') = coalesce(c.uti_prfx,'x') and pos.uti_val = c.uti_val and (pos.party1_acct_id = c.party1_acct_id or pos.delegation_acct_id = c.party1_acct_id))pos
-- where pos.rank = 1
-- -- AND POS.UPDATED_TS > prev_cutofftime
-- ;



INSERT INTO gtr_etd.ETD_eod_position_temp
( uti_prfx
,uti_val
,party1_acct_id
,as_of_date_time
,primary_asset_class
,message_type
,party1_prfx
,party1_val
,party2_acct_id
,party2_prfx
,party2_val
,trade_accept_id
,transaction_type
,data_submitter_prfx
,data_submitter_val
,submitted_for_prfx
,submitted_for_val
,scheduled_termination_date
,product_id1_prfx
,product_id1_val
,product_id2_prfx
,product_id2_val
,notional_amount
,notional_ccy_units
,quantity
,price_notation_price
,price_notation_type
,option_type
,beneficiary_id_party1_prfx
,beneficiary_id_party1_val
,beneficiary_id_party2_prfx
,beneficiary_id_party2_val
,buyer_prfx_party1
,buyer_val_party1
,buyer_prfx_party2
,buyer_val_party2
,underlying_asset
,mtm_val_party1
,mtm_ccy_party1
,mtm_val_party2
,mtm_ccy_party2
,mtm_val_ccp
,mtm_ccy_ccp
,party1_transaction_id
,party2_transaction_id
,reporting_oblgtn_party1
,reporting_oblgtn_party2
,position_status
,created_ts
,created_user
,updated_ts
,updated_user
,party1_name
,party2_name
,data_submitter_message_id
,Valuation_ts_party1
,option_strike_price
,option_strike_price_ccy
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
,submitter_acct_id
,report_date
)

----------------inserting for values in position with submitted for = both
select
a.uti_prfx
,a.uti_val
,a.party1_acct_id
,a.as_of_date_time
,a.primary_asset_class
,a.message_type
,a.party1_prfx
,a.party1_val
,a.party2_acct_id
,a.party2_prfx
,a.party2_val
,a.trade_accept_id
,a.transaction_type
,a.data_submitter_prfx
,a.data_submitter_val
,a.submitted_for_prfx
,a.submitted_for_val
,a.scheduled_termination_date
,a.product_id1_prfx
,a.product_id1_val
,a.product_id2_prfx
,a.product_id2_val
,a.notional_amount
,a.notional_ccy_units
,a.quantity
,a.price_notation_price
,a.price_notation_type
,a.option_type
,a.beneficiary_id_party1_prfx
,a.beneficiary_id_party1_val
,a.beneficiary_id_party2_prfx
,a.beneficiary_id_party2_val
,a.buyer_prfx_party1
,a.buyer_val_party1
,a.buyer_prfx_party2
,a.buyer_val_party2
,a.underlying_asset
,a.mtm_val_party1
,a.mtm_ccy_party1
,a.mtm_val_party2
,a.mtm_ccy_party2
,a.mtm_val_ccp
,a.mtm_ccy_ccp
,a.party1_transaction_id
,a.party2_transaction_id
,a.reporting_oblgtn_party1
,a.reporting_oblgtn_party2
,a.position_status
,a.created_ts
,a.created_user
,current_timestamp as updated_ts
,current_user as updated_user
,a.party1_name
,a.party2_name
,a.data_submitter_message_id
,a.Valuation_ts_party1
,a.option_strike_price
,a.option_strike_price_ccy
,a.delegation_acct_prfx
,a.delegation_acct_val
,a.delegation_acct_id
,a.party1_known
,a.party2_known
,a.delegation_known
,a.submitter_acct_id
,a.report_date
from
(
select
a.uti_prfx
,a.uti_val
,a.party1_acct_id
,a.as_of_date_time
,a.primary_asset_class
,a.message_type
,a.party1_prfx
,a.party1_val
,a.party2_acct_id
,a.party2_prfx
,a.party2_val
,a.trade_accept_id
,a.transaction_type
,a.data_submitter_prfx
,a.data_submitter_val
,a.submitted_for_prfx
,a.submitted_for_val
,a.scheduled_termination_date
,a.product_id1_prfx
,a.product_id1_val
,a.product_id2_prfx
,a.product_id2_val
,a.notional_amount
,a.notional_ccy_units
,a.quantity
,a.price_notation_price
,a.price_notation_type
,a.option_type
,a.beneficiary_id_party1_prfx
,a.beneficiary_id_party1_val
,a.beneficiary_id_party2_prfx
,a.beneficiary_id_party2_val
,a.buyer_prfx_party1
,a.buyer_val_party1
,a.buyer_prfx_party2
,a.buyer_val_party2
,a.underlying_asset
,a.mtm_val_party1
,a.mtm_ccy_party1
,a.mtm_val_party2
,a.mtm_ccy_party2
,a.mtm_val_ccp
,a.mtm_ccy_ccp
,a.party1_transaction_id
,a.party2_transaction_id
,a.reporting_oblgtn_party1
,a.reporting_oblgtn_party2
,a.position_status
,a.created_ts
,a.created_user
,a.updated_ts
,a.updated_user
,a.party1_name
,a.party2_name
,a.data_submitter_message_id
,a.Valuation_ts_party1
,a.option_strike_price
,a.option_strike_price_ccy
,row_number() over (partition by a.uti_prfx,a.uti_val,a.party1_acct_id order by a.updated_ts desc) as rank
,a.delegation_acct_prfx
,a.delegation_acct_val
,a.delegation_acct_id
,a.party1_known
,a.party2_known
,a.delegation_known
,a.submitter_acct_id
,a.report_date
from gtr_etd.etd_eod_position a)a
where a.rank =1;


--------------------updating etd_eod_position for Auto Exit--------------------
INSERT INTO gtr_etd.ETD_eod_position
( uti_prfx
,uti_val
,party1_acct_id
,as_of_date_time
,primary_asset_class
,message_type
,party1_prfx
,party1_val
,party2_acct_id
,party2_prfx
,party2_val
,trade_accept_id
,transaction_type
,data_submitter_prfx
,data_submitter_val
,submitted_for_prfx
,submitted_for_val
,scheduled_termination_date
,product_id1_prfx
,product_id1_val
,product_id2_prfx
,product_id2_val
,notional_amount
,notional_ccy_units
,quantity
,price_notation_price
,price_notation_type
,option_type
,beneficiary_id_party1_prfx
,beneficiary_id_party1_val
,beneficiary_id_party2_prfx
,beneficiary_id_party2_val
,buyer_prfx_party1
,buyer_val_party1
,buyer_prfx_party2
,buyer_val_party2
,underlying_asset
,mtm_val_party1
,mtm_ccy_party1
,mtm_val_party2
,mtm_ccy_party2
,mtm_val_ccp
,mtm_ccy_ccp
,party1_transaction_id
,party2_transaction_id
,reporting_oblgtn_party1
,reporting_oblgtn_party2
,position_status
,created_ts
,created_user
,updated_ts
,updated_user
,party1_name
,party2_name
,data_submitter_message_id
,Valuation_ts_party1
,option_strike_price
,option_strike_price_ccy
,delegation_acct_prfx
,delegation_acct_val
,delegation_acct_id
,party1_known
,party2_known
,delegation_known
,report_date
)
select
pos.uti_prfx
,pos.uti_val
,pos.party1_acct_id
,pos.as_of_date_time
,pos.primary_asset_class
,pos.message_type
,pos.party1_prfx
,pos.party1_val
,pos.party2_acct_id
,pos.party2_prfx
,pos.party2_val
,pos.trade_accept_id
,pos.transaction_type
,pos.data_submitter_prfx
,pos.data_submitter_val
,pos.submitted_for_prfx
,pos.submitted_for_val
,pos.scheduled_termination_date
,pos.product_id1_prfx
,pos.product_id1_val
,pos.product_id2_prfx
,pos.product_id2_val
,pos.notional_amount
,pos.notional_ccy_units
,pos.quantity
,pos.price_notation_price
,pos.price_notation_type
,pos.option_type
,pos.beneficiary_id_party1_prfx
,pos.beneficiary_id_party1_val
,pos.beneficiary_id_party2_prfx
,pos.beneficiary_id_party2_val
,pos.buyer_prfx_party1
,pos.buyer_val_party1
,pos.buyer_prfx_party2
,pos.buyer_val_party2
,pos.underlying_asset
,pos.mtm_val_party1
,pos.mtm_ccy_party1
,pos.mtm_val_party2
,pos.mtm_ccy_party2
,pos.mtm_val_ccp
,pos.mtm_ccy_ccp
,pos.party1_transaction_id
,pos.party2_transaction_id
,pos.reporting_oblgtn_party1
,pos.reporting_oblgtn_party2
,'AutoExit' as position_status
,a.created_ts created_ts
,pos.created_user
,a.created_ts as updated_ts
,pos.updated_user
,pos.party1_name
,pos.party2_name
,pos.data_submitter_message_id
,pos.Valuation_ts_party1
,pos.option_strike_price
,pos.option_strike_price_ccy
,pos.delegation_acct_prfx
,pos.delegation_acct_val
,pos.delegation_acct_id
,pos.party1_known
,pos.party2_known
,pos.delegation_known
,pos.report_date
from gtr_etd.etd_auto_exit a,gtr_etd.etd_eod_position_temp pos
where
pos.party1_acct_id = a.party1_acct_id
and pos.uti_val = a.uti_val
and coalesce(pos.uti_prfx,'x') = coalesce(a.uti_prfx,'x')
and a.report_date = rep_date AND pos.position_status = p_Active;





INSERT INTO gtr_etd.ETD_MATCHING_temp
(
uti_prfx
,uti_val
,primary_asset_class
,message_type
,party1_acct_id
,party1_prfx
,party1_val
,party2_acct_id
,party2_prfx
,party2_val
,trade_accept_id
,transaction_type
,data_submitter_prfx
,data_submitter_val
,as_of_date_time
,scheduled_termination_date
,product_id1_prfx
,product_id1_val
,product_id2_prfx
,product_id2_val
,notional_amount
,notional_ccy_units
,quantity
,price_notation_price
,price_notation_type
,option_type
,buyer_prfx_party1
,buyer_val_party1
,buyer_prfx_party2
,buyer_val_party2
,reporting_oblgtn_party1
,reporting_oblgtn_party2
,report_date
,trade_status
,created_ts
,created_user
,updated_ts
,updated_user
,option_strike_price
,option_strike_price_ccy
,party1_name
,party2_name
,party1_known
,party2_known
)
select
a.uti_prfx
,a.uti_val
,a.primary_asset_class
,a.message_type
,a.party1_acct_id
,a.party1_prfx
,a.party1_val
,a.party2_acct_id
,a.party2_prfx
,a.party2_val
,a.trade_accept_id
,a.transaction_type
,a.data_submitter_prfx
,a.data_submitter_val
,a.as_of_date_time
,a.scheduled_termination_date
,a.product_id1_prfx
,a.product_id1_val
,a.product_id2_prfx
,a.product_id2_val
,a.notional_amount
,a.notional_ccy_units
,a.quantity
,a.price_notation_price
,a.price_notation_type
,a.option_type
,a.buyer_prfx_party1
,a.buyer_val_party1
,a.buyer_prfx_party2
,a.buyer_val_party2
,a.reporting_oblgtn_party1
,a.reporting_oblgtn_party2
,a.report_date
,a.trade_status
,a.created_ts
,a.created_user
,a.updated_ts
,a.updated_user
,a.option_strike_price
,a.option_strike_price_ccy
,a.party1_name
,a.party2_name
,a.party1_known
,a.party2_known
from(
select
a.uti_prfx
,a.uti_val
,a.primary_asset_class
,a.message_type
,a.party1_acct_id
,a.party1_prfx
,a.party1_val
,a.party2_acct_id
,a.party2_prfx
,a.party2_val
,a.trade_accept_id
,a.transaction_type
,a.data_submitter_prfx
,a.data_submitter_val
,a.as_of_date_time
,a.scheduled_termination_date
,a.product_id1_prfx
,a.product_id1_val
,a.product_id2_prfx
,a.product_id2_val
,a.notional_amount
,a.notional_ccy_units
,a.quantity
,a.price_notation_price
,a.price_notation_type
,a.option_type
,a.buyer_prfx_party1
,a.buyer_val_party1
,a.buyer_prfx_party2
,a.buyer_val_party2
,a.reporting_oblgtn_party1
,a.reporting_oblgtn_party2
,a.report_date
,a.trade_status
,a.created_ts
,a.created_user
,current_timestamp as updated_ts
,current_user as updated_user
,a.option_strike_price
,a.option_strike_price_ccy
,a.party1_name
,a.party2_name
,a.party1_known
,a.party2_known
,row_number() over (partition by a.uti_prfx,a.uti_val,a.party1_acct_id
order by a.updated_ts desc) as rank
from gtr_etd.etd_matching a)a
where a.rank = 1;

--------------------updating etd_matching for Auto Exit--------------------

INSERT INTO gtr_etd.ETD_MATCHING
(
uti_prfx
,uti_val
,primary_asset_class
,message_type
,party1_acct_id
,party1_prfx
,party1_val
,party2_acct_id
,party2_prfx
,party2_val
,trade_accept_id
,transaction_type
,data_submitter_prfx
,data_submitter_val
,as_of_date_time
,scheduled_termination_date
,product_id1_prfx
,product_id1_val
,product_id2_prfx
,product_id2_val
,notional_amount
,notional_ccy_units
,quantity
,price_notation_price
,price_notation_type
,option_type
,buyer_prfx_party1
,buyer_val_party1
,buyer_prfx_party2
,buyer_val_party2
,reporting_oblgtn_party1
,reporting_oblgtn_party2
,report_date
,trade_status
,created_ts
,created_user
,updated_ts
,updated_user
,option_strike_price
,option_strike_price_ccy
,party1_name
,party2_name
,party1_known
,party2_known
)
select
mat.uti_prfx
,mat.uti_val
,mat.primary_asset_class
,mat.message_type
,mat.party1_acct_id
,mat.party1_prfx
,mat.party1_val
,mat.party2_acct_id
,mat.party2_prfx
,mat.party2_val
,mat.trade_accept_id
,mat.transaction_type
,mat.data_submitter_prfx
,mat.data_submitter_val
,mat.as_of_date_time
,mat.scheduled_termination_date
,mat.product_id1_prfx
,mat.product_id1_val
,mat.product_id2_prfx
,mat.product_id2_val
,mat.notional_amount
,mat.notional_ccy_units
,mat.quantity
,mat.price_notation_price
,mat.price_notation_type
,mat.option_type
,mat.buyer_prfx_party1
,mat.buyer_val_party1
,mat.buyer_prfx_party2
,mat.buyer_val_party2
,mat.reporting_oblgtn_party1
,mat.reporting_oblgtn_party2
,mat.report_date
,'AutoExit' as trade_status
,mat.created_ts
,mat.created_user
,a.created_ts as updated_ts
,mat.updated_user
,mat.option_strike_price
,mat.option_strike_price_ccy
,mat.party1_name
,mat.party2_name
,mat.party1_known
,mat.party2_known
from gtr_etd.etd_auto_exit a,gtr_etd.etd_matching_temp mat
where
mat.party1_acct_id = a.party1_acct_id
and mat.uti_val = a.uti_val
and coalesce(mat.uti_prfx,'x') = coalesce(a.uti_prfx,'x')
and a.report_date = rep_date AND MAT.TRADE_STATUS = p_Active;




RETURN 1;

EXCEPTION WHEN OTHERS THEN




RAISE NOTICE 'Error in loading data into eod auto exit table [%]',sqlerrm;


INSERT INTO gtr_etd.etd_db_error_log (FILE_ID,BATCH_ID,ERROR_ID,ERROR_TS,ERROR_TEXT,ERROR_SOURCE)
VALUES(null,null,ERROR_CODE,current_timestamp,sqlerrm,'gtr_etd.etd_auto_exit()');


return -1;






END ;

$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION gtr_etd.etd_auto_exit()
OWNER TO gtradmin;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtr_etd_read;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtr_etd_write;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtr_read;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtr_dev;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtr_etd_admin;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtr_itr_app;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO gtradmin;
GRANT EXECUTE ON FUNCTION gtr_etd.etd_auto_exit() TO public;

[qt_ky]

What is the connection to DataStage Server Edition forum? All I see is a bunch of SQL.

[chulett]

So... not a DataStage question. There are plenty of resources out there on how to use the RANK analytical function in Oracle at sites dedicated to supporting Oracle and/or SQL, search and ye shall find.

[ray.wurlod]

To calculate a rank within DataStage you need your data to be sorted on the column for which the rank needs to be calculated, and you need two stage variables.

The first stage variable returns the rank, and is incremented if the value in the current row is different from the value in that column in the previous row. It should be initialised to 0.

The second stage variable "remembers" the value from the previous row. It should be initialised to a value that does not occur in the column data.

If you want to handle ties properly, you need two more stage variables, one to remember the "true" rank number, and one that flags whether a tie occurred in the previous row.