#!/cluster/uapp/perl/bin/perl -w
use KAP; #internally developed library.
use strict;
use Date::Manip; #internally developed library.
use DBAccess;
use EQ_Misc; #internally developed library.
use DBD::CSV;
use MIME::Lite;
use Net::SMTP;
use Data::Dumper;

  my $_db_lv_conn_file = "MSLV.eq_script_user";
  my $_read_dbi = undef;

            # Run stored proc
    my $sp = "lv..check_tax_lot"; #The stored procedure that I am running, which returns me 7 resultset.

    KAP::write_log("Preparing $sp");#To write log
    
    $_read_dbi = KAP::opendbi($_db_lv_conn_file) || handle_error_and_exit ("Failed to get a connection to MSLV db server"); # To connect to the database
    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");#To write log

    $_read_sth->execute();
    if($DBI::err) {
        handle_error_and_exit( "**ERROR executing $sp: " . $DBI::errstr);
    }
    KAP::write_log("Execute of $sp done");#To write the log

    do {
        my $row = $_read_sth->fetchrow_arrayref();
        print Dumper $row;
        } while ($_read_sth->{syb_more_results}); 

##</code><code>##

nbsda1@vcstest3$ perl sudip.pl
$VAR1 = [
          '15140EQJP_55121',
          'DD UN',
          '2018175',
          'DU PONT (E.I.) DE NEMOURS',
          'BO data mismatch',
          '275000.0',
          '98027EQJP_54981'
        ];
nbsda1@vcstest3$ 

##</code><code>##

########## RESULTSET 1 ##############
														
Account	Symbol	Sedol	Security Name	Encumbered	Enc. qty	Borrow Acct	
								
15140EQJP_55121	DD UN	2018175	DU PONT (E.I.) DE NEMOURS	BO data mismatch	275000	98027EQJP_54981									
15140EQJP_55121	AFL UN	2026361	AFLAC INC	BO data mismatch	100000	29075EQJP_54301									
15140EQJP_55121	SLM UN	2101967	SLM CORP	BO data mismatch	180000	98002EQJP_54161									
						
########## RESULTSET 2 ##############															
Position integrity:															
															
lender_account	borrow_account	sedol	symbol	security_name	encumbered	loaned	diff								
15140EQJP_55121	98003EQJP_54181	6497071	000700 KP	HANJIN SHIPPING	25000	0	25000								
15140EQJP_55121	98003EQJP_54181	6494997	005880 KP	KOREA LINE CORP	8160	0	8160	
															
########## RESULTSET 3 ##############															
91 - LOAN-IP - Loan in progress inconsistencies:															
															
tax_lot_id	lender_account	borrow_account	sedol	symbol	security_name	order_id	block_id	encumbered	quantity_ordered	quantity_confirmed	quantity_closed	quantity_executed	unsent_executions	quantity_left	diff
No loan in progress inconsistencies found															

########## RESULTSET 4 ############## 															
Loan IP tax_lot without open order?															
															
account	symbol	sedol	security_name	tax_lot_id	security_id	position_type_code	quantity	encumbered_quantity							
No missing open orders found.															

########## RESULTSET 5 ############## 															
Loan tax_lot without loan account (user_field_6) or "LV Transaction ID" (user_field_4):															
															
account	symbol	sedol	security_name	Enc. qty	Loan Account	LV Transaction ID									
15140EQJP_55121	FIC UN	2330299	FAIR ISAAC CORP	1105		[NULL]									

########## RESULTSET 6 ############## 															
Negative tax_lots on index account (Over-encumbered??):															
															
account	symbol	sedol	security_name	quantity											
28963EQJP_55441	2379 TT	6051422	REALTEK SEMICONDUCTOR CORP	-37000											
15140EQJP_55121	2379 TT	6051422	REALTEK SEMICONDUCTOR CORP	-83379											
							
########## RESULTSET 7 ############## 															
Encmbered quantity differs from quantity on taxlot:															
															
account	symbol	sedol	security_name	quantity	encumbered_quantity	encumbered_type	description								
No faulty encumbrances found.