#!/cluster/uapp/perl/bin/perl -w
use KAP;
use strict;
use Date::Manip;
use DBAccess;
use EQ_Misc;
use DBD::CSV;
my $_db_lv_conn_file = "MSLV.eq_script_user";
my $_read_dbi = undef;
# Run stored proc
my $sp = "lv..check_tax_lot";
KAP::write_log("Preparing $sp");
$_read_dbi = KAP::opendbi($_db_lv_conn_file) || handle_error_and_exit ("Failed to get a connection to MSLV db server");
my $_read_sth = $_read_dbi->prepare($sp);
if($DBI::err) {
handle_error_and_exit("**ERROR preparing $sp: " . $DBI::errstr);
}
KAP::write_log("Prepare done, executing $sp");
$_read_sth->execute();
if($DBI::err) {
handle_error_and_exit( "**ERROR executing $sp: " . $DBI::errstr);
}
KAP::write_log("Execute of $sp done");
my $_datafile = KAP::datafile();
my $numrecords = 0;
my @data = undef;
# Loop through the data and write to file
KAP::write_log("Start to loop through result set of stored proc and write data");
my $header_rec = join(",", @{$_read_sth->{NAME}});
print $_datafile $header_rec . "\n";
while (@data = $_read_sth->fetchrow_array) {
@data = EQ_Misc::arr_replace_undef("", @data);
my $csv_record = join(",", @data);
print $_datafile $csv_record . "\n";
$numrecords++;
}
KAP::write_log( "$numrecords records written to csv file.");
close $_datafile

/*********************************************
**************************************************************************************************************
* NAME : check_tax_lot
* TYPE : custom procedure
* CALLED FROM : Perl
*
* FUNCTIONAL AREAS : Check tax lot report
*
* DESCRIPTION : Executes and generates the tax lot report
*
* Date Author Description
* -------- ------ -----------------------------------------------------------------------
* MODIFIED : 20080204 nbare1 Created
* MODIFIED : 20080504 nbyla1 Commented t.* for Loan IP taxlot with open order and kept only reqd columns
*
**************************************************************************************************************/

create procedure dbo.check_tax_lot
as
begin
print 'Encumberances to look into:'
select
'Account' = a.short_name,
'Symbol' = s.symbol,
'Sedol' = s.user_id_4,
'Security Name' = s.name_1,
'Encumbered' = et.description,
'Enc. qty' = t.encumbered_quantity,
'Borrow Acct' = la.short_name
from tax_lot t
join account a on a.account_id = t.account_id
join security s on s.security_id = t.security_id
join encumbered_type et on et.encumbered_type_code = t.encumbered_type_code
left outer join account la on la.account_id = t.user_field_6
where t.encumbered_type_code in (94, 95, 96)
if @@ROWCOUNT = 0
begin
print 'No tax_lots of type LOANCOAC, SYS ENC or BO UNKN.'
end
print ''
print 'Position integrity:'
-- Integrity check of tax_lots for loans.
-- The sum of loan encumbered tax_lots on the lending account
-- should match the negative position on the borrowing account
set nocount on
create table #lender_tax_lots
(
account_id numeric(10,0) NOT NULL,
borrow_account_id numeric(10,0) NOT NULL,
security_id numeric(10,0) NOT NULL,
encumbered_quantity float NOT NULL
)
create table #borrowing_tax_lots
(
account_id numeric(10,0) NOT NULL,
security_id numeric(10,0) NOT NULL,
quantity float NOT NULL
)
create table #loan_transactions
(
account_id numeric(10,0) NOT NULL,
security_id numeric(10,0) NOT NULL,
quantity float NOT NULL
)
insert #lender_tax_lots
(
account_id,
borrow_account_id,
security_id,
encumbered_quantity
)
select
t.account_id,
t.user_field_6,
t.security_id,
isnull(sum(t.encumbered_quantity), 0)
from tax_lot t
where t.encumbered_type_code in (92, 94, 96)
and t.user_field_6 is not null
group by
t.account_id,
t.user_field_6,
t.security_id
insert #borrowing_tax_lots
(
account_id,
security_id,
quantity
)
select
t.account_id,
t.security_id,
sum(t.quantity)
from account a,
tax_lot t,
security s
where a.major_account_code = 1 -- TBD accounts (borrowing accounts)
and t.account_id = a.account_id
and t.position_type_code = 1 -- negative positions
and s.security_id = t.security_id
and s.major_asset_code = 1 -- equities
group by
t.account_id,
t.security_id
-- Insert any missing lender accounts
insert #lender_tax_lots
(
account_id,
borrow_account_id,
security_id,
encumbered_quantity
)
select
account_id = nila.account_id,
borrow_account_id = b.account_id,
security_id = b.security_id,
encumbered_quantity = 0
from #borrowing_tax_lots b,
norges_internal_borrowing_acct niba,
norges_internal_lending_acct nila
where niba.account_id = b.account_id
and nila.pool_id = niba.pool_id
and not exists (select 1
from #lender_tax_lots ltl
where ltl.account_id = nila.account_id
and ltl.borrow_account_id = b.account_id
and ltl.security_id = b.security_id )
-- Insert missing borrowing accounts
insert #borrowing_tax_lots
(
account_id,
security_id,
quantity
)
select
account_id = l.borrow_account_id,
security_id = l.security_id,
quantity = 0
from #lender_tax_lots l
where not exists (select 1
from #borrowing_tax_lots b
where b.account_id = l.borrow_account_id
and b.security_id = l.security_id)
-- Adjust borrowing account with sent uncofirmed trades
insert #loan_transactions
(
account_id,
security_id,
quantity
)
select
a.account_id,
a.security_id,
sum(a.quantity * s.market_value_sign )
from
#borrowing_tax_lots blt
join allocations a on a.account_id = blt.account_id
and a.security_id = blt.security_id
join side s on s.side_code = a.side_code
join orders o on o.order_id = a.order_id
where a.primary_pending = 1
and a.deleted = 0
and a.primary_canceled = 0
and o.user_field_1 is not null
group by
a.account_id,
a.security_id
update #borrowing_tax_lots
set quantity = #borrowing_tax_lots.quantity + lt.quantity
from #loan_transactions lt
where lt.account_id = #borrowing_tax_lots.account_id
and lt.security_id = #borrowing_tax_lots.security_id
-- Final select
select
lender_account = a_lender.short_name,
borrow_account = a_borrow.short_name,
sedol = s.user_id_4,
symbol = s.symbol,
security_name = s.name_1,
encumbered = l.encumbered_quantity,
loaned = b.quantity,
diff = (l.encumbered_quantity - b.quantity)
from #lender_tax_lots l,
#borrowing_tax_lots b,
account a_lender,
account a_borrow,
security s
where b.account_id = l.borrow_account_id
and b.security_id = l.security_id
and a_lender.account_id = l.account_id
and a_borrow.account_id = b.account_id
and s.security_id = b.security_id
and abs(l.encumbered_quantity - b.quantity) > 0.05
order by
a_lender.short_name,
s.symbol,
a_borrow.short_name
if @@ROWCOUNT = 0
begin
print 'No position inconsistencies found'
end
drop table #lender_tax_lots
drop table #borrowing_tax_lots
drop table #loan_transactions

