CHK_OPO     BBTradeDb..t_bb_opo tickets for an account/position

--------------------------------------------------------------------------------
-- BBTradeDb..t_bb_opo tickets for an account/position
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set Date1="`date '+%b %d %Y'`"
\set Date2="`date '+%b %d %Y'`"
--
print "BBTradeDb..t_bb_opo ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
select date_received=convert(varchar(20), date_rcvd, 116)
     , seqno
     , open_pos=open_position
     , open_avg=open_avg_cost
     , curr_pos=current_position
     , curr_avg=current_avg_cost
     , real_pnl=realized_pnl
     , secid
     , bb_id
  from BBTradeDb..t_bb_opo
 where "${Date1}" <= trade_date and trade_date <= "${Date2}"
   and trader = "${ACCT}"
   and (secid = "${SECURITY}" or bb_id = substring("${SECURITY}", 1, 8))
order by bb_id
       , date_rcvd
;-m vert
--------------------------------------------------------------------------------


CHK_OTR     BBTradeDb..t_bb_otr tickets for an account's movements

--------------------------------------------------------------------------------
-- BBTradeDb..t_bb_otr tickets for an account's movements
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set Date1="`date '+%b %d %Y'`"
\set Date2="`date '+%b %d %Y'`"
--
print "BBTradeDb..t_bb_otr  ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
select received=convert(char(8),date_rcvd,8)
     , seqno
     , tran_num
     , orig_tnum=orig_tran_num
     , rec_type
     , txtype = trans_type
     , c4c=cancel_correct_fg
     , rf=retail_feed_fg
     , bs=buysell_fg
     , trade_amt
     , price
     , discount_rate
     , long_note3
     , long_note4
     , trd_time=convert(char(8),trade_time,8)
     , asof_time=convert(char(8),asof_trade_time,8)
     , trd_date=convert(char(11),trade_date,106)
     , asof_date=convert(char(11),asof_trade_date,106)
     , secid
     , bb_id
     , ticker
 from BBTradeDb..t_bb_otr
where ("${Date1}" <= trade_date and trade_date <= "${Date2}")
  and trader_name = "${ACCT}"
  and (secid = "${SECURITY}" or bb_id = "${SECURITY}")
order by trader_name, bb_id, date_rcvd
;-m vert
--------------------------------------------------------------------------------


FST_POS     FstPosition_audit for selected account, security

--------------------------------------------------------------------------------
-- FstPosition_audit for selected account, security
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set Date2="`date '+%b %d %Y'`"
--
-- use temporary variables
declare @CUSIP varchar(20),
        @BB_ID varchar(20),
        @ACCTk int,
        @SECURITYk int
--
set nocount on
--
-- use temporary variables
select @SECURITYk = ItpIns#key
     , @CUSIP = cusip
     , @BB_ID = bb_id
  from FstDb..FstInsFixed
 where cusip = "${SECURITY}"
    or bb_id = "${BB_ID}"
--
select @ACCTk = ItpTransBucket#key
  from FstDb..FstAccount
 where name = "${ACCT}"
--
print "FstDb..FstPosition_audit  ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
set nocount off
select cusip=@CUSIP
     , bb_id=@BB_ID
     , inskey=@SECURITYk
     , curr_pos=current_trading_position
     , real_pnl=todays_realized_pl#amount
     , update_time=convert(varchar(8), last_update#time, 8)
     , trade_date=convert(varchar(11), bb_trade_key#trade_date, 106)
     , bb_seqno=bb_trade_key#bb_seqno
     , avg_price=todays_avg_price#amount
  from FstDb..FstPosition_audit
 where trans_bucket_key = @ACCTk
   and ins_key = @SECURITYk
   and ("${Date1}" <= bb_trade_key#trade_date and
                      bb_trade_key#trade_date <= "${Date2}")
;-m vert
--------------------------------------------------------------------------------


TRD_CLOSE   closingPosition records from TradeDb

--------------------------------------------------------------------------------
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set Date2="`date '+%b %d %Y'`"
\set FROM_DAYBEFORE=y
--
declare @DateLo datetime, @DateHi datetime
declare @sDateLo varchar(11), @sDateHi varchar(11)
\if [ "${FROM_DAYBEFORE}" = y ]
    \set SHIFT=1
\else
    \set SHIFT=0
\fi
set nocount on
select @DateLo=dateadd(day, 0 - ${SHIFT}, "${Date1}"),
       @DateHi=dateadd(day, 1 - ${SHIFT}, "${Date2}")
select @sDateLo=convert(varchar(11), @DateLo, 106),
       @sDateHi=convert(varchar(11), dateadd(day, -1, @DateHi), 106)
--
print "TradeDb..closingPosition  ${ACCT} ${SECURITY}  (%1! to %2!)"
      , @sDateLo, @sDateHi
print ""
--
select report_date_time=convert(varchar(20), report_date, 116)
     , trader
     , secid
     , pos=position
     , settle_pos=settle_position
     , ticker
     , coupon
     , maturity=convert(varchar(11), maturity_date, 106)
  from TradeDb..closingPosition
 where trader="${ACCT}"
   and secid="${SECURITY}"
   and (@DateLo <= report_date and report_date < @DateHi)
;-m vert
--------------------------------------------------------------------------------


TRD_STAT    TradeDb..genericTrades tickets for an account/position

--------------------------------------------------------------------------------
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%Y%m%d'`"
\set Date2="`date '+%Y%m%d'`"
\set TP_ONLY=y
\set CURRENT_ONLY=y
--
\if [ "${TP_ONLY}" = 'y' ]
    \set TP_CLAUSE="and fst_app like '%TicketProcessor%'"
\fi
--
\if [ "${CURRENT_ONLY}" = 'y' ]
    \set CURRENT_CLAUSE="and is_current = 1"
\else
    \set CURRENT_DISPLAY=', x=case is_current when 1 then "Y" else "N" end'
\fi
--
print "TradeDb..genericTrades  ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
select upd_time=convert(char(8), st.update_time, 108)
     , date=g.hit_date
     , group_id=substring(g.group_id,1,10)
     , fst_tran=g.fst_tran_num
     , bb_tran=map.bb_tran_num
     --, map.*
     , orig_fst=g.orig_fst_tran_num
     , orig_bb=g.orig_tran_num
     , grp_seq=g.group_tran_num
     , g.seqno
     , g.ticket_type
     , g.action_type
     ${CURRENT_DISPLAY}
     , side=case g.trade_side when 1 then "S" else "B" end
     , post_trd=case
                when g.trade_date=g.asof_trade_date then " "
                else convert(varchar(8), g.asof_trade_date, 112) end
     , quantity = convert(numeric(15,0), quantity)
     , st.rcode
     , st.fst_app
     , st.comment
     , account=g.trader_account
     , g.secid
  from TradeDb..genericTrades g
     , TradeDb..genericTradeMap map
     , TradeDb..genericTradeStatus st
where g.fst_tran_num = map.fst_tran_num
  and g.fst_tran_num *= st.fst_tran_num
  and (${Date1} <= g.hit_date and g.hit_date <= ${Date2})
  and trader_account = "${ACCT}"
  and (secid = "${SECURITY}" or secid = "${BB_ID}")
  ${CURRENT_CLAUSE}
  ${TP_CLAUSE}
-- order by g.fst_tran_num
order by st.update_time
;-m vert
--------------------------------------------------------------------------------


~           TradeDb..genericTrades action and ticket type to string
tkt_type_act="["+convert(char(2),ticket_type)+","+
                convert(char(2),action_type)+"="+
                -- text version
                convert(varchar(16),
                case action_type
                when  1 then "   New"
                when  2 then "   Cxl"
                when  3 then "  Corr"
                when  4 then "  PNew"
                when  5 then "  PCxl"
                when  6 then " PCorr"
                when  7 then "   Rej"
                when  8 then "  Conf"
                when  9 then "   C4c"
                when 10 then " Unchg"
                when 11 then "Corr4Cxl"
                when 12 then "Unconf"
                when 13 then " UnCxl"
                when 14 then "Cxl4Rej"
                else "???"+ltrim(convert(char(2),action_type))
                end
                + " " +
                case ticket_type
                when 40 then "Sale   "
                when 41 then "Trader "
                when 42 then "Match  "
                when 43 then "Master "
                when 44 then "Alloc  "
                when 45 then "Slate  "
                when 46 then "Journal"
                when 47 then "Swap   "
                else "???"+ltrim(convert(char(2),ticket_type))
                end)
                + "]"