-- Loan in progress inconsistencies
-- Loan in Progress
select 'Loan in progress inconsistencies:'
create table #loan_in_progress (
tax_lot_id numeric(10,0),
order_id numeric(10,0),
account_id numeric(10,0) NOT NULL,
borrow_account_id numeric(10,0) NOT NULL,
security_id numeric(10,0) NOT NULL,
encumbered_quantity float NOT NULL
)
create table #new_loan_orders (
order_id numeric(10,0),
block_id numeric(10,0),
quantity_ordered float,
quantity_confirmed float,
quantity_closed float,
quantity_executed float,
unsent_executions float,
quantity_left float
)
create table #unsent_executions (
block_id numeric(10,0),
unsent_executions float
)
insert #loan_in_progress (
tax_lot_id,
order_id,
account_id,
borrow_account_id,
security_id,
encumbered_quantity
)
select
tax_lot_id = t.tax_lot_id,
order_id = convert(numeric(10,0), t.user_field_5),
account_id = t.account_id,
borrow_account_id = convert(numeric(10,0), t.user_field_6),
security_id = t.security_id,
encumbered_quantity = t.encumbered_quantity
from tax_lot t
where t.encumbered_type_code = 91
and t.user_field_6 is not null
and t.user_field_5 is not null
and t.encumbered_quantity > 0.005

insert #new_loan_orders (
order_id ,
block_id ,
quantity_ordered ,
quantity_confirmed ,
quantity_closed ,
quantity_executed ,
unsent_executions ,
quantity_left
)
select
order_id = o.order_id,
block_id = o.block_id,
quantity_ordered = bo.quantity_ordered,
quantity_confirmed = bo.quantity_confirmed,
quantity_closed = bo.quantity_closed,
quantity_executed = bo.quantity_executed,
unsent_executions = 0,
quantity_left = 0
from orders o
join blocked_orders bo on bo.block_id = o.block_id
join side s on s.side_code = o.side_code
where o.user_field_1 is not null
and s.buy_indicator = 0
insert #unsent_executions (
block_id ,
unsent_executions
)
select
block_id = nlo.block_id,
unsent_executions = sum(t.quantity_executed)
from #new_loan_orders nlo
join ticket t on t.block_id = nlo.block_id
where t.primary_pending = 0
and t.primary_confirmed = 0
and t.deleted = 0
and t.primary_canceled = 0
group by nlo.block_id
update #new_loan_orders
set unsent_executions = ue.unsent_executions
from #new_loan_orders nlo
join #unsent_executions ue on ue.block_id = nlo.block_id
update #new_loan_orders
set quantity_left = quantity_ordered -
quantity_confirmed -
quantity_closed -
(quantity_executed - unsent_executions)
-- Insert missing tax_lots
insert #loan_in_progress (
tax_lot_id,
order_id,
account_id,
borrow_account_id,
security_id,
encumbered_quantity )
select
tax_lot_id = 0,
order_id = o.order_id,
account_id = o.account_id,
borrow_account_id = o.user_field_1,
security_id = o.security_id,
encumbered_quantity = 0
from #new_loan_orders nlo
join orders o on o.order_id = nlo.order_id
where o.user_field_1 is not null
and o.deleted = 0
and o.closed = 0
and not exists (select 1
from tax_lot t
where convert(numeric(10,0), t.user_field_5) = o.order_id
and t.encumbered_type_code = 91 )
-- Show any differences
select
tax_lot_id = case lip.tax_lot_id
when 0 then null
else lip.tax_lot_id
end,
lender_account = a_lender.short_name,
borrow_account = a_borrow.short_name,
sedol = s.user_id_4,
symbol = s.symbol,
security_name = s.name_1,
order_id = lip.order_id,
block_id = nlo.block_id,
encumbered = lip.encumbered_quantity,
quantity_ordered = nlo.quantity_ordered,
quantity_confirmed = nlo.quantity_confirmed,
quantity_closed = nlo.quantity_closed,
quantity_executed = nlo.quantity_executed,
unsent_executions = nlo.unsent_executions,
quantity_left = nlo.quantity_left,
diff = lip.encumbered_quantity - nlo.quantity_left
from #loan_in_progress lip
join #new_loan_orders nlo on nlo.order_id = lip.order_id
join account a_lender on a_lender.account_id = lip.account_id
join account a_borrow on a_borrow.account_id = lip.borrow_account_id
join security s on s.security_id = lip.security_id
where abs(lip.encumbered_quantity - nlo.quantity_left) > 0.005
if @@ROWCOUNT = 0
begin
print 'No loan in progress inconsistencies found'
end
drop table #loan_in_progress
drop table #new_loan_orders
drop table #unsent_executions
-- Loan IP tax-lot without open loan order?
print ''
print 'Loan IP tax_lot without open order?'
select
account = a.short_name,
symbol = s.symbol,
sedol = s.user_id_4,
security_name = s.name_1,
--- nbyla1 commented / added START. Commented extraction of all the columns and kept only required columns
-- t.*
t.tax_lot_id,
t.security_id,
t.position_type_code,
t.quantity,
t.encumbered_quantity
--- nbyla1 commented / added END. Commented extraction of all the columns and kept only required columns
from tax_lot t
join account a on a.account_id = t.account_id
join security s on s.security_id = t.security_id
where t.encumbered_type_code = 91
and not exists (select 1
from orders o
where o.order_id = convert(numeric(10,0), t.user_field_5)
and o.closed = 0
and o.deleted = 0 )
if @@ROWCOUNT = 0
begin
print 'No missing open orders found.'
end

print ''
print 'Loan tax_lot without loan account (user_field_6) or "LV Transaction ID" (user_field_4):'
select
account = a.short_name,
symbol = s.symbol,
sedol = s.user_id_4,
security_name = s.name_1,
'Enc. qty' = t.encumbered_quantity,
'Loan Account' = isnull(la.short_name, ''),
'LV Transaction ID' = t.user_field_4
from tax_lot t
join account a on a.account_id = t.account_id
join security s on s.security_id = t.security_id
left outer join account la on la.account_id = t.user_field_6
where t.encumbered_type_code = 92
and (t.user_field_4 is null or t.user_field_6 is null)

if @@ROWCOUNT = 0
begin
print 'No faulty loan tax-lots found.'
end

-- Negative taxlots on index accounts
-- (Mainly due to overencumberance from IBT)
print ''
print 'Negative tax_lots on index account (Over-encumbered??):'
select
account = a.short_name,
symbol = s.symbol,
sedol = s.user_id_4,
security_name = s.name_1,
quantity = tl.quantity
from norges_internal_lending_acct nila
join account a on a.account_id = nila.account_id
join tax_lot tl on tl.account_id = nila.account_id
join security s on s.security_id = tl.security_id
where tl.position_type_code = 0
and tl.quantity < 0.0
if @@ROWCOUNT = 0
begin
print 'No negative tax-lots on index accounts found.'
end
print ''
print 'Encmbered quantity differs from quantity on taxlot:'
select
account = ac.short_name,
symbol = se.symbol,
sedol = se.user_id_4,
security_name = se.name_1,
quantity = tl.quantity,
encumbered_quantity = tl.encumbered_quantity,
encumbered_type = et.description,
description = tl.user_field_1
from tax_lot tl
join account ac on ac.account_id = tl.account_id
join security se on se.security_id = tl.security_id
join encumbered_type et on et.encumbered_type_code = tl.encumbered_type_code
where tl.encumbered_type_code is not null
and tl.encumbered_quantity <> quantity
if @@ROWCOUNT = 0
begin
print 'No faulty encumbrances found.'
end
end